Heart Beats Is a Manufacturer of Medical Equipment

Question

Heart Beats Is a Manufacturer of Medical Equipment

Examlex · Accepted Answer

The Answer is This is a minimum-cost flow problem.To set up a spreadsheet model,first list all of the arcs as shown in B4:C11,along with their capacity (F4)and unit cost (G4:G11).Only the arc from F1 to WH1 is capacitated.Then list all of the nodes as shown in I4:I11 along with each node's supply or demand (L4:L11).

This is a minimum-cost flow problem.To set up a spreadsheet model,first list all of the arcs as shown in B4:C11,along with their capacity (F4)and unit cost (G4:G11).Only the arc from F1 to WH1 is capacitated.Then list all of the nodes as shown in I4:I11 along with each node's supply or demand (L4:L11). The changing cells are the amount of flow to send through each arc.These are shown in Flow (D4:D11)below,with an arbitrary value of 10 entered for each.The flow through the arc from F1 to WH1 must be less than the capacity of 250,as indicated by the constraint D4 <= F4. For each node,calculate the net flow as a function of the changing cells.This can be done using the SUMIF function.In each case,the first SUMIF function calculates the flow leaving the node and the second one calculates the flow entering the node.For example,consider the F1 node (I4).SUMIF(From,Nodes,Flow)sums each individual entry in Flow (the changing cells in D4:D11)if that entry is in a row where the entry in From (B4:B11)is the same as in that row of Nodes are rows 4 and 5,the sum in the ship column is only over these same rows,so this sum is D4+D5. The goal is to minimize the total cost of shipping the product from the factories to the wholesalers.The cost is the SUMPRODUCT of the Unit Costs with the Flow,or Total Cost = SUMPRODUCT(UnitCost,Flow).This formula is entered into TotalCost (D13). The Solver information and solved spreadsheet are shown below. Thus,Flow (D4:D11)indicates how to distribute the product so as to achieve the minimum Total Cost (D13)of $58,500.

The changing cells are the amount of flow to send through each arc.These are shown in Flow (D4:D11)below,with an arbitrary value of 10 entered for each.The flow through the arc from F1 to WH1 must be less than the capacity of 250,as indicated by the constraint D4 <= F4.

For each node,calculate the net flow as a function of the changing cells.This can be done using the SUMIF function.In each case,the first SUMIF function calculates the flow leaving the node and the second one calculates the flow entering the node.For example,consider the F1 node (I4).SUMIF(From,Nodes,Flow)sums each individual entry in Flow (the changing cells in D4:D11)if that entry is in a row where the entry in From (B4:B11)is the same as in that row of Nodes are rows 4 and 5,the sum in the ship column is only over these same rows,so this sum is D4+D5.

The goal is to minimize the total cost of shipping the product from the factories to the wholesalers.The cost is the SUMPRODUCT of the Unit Costs with the Flow,or Total Cost = SUMPRODUCT(UnitCost,Flow).This formula is entered into TotalCost (D13).

The Solver information and solved spreadsheet are shown below.

Thus,Flow (D4:D11)indicates how to distribute the product so as to achieve the minimum Total Cost (D13)of $58,500.

Heart Beats Is a Manufacturer of Medical Equipment

Definitions: