Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your models.


 

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 Warehouse 2 Warehouse 3 Warehouse 4  
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  
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
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
   
  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 Warehouse 2 Warehouse 3 Warehouse 4 Total  
Factory 1 Product 1 0 0 0 0 0  
  Product 2 0 0 0 0 0  
  Product 3 0 0 0 0 0  
Factory 2 Product 1 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  
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total
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
   
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total
Warehouse 1 Product 1 0 0 0 0 0 0 0
  Product 2 0 0 0 0 0 0 0
  Product 3 0 0 0 0 0 0 0
Warehouse 2 Product 1 0 0 0 0 0 0 0
  Product 2 0 0 0 0 0 0 0
  Product 3 0 0 0 0 0 0 0
Warehouse 3 Product 1 0 0 0 0 0 0 0
  Product 2 0 0 0 0 0 0 0
  Product 3 0 0 0 0 0 0 0
Warehouse 4 Product 1 0 0 0 0 0 0 0
  Product 2 0 0 0 0 0 0 0
  Product 3 0 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  
   
Total cost of shipping $0          
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.        

To Learn More:

For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.
User type
Email address
Name
First Last
Company
University
Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.