distribution examples

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. As an example we will look at a truck 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.

document.xls

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 $1.75 $2.25 $1.50 $2.00 $1.50

Factory 2 $2.00 $2.50 $2.50 $1.50 $1.00

Number of products shipped

Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total Capacity

Factory 1 0 0 0 0 0 0 60,000

Factory 2 0 0 0 0 0 0 60,000

Total 0 0 0 0 0

Demand 30,000 23,000 15,000 32,000 16,000

Total cost of shipping $0

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

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.

thousands of dollars each year.

document.xls

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.


Destinations

Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4

Factory 1 $0.50 $0.50 $1.00 $0.20

Factory 2 $1.50 $0.30 $0.50 $0.20


Factory 1 $1.75 $2.50 $1.50 $2.00 $1.50

Factory 2 $2.00 $2.50 $2.50 $1.50 $1.00


Warehouse 1 $1.50 $1.50 $0.50 $1.50 $3.00

Warehouse 2 $1.00 $0.50 $0.50 $1.00 $0.50

Warehouse 3 $1.00 $1.50 $2.00 $2.00 $0.50

Warehouse 4 $2.50 $1.50 $0.20 $1.50 $0.50


Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4 Total

Factory 1 0 20,000 0 15,000 35,000

Factory 2 45,000 0 11,000 0 56,000

Total 45,000 20,000 11,000 15,000

Capacity 45,000 20,000 30,000 15,000

Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total

Factory 1 10,000 0 0 15,000 0 25,000 Factory

Factory 2 0 0 0 0 0 0 Capacity

Total products shipped out of factory 1 60,000 60,000

Total products shipped out of factory 2 56,000 60,000


Warehouse 1 0 23,000 0 17,000 5,000 45,000

Warehouse 2 20,000 0 0 0 0 20,000

Warehouse 3 0 0 0 0 11,000 11,000

Warehouse 4 0 0 15,000 0 0 15,000

Total 30,000 23,000 15,000 32,000 16,000

Demands 30,000 23,000 15,000 32,000 16,000

Total cost of shipping $237,000

Problem

document.xls

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

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.

document.xls

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



Destinations


Factory 1 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

Factory 2 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


Factory 1 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

Factory 2 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


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


Product 2 $1.25 $1.00 $1.00 $0.90 $1.50

Product 3 $1.10 $1.10 $0.90 $1.40 $1.75


Product 2 $0.90 $1.35 $1.45 $1.80 $1.00

Product 3 $1.25 $1.20 $1.75 $1.70 $0.85


Product 2 $1.75 $1.30 $0.70 $1.25 $1.10

Product 3 $1.50 $1.10 $1.50 $1.10 $0.90



Factory 1 Product 1 0 0 0 0 0

Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0

document.xls

Total Product 1 0 0 0 0

Product 2 0 0 0 0

Product 3 0 0 0 0

Capacity 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


Factory 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

Factory 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

Capacity

Total products shipped out of factory 1 Product 1 0 90,000

Product 2 0 100,000

Product 3 0 80,000

Total products shipped out of factory 2 Product 1 0 75,000

Product 2 0 65,000

Product 3 0 90,000


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


Product 2 0 0 0 0 0 0

Product 3 0 0 0 0 0 0


Product 2 0 0 0 0 0 0

Product 3 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 0 0 0 0 0

Product 2 0 0 0 0 0

Product 3 0 0 0 0 0

Demands 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

document.xls


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

number of products made in the factories depends on the demand and distribution rather than being constant,

see the worksheet Prodtran in this workbook.

document.xls

0

0

0

0

0

0

0

0

0

0

0

0

document.xls

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

Comp. 1 Comp. 2 Comp. 3 Comp. 4

Size (gallons) 1200 800 1300 700

Loading of Compartments (1=yes, 0=no)


Gas 1 0 0 0 0

Gas 2 0 0 0 0

Gas 3 0 0 0 0

Total 0 0 0 0

Amount (gallons)

