Excel 2007 : Forward Looking Months of Supply Formula

**jrr1** · 11-09-2011, 04:16 PM

I am trying to come up with a forward looking Months of supply formula based on the the next several months sales forecast.

Any help would be appreciated!

**NBVC** · 11-09-2011, 04:41 PM

Can you elaborate?

What is some expected output and based on what?

**shg** · 11-09-2011, 05:06 PM

Does this look correct?

Please Login or Register  to view this content.

The formula in B11 and copied across is

=LOOKUP(9E+307, CHOOSE({1,2}, B9/C8, PERCENTILE({1,2,3,4,5}, PERCENTRANK(SUBTOTAL(9, OFFSET(C8, 0, 0, 1, {1,2,3,4,5})), B9, 6))))

It would be a little simplified in Excel 2007+.

**jrr1** · 11-09-2011, 05:09 PM

I want this formula to calculate the months of supply based on the ending inventory and taking into account the sales forecast for the next several months. That way I would know how much to add to my receipts to get to an acceptable inventory position going foward. I hope I am explaining this well enough.