Inventory Months Supply Question

[JLewis]

I have a spreadsheet with current inventory on hand and the sales for each
month from the last year. I want to figure how many months our inventory on
hand will last based on the last years sales. For instance, WidgetA has 100
on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively.
I know that we have 4.5 months supply, but I have to manually figure this
calculation. I have more than 400 items. This takes a long time to figure, is
there any way to automate the figuring of the months supply?

 

[Glenn]

I have a spreadsheet with current inventory on hand and the sales for each
month from the last year. I want to figure how many months our inventory on
hand will last based on the last years sales. For instance, WidgetA has 100
on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively.
I know that we have 4.5 months supply, but I have to manually figure this
calculation. I have more than 400 items. This takes a long time to figure, is
there any way to automate the figuring of the months supply?

Look at this:

http://tinyurl.com/bndrvj

If you are having trouble making it work, put your worksheet on www.savefile.com
and I will see if I can help.

 

[Shane Devenshire]

Hi,

Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is
in B1

=12*B1/SUM(A1:A12)

 

[JLewis]

This doesn't work for my data. I tried this approach already. It might work
if each month's sales were fairly equal. The variance between months is too
great. For my example, I got 9.23 when I know the result should be 4.5 or at
its basic using full cells, 5. Thanks for the help though.

 

[Glenn]

Hi,

Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is
in B1

=12*B1/SUM(A1:A12)

This will provide very inconsistent results. If you assume the known data (5,
10, 15, 40, 60 for March through July) and fill in 5 (minimum sales) for the
unknowns, your answer is 7.5 months supply. If you fill in 60 (maximum sales)
for the unknowns, your answer is about 2.45 months supply. Yet clearly the
"correct" answer, according to the OP, is 4.5 months supply. That is why I
posted the link to a more complex solution (originally provided by Harlan Grove)
that will give you 4.5 months supply regardless of the other unknown data.

 

[JLewis]

It looks like this is on the same level as what I want to do, but I can't get
the formula to work. It gives me an error message saying "A value used in the
formula is of the wrong data type". I don't understand as all of the data
used in the formula is of the "Number" type.

 

[Glenn]

OK, but I can't help without more information. Either post your worksheet on
www.savefile.com or describe your exact data and formulas here so I can recreate
it on my end.

 

[JLewis]

File link here: http://www.savefile.com/files/2013754

Thank you very much for your help!!!

Jennifer

 

[Glenn]

OK, first it looks like you missed the part about array formulas needing to be
committed by hitting CTRL+SHIFT+ENTER.

Also, this is a two-cell solution. The formula you entered could/should be in a
hidden cell, and then the second formula would be displayed. Try it this way
(copy directly from this post if possible):

R6 = SUM(($F$2>MMULT(H5:Q5,(COLUMN(H5:Q5)>=TRANSPOSE(COLUMN(H5:Q5)))+0))+0)
*** This is the array formula ***

D6 = R6+(($F$2-SUM(H5:INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+3,FALSE)))/
(INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+4,FALSE)))

Copy to R7 and D7 for the "High Value" results.

 

[Shane Devenshire]

Hi,

If that won't work for you, you need to tell us what the thought process
should be for making the estimate. For example, are you assuming that the
monthly sales are the same each year so you can use the prior data to
estimate the future figure. Or are you assuming the the future figures will
be proportional to a prior years results? If you don't have prior data how
can you predict the future?

In other words if you know Jan - Jun what tells you what July's needs might
be?

 

[JLewis]

Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank
you! I really appreciate your help on this. This will help me in other
reports as well.

 

[Glenn]

Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank
you! I really appreciate your help on this. This will help me in other
reports as well.

Glad I could help.

 

[JLewis]

Glenn,

After checking the data calculations, I noticed that partial months (less
than one month) calculations do not work quite right. I sorta fixed this by
including an "IF" function in the 2nd half of the formula that says if the
calculation is less than zero to add a 1. This works. For instance, I had 1
item that the calculation was -.02. I had 81 in stock and 83 sales in the
first month. It should have been .98, so adding 1 was the solution. If there
was no inventory the calculation was -1. Adding one solved this problem as
well.

Again, this is a great formula, it just needed a little tweaking for less
than a month supply. Did you have a better fix for this? or did I have
something wrong in your formula that caused the negatives?

I also had another problem that wasn't really associated with the formula.
We have some new items that do not have sales in the first few or more months
of the year (because the item was introduced later in the year). The
calculation counts these zero months as full months of supply. Is there any
way to account for leading zero months and skip them? As a fix to get the
report finished, I created a new formula in another cell to count zero months
and then deducted them from the calculation. This worked somewhat well, but
it also counts months where there was no sales in the middle of the year.
(sales in Jan-May, no sales in Jun, sales in Jul-Dec for instance). These
should be accounted for as having no sales. Mostly due to the seasonality of
our business.

Any thoughts you have would be appreciated. Again, I really love the
formula, it saved me hours of work!

Jennifer

 

[Glenn]

I use a version of this formula to evaluate inventory against a forecast of
future sales, so empty months (or weeks, in my case) are counted.

As Shane pointed out, when using sales history to predict future sales, missing
data makes this much more difficult. You are probably going to have to use
work-arounds suited to your business and the purpose of the data you are generating.

For example, if you have partial information on a new item, you may need to find
a comparable item that you have complete history for and use that for your
projections.

As for the adjustments you made, I would have to see the final formula and the
data that led you to make the adjustment to see what was happening. However, if
you are getting the correct answer, than the adjustment works and I wouldn't
worry about finding a better fix.