Comp. 1 Comp. 2 Comp. 3 Comp. 4 Total Demand Loss

Gas 1 0 0 0 0 0 1800 $450.00

Gas 2 0 0 0 0 0 1500 $375.00

Gas 3 0 0 0 0 0 1000 $250.00

Total Loss ###

Maximum Amount (gallons)


Gas 1 0 0 0 0

Gas 2 0 0 0 0

Gas 3 0 0 0 0

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

document.xls

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

of the items while not exceeding the weight allowed by the backpack. There is a wide set of problems that

fall into this category.

document.xls

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)

Plant 1 Plant 2 Plant 3 Plant 4 Plant 5

Warehouse 1 $4,000 $2,000 $3,000 $2,500 $4,500

Warehouse 2 $2,500 $2,600 $3,400 $3,000 $4,000

Warehouse 3 $1,200 $1,800 $2,600 $4,100 $3,000

Warehouse 4 $2,200 $2,600 $3,100 $3,700 $3,200

Open/close decision variables

Plant 1 Plant 2 Plant 3 Plant 4 Plant 5

Decision 0 0 0 0 0

Number of products to ship (per 1000)

Plant 1 Plant 2 Plant 3 Plant 4 Plant 5 Total Demand

Warehouse 1 0 0 0 0 0 0 15

Warehouse 2 0 0 0 0 0 0 18

Warehouse 3 0 0 0 0 0 0 14

Warehouse 4 0 0 0 0 0 0 20

Total 0 0 0 0 0

Capacity 0 0 0 0 0

Distr. Cost $0 $0 $0 $0 $0

Fixed Cost $0 $0 $0 $0 $0

Total Cost $0 $0 $0 $0 $0 $0

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.

document.xls

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.

document.xls

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


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 $0

Factory 2 0 0 0 $0

Total Cost $0


Destinations


Factory 1 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

Factory 2 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


Factory 1 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

Factory 2 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



Product 2 $1.00 $0.90 $1.20 $1.30 $2.10

Product 3 $1.25 $0.70 $1.10 $0.80 $1.60


Product 2 $1.25 $1.00 $1.00 $0.90 $1.50

Product 3 $1.10 $1.10 $0.90 $1.40 $1.75


Product 2 $0.90 $1.35 $1.45 $1.80 $1.00

Product 3 $1.25 $1.20 $1.75 $1.70 $0.85


Product 2 $1.75 $1.30 $0.70 $1.25 $1.10

Product 3 $1.50 $1.10 $1.50 $1.10 $0.90

document.xls




Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0

Total Product 1 0 0 0 0

Product 2 0 0 0 0

Product 3 0 0 0 0

Capacity 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



Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0

Total products shipped out of factory 1 Product 1 0

Product 2 0

Product 3 0

Total products shipped out of factory 2 Product 1 0

Product 2 0

Product 3 0


Warehouse 1 Product 1 0 0 0 0 0

Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0


Product 2 0 0 0 0 0

Product 3 0 0 0 0 0

Total Product 1 0 0 0 0 0

Product 2 0 0 0 0 0

document.xls

Product 3 0 0 0 0 0

Demands 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 production $0

Total Cost $0

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 production of each product at each plant depends on the distribution. How many

products should each factory produce and how should the products be distributed in order to minimize total cost while

meeting demand?

Solution

Notice that this is an extension of the transportation model as seen in the Transport3 worksheet. This time the factories do

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.

document.xls

Total

0

0

0

0

0

0

Capacity

0

0

0

0

0

0

Total

0

0

0

0

0

0

0

0

0

0

0

0

document.xls

A company wants to minimize the cost of shipping three different products from factories to warehouses and customers

and from warehouses to customers. The production of each product at each plant depends on the distribution. How many

products should each factory produce and how should the products be distributed in order to minimize total cost while

Notice that this is an extension of the transportation model as seen in the Transport3 worksheet. This time the factories do

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

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,

distribution examples

Documents

worksheet transport1

problem

cost

company

distribution

shipping

product

customer