Shared formulas are calculations assigned to an account. They persist across selected levels and the entire time span of a version. Unlike other calculated data, shared formulas provide values that aren't locked in sheets. Because the value isn't locked, you can edit the formula in the sheet cell or enter different data.
Use shared formulas when you want to provide:
- Calculations but still give your team the option to enter different data.
- Different calculations for different levels or versions.
Shared formula maximums:
- 200,000 at the account and level intersection.
- 10 million across account, level, and time intersections.
How Shared Formulas Work
Shared formulas calculate data at the intersection of:
- The version selected from the version drop-down above the toolbar.
- The account selected from the account drop-down in the toolbar.
- The levels listed in the level hierarchy.
From the Formula screen, update formulas for one account and version at a time. You can update multiple levels with the same formula. Or you can enter a formula in the parent level, which copies to all the child levels.
For bulk edits, export the shared formulas. Then, make changes to the Excel file and import them.
General ledger and custom accounts. These accounts must not have:
- Master formulas.
- Link selected for the account Type setting.
- Cube selected for the Data Entry Sheet Type setting.
How You Get There
From the nav menu, select Formulas.
The Formulas Page
1 The toolbar: See below for information about the buttons and drop-down.
2 Level tab: Displays the level hierarchy and the status of the shared formulas. Use the Selected tab to filter out unselected levels. Use the Search tab to view the results of your search. The Edit icon next to the level name means that someone entered data or formulas in sheets to override the formula.
3 Formula panel: The formula assigned to the version, account, and level selected. The User Edits settings can prevent the updated formula from replacing some existing data.
Icons and Toolbar Buttons
|Removes shared formulas from the level selected. Select a level to activate the button. Select a level with a formula to activate.|
|Removes user edits. Reverts data at the intersection to the calculated value of the shared formula. Select a level with a formula to activate.|
|Includes general ledger and custom accounts that are available for shared formulas.|
Exports the shared formulas. Choose:
|Imports bulk updates and links to the template for imports.|
|Expands the entire level list.|
|Indicates that someone entered data or formulas at the intersection.|
|Indicates that the account isn't available at the level.|
Override Shared Formulas
- Add a master formula to the account. This removes the shared formula from the account at all levels and versions. The master formula calculates the data for the account. The calculated value replaces all existing data, even in locked versions.
- Link the account to another account. This pulls the data from the source account. It replaces all data, even in locked versions.
- Edit the intersection in a sheet. If the account is available for user edits, you can override the shared formula on the sheet.
In sheets, cells with shared formulas display a blue triangle. Unlike other calculated accounts, the cells with shared formulas are generally editable. You can enter raw data or a different formula that overrides the shared formula. From the Formulas screen, you can see where your team has edited the calculated data. Click Remove User Edits from the toolbar to revert the data back to the calculated value.
Updates to the Model and Shared Formulas
When you add child levels, they inherit the shared formulas of the parent levels by default. Go to the Formulas screen to update the shared formula for the level. See Inherited or Cloned Level Settings and Data.
New Plan Versions
When you add new plan versions, you have the option to copy all shared formulas from another version. See Create and Edit Plan Versions.