Budget vs Rolling FC

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

Hi Team,

 

We have recently completed our annual FP&A planning cycle and now have a FY21 budget (Jul20 to Jun21) locked down. I'm creating our new FP&A model which will be a Rolling Forecast & also have the FY21 Budget values static to compare against

 

How do other people complete this task? I don't want to use Versions and we will have more than 1 live Version running at any time & will need to use the Formula scope 'All Versions except Actual'

 

The only way I can see is to create Budget modules and import the data from the Budget model. Then I can create modules which calculate the difference between the 2 values, Rolling Forecast v Plan. So basically creating an additional 2 Modules for every module we wish to bring in from the Budget model. 1 for the Budget values and 1 for the variance

 

Are there any other methodologies to complete this task? Seems very time consuming 

 

Thanks

 

Mark

Tagged:

Answers

  • anirudh
    anirudh

    Hi Mark,

     

    The method described by you is exactly what I have implemented at a customer, with rolling forecasts every quarter. Unfortunately, there is no other way to achieve this is in Anaplan. You have to create Budget modules with a forecast cycle dimension for each rolling forecast you would like to store.

     

    Then, for variance analysis modules, look up the desired rolling forecast and subtract from the "live" forecast version

     

    Let me know if you have any other questions

     

    Regards,

    Anirudh

  • einas.ibrahim
    einas.ibrahim

    Hi @MarkTurkenburg 

    I might not have understood your ask exactly - I don't know what you mean when you say "Create Budget Modules". Are you having data for each version in a separate module??

     

    But there is a way to create Rolling Forecast with a budget. You can use a combination of native versions (for Actual, budget) and a "fake versions" which you implement in a list for the forecast cycles versions
    I wouldn't say it's a topic you will find in Anaplan 101 (it's a bit advanced) but it exists.


     

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Thanks @einas.ibrahim 

     

    I have read/heard about the Fake version method but I don't think it really works when you need to have a Version/List item effectively hard coded - e.g. the Budget list item to not have live calculations

     

    For 1 I haven't built the model with a Fake Scenario/Budget list so I would need to go back and re-work it which would be a heap of work but secondly even if I did this I can't see the benefit when compared against just creating an additional 2 modules, 1 for the Plan values (hard coded via an Import) & a Variance module (Rolling FC/Current Model modules v Budget modules) for the following reasons:

     

    - If I create a Fake version list when I Applied this to a module the calculation in that line item would apply to both list items, Rolling FC & Budget, and we only want the Rolling FC to be a live calculation & the Budget list item to be a hard coded values. We could start implementing Version Formulas but that difficult function to use, analyse and audit and would advise people to not use it

    - Therefore the only solution is to have 2 additional modules for every module that you wish to have Budget data and Variance data for. Obviously this is a crazy amount of work but I really don't see another solution

     

    In my humble opinion this is Anaplans biggest downfall. There should be native functionality to allow for detailed Budget v FC analysis as this is essentially the whole point of the Planning cycle but it is never described how to achieve it in a practical sense. 

     

    I have spoken to a number of people who work at Anaplan and raised this on the forum a few times and no-one has ever had a real answer to it....I don't understand how other people are achieving the outcome of comparing in the Rolling FC v Plan (in detail while holding Plan values as a hard coded value)

     

    Mark 

  • anirudh
    anirudh

    @MarkTurkenburg 

    Have you tried achieving your requirement with SUM formulas?

    Something like this:

    1. Store all your data (current forecast as well as all previous cycles) in a flat module dimensioned by a key; Accountcode-department-budget scenario-Time. By budget scenario, I mean Actual, last forecast, AOP etc. Module updates, through imports, depending on the frequency of the cycles
    2. Create a single centralized module which is dimensioned by Accounts, Department, Time as well as Budget scenario with a single line item

     

    Module 1.Data[SUM: Account, SUM: Department, SUM: Budget Scenario, SUM: Time]​

     

    With that in place, it's trivial to add a comparison line item in your existing modules with a formula like

     

    Current Forecast - Module 2.Data[LOOKUP: Scenario]

     

    Lookup Scenario pointing to a user driven selection on a dashboard, or if you want to hardcode a version:

     

    Current Forecast - Module 2.Data[SELECT: ScenarioMember]​

     

     

    The challenge here is size of course, but we can get around that to a decent extent by turning off summaries in module 2 and using subsets to restrict list members

     

    Key differences from your method:

    1. No version formulas

    2. No dual modules for each comparison member, everything is centralized in module 2

    3. Set and forget, if import actions are configured correctly

     

    Regards,

    Anirudh

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Thanks @anirudh I really appreciate the thought & effort in producing this option but it seems like a lot of work to effectively achieve the same result as I would get with module duplication method?

     

    There are many modules that I would like to create Plan data and variance data for and with your method I would then to create Flat Modules for all of them to hold the data and then a module to bring that data in? Or are you suggesting to create 1 module to hold all the data from every module that we would like to use for comparison purposes?

     

    Again I'm surprised there is no standard way to achieve this, it is surely such a common use case

     

    Mark

  • anirudh
    anirudh

    Hi Mark,

     

    > There are many modules that I would like to create Plan data and variance data for and with your method I would then to create Flat Modules for all of them to hold the data and then a module to bring that data in? 

    Nope! Just a single module with maybe 5-6 different line items for each unique dimension combination. Let me unpack this more:

    • Assuming you currently have your planning data at different levels across a number of modules. The main effort in my method goes in combining all the data from these modules into a single centralized module which is then updated frequently.
    • This is achieved by creating a unique key combination which identifies all the data points at their lowest level (say country, chart of accounts, department, month, scenario) This can be a little tedious depending on the number of modules you have to edit to add a line item (the unique key). Also create a scenario list; Actual, AOP, forecast etc. I'll call these modules with the key and the plan numbers as data source modules
    • Then create a list called ForecastDataKey which is the unique key as name/code.
    • Create a module called ForecastData dimensionalized by ForecastDataKey and line items to store the forecast numbers plus line items for each dimension (formatted with the respective list). The data source modules dump their current data to this module. These steps are about 70% of the effort
    • Create another module called ArchivedData with each line item catering to each unique dimension combination. For instance, supposing one of the comparisons you want to make is only at the Country and Chart of Accounts level, create a line item with just those 2 dimensions and the scenario dimension, then apply the SUM formula I explained earlier. This module is the one that consumes the maximum amount of space, so consider each line item carefully and apply subsets if need be
    • Module in the above step is then referred to your current planning modules with a LOOKUP or SELECT on the scenario dimension

     

    Hope this clarifies a little more. It's basically just 2 extra modules - ForecastData and ArchivedData and 2 extra lists - ForecastDataKey and Scenario and probably around 20 line items for the whole model.

     

    > Again I'm surprised there is no standard way to achieve this, it is surely such a common use case

    Yes, it would certainly be helpful to have these "common sense" features be enabled by a quick setting or two, but it helps to think of Anaplan as more of an empty canvass than prebuilt software

     

    Regards,

    Anirudh

     

     

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Thanks @anirudh for the detailed breakdown

     

    Apologies but again I can't really see what I would go to this trouble rather than just creating the duplicate modules. I will still need to create duplicate/replica modules for the variance calculation because say for example the Line Item 'Revenue' in the P&L:

     

    - If I create the dimension Forecast/Budget Type with the List Items Forecast & Plan as you state I still need to create a separate line item calculates the variance between the 2 (which I don't want to to do as I wish to be able to publish the module on a dashboard and have them lined up)

    - Also I cannot use a Variance list item in the same list because I cannot reference other items in the list in the same Line Item (e.g. I couldn't have a list item called 'Variance' and then in the Revenue Line Item have the formula state if ITEM = Variance then Revenue[Select: Plan] - Revenue[Select: Forecast]

    - So essentially I would need to create a Variance module anyway to contain this calculation

     

    Thanks for your help

     

    Mark

     

     

     

Categories