Use formulas

Formulas are advanced calculations that you add to plan lines.

A formula can include the following:

  • Values: Whole numbers, decimals, or percent.

  • Operators: + , - , * , / , % , and ()

  • Plan line references: This creates a dependency between the current plan line and the referenced plan lines.

    For more information, see Insert a plan line reference.

Add or edit a formula

In the plan grid, formula indicates the plan line relies on a formula. If your permissions allow it, you can edit the formula to update the plan line data. You can also add a formula to any plan line. See Who can add, edit, or remove formulas?

Start

To add a new formula to a plan line or to edit an existing formula, navigate to a plan line and do the following:

  1. In the last column, select Actions menu and then Add formula or Edit formula.

    The Formula editor opens.

    Formula editor

    In the plan line details flyout, you can see how your changes to the formula impact the other plan lines that reference the current plan line data.

    Start

    • In the last column of the plan grid, select Actions menu and then View details.

    • Select Add formula or Edit formula.

    For more information on the plan line details flyout, see View plan line details.

  2. Add or edit a formula.

    After the = sign in the formula editor, do the following:

    • Enter calculations with numbers and standard operators.

    • Add plan line references as described in Insert a plan line reference.

    • Copy and paste formulas.

    • copy Copy: Copy formula from the formula editor to the clipboard

    • paste Paste: Paste the copied formula to the formula editor

      Your browser might ask you to allow the use of the clipboard for this site to paste data in your plan.

      1. With Sage Intacct Planning open in your browser, navigate to the View site information icon that appears in the browser address bar to the left of the url address.

      2. Allow clipboard. For example, in Chrome, go to Site settings > Privacy and security > Clipboard. Select Allow.

      3. Refresh or reload the page (Ctrl+R).

  3. Save or cancel your changes.

    • To save your changes, select Save.

    • To cancel all changes since you last saved, select Cancel.

Insert a plan line reference

When you build a formula, you can easily find and insert references to other plan lines directly from the formula editor. This makes it simple to create dependencies without manually searching through your plan.

When you update any referenced plan line, you automatically update the current plan line too.

  • In each formula, you can add as many plan line references as needed.

  • In each plan line reference, you can reference a single or multiple plan lines.

  • The values of each time period depend on the corresponding time periods of the referenced plan lines.

Start

To add a plan line reference, do the following:

  1. With the cursor in the formula editor, press Ctrl+P on your keyboard.

    Make sure caps lock is off. If caps lock is on, Ctrl+P may launch your print dialog instead.

  2. Select an account and dimensions of the plan line you want to add as a reference.

    • The dropdown lists are filtered to only include values associated with your previous selections.

    • From each dropdown list, you can select a single value, a parent value that includes multiple values, or all values.

    • If your selection includes multiple values, the aggregated value of all these plan lines is added to the formula calculation.

      Select USA to include all available USA locations, or All to include all available locations worldwide.

      Formula reference location selection

    • Enter the name or number of the account or dimension instead of scrolling.

    • Press Tab to navigate to the next selection, or Shift+Tab to return to the previous selection.

    • Hover over the account and dimension values to see the names behind the numbers.

  3. Ensure that no dimension values appear in red, which indicates that no such plan line exists for the current combination.

    Account 4130 with dimension 101 does not exist in this plan, so you need to select a different combination to make the plan line reference valid.

    Example of invalid dimensions that appear in red

  4. Select Apply.

    If you try to apply a formula that references the current plan line directly or indirectly, this creates a loop error. Select Remove or change the formula and Apply again. If you are unsure why the loop error message appears, check the Referenced by tab in the plan line details flyout. Your formula can't reference any plan line listed there, because those plan lines reference back to the current line, creating the loop.

  5. (Optional) Edit data of a referenced plan line

    After you apply a formula with referenced plan lines, you can view and edit the referenced plan lines data in the References tab of the plan line details flyout. See Edit references data.

Coffee Bean is trying to project sales for a new branch opening in Notting Hill next year. The marketing team thinks that revenue will mirror the performance of their Soho location, with a 10% increase.

Sophie, the marketing manager, adds a formula that references the sales plan line of the Soho branch [4100.100. 411] and adds 10%.

Formula example

This dependency is convenient for Sophie. When the projections at the Soho branch changes for October through December, Sophie knows the corresponding months at the Notting Hill branch adjust accordingly.

Africa Tours offers a new product - an 8 day all inclusive safari travel package to Kenya. Emma, the sales director at Africa Tours, has a number of agencies that sell the new product. She wants agency commissions to be dependent on the sales revenue for the new product.

She adds a formula to all the regional sales teams commission plan lines that reference the revenue plan line for the new product. Now, if she adjusts the sales projection for any time period, she can see the effect it has on the same time period of the commission plan lines.

Remove a formula and edit the data

To manually edit individual cell data of a plan line that relies on a formula, you first need to remove the formula. After you remove the formula, the plan line values determined by the formula remain as is. You can edit or adjust the cells data manually as described in Modify plan data.

Start

Navigate to a plan line and do the following:

  1. In the last column, select Actions menu and then Remove formula.

    A confirmation message appears.

  2. Select Remove.

Who can add, edit, or remove formulas?

The following table summarizes formula capabilities per role:

Role Formula capabilities

Owner

Can create, edit, and remove all formulas.

User

  • Can create, edit, and remove new formulas based on permissions.

  • For shared plans that contain existing formulas, a user can't create, edit, or remove the existing formula, but can do the following:

    • Identify that a plan line contains a formula.

    • View time period values generated by a formula.

User with extended access

  • Can view formula related data in shared plans beyond their defined access.

  • Can Enable editing to view and remove existing formulas, and add new formulas in shared plans.

    Enable editing message for extended users

Formula capabilities per role

Is this helpful?