Use case: Compare actuals-to-budget using variance calculations
This use case describes how to create a variance calculation for actuals versus budget comparisons. The end result is a report that shows the following data:
January | January | January | February | February | February | March | March | March | Qtr 1 (+) | Qtr 1 (+) | Qtr 1 (+) | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
Actual | Plan | Variance | Actual | Plan | Variance | Actual | Plan | Variance | Actual | Plan | Variance | |
Gross Sales (-) | 433,362 | 2,274,977 | -1,841,615 | 904,098 | 2,129,063 | -1,224,965 | 1,327,575 | 6,357,212 | -5,029,636 | 2,665,035 | 10,761,251 | -8,096,216 |
Revenue from product sales | 1,727,969 | -1,727.969 | 1,700,756 | -1,700,756 | 5,076,972 | -5,076,972 | 8,505,697 | -8,505,697 | ||||
Revenue from services | 280,263 | 319,674 | -39,411 | 827,548 | 314,640 | 512,908 | 1,097,927 | 939,240 | 158,687 | 2,205,738 | 1,573,554 | 632,184 |
Other Revenue | 153,099 | 227,333 | -74,234 | 76,550 | 113,667 | -37,117 | 229,649 | 341,000 | -111,351 | 459,298 | 682,000 | -222,702 |
Cost of Sales (-) | 1,134,527 | 1,134,527 | 1,115,663 | 1,115,663 | 3,306,887 | 3,306,887 | 5,557,077 | 5,557,077 | ||||
Cost of Goods Sold | 1,123,180 | 1,123,180 | 1,105,492 | 1,105,492 | 3,300,032 | 3,300,032 | 5,528,703 | 5,528,703 | ||||
Other Direct Charges | 11,347 | 11,347 | 10,171 | 10,171 | 6,855 | 6,855 | 28,373 | 28,373 |
The business problem
If an organization is failing to meet its planned budget by a significant amount, it is vital to act appropriately to reduce the variance amount. Otherwise, creating a budget would be pointless.
Comparing actuals to budget is an important way of measuring how an organization is progressing through the financial year and whether it is on track to meet its long-term goals.
The solution
Use the Version and Account dimensions to an intelligent variance calculation for actuals versus budget comparisons
The benefits
An actuals to budget comparison can be examined quickly and easily and thus lends itself to identifying areas that need more funding, as well as determining whether the budget is realistic.
How it's done
To demonstrate how an actuals to budget comparison can be implemented, this use case starts with the assumption that plan data already exists in the budget version.
There are two parts to the process. The first part is to prepare the dimensions as follows:
- Ensure that the Account and Version dimensions have the necessary settings.
- Add the formula to the Version dimension.
The second part is to build the comparison report.
Prepare the dimensions and create the variance calculation
Before you can create an intelligent calculation, you must ensure that all the accounts in the Account dimension have been assigned an account type of either Revenue or Expense, as this enables the calculation to determine whether to subtract budget from actual (for a Revenue account) or to subtract actual from budget (for an Expense account).
-
In Model Manager, in the Account dimension, set the Account type property for each account. The following example shows a Gross Sales account being set to an account type of Revenue:
- Save the Account dimension.
-
In the Version dimension, create a Variance version. Assign this new version a key and an appropriate name, and set its Version type to Calculated:
Note: It is not necessary to set a Start Date or End Date for this version.
- In the Calculation Definition, click .
-
In the Formula Editor, create the following variance formula:
IF(IsRevenue(),[ACT]-[PLAN01],[PLAN01]-[ACT])
Note: The formula applies the appropriate calculation depending on the type of account selected. For example, if a revenue account such as Revenue from Product Sales is selected, the variance version calculates the variance by subtracting the budget data from actual data. If an expense account such as Cost of goods sold is selected, the variance is calculated by subtracting the actuals data from the budget data.
- Check that your syntax is correct, and click OK.
- Save the Version dimension.
Build a comparison report
- To build a report for variance analysis, open Ad Hoc Analysis and create a new data view.
- Define the layout of the data view:
- In the Choose Layout tab, drag the Version dimension to Columns.
- In the Choose Members tab, select Version and move the Actuals, Plan, and Variance members to the right-side panel.
-
Save the report template.
When you run the report, it produces the data view described above.