Rate/Balance Variance Analysis

SHayes
SHayes
in Modeling

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

  • Dikshant
    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
    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:

    1. Scenario A Balance * Scenario B Rate
    2. Scenario A Balance * Scenario A Rate

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

    1. Scenario B Balance * Scenario B Rate
    2. 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
    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
    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

Categories