Wondering what everyone does when creating Forecast (Actual + Plan) v Plan modules for P&L use cases.
More specifically I am asking whether people
1) Take a copy & create a new version of the Plan P&L at the time of finalisation of the Plan P&L, keep the Swtichover as July (For June Financial Year End periods) & then use the Variance Version to compare Forecast v Plan as the year progresses
2) Create Plan & Variance modules within your current version & import the Plan data into those modules as static values (no formula)
Ideally Option 1 is preferred but we are updating & improving our model frequently & hence the Plan values will change if any calculation is adjusted & hence we have used method 2 for the current financial year
Does anyone else have the same issue & if so what are your solutions?
As Jared said, every time it is a little different.
Personally, I'd recommend having your version comparison on a specific module where the versions are line items.
The simplest is just to do a [SELECT:your version] for each line item. This give you complete flexibility on the logic (change the rate, replace fcst by actuals etc etc without touching the actual data.
And it allows you to simply archive. Remove the formula and voila, your values are here to stay and you can re-use the actual version for planning.
So yes, it's not fully dynamic and requires the admin to change formulas but in my opinion (having managed an FP&A model long term) it's the easiest for the admin.
I have been creating duplicate's of key modules, with 1 being Plan & the other Variance. In the Plan Modules I zero out values & then Import the data from the Forecast module (which at the time is the same as the Plan) using a saved view which is only the future Plan period, then I create the Variance module by inputting a formula of Forecast module less Plan module
This is a bit cumbersome/time consuming but it only needs to be performed once a year & it allows for detailed variance analysis vs plan
Great question and I'm sure there a numerous answers which really depend on your use case!
First of all I would point out that the first decision is always to decide whether to use native versions or a dummy version list.
This decision is often made easy but deciding on the number of versions you will require and whether you will need to optimise performance by creating a subset of versions to reduce sparsity.
Also as you will know some of the functionality that can be used on versions versus a general list are limited which means you may have to use the select formula.
In the case you describe I can see you've opted for native versions meaning you have to use select.
As select on versions is not always sustainable you must consider creating flexibility and allowing yourself to not have to change the formulas every year.
In this scenario it always better to create a module where you can reference the version that needs to be selected for the formula, this way you can change which version is selected.
I would suggest creating a dummy version list and mapping it to the real versions and then use a boolean to define which version to lookup for your formula, for this to work the module will need to be dimensioned by the dummy version.
We decided to have native versions and setup only budget / forecasts versions as "live" / latest up to date previsions and use those for the calculations.
Also we created fake versions to store the multiple "versions snapshot" of previsions at different times during a year.
That allows to keep the first official p&l plan as a fixed set of figures while the operations an time goes on.
Once users need another snaphot to be created they can also create a new version and snapshot it at the desired time using actions / processes that import into dedicated reports modules the current previsions / actuals setting and keep it static