Simple Inventory Management

(1)

http://www.jondbennett.com

Simple Inventory Management

Free Up Cash While Satisfying Your Customers

Part of the Business Philosophy White Papers Series

Author: Jon Bennett

September 2012

(2)

(3)

Page i

Executive Summary

Poor or improper inventory management policies starve a business of cash and increase its financial risk. While there are a variety of inventory management techniques that are well documented in the

literature of operations management, most of these techniques are based on theoretical conditions that do not hold in practice, or depend upon information that is not readily available to most businesses. As a result of the lack of simple-to-use models, most business managers use methods that maintain either too little or too much inventory. Both situations starve the business of cash and increase the risks of customer dissatisfaction, cash crunches, and bankruptcy.

This paper proposes a simple to use method using history-based forecasting to measure average sales and sales variability. This method uses the t-distribution to calculate the required inventory needed to achieve a desired customer service level while minimizing inventory carrying costs. The method is effective for both seasonal and non-seasonal items and works as follows:

1. Calculate both the the average historical sales for each month of the previous five years, and the standard deviation of the monthly sales. For example, suppose we calculate an average January sales volume of 100 items with a standard deviation of 5.

2. Using the t-distribution table1 lookup the t-value for a given stock coverage probability based on the degrees of freedom (years-1). For example, for a stock coverage probability of 90% with 5 years of data (df = 4) the t-value is 1.5332.

3. Calculate the target inventory level for the specified month as follows: Average Sales for That Month + (t-value X Standard Deviation for That month) e.g. 100 + (1.5332 X 5) = 107.67 or 108 items.

Like all mathematical models, this model is based on certain assumptions that may not hold, thus it has limitations. The most important assumption is that the future can be mathematically derived from the past. While the past may be our best guide to the future, the future is not calculable. The second assumption is that sales will vary according to the t-distribution. The t-distribution was selected for solid statistical reasons, but that provides no certainty that sales volume will not be affected by factors outside the model. Finally, as always, the manager must use his or her personal judgment about how the results should be applied, and never blindly follow any model, mathematical or otherwise.

The best way to use this model is to examine its potential and if implementation seems warranted then work with a trained operations consultant to implement the method in the business.

1

(5)

Introduction

First of all, let’s be clear. The simplest inventory control method is to use a constant monthly inventory level that is 1/12th of last year’s sales. If monthly sales are generally constant then this can work very well. Of course many businesses do not even go to this much trouble; they just “re-order a few” when stock runs out. This produces stock-outs that deprive the company of cash, and leaves the customer wondering if they should shop somewhere else. The alternative is to keep a lot of inventory on hand. This produces high inventory carrying costs as well as depriving the company of cash. Notice the

common denominator: poor inventory management deprives the company of cash. Since most business managers focus on sales, the high-inventory levels approach is disappointingly common.

In an earlier paper2 we discussed the ways in which a profitable company runs out of cash and becomes bankrupt. One of the accounts that consistently consumes cash is idle inventory. Idle inventory, usually the result of trying to make sure you have items in stock when your customer want them, sits on your books and makes your assets too high while making it difficult to meet cash demands. This problem is further exacerbated when the inventory expires and must be liquidated. Both problems function as a cash drain on the company. What to do?

If an interested business owner takes the time to do a bit of research on inventory management

methods he or she will be shocked. There are theories, formulas, functions, and equations that describe what ideal inventory management would look like… if someone understood how to apply them. Of course these methods depend on unrealistic environmental conditions such as constant demand, or on information, such as part failure rates, that is difficult to obtain. The end result is that the manager simply gives up the search and goes back to whatever method he or she is familiar with, the inventory builds and cash dwindles. There is another way.

This paper and its associated Excel® spreadsheet describe a simple-to-use statistical method for managing the monthly inventory of a single item. While there is an underlying statistical model, the spreadsheet user does not need to understand the math. All the spreadsheet user must do is enter information on monthly sales volume, cost, price, and other pieces of information that should be readily available and the spreadsheet will provide suggested monthly inventory levels. This spreadsheet is designed to deal with seasonality effects, but it works equally well with non-seasonal items. While the management of a single item is inadequate for retail environments, the spreadsheet does a good job of illustrating what a good inventory management scheme can do for your business. This illustration is provided by way of a comparison between the suggested statistical method, and the more common “stock-up then liquidate” method.

