What is Variance Analysis?

As of Planning Analytics Workspace 3.1.3/ 2.1.16 we have introduced the ability to identify driver-based variation across versions of Data. Variance analysis is the quantitative process of comparing planned financial outcomes (like a budget or forecast) with actual financial results to identify differences, known as variances, and understand the reasons for these deviations. The goal is to determine if these variances are favorable (e.g., higher revenue, lower costs) or unfavorable (e.g., lower revenue, higher costs) and to explain the causes, such as changes in market conditions or inefficient resource use, to make better financial decisions and improve future planning.

Planning Analytics offers a manual variance analysis calculation feature through 'calculations'. However, this method can be cumbersome when dealing with multiple drivers. The new variance analysis feature simplifies this process, allowing you to perform variance analysis against two drivers, such as Price and Quantity. This analysis is run against the first 120 rows of the selected view, displaying an AI generated summary and calculated variances for each specific row.

Before diving into the steps of Variance Analysis, let's cover the necessary groundwork. To walk through the variance analysis steps, we'll create a new cube.

If you are already familiar with the steps to import the cube in Planning Analytics, you can download the CSV file here.

You will have to add a rule to the “Gross Revenue” before performing the Variance Analysis, after the import go to the following step - Add a rule to Variance Analysis cube

Import cube

Specify data source

1) In your database tree, right click on the “Cubes” and select “Import Data”

2) Select the Data source as “File” and select the “Upload a file” radio button. Attach the following csv file

3) After uploading the csv, your data should be previewed like this 

Specify a cube

1) In the next step, select the “New Cube” option and name the cube - “Variance Analysis”

2) In the “Dimensions” section, we will be adding four new dimensions to the "Dimensions" section. To avoid potential conflicts, we will prefix the dimension names with "V_". The new dimensions will be:

• V_Revenue
• V_Year
• V_Version
• V_Product

Map member data

1) Now we will map the columns from the imported data to the dimensions. Select the Type as ‘Element’ and Qualifier as ‘Leaf’ for columns Revenue, vYear, Version, Product.

2) Select Type as ‘Data’ and Qualifier as ‘Numeric’ for Value.

Import

Click on “Import” after making the selections, you should see the following view 

Add a rule to Variance Analysis cube

1) Go to Planning Analytics Workbench and right click on the ‘Variance Analysis’ cube 

2) Add the following rule to the file - ['Gross Revenue'] = N: ['Unit Price'] * ['Unit Sales'];

and save it. Select ‘Revenue’ member for the Variance Analysis cube, it should look like this 

Perform Variance Analysis

To perform Variance Analysis, your cube needs to be organized in a specific way. Your Version dimension should be in the column. You can have nested dimensions with Version as well. For the first release, the focus is on leaf level data on context dimensions. Consolidations are not supported. In our case, we are going to add Year as the nested dimension in the columns. We want to perform Variance analysis for all of our products so we are going to move V_Product dimension to rows. We are performing variance analysis for ‘Gross Revenue’, so select the ‘Gross Revenue’ member for the ‘V_Revenue’ dimension in the context.

The remainder of the dimensions that are in the context of the cube view must be set to a leaf level of a dimension; Variance Analysis cannot be run otherwise.

Let’s do a Variance Analysis for 2024 Actual vs 2025 Actual. Select 2024 Actual and 2025 Actual, and right click to show the menu items. Select ‘Variance Analysis [AI]’ to launch the Variance Analysis dialog.

Select data scope

1) Select the base and alternate versions of the data to analyze the variation between them. You can switch the version selections using the button between the two boxes. Since we are performing a Variance Analysis for 2024 Actual vs 2025 Actual, we will leave the selections as is.

2) You can optionally set a threshold for the variance analysis. The threshold value will be a percentage value. If you set the threshold to 5%, all the variances within -5% and +5% will be omitted. All the variances outside of -5% and +5% will be considered. For our use case let’s leave the field empty.

3) To proceed, select the cube containing the variance driver. Typically, the drivers are located in the same cube. However, in some cases, they may reside in a different cube. If this is the case, you can choose an alternative cube from the dropdown menu.

Note that the available cubes in the dropdown are filtered based on the dimensions selected in the rows and columns of the view where the Variance Analysis feature was launched. The selected cube must include all the dimensions present in the rows and columns of the view.

In this instance, the only available option is the 'Variance Analysis' cube.

4) Your data scope page should look like this 

Select the drivers

1) When a cube is selected from the Data scope page, all of its context dimensions become available in the 'Driver 1' and 'Driver 2' boxes. In this case, only the 'V_Revenue' dimension is displayed in the "Select the dimension where Driver 1 is located" box, as it is the sole dimension in the current context.

2) Optional: The "Compute Driver values" checkbox allows users to manually calculate "Driver 1 Actual" or “Driver 2 Actual” values, which may be necessary for manual calculation of "Price Actual".

3) Once a dimension is selected, a dimension picker box will appear, displaying the dimension name, hierarchy name, and the default selected member. You can modify the hierarchy for the dimension and choose a different member for "Driver 1" and "Driver 2". For this example, we will select "Unit Price" as the "Driver 1" member and "Unit Sales" as the "Driver 2" member.

4) Optional: If the selected cube for drivers on the "Data scope" page differs from the cube used to initiate Variance Analysis, any additional dimensions in the new cube will be displayed under the "Show context filters" option. In this instance, since the drivers are from the same cube, the context filters remain empty.

Analysis Summary

Once you perform the analysis, you will see two tabs - ‘Overall analysis’ and ‘Member analysis’.

1) Overall analysis

The 'Overall Analysis' typically assesses data across 120 rows in a cube view. However, in this instance, we are working with a smaller dataset of 6 rows. The analysis will display the top 3 and bottom 3 variances for 'Driver 1' and 'Driver 2' on the left, accompanied by a text summary on the right. Specifically, 'Driver 1' refers to 'Unit Price' and 'Driver 2' refers to 'Unit Sales'. The summary will highlight which driver had the most significant impact on each product.

2) Member analysis

Once you've identified the top and bottom variances for the product, you can drill down into each product to analyze the underlying drivers. Specifically, you can examine the 'Driver 1' and 'Driver 2' values, as well as the 'General Variance' values, to gain a deeper understanding of the variances. Additionally, you can assess the overall impact of these variances.

In our case, Nebula Pro exhibited the maximum Unit Price Variance (Driver 1 Variance). Let's take a closer look at this product. Upon drilling down, you'll find a detailed variance table and summary for the member, providing further insight into the variance drivers.