Using formulas that operate on Versions, you can create a classic variance report, containing Actual, Budget, Forecast, and Variance figures for the current month, year-to-date, or full year. You can also create a comparison with the previous year, or show year-on-year percentage growth.
Updates to this kind of report are automated by changes to the Current Period. When the Current Period is updated, all calculations referencing it will update values in variance reports.
Current Period is configured on the Model Calendar tab in Model Settings > Time. This is also where you can choose the aggregated values you require: Quarter; Half-Year; Year to Date Summary; Year to Go Summary; Total of All Periods.
The following video shows you how to create a current month, year-to-date, and year-over-year variance report when you have a timescale spanning multiple years.
Variance Reports and Formulae
You can apply formulae to Versions. Formulae are displayed:
- on the Versions page in Model Settings;
- in the Formula Editor when a cell is selected.
The SELECT function is frequently used in variance reports, and can only be used on line items. The optimal way to structure the module is to have line items on columns and a line item subset on rows.
The COLLECT () function pulls values from the source(s) of the line item subset. The line item subset can then be used as a dimension in a model.
The CURRENTPERIODSTART and CURRENTPERIODEND functions are used to retrieve Current Period values. Calculations can also be created to increase or reduce number of days around the Current Period.
This example calculates a value for the Current Period plus 42 days.
START() < CURRENTPERIODSTART() + 42 AND START() >= CURRENTPERIODSTART()