B 3 4 5 6 7 8 9 10 11 12 13 c d from f1 f1 f2 wh1 wh1

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: f 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). B 3 4 5 6 7 8 9 10 11 12 13 C D From F1 F1 F2 WH1 WH1 WH2 WH2 WH2 To WH1 WH2 WH2 WS1 WS2 WS2 WS3 WS4 Flow 10 10 10 10 10 10 10 10 E F G <= Capacity 250 Unit Cost $40 $35 $25 $60 $35 $55 $50 $65 H C J Net Flow 20 10 10 10 -10 -20 -10 -10 K L = = = = = = = = Supply/Demand 400 250 0 0 -200 -100 -150 -200 Total Cost $3,650 13 I Nodes F1 F2 WH1 WH2 WS1 WS2 WS3 WS4 D Total Cost =SUMPRODUCT(UnitCost,Flow) 3 The Solver information and solved spreadsheet are shown below. B 3 4 5 6 7 8 9 10 11 12 13 C D From F1 F1 F2 WH1 WH1 WH2 WH2 WH2 To WH1 WH2 WH2 WS1 WS2 WS2 WS3 WS4 Flow 250 150 250 200 50 50 150 200 Total Cost E F G <= Capacity 250 Unit Cost $40 $35 $25 $60 $35 $55 $50 $65 H I J Nodes F1 F2 WH1 WH2 WS1 WS2 WS3 WS4 Net Flow 400 250 0 0 -200 -100 -150 -200 K L = = = = = = = = Supply/Demand 400 250 0 0 -200 -100 -150 -200 $58,500 Solver Parameters Set Objective Cell: TotalCost To: Min By Changing Variable Cells: Flow Subject to the Constraints: D4 <= Capacity NetFlow = SupplyDemand Solver Options: Make Variables Nonnegative Solving Method: Simplex LP Range Name Capacity Flow From NetFlow Nodes SupplyDemand To TotalCost UnitCost Cells F4 D4:D11 B4:B11 J4:J11 I4:I11 L4:L11 C4:C11 D13 G4:G11 J 3 4 5 6 7 8 9 10 11 Net Flow =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) =SUMIF(From,Nodes,Flow)-SUMIF(To,Nodes,Flow) C 13 D Total Cost =SUMPRODUCT(UnitCost,Flow) Thus, Flow (D4:D11) indicates how to distribute the...
View Full Document

This document was uploaded on 01/31/2014.

Ask a homework question - tutors are online