How can I prevent my Plan values from changing when I update my Actual Data?
Many accounts have formulas in plan versions that refer to Actuals data in previous months. This means that if actuals data is updated later, this can affect the formulas in the plan versions.
Locking a version prevents users from being able to import new values or overwrite values directly on the sheets. However, a locked version will still update plan values if they are derived from formulas that reference Actuals data. If new Actuals data is imported for previous months, the value in formulas will change to reflect the updated value.
If you would like to prevent the plan data from updating, you can navigate to Modeling > Versions and then select the “Calculate Formula Values” option on a version (Note: this version will need to be locked).
Once this option is selected, two things will happen:
- New Actuals data that is imported will not change the plan data at all, even if formulas in this version refer to values in the Actuals version.
- As an added bonus, performance will improve when navigating through sheets and reports with this version selected. This happens because the values are pre-calculated immediately after the “Calulate formula values” option is selected, and thus the evaluations will have already been computed when navigating to a sheet or report with this version.
Please see the article HERE for more detailed information on this feature.