Heart Start produces automated external defibrillators (AEDs)in each of two different plants (A and B).The unit production costs and monthly production capacity of the two plants are indicated in the table below.The AEDs are sold through three wholesalers.The shipping cost from each plant to the warehouse of each wholesaler along with the monthly demand from each wholesaler are also indicated in the table.How many AEDs should be produced in each plant,and how should they be distributed to each of the three wholesaler warehouses so as to minimize the combined cost of production and shipping? (a)Formulate this problem as a transportation problem by constructing the appropriate parameter table.(b)Formulate and solve a linear programming model in a spreadsheet for this problem.

Question

Heart Start produces automated external defibrillators (AEDs)in each of two different plants (A and B).The unit production costs and monthly production capacity of the two plants are indicated in the table below.The AEDs are sold through three wholesalers.The shipping cost from each plant to the warehouse of each wholesaler along with the monthly demand from each wholesaler are also indicated in the table.How many AEDs should be produced in each plant,and how should they be distributed to each of the three wholesaler warehouses so as to minimize the combined cost of production and shipping?  (a)Formulate this problem as a transportation problem by constructing the appropriate parameter table.(b)Formulate and solve a linear programming model in a spreadsheet for this problem.

Quizplus · Accepted Answer

The Answer is (a)The above table already is close to being a parameter table for a transportation problem,but we still need to add the two unit costs shown for each combination of a plant and a warehouse,as well as identify the monthly production capacity as being the "supply" from each plant.Because the total supply (220)exceeds the total demand (210),we also need a dummy destination to receive the excess supply.The resulting parameter table for formulating this problem as a transportation problem is shown below.

(a)The above table already is close to being a parameter table for a transportation problem,but we still need to add the two unit costs shown for each combination of a plant and a warehouse,as well as identify the monthly production capacity as being the supply from each plant.Because the total supply (220)exceeds the total demand (210),we also need a dummy destination to receive the excess supply.The resulting parameter table for formulating this problem as a transportation problem is shown below. (b)This is a transportation problem as described in Sec.9.1 of the textbook.Start by entering the data.The data for this problem are the unit shipping cost,the unit production cost and monthly production capacity at each plant,and the monthly demand at each warehouse.The data are shown below,where range names of UnitProductionCost (H5:H6),MonthlyCapacity (H11:H12),and MonthlyDemand (C15:E15)are assigned to the corresponding data cells.Note that space has been reserved in the middle of the spreadsheet (C11:E12)for the changing cells. The decisions to be made in this problem are how many units to ship from each plant to each warehouse.Therefore,we add a table of changing cells with range name UnitsShipped (C11:E12).The values in UnitsShipped (C11:E12)will eventually be determined by the Solver.For now,zeroes are entered. The goal is to minimize the total cost,including production costs and shipping costs.The total production costs and the total shipping costs are calculated as follows.Total Production Cost = SUMPRODUCT(UnitProductionCost,UnitsShipped),Total Shipping Cost = SUMPRODUCT(ShippingCost,UnitsShipped).These formulas are entered into H16 and H17,with the overall total cost calculated by summing these two costs in H18,as shown in the spreadsheet below. The functional constraints in this problem are that each plant can't ship out more than its monthly capacity,and each warehouse needs to receive its monthly demand.The units shipped out of each plant are calculated by summing each row of changing cells,while the units shipped to each warehouse are caculated by summing each column of changing cells.These constraints are shown in the spreadsheet below. The Solver information and solved spreadsheet are shown below. Thus,from Plant A they should ship 40 to Warehouse 1 and 60 units to Warehouse 2,from Plant B they should ship 40 units to Warehouse 1 and 70 units to Warehouse 3,giving an overall total cost of $132,790.

(b)This is a transportation problem as described in Sec.9.1 of the textbook.Start by entering the data.The data for this problem are the unit shipping cost,the unit production cost and monthly production capacity at each plant,and the monthly demand at each warehouse.The data are shown below,where range names of UnitProductionCost (H5:H6),MonthlyCapacity (H11:H12),and MonthlyDemand (C15:E15)are assigned to the corresponding data cells.Note that space has been reserved in the middle of the spreadsheet (C11:E12)for the changing cells.

The decisions to be made in this problem are how many units to ship from each plant to each warehouse.Therefore,we add a table of changing cells with range name UnitsShipped (C11:E12).The values in UnitsShipped (C11:E12)will eventually be determined by the Solver.For now,zeroes are entered.

The goal is to minimize the total cost,including production costs and shipping costs.The total production costs and the total shipping costs are calculated as follows.Total Production Cost = SUMPRODUCT(UnitProductionCost,UnitsShipped),Total Shipping Cost = SUMPRODUCT(ShippingCost,UnitsShipped).These formulas are entered into H16 and H17,with the overall total cost calculated by summing these two costs in H18,as shown in the spreadsheet below.

The functional constraints in this problem are that each plant can't ship out more than its monthly capacity,and each warehouse needs to receive its monthly demand.The units shipped out of each plant are calculated by summing each row of changing cells,while the units shipped to each warehouse are caculated by summing each column of changing cells.These constraints are shown in the spreadsheet below.

The Solver information and solved spreadsheet are shown below.

Thus,from Plant A they should ship 40 to Warehouse 1 and 60 units to Warehouse 2,from Plant B they should ship 40 units to Warehouse 1 and 70 units to Warehouse 3,giving an overall total cost of $132,790.

Heart Start Produces Automated External Defibrillators (AEDs)in Each of Two