Variance impact calculator

AnyaS · July 9

Author: Anya Skvortsova is a Certified Master Anaplanner and CoE Lead at Asana.

Purpose

FP&A is often asked to calculate the impact of variables on the total variance. There is more than one way to go about it. I’d like to share a variety that uses the typical Salary/HC reporting modules output to define the contributions of HC and Average Salary impact on the Period over Period variance amount in Salary dollars.

UX setup

As a data source, for simplicity of the illustration, we’ll use the module where the data is input. In the “real world” this would be linked to the outputs of forecast/actual/snapshots of the model.

The module is dimensioned by Department, custom version dimension, and the list containing Salary and HC list items. The line item for data inputs is called “Data Input”, the other line item, “Data HC Calc”, is needed for properly calculating quarter level average and impact. The difference between the two line items is the summary method setting.

The second UX component is the set of period/version selectors (the detailed set up for those is available in other articles of Anaplan Community and in the examples provided in a couple of reporting apps available for download from the Anaplan Modeling Showcase/App Hub).

Backend mechanics

Reporting list (Salary, HC…) and staging module with “BvA” line items (Version 1 Month 1…) is used to structure the data in the layout needed for the subsequent calculations of the variance impact. The tricky part is to get the proper data show up for HC on the quarter level and for the Average salary at the Dept total level.

Quarter case: For the reporting purposes the HC for the quarter = the month ending period headcount, however, for the average calculation we need to reference the total for three months of that quarter. On the monthly basis, we need to reference that month data to calc average.

Dept total case: by default, this hierarchy will generate sum of average salaries at the top level, while the simple average needs to be calculated so we are using the syntax to call out Dept Total piece of calc and then apply “Formula” summary (we cannot use “Average” Summary method as our line items are “BvA” type and Avg calc is the list item).

Example of logic in “Version 2 Quarter 2” (blue prints attached below):

We solve for Dept total in the next calculation module brining the looked-up data for Dept total for Salary and HC from stage and pulling calculated Dept total average salary. Summary method is set to Formula to override “natural” roll up.