Adjust account values - Dynamic rules

Within Spotlight Forecasting, you can set dynamic rules to adjust accounts based on a formula - for example to set a Cost of Sales account to automatically be a percentage of a revenue account, or to calculate budgeted revenue based on your unit price drivers.

Note: This feature is only available in the Forecast tasks. This article describes how to add a dynamic rule, which automatically adjusts the values in the chosen account when the result of the formula changes. For information about how to add a one-off adjustment, please refer to our separate article.

To add a dynamic rule

  1. Within the forecast, open the Profit and Loss tab.
  2. Open the required section, for example Cost of Sales.
  3. Click the Profit & Loss Rules button, then from the drop-down list choose Add New Dynamic Rule.

add dynamic rule new2


  1. Enter a rule name - the Rule Type drop-down list automatically defaults to Dynamic Rule.
  2. From the Apply Rule To drop-down list, choose the account you want to adjust.
  3. In the Apply Adjustment To section, choose the date range you want to adjust. By default, this date range covers the full date range for the forecast.
  4. Use the Formula Builder to create the required formula. For detailed help with this, please refer to the Formula Builder section.
  5. To continue, click Next.
  6. If required, add a note regarding the new rule.
  7. To finish and apply the new rule, click Save Rule.

The Formula Builder

When you use a dynamic rule, you can create a formula to adjust the values. For example, you may choose to have a cost of sales account as always a set percentage of the appropriate revenue account, or base the cost on the set unit price for an item.  You can use imported accounts or drivers when creating your formula.

To add an account to the formula

  1. Within the dynamic rule window, click Add Account. You can then choose the account or accounts you want in several ways:

  2. Add Account By Name - Use this option to add a single account to the formula. When you choose this option, you can select the account you want from the drop-down list.
  3. Add Account(s) by Report Code - Use this option to select all accounts which share a specific report code. When you choose this option, you can browse to the required report code.
  4. Add Account(s) by Display Name - Use this option to select all accounts which share the same Spotlight Display Name.  When you choose this option, you can choose the Spotlight Display Name you want from the drop-down list.

  5. To add the account(s), click Save.

To add a driver to the formula

For information about how to set up your drivers, please refer to our separate article.

  1. Within the dynamic rule window, click Add Driver.  
  2. In the Select Driver window, choose the required driver from the drop-down list.  Alternatively, search for the driver you want by entering the first few characters of the name.


Once you've added the account or driver you want, you can then use any symbols or set values you need to create the rest of your formula.

To use symbols and set values

You can add symbols, or enter set values, using the buttons available.  To enter a specific value into a formula, use the '123' button and enter the value you want.

dynamic rule symbols2

To remove an account or symbol from the formula

To remove an account or symbol from the formula, click and drag it to the box below the formula.

dynamic rule remove account

To view values posted by dynamic rules


When you adjust an account using a dynamic rule, you can see the value posted by the rule against the profit and loss account.

  1. In the Profit and Loss tab, open the required section, for example Cost of Sales.
  2. Locate the account you want to check, then click the plus sign to the left of the account name.

dynamic rule plus sign new


  1. The icon shown alongside each row indicates where the value was taken from:

dynamic rule value breakdown new


  1. Values posted to the account by the rule appear with the From Dynamic Rule label.
  2. Values which were imported from your accounting system, or added using a one-off adjustment, appear with the icon representing your accounting system.
  3. Values posted by a balance sheet rule appear with the From Balance Sheet Rule label.

To adjust a dynamic rule


You can go back to a dynamic rule and adjust it at any time.

  1. In the Profit and Loss tab, open the required section, for example Cost of Sales.
  2. Locate the account you want to check, then click the plus sign to the left of the account name.

dynamic rule plus sign new


  1. Click the name of the dynamic rule.
  2. Make any adjustments required, then click Next.
  3. If required, add a note regarding the new rule.
  4. To finish and apply the new rule, click Save Rule.

To delete a dynamic rule

  1. In the Profit and Loss tab, open the required section, for example Cost of Sales.
  2. Locate the account you want to check, then click the plus sign to the left of the account name.

dynamic rule plus sign new


  1. Click the name of the dynamic rule.
  2. From the bottom left-hand corner, click Delete, then when prompted, click Delete again.

Troubleshooting

If you run into difficulties working with dynamic rules, for information about some possible issues and how to resolve them, please refer to our troubleshooting article.

Still need help?
Get in touch!