Use three versions in model

LVermunt
LVermunt
in Modeling

Hi,

 

I'm trying to build a rolling forecast model that has actuals, budget, latest estimate and forecast with the following dates:

Current month = jul 2015

Budget = currenlty available for 2015, should be updated every year

Latest estimate = sum of actuals and remaining budget in the current year (2015) according to the current month

Forecast = 2016 - 2020

 

I'm not sure how this can be best build in the model, either through time ranges or versions. Currently I'm trying to build some versions (see attached image). Here I'm not sure how the rollovers should work and what is the current version. Is there anyone that has some advise on this?

 

Another question: how can formula's be applied to different versions? For example: forecast = (t - 1) * ( 1 + 1%). Where, in the first column t - 1 should be linked to the budget / actual.

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓

    @LVermunt

    A few quick points on Versions.

    • Within a line item you can specify 'Formula Scope' which determines if the formula applies to different versions, but they are "generic". This contruct uses the 'Actual' and 'Current' version settings. The options are: Actual, All Except Actual, Current
    • I would also be wary of using formulae within the version settings themselves.  Because of the nature of mutli-dimensional calculations, these can lead to incorrect answers in totals.  In your case, the formula is quite simple so you should be OK
    • I would also set up a Time Settings module utilising CURRENTPERIODSTART() to flag which months are actual, budget, forecast etc.

    You can achieve specific formuale for different versions with a little known technique called Version Formulae.

    Now this only will work with numeric values becasue it uses Line Item Subsets

     

    1. Create a line item subset using the target module

    2. Include the line item(s) that you want the different formulae to apply to (in my example, I have only 1 line item 'Data')

    3. In the target module enter blueprint mode and click on the edit menu; you will see a new menu item at the bottom2019-01-04_10-06-19.png

    4. Click this, select the respective version (in this example it is 'Latest Estimate') and enter the formula2019-01-04_10-11-41.png

     

     

    5. This will now take effect (In my example, June-18 are forecast months onwards)2019-01-04_10-12-08.png

     

     

    Regarding your last point, I would definitely take that calculation out into a separate module to calculate the Forecast uplift % and then refer to that formula in the above structure

    I hope that helps

    David

     

     

Categories