encode in excel the spreadsheet model based on the network diagram and solve for the optimal...

Transcribed Image Text:

encode in excel the spreadsheet model based on the network diagram and solve for the optimal solution using excel solver. encode data on spreadsheet model. changing cells (ship column) should be marked 0. encode the required formula (sum-if) on netflows cells. encide the required formula (sumproduct) on the objective cell. color code the cells. fill up solver dialogue box on solver. Molave Furniture Co. has two factories producing design product that needs to be shipped to two warehouses W Factory 1 produces 70 units. Factory 2 produces 60 units Warehouse 1 needs 50 units Warehouse 2 needs 80 units There are road links directly from Factory 1 to Warehouse 1 and Factory 2 to Warehouse 2 Independent truckers are available to ship up to 20 units from each factory to the distribution center, and then 20 units from the distribution center to each warehouse The shipping costs are given such that From F1 to W1 $600, F1 to DC $200, DC to W2 $300; F2 to W2 $900 F2 to DC $400. DC to W1 $100 Solution Using Excel Solver Determine the units of goods that could be transported from the source to the destination points subject to the costs and load limitations, while minimizing the total cost From To Ship F1 W1 F1 HONNO DC DC W2 F2 W2 F2 DC DC W1 Total Cost A A A A A A Capacity Unit Cost Nodes Net Flow Supply Demand F1 F2 DC W1 W2 encode in excel the spreadsheet model based on the network diagram and solve for the optimal solution using excel solver. encode data on spreadsheet model. changing cells (ship column) should be marked 0. encode the required formula (sum-if) on netflows cells. encide the required formula (sumproduct) on the objective cell. color code the cells. fill up solver dialogue box on solver. Molave Furniture Co. has two factories producing design product that needs to be shipped to two warehouses W Factory 1 produces 70 units. Factory 2 produces 60 units Warehouse 1 needs 50 units Warehouse 2 needs 80 units There are road links directly from Factory 1 to Warehouse 1 and Factory 2 to Warehouse 2 Independent truckers are available to ship up to 20 units from each factory to the distribution center, and then 20 units from the distribution center to each warehouse The shipping costs are given such that From F1 to W1 $600, F1 to DC $200, DC to W2 $300; F2 to W2 $900 F2 to DC $400. DC to W1 $100 Solution Using Excel Solver Determine the units of goods that could be transported from the source to the destination points subject to the costs and load limitations, while minimizing the total cost From To Ship F1 W1 F1 HONNO DC DC W2 F2 W2 F2 DC DC W1 Total Cost A A A A A A Capacity Unit Cost Nodes Net Flow Supply Demand F1 F2 DC W1 W2