Calculating accurate forward days coverage or Days On Hand

Hi,

I made a function that takes Ending Inventory (in Row 11) and then looks over to the next column (or columns) to calculate at what point (which week) the End Inv will go to Zero.
The function is Days On Hand - and as you can see my function calculates accurately.

However I have to extend/make my function really big in order to give me "actual days". Right now I just say any Days On Hand above 28 is ">28".

Can anyone help me write a function that will calculate the Days on Hand to the exact number of days so that I can go beyond the restriction of ">28".

Looking for a function that won't bog down the spreadsheet as there may be 20 items and several columns on one sheet.

Much appreciated!!!

I was thinking on this and figured that, I suspect, what you should really do is focus your "capacity per day" on the week in which your ending inventory is exhausted

in other words, establish how many "items" are required going into that final week, and calculating days required in that week relative to that week's forecast

Formula:

Please Login or Register  to view this content.

in the majority of cases this change will have no impact, only where forecast fluctuates, materially, m-o-m

using the above, versus prior suggestion, you would see biggest difference in P13 Wk4 where:

prior calc (mimicking your current logic) would return 29.5 --> based on 4,092,056 / daily average of 138857 [4860000 / 35]

above calc would return 29.9 --> based on 4 weeks [28 days] + final week days: 282056 units required / daily average of 150000 [1050000/7] --> 1.9 days --> 28 + 1.9 --> 29.9

XLent - thanks a lot. your help is very much appreciated. You were able to solve this fine.

Much appreciated!!!