51448604 Distribution/Logistics Examples
On each example worksheet, read the comments at the bottom of the sheet, then click Tools Solver... to examine the decision variables, constraints, and objective. To find the optimal solution, click the Solve button. An important group of Solver applications is based on distribution or network models. The amount of money that companies save each year by applying linear programming towards their distribution problems is enormous. In this series we will look at a simple transportation problem in worksheet Transport1, then extend it to a 2-level multi-product model in worksheets Transport2 and Transport3. We'll also examine a frequently encountered class of problems called 'knapsack' problems, in worksheet Knapsack. Knapsack. As an example we will look at a truck that that has to transport different kinds of gas. In the Facility worksheet we will look at a facility location problem, where a company has to decide if it's profitable to close down one or more of their plants and save overall costs. Finally, in the Prodtran worksheet, we will examine a combination production and transportation model where the number of products made in plants depends on choices made in distribution. This kind of combination is often possible, but many users prefer to split these models up into smaller ones to simplify the problem.
Page 1
51448604 Transportation Problem 1 Minimize the costs of shipping goods from factories to customers, while not exceeding the supply available from each factory and meeting the demand of each customer. Cost of shipping ($ per product) Destinations
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Factory 1 Factory 2
$1.75 $2.00
$2.25 $2.50
$1.50 $2.50
$2.00 $1.50
$1.50 $1.00
Number of products shipped
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Factory 1 Factory 2 Total Demand
0 0 0 30,000
0 0 0 23,000
Total cost of shipping
0 0 0 15,000
0 0 0 32,000
0 0 0 16,000
Total
Capacity
0 0
60,000 60,000
$
Problem
A company wants to minimize the cost of shipping a product from 2 different factories to 5 different customers. Each factory has a limited supply and each customer a certain demand. How should the company distribute the product? Solution
1) The variables are the number of products to ship from each factory to the customers. These are given the name Products_shipped in worksheet Transport1. 2) The logical constraint is Products_shipped >= 0 via the Assume Non-Negative option The other two constraints are Total_received >= Demand Total_shipped <= Capacity 3) The objective is to minimize cost. This is given the name Total_cost. Remarks
This is a transportation problem in its simplest form. Still, this type of model is widely used to save many housands of dollars each year. In worksheet Transport2 we will consider a 2-level transportation, and in worksheet Transport3 we expand this to a multi-product, 2-level transportation problem.
Page 2
51448604 Transportation Problem 2 (2-stage-transport) Minimize the costs of shipping goods from factories to warehouses and customers, and warehouses to customers, while not exceeding the supply available from each factory or the capacity of each warehouse, and meeting the demand from each customer. Cost of shipping ($ per product) Destinations
Warehouse 1 arehouse 2 arehouse 3 arehouse 4 Factory 1 Factory 2
$0.50 $1.50
$0.50 $0.30
$1.00 $0.50
$0.20 $0.20
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Factory 1 Factory 2
$1.75 $2.00
$2.50 $2.50
$1.50 $2.50
$2.00 $1.50
$1.50 $1.00
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Warehouse Warehouse Warehouse Warehouse
$1.50 $1.00 $1.00 $2.50
$1.50 $0.50 $1.50 $1.50
$0.50 $0.50 $2.00 $0.20
$1.50 $1.00 $2.00 $1.50
$3.00 $0.50 $0.50 $0.50
Number of products shipped
Warehouse 1 arehouse 2 arehouse 3 arehouse 4 Factory 1 Factory 2 Total Capacity
0 45,000 45,000 45,000
20,000 0 20,000 20,000
0 11,000 11,000 30,000
15,000 0 15,000 15,000
Total
35,000 56,000
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Factory 1 Factory 2
10,000 0
0 0
0 0
15,000 0
0 0
Total products shipped out of factory Total products shipped out of factory
Total
25,000 0 60,000 56,000
Factory Capacity
60,000 60,000
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total Warehouse Warehouse Warehouse Warehouse Total Demands
0 20,000 0 0 30,000 30,000
Total cost of shipping
23,000 0 0 0 23,000 23,000
0 0 0 15,000 15,000 15,000
17,000 0 0 0 32,000 32,000
5,000 0 11,000 0 16,000 16,000
45,000 20,000 11,000 15,000
###
Problem
A company has 2 factories, 4 warehouses and 5 customers. It wants to minimize the cost of shipping its product from the factories to the warehouses, the factories to the customers, and the warehouses to the customers. The number of products received by a warehouse from the factory should be the same as the number of products leaving the warehouse to the customers. How should the company distribute the products? Solution
1) The variables are the number of products to ship from the factories to the warehouses, the factories to the
Page 3
51448604 customers, and the warehouses to the customers. These are defined in worksheet Transport2 as Factory_to_warehouse, Factory_to_customer, Warehouse_customer. 2) The logical constraints are all defined via the Assume Non-Negative option: Factory_to_warehouse >= 0 Factory_to_customer >= 0 Warehouse_customer >= 0 The other constraints are Total_from_factory <= Factory_capacity Total_to_customer >= Demand Total_to_warehouse <= Warehouse_capacity Total_to_warehouse = Total_from_warehouse 3) The objective is to minimize cost, given by Total_cost. Remarks
Please note that the last constraint must be an '=' , because otherwise products would start piling up at the warehouse. It would be possible to make this a multi-period model where storage at the warehouses would be possible and even desired, if transportation prices would fluctuate during the different time periods. In worksheet Transport3 we will look at a multi-product situation.
Page 4
51448604 Transportation Problem 3 (2-stage-transport, multi-commodity) Minimize the costs of shipping 3 different goods from factories to warehouses and customers, and warehouses to customers, while not exceeding the supply available from each factory or the capacity of each warehouse, and meeting the demand from each customer. Cost of shipping ($ per product) Destinations
Warehouse 1 arehouse 2 arehouse Fac tory 1
Produc t 1 Product 2 Product 3
Fac tory 2
Produc t 1 Product 2 Product 3
$0.50 $1.00 $0.75 $1.50 $1.25 $1.40
$0.50 $0.75 $1.25 $0.30 $0.80 $0.90
$1.00 $1.25 $1.00 $0.50 $1.00 $0.95
arehouse 4
$0.20 $1.25 $0.80 $0.20 $0.75 $1.10
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Fac tory 1
Produc t 1 Product 2 Product 3
Fac tory 2
Produc t 1 Product 2 Product 3
$2.75 $2.50 $2.90 $3.00 $2.25 $2.45
$3.50 $3.00 $3.00 $3.50 $2.95 $2.75
$2.50 $2.00 $2.25 $3.50 $2.20 $2.35
$3.00 $2.75 $2.80 $2.50 $2.50 $2.85
$2.50 $2.60 $2.35 $2.00 $2.10 $2.45
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3
$1.50 $1.00 $1.25 $1.00 $1.25 $1.10 $1.00 $0.90 $1.25 $2.50 $1.75 $1.50
$0.80 $0.90 $0.70 $0.50 $1.00 $1.10 $1.50 $1.35 $1.20 $1.50 $1.30 $1.10
$0.50 $1.20 $1.10 $0.50 $1.00 $0.90 $2.00 $1.45 $1.75 $0.60 $0.70 $1.50
$1.50 $1.30 $0.80 $1.00 $0.90 $1.40 $2.00 $1.80 $1.70 $1.50 $1.25 $1.10
$3.00 $2.10 $1.60 $0.50 $1.50 $1.75 $0.50 $1.00 $0.85 $0.50 $1.10 $0.90
Number of products shipped
Warehouse 1 arehouse 2 arehouse Fac tory 1
Produc t 1 Product 2 Product 3
Fac tory 2
Produc t 1 Product 2 Product 3
Total
Product 1 Product 2 Product 3
Capacity
Product 1 Product 2 Product 3
200 1,654 366 654 987 1,890 854 2,641 2,256 35,000 30,000 20,000
350 1,322 355 657 822 5,500 1,007 2,144 5,855 20,000 25,000 20,000
300 1,222 312 666 908 5,678 966 2,130 5,990 30,000 15,000 25,000
arehouse 4
456 1,100 301 655 912 5,600 1,111 2,012 5,901 15,000 24,000 20,000
Total
1,306 5,298 1,334 2,632 3,629 18,668
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
Page 5
Total
51448604 Fac tory 1
20,000 25,000 4,326 27,000 12,540 21,309
Produc t 1 Product 2 Product 3
Fac tory 2
Produc t 1 Product 2 Product 3
7,000 7,000 6,788 6,754 6,700 14,567
18,000 5,000 8,765 5,476 11,000 17,654
21,000 15,000 9,608 4,657 7,677 11,765
20,000 31,000 23,456 11,230 9,897 5,633
86,000 83,000 52,943 55,117 47,814 70,928
87,306 88,298 54,277 57,749 51,443 89,596
90,000 100,000 80,000 75,000 65,000 90,000
Capacity Total products shipped out of factor Product 1 Product 2 Product 3 Total products shipped out of factor Product 1 Product 2 Product 3
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3 Warehouse Product 1 Product 2 Product 3 Total
Product 1 Product 2 Product 3
Demands
Produc t 1 Product 2 Product 3
Total cost of shipping
55 0 0 0 0 0 0 0 0 0 0 0 47,055 37,540 25,635 30,000 20,000 25,000
122 0 0 0 0 0 0 0 0 0 0 0 13,876 13,700 21,355 23,000 15,000 22,000
432 0 0 0 0 0 0 0 0 0 0 0 23,908 16,000 26,419 15,000 22,000 16,000
199 0 0 0 0 0 0 0 0 0 0 0 25,856 22,677 21,373 32,000 12,000 20,000
46 0 0 0 0 0 0 0 0 0 0 0 31,276 40,897 29,089 16,000 18,000 25,000
Total
854 0 0 0 0 0 0 0 0 0 0 0
854 2,641 2,256 1,007 2,144 5,855 966 2,130 5,990 1,111 2,012 5,901
###
Problem
This model builds on model Transport2. Again, a company wants to minimize cost of shipping, but this time there are 3 products to distribute. How should the company distribute the products? Solution
The solution to the problem is identical to the one in Transport2. Notice that we have used the 'Insert Name Define' command to extend the model to a multiproduct problem. This way the variables and constraints are still the same as in Transport2. Remarks
Notice that this model delivers the same result as three separate models for the three products. There will be times however, that there are constraints that apply to more than one product. In that case it would not be desirable to have three different models and maybe even impossible. For an extension of this model, where the
Page 6
51448604 number of products made in the factories depends on the demand and distribution rather than being constant, see the worksheet Prodtran in this workbook.
Page 7
51448604 Partial Loading (Knapsack Problem) A fuel truck with 4 compartments needs to supply 3 different types of gas to a customer. When demand is not filled, the company loses $0.25 per gallon that is not delivered. How should the truck be loaded to minimize loss? Truck Specifications Size (gallons
Comp. 1
Comp. 2
Comp. 3
Comp. 4
1200
800
1300
700
Loading of Compartments (1=yes, 0=no) Comp. 1
Comp. 2
Comp. 3
Comp. 4
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
Comp. 1
Comp. 2
Comp. 3
Comp. 4
Total
Demand
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
1800 1500 1000
Gas 1 Gas 2 Gas 3 Total Amount (gallons) Gas 1 Gas 2 Gas 3
Total Loss
Loss
$450.00 $375.00 $250.00 ###
Maximum Amount (gallons) Comp. 1
Comp. 2
Comp. 3
Comp. 4
0 0 0
0 0 0
0 0 0
0 0 0
Gas 1 Gas 2 Gas 3
Problem
A fuel truck needs to supply 3 different kinds of gas to a customer. When demand is not filled the company loses $0.25 per gallon that is not delivered. The truck has 4 separate compartments of different size. How should the truck be loaded to minimize loss? Solution
1) The variables are the decisions to fill the compartments for each type of gas, and the amounts to be put in if the compartment is filled. In worksheet Knapsack, these are given the name Gallons_loaded and Loading_decisions. 2) The logical constraints are Gallons_loaded >= 0 via the Assume Non-Negative option Loading_decisions = binary Since there can only be one kind of gas in any compartment we have Total_decisions <= 1 The size limitations of the truck give Gallons_loaded <= Maximum_gallons We don't want to load more than needed. This gives Total_gallons <= Demand 3) The objective is to minimize the loss. This is given the name Total_loss. Remarks
It is often possible to have different objectives in these types of problems. We might, for instance, want to minimize the wasted space in the truck in this example. Knapsack problems are characterized by a series of 0-1 integer variables with a single capacity constraint. If someone goes camping and his backpack can hold only a certain amount of weight, what items should the camper bring? He should try to optimize the value
Page 8
51448604 of the items while not exceeding the weight allowed by the backpack. There is a wide set of problems that fall into this category.
Page 9
51448604 Facility Location A company currently ships its product from 5 plants to 4 warehouses. It is considering closing one or more plants to reduce cost. What plant(s) should the company close, in order to minimize transportation and fixed costs? Transportation Costs (per 1000 products) Warehouse Warehouse Warehouse Warehouse
Plant 1
Plant 2
Plant 3
Plant 4
Plant 5
$4,000 $2,500 $1,200 $2,200
$2,000 $2,600 $1,800 $2,600
$3,000 $3,400 $2,600 $3,100
$2,500 $3,000 $4,100 $3,700
$4,500 $4,000 $3,000 $3,200
Open/close decision variables Decision
Plant 1
Plant 2
Plant 3
Plant 4
Plant 5
0
0
0
0
0
Number of products to ship (per 1000) Warehouse Warehouse Warehouse Warehouse Total Capacity Distr. Cost Fixed Cost Total Cost
Plant 1
Plant 2
Plant 3
Plant 4
Plant 5
Total
Demand
0 0 0 0 0 0 $ $ $
0 0 0 0 0 0 $ $ $
0 0 0 0 0 0 $ $ $
0 0 0 0 0 0 $ $ $
0 0 0 0 0 0 $ $ $
0 0 0 0
15 18 14 20
$
Problem
A company currently ships products from 5 plants to 4 warehouses. The company is considering the option of closing down one or more plants. This would increase distribution cost but perhaps lower overall cost. What plants, if any, should the company close? Solution
1) The variables are the decisions to open or close the plants, and the number of products that should be shipped from the plants that are open to the warehouses. In worksheet Facility these are given the names Open_or_close and Products_shipped. 2) The logical constraints are Products_shipped >= 0 via the Assume Non-Negative option Open_or_close = binary The products made can not exceed the capacity of the plants and the number shipped should meet the demand. This gives Products_made <= Capacity Total_shipped >= Demand 3) The objective is to minimize cost. This is given the name Total_cost on the worksheet. Remarks
It is often possible to increase the capacity of a plant. This could be worked into the model with additional 0-1 or binary integer variables. The Solver would find out if it would be profitable to extend the capacity of a plant. It could also be interesting to see if it would be profitable to open another warehouse. An example of this can be found, in somewhat modified form, in the capacity planning model in the Finance Examples workbook.
Page 10
51448604 Production Transportation Problem (2-stage-transport, multi-commodity) Minimize the costs of producing 3 different goods, and shipping them from factories to warehouses and customers, and warehouses to customers, while not exceeding the supply available from each factory or the capacity of each warehouse, and meeting the demand from each customer. Cost to make products
Product 1
Product 2
Product 3
Factory 1
$4
$5
$3
Factory 2
$2
$8
$6
Product 1
Product 2
Product 3
Cost
Factory 1
0
0
0
$
Factory 2
0
0
0
$
Total Cost
$
Cost of shipping ($ per product) Destinations
Warehouse 1 Factory 1
Factory 2
Product 1
$0.50
$0.50
$1.00
$0.20
Product 2
$1.00
$0.75
$1.25
$1.25
Product 3
$0.75
$1.25
$1.00
$0.80
Product 1
$1.50
$0.30
$0.50
$0.20
Product 2
$1.25
$0.80
$1.00
$0.75
Product 3
$1.40
$0.90
$0.95
$1.10
Customer 1 Factory 1
Factory 2
arehouse 2 arehouse 3 arehouse 4
Customer 2 Customer 3 Customer 4 Customer 5
Product 1
$2.75
$3.50
$2.50
$3.00
$2.50
Product 2
$2.50
$3.00
$2.00
$2.75
$2.60
Product 3
$2.90
$3.00
$2.25
$2.80
$2.35
Product 1
$3.00
$3.50
$3.50
$2.50
$2.00
Product 2
$2.25
$2.95
$2.20
$2.50
$2.10
Product 3
$2.45
$2.75
$2.35
$2.85
$2.45
Customer 1
Customer 2 Customer 3 Customer 4 Customer 5
Warehouse 1 Product 1
$1.50
$0.80
$0.50
$1.50
$3.00
Product 2
$1.00
$0.90
$1.20
$1.30
$2.10
Product 3
$1.25
$0.70
$1.10
$0.80
$1.60
Warehouse 2 Product 1
$1.00
$0.50
$0.50
$1.00
$0.50
Product 2
$1.25
$1.00
$1.00
$0.90
$1.50
Product 3
$1.10
$1.10
$0.90
$1.40
$1.75
Warehouse 3 Product 1
$1.00
$1.50
$2.00
$2.00
$0.50
Product 2
$0.90
$1.35
$1.45
$1.80
$1.00
Product 3
$1.25
$1.20
$1.75
$1.70
$0.85
Warehouse 4 Product 1
$2.50
$1.50
$0.60
$1.50
$0.50
Product 2
$1.75
$1.30
$0.70
$1.25
$1.10
Product 3
$1.50
$1.10
$1.50
$1.10
$0.90
Number of products shipped
Warehouse 1 Factory 1
Factory 2
arehouse 2 arehouse 3 arehouse
Total
Product 1
0
0
0
0
0
Product 2
0
0
0
0
0
Product 3
0
0
0
0
0
Product 1
0
0
0
0
0
Product 2
0
0
0
0
0
Product 3
0
0
0
0
0
Page 11
51448604 Total
Capacity
Product 1
0
0
Factory 2
0
Product 2
0
0
0
0
Product 3
0
0
0
0
Product 1
35,000
20,000
30,000
15,000
Product 2
30,000
25,000
15,000
24,000
Product 3
20,000
20,000
25,000
20,000
Customer 1 Factory 1
0
Customer 2 Customer 3 Customer 4 Customer 5
Total
Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0
Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0 Capacity
Total products shipped out of factory 1
Total products shipped out of factory 2
Customer 1
Product 1
0
0
Product 2
0
0
Product 3
0
0
Product 1
0
0
Product 2
0
0
Product 3
0
0
Customer 2 Customer 3 Customer 4 Customer 5
Total
Warehouse 1 Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0
Warehouse 2 Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0
Warehouse 3 Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0
Warehouse 4 Product 1
0
0
0
0
0
0
Product 2
0
0
0
0
0
0
Product 3
0
0
0
0
0
0
Total
Product 1 Product 2
Demands
0
0 0
0 0
0 0
0 0
0
Product 3
0
0
0
0
0
Product 1
30,000
23,000
15,000
32,000
16,000
Product 2
20,000
15,000
22,000
12,000
18,000
Product 3
25,000
22,000
16,000
20,000
25,000
Total cost of shipping
$
Total cost of production
$
Total Cost
$
Problem
A company wants to minimize the cost of shipping three different products from factories to warehouses and customers and from warehouses to customers. The p roduction of each product at each plant depends on the distribution. How man products should each factory produce and how should the products be distributed in order to minimize total cost while meeting demand?
Page 12
51448604 Solution
Notice that this is an extension of the transportation model as seen in the Transport3 worksheet. This time the factories not produce a fixed amount. The amounts produced are now variables. 1) The variables are the number of products to make in the factories, the number of products to ship from factories to warehouses, factories to customers, and warehouses to customers. In worksheet Prodtran these are given the names Products_made, Factory_to_warehouse, Factory_to_customer, and Warehouse_to_customer. 2) The logical constraints are all defined via the Assume Non-Negative option: Products_made >= 0 Factory_to_warehouse >= 0 Factory_to_customer >= 0 Warehouse_to_customer >= 0 The other constraints are Total_from_factory <= Factory_capacity Total_to_customer >= Demand Total_to_warehouse <= Warehouse_capacity Total_to_warehouse = Total_from_warehouse 3) The objective is to minimize cost. This is defined in the worksheet as Total_cost. Remarks
This is one of the more complex models in this series of examples. If the number of products, factories and warehouses becomes large, the number of variables in a model like this one becomes very large. Also bear in mind the degree of coordination between business units that may be needed in order to implement the optimal solution. For these reasons some users prefer to split problems like this one into a set of smaller, simpler models.
Page 13
51448604
Page 14
51448604
y
Page 15
51448604 do
,
Page 16
