The information needed from the Satco Excel Template is included. It is the charts at the bottom under the "data" tab in the picture.
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 CapacityVariable Production Cost
Plant (in millions)(per pound)
New York 9 $.20
Los Angeles7 $.23
St. Louis8 $.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
DistributorLocation(in millions of pounds)
1Boston2
2Memphis2.5
3Detroit4
4Denver2
5Dallas3.5
6San Francisco5
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
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 5.20 Los Angeles 7 S.23 St. Louis S.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 S.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 I 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 cast coast operations (average for Philadelphia and Atlanta warehouses) and midwest 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) Boston 2 2 Memphis 2.5 3 Detroit 4 Denver Dallas San Francisco 3.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 Answer Data Data Analysis Transportation Model TABLE - Operating Cat SL Motory for El Com POUNDS SHIPPED ME SHIPPED GO 0.015 . 5 50 SEM 11 TO 330 02 ST WER IT SPS OG 16 16 SEDERT 100 160 TNESS 10 18 417 C Mileage data from plant to warehouse PE CHC ATL 100 2703 2054 2182 NY LA STL 159 PH AT KG Mileage data from warehouse to demand destination MW DETDENDAL 2 576 053 1037 371 600 139 795 1391 TANA 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 5.20 Los Angeles 7 S.23 St. Louis S.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 S.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 I 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 cast coast operations (average for Philadelphia and Atlanta warehouses) and midwest 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) Boston 2 2 Memphis 2.5 3 Detroit 4 Denver Dallas San Francisco 3.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 Answer Data Data Analysis Transportation Model TABLE - Operating Cat SL Motory for El Com POUNDS SHIPPED ME SHIPPED GO 0.015 . 5 50 SEM 11 TO 330 02 ST WER IT SPS OG 16 16 SEDERT 100 160 TNESS 10 18 417 C Mileage data from plant to warehouse PE CHC ATL 100 2703 2054 2182 NY LA STL 159 PH AT KG Mileage data from warehouse to demand destination MW DETDENDAL 2 576 053 1037 371 600 139 795 1391 TANA
