Sample Report: Expense Variance


Demonstrates using NLG Studio to create a report on variance of expense figures.

Enter your Studio email below to access this sample

 

Click Access Download above to get this categorical expense variance sample project for use within NLG Studio. Scroll down for the sample’s documentation—including example code, the full JSON schema, and a sample report.


Sample Report: Expense Variance

 

Introduction

This categorical expense variance (CEV) project is used to compare variance within an organization’s expenses across different comparison models. A single report can describe one or more comparisons, such as actual versus budget or month versus previous month. Within each comparison, the report is broken down into the different organizational units (e.g. different offices). For each model, we describe variance across two dimensions: Line of Business and Expense Category.

Lines of Business correspond to particular functions of the company. For example, an insurance company might have different lines of business (LOBs) called Home Insurance, Travel Insurance, and Car Insurance.

Categories (or Expense Lines) represent what the business is spending money on. For example, Personnel is a typical expense category. Within this category there can be subcategories such as Benefits, Payroll, and Bonuses.

The data is split initially on comparisons, with each comparison representing the variance between two different models. These models can be about plans (e.g. actual versus budget) or they can be historical (e.g. month-over-month, year-over-year). Within each comparison, the data is broken down into high-level organizational units (org units), for example: US, UK, Corporate. The org unit for a particular model is broken down into a hierarchical tree of categories. Each node in the tree has a total variance, and can be broken down into the LOBs.

 

Sample Report

This report is generated from the sample data that comes with the sample CEV project.

Expense Summary: Q3 2017 vs Forecast – Outcome unfavourable by (£4.0MM)

Main drivers:

Corporate outcome unfavourable by (£4.2MM)

  • The expense lines that caused this were Central Services (£2.1MM) (primarily IT Support related), Personnel & Benefits (£1.7MM) (Bonuses (£1.6MM) and Benefits (£499.7K)) and Other General (£563.4K) (Brokerage Related Fees (£451.7K) and General & Admin (£111.7K)).
  • The business units behind this were Program Development (£3.7MM) and Tech & Analysis (£640.0K).
  • The most significant driver, Program Development, was led by Personnel & Benefits (£1.9MM) and Central Services (£1.5MM).

US outcome on forecast

Expense Summary: Q3 2017 vs Q3 2016 – Fell by £8.6MM

Main drivers:

Corporate fell by £8.3MM

  • The expense lines that contributed most to this were Central Services £6.2MM (IT Support £5.6MM and HR £356.2K) and Personnel & Benefits £1.5MM.
  • The business units that drove this were Program Development £7.0MM and Operations £1.1MM.
  • The biggest driver, Program Development, was driven by Central Services £6.1MM.

US decreased by £238.8K

  • The expense lines behind this were Personnel & Benefits £601.6K and Central Services £395.8K (primarily HR related). However, these were largely offset by Equipment (£814.8K) and Professional Fees (£132.1K).
  • The business unit that contributed most to this was Operations £1.6MM but it was largely offset by Procurement (£1.1MM) and Program Development (£276.1K).

Report Structure

Comparison Description

The CEV report is broken down into different sections for different comparisons. Each comparison has a headline naming the two models and describing overall performance of the two models. How the performance is described depends on whether the comparison is plan-based (the default), or historical. Plan-based comparisons are described in terms of favourability—for example, whether the actual amount spent is better than the planned spend. Historical comparisons are described in terms of increases or decreases. In either case, if there isn’t a notable difference (i.e., total change is below the overallNoChangeThreshold), then the description will be skipped.

Within each comparison is a section for each of its org units. Org units break the company down into large segments, such as different countries. Each unit has a high-level description describing how it performed when comparing the two models. If the two models are aligned (i.e., if total change is below the overallNoChangeThreshold), then they will be described as “on forecast” or “unchanged”.

Org Unit Description

Org units that are displaying variance are broken down into more detail. There are three possible bullet points: Categories Description, Lines of Business Description, and Most Significant Driver Description. These descriptions slice the data in different ways and discuss significant drivers and offsets. Drivers are partitions of data that support the overall variance, while offsets are partitions that have varied in the opposite direction to the overall variance. A driver or offset is significant (and as such can be included) if it makes up more than the noChangeThresholdPercentage of all drivers.

Categories Description. This details how the different categories or expense lines of the org unit have contributed to the variance. This bullet point is only included if the org unit has subcategory data. Each significant category driver will be listed alongside its variance, and any subcategories that significantly contributed to the category will be listed as well. If there were any significant offset categories, then the report will mention what they are.

