Ytukyg

I am trying to simulate a backlog of cases by age.

Assumptions:

1. 
   FIFO: (First In First Out): This means we will always prioritize cases in the 4+ bucket then move to the bucket to it's left and so on. This means current day Inflow has last priority.


2. 
   Productivity = 1,000 = Constant: The most that can be worked in a single day is 1,000


3. Before the simulation begins we have 1'000 aged 1 day, 750 aged 2 days, 500 aged 3 days, & 250 aged 4 days
   

Given this information, I believe it is possible to input any Inflow and have the buckets complete themselves. I cannot figure out what equations to use to populate the desired result though.

I have tried a series of SUM, SUMIF, IF, and checking if the differences between a bucket and productivity is negative (all unsuccessful).

In Photo, the buckets for each day represent the end of day age of everything. Towards the end, when backlog is cleared, all buckets show 0 since the inflow is less then the productivity I.E. cleared that day and never gets to actually age. The inflow column essentially represents 0 days old

Gray columns are locked

How can I correctly populate the buckets and produce no negatives?

I think this is what you're after.

In H4: =MAX(SUM(G3:H3-$A$3),0)

In G4: =MAX(SUM(F3:H3)-$A$3-SUM(H4),0)

In F4: =MAX(SUM(E3:H3)-$A$3-SUM(G4:H4),0)

In E4: =MAX(SUM(E3:H3)+D4-$A$3-SUM(F4:H4),0)

Then fill down.
In those formulas $A$3 is the productivity. Hope that helps.

Step1: Guiding principle for each bucket:

If a bucket has more than the remaining productivity for that bucket for that day, then (BUCKETVALUE - REMAININGUNITS), else 0.

We will deploy this IF logic in steps 3 - 6.

Step 3 defines what is REMAININGUNITS for each bucket, which is again used in subsequent steps 3 - 6

Step2:
Now let's define REMAININGUNITS as the amount of units that can be cleared for the day for a bucket

So, REMAININGUNITS for Bucket 4 is PRODUCTIVITY, i.e. potentially all of A3 can be used to clear Bucket 4                               

    =$A$3                           

Remaining units for bucket 3 = PRODUCTIVITY - Actual Reduction in Bucket 4                              

    =($A$3-(H3-H4))                         

Remaining units for bucket 2 = PRODUCTIVITY - Actual Reduction in Bucket 4 - Actual Reduction in Bucket 3                               

    =($A$3-(H3-H4)-(G3-G4))                         

Remaining units for bucket 1 = PRODUCTIVITY - Actual Reduction in Bucket 4 - Actual Reduction in Bucket 3 - Actual Reduction in Bucket 2                                

    =($A$3-(H3-H4)-(G3-G4)-(F3-F4))                         

Step3:
Clear as much of 4th bucket as possible,

H4=IF(H3>$A$3,H3-$A$3,0)

Step4:
Clear as much of 3rd bucket as possible,

G4=IF(G3>($A$3-(H3-H4)),G3-($A$3-(H3-H4)),0)

Step5:
Clear as much of 2nd bucket as possible,

F4=IF(F3>($A$3-(H3-H4)-(G3-G4)),F3-($A$3-(H3-H4)-(G3-G4)),0)

Step6:
Clear as much of (1st bucket + INFLOW) as possible,

E4=IF(E3+D3>($A$3-(H3-H4)-(G3-G4)-(F3-F4)),E3+D3-($A$3-(H3-H4)-(G3-G4)-(F3-F4)),0)

Step7:
Fill the formulaes from Row 4 to the remanining rows