Memorialize Actual Data in Historical Planned Versions
Hello,
I am currently running into an issue with one of my models where historical plans are changing due to actual restatements.
I have a model with 4 versions. Actual, Budget, Working, and Current Forecast. Working is where all of the planning is done, and then that version is copied from working to current forecast/budget depending on the time of year.
When we are preparing to start a forecast we will move periods forward and then load actual data inclusive of any restatements made to prior periods. Right now that data is also changing the current forecast/budget scenarios because we utilize the swtichover functionality. As an example. If the current period is Mar 20, we can have an intersection in Jan 19 = 100 when we completed our forecast but after loading actual data Jan 19 = 80 because of a restatement. Which then throws off our current forecast scenario because of switchover.
I'm wondering if anyone has experienced this before and has any suggestions? Our initial thought was to tag Working as the current version and change the formula scope in our modules but some line items with formulas IF ISACTUALVERSION() THEN 'do something' ELSE 'do something else' stopped working because that first condition is not met (Actual <> Current Version).
We would also prefer to continue using switchover due to the size saving benefits.
Thanks,
Ryan
Best Answer
-
Hi Ryan,
I believe the only solution in your predicament is to store the actuals per version. Because you effectively want to track changes in actual periods for different versions, hence the need to store those somewhere.
Unfortunately that also means getting rid of the switch-over.
This is what I have in mind:
Pre requisite:
1) Store your actuals in a new module dimensioned by version (let's call it Actuals per Version)
2) Untick the switch-over boolean
3) Change formula scope to Current Version only, it's not mandatory but would make your model more efficient
New Formula:
If item(time) < Version.Switch-over Period then Actuals per Version.Amount else Forecast Calculation
It's not perfect but it should solve your issue. Hope this helps
Doug0
Answers
-
Glad you mentioned that last part about wanting to use switchover.
While I see the benefits of switchover, I tend to use an input module that contains the current month. Since an admin will have to manually change the period anyway, it always seemed to me to be easier to just put it in an input module then I can put it on a dashboard.
But, if you're committed to switchover, one thing you can try is to create a set of audit modules that look for discrepancies between elapsed periods and your restatements. These can then be used either in a separate import to restate your forecast or you can address the discrepancy in your planning module.
Another idea I was pondering is making use of the version scope. Not sure how I would implement that but if you want I can give it some more thought.
I'm pretty sure there's a reasonable answer here. Let me know if anything here looks intriguing. I'll whip up some examples.
1 -
Have you considered setting formula to "current version" only? This way you keep the versioning plus associated model structures and not have to worry about creating a "fake version" history module.
This also has the added bonus of keeping model calculation times down as well as all these data points are just values.
We've done this recently as part of a model optimisation process and found it am easy step to put in. Just need to figure out which is the right module(s) to set this in.
Hope that helps!1 -
Current version will not work because any formula using ISACTUALVERSION() will not be met because it will never be the actual version.
0 -
Hi Jared,
We have already gone down the formula scope road and it will not work because any formula using isactualversion() will not work. Also fails on any line items that are actual scope since they will always pull actauls.
Audit modules do not sound viable in my situation (too many modules to check against)
-Ryan
0 -
Hi Doug,
Thats what i was thinking. Thanks for the response.
-Ryan
0 -
Actually I thought of a different solution. Less auditable than my first suggestion but would save up a little bit of size.
Change your current line item and remove the switch-over boolean, add my formula with a reference to the switch-over period (see below) and make it current version only (formula scope).
That way when you copy your version across none of the values will change. But you won't be able to see how actuals changed per version (if that was ever something you might have wanted).Formula:
If item(time) < Version.Switch-over Period then Current Actual Module.Amount else Forecast Calculation
0 -
I believe we would also have to switch the boolean for any line items referenced by that line item as well. That could turn into a little bit of a nightmare as well. a few different solutions i guess.
0
