Inventory Management Review

EOQ Formula Derivations

In a recent article, I detailed how to find the economic order quantities for a firm.  This post will pick up where I left and will show how to find how much money you can actually save by using correct ordering costs instead of inflated costs.  The post will then cover how much money a firm can save if it can truly reduce its ordering costs.  Today's post will conclude with a formula you can use in excel.

True savings from accurate ordering costs
First, we need to determine the difference in order sizes EOQ gives us when we use the correct order costs instead of artificially high ordering costs.  The difference from the example in the previous post is 876 parts.  When looked at as average inventory for a steady production, this results in average inventory that is 867/2 which is 433.5 additional inventories per year.

It is half of 867 because average inventory for a steady production is half of the order quantity.  Intuitively, this should make sense because if you start out with 876 parts and are constantly using them until you order more, then half the time you have more than half of 876 and half the time you have less.  At a steady rate, half of 876 is the average increase in inventory held.

Effect of incorrect ordering costs on EOQ
Something important to notice is that the difference in parts ordered is not the ratio of the wrong holding cost to the right holding cost. That is to say that although the ordering costs used before were almost twice as high as the ordering costs used now, the difference in parts ordered before is not half of what it is now.  This is because of the square root, which actually makes the difference in parts the ratio of the square root of each ordering cost.

In other words, take the square root of the old ordering costs:
SQRT($82)=9.0554
And the new, correct, ordering costs:
SQRT($42)=6.4807

Find the ratio between the square root of the two ordering costs:
9.0554/6.4807=1.3972

This ratio is the ratio between the old EOQ and the new EOQ:
3053/2185=1.3972

This is useful because you can take the old ordering costs and the new ordering costs to determine the percent difference in ordering sizes.  In other words, you can see the reduction in order sizes by percentage as a result of realizing the correct ordering costs.

Percent difference:
(observed-expected)/expected
In our case that would be:
{[SQRT(New S)]-[SQRT(Old S)]}/[SQRT(Old S)]
Or:
{[SQRT($42)]-[SQRT($82)]}/SQRT($82)=-28%

This formula shows us that the new ordering costs compared to the old ordering costs represent a 28% reduction in order sizes.

Monetary impact of holding extra inventory
When ordering costs are artificially high, so are inventories.  With these inventories comes an increase in holding costs.  The additional holding costs incurred are equal to the excess inventory multiplied by the annual holding cost per part.  Remember, the excess inventory is half the excess order size.  In this example, the excess inventory is 433.5 units and the holding cost is $2.64 per unit per year (determined in previous article).

433.5*$2.64=$1146 spent on excess inventory per year.

Total Savings
The total savings, however, are actually less than that. Remember that with smaller batch sizes, orders are placed more frequently.  We were placing 49 orders per year, now we are doing 68.  This means 19 additional orders (68-49) at a rate of $42 each.  This means that the total savings in EOQ is equal to the $1146 saved from inventory control minus the additional amount paid to floor managers for processing the additional orders [(68-49)*$42].

$1146-{(68-49)*$42}=$347.65 of total money saved through correct ordering costs.

Now that we've determined the overall dollar amount in cost savings, we should determine the overall cost savings as a percent decrease of the old system.  The formula to determine this is:

[(New total cost)-(old total cost)]/(old total cost)

But first, we need to find the total cost of holding and ordering inventory.

Total cost old system=(new ordering cost*number of orders)+[(H)*(Old EOQ/2)]

The new ordering cost is used because it has always been the correct ordering cost but it was only recently realized.  It was still the true cost under the old system regardless of whether or not it was realized.

Plugging in the numbers the equation is as follows:
($42*49)+[($2.64)*(3052/2)]=$6093

Formula for the total cost of the new system is the same, but with all new number of orders and new EOQ.

