Please need help with this problem below. Please use excel and LINDO software to solve. Thank you!

Satco Foods produces tomato sauce at three plants located in Los Angeles, New York and St. Louis. The monthly production capacity (in millions of pounds) of each plant and variable production cost (per pound) are as follows:

Production Capacity Variable Production Cost

Plant (in millions) (per pound)

New York 9 $.20

Los Angeles 7 $.23

St. Louis 8 $.18

After the tomato sauce is produced it is shipped to four different warehouses located in Philadelphia, Chicago, Atlanta and Kansas City. The shipping cost from plant to warehouse is the same for all locations it is $.0002 pounds per mile. At least 2,000,000 pounds of tomato sauce per year must be shipped through a warehouse for it to be economical to use. If a warehouse site is not used it incurs no cost. To get some idea of monthly shipping and operating cost at the warehouses, Satco has tabulated historical data in file Satco_Template.xlsx. This file contains information of monthly shipping and operating cost for the warehouses. It is based on analysis of other similar products that are stored in the warehouse and shipped to final destinations. This data is provided in two tables. Table 1 shows shipping/ transportation cost from the warehouses to final destinations as a function of pounds and mileage shipped. Table 2 shows average warehouse operating costs for the warehouse operations over the last 18 months. These operating costs are associated with storage and handling of product on a per pound basis. The cost are segregated by east coast operations (average for Philadelphia and Atlanta warehouses) and mid west operations (average for Chicago and Kansas City Operations). Use this information to determine unit shipping / transportation and operation cost associated with the warehouses.

Satco ships tomato sauce to six distributors from the warehouse operations. The monthly amounts of tomato sauce required by each distributor is as follows:

Monthly Requirement

Distributor Location (in millions of pounds)

1 Boston 2

2 Memphis 2.5

3 Detroit 4

4 Denver 2

5 Dallas 3.5

6 San Francisco 5

The travel distance (in miles) between each plant and potential warehouse site is as follows:

	Philadelphia	Chicago	Atlanta	Kansas City
New York	100	802	841	1198
Los Angles	2706	2054	2182	1589
St. Louis	868	289	541	257

The travel distance (in miles) between each warehouse and each distributor is as follows:

Mileage Data from Warehouse to Demand Destination
	Boston	Memphis	Detroit	Denver	Dallas	San Francisco
Philadelphia	296	1000	576	1691	1452	2866
Chicago	963	530	266	996	917	2142
Atlanta	1037	371	699	1398	795	2496
Kansas City	1391	451	743	600	489	1835

Determine how Satco can minimize the next month cost of getting tomato sauce from plants to customers. Hint: use the historical data in tables to create a forecast to help solve the transportation problem.

Perform all work in the Satco Excel Template

TABLE 1: Warehouse Shipping Cost Data POUNDS SHIPPED MILES SHIPPED COST 40000 300 $13.889.54 48000 400 $19.296.10 56000 500 $25,289.94 64000 380 $25,094.49 72000 420 $29,323.66 80000 590 $38,688.36 88000 780 $49,111.94 96000 990 $60,853.76 104000 1000 $65,554.65 112000 350 $42,187.98 120000 450 $51,092.73 128000 562 $60,937.77 136000 782 $75,748.82 144000 345 $53,691.41 152000 672 $78,222.92 160000 981 $99,255.48 168000 189 $46,002.37 176000 329 $63,944.35 184000 417 $75,665.58 192000 506 $85,680.52 TABLE 2 - Operating Cost ($/LB) East Coat Ops Mid West Ops Month Cost/lb Month Cost/1b 0.015 1 0.014 0.016 2 0.015 0.016 0.016 0.018 0.015 0.019 0.016 0.02 0.017 0.02 0.016 0.022 0.017 0.023 0.019 0.023 10 0.02 0.024 11 0.02 0.026 0.021 0.026 13 0.021 0.027 0.02 0.028 15 0.021 0.029 16 0.023 0.029 17 0.024 18 0.03 18 0.024 OGUN OWN 12 14 Mileage data from plant to warehouse PHIL CHIC ATL KC NY 100 802 841 1198 LA 2706 2054 2182 1589 STL 868 289 541 257 Mileage data from warehouse to demand destination BOST MEMP DET DENV DALL SF PHIL 296 1000 576 1691 1452 2866 CHIC 963 530 266 996 917 2142 ATL 1037 371 699 1398 795 2496 KC 1391 451 743 600 489 1853 TABLE 1: Warehouse Shipping Cost Data POUNDS SHIPPED MILES SHIPPED COST 40000 300 $13.889.54 48000 400 $19.296.10 56000 500 $25,289.94 64000 380 $25,094.49 72000 420 $29,323.66 80000 590 $38,688.36 88000 780 $49,111.94 96000 990 $60,853.76 104000 1000 $65,554.65 112000 350 $42,187.98 120000 450 $51,092.73 128000 562 $60,937.77 136000 782 $75,748.82 144000 345 $53,691.41 152000 672 $78,222.92 160000 981 $99,255.48 168000 189 $46,002.37 176000 329 $63,944.35 184000 417 $75,665.58 192000 506 $85,680.52 TABLE 2 - Operating Cost ($/LB) East Coat Ops Mid West Ops Month Cost/lb Month Cost/1b 0.015 1 0.014 0.016 2 0.015 0.016 0.016 0.018 0.015 0.019 0.016 0.02 0.017 0.02 0.016 0.022 0.017 0.023 0.019 0.023 10 0.02 0.024 11 0.02 0.026 0.021 0.026 13 0.021 0.027 0.02 0.028 15 0.021 0.029 16 0.023 0.029 17 0.024 18 0.03 18 0.024 OGUN OWN 12 14 Mileage data from plant to warehouse PHIL CHIC ATL KC NY 100 802 841 1198 LA 2706 2054 2182 1589 STL 868 289 541 257 Mileage data from warehouse to demand destination BOST MEMP DET DENV DALL SF PHIL 296 1000 576 1691 1452 2866 CHIC 963 530 266 996 917 2142 ATL 1037 371 699 1398 795 2496 KC 1391 451 743 600 489 1853