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 | 25 | 28.28427 | $713 | 5500 | ||||

Product 2 | 25 | 40.31129 | $900 | 10625 | ||||

Product 3 | 25 | 36.51484 | $1,175 | 15750 | ||||

Product 4 | 25 | 31.62278 | $488 | 11875 | ||||

Total | $3,275 | 43750 | ||||||

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. | ||||||||