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.
Once again I remind you that the analysis we are about to undertake only
works when these assumptions are in effect!
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:
Some costs which may be included in the ordering cost component are:
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
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.
In business lingo:
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
D = 18 pairs per week so the annual demand, DA
= 900 pairs
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.
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!)
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.
The following additional information is available:
You should be about to find the answers provided in parentheses.
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
For a maximum grade of B-
For a maximum grade of B
For a maximum grade of A