Count back for turnover

mikhivin

Hello everybody,

 

I've come across a task to count some KPI's based on count back method.

For instance, I need to count how many days it takes a company to cover its Account Receivable (AR) amount with monthly Revenues.

It means that if a company has AR at Jan end = $ 2000 and Revenues as:

current month (Jan) = $ 200

month-1 (Dec)= $ 400

month-2 ( Nov) = $ 600

month-3 (Oct) = $ 700

month-4 (Sep) = $ 500

 

then as per this methodology AR turnover in days will be = 31 (Jan) + 31 (Dec) + 30 (Nov) + 31 (Oct) + 6 (Sep) [30 (Sep) * 100(remaining AR to be covered by Sep revenue) / 500 (Sep revenue)] = 129 days

 

Could you please recommend the best way to calculate such things if I have model where the lowest time frame is month and this indicator is to be calculated for each month?

 

Thank you.

 

nathan_rudman

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