The first few sections of this paper cover the basics of the statistical model, its inputs, outputs, and limitations. After that there is a tutorial on how to use the spreadsheet to compare inventory methods and see how much cash is freed up by the statistical method versus manual methods.

2

(6)

The Statistical Model

The statistical inventory method described here is based on a probability distribution called the “t-distribution.” In order to understand the model a little better we need to introduce a few terms.

Mean – Simply a more “mathematical” way of saying “average.” This term is used in

mathematical descriptions because the term “average” is commonly applied to several distinct mathematical concepts3. To avoid any confusion, I have chosen to use the word “mean” in this paper.

Standard Deviation – This term is in much less common usage. Let’s start with an example: average monthly sales. Now, let’s suppose that you sold 432 units of some item last year. That is an average monthly sales rate of 432/12 or 36 units per month. Does that mean that you sold exactly 36 items every month? Of course not, some months you may have sold more, some months less, but you sold 432 during the year. So, the question is, for lack of a better term, how “chunky” the sales were. If most months you sold about 36, give or take a couple then the sales were pretty smooth and standard deviation would be low. On the other hand, suppose you sold 19 units every month for 11 months, then 223 units in the last month. Well, that’s: (19*11) +223 = 432, an average of 36 per month, but not nearly as smooth as before, and standard deviation would be high. So, standard deviation is a measure of the “chunkiness” the data; chunkier data, higher standard deviation.

Statistical Distribution – This is a term for a type of mathematical construct used to predict probabilities. Suppose we have a bunch of monthly sales data with a mean of 100 and an SD (standard deviation) of 5. Chances are that any given month you will see sales pretty close to 100; a little over, a little under. On the other hand, suppose we have a mean of 100 and an SD of 25. Now, any given month is much more likely to be further over or under 100. So, the job of a distribution is, given a mean and standard deviation, to predict how likely it is that a certain number will show up. They typically look something like this:

Figure 14

3

When people say average they sometimes mean: Mean – the true average, Median – the “middle of the road number,” and Mode – the most common value. The term “mean” has not (yet) become so unclear.

(7)

Copyright ® 2012 Jon Bennett Consulting All Rights Reserved Page 5 of 14 This diagram of the “Normal Distribution” shows that the values are more likely to cluster around the mean (center) and become increasingly less likely as they get further from the middle. In fact, we expect 68.2% of the values to be within one SD of the mean (inside the ± 1σ region).

Now you can see the basis of the model: If we were using the Normal Distribution, and you had mean monthly unit sales of 100 with an SD of 5 then we would expect 68.2% of the monthly sales to be between 95 and 105 units. We could work the problem backwards by asking the question “how many SDs over the mean would we need to keep in stock in order to cover 95% of the months?”

That is how the model works. This model, for complex reasons, uses the t- distribution to figure out how many SDs over the mean will be needed in order to achieve the target stock coverage. Then we just multiply the required SDs by the actual SD of the data, add it to the mean and get a target inventory level. Appendix 1 contains a t-distribution table that you can use as a reference. Move down the left df (degrees of freedom) column to one fewer than the number of years of sales data (for 5 years, df = 4), then move right until you are under the desired stock-coverage probability, and read the number of SDs needed to achieve the coverage. For example, for five years of data at the 90% stock coverage level, move down to row 4, then right to the column under 0.900 and you will find 1.5332 SDs. So, with a mean sales volume of 100 units and an SD of 5 you would stock 108 units:

100 + (1.5332 * 5) = 107.66 rounded up to 108.

Inputs and Outputs

With an understanding of how the model works, the inputs and outputs should be pretty obvious. You need to know the monthly sales history and the desired stock coverage. The output is the target stock levels for each month.

Assumptions and Limitations

