90.2K

Verified Solution

Link Copied!

See the "Optimization Model spreadsheet

You are a manufacturer of outdoor picnic furniture (benches, picnic tables, litter receptacles, etc.). You must decide on the production locations for a new product line Your Data Scientist has built a facility location optimization model in Excel Solver. It is a binary linear model that determines if you should open a production facility in the

designated city.

Blue cells you will alter for your sensitivity analysis Yellow cells are the decision variables (0 - no, 1 open production facility in this

location) (Y5-Y14)

Pink cells are calculations

Orange cell is total cost (AE15)

1. Run solver, report on the optimal production locations and the total cost.

2. Model if the President has imposed tariffs on Vietnam, effectively increasing labor cost from $2 to $5 per hour, and at the same time has negotiated a trade agreement with Mexico and Canada effectively reducing their labor cost per hour (Monterrey from $6 to $5. Toronto = from $28 to $20). Make these changes and run Solver. How did the optimal solution and total cost change? Comment on the impact of labor costs changes on the optimal solution.

3. Return to the original values for labor costs. New environmental regulations are i affecting ocean shipping costs. Double the Inbound and Outbound costs for Hanoi Vietnam. Run Solver, and report the optimal locations and total cost. Comment on how this may affect customer service.

4. One variable in the spreadsheet that is not included in the model is Delivery Time (days). If marketing decides that shipping time must be under 3 days, averaged across all production facilities, describe how you could include this in the model. I want only for you to explain how you would approach this from a modeling perspective and how it could be added to the model. You DO NOT need to try to add it to the model.

>> SER = 1. He - Hear , L M N O P Q R 33 18 8 8 8 8 8 8 8 8 8 8 8 8 | | ---- Total - - beeeeeee >> SER = 1. He - Hear , L M N O P Q R 33 18 8 8 8 8 8 8 8 8 8 8 8 8 | | ---- Total - - beeeeeee

Answer & Explanation Solved by verified expert

See Answer