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 1 28.28427125 $10,013 220
Product 2 1 40.31128874 $16,260 425
Product 3 1 36.51483717 $20,015 630
Product 4 1 31.6227766 $7,508 475
Total $53,795 1750
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.
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.