Question

Problem 1: Heart Beats is a manufacturer of medical equipment. The company's primary product is a device used to monitor the heart during medical procedures. This device is produced in two factories and shipped to two warehouses. The product is then shipped on demand to four third-party wholesalers. All shipping is done by truck. The product distribution network is shown below. The annual production capacity at Factories 1 and 2 is 400 and 250, respectively. The annual demand at Wholesalers 1.2, 3, and 4 is 200, 100, 150, and 200, respectively. The cost of shipping one unit in each shipping lane is shown on the arcs. Due to limited truck capacity, at most 250 units can be shipped from Factory 1 to Warehouse 1 each year. Formulate and solve a network optimization model in a spreadsheet to determine how to distribute the product at the lowest possible annual cost. 11-200 S60 $40 4001 FI 2501 535 S55 S50 12501 (F2 WH $65 WS-200
please answer and show work

Answer 1

The problem can be solved by minimum-cost flow problem. the spreadsheet is developed as follows:

1. List all from-to arcs and their unit cost of transportation
2. List decision variable “Arc flow units” in column D
3. List any flow constraints (arc capacity) in column F
4. List constraints of net flow:– (In-flow to node)– (Out-flow from node) = supply/demand
   1. List all nodes
   2. List in-flow:- =SUMIF($A$3:$A$10,H3,$D$3:$D$10)     (copy from I3 to I10)
   3. List out-flow:- =SUMIF($B$3:$B$10,H3,$D$3:$D$10)     (copy from j3 to J10)
   4. List net flow:- =+I3-J3    (copy from K3 to K10)
5. Total cost (Cell J12) = (arc cost) x (Arc flow units) :- =SUMPRODUCT(C3:C10,D3:D10)
6. Solver option: (refer image)

The spreadsheet model is as follows:

Solver Parameters Arc Unit Arc Flow Units Supply/ Demand From To Cost Capacity NodesIn-Flow Out-Flow Net Flow WH1 $40 WH2 WH2 Set Objective: To: Max By Changing Variable Cells: SJS12 F1 F1 F2 <250 F1 F2 WH1 WH2 WS1 WS2 WS3 $35 150 250 250 Min Value Of 7 WH1 WS2 $35 200 SDS3:SDS10 100 100 100 150 200 Subject to the Constraints: 10 WH2 200 200 200 SDS3:SDS10 integer SKS3:SKS10 SMS3:SM$10 Total Cost of netwrok $58,500 12 14 15 16 17 18 Make Unconstrained Variables Non-Negative Select a SolvingSimplex LP Method:

G H Arc Ard Unit Flow Cost Units Arc Supply/ Demand From To In-Flow Out-Flow Net Flow F1 SUMIF(SA$3:SA$10, H3,SDS3:$D$10 SUMIF(SB$3:$B$10,H3,SD$3:$D$10) +13-J3 F2 SUMIF(SA$3:SA$10, H4,SD$3:SD$10 SUMIF(SB$3:SB$10,H4,SD$3:$D$10 +14-14 WH1 -SUMIF(SA$3:SA$10,H5,SD$3:SD$10) SUMIF(SB$3:SB$10,H5,SD$3:SD$10) +15-J5 WH2 -SUMIF(SA$3:SA$10,H6,SDS3:$D$10 SUMIF(SB$3:$B$10,H6,SD$3:$D$10) 16-16 WS1 -SUMIF(SA$3:SA$10,H7,SD$3:$D$10) SUMIF(SB$3:$B$10,H7,SD$3:$D$10) +17-17 WS2SUMIF(SA$3:SA$10,H8,SD$3:ŞD$10) SUMIF(SB$3:$B$10, H8,SD$3:$D$10) +18-J8 WS3 -SUMIF(SA$3:$A$10,H9,SD$3:$D$10) SUMIF(SB$3:$B$10,H9,SD$3:$D$10) 9-J9 WS4 -SUMIF(SA$3:SA$10,H10,SD$3:$D$10) SUMIF(SB$3:$B$10,H10,SD$3:SD$10) -+110-J10200 250 150 250 200 <250 400 250 6 WH1 Wsi -200 50 -100 -150 9WH2WS3 50 10 WH2 WS4 65 150 12 Total Cost of netwrok SUMPRODUCT (C3:C10, D3:D10)

Optimal Solution:

Arc		Arc Unit Cost	Arc Flow Units
From	To
F1	WH1	40	250
F1	WH2	35	150
F2	WH2	25	250
WH1	WS1	60	200
WH1	WS2	35	50
WH2	WS2	55	50
WH2	WS3	50	150
WH2	WS4	65	200