Spreadsheet Models for Managers


Getting Access to Spreadsheet Models for Managers


If Spreadsheet Models for Managersyou use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

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:

Equation 1

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

Assuming Constant Demand

Although the assumption of constant demand is critical to justifying the derivation of the formula for Economic Order Quantity, most problems don’t satisfy that requirement in the strict sense. But EOQ is nevertheless a valuable concept in two kinds of circumstances. The first case is when the time scale of the inventory management decisions is much shorter than the time scale of the variations in demand. And the second is when the fluctuations in demand occur much more rapidly than the inventory management decisions.

These two approximations occur repeatedly in modeling problems. Watch for opportunities to apply them elsewhere.