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.


 

Inventory Policy 1
What is the best ordering policy for a warehouse to minimize cost, while meeting demands?
The warehouse has a limited storage capacity of 50000 cubic meters (m3).  
  Holding Cost Storage Space per unit (m3) Demand per month Ordering cost per order   Storage space available (m3)  
Product 1 $25 440 200 $50 50000  
Product 2 $20 850 325 $50  
Product 3 $30 1260 400 $50  
Product 4 $15 950 150 $50      
Quantity to order each month          
  EOQ Cost Space used (m3)
Product 1 10 28.28427 $1,125 2200  
Product 2 10 40.31129 $1,725 4250  
Product 3 10 36.51484 $2,150 6300  
Product 4 10 31.62278 $825 4750  
      Total $5,825   17500  
Problem                
A warehouse sells 4 products with a different demand for each product. Each product has a different holding cost
and requires a certain amount of space. What should the ordering policy for the warehouse be, given its limited
storage capacity?  
   
Solution  
There is an analytical solution for this problem, which is known as the Economic Order Quantity (EOQ) and is
given by the following formula: q = SQRT(2 k d/h), where q is the quantity to order, k is the cost to place an order,
d is the demand and h is the holding cost of the product. Unfortunately, this formula doesn't always work in the real
world. Demand usually fluctuates, ordering time is variable, and other factors arise to further complicate the
problem. In this model we have one such factor, a limited storage space.  
   
1) The variables are the amounts to order each month for each product. These are defined as Quantities in this
worksheet. By changing these variables we change the total cost.  
2) The constraints are very simple. We have a logical constraint and the storage capacity constraint. This gives
  Quantities >= 0 via the Assume Non-Negative option  
  Space_used <= Available_space  
If the latter constraint wasn't present, the solution to the problem could be calculated by the formula given above.
3) The objective is to minimize the total cost, which is defined as Total_cost. It is calculated by adding the
individual costs for each product. Those costs are calculated by using the formula:  
Cost = h q /2 + k d /q, where h, q, k and d are as above.  
This formula is easy to understand if we realize that the average inventory level is q/2 and the average number of
orders is d/q.  
   
Remarks  
In this worksheet we have also calculated the EOQ with the formula given above. Check to see that when you
increase the storage capacity and thus relax that constraint, the answers found by the Solver will approach the
analytic solution.  
   
This model is an example of a non-linear problem, as can easily be seen by looking at the cost formula. Whereas in
linear problems it does not matter what are starting values for the variables are, it can be very important to have
reasonable starting values in non-linear problems. In this model it is not possible to start with a quantity of 0, since
this would cause an error in the calculation of the cost.  
   
Please see for yourself that the Solver will still find the correct answer, even when the starting values are close
(but not equal to) zero.              

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.