FIFO Application - Adding to, subtracting from, and shifting columns.

[AlteryxB2C]

Here's one that's stumped me, but I'm sure there's a solution.

In a process where inventory flows in and is processed out, I'm ultimately looking to identify when inventory will reach a certain "age" threshold. 

All inflows and outflows have forecasted projections for each future date.

Here's how I thought to set this up in a table. Assume it is December 1st, and like every present day, we know today's inventory. 

Row 2 will always be forecasted inflow.

The way I though this could be accomplished was by shifting the data over one column and down one row, then adding the inflow forecast to row 2, and somehow subtracting projected outflow from the "last" row with data. NOTE: If projected outflow is greater than the value in the last row, than it would somehow have to also subtract from the row above it as well. E.g. if projected outflow on Dec. 3rd was 400, than there would only be 3 inventory left, and all "1 day" in age.

I've kept projected inventory in red and known inventory in black, to illustrate how inventory moves through this system (e.g. 101 new items on Dec. 2nd, while 50 items left)

Any thoughts here would be welcome!

[AndrewDMerrill · Accepted solution]

This was not as simple as I first thought it would be, but it's not horrible either. MVP of the workflow (or MVT, rather) is the Multi-Row Formula Tool. Instead of doing each step day-by-day, which I believe would require an iterative macro, we make use of fact that we can first handle the inflow for every day, and then handle all the outflows at once:

[AlteryxB2C]

What a great solution Andrew! With some slight adjustments to account for the nuances of the real process I modeled this example on, I can confirm that this thoroughly meets the task. 

Marking post as solved.