There are significant assumptions and limitations. The first assumption, and by far the most significant, is that the future can be predicted by the past. It cannot. So, while the past is probably our best guide you cannot abdicate judgment to a non-thinking mathematical construct and assume that anything but trouble will follow5. Here we come to the crux of the problem with financial modeling. A few, say 5, years of data is not really enough to establish a strong statistical pattern. If we moved up to a larger number of years of sales history, say 30, it is unlikely that the conditions from 30 years ago have any relevance to next year’s sales volume. So, you will have to exercise judgment and override the model when it seems appropriate.

A second, but also significant, assumption is that the data is distributed according to the t-distribution. While the t-distribution is probably the best choice for this problem, again, you must exercise judgment about the projections. For example, if there was some special reason that sales were down last year, like the business was closed for six months for renovation, then the low sales number will affect both the mean and standard deviation. The computer has no way of knowing this, so you might have to lie to the

5

(8)

computer by plugging in the previous year’s sales number as a substitute for the year in which the business was closed for renovation. Again, you are the manager so use your judgment instead of letting the machine think for you. ‘nuff said, now on to the spreadsheet.

The Spreadsheet

The spreadsheet provides input areas for the historic sales quantities, stock coverage levels, and a play area where you can (and should) try your hand at beating the model. There are also some graphics and a “free cash” analysis to evaluate the performance of the model. Finally, there is a Monte Carlo simulation of uncertain demand to see how the model performs under various uncertain demand conditions.

Inputting the Data

At this point it might be best to open the spreadsheet file in Excel and follow along. There are three main sections to the spreadsheet. The first area is where you enter your target stock confidence level and monthly sales information. It looks like this:

Figure 2

The stock confidence level is, by far, the most important single input to the model. You will be making adjustments to this number, so start with an optimistic level like 90%. Next, enter your historic sales volume data from your sales records for the last 5 years. This is the quantity sold, not the dollar revenue amount. Next, if you have reason to believe that next years will be different, enter your expected growth percentage in the provided field. Finally, at the bottom of this section you will see a statistical forecast of next year’s demand based on the mean monthly sales and the expected growth rate, followed by the suggested target inventory level to achieve the specified stock coverage probability. Next there is a graphic summarizing all of this data.

Stock Confidence Level 90.00% << 1% - 99.99% 1.533

Quantity Sold By Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

1 4 2 4 38 50 79 129 83 59 10 5 12 2 4 0 4 21 42 79 132 126 62 10 6 11 3 3 2 4 35 36 77 135 136 90 11 4 6 4 4 4 3 32 25 88 128 115 64 12 7 18 5 4 3 1 27 36 78 112 111 56 15 5 3 Max. Sold 4.0 4.0 4.0 38.0 50.0 88.0 135.0 136.0 90.0 15.0 7.0 18.0 Avg. Sold 3.8 2.2 3.2 30.6 37.8 80.2 127.2 114.2 66.2 11.6 5.4 10.0 Min. Sold 3.0 0.0 1.0 21.0 25.0 77.0 112.0 83.0 56.0 10.0 4.0 3.0 Std. Dev. 0.4 1.5 1.3 6.7 9.2 4.4 8.9 20.0 13.6 2.1 1.1 5.8 Stock Buffer 0.7 2.3 2.0 10.3 14.1 6.8 13.7 30.7 20.9 3.2 1.7 8.9

Next-Year's Forecast Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Expected Growth 5%

Next Year's Demand Forecast 4 2 3 32 40 84 134 120 70 12 6 11

Statistical Target Inventory 5 5 5 42 54 91 147 151 90 15 7 19

Stock Buffer Factor:

(9)

Figure 3

The red line represents the mean sales by month. There are “guard bands” indicating the maximum and minimum quantities sold from the historical data. These guard bands provide a sense of how variable the data is, but should not be confused with any statistical measure. Finally, the purple line shows the suggested monthly inventory level based on the desired coverage probability. While this is all

interesting, it does not get to the most important aspect of managing your inventory: cash. The next section, entitled “Analysis of Statistical Inventory Control Method” is shown below.

Figure 4

As you can see, this section requires you to input cost and margin information as well as an estimate of your inventory holding costs and the cost of a lost sale.

