Rate/Balance Variance Analysis

I'm trying to create a standardised way of doing a rate/balance variance analysis between 2 different scenarios (Scenario A and Scenario B) for an output variable to show what is driving the difference. I'd like to be able to select the balance from a drop down and a rate to apply from another drop down and have the variances and rates calculated but struggling to work out the calculations/dimensions for this.

Any suggestions welcomed!

Accepted answers

All comments

Dikshant

@SHayes - Can you give some examples or a prototype in Excel to explain how the calculations work?

What I understood, you want 2 dropdowns to select scenarios and rates and then based on that you want to see the calculation. If yes can you show in excel how the calculation works?

SHayes

Anaplan Rate Balance Variance.xlsx

Have attached an example here of how I'd see the module working

In general, we have quite a few metrics where we have an output like interest expense, that can be broken down into the balance and the interest rate. So we'd have a list of output variables that we would link to a specific rate and balance in the calculation.

In the example, I've got three scenarios and two different output metrics and have created a couple of drop downs that I'd see being used as inputs or dimensions in the model.

Variance Calculation

For the variance calculations we calculate the variance due to difference in balance and the variance due to differences in rates:

Variance due to difference in rate is 1 - 2 below:

Scenario A Balance * Scenario B Rate
Scenario A Balance * Scenario A Rate

Variance due to difference in balance is 1 - 2 below:

Scenario B Balance * Scenario B Rate
Scenario A Balance * Scenario B Rate

Ideally this could be done for any output metric in the module to calculate the variance based off of its associated rate and balance.

rob_marshall

@SHayes

Can you please validate the logic from above, specifically under the Variance due to difference in balance, the 2nd part (Scenario A Balance * Scenario B Rate)? Why is that the same logic as #1 in Variance due to the difference in rate? Should it be Scenario B Balance * Scenario A Rate?

SHayes

have checked against our reporting side and looks correct. It means that the end result ties exactly to the total variance because you eliminated that term when you add them together

Quick Links

Can you help?

Unanswered questions

How can I Delete Users in Administration
Now I faced below warning But I cannot find any delete action for those users I don't need anymore Can I have any action to solve this warning?
Different Subsets of Parent Lists in Input & Output Modules
I have 2 lists used in modelling, In Input module I'm using subset of territories from Country List and subset of Regions from Flat list. But in Output module,Its reversed, I'm using subset of regions from Country list and subset of territories from flat list.How to derive the numbers from input to output module. I do have…
Is there a way to turn off the Show Comments Summary panel in UX pages with Comments?
This shows all comments across all data to all users with access to the UX page. This is not appropriate for any UX which might contain security access sensitive data. Comments on grids and cards ARE security dependent. That works fine. But anyone with access to the page can open this Summary Panel and see ALL comments…