Count back for turnover

The difficulty is to find how many months you need to cover your AR days. For that, the only way in pure Anaplan is to calculate every possible way (another way would be to use the optimizer).

Here's the result:

and here's the formulas: 

You can see this is pretty standard. I'm using MOVINGSUM to calculate the sum of the past x months, compare that to the current AR, if it is higher, then I take the necessary days of the last months in the calc using LAG. DAYSINMONTH is very helpful here, giving us the numbers of days in the month.

Now as I've said the trick is to know how many months we need. That's achieved with another module:

You see I've created a list of items and I'm using their values to run the MOVINGSUM as many times as we have items. We then MOVINGSUM is higher than the AR, I return the value of the item, which is the number of month we need (-1 because inclusive of current). Then I set the summary method of that line item to MIN which is the result we want. That's why when the MOVINGSUM is below the AR I return 9999 so it is ignored by the MIN. Then we can bring this value into the "normal" module and use it for our calculation.

The drawbacks here are:

- it's not entirely dynamic as it relies on the items in the list. If you know that you average number of months is 13, create items from 5 to 20, depending on variability, and it should encompass all possibilities

- this can be quite size heavy if you are doing it by multiple countries / products