Problem 1 [Source: E. Book Case 7.1 page 255) 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? Xi $8 $6 2 Build a factory in San Francisco? X 5 3 3 Build a warehouse in Los Angeles? Xy 6 5 Build a warehouse in San Francisco? X Capital available: $10 million on 66 Nw8 Recreate the spreadsheet model for this problem and run Parametric post optimality analysis using solver table to analyze the effects of reducing or increasing the available budget by $5 million in increments of S1 million on all the variables, amount spent, and the total NPV. Requirement to be completed individually during the workshop on November 18, 2021, ISA: Use your own words to provide full recommendations on the best budget that should be allocated based on the following five different criteria using horizontal and vertical analysis of the obtained solver table results: (a) Maximizing the total NPV; (b) Maximizing the number of buildings to build; (c) Minimizing deviation from the current available budget; (d) Maximizing the estimated net present gain (Total NPV - Total Spent); and (e) Maximizing the Return on Investment ratio (Estimated net present gain / S spent). Required Structure of Solver Table: Input Cell Acted FLA 505 506 FSF Output Cells WLA WSF S Spent a S NPV (b) Buildings (c) Deviation (d) ENP Gain (e) ROI SOM 514 SI5M Problem 1 [Source: E. Book Case 7.1 page 255) 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? Xi $8 $6 2 Build a factory in San Francisco? X 5 3 3 Build a warehouse in Los Angeles? Xy 6 5 Build a warehouse in San Francisco? X Capital available: $10 million on 66 Nw8 Recreate the spreadsheet model for this problem and run Parametric post optimality analysis using solver table to analyze the effects of reducing or increasing the available budget by $5 million in increments of S1 million on all the variables, amount spent, and the total NPV. Requirement to be completed individually during the workshop on November 18, 2021, ISA: Use your own words to provide full recommendations on the best budget that should be allocated based on the following five different criteria using horizontal and vertical analysis of the obtained solver table results: (a) Maximizing the total NPV; (b) Maximizing the number of buildings to build; (c) Minimizing deviation from the current available budget; (d) Maximizing the estimated net present gain (Total NPV - Total Spent); and (e) Maximizing the Return on Investment ratio (Estimated net present gain / S spent). Required Structure of Solver Table: Input Cell Acted FLA 505 506 FSF Output Cells WLA WSF S Spent a S NPV (b) Buildings (c) Deviation (d) ENP Gain (e) ROI SOM 514 SI5M