Skip to Content
Business Trends
Author's profile photo Filippo Naggi

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nikhil Anand
      Nikhil Anand

      Filippo Naggi

      Great write up explaining different type of Variances.

      For anyone who is finding it difficult to understand how each f the variances were calculated in the above blog in advanced formula, below is table explaining it.

      Variance Type Calculation Value Working
      Volume Variance (Actual Qty - Budget Qty)* Budget price of the total product mix (Bags + Boxes) 40 =(10-5)*160/20
      Rate Variance (Actual Rate- Budget Rate)* Budget Qty 5 =(6-5)*5
      Mix Variance (Actual Qty-Budget Qty)*(Actual rate of individual prod (Bag 001)- Actual rate of total mix) -10 =(10-5)*((60/10)-(160/20))
      Author's profile photo Filippo Naggi
      Filippo Naggi
      Blog Post Author

      Thank you Nikhil, super!

      Author's profile photo Luigi Simoncini
      Luigi Simoncini

      Can somebody explain what is the meaning of H1P, H2P in the DS dimension, and why do products have a corresponding attribute? are those really necessary? what purpose do they serve?

      Author's profile photo Filippo Naggi
      Filippo Naggi
      Blog Post Author

      Ciao Luigi,

      I appreciate you taking the time to review the code and discovering this valuable feature.

      I had planned to elaborate on this topic in a future episode of the blog, but let me give you a sneak peek in advance.

      The Volume and Mix Effects are calculated as:

      Volume Effect = Delta Volume * Total Actual Rate

      Mix Effect = Delta Volume * (Product Actual Rate - Total Actual Rate)

       

      H1P and H2P play a role in calculating the Total.

      By utilizing the H1P or H2P attribute, the code can group products and generate various rates for the Actual Total.

      It will be possible to generate a variety of variances based on the product grouping.

      Business enthusiasts appreciate the ability to analyze Volume and Mix Impact by product group such as Category, Business Type, or Geography, as it is a highly valued feature.

       

       

       

      The involvement of H1P and H2P comes into play in the computation of the Total.

      Author's profile photo Nikhil Anand
      Nikhil Anand

      Luigi Simoncini

      I think its to scope the products into groups and aggregate the metrics (price/sales/volume) for calculation of these variances. Cleaner way of aggregating from a modelling perspective.

      Nikhil