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.