Compute the NPV of each alternative. Use the results from the transportation models for the COGS for each alternative. ( Hint: Your analysis will be simplified if you think in terms of incremental cash flows.) Create an easy-to-read spreadsheet for each alternative.

Below is the results from the transporation models and the COGS for each alternative from problem 2

Here is link to problem 2 as well https://www.chegg.com/homework-help/questions-and-answers/use-transportation-method-location-problems-pom-windows-find-optimal-distribution-pattern--q20975657

Solution Excel gives the following minimum cost solution.

Denver should be chosen as the location for fourth plant. Optimal producition and shipping plan is determined by Excel Solver. (in orange color)

1 Distance (in miles) and costs (capacity and Demand is in 1000 units) 1 *Selected Location Select Building & Annual Fixed Variable From I To Boston Cleveland Chicago St. Louis Denver Capacity Location Equipment Costs (SGA) Costs/Unit Land 9,500 Boston 650 1,000 1,200 2,000 400 650 350 600 1,400 400 1 7700 300 5.00 400 Cleveland Chicago 1.000 350 300 1,000 500 1 8600 400 3.25 600 600 300 850 500 0 10,800 750 3.05 800 St. Louis 1,200 500 1 12,100 550 5.15 1,200 Denver 2,000 1.400 1,000 850 80 200 370 440 610 Demand 10 Optimal Production and Shipping plan Per unit shipping cost 0.0005 per mile Row Spare From I To Boston Cleveland Chicago St. Louis Denver Total Capacity 11 Boston 220 Cleveland 0 0 370 20 110 500 Chicago 14 St. Louis Denver 16 Column Total 17 19 Total shipping cost 250,000 20 Building & Equipment Cost 37,900 21 Annual Fixed Cost 1,850 22 Annual Variable Cost 5,540,000 23 Land Cost 2,700 24 Total Annual Cost 5,832,450 26 1 Distance (in miles) and costs (capacity and Demand is in 1000 units) 1 *Selected Location Select Building & Annual Fixed Variable From I To Boston Cleveland Chicago St. Louis Denver Capacity Location Equipment Costs (SGA) Costs/Unit Land 9,500 Boston 650 1,000 1,200 2,000 400 650 350 600 1,400 400 1 7700 300 5.00 400 Cleveland Chicago 1.000 350 300 1,000 500 1 8600 400 3.25 600 600 300 850 500 0 10,800 750 3.05 800 St. Louis 1,200 500 1 12,100 550 5.15 1,200 Denver 2,000 1.400 1,000 850 80 200 370 440 610 Demand 10 Optimal Production and Shipping plan Per unit shipping cost 0.0005 per mile Row Spare From I To Boston Cleveland Chicago St. Louis Denver Total Capacity 11 Boston 220 Cleveland 0 0 370 20 110 500 Chicago 14 St. Louis Denver 16 Column Total 17 19 Total shipping cost 250,000 20 Building & Equipment Cost 37,900 21 Annual Fixed Cost 1,850 22 Annual Variable Cost 5,540,000 23 Land Cost 2,700 24 Total Annual Cost 5,832,450 26