Small Business Inventory Management

Managing inventory variability is a constant struggle in business one tool to help manage this is a regression analysis. A regression analysis is a statistical assessment of date that seeks to identify a general overlaying trend. It defines a sample set of date as a line. Like all statistical tools a regression analysis has limitations and without an intuition of the sample data a regression analysis is useless. The following is a sample of how a regression analysis could be used in small business inventory management and how knowledge of the real world implications was taken into account.

As an operations engineer of an aluminum and bronze casting facility that produces a proprietary line of electrical transmission components it was my job to address product requisitions, generate quotes and do what I can to make the fulfillment of orders go smoothly. One of the dilemmas that we faced was providing parts with a minimal lead time.

More often then not customers came to us looking for parts they needed quickly, and in the world of power transmission a day can mean thousands upon thousands of dollars lost. We did what we could to get the needed parts out as fast as we could but if a part was not on the shelf there would most certainly be a delay. It isn’t practical to maintain a large inventory all the time due to the carrying costs involved so, as an alternative we kept a select inventory of our most popular parts available.

As our name grew and more people heard about the product line, our market share also saw growth. This selective inventory model worked nicely for us but with the continual rise in sales comes a need for more parts on hand. The question became; at what rate should we continue to add to our selective inventory?

Each part had a separate demand so for the sake of this exercise I focused on only one item. I have gathered the quantities sold of this part over a 34 month period. The data is as follows:

Monthly

date By Month

Sum Of qty

 

 

January 2003

245

February 2003

186

March 2003

55

April 2003

326

May 2003

510

June 2003

329

July 2003

110

August 2003

52

September 2003

677

October 2003

100

November 2003

37

December 2003

362

January 2004

1014

February 2004

45

March 2004

136

April 2004

186

May 2004

196

June 2004

60

July 2004

213

August 2004

239

September 2004

191

October 2004

151

November 2004

32

December 2004

190

January 2005

1003

February 2005

100

March 2005

361

April 2005

154

May 2005

161

June 2005

561

July 2005

338

August 2005

135

September 2005

820

October 2005

1259

 
Now that we have the data we can plot a regression and see our trend. Using Excel QM we find the definition of this trend line to be Y=8.52X+160

As you can see our quantities are all over the place but there is an indication that we were experiencing an upward trend in our average volume.

This regression suggests that we were selling 8.52 additional parts every month. If this were the case it would be wise to add 8.52 additional parts to our on hand inventory every month to compensate. The result would be an out put that doubles roughly every 2 years.
A product that doubles in sales every two years is a great for a company but lets look at the over all sales for each year to see how they match up. Taking a step back to make sure we see the full picture proved to be prudent.  

Yearly

date By Year

Sum Of qty

 

 

2003

2989

2004

2653

2005

4892

 

   The data that we used to generate our regression shows a dip in sales in 2004 and a spike in 2005. It would be wise to take note of this as the deviation in sales is a good indication that the demand is not constant. If our demand isn’t constant we need to be careful about producing more parts then what we can sell.

One way to combat this is to make the on site inventory a function of the prior month’s sales. Knowing that the ratio of standard orders to rush orders was about 7:1 we could determine a good starting point for our inventory to be 20% of the prior month’s sales. While this may result in a larger on site inventory then what is required for rush orders, it is more then likely not going to exceed the quantity that we will sell for the month.

Every bit of data helps us determine what tomorrow might bring but no extent of data will ever insure us as to what tomorrow will bring. The regression analysis provided an indication to our macro rate of change while a quick look at our over all sales showed us that the distribution of sales was not uniform.

A good practice would be to continually run the regression analysis for different time periods and see if perhaps we could identify potential cycles or seasonal trends that we could later exploit in order to maximize profit.

I hope this illustrates how a tool like a regression analysis can be helpful but could lead to making wrong decisions if the practical circumstances are not understood. Many people right out of college have a great deal of tools under their belt but often do not know how to properly us them in the real world. My hope is that this illustration will push you to better understand your problems before spouting off a potential solution.  

 

TJ

About TJ

Engineer and Entrepreneur