Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Low Maintenance Way to be able to Reference the First Non-Actual Month (sort of Start of Plan) in a Formula

Currently there is no direct way to know the first non-actual month nor the start of plan in a formula, in the past many customers have created a “Flag” account and entered a 1 in the period representing start of plan.  This solution requires someone to remember to change the Flag as the start of plan changes.  This article documents the steps to setup a solution that should require no further maintenance.  NOTE:  This solution flags the first non-actual month which is not always the start of plan.

Use cases

  • Want a modeled sheet formula to only calculate for budget periods
  • Need a count of the number of budget months in a version
  • If you use a modeled account to hold a total for display column reasons, this can help get the right value into that account if your version spans multiple years

Examples, and what you get

  • Everything before the start of plan is actual data
    • Actual data loaded through March 2019, start of plan set to April 2019
    • This solution will place a 1 in the first non-actual month (April 2019) which also happens to be the start of plan
  • Start of plan is set more than one month ahead of actual data
    • Actual data loaded through October 2019, start of plan set to January 2020
    • This solution will place a 1 in first non-actual month (November 2019), there is no way to know what the start of plan is set to in this situation without manual Flag accounts

Summary of the solution

You will create a new assumption account that holds 1’s for all plan periods.  As the actual data is imported and the start of plan is changed it will replace the 1 with blanks for actual months.  We can then reference the change over from blank to 1 to work out the first non-actual month.

Detailed steps

1.     Navigate to Model Management -> Assumptions and click the Create New Account button clipboard_e0585aff3cd5395bb1dd15e2281d948a1.png
            a.     This account can be added to any existing grouping of assumptions, or you can create a new group or leave it just off the top Assumptions bucket
            b.     Give the account a code and a name, for this example we used 
                         i.     CODE:  IsPlanPeriod
                        ii.     Name:  Is Plan Period
            c.      Click save
2.     Edit the data for the new assumption account in all necessary versions
            a.     If you added the new account to an existing group, you should see the account in an existing assumption sheet
            b.     If it is not on an existing sheet you need to create a new user assigned sheet, add the new account, and give yourself access to it
            c.      Once you can edit the account, type a 1 into the first non-actual month and copy forward to the end
            d.     Click save, repeat for all affected versions
3.     Given the code used for this example we can then use the following formula anywhere to work out the first non-actual month
            a.     ASSUM.IsPlanPeriod - ASSUM.IsPlanPeriod[time=this-1]
            b.     This formula will only return a 1 where actual data meets plan data therefor marking our first non-actual month, which is usually the start of plan, with a 1
            c.      You can use that formula in an IF statement to check if the result is 1, driving the correct course of action

A side effect of this solution is that you now have an account that will give you the total number of planning months in a version as the 1’s add up for the year.

 


  • Was this article helpful?