Variance reporting is core to all FP&A reporting packs yet it is devilishly tricky to create decent looking reports in Anaplan...Here's how we do it!
❔ Why is it so painful ❔
As line items are used to construct a calculation and only one axis can contain the line items dimension calculations can only flow across one axis.
With variance reporting we need to apply calculations across two. Not only do we need to calculate down the P&L as we derive gross and net margin, ratios and growth rates we also need to calculate across as we determine the movement against forecast, budget or any other comparator such as prior year.
This is how we do it...
REP01 - a working module containing our header, entered as line items ( Actual, Forecast, Budget, Prior Year, Act v For, Act v Budget, CY v PY ).
LISS_REP01 - line item subset derived from Actual, Budget, Forecast and Prior Year of REP01.
REP02 - final report module containing our P&L line items (Revenue, Direct Costs, Gross Margin, Overheads and Net Profit ).
LISS_REP02 - line item subset derived from Revenue, Direct Costs and Overheads).
Here's the crazy interesting bit....
We add the LISS_REP02 to REP01 and LISS_REP01 to REP02.
What we are left with is REP01 dimensioned by our line item headers ( Actual, Forecast, Budget, Prior Year, Act v For, Act v Budget, CY v PY ) and our P&L line items in the form of LISS_REP02.
We can now map our actual, forecast and budget data into REP01 using a mapping pointing any relevant combination of source dimensions to the correct P&L line. We can use LAG or OFFSET to derive prior year and a simple calculation for our variances.
With REP02 we have our original P&L as line items now combined with our LISS_REP01 headers. For those lines which have been mapped into REP01 we use COLLECT() to pull the data over. Other summary lines, ratios and percentages can be calculated. Line items can also be individually formatted and styled.
REP02 is our final variance report.
The benefits of this approach over others is the ability to individually format and style P&L lines. The report therefore looks and acts more intuitively, is more engaging to our end users and contains other calculations such as growth rates, percentages and other ratios.
