Bringing Business Variance Analysis to Life with SAP Analytics Cloud Planning

Hello,

My name is Filippo Naggi and I work at NIMBL as a Solution Architect specializing in SAP Financial Planning. My primary role is to provide support to Finance Teams, leveraging the latest cutting-edge technologies developed by SAP.

In this blog, I would like to dive into Variance Analysis using SAP Analytics Cloud Planning.

If you are interested in this topic, there is also a blog from my colleague Brian, who explains how to perform Variance Analysis with  SAC Formulas.

Let’s get this started!

Variance analysis is a process of analyzing the difference between expected and actual outcomes in a given situation. It is commonly used in finance and accounting to track and evaluate the performance of an organization, project, or individual. The main objective of variance analysis is to identify and explain the causes of differences between expected and actual results, in order to improve decision-making and performance.

In financial terms, variance analysis typically involves comparing actual financial results, such as revenues and expenses, with the budgeted or forecasted results. The difference between the two is called the variance, and it can be positive or negative, indicating whether the actual result exceeded or fell short of the expected result. By analyzing the variance, managers can identify the factors that contributed to the difference and take appropriate actions to improve performance.

Variance analysis can also be applied in other fields, such as project management, quality control, and operations management, to monitor and evaluate performance and identify opportunities for improvement. Overall, variance analysis is a valuable tool for evaluating performance, identifying areas for improvement, and making informed decisions.

Volume, mix, and rate effects are important components of variance analysis, and they are used to explain the differences between actual and expected results. Each effect represents a specific factor contributing to the variance between actual and expected results.

1. Volume Effect: The volume effect is a type of variance that results from changes in the number of products or services sold. This effect is also known as the “quantity” effect, and it occurs when the actual volume of output is different from the budgeted or expected volume. The volume effect is calculated by multiplying the actual volume by the budgeted or expected rate.
2. Mix Effect: The mix effect is a type of variance that results from changes in the product or service mix. This effect is also known as the “product” effect, and it occurs when the actual mix of products or services sold is different from the budgeted or expected mix. The mix effect is calculated by multiplying the budgeted or expected rate by the difference between the actual mix and the budgeted or expected mix.
3. Rate Effect: The rate effect is a type of variance that results from changes in the price or rate of products or services sold. This effect is also known as the “price” effect, and it occurs when the actual price or rate is different from the budgeted or expected price or rate. The rate effect is calculated by multiplying the budgeted or expected volume by the difference between the actual rate and the budgeted or expected rate.

In summary, the volume, mix, and rate effects of variance analysis help to explain the differences between actual and expected results by isolating and quantifying the impact of specific factors. Understanding these effects is important for managers to identify areas of improvement and make informed decisions to improve organizational performance.

Let’s now jump to SAC and deep dive into the Variance Calculation

Business folks, stay with me!
In a few paragraphs, we will showcase the Variance Calculations to analyze our Actual result compared to the Budget.

This is our SAC data model:

The data is going to be organized by Business Unit and Product, with a standard Account dimension.

The Total variance can be calculated as:

DATA([d/Account] = "TOTVARIANCE", [d/DataSource] = "H1P") = RESULTLOOKUP([d/Account] = "Sales", [d/DataSource] = "Input") - RESULTLOOKUP([d/Account] = "Sales", [d/Version] = "public.Budget", [d/DataSource] = "Input")

To calculate the Variance denominator we can use this script:

DATA([d/DataSource] = "H2P", [d/Product] = [d/Product].[p/H2P]) = RESULTLOOKUP([d/DataSource] = "Input")

each product will have one or more attributes ( in this case H2P), which will be used to identify the group to be used for both Volume and Mix Variances

Volume Variance will be:

// VOLUME
DATA([d/Account]="VAR1-VOL",[d/DataSource]="H1P") = 
(  RESULTLOOKUP([d/Account] = "Volume", [d/DataSource] = "Input")
 -
 RESULTLOOKUP([d/Account] = "Volume", [d/Version] = "public.Budget", [d/DataSource] = "Input") 
)
*
(RESULTLOOKUP([d/Account]="Sales",[d/DataSource]="H1P",[d/Product]=[d/Product].[p/H1P])
 /
 RESULTLOOKUP([d/Account]="Volume",[d/DataSource]="H1P",[d/Product]=[d/Product].[p/H1P]))

Mix Variance will be:

// MIX
DATA([d/Account]="VAR2-MIX",[d/DataSource]="H1P") = 
( RESULTLOOKUP([d/Account] = "Volume", [d/DataSource] = "Input") 
 -
  RESULTLOOKUP([d/Account] = "Volume", [d/Version] = "public.Budget", [d/DataSource] = "Input") 
)
*
(( RESULTLOOKUP([d/Account] = "Sales",[d/DataSource] = "Input")
 /
 RESULTLOOKUP([d/Account] = "Volume",[d/DataSource] = "Input"))
 -
 (RESULTLOOKUP([d/Account]="Sales",[d/DataSource]="H1P",[d/Product]=[d/Product].[p/H1P])
 /
 RESULTLOOKUP([d/Account]="Volume",[d/DataSource]="H1P",[d/Product]=[d/Product].[p/H1P])))

Rate Variance will be:

//RATE
DATA([d/Account]="VAR3-RATE",[d/DataSource]="H1P") = 
(
 RESULTLOOKUP([d/Account] = "Sales",[d/DataSource] = "Input")
 /
  RESULTLOOKUP([d/Account] = "Volume",[d/DataSource] = "Input")
- 
 RESULTLOOKUP([d/Account] = "Sales",[d/Version]="public.Budget",[d/DataSource] = "Input")
 /
 RESULTLOOKUP([d/Account] = "Volume",[d/Version]="public.Budget",[d/DataSource] = "Input")
 
)
*
 RESULTLOOKUP([d/Account] = "Volume",[d/Version]="public.Budget",[d/DataSource] = "Input")

Once the calculations are active, we can finally create a dashboard and test our Variances:

Let’s do a managerial review of the performance of BAG001.

The result is :

There is an Actual Sales Result of 60$ compared to a Budget of 25$, that’s an impressive +35$ Total Variance.

Let’s open it into the three effects:

Volume Variance: +40$, because we were supposed to have a Budget of 5 units, and we ended with an Actual of 10.

Rate Variance: +5$, because we were supposed to sell with 5$/unit rate in Budget, and we ended up with an actual of 6$/unit

and finally…..

Mix Variance: -10$, because we were supposed to sell more BOX001 ( which has a Rate of 10$/Unit) and we ended up selling more BAG001 (with a Rate of 6$/Unit).

Hope you liked this blog:  please don’t forget to hit the Like button!

Feel free to ask any question and stay tuned for fresh blogs on Business, Finance, SAP BPC, and SAC.