Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Current Month Expense Variance Analysis with YTD: Behind the Scenes

If you have seen a demo of our software or if we showed you an example of a matrix HTML report, chances are you have seen this report; it is pretty, has really cool formatting and all kinds of toggles and switches to make it dynamic. You can see a screenshot of this below: 

What if you wish to make your own version or modify this existing report to include things that you care about? 

Below is the example set up for this type of report:

X axis (Horizontal axis): 2 tiers of elements, upper tier contains time elements and the lower tier has versions and difference/variance elements.

  • Single month and a Year To Date element are both on the upper tier of the horizontal axis.

  • Beneath that, we have a few elements that are versions and custom difference calculations. 

    • The first version elements is our Actuals, the second one is our plan or budget version.

    • The remaining 4 elements are various difference calculations for variance displays: % Variance, Zone display, % variance Previous Month, and % Variance Previous Year. All of these are connected to a parameter which we will look at later.

    • This set up would mean that we get 2 section of the report with a total of 12 columns, 6 for single month and 6 for Year to Date.

Y axis (vertical axis):

  •  Simply contains your accounts, whichever accounts you wish to use for this report, expenses are popular for this type of report. 

  • You can use account attributes here as well.

Filters:

  •  Levels are included into the filter for an overall cleaner look of the report.

  • Currency is also included to make the report more dynamic.

Parameters:

  • The levels from the filters are connected to the parameter.

  • Currency is also linked to the parameter.

  • Every time period from the horizontal axis are connected to the same time parameter so that they both update at the same time as you change the parameter.

  • The Actuals are connected to one version parameter, called "Version 1" and the other version, your plan or your forecast/budget is connected to a parameter "Version 2"

  • The variances are also connected to these parameters so that the calculations follow suit with the version values. 

Now, Let's take a look at some of the specific properties behind the elements. On each of them, you can right-click and select "Properties":

  •  Singular month element on the upper tier:

    • Properties tab: It is connected to the parameter "Time".

    • Style tab: Under Header, the fill is blue, but you can pick whatever color you wish. Everything else is set to default.

    • Conditional formatting tab: stays the same.

    • Numbers tab: set to default on all items (Uses account's format).

  • Year to Date element on the upper tier: 

    • Properties tab: Stratum is set to "Year", Year to date is set to the same month as the previous singular month element, and it is connected to the parameter "Time".

    • Style tab: Under Header, the fill is blue, but you can pick whatever color you wish. Everything else is set to default.

    • Conditional formatting tab: stays the same.

    • Numbers tab: set to default on all items (Uses account's format).

  • 1st version element on the lower tier:

    • Properties tab: It is connected to the parameter "Version 1".

    • Version options tab: Stratum is set to stratum "Month" Offset is 0.

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish. Everything else is set to default.

    • Conditional formatting tab: stays the same.

    • Numbers tab: set to default on all items (Uses account's format).

  • 2nd version element on the lower tier:

    • Properties tab: It is connected to the parameter "Version 2".

    • Version options tab: Stratum is set to stratum "Month" Offset is 0.

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish. Everything else is set to default.

    • Conditional formatting tab: stays the same.

    • Numbers tab: set to default on all items (Uses account's format).

  • 1st Variance element (a difference calculation under "Calculations>>Difference "):

    • Properties tab: Label is "% Var", "Display as" is set to Percent. The box for reversing the sign is unchecked.

    • Difference Options: Subtract version is set to your original plan or budget version that is already on the report, stratum is Month, no offset and it is connected to parameter "Version 2"; From Version is set to Actuals, Stratum is Month, no offset and it is connected to parameter "Version 1".

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish. Data is set to be bold.

    • Conditional formatting tab: There are 5 rules there: When the value is <= -10, highlight the area with red; When the value is > -10, highlight the area with yellow; When the value is > 1, No highlight; When the value is >=1, highlight the area with yellow; When the value is >= 10, highlight the area with red. No rollup rules.

    • Numbers tab: set to default on all items (Uses account's format) on everything but the Magnitude, the magnitude is set to "Show value".

  • 2nd Variance element:

    • Properties tab: Label is "Zone", "Display as" is set to Percent. The box for reversing the sign is unchecked.

    • Difference Options: Subtract version is set to your original plan or budget version that is already on the report, stratum is Month, no offset and it is connected to parameter "Version 2"; From Version is set to Actuals, Stratum is Month, no offset and it is connected to parameter "Version 1".

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish.

    • Conditional formatting tab: There are 7 rules there: When the value is between -0.5 and 0.15, show a green check-mark icon; When the value is between -0.5 and -0.15, show a green flag icon;  When the value is between 0.15 and 0.5, show a green flag icon; When the value is between -1 and -0.5, show a yellow flag icon; When the value is between 0.5 and 1, show a yellow flag icon; When the value is <= -1.01, show a red flag icon; When the value is >= 1.01, show a red flag icon; No rollup rules.

    • Numbers tab: set to default on all items (Uses account's format) on everything but the Magnitude, the magnitude is set to "Show value".

  • 3rd Variance element: 

    • Properties tab: Label is "% Var PM", "Display as" is set to Percent. The box for reversing the sign is unchecked.

    • Difference Options: Subtract version is set to Actuals, stratum is Month, Offset is set to 1 (Backward) and it is connected to parameter "Version 1"; From Version is also set to Actuals, Stratum is Month,, no offset and it is connected to parameter "Version 1".

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish.

    • Conditional formatting tab: No rules.

    • Numbers tab: set to default on all items (Uses account's format) on everything but the Magnitude, the magnitude is set to "Show value".

  • Final variance element: 

    • Properties tab: Label is "% Var PY", "Display as" is set to Percent. The box for reversing the sign is unchecked.

    • Difference Options: Subtract version is set to Actuals, stratum is Month, Offset is set to 12 (Backward) and it is connected to parameter "Version 1"; From Version is also set to Actuals, Stratum is Month,, no offset and it is connected to parameter "Version 1".

    • Style tab: column widths is set to 70 pixels, under Header, the fill is light blue, but you can pick whatever color you wish.

    • Conditional formatting tab: No rules.

    • Numbers tab: set to default on all items (Uses account's format) on everything but the Magnitude, the magnitude is set to "Show value".

  • Last column is just a blank spacer.

On the vertical axis, we have only the accounts, set up below:

  • Properties tab: No changes.

  • Style tab: No changes.

  • Conditional formatting tab: No changes.

  • Number tab: Display zeroes is set to 0; 1000 separator is set to "Show 1000 separator"; the magnitude is set to "Show value".

To double check that everything is connected properly to the parameters, refer to the parameters area and note the following connections:

  •  Level>>Top level.

  • Time>> Month & Year to Date.

  • Version 1>>Actuals, % Var, Zone, % Var PM, % Var PY.

  • Version 2>>Budget version, % Var, Zone.

  • Currency>> USD - United States of America, Dollars, or your instance's corporate currency to start with. 

Lastly, let's review the report properties overall. Click on the gear icon at the top of the screen: 

Display tab is the only one we really need to worry about, settings are as follows: 

Of course, you can deviate from this set up to include the things you need on the report, perhaps additional segments or additional custom calculations. These are merely guidelines for an example of a somewhat advanced variance report.