Noble Amazon sells books online. Management is trying to determine the best sites for the company's warehouses. The five potential sites under consideration are listed in the first column of the following table. Most of the sales come from customers in the United States. The average weekly demand from each region of the country, the average shipping cost from each 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 following table.

Problem 1 [Source: E. Book Page 255, Section 7.1] 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. TABLE 7. 1 . . Capital gamut?\" me,c\"'gm"' Decision Yes-or-No Decision Nee Present Required \"lermg ' Number Question Variable Value (Millions) (Millions) 1 Build a factory In Los Angeles? x. $8 $6 2 Build a factory In San Francisco? x2 5 3 3 Build a warehouse In Los Angeles? x3 6 5 4 Build a warehouse in San Francisco? x. 4 2 Capltalavallable1$1o million 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. Use your own words to 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.|