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!
Answers
-
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.
0 -
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?
0 -
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
0
