Calculating the average of a balance sheet item for quarterly / annual totals

OliverMunns

I have a number of balance sheet accounts (e.g. Closing Receivables*) for which I need to calculate the average value.

 

This should be calculated as follows:

 - Monthly: average between the current month and prior month value

 - Quarterly Total: average of the last 4 data points

 - Annual Total: average of the last 13 data points

 

*note that Closing Receivables will have 'Sum, Time: Closing Balance' as its aggregation setting

 

Below is a worked example:

 

Currently my method to do this seems complex:

 - Create an 'Opening Receivables' line item with calculation 'previous(Closing Receivables)' and aggregation 'Sum, Time: Opening Balance'

 - Create a 'Closing Receivables (time sum)' line item with calculation 'Closing Receivables' and aggregation 'Sum, Time: Sum'

 - Create a 'Time Aggregation' line item with calculation '1' and aggregation 'Average, Time: Sum'

 

Then apply the following calculation to the Average Receivables line item with aggregation 'Formula':

(Opening Receivables + 'Closing Receivables (Time Sum)') / (Time Aggregation + 1)

 

I am hoping that someone can suggest a simpler way to achieve this? My particular concern is that for every closing balance that I need an average for, I will need to create 2 additional line items to feed into the average calc (the Time Aggregation line item can be shared).

andrewtye

Have you considered using movingsum? It might help with the average element.

OliverMunns

Unfortunately, I don't think that MovingSum is an option as if you use it in the formula, you cannot then set the time aggregation setting so 'formula'. And if you have anything else as the time aggregation setting, you don't get the correct quarterly/FY values.

rob_marshall

@OliverMunns 

 

First, I agree with @andrewtye in that you should be using MovingSum instead of TimeSum as using TimeSum with a Time list can cause performance issues.

 

https://help.anaplan.com/13d8b551-4e13-413b-a39f-f25579d21e38-TIMESUM

 

 

Secondly, have you considered using the Ratio as a summary option? 

MattHarvey

@OliverMunns - Hi - how did you get on with this? We are trying to do the same so just wondered if you had made any progress on it. Also did your method work when doing YTD averages - I think it would but wanted to check if you had experience of it.

OliverMunns

@MattHarvey Hi, we're still using the same method for Average Receivables and other balance sheet accounts. I think the complexity on this is that for months we want a 2-point average, for quarters a 4-point and for FY a 13-point. Would love a simpler solution but haven't found it yet.

For YTD Average Receivables we did find something simpler. This is were we want:

Jan 2-point average

Feb 3-point

Mar 4-point

Q1 4 point

Apr 5-point

…

Q2 7-point

…

FY 13-point

For this we've used:

(MOVINGSUM(Closing Receivables, -YEARTODATE(1) + 1, 0) + LAG(Opening Receivables, YEARTODATE(1) - 1, 0)) / (YEARTODATE(1) + 1)

Time summary = closing balance

Hope that this is helpful. Let me know if you find anything on Average Receivables

MattHarvey

Ah got it - thanks. Will let you know if we find anything else.