Requirement:

provide full comments on the above case based on your horizontal and vertical analysis of the solver table outputs that would lead into providing relevant recommendations to the Management. When conducting solver table analysis, vary the available budget by $5 million above and below the current $10 million available budget in increments Of one million.

2 Refer to the Facility Location Problem in California Manufacturing Co. case from Section 7.1 where management is interested in determining the most profitable combination of investments using an available budget of $10 million. Capital Decision Yes-or-No Decision Net Present Required Number Question Variable Value (Millions) (Millions) 1 Build a factory in Los Angeles? $8 $6 2 Build a factory in San Francisco? 3 Build a warehouse in Los Angeles? 4 Build a warehouse in San Francisco? Capital available: $ 10 milion Recreate the spreadsheet model for this problem and provide your recommendations on the best budget to recommend based on the following criteria: (a) Maximizing NPV; (b) Minimizing deviation from the current available budget; (c) Maximizing the expected net present gain; (d) Maximizing the number of buildings to build; and (e) Maximizing the Return on investment ratio. A B D F G H E 1 California Manufacturing Co. Facility Location Problem NPV ($millions) LA Warehouse 6 SF 4 Factory 8 8 2 3 4 5 6 7 8 9 10 11 5 Capital Required (Smillions) Warehouse Range Name Cells Build? C16:D18 BuildFactory? C18:D18 BuildWarehouse? C16:D16 CapitalAvailable G12 Capital Required C10:012 CapitalSpent E12 MaxWarehouses G16 NPV 04:06 TotalNPV D20 TotalWarehouses E16 LA 5 SF 2 Capital Spent 9 Capital Available 10 12 Factory 6 3 Maximum Warehouses Build? Warehouse LA 0 Total Warehouses 0 SF 0