Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Concept: Locations for Creating Formulas

Concept: Locations for Creating Formulas

Administrators can:
  • Create default formulas in specific accounts and levels
    : The same account can hold a different formula in different levels. For example, a Supplies expense can be calculated one way in Marketing (such as as a function of the number of planned trade shows) and another way in Engineering (such as a function of headcount).
  • Modify formulas on levels:
    For example, a specific Marketing department may require a Supplies expense calculation different from that of other Marketing departments.
  • Create formulas as a helpful starting point:
    Administrators can choose to let individual planners override a formula or they can configure a formula to prevent changes (read-only).
  • Choose whether actuals data is calculated or imported:
    Make selections on an account-by-account basis.
Formulas are made up of terms and statements which can be numbers, functions, or references to individual or rollup values of accounts or assumptions. Formulas in different areas of generally give access to different types of terms.

Sheets

When working on sheets, you can input formulas by typing into the formula bar at the top of the sheet or by typing directly into an editable cell of an account or assumption. Formula terms can refer to other accounts and assumptions, either individual months or time ranges, and optionally filtered by dimensions.

Shared Formulas

You can enter a formula for a general ledger or custom account across all months on multiple levels at once. Formulas on this page can refer to any type of term available on a sheet. With the appropriate permissions, users can create local overrides of shared formulas.

Default Formulas

Default formulas are global, account-level formulas that define global concepts like Net Income. Default formulas are a setting defined once per account. Default formulas are calculated the same on all levels in all versions, are read-only on sheets, and cannot be overridden. Use a default formula to apply the formula to all levels and versions in a model. To improve performance, use a shared formula instead to apply the formula only to specific levels and versions.

Metric Accounts

Metric accounts are accounts that have a single formula as their value and apply across all levels, versions, and time periods. This formula is read-only and cannot be overridden. Metric account formulas are created on the Manage Your Metric Accounts screen, and generally cannot use terms that refer to individual levels or times. Metric accounts created on a cube sheet are called cube metric accounts, and work similarly.
When a formula is used in both actuals and plan, and the formula is the same across all levels and versions, use a metric account for better performance.

Modeled Calculated Accounts

You can create modeled calculated accounts from the modeled sheet builder. When creating formulas for modeled accounts, you can refer to:
  • Accounts and assumptions in your model.
  • Columns in the sheet.
  • Lookup tables.

Cube Calculated Accounts

Cube calculated accounts are created in the Cube Sheet Editor. Like modeled accounts, cube calculated accounts can refer to accounts either within or outside the cube. Like metric accounts, cube calculated accounts have a single formula that cannot be overridden and applies to all level and dimension combinations of the account on the cube sheet.

Custom Calculations in Reports

Custom calculation elements in matrix reports allow arbitrary formulas, but only terms that refer to other elements on the report or other elements of their tier's dimension. Refer to Formula Syntax for Custom Calculation Elements in Reports.