Inventory Holding Costs are one of the most important aspects of cash management for a business. Consider this: suppose you had to borrow6 the money to buy the inventory, what would the monthly

6

Whether the company owes you or the bank, the company always borrows the money.

0 20 40 60 80 100 120 140 160

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Quantity Sold by Month with Max / Min markers

Max. Sold Avg. Sold Min. Sold Statistical Target Inventory

Analysis of Statistical Inventory Control Method

Cost $ 250.00

Margin 35.0%

Price $ 384.62 Profit $ 134.62 Annual Inventory Holding Cost (% cost) 8%$ 20.00 Cost of a lost sale (% price) 10%$ 38.46

Cash Analysis Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Actual Demand 4 2 3 32 40 84 134 120 70 12 6 11 Inventory Tracking Beginning Inventory 5 5 5 42 54 91 147 151 90 20 8 19 Qty. Sold 4 2 3 32 40 84 134 120 70 12 6 11 Inventory Purchases 4 2 40 44 77 140 138 59 - - 17 -Ending Inventory 5 5 42 54 91 147 151 90 20 8 19 8

Qty. Lost Sales - - -

-Revenues and Costs Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Sales $ 1,538 $ 769 $ 1,154 $ 12,308 $ 15,385 $ 32,308 $ 51,539 $ 46,154 $ 26,923 $ 4,615 $ 2,308 $ 4,231 Cost of Lost Sales - - - -Inventory Purchases (1,000) (500) (10,000) (11,000) (19,250) (35,000) (34,500) (14,750) - - (4,250) -Inventory Holding Cost (8.33) (8.33) (70.00) (90.00) (151.67) (245.00) (251.67) (150.00) (33.33) (13.33) (31.67) (13.33) Monthly Gross Profit $ 530 $ 261 $ (8,916)$ 1,218 $ (4,017)$ (2,937)$ 16,787 $ 31,254 $ 26,890 $ 4,602 $ (1,974)$ 4,217 Annual Gross Profit (No TVM) $ 67,916

Less: Cash Tied up In Inventory $ (2,000) Net Available Cash $ 65,916.49

Simulation of Uncertain Demand

Confidence Level of Forecast 95.00%

Low Expected High 64,727

64,946 65,165 Expected Net Available Cash

(10)

interest rate be? That is the inventory holding cost. Don’t forget to divide the APR by 12 to get the monthly rate.

The cost of a lost sale is somewhat controversial and extremely difficult to figure. Just imagine that if a potential customer shows up to purchase the product and it is not in stock, he or she may go

somewhere else. Once that pattern is established, they might go to that somewhere else the next time instead of “wasting time” shopping somewhere that does not reliably stock the product. The minimum dollar cost of the lost sale is at least the same as the dollar amount of the inventory holding cost on one unit since you will have to pay that to hold the unit in stock. The maximum dollar cost of the lost sale could be as high, should they never return, as the estimated present value of all future sales to that customer. Use your judgment and take a shot. In fact, if you take the question seriously and think about the origin of the inventory holding costs and the cost of lost sales, you will learn more about corporate finance and business management than you are likely to learn through any other activity. With the data entered, we can examine the output.

Understanding the Output

The output section begins with the inventory tracking area. This area calculates the monthly inventory levels and inventory purchases given the forecast demand. This provides the input to the revenues and costs section.

The revenues and costs section figures out how much revenue is generated, and subtracts the cost of lost sales, inventory purchases, and inventory holding costs to arrive at a monthly gross profit. We add up all of the profits from the year to get an annualized gross profit number. From that we subtract the amount of cash tied up in inventory at the end of the year to arrive at the most important figure in the output: Net Available Cash. Since freeing up cash is the test of a good inventory management strategy, this is the only number that really matters. Now things get interesting.

The Monte Carlo Simulation

Anyone with any sales forecasting experience knows that the numbers are a fabrication based on past sales and expected growth. We also know that there is no reasonable expectation that actual sales will exactly match the forecast. This raises several difficult but important questions. What if the growth does not occur? What if sales are higher than expected some months and lower others? How will the cash balance look under these conditions? These are the types of questions that a Monte Carlo simulation can answer.

