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 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 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  
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
Factory 1 $1.75 $2.50 $1.50 $2.00 $1.50
Factory 2 $2.00 $2.50 $2.50 $1.50 $1.00
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
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
Number of products shipped          
  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
   
  Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Total  
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              
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.      

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.