Lines of Business Description. This details how the LOBs of the org unit have contributed to the variance. This bullet point is only included if the org unit has LOB data. Each significant LOB driver will be listed alongside its variance. If there were any significant offset LOBs, then the report will mention what they are.

Most Significant Driver Description. This looks across both lines of business and categories, provided there are subcategories with LOB data. It describes the biggest single driver (whether it’s a LOB, category, or subcategory), then breaks it down across the other dimension. That is, a LOB driver will be described in terms of category, while a category driver will be described in terms of LOB.

 

Example Data

The example that follows shows the data structure. (This is an excerpt; it does not represent the full sample report.)

{
    "metadata": {
       "overallNoChangeThreshold": 250000,
       "unit": "GBP",
       "noChangeThresholdPercentage": 10,
       "largeOffsetThresholdPercentage": 50,
       "slightOffsetThresholdPercentage": 10
    },
    "comparisons": [{
       "type": "plan",
       "baseName": "Q3 2017",
       "contrastName": "Forecast",
       "varianceData": [{
          "orgUnit": "corporate",
          "totalVariance": {
             "name": "Total",
             "total": -4188450.2,
             "linesOfBusiness": [{
                "name": "Procurement",
                "variance": 130505.551
             }],
             "subcategories": [{
                "name": "Personnel & Benefits",
                "total": -4188450.2,
                "linesOfBusiness": [{
                   "name": "Procurement",
                   "variance": 130505.551
                }],
                "subcategories": [{
                   "name": "Salaries",
                   "total": -4188450.2,
                   "linesOfBusiness": [{
                      "name": "Procurement",
                      "variance": 130505.551
                   }]
                }]
             }]
          }
       }]
    }]
}

 

JSON Schema

ROOT

Path Example Description
metadata
An object containing settings that change how the report is created.
metadata/ overallNoChangeThreshold
250000
The maximum amount of variance for an org unit classed as “on track”. This is an absolute amount.
metadata/
unit
USD
The unit of currency for the report as an ISO 4217 code. Note that this is for presentation only; all numbers should already be in this currency. Supported codes are: USD, AUD, CAD, CHF, CNY, EUR, GBP, JPY, NZD, ZAR.
metadata/ noChangeThresholdPercentage
10
The minimum percentage of total drivers that a driver or offset must have before it is reported.

Optional, defaults to 10.

metadata/ largeOffsetThresholdPercentage
50
The minimum percentage of total drivers that must be offset before the offsets are classed as “largely” offsetting the drivers.

Optional, defaults to 50.

metadata/ slightOffsetThresholdPercentage
10
The maximum percentage of total drivers that can be offset for the offsets to be classed as “slightly” offsetting the drivers.

Optional, defaults to 10.

comparisons
A list of the different model comparisons in the report. For example, actual vs. plan, current month vs. previous month. These become separate sections of the report. Each item is a COMPARISON object.

 

COMPARISON

Path Example Description
type
plan
The type of comparison being made. Used to choose how the variance is described.

Options:

plan – Comparing against a forecast model, described as favourable/unfavourable.

historical – Comparing with a previous data point, described as decrease/increase.

Optional, defaults to plan.

baseName
Q3 2017
The name for the main source of data in a comparison. Part of the title.
contrastName
Forecast
The name for the secondary source of data in a comparison. Part of the title.
varianceData
US, UK, Australia
A list of data for different org units. These become separate headers under the comparison title. Each item is a DATASET object.

DATA SET

Path Example Description
orgUnit
UK
The name of the org unit this data set represents.
totalVariance
The high-level node representing all data in this data set. This is a DATA object.

DATA

Path Example Description
name
Marketing
The name of this data. Typically, this is either “total” (for all data in an org unit), the category name, or the subcategory name.
total
418845
The total variance between the main source of data and the contrasting data across all lines of business.
linesOfBusiness
A list of data for different LOBs. These should add up to the total. Each item is a LOB object.

Optional, only needed if you want this type of analysis.

subcategories
A list containing the data segmented into sub-categories. For example, the total data could be broken down into Personnel, Equipment, and Marketing. Personnel could then have subcategories called Salaries and Bonuses. Each item is another DATA object.

Required for the top-level DATA object in a DATASET, Optional for all other data.

 

LOB

Path Example Description
name
London
The name of the LOB this data-point represents.
variance
130505
The variance between the main source of data and the contrasting data for this particular line of business.