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).

  1. 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:

  2. Save the Account dimension.

  3. 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.

  4. In the Calculation Definition, click .

  5. 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.

  1. Check that your syntax is correct, and click OK.
  2. Save the Version dimension.

Build a comparison report

  1. To build a report for variance analysis, open Ad Hoc Analysis and create a new data view.
  2. 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.

  3. Save the report template.

    When you run the report, it produces the data view described above.