Inventory Level (days) based on actual pull-out

[Jonasaur]

I want to identify my inventory level (days) based on actual/forecasted pull-out and not the average.

The desired result can be achieved thru this formula:

=IF(F9>J9,1,F9/J9)+IF((F9-J9)>O9,1,(F9-J9)/O9)…

But it would be a very long equation if I apply it to day 1 to 31.

Can anyone help me simplify the equation given the structure of the template below:

https://drive.google.com/open?id=1Nw...pyRFjk0Z3QWLC6

Excel File: https://drive.google.com/open?id=1Qu...FcXd8nBccbMFx9

Thanks!

 

[Rijnsent]

Hi Jonasaur,

that was a tough nut to crack... Something that seems so obvious for us humans is pretty hard to catch in a simple excel formula. In the end I ended up comparing it to cash flows: say you invest 100 now and get back 20 in year 1, 40 in year 2, etc. In how many years would you get back your investment? That got me to e.g. this site: https://techtites.com/calculating-payback-period-in-excel/
As you see, they use a lot of extra formulas to get to this one number (days of stock left in your case).
There are luckily some people who came up with a monster formula, see: https://www.reddit.com/r/excel/comments/55cumo/elegant_method_for_calculating_payback_and/
The file there: http://upload.jetsam.org/documents/Payback Period.xlsx (the initial amount is in C7, the yearly amounts in D7:H7)
And the solution:

=IF(SUM(D7:H7,C7)<0,"Never",SUMPRODUCT(CHOOSE(SIGN(INT((MMULT(D7:H7*((COLUMN(D7:H7)<=TRANSPOSE(COLUMN(D7:H7)))),--TRANSPOSE(D7:H7=D7:H7))+C7)/IFERROR(1/(1/ABS(TRANSPOSE(D7:H7))),1)))+2,1,1-((MMULT(D7:H7*((COLUMN(D7:H7)<=TRANSPOSE(COLUMN(D7:H7)))),--TRANSPOSE(D7:H7=D7:H7))+C7)/TRANSPOSE(D7:H7)),0)))

Note: it's an array formula, needs CTRL+SHIFT+ENTER to work.
Having said that, you probably do want to re-order your data so you have all your consumption in one block (maybe on another sheet or at the end of the row), not the format you have currently. Another option might be to create a VBA function.

Good luck,

Koen

 

[Jonasaur]

Hi Koen!

Thanks for the feedback!

Can it be applied given the structure of the template I shared? I am also considering to add another worksheet as helper but it would be hard to maintain when I am not around.

Thanks!