DISTOPSHET 8
P5-13 (a)
| Problem 5-13 | |||||||||||
| Shipments: | To | Flow balance equations | |||||||||
| From | Job 1 | Job 2 | Job 3 | Flow out | Location | Flow in | Flow out | Net flow | Sign | RHS | |
| Central | 0.0 | Central | 0 | 0 | 0 | >= | -3000 | ||||
| Rock | 0.0 | Rock | 0 | 0 | 0 | >= | -4000 | ||||
| Acme | 0.0 | Acme | 0 | 0 | 0 | >= | -6000 | ||||
| Flow in | 0.0 | 0.0 | 0.0 | Job 1 | 0 | 0.0 | 0 | = | 2500 | ||
| Job 2 | 0 | 0.0 | 0 | = | 3750 | ||||||
| Unit costs: | To | Job 3 | 0 | 0.0 | 0 | = | 4850 | ||||
| From | Job 1 | Job 2 | Job 3 | ||||||||
| Central | $9 | $8 | $7 | ||||||||
| Rock | $7 | $11 | $6 | ||||||||
| Acme | $4 | $3 | $12 | ||||||||
| Total cost = | $0 | <--- Minimize total transportation costs. Formula = SUMPRODUCT(B5:D7,B12:D14) | |||||||||
| Note: | |||||||||||
| Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569–571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 44–49 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 40–51 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints. | |||||||||||
P5-13 (b)
| Problem 5-13 (Transshipment) | ||||||||||||
| Shipments: | To | Flow balance equations | ||||||||||
| From | Job 1 | Job 2 | Job 3 | Acme | Flow out | Location | Flow in | Flow out | Net flow | Sign | RHS | |
| Central | 0.0 | Central | 0 | 0 | 0 | >= | -3000 | |||||
| Rock | 0.0 | Rock | 0 | 0 | 0 | >= | -4000 | |||||
| Acme | 0.0 | Acme | 0.0 | 0 | 0.0 | >= | -6000 | |||||
| Flow in | 0.0 | 0.0 | 0.0 | 0.0 | Job 1 | 0 | 0 | 0 | = | 2500 | ||
| Job 2 | 0 | 0 | 0 | = | 3750 | |||||||
| Unit costs: | To | Job 3 | 0 | 0 | 0 | = | 4850 | |||||
| From | Job 1 | Job 2 | Job 3 | Acme | ||||||||
| Central | $9 | $8 | $7 | $1 | ||||||||
| Rock | $7 | $11 | $6 | $1 | ||||||||
| Acme | $4 | $3 | $12 | $0 | ||||||||
| Total cost = | $0 | |||||||||||
| Note: | ||||||||||||
| Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569–571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 44–49 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 40–51 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints. | ||||||||||||