Inventory Age Calculation Optimization
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.