b)
A12345678910111213141516171819202122BCDEFGStationLocationTract 1Tract 2Tract 3Tract 4Tract 5Tract 1520152510Response Time (minutes)Tract 2Tract 3Tract 4123020415102061515254251512Station within 15 minutes?Tract 2Tract 3Tract 4100111011101011211>=>=>=111450Tract 5152512105StationCost($thousand)200250400300500StationLocationTract 1Tract 11Tract 20Tract 31Tract 40Tract 51Stations within 15 minutes1>=1Tract 5101111>=1Station?11000Total Cost ($thousand) Cases 9.1
a) With this approach, we need to formulate an integer program for each month and
optimize each month individually.
In the first month, Emily does not buy any servers since none of the departments implement the intranet in the first month.
9-17
In the second month she must buy computers to ensure that the Sales Department can start the intranet. Emily can formulate her decision problem as an integer problem (the servers purchased must be integer. Her objective is to minimize the purchase cost. She has to satisfy to constraints. She cannot spend more than $9500 (she still has her entire budget for the first two months since she didn't buy any computers in the first month) and the computer(s) must support at least 60 employees. She solves her integer programming problem using the Excel solver. A12345678910111213141516BStandardIntel$2,5000%$2,500CEnhancedIntel$5,0000%$5,000DSGIWorkstation$10,00010%$9,000ESunWorkstation$25,00025%$18,750FGHOriginal CostDiscountUnit CostSupportNumber of Employees Server Supports30802002,000Budget Spent per Server Purchased$2,500$5,000$9,000$18,750StandardIntel0EnhancedIntel1SGIWorkstation0SunWorkstation0TotalSupport80BudgetSpent$5,000>=SupportNeeded60BudgetAvailable$9,500TotalCost$5,000Budget<=ServersPurchased 5ABCDEUnit Cost=(1-Discount)*OriginalCost=(1-Discount)*OriginalCost=(1-Discount)*OriginalCost=(1-Discount)*OriginalCost Range NameBudgetBudgetAvailableBudgetSpentDiscountOriginalCostServersPurchasedSupportSupportNeededTotalCostTotalSupportUnitCostCellsB8:E8F8H8B4:E4B3:E3B12:E12B8:E8H8H12F8B4:E5 678910Budget11Spent12=SUMPRODUCT(B12:E12,ServersPurchased) H14Total15Cost16=SUMPRODUCT(UnitCost,ServersPurchased)
FTotalSupport=SUMPRODUCT(Support,ServersPurchased)
Note, that there is a second optimal solution to this integer programming problem. For the same amount of money Emily could buy two standard PC's that would also support 60 employees. However, since Emily knows that she needs to support more employees in the near future, she decides to buy the enhanced PC since it supports more users.
9-18
For the third month Emily needs to support 260 users. Since she has already computing power to support 80 users, she now needs to figure out how to support additional 180 users at minimum cost. She can disregard the constraint that the Manufacturing Department needs one of the three larger servers, since she already bought such a server in the previous month. Her task leads her to the following integer programming problem and solution.
A123456789101112BStandardIntel$2,5000%$2,500CEnhancedIntel$5,0000%$5,000DSGIWorkstation$10,0000%$10,000ESunWorkstation$25,0000%$25,000FGHOriginal CostDiscountUnit CostSupportNumber of Employees Server Supports30802002,000StandardIntel0EnhancedIntel0SGIWorkstation1SunWorkstation0TotalSupport200>=SupportNeeded180TotalCost$10,000ServersPurchased Emily decides to buy one SGI Workstation in month 3. The network is now able to support 280 users.
In the fourth month Emily needs to support a total of 290 users. Since she has already computing power to support 280 users, she now needs to figure out how to support additional 10 users at minimum cost. This task leads her to the following integer programming problem:
A123456789101112BStandardIntel$2,5000%$2,500CEnhancedIntel$5,0000%$5,000DSGIWorkstation$10,0000%$10,000ESunWorkstation$25,0000%$25,000FGHOriginal CostDiscountUnit CostSupportNumber of Employees Server Supports30802002,000StandardIntel1EnhancedIntel0SGIWorkstation0SunWorkstation0TotalSupport30>=SupportNeeded10TotalCost$2,500ServersPurchased Emily decides to buy a standard PC in the fourth month. The network is now able to support 310 users.
9-19
Finally, in the fifth and last month Emily needs to support the entire company with a total of 365 users. Since she has already computing power to support 310 users, she now needs to figure out how to support additional 55 users at minimum cost. This task leads her to the following integer programming problem and solution.
A123456789101112BStandardIntel$2,5000%$2,500CEnhancedIntel$5,0000%$5,000DSGIWorkstation$10,0000%$10,000ESunWorkstation$25,0000%$25,000FGHOriginal CostDiscountUnit CostSupportNumber of Employees Server Supports30802002,000StandardIntel0EnhancedIntel1SGIWorkstation0SunWorkstation0TotalSupport80>=SupportNeeded55TotalCost$5,000ServersPurchased Emily decides to buy another enhanced PC in the fifth month. (Note that again she could have also bought two standard PC's, but clearly the enhanced PC provides more room for the workload of the system to grow.) The entire network of CommuniCorp consists now of 1 standard PC, 2 enhanced PC's and 1 SGI workstation and it is able to support 390 users. The total purchase cost for this network is $22,500.
b) Due to the budget restriction and discount in the first two months Emily needs to distinguish between the computers she buys in those early months and in the later months. Therefore, Emily uses two variables for each server type.
Emily essentially faces four constraints. First, she must support the 60 users in the sales department in the second month. She realizes that, since she no longer buys the computers sequentially after the second month, that it suffices to include only the constraint on the network to support the all users in the entire company. This second constraint requires her to support a total of 365 users. The third constraint requires her to buy at least one of the three large servers. Finally, Emily has to make sure that she stays within her budget during the second month.
9-20