Total cost of new system=($42*68)+[($2.64)*(2184/20]=$5767

Now we can take these two numbers and plug them into our percent savings formula from above:

(5767-6093)/6093=-5.34%

This means that by realizing what the true ordering costs are, Chuck Co. is able to save 5.34% on the combined values of ordering and holding inventory.

Now we'll see what were to happen if they were not only able to realize that their ordering costs were wrong, but if they were actually able to reduce ordering costs.

Reducing ordering costs through improvement
If Chuck Co. were able to actually able to reduce ordering costs, instead of merely realizing that they weren't spending as much on ordering costs than they thought they were, then Chuck Co. would be able to save some serious money.

Before, when lower ordering costs were just a discovery, they were really only saving money on holding less inventory. In fact, once they started ordering more, they spent more money on orders because it was the same ordering cost with more frequent orders.  If, however, they actually reduced inventory, then they would be able to have actual savings in both ordering and inventory holding costs.

Let's assume that ordering costs really were $82 before and they changed their methods to drop them to $42.  What would the affect be?  All of the numbers, old and new, (EOQ, ordering costs, holding costs) would be the same from our previous example.  This means we're ready to find the total cost of each system and then find the percent savings using the percent savings formula.

Total cost of old system=($82*49)+[($2.64)*(3052/2)]=$8058

Notice that the old ordering costs are used instead of the new ordering costs.  This is because the old system really did have an $82 ordering cost in this example, not like before where Chuck Co. thought it was $82 but it was really $42,  until they actually lowered it, which we'll see here:

Total cost of new system=($42*68)+[($2.64)*(2184/2)]=$5767

We then take the two costs of these systems to find the percent savings in lower ordering costs:

($5767-$8058)/$8058=-28.4%

This means that an actual reduction of ordering costs resulted in 28% savings in ordering and holding costs.

Finally, you may be aware that if ordering cost is the only term changing in the EOQ formula, then there must be some way to determine total cost savings with a formula derived from EOQ using only the variable S (ordering costs).  Well, you're right, there is a formula, and lucky for you, I spend the time to derive it.  Here it is, but first let's get familiar with the variables involved:

%TC= Percent change in total cost
Sn=New ordering cost
So=Old Ordering cost
And, as always, SQRT=Square Root

%TC=[(SQRT(Sn/2)+((1/SQRT(Sn))*Sn))-
(SQRT(So/2)+((1/SQRT(So))*So))] /
[(SQRT(So/2)+((1/SQRT(So))*So))]

If you plug in the numbers we used here:
Sn=$42
So=$82
The formula will look like such:

((SQRT($42/2)+((1/SQRT($42))*$42))-
(SQRT($$82/2)+((1/SQRT($82))*$82)))/
((SQRT($82/2)+((1/SQRT(So))*$82))) = 28.4%

This is the same reduction is total costs that we saw when we plugged in the total cost savings numbers from before.  This time, we only looked at ordering costs.  This shows that this formula can be used to determine the total cost savings EOQ can bring you when you are able to actually reduce ordering costs and when you only know your ordering costs.  In other words, regardless of holding costs and demand, you can determine how much money you can save by simply lowering ordering costs.

To use this formula for yourself, copy and paste the following into cell A1 in excel (make sure the cell has been double clicked):

=((SQRT(C5/2)+((1/SQRT(C5))*C5))-
(SQRT(B5/2)+((1/SQRT(B5))*B5)))/
((SQRT(B5/2)+((1/SQRT(B5))*B5)))

Now, make cell c5 your new ordering cost and cell b5 your old ordering cost.  Make sure cell A1 is in percent format and there you have your reduction in total costs.

Advanced Economic Order Quanitity

In previous articles I've referenced the Economic Order Quantity (EOQ).  This article is going to be the first of a few articles detailing various aspects of EOQ.  This first post will discuss the basics and go step-by-step through an example of how to use EOQ when trying to determine how much to order for a single good that has a known projected demand.

First of all, what is EOQ?
EOQ is a mathematical formula designed to minimize the combination of annual holding costs and ordering costs.  There is a lot of hype about just in time inventory systems (JIT), which achieve smaller inventories through very frequent orders, but frequent ordering can often result in an over-spending on ordering costs.  Even though companies often miscalculate their ordering costs, which makes frequent ordering seem costly, EOQ is an important tool for determining what inventory should be.  Let's move on to the example to help explain what EOQ is.

Chuck Co. is a firm that manufactures toys requiring a part that costs $12, and can be received from multiple suppliers.  The firm, which recently began ordering once a week instead of twice a month, in order to reduce inventory, wants to know how much it should order at a time because it has noticed that while their holding costs have decreased, they seem to be spending more on overtime for floor managers than they used to.  While Chuck Co.'s inventory manager is happy, the plant manager is not sure the reduction in holding costs is worth the overtime pay.  Here's the data they've provided us with:

Demand (variable D)
Annually, the part for the toy is consumed at a rate of 150,000 per year.  While there is seasonality in the toy industry, this firm produces at a level rate because of union agreements.

Ordering Costs (variable S)
Chuck Co. has identified 2 major costs associated with ordering; floor manager overtime required and plant manager time.

The floor managers find themselves with very little time to process orders during their shift.  When an order needs to be made, a floor manager from the day shift needs to work 2 hours of overtime to shop the multiple suppliers and place the order. Overtime pay is $21/hour.

The plant manager spends 1 hour per order to approve the order, determine the tax implications of the order and authorize payments. Earning $80,000 per year and working 2000 hours per year, the plant manager at Chuck Co. earns $40 in the 60 minutes he requires to process the order.

Total, the ordering cost is $82 per order.

Holding Costs (variable H)
When I wrote about Holding Costs, I mentioned the different factors that drove holding costs.  For Chuck Co. the most major factor is opportunity cost.  Another toy in their product line is currently earning 20% a year for every dollar invested in it.  Chuck Co. would like to invest more into the product line but their credit rating and unhappy investors are currently preventing this from happening.  Each dollar in inventory is another dollar that could be in the 20% gain product line.

The opportunity cost for every dollar invested in inventory is the 20% that could be invested in the other toy, plus an additional 2% from rent and other various holding costs.  Ultimately, the holding cost is 22% annually.  Multiply this by the cost of the part ($12) and the holding cost is $2.64 annually.

EOQ
Using the information presented above, the EOQ formula can be used to determine the optimal order.

The formula is:
EOQ=SQRT{(2DS)/(H))}

Plugging in the numbers given from Chuck Co. we get:
EOQ=SQRT{(2*150,000*$82)/($2.64)}=3053

According to these calculations, the most efficient amount ordered is 3502 per order.  Spread out over the annual demand of 150,000 per year, the part should be ordered 49 times per year (150,000/3053).  Seemingly, leading the once per week reorder calculation to be roughly correct, however, this calculation has an error in it.

Common Misconceptions Regarding Ordering Costs
Chuck Co. identified 2 major costs associated with ordering.  Only one of them however is actually driven by the amount of orders placed.  When using EOQ to minimize ordering costs, only costs that can actually be minimized should be taken into consideration.

Specifically, only the overtime hours in our example are true ordering costs. The plant manager definitely spends time ordering and he is getting a salary during those hours, but this salary is a part of his overall duties as the plant manager.  If ordering frequency went down by 10%, it is unlikely that his hours and salary would be scaled back.  His salary is a sunk cost and must be treated accordingly.

The floor managers' hours, however, actually do go up and down in accordance with the number of orders placed.  Each order they place, they receive $42 of overtime compensation for.  Thus, $42 is our true ordering cost. Let's take a look at how this affects our calculations:

Correct EOQ=SQRT{(2*150,000*$42)/($2.64)}=2185

This order size leads to 68 orders per year (150,000/2185), making the old calculation, and the once per week practice wrong.

EOQ can be a very effective tool for helping to optimize inventory.  However, in order for it to be effective, it requires good and thoughtful data.  This means having decent demand projections, well-evaluated holding and ordering costs.  The next post will discuss how much money this correct order size actually saves Chuck Co. and will cover some derivations of the EOQ formula.

Cycle Times and Bottlenecks

In order to figure out how much inventory you need, you should really know your capacity.  This post is going to go step by step and cover in detail the difference between throughput, cycle times, and bottlenecks.  The example we'll look at is going to be a handbag manufacturer.

The following steps, with their corresponding times, are required to produce a handbag:

Process                                  Process Time

Leather cutting                                 120 seconds

Zipper attachement                            70 seconds

Sewing                                               30 seconds

Throughput time
The times that are shown are the processing times.  They represent how long it takes an individual bag to physically pass through each step.

The throughput time is the total time it takes for a bag to physically pass through all steps.  It is the sum of all the times and it is 220 seconds.

Cycle time
There are two cycle times.  There is the cycle time for each station and there is the cycle time for the entire handbag manufacturing process.  To determine this, you will need the capacity which can be determined from the following table which shows how many workstations there are for each process.

Process             Stations available     Cycle Time

Leather Cutting            6                                20 seconds

Zipper Attachment       2                                35 seconds

Sewing                         1                                30 seconds

The cycle time for each station is the average amount of time it will take each bag to be completed.  Assuming each station can process 1 bag at a time, cycle time is the process time divided by the number of workstations.  Leather cutting for example takes 120 seconds per bag, but since there are 6 of them being worked on at a time, it is as if one bag is being completed every 20 seconds.  Now to analyze the cycle times to determine the bottleneck.

Bottleneck
The bottleneck is the slowest cycle time in the process and it determines the rate at which the entire system can produce handbags.  In other words, the bottleneck, which is the zipper attachment process, determines the capacity for the entire handbag manufacturing plant in our example.

Here's how this works:
Imagine you're standing at the end of the assembly line.  You know it takes an individual bag 220 seconds to be processed from start to finish, but how long is the wait for individual bags at the end of the line.  In other words, if you're standing at the end of the line, how much time elapses between finished bags?  I'll give you a hint, it's a lot less than 220 seconds.  Remember, during that 220 seconds, there are multiple bags being processed at any given point in time by the multiple work stations.

The answer is once every 35 seconds.  This is the time it takes on average per bag at the bottleneck and is therefore the time that elapses between each complete bag.

At station 1, there is an average of 1 cut piece of leather transferred to station 2 every 20 seconds.

Station 2, however, can only process 1 bag every 35 seconds.  This means that station three only gets a new a bag to work on every 35 seconds.

So, station 3 completes a bag in 30 seconds.  But then it waits 5 more seconds until station 2 can finish its work and send over another bag.

Therefore, station three sends one finished bag to the end of the production every 35 seconds.

No matter how fast station 1 works, station two cannot handle anymore inventory than it's already getting.  Also, no matter how fast station 3 works, the system will not produce bags at a faster rate.  If station 3 could sew the bags together at a rate of 1 bag every 10 seconds, it wouldn't make a difference because station 3 would still have to wait for station two to supply it with more work in process.

Only if the bottleneck is improved will the rest of the system improve.

That said, take a look at leather cutting.  Is it really necessary to have six workstations.  I can't see what's good about it.  Even with only four workstations, it still operates faster than the bottleneck and therefore would be sufficient capacity.

Currently, station one is over-producing.  If station 1 were to produce non-stop, there would be no way station 2 could keep up and huge work in process inventory would be the result.  A post in the near future will cover this topic in greater detail.

In the mean time, if you can't wait, I highly suggest reading Eliyahu Goldratt's The Goal.  This book is an essential (and easy, enjoyable) read for anyone who wishes to have a greater understanding of the effects of bottlenecks on inventory control and plant capacity.

Average Inventory Levels

I have recently received a lot of inquiries regarding average inventory levels so I thought I would devote and article regarding how to find them. The first half of this article covers how to find what inventory levels should be, and the second half covers what they have been in the past.

Part I: How to Optimize Average Inventory Levels

This section is mainly here to provide a brief description for how optimal inventory levels for materials are kept assuming the company is a textbook example with no strange variables. Essentially, this section can serve as a starting point for inventory managers. 

First thing you need to determine the ideal inventory levels is a material's Economic Order Quantity (EOQ). This is the amount you should be ordering when you place orders.

Next you need to determine your Safety Stock (SS). This is the amount that you should have remaining when the EOQ arrives. 

Basically, safety stock is the average bare minimum you will have at any give time, and EOQ+SS is the average maximum amount you will have at any given point in time. This should be intuitive because safety is what you have when your shipment arrives and when the order arrives (EOQ) it gets added to the safety stock. 

I say average minimum and maximum because you might not receive the EOQ exactly when you planned to and therefore may have more or less. On average you should have the SS amount when you receive shipments. Between these two average minimum and maximum values lies your long-term average inventory. 

The formula for this is:

Optimal Average Inventory=(EOQ+SS+SS)/2 

This is for materials. For finished goods, you should aim to keep an inventory level designed to prevent a stock out. This level would be a safety stock of finished goods, thus making the ideal average inventory for finished the safety stock value based on your company's service level. 

Part II: How to Assess Inventory Levels 

Simplistic Method - Historical Inventory Levels

Most methods of accounting take the beginning inventory of a period, add it to the ending inventory of a period, and divide by 2. This essentially provides the mathematical average for a given month. 

For example, if your inventory level for a good is 2000 on July 1st, you produce 3000 units and sell 1000 units by July 31st. This leaves you with 4000 units. The formula is: 

Avg. Inventory = (Beginning Inventory+(Beginning Inventory+Units Produced-Units Sold))/2 

Avg. Inventory = (2000+(2000+3000-1000))/2 = 3000 

Or more simply:

Avg. Inventory = (Beginning Inventory+Ending Inventory)/2 

Avg. Inventory=(2000+4000)/2=3000 

So this covers historical looks using an accounting approach. A lot of firms use this method to evaluate their average inventory levels. Personally, I have a problem with this method which I believe the following example will help to illustrate: 

Daily Weighted Average Inventory Approach

