Go to MIS 530 Home Page
Module 6


Careful inventory management is critical to the financial health of businesses whose primary venture is manufacturing or retailing. In retail and manufacturing companies, huge amounts of time and $'s are expended in keeping and managing inventory. Many of the techniques that we have applied to tables, graphs, and what-if analysis also apply to inventory analysis. We need to understand the situation, develop a spreadsheet model, then apply the results of the model to the decision making process. Let's jump right in with some of the basic concepts involved in inventory management.
We will build an inventory model in order to answer two basic questions: How much do we order? And when? - with the goal of minimizing the total inventory costs. In the most basic of all inventory models we are going to make several important assumptions in order to keep the model simple. Then we will extend the basic model by relaxing some of the assumptions and develop slightly more realistic models.

  • Only one item is considered.
  • An entire order arrives at once.
  • No shortages are allowed.
  • The demand is known and constant over time.
  • The time value of money is zero.
  • Price for items is not a function of order quantity.
  • Lead-time is known and constant.
  • Once again I remind you that the analysis we are about to undertake only works when these assumptions are in effect!

    There are three basic types of inventory - raw materials, work-in-process, finished good. Our analysis may apply to any of the three with minor variations. The principle remains the same.

    Another way to differentiate inventory is by its use. There are four basic uses - cycle inventory, safety stock, anticipation inventory, and in-transit inventory.

    You may have also heard about independent and dependent demand inventories. We will work with independent demand in our analysis. Independent demand is most frequently associated with finished goods where the demand is more or less unknown. Dependent demand inventory is associated with MRP systems and bills of materials.

    The costs associated with inventory fall into two broad categories or components. All the costs associated with carrying too much inventory can be "lumped" together; we will call this the holding cost component. All the costs associated with carrying too little inventory can also be "lumped" together; we will call this the ordering cost component. Some costs which may be included in the holding cost component are:

  • opportunity cost
  • taxes
  • insurance
  • storage
  • shrinkage
  • Some costs which may be included in the ordering cost component are:
  • ordering costs
  • set-up costs
  • transportation costs
  • small lot costs
  • stockouts and backorders
  • We will first build a mathematical (analytical) model. Following typical conventions, we will need some descriptions and shorthand notation for variables used in the model.

    Order quantity (Q) - Number of units ordered, also called the lot or batch size.

    Demand (D) - Usually the annual demand. You may need to convert available information to annualized data.

    Item Cost (C) - Purchase price of raw materials or value of finished goods or WIP.

    Carrying charge rate (i) - Composite % or decimal fraction of the item's cost that reflects the cost of keeping one unit in inventory for one year. Usually, but not always, expressed in % per unit per year.

    Holding Cost per unit (H) - Cost, in dollars, to keep one unit in inventory for one year, H = i x C. Make sure you don't confuse this with the holding cost component.

    Ordering Cost (Co) - Cost to place one order, not to be confused with the Ordering Cost Component. Frequently, this is designated S, the set-up cost, when dealing with WIP or finished goods inventory.

    Lead Time (LT) - The time that elapses between placing an order and receipt of that order.

    ReOrder Point (ROP) - The on-hand inventory levelat which we should place the order for the next batch.

    Stockout Cost (S)- The cost incurred when there is insufficient inventory.


    EOQ Model

    Given the above we can develop the basic Economic Order Quantity (EOQ) Model. This classic model will give us the most economical order size That is, it will tell us how much to order with a goal of minimizing the Total Annual Inventory Cost (TAIC). First, let's look at how to determine the TAIC.

    TAIC = costs associate with having more inventory than we need + costs associated with having less inventory than we need.

    In business lingo:
    TAIC = holding cost component + ordering cost component.

    Holding Cost Component = Amount of inventory you hold, on the average, for a year x cost to hold one item per year =

    Ordering Cost Component = Number of orders you place in a year x cost to place an order =


    With this expression we can find the total cost for various order quantities (Q), but we still don't know how much or when to order to achieve our objective of minimizing the total (annual) inventory cost. The values for all the variables are fixed and known, except for Q. Q is the decision variable. We can choose any amount to order (choose any value for Q) but it might be advantageous to select a value that minimizes our costs. To state it another way, we can find the total inventory cost (TAIC) for any and all possible values of Q by using the above mathematical expression, but we want to find the Q at which the TAIC is the smallest.

    An Example EOQ Problem

    Let's apply what we just learned to a simple situation. As assistant manager of a soccer specialty store you have been asked to review the inventory costs associated with the store's best selling soccer shoe. The information you have available concerning this shoe follows:

    • Demand = 18 pairs per week
    • Ordering cost = $32 per order
    • Cost of a pair = $90
    • Carrying charge rate = 10%
    • The store operates 50 weeks per year.
    The store currently places an order every two months for 150 pairs. (Does this meet the annual demand? What is the annual demand?) What is the total annual inventory cost (TAIC) with the current ordering policy?

    D = 18 pairs per week so the annual demand, DA = 900 pairs
    Co = $32
    C = $90
    i = 10% or 0.10
    H = 10% x $90 = $9
    Q = 150 pairs per order

    Substituting, TAIC = (Q/2)iC + (DA/Q)Co = (150/2)(.10)($90) + (900/150)($32) = $675 + $192 = $867

    So the total annual inventory cost is currently $867. Is there a better policy? Let's make a table that looks at the costs associated with several values of Q. The complete table and graph are shown below.

    EOQ Calculations

    What is the best ordering policy? There is a way to find the EOQ mathematically. Notice that the TAIC line in the graph is a curve and recall from calculus that you can find the minimum or maximum point on a curve by taking the first derivative of TAIC with respect to Q and setting it equal to zero. (Yeah! Right! OK!)

    If you are able to do the above you get:

    This is one of the basic and most important relationships in inventory analysis. Try it on the problem above and you should get EOQ = 80 which we already determined from the table and graph. Keep in mind that this equation only works for the basic inventory problems under the assumptions that we have discussed.

    The next decision we need to make is when to place an order for more items to insure that we don't run out. Generally, a shipment does not arrive instantaneously when you place an order; there is some Lead Time associated. Lead Time is the time that elapses from the moment we place an order until the order is received in our on-hand inventory. The inventory system we will discuss here is called a continuous review system, which is one type of independent demand inventory system. We continuously review the inventory level and, when it reaches some level called the Reorder Point (ROP), we place an order (for Q* if we wish to minimize cost, but it doesn't have to be) so that the order will arrive just as our inventory level reaches zero. (Under the basic assumptions.)

    To determine the reorder point, multiply the demand x the lead time making sure they are both in the same time units. In the soccer shoe example, suppose the lead time is 2 weeks. For example, 900 items per year is equivalent to 900/50 = 18 items per week. So the ROP = 2 weeks x 18 items/week = 36 items. When the inventory level gets down to 36, place an order and 2 weeks later the order will arrive just as our inventory reaches zero. Note that the ROP is an amount in inventory measured in number of items.

    It is fairly straightforward to plug the appropriate soccer shoe numbers into the EOQ formula to find that the EOQ = 80 pairs of shoes and the TAIC = $720 if we order batch sizes of 80. These results are confirmed by looking at the table and graph we constructed for this example.

    Inventory Exercise

    A pet store operates 52 weeks a year, 7 days per week and uses a continuous review inventory system for a particular brand and size of dog food. A bag of this food costs $11.70.

    The following additional information is available:

  • Demand = 90 bags per week
  • Ordering cost = $54 per order
  • Carrying charge rate = 27%
  • Lead time = 2 weeks
  • You should be about to find the answers provided in parentheses.
    What is the EOQ? (400 bags)
    What is the total inventory cost if EOQ is used? ($1263.60)
    If EOQ is used, how many orders should be placed per year? (11.7 orders per year, don't round)
    How many weeks are there between orders? Days? (4.44 wks; 31.2 days)
    What is the reorder point? (180 bags)
    The store is currently ordering 500 bags at a time. How much will they save if they use EOQ? ($31.59)

    For both the soccer shoe example and the pet food exercise, we could find all the information we needed without conducting any spreadsheet analysis. So why do we need spreadsheets for inventory analysis? It has been stressed that the mathematical analysis for EOQ can be used only if our basic assumptions remain in effect. As the problems get more complicated, the spreadsheet approach must be used to find EOQ and related decisions, since the basic EOQ formula will no longer apply.

    Safety Stock Model

    If we relax the demand assumption, that is, our demand is no longer known or constant, we can use a spreadsheet to construct and analyze what is called a Safety Stock model.

    In the Safety Stock model, we need to revisit the ROP. You can think of the reorder point in terms of demand also. The amount of inventory at which you place an order is the amount of inventory you need, on average, to get through the lead time period. It could be considered the Average Demand During Lead Time (ADDLT). In the Safety Stock model, the actual forthcoming demand during future lead-time periods is unknown, but we can use the average demand from previous lead time periods to find a historical average. Using this historical data we can find both the ADDLT and the standard deviation of the demand during lead time (SDDDLT). We will assume that the inventory follows the normal distribution or bell curve. Click here for a brief review of the normal distribution, means, and standard deviations.

    We may still use the EOQ model to calculate the order quantity but with varying demand. Do you see that if we used the ROP as calculated in the basic EOQ model, the firm would run out of inventory before the next order arrived 50% of the time. To lessen the chances of running out, the firm maintains safety stock. Now the decision becomes to find the best balance between some customer service level (having inventory in stock when asked) and the costs of carrying extra inventory.

    Safety stock model exercise

    For our example let's arbitrarily choose some desirable level of customer service, say 95%, which means that we will run out of inventory before the next order arrives only 5% of the time. Then, we compute the reorder point based on those calculations and recalculate the TAIC which will necessarily include stockout costs. These calculations are somewhat more involved as they require using some statistical techniques.

    Look at the spreadsheet below.

    All of the information in rows 1 through 14 is given, presumably derived from historical data.. ADDLT is calculated from the lead time and the average weekly demand. The standard deviation of Demand During Lead Time is computed by

    (Standard deviation of weekly demand) x (square root of the lead time)

    I used the =round function to get whole numbers. (Note: If you don't round, or use another rounding function and/or parameters, your numbers will be slightly different. Slight differences can compound themselves as you complete more and more arithmetic operations. The bottom line is, don't be too concerned if your values are not exactly the same as the ones in the table. I'm guessing that they should be within 5% however.)

    For the basic EOQ model the Reorder Point is the same as the ADDLT and TAIC is calculated using the basic TAIC expression.

    Calculations for the Safety Stock Model become a bit more complicated. To find the Reorder point I used the =norminv(probability, mean, std. dev.) function. If I set the probability to .95, mean = ADDLT, and std. dev. to SDDDLT, this results in a value of 246. The meaning is that, assuming the distribution of demand during lead time is normal (with a mean of 200 and a std. dev. of 28), 95% of the time, the actual demand during lead time will be less than 246 units. Then calculate the safety stock and resultant (EOQ model) TAIC. (Hint: The average amount of inventory on hand at any one time is Safety Stock + Q/2. Do you see why?)

    But 5% of the time we are going to run out of inventory before the next shipment arrives. There are costs (customer satifaction, lost sales, etc.) and they need to be included in our calculations. The Stockout Cost Factor (B5) is a per unit factor but we need to calculate the number of units, on average, that we will not have, i.e., the expected number of stockouts. To find this number use:

    No. of orders per year x SDDDLT x appropriate constant in table at B44:C50

    The factors in the table were the result of research published in the 1960's and can be found in many operations management texts and reference books. The mathematical derivation is far beyond our interest (probably).

    To get the appropriate proportion factor from the table I used the =vlookup function, looking up the factor associated with the percentage in cell B6. Use the expected number of stockouts to find the final total costs.

    Putting the model into a decision-making context, we might want to compare the costs for different levels of customer service. I built the table (rows 35-41) using the Data-Table menu option. This is an excellent example of how useful it cna be.

    Mathematics for some of the values for item RJ10050

    SDDDLT: 20 * sqrt(2) = 28

    Safety Stock Reorder Point: =norminv(.95,200,28) = 246

    Expected no. of stockouts: (5000/167)*28*.0209 = 17.52 => 18
    Total SO Cost: 18*(10%*$1500) = $2700


    For a maximum grade of B-

    1. Construct the spreadsheet and graph for the pet food example.

    For a maximum grade of B

    1. Complete the B- level assignment.

    2. Construct the Safety Stock Model without the "Cost as a function " section.

    For a maximum grade of B+

    1. Complete the B level assignment.

    2. Complete the Safety Stock Model spreadsheet then write a short "business report" with your recommendations with supporting charts, graphs, cost analysis figures, etc. Some graph(s) should show the costs over a range of service levels.

    For a maximum grade of A-

    1. Complete the B+ level assignment.

    2. For each item in the analysis the inventory management policy is based on the forecasted weekly demand. Unfortunately, forecasted demands are frequently incorrect. Extend your analysis of the safety stock model for item no. RJ10050 to include a sensitivity analysis of the forecasted weekly demands. That is, if the weekly demand changes how does this affect costs, assuming we do not change our ordering policy. You will need to examine the changes, in steps, over a wide range, say from a decrease of 50% to an increase of 100%. For different demands, track the cost changes, construct appropriate tables and graphs, and make recommendations.


    For a maximum grade of A

    1. Complete the A- level assignment.

    2. As general manager of a Pennsylvania-based company that manufactures small tractors equipped with front-end loaders, you want to make changes in your ordering policy due to recent developments in the "bucket" market. (The bucket is the scoop part of the loader.) You are concerned about inventory costs associated with your production line, which uses 5 buckets each day (20 working days per month). You have found it necessary to change your bucket supplier. The only supplier that meets your needs is located in Canada and has a different pricing structure than the one you have used previously. The following information is available.

      Total annual usage of buckets - 1200 units
      Inventory holding cost - $50 per unit per year
      Weight per unit - 500 lbs
      Ordering cost - $25 per order
      Item costs (f.o.b. - freight on board in Minnesota)
      First 100 units in a single order - $500/unit
      Second 100 units in a single order - $490/unit
      All over 200 units in a single order - $480/unit
      Transportation costs - $6/cwt (cwt=100 lbs) for a full truckload of 50,000 lbs (100 buckets). If a shipment is less that a full truckload (LTL) the cost is $10/cwt or $3000, whichever is less. A single order may use more than one truck.

      Construct appropriate tables and graphs and use them to recommend an EOQ that minimizes the total cost.

      Some hints: It is handy to have 7 columns: Q, No. of orders per year, ordering costs, item costs, holding costs, transportation costs, TAIC. TAIC should be the sum of ordering, item, holding, and transportation costs. My lowest cost was $631,075, but it was not the lowest by very much. Work with fractional values for orders/year. I incremented Q by 20 units from 20 to 800 units.

      This may be quite a challenging exercise for some students, especially the item and transportation costs. For maximum credit try to develop one expression for the transportation cost that you can copy down the entire column. You can do the same with item costs but it gets unwieldy with the =IF statement. I used =INT, =MIN, and =MOD in my transportation expression, but there are other ways, possibly more concise or efficient.
    Top of page