This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
What happens when a user overwrites a shared formula directly on the cell and how do I identify the users change?
As an example, a formula for Net Income may be:
ACCT.4000_Revenue - ACCT.5500_Cost_Of_Goods_Sold - ACCT.6000_Expenses_7000_Allocations + ACCT.8000_NonControlling_Interest + ACCT.5100_Non_Operating_Income
If a user overwrites this formula and enters a value directly on the sheet in one level, it will cause the Net Income account to be different on the top level. However, this will not change the values in the accounts that make up the Net Income formula, such as the revenue and expense accounts. This will ultimately make the Net Income un-equal to the sum of the accounts in the formula on the top level.
If an account has a shared formula (that is, the formula is the same on all sheets), one way to do this would be to go to the Formulas page and select the desired account. The small fx symbol represents where a formula has been changed on the sheet to represent another formula or value. User edits include either manually entered data, imported data, or manually entered formulas within sheets. Erasing user edit(s) will clear data from all time periods in the Version for the specified Level/Account, and result in the underlying shared formula driving the account value. User edits can be removed on the Formulas screen one of two ways:
- The first is by selecting the desired levels within the Formulas page, and selecting the 'Remove user edits' option at the top left.
- The second is by making any edit to the preexisting formula with the 'Remove' option selected under 'User edits' in the Details panel.
Should you want to clear all edits/data in a Plan Version, you may navigate to Modeling > Versions and select the version in which you want to clear all user edits. From here you can move the 'Start of plan' back to a date prior to the current 'Start of plan' (e.g. one year prior), then move the 'End of plan' back to the original 'Start of plan', and the 'Left scroll limit' to match the 'Start of plan' and save. Then, move the 'Start of plan', 'End of plan', and 'Left scroll limit' dates to what you expect for the version. This clears all data entered on sheets, but it does not impact formulas on the Formulas tab.
Although the formula page is the fastest way to check for user edits, it may not be helpful if the formula is not a shared formula (that is, the formula was made directly on the sheet). For this, you can create a report to visualize each of the values on one page.
To build your test report, you will want to create an extended organization structure on the horizontal axis, and the accounts in question on the vertical axis. You can then create a custom calculation next to the account to match what the correct formula should be. When you run the report, it will evaluate both the account and the correct formula by level, which can help trace the level in which this was changed.