The Monte Carlo simulation in this model produces various monthly sales scenarios. The mean sales for each month will match the forecast, but they will vary at a probabilistic rate based on SD of the

historical sales. So, after a ‘controlled randomization’ of the monthly sales, the spreadsheet records the Net Available Cash balance and repeats the cycle. This spreadsheet repeats the cycle 2,000 times and records each result. From there, we can calculate a mean Net Available Cash under uncertain demand. This is the number that *really* matters because we know that demand is uncertain.

(11)

Beat the Model

Below the Analysis of Statistical Inventory Control Method section is another, nearly identical, section where you can try your hand at entering various monthly target inventory levels to see if there is a set of levels that performs better than the statistical method. It should be obvious how it works as it is almost identical to the previous section. Following this section is a graphical comparison of the two methods as show below.

Figure 5

Notice that each method produces a variety of results based on the simulation of uncertain demand. The mean Net Available Cash is indicated by the vertical line associated with each set of results. Of course next year’s actual Net Available Cash will not match the mean because, wait for it, demand is uncertain. So, we introduce anther statistical concept called a confidence interval. The two black lines on each side of the mean for each method indicate the region in which we expect to see next year’s Net Available cash. Of course the more “certain” we want to be, the broader the range will be. The width of the range is controlled by the Confidence Level of Forecast input in cell B79. Let’s assume that you have set the common value of 95% as the confidence level of the forecast. The correct way to read the results is to say “the model indicates that, with 95% confidence, next year’s Net Available Cash will be between $52,129 and $52,926.” The actual numbers will vary.

Typical Usage

The normal way to use this model is to try various stock confidence levels and find the one that produces the highest level of Net Available Cash. Start by making a table like this:

Stock Confidence Level Net Available Cash 50% 60% 70% 80% 90% 95% 99% Table 1 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% - 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000 90,000

Comparison of Methods Under Uncertain Demand

(12)

Now, enter each stock confidence level in the appropriate input cell (B3) and record the expected net available cash (cell E81). It might be easier to split the Excel view horizontally so that you can see both cells at the same time. Using the sample data I get this output (yours will not match due to the randomized nature of the simulation):

Stock Confidence Level Net Available Cash

50% $62,349 60% $63,598 70% $64,470 80% $65,003 90% $64,701 95% $62,914 99% $55,990 Table 2

So, how do I use these numbers? Well, I can see that the highest Net Available Cash is reached with a stock confidence level of 80%. This will vary depending in both the stock confidence level and the other factors such as inventory holding costs, cost of a lost sale, and price and cost. However, for these data, the 80% level produces the highest Net Available Cash. However, I see that the 90% level is pretty close at only $302 less. So for $300 (give or take) I can purchase 10% more confidence that I will have the product in stock for my customers. Not a bad deal, so I would choose the 90% confidence level and set my inventory policy for this item accordingly.

Other Considerations

You might have noticed that, as I was thinking out loud about which stock confidence level to choose, I was considering my customer. As a business owner, he or she is your boss and should be considered accordingly. Not all decisions are purely financial, and indeed other considerations, such as marketing considerations, should prevail. As always, use your own judgment and do not abdicate your

responsibility as a community member and business manager to some mindless financial model. If you think that statistical inventory control is for you, work with a trained consultant to implement such a method in your business. This simple spreadsheet model is not up to the task of managing dozens, hundreds, or thousands of SKUs.

(13)

Appendix: Student’s t-Distribution Table

7

(14)

Disclaimer

This publication contains general information only and is based on the experiences and research of Jon Bennett. Jon Bennett is not, by means of this publication, rendering business, financial, investment, or other professional advice or services. This publication is not a substitute for such professional advice or services, nor should it be used as a basis for any decision or action that may affect your business. Before making any decision or taking any action that may affect your business, you should consult a qualified professional advisor. Jon Bennett shall not be responsible for any loss sustained by any person who relies on this publication.

Simple Inventory Management

http://www.jondbennett.com