Spreadsheet Models for Managers

Demonstrations for Session 11
Inventory Modeling

We have one demonstration (2007+) for this session: Computing the Economic Order Quantity (EOQ)

Computing the Economic Order Quantity (EOQ) (2007+)
[Sheet: Example]

(a)

You’re a buyer of dyes for an apparel manufacturer. The annual demand for dyes of all colors is 64,000 liters. The price is $10 per liter. The cost to hold an item in inventory is 20% of the value of the item per year. It costs $10 to place an order (you use email). Compute the EOQ. Make plots showing the two components of total cost as functions of order quantity.

Begin by creating a parameter block to hold all the data above. Make named cells to hold UnitPrice, CostToHold, CostToOrder, and AnnualDemand. From these we can construct two additional parameters, the two coefficients that multiply powers of Q in the total cost expression:

Call Coeff1 the coefficient of the first term, and Coeff2 the coefficient of the second. We’ll use these below.

For now, though, we are to compute the EOQ. Using formula 10 from the readings, or from the class notes (PowerPoint 2007+), we compute the result in a cell labeled EOQ, directly below the parameter block.

Next we use Coeff1 and Coeff2 to make a table showing the two components of total cost, as functions of Q, the order quantity. From that table, we use build a line graph showing the total cost, and its two components, as functions of order quantity. Note that the EOQ is in fact the low point of total cost.

(b)

You expect volume to pick up next year, so you negotiated a quantity discount. If you buy more than 1000 liters at a time, you can get a price of $8 per liter. If you buy more than 3000 liters at a time, the price is only $7 per liter. Now what is the optimum order quantity?

This situation is a little trickier. Our approach is to compute the total costs of the operation, including the cost of ordering, the cost of carrying, and the cost of buying the inventory. In the constant-price scenario, as in part (a), we don’t have to include this third cost source, because it doesn’t depend on Q. But when the cost of acquiring the inventory itself depends on Q, we must include its effects in determining the optimal Q. We’ll do that below.

First, though, a little exploration to illustrate a technique. Let’s find the EOQs for each of the three prices, and then check to see if any of them lie in the permitted quantity range for that price. This is an interesting thing to do — they might not!

We create a table showing the Price (P), the break point for that price (Break), and the EOQ for that price. For each price, we compute QMin, the minimum of the EOQ and the Break. If QMin is below the Break, then we can’t ever purchase inventory at that price in quantities equal to the EOQ. We see from the table that only the highest price supports the EOQ. But all is not lost. We can still include the effects of the price breaks. That’s done in the next table, below.

To the two cost factors we already considered, we add a third that takes the actual price into account, and in the last column, we compute the total. We see that the minimum occurs at Q = 3000.

The costs of ordering and carrying are plotted using Excel’s charting facility. Note that they produce a different minimum. That’s because they do not include the cost of the inventory itself.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT