business analysis
chalmerswong
09124fb6b5976c75527efe0bf9394f9e.xlsxBase Model
| $/unit/mile cost of forward shipping between plants and warehouses | 1 | |||||||||||||
| Fixed cost of opening a plant | 760553 | |||||||||||||
| Fixed cost of opening a warehouse | 695736 | |||||||||||||
| Distance in miles | ||||||||||||||
| Plants/Warehouses | W1 | W2 | W3 | Supply | Plants/Warehouses | W1 | W2 | W3 | Total Shipped | Supply | Binary Variable for Plants | Linking Constraint for Plants | ||
| A | 4351 | 3810 | 1459 | 12426 | A | 12426 | ||||||||
| B | 2963 | 2473 | 2624 | 17187 | B | 17187 | ||||||||
| Forward Flow Balance @ Warehouses | ||||||||||||||
| Binary Variable for Warehouses | ||||||||||||||
| Linking Constraints for Warehouses | ||||||||||||||
| Distance in miles | ||||||||||||||
| Warehouses/Customers | C1 | C2 | C3 | C4 | Warehouses/Customers | C1 | C2 | C3 | C4 | |||||
| W1 | 2103 | 1937 | 3649 | 3521 | W1 | |||||||||
| W2 | 2109 | 3140 | 1532 | 3474 | W2 | |||||||||
| W3 | 3853 | 2523 | 3541 | 1536 | W3 | |||||||||
| Demand | 20404 | 3288 | 3823 | 2098 | Total Received | |||||||||
| Demand | 20404 | 3288 | 3823 | 2098 | ||||||||||
| Fixed Cost | ||||||||||||||
| Variable Cost | ||||||||||||||
| Total Cost |
Retrofit
| $/unit/mile cost of forward shipping between plants and warehouses | 1 | |||||||||||||
| Fixed cost of opening a plant | 760553 | |||||||||||||
| Fixed cost of opening a warehouse | 695736 | |||||||||||||
| Distance in miles | ||||||||||||||
| Plants/Warehouses | W1 | W2 | W3 | Supply | Plants/Warehouses | W1 | W2 | W3 | Total Shipped | Supply | Binary Variable for Plants | Linking Constraint for Plants | ||
| A | 4351 | 3810 | 1459 | 12426 | A | 12426 | ||||||||
| B | 2963 | 2473 | 2624 | 17187 | B | 17187 | ||||||||
| Forward Flow Balance @ Warehouses | ||||||||||||||
| Binary Variable for Warehouses | ||||||||||||||
| Linking Constraints for Warehouses | ||||||||||||||
| Distance in miles | ||||||||||||||
| Warehouses/Customers | C1 | C2 | C3 | C4 | Warehouses/Customers | C1 | C2 | C3 | C4 | |||||
| W1 | 2103 | 1937 | 3649 | 3521 | W1 | |||||||||
| W2 | 2109 | 3140 | 1532 | 3474 | W2 | |||||||||
| W3 | 3853 | 2523 | 3541 | 1536 | W3 | |||||||||
| Demand | 20404 | 3288 | 3823 | 2098 | Total Received | |||||||||
| Demand | 20404 | 3288 | 3823 | 2098 | ||||||||||
| Fixed Cost | ||||||||||||||
| Variable Cost | Warehouses/Customers | C1 | C2 | C3 | C4 | Binary for Retrofitting | Linking for Retrofitting | Cannot be used unless the other open | ||||||
| Fixed Retrofitting Cost | W1 | |||||||||||||
| Variable Reverse Flow Cost | W2 | |||||||||||||
| Total Cost | W3 | |||||||||||||
| Sent from each C1 cannot exceed demand | ||||||||||||||
| Total Sent>=0.75* total demand |
1
2
3
4
5
6
7
8
9
A
B
C
D
$/unit/mile cost of forward
shipping between plants
and warehouses
1
Fixed cost of opening a
plant
760553
Fixed cost of opening a
warehouse
695736
Distance in miles
Plants/Warehouses
W1
W2
W3
A
4351
3810
1459
B
2963
2473
2624
