"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.