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?

JLewis wrote:
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.

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" wrote:

JLewis wrote:
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.

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 wrote:
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" wrote:

JLewis wrote:
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.

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

Thank you very much for your help!!!

Jennifer

"Glenn" wrote:

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 wrote:
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" wrote:

JLewis wrote:
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.

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$2MMULT(H5:Q5,(COLUMN(H5:Q5)=TRANSPOSE(CO LUMN(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.


JLewis wrote:
File link he http://www.savefile.com/files/2013754

Thank you very much for your help!!!

Jennifer

"Glenn" wrote:

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 wrote:
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" wrote:

JLewis wrote:
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.

Hi,

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

=12*B1/SUM(A1:A12)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"JLewis" wrote:

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?

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.

"Shane Devenshire" wrote:

Hi,

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

=12*B1/SUM(A1:A12)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"JLewis" wrote:

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?

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?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"JLewis" wrote:

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.

"Shane Devenshire" wrote:

Hi,

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

=12*B1/SUM(A1:A12)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"JLewis" wrote:

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?

"JLewis" wrote:
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?


Shane Devenshire wrote:
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.