Inventory Age Calculation Optimization

Options
Taylor Moore
Taylor Moore
in Modeling

Hi Everyone,

I am working through a calculation where we are trying to determine the average age of our inventory backlog each day in time. We are using FIFO to determine what inventory is processed first. If in Day 3 I have 1000 1 day old widgets and 500 2 day old widgets, and I have the capacity to process 750 widgets, the 500 2 day old widgets would be processed first and the 250 of the 1 day old widgets would be processed. The remaining 750 1 day old widgets would move the Day 4 and become 2 day old widgets. The same calc would then be performed each day for and the rest of time.

The only way I have found to do this so far is to create 20 predefined periods to iterate through the previously mentioned calculation for each day in time. This gets the answer we are looking for, but it is a very taxing calculation in Anaplan. We have a lot of different inputs that can change this calculation and the time it takes for an input change to process has increased from 6 to 12 seconds, which is concerning when it comes to concurrency issues for our process. We have thought of a couple of stop-gaps to only calc the formula if necessary, but would love to find a more computationally efficient way to approach this math problem. Has any figured out a more streamlined solution to this?

I attached the module blueprint of the calcs and output. There are a couple of line items missing from the blueprint, but it should be enough to understand the jist.

*N/A inventory is beginning inventory from Day 1 in the model where we do not know the age. It is processed first in priority.

Inventory Age Blueprint.xlsx
Inventory Age Output.xlsx

Answers

  • rob_marshall
    rob_marshall
    Options

    @Taylor Moore

    We did something similar about 4 years ago, so my head is a bit foggy. This was for an oil company planning their rigs where both the Planned Rigs and Adjusted Rigs (Rigs that were put in the field) could be adjustted by Users.

    This is a bit different as we did it by Year, but you can make the adjustment.

    image.png

    What the above is saying, for FY19, we planned 6 Rigs, but really put 9 rigs into the field so we had to "borrow" from a future year. Basically, the opposite of your issue, if I am understanding it correctly. In FY 20, we planned 6, but really put 9 into the field but we had already pulled 3 (of the 6) into FY19, so we needed to pull 6 from FY21. This is how we accomplished this.

    First, we created a Fake Time list, this would be in place of all of your line items.

    image.png

    In a SYS Fake Time module, we created a mapping to the native Time Period

    image.png

    In our "worker" module, only dimensionalized by native time, we have the following line items:

    image.png
    image.png

    in our old school dashboard (this is how old this is), we are showing the Coverage line item from above

    image.png

    Again, now that I think more about it, we are doing the exact opposite of you as we are pulling future Planned Rigs back to the current year to cover our Adjusted amount.

    Hope this helps,

    Rob

Categories