IT business
mottcr
excelprogsolverv1-template_1.xlsEx1-Unsolved
| Example 1 (Transportation Problem from Handout Notes) | |||||||
| Origin | # | Capacity | Destination | # | Demand | ||
| Seattle | 1 | 9000 | Pittsburgh | 1 | 3000 | ||
| Columbus | 2 | 4000 | Mobile | 2 | 5000 | ||
| New York | 3 | 8000 | Denver | 3 | 4000 | ||
| 21000 | Los Angeles | 4 | 6000 | ||||
| Washington | 5 | 3000 | |||||
| 21000 | |||||||
| Warehouse | |||||||
| Plant | Pittsburgh | Mobile | Denver | Los Angeles | Washington | ||
| Seattle | 10 | 20 | 5 | 9 | 10 | ||
| Columbus | 2 | 10 | 8 | 30 | 6 | ||
| New York | 1 | 20 | 7 | 10 | 4 | ||
| Decision Variables: | Pitts | Mobile | Denver | LA | Wash | ||
| xij | 1 | 2 | 3 | 4 | 5 | ||
| Seattle | 1 | 0 | 0 | 0 | 0 | 0 | |
| Columbus | 2 | 0 | 0 | 0 | 0 | 0 | |
| NY | 3 | 0 | 0 | 0 | 0 | 0 | |
| Objective Function: | Total Cost = $ | 0 | |||||
| Constraints: | Seattle Capacity | 0 | <= | 9000 | |||
| Columbus Capacity | 0 | <= | 4000 | ||||
| NY Capacity | 0 | <= | 8000 | ||||
| Pittsburgh Demand | 0 | = | 3000 | ||||
| Mobile Demand | 0 | = | 5000 | ||||
| Denver Demand | 0 | = | 4000 | ||||
| LA Demand | 0 | = | 6000 | ||||
| Washington Demand | 0 | = | 3000 | ||||
| Note: | 3 X 5 = 15 decision variables | ||||||
| 3 (orgins) + 5 (destinations) = 8 regular constraints |
Ex1-Solved
| Example 1 (Transportation Problem from Handout Notes) | |||||||
| Origin | # | Capacity | Destination | # | Demand | ||
| Seattle | 1 | 9000 | Pittsburgh | 1 | 3000 | ||
| Columbus | 2 | 4000 | Mobile | 2 | 5000 | ||
| New York | 3 | 8000 | Denver | 3 | 4000 | ||
| 21000 | Los Angeles | 4 | 6000 | ||||
| Washington | 5 | 3000 | |||||
| 21000 | |||||||
| Warehouse | |||||||
| Plant | Pittsburgh | Mobile | Denver | Los Angeles | Washington | ||
| Seattle | 10 | 20 | 5 | 9 | 10 | ||
| Columbus | 2 | 10 | 8 | 30 | 6 | ||
| New York | 1 | 20 | 7 | 10 | 4 | ||
| Decision Variables: | Pitts | Mobile | Denver | LA | Wash | ||
| 1 | 2 | 3 | 4 | 5 | |||
| Seattle | 1 | 0 | 0 | 4000 | 5000 | 0 | |
| Columbus | 2 | 0 | 4000 | 0 | 0 | 0 | |
| NY | 3 | 3000 | 1000 | 0 | 1000 | 3000 | |
| Objective Function: | Total Cost = $ | 150000 | |||||
| Constraints: | Seattle Capacity | 9000 | <= | 9000 | |||
| Columbus Capacity | 4000 | <= | 4000 | ||||
| NY Capacity | 8000 | <= | 8000 | ||||
| Pittsburgh Demand | 3000 | = | 3000 | ||||
| Mobile Demand | 5000 | = | 5000 | ||||
| Denver Demand | 4000 | = | 4000 | ||||
| LA Demand | 6000 | = | 6000 | ||||
| Washington Demand | 3000 | = | 3000 |
Ex2-Template
| Example 2 (Transshipment Problem from Handout Notes) | |||||||||||
| Warehouse (Hub) | Customer | ||||||||||
| Plant | 4 | 5 | Capacity | Warehouse | 6 | 7 | 8 | 9 | |||
| 1 | 4 | 7 | 450 | 4 | 6 | 4 | 8 | 4 | |||
| 2 | 8 | 5 | 600 | 5 | 3 | 6 | 7 | 7 | |||
| 3 | 5 | 6 | 250 | Demand | 300 | 300 | 300 | 400 | |||
| Decision Variables: | |||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||
| 1 | NA | NA | NA | 0 | 0 | NA | NA | NA | NA | ||
| 2 | NA | NA | NA | 0 | 0 | NA | NA | NA | NA | ||
| 3 | NA | NA | NA | 0 | 0 | NA | NA | NA | NA | ||
| 4 | NA | NA | NA | NA | NA | 0 | 0 | 0 | 0 | ||
| 5 | NA | NA | NA | NA | NA | 0 | 0 | 0 | 0 | ||
| Obj Function: | Total Cost = | ||||||||||
| Constraints: | P1 Capacity | <= | 450 | Outbound | |||||||
| P2 Capacity | <= | 600 | Outbound | ||||||||
| P3 Capacity | <= | 250 | Outbound | ||||||||
| Hub4 | = | 0 | Hub | ||||||||
| Hub5 | = | 0 | Hub | ||||||||
| C6 Demand | = | 300 | Inbound | ||||||||
| C7 Demand | = | 300 | Inbound | ||||||||
| C8 Demand | = | 300 | Inbound | ||||||||
| C9 Demand | = | 400 | Inbound | ||||||||
| Note: | (3 X 2) + (2 X 4) = 14 decision variables | ||||||||||
| 3 (origins) + 2 (hubs) + 4 (destinations) = 9 regular constraints |
Ex2-Solved
| Example 2 (Transshipment Problem from Handout Notes) | |||||||||||
| Warehouse (Hub) | Customer | ||||||||||
| Plant | 4 | 5 | Capacity | Warehouse | 6 | 7 | 8 | 9 | |||
| 1 | 4 | 7 | 450 | 4 | 6 | 4 | 8 | 4 | |||
| 2 | 8 | 5 | 600 | 5 | 3 | 6 | 7 | 7 | |||
| 3 | 5 | 6 | 250 | Demand | 300 | 300 | 300 | 400 | |||
| Decision Variables: | |||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||
| 1 | 0 | 0 | 0 | 450 | 0 | 0 | 0 | 0 | 0 | ||
| 2 | 0 | 0 | 0 | 0 | 600 | 0 | 0 | 0 | 0 | ||
| 3 | 0 | 0 | 0 | 250 | 0 | 0 | 0 | 0 | 0 | ||
| 4 | 0 | 0 | 0 | 0 | 0 | 0 | 300 | 0 | 400 | ||
| 5 | 0 | 0 | 0 | 0 | 0 | 300 | 0 | 300 | 0 | ||
| Obj Function: | Total Cost = | 11850 | |||||||||
| Constraints: | P1 Capacity | 450 | <= | 450 | Outbound | ||||||
| P2 Capacity | 600 | <= | 600 | Outbound | |||||||
| P3 Capacity | 250 | <= | 250 | Outbound | |||||||
| Hub4 | 0 | = | 0 | Hub | |||||||
| Hub5 | 0 | = | 0 | Hub | |||||||
| C6 Demand | 300 | = | 300 | Inbound | |||||||
| C7 Demand | 300 | = | 300 | Inbound | |||||||
| C8 Demand | 300 | = | 300 | Inbound | |||||||
| C9 Demand | 400 | = | 400 | Inbound | |||||||
| Note: | (3 X 2) + (2 X 4) = 14 decision variables | ||||||||||
| 3 (origins) + 2 (hubs) + 4 (destinations) = 9 regular constraints |