Suppose you start with 10,000 units on May 1st. Also suppose you produce 10,000 units in that month spread out across 21 business days. Now (and this is the important part) suppose you sold 20,000 units in May. This brings the ending inventory to 0. Using accounting methods, the formula gives us 10,000 as the average inventory. 

So why is this so bad? In short, because the average inventory is not 10,000 units. In fact, there were only two days in which 10,000 units or less were held and these days were May 1st (10,000 units) and May 31st (0 Units). Assuming that production was level through the 21 day working month, this means that 500 units were produced daily, thus raising inventory by 500 units a day until inventory was dropped by 20,000 on the 31st. Here is what the inventory levels look like in the month of May (each record represents the end of 1 working day in a 21 day work month). 

1) 10,500
2) 11,000
3) 11,500
4) 12,000
5) 12,500
6) 13,000
7) 13,500
8) 14,000
9) 14,500
10) 15,000
11) 15,500
12) 16,000
13) 16,500
14) 17,000
15) 17,500
16) 18,000
17) 18,500
18) 19,000
19) 19,500
20) 0
Average=14048 

Clearly, this method of average inventory provides much different average inventory values than accounting procedures. It gives each day of the month an equal weight as opposed to giving the first and the last day of the month 50% weight each which I believe to provide more accurate results.

My example may seem a bit extreme, but consider a company who produces large runs of goods for another company and agrees to hold onto their inventory delivering once a month. In this case the supplier could start and end with 0 units each month end ship it all off, ending with 0 units each month making the average finished goods inventory 0. Their average inventory sure as hell isn't 0. They are higher, and they may be much higher. For this reason, it is very important to carefully choose how you assess your average inventory levels.

Why are inventory levels so important?

To put it simply, average inventory levels are important because they allow you to determine how much money you have tied up in inventory and how much value your inventory assets hold. Helping to determine what they should be can help cut back on unneeded inventory, and knowing what they are can help you determine average warehouse usage, inventory risk, percentage of assets that are made up inventory, holding costs, etc. To review information regarding high and low inventory levels, refer to my previous article, The Pros and Cons of Inventory.

Safety Stock

First of all, here's the formula so you don't have to dig through my well-written article for it.

Safety Stock:  {Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2}

If that wasn't clear to you, I suggest reading on.  This article will explain in detail what safety is used for, and how to use it.

Inventory management is about two things: not running out, and not having too much. Our desire to not run out, along with uncertainties in demand and supplier lead times are why we have inventory in the first place. Essentially, inventory is a reserve system to prevent a stock out. However, as important as it is to prevent such a stock out, we also don’t want to hold onto too much inventory because of holding costs. So how do you balance the two and what is the right amount? More importantly, when should you re-order in order to prevent a stock out? The answer to this can be determined by obtaining and applying the following information about the inventory you wish to manage.

Re-order Point (ROP)

1. What is the average lead time for the part/finished good that you need?

2. What is the standard deviation of that lead time? It is very important to track how long shipments take from you suppliers. If you are not doing this, start. It should be your top priority. Assuming you have tracked the data, excel can very easily help you determine your standard deviation. In excel, go to the toolbar and click on Insert, then click on Function, and choose STDEV and click ok. Then, enter in as much lead time data you have and presto, you have your standard deviation.

3. What is the expected demand you are working with?

4. What is the standard deviation on this demand?  Perhaps this is something you will be familiar with from experience, however, if not, this is something you should be able to squeeze out of Ted from the marketing department.  One way to find it is to look at historical demand and use the STDEV function in excel to determine it.

5. How sure do you want to be that you aren’t going to run out? 90%, 95%, 98%, 99%? Whatever you decide, this will become your service level. Using this percentage, a statistical z-table should be used to get the corresponding “z-value.” A good z-value webpage can be found at http://www.inventoryops.com/safety_stock.htm. So, for example, if you want a 98% service level, you would use 2.05 as your z-value.

Ok, so you’ve gathered this data, now here’s what you do with it.

(Underlined section is safety stock)

Re-order point=Average Lead Time*Average Demand + Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2)

In this formula, the first term (Average Lead Time*Average Demand) is the average demand.

The second term {Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2} is the term that allows for the safety stock. In other words, the second term is the optimal safety stock level.

It is not simple to gather all the data that is needed for the calculations. For a product with multiple parts, each part needs to have its own re-order point calculations and its own safety stock calculation. This can all become very confusing if proper computer modeling is not employed.

Although I mentioned excel earlier, excel is probably not sufficient for your company’s software needs. If you have not already done so, it is very important to look into an integrated software package for these calculations and many others.