Identify User Edits that Overwrite Shared Formulas
Question
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.
Answer
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.