Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

How to have Different Modeled/Cube Sheet Calculations and Master Formulas in Different Versions

All of the following types of formulas are universal to the model:

  • Model sheet formulas

  • Cube sheet formulas (calculated and metric)

  • Master formulas

A change to any of those formulas will affect all versions, including those that are locked.  Well there are a number of workarounds that customers have used, this article outlines the easiest and most flexible solution.
 

The solution

Create a new assumption account to hold the value of the formula you want to use and update your formulas to use a Switch statement to pick up the number of the formula to use.  In your locked versions this account will have no value so we will make that the default option. For this years budget/forecast you enter a 1 to indicate use a new formula. In future years if you change the formula again just change the 1 to a 2 and update the formulas to no have a formula where the value is set to 2.
 

Current Setup

The example below focuses on a modeled account but the logic is the same for cube and master formulas.

  • Existing modeled accounts:

    • Personnel.Salary

    • Personnel.MyBonus

  • Existing assumption account:

    • ASSUM.Bonus_PCT

  • Existing formula for Personnel.MyBonus:

    • ROW.Salary * ASSUM.Bonus_Pct
       

The New Scenario

Historically everyone in the company got the same bonus as a percent of their monthly salary.  Going forward anyone who makes less than 120k per year (10k per month,in their local currency) will get 1.5 times the global bonus rate.  We can’t just change the formula as that would change locked versions.

Below are the steps to support this change without affecting the past versions.
 

Step by Step Guide

  1. Create a new global assumption account with the following details:

    • My model has a section for Personnel Assumptions, you should put this assumption where it makes sense for you.

    • Code:  Bonus_Calc_Flag

    • Name:  Bonus Calc Flag

    • Actuals overlay:  select “No actuals for account (show plan data)”

    • Any other settings as you see fit.

  1. Optional:  Depending on where you added your assumption account, you may have to add it to a user assigned sheet.  For my example since I added it to a group of similar accounts it will auto show up on my Personnel Assumptions sheet.

  2. Navigate to the assumption sheet containing your new account:

    • Make sure you are on the version where you want the new formula to be used.

    • Enter a value of 1 into the first month of that version and copy it forward.

  3. Navigate to Model Management, edit the modeled sheet, select Modeled Accounts and then select the Personnel.MyBonus account (or whatever you’re updating).  Update the formula to the following:

switch( ASSUM.Bonus_Calc_Flag,
 # Flag set to 1 so use the new formula
 1, iff(ROW.Salary < 10000, ROW.Salary * (ASSUM.Bonus_Pct*1.5),ROW.Salary * ASSUM.Bonus_Pct),
 # Default is to use the old formula
 ROW.Salary * ASSUM.Bonus_Pct
)

  1. Navigate to your modeled sheet (refresh if it was already open), right click on a row where someone makes less than 120k and view row details.  Repeat for someone making more than 120k to ensure the formula is now behaving the new way.

You should be all set at this point for your new versions (with flag set to 1) to use the new formula and all old versions (with no value for flag) to use the original version. This method also gives you the flexibility in the future to change the formula again and just set the new version flag to 2, then update the formula to have an option for 2 between the current 1 and the default.

 


  • Was this article helpful?