IE 323: IE 323 Warehouse location problems
Basic idea: 2 Basic idea Integrated plan for production and distribution:
Where should production plants be located?
Where should transshipment centers (warehouses) be located?
How should goods be transported?
Objective is to minimize total operating cost
Ideas should be familiar: 3 Ideas should be familiar You’ve seen:
Transportation and transshipment problems
Location problems
Now combine these to produce an integrated logistics plan
Huntco example: 4 Huntco example Determine:
Which plants and warehouses to operate
How much to ship from each plant to each warehouse
How much to ship from each warehouse to each customer
Minimize total fixed costs and production and shipping costs
Objective: 5 Objective Minimize total costs, which include:
Fixed costs of operating plants and warehouses
Variable production costs at plants
Shipping costs from plants to warehouses and from warehouses to customers
Constraints: 6 Constraints Don’t ship at all from plants that are closed
Don’t ship more than capacity from plants that are open
Amounts shipped into warehouses must equal amounts shipped out
Don’t ship into or out of warehouses that are closed
Meet customer requirements
Inputs: 7 Inputs Unit production/shipping costs from plants to warehouses
Unit shipping costs from warehouses to customers
Fixed costs of operating plants
Fixed costs of operating warehouses
Customer requirements
Decision Variables: 8 Decision Variables Amounts shipped from plants to warehouses
0-1 variables to indicate which plants are operating
Amounts shipped from warehouses to customers
0-1 variables to indicate which warehouses are operating
Spreadsheet Model(See “Steps 1-3” sheet of Excel): 9 Spreadsheet Model (See “Steps 1-3” sheet of Excel) Step 1: Enter all inputs
Step 2: Enter any amounts to ship from each plant to each warehouse and from each warehouse to each customer
Step 3: Enter any values in the 0-1 changing cells for which plants and warehouses to operate
See “Steps 4,5” sheet of Excel: 10 See “Steps 4,5” sheet of Excel Step 4: Calculate the amounts shipped out of the plants as row sums
Step 5: Using 0-1 variables, calculate the logical upper limits for the sums in step 4:
For an operating plant, this is the plant capacity
For a closed plant, this is 0
See “Steps 6,7” sheet of Excel: 11 See “Steps 6,7” sheet of Excel Step 6a: Calculate an upper limit on the amount shipped out of each warehouse
Total customer demand
Step 6b: Using the 0-1 variables, calculate a logical upper bound on the amount shipped out of each warehouse:
Bound from step 6a for an operating warehouse
0 for a closed warehouse
See “Steps 6,7” sheet of Excel: 12 See “Steps 6,7” sheet of Excel Step 7a: Calculate the amounts shipped out of the warehouses as row sums
Step 7b: Transpose these into a row
See “Steps 8,9” sheet of Excel: 13 See “Steps 8,9” sheet of Excel Step 8: Calculate the amounts received by the warehouses as column sums
Step 9: Calculate the amounts received by the customers as column sums
See “Steps 10-12” sheet of Excel: 14 See “Steps 10-12” sheet of Excel Step 10: Calculate all production and shipping costs with SUMPRODUCT functions
Step 11: Calculate all fixed costs with SUMPRODUCT functions
Step 12: Calculate the total cost
See “Optimal” sheet of Excel: 15 See “Optimal” sheet of Excel Click on Tools/Solver
Fill in the Solver dialog box:
See next slide
Don’t forget to check the Assume Linear Model box under Options
Click on Solve
Solver Dialog Box: 16 Solver Dialog Box
Optimal Solution: 17 Optimal Solution Plants 2, 3, and 5 are operated
Warehouses 2 and 3 are operated
The plant-warehouse shipments are:
P2-W2, P3-W3, P5-W3
All operating plants produce and ship their capacity
The warehouse-customer shipments are:
W2-C1, W3-C2, W3-C3, W3-C4
Summary: 18 Summary This model combined:
Transportation and transshipment analysis
Location analysis
It produced an integrated logistics plan for Huntco
Next step would probably be to start asking “what-if” questions:
Cost variation, etc.