Working out months of inventory on hand

Hi,

Can anyone help with the below - I am trying to calculate in cell AN3 the current months of stock I have on hand based on the current forecasts and it is proving very tricky.
I have put a description in cell AN3 of the attached of exactly what it is I am trying to do to try to clarify but essentially it takes the current month, looks at the current stockholding and then counts ahead the forecast to return in how many months time we will be out of stock. This one had me beaten so any help will be appreciated,

thanks

The most obvious way to solve this (to me) is to create another sheet that tracks the cumulative sums for your forecast. I could not think of a way to do this with just one formula, and without VBA, although it may be possible.

You have a problem with the dates in your header row. See column Z, and then in column AA you start over again with 12/1/2016. It looks like that's supposed to be 2018. I have replaced your dates with a formula to generate the dates.

In AN2 I assume the heading should be "Months Cover", not "Weeks Cover".

There are a couple of issues that you have because of your data design that don't have anything to do with your question or my solution. If you want help with these, you can continue here or start a new thread.

1. Keep in mind that your granularity is at the month level. For example, today is 3/30 but when adding your forecast you will count the entire month's forecast, even though there's only a day left. Do you need to do prorating?

2. This formula tells you how many whole months you have left. If you have enough for a little over 2 months, the result will be 2.

Hi Dflak and 6stringjazzer - both of you, great answers, thank you so much. I am currently using both of them on different workbooks to decide which will suit best going forward but I wanted to thank you both for supporting with your expertise, it is very much appreciated. They both work fantastically well.