Why am I not getting the correct answer for 7.18. I have included solver and formulas. Can someone please help

cells b18 to F18 are..

Total Shipped column

=SUM(B13:B17)

=SUM(C13:C17)

=SUM(D13:D17)

=SUM(E13:E17)

=SUM(F13:F17)

Shipped Row

=H3*G13-SUM(B13:F13)

=H4*G14-SUM(B14:F14)

=H5*G15-SUM(B15:F15)

=H6*G16-SUM(B16:F16)

=H7*G17-SUM(B17:F17)

shipping cost =SUMPRODUCT(B3:F7,B13:F17)

fixed cost =SUMPRODUCT(G3:G7,G13:G17)

Total Cost =SUM(K19:K20)

876 BADMS03 Vol. 2 Chapter Seven Ung e hYN b ed in the table below, along with the expected new vabae of each project (all values in millions of dollars Project 3 2 4 5 514 Year 2 Femme HIP w in a brief for this problem Potem solve this mes h 7.17. The school band to the leve l District has made the decision to purchase 1.350 tional Mach com pers for computer laboratories in all its schools and on experience, the schoolboard has directed that these cou r s should be purchased from some combination of the companies Educomp, Macwin, and McElectronics in all three cases, the companies charge a discounted variable cost per computer anda Fixed delivery and installation cost for these large sales to school districts. The table below shows these charges as well as the capacity the maximum number of computers that can be sold from the limited inventory for each of the companies SS $2 $15 $20 59 523 Educomp Macwin Capadty Fred cost Variable cost McElectronics 1.000 $5,000 $45.000 $ 0 $35,000 $775 5700 E Each woject must be done in full with the corresponding sush nows for all four years) or not done at all. Furthermore, there are the following additional considerations, Project I cannot be done unless Project 2 is also undertaken, and projects and 4 would compete with each other, so they should not both hecho. YOC expects to have the following cash available to invest in the projects: 540 million for year 1. S25 million for year 2 $16 million for year, and S12 million for year 4. Any available money not spent in a given year is then available to spend the following year. YCC's policy is to choose their projects so as to maximize their total expected NPV. a. Formulate a BIP model in algebraic form for this problem. alate and solve this model on a spreadsheet. Read the referenced article that fully describes the man agement science study summarized in the application vignette presented in Section 7.5. Briefly describe how mixed BIP was applied in this study. Then list the various financial and non- mancial benefits that resulted from this study. 7.16. An electrical utility needs to generate 6,500 megawatts of electricity today. It has five generators. If any clectricity is penerated by a given generator, that generator must be started up and fixed start-up cost is incurred. There is an additional cost Grech megawatt generated by a generator. These costs, as well. as the maximum capacity of each generator, are shown in the following table. The objective is to determine the minimum cost plan that meets the electrical needs for today. The school board wants to selecting the minimum-cost plan for meeting its computer needs. Formate a BIP model in algehine form for this problem b Formulate and solve this model on a spreadsheet. Now suppose that Macwin has not submitted its final bid yet, so the per computer cost is not known with certainty. Generales parameter analysis report with RSPE to show the optimal order quantities and total cost of the optimul solution when the cost per computer for Macwin is 5680, $690, 5700 S710,..., 5790, or $800. 7:18. Noble Amazon sells books online, Manngement is try. ing to determine the best sites for the company's warehouse The five potential sites under consider a re listed in the first column of the table at the bottom of the page. Most of the sales come from customers in the United States. The average weekly demand from each region of the country, the average shipping A $3,000 Generator C $2.500 56 2,500 $1,500 51.000 B $2,000 $4 1,800 Fixed start-up cost Cost per megawatt/day generated Maximum capacity ( M idan 1,500 3,000 2,100 Average Shipping Cost (5/book) Warehouse Site Spokane, WA Reno, NV Omaha, NE Harrisburg. PA lacksonville, FL Customer demand per week) Northwest Southwest 52.40 $3.50 $3.25 $2.30 3405 $3.25 $5 25 56.05 $6.95 $5.85 8,000 12.000 Midwest Southeast $4.80 56 80 $3.40 $5.25 $2.85 54.30 $430 $3.25 54 80 52.10 9,000 14,000 Northeast 55.75 36.00 $4.75 $2.75 53.50 17,000 Fixed Cost (per week) $40,000 $30,000 $25,000 540,000 $30,000 Warehouse Capacity (books/week) 20,000 20,000 15.000 25.000 15,000 Introduction to Managemen ution to Management Science A Modeling and Case Studies Approach with Spreadsheets 877 Case 1 A 251 production and distribution of the WchSurfer from the various plants, through the warehouses, to the customers that will minimize total costs Fixed Cost (S/week) cost from cach warehouse site to each region of the country, the fixed cost per week of each warehouse if it is operated and the maximum capacity of each warehouse (if it is operated) are shown in the table. Formulate and solve a mixed BIP model in a spreadsheet to determine which warehouse sites Noble Amazon should operate and how books should be distributed from each warehouse to each region of the country to minimize total cost. E7.19. Aberdeen Computer Corp. (ACC) is located in Aber- deen, Washington. The company has developed the WebSurfer, a low-cost e-mail and Web-surfing appliance. This product is manufactured at four plants, located in Atlanta, Kansas City. Aberdeen, and Austin. After production, the WebSurfers are shipped to three warehouses, located in Nashville, San Jose, and Houston ACC sells the WebSurfers through the retail channel. In particular, five different retailers currently sell the WebSurfer-Sears, Best Buy, Fry's, Comp USA, and Office Max. ACC makes weekly shipments to the main warehouses of these five retailers. The shipping cost from each plant to each warehouse, along with the production cost and weekly produc- tion capacity at each plant, are given in the table below. Plant Atlanta Kansas City Aberdeen Austin 39.000 $10.000 Warehouse Fixed Cost S/week) San Jose Houston 54.000 55.000 $5.000 b. Now suppose that ACC is considering saving money by closing some of its production facilities Plant Shipping Cost (S/unit) Nashville San Jose Houston $30 540 $25 $40 545 $55 530 530 Capacity (units/week) 200 Atlanta Kansas City Aberdeen Austin 550 Production Cost (S/unit) $208 $214 $215 5210 300 300 $30 $50 400 The shipping cost from each warehouse to each customer, the variable cost (cost per unit moved through the warehouse), the capacity (maximum number of units that can be moved through the warehouse per week) for each warehouse, and the weekly demand for each customer are given in the table below. and/or warehouses. Suppose there is a fixed cost to operate each plant and each warehouse as indi- cated in the tables above. Add binary variables to your model in part a to incorporate the decision of which plants and warehouses to keep open so as to Shipping Cost (S/unit) Best Comp Fry's USA Variable Cost (S/unit) Sears $40 $15 Buy $45 530 Capacity (units/week) 300 500 500 Warehouse Nashville San Jose Houston Customer demand (per week) $25 Office Max $20 $40 $50 150 $50 535 50 $15 $40 550 100 75 300 4. Formulate and solve a linear programming model in a spreadsheet to determine the plan for weekly minimize total cost (including the fixed costs for any plant or warehouse that is operated). Share Comments Data Solve What If Analysis Outline ? Solver Parameters Set Objective: $K$21 To: Max Min Value Of: By Changing Variable Cells: $B$13:56$17 Subject to the Constraints: SBS18:5F$18 = $B$9:$F$9 SG$13:$G$17 - binary SH$13:5HS17 >= 0 Add Change Delete Reset All Load/Save DUCT (B3:F7.813:F DUCT(G3:G7,613. B:K20) Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRC Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth. Close Solve Recover Unsaved Workbooks. We were able to save changes to one or more files. Do you want to recover them? 13 x f x =H3*G13-SUM(B13:F13) Shipping Cost Spokane Reno Northwest $2.40 $3.25 $4.05 $5.25 $6.95 Southwest $3.50 $2.30 $3.25 $6.05 $5.85 Midwest $4.80 $3.40 $2.85 $4.30 $4.80 Southeast $6.80 $5.25 $4.30 $3.20 $2.10 Northeast $5.75 $6.00 $4.75 $2.75 $3.50 Cost $40,000 $30,000 $25,000 $40,000 $30,000 Capacity 20,000 20,000 15,000 25,000 15,000 Omaha Hamsburg Jacksonville 8,000 12,000 9,000 14,000 17,000 Total Southwest Southeast Northeast Operate Warehouse? Shipped Capacity Northwest 8,000 Midwest 0 12,000 Shipments Spokane Reno Omahe Hamsburg Jacksonville Total Shipped 0 9.000 OOOO 0 17,000 14.000 14,000 + 8,000 12,000 9.000 17,000 Weally Demand Shipping Cost Fixed Cost Total Cost $148,600 $104,200 $252,800