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.

Inventory Policy 1

We're Here to Help