Calculating the average of a balance sheet item for quarterly / annual totals
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).
Comments
-
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.
0 -
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?
0
