All Collections
Forecasting
Setting up the baseline
Using a Formula or Drivers to Set a Value
Using a Formula or Drivers to Set a Value

How to set up formula or driver based value rules, how to use the formula builder in forecasting

Updated over a week ago

Contents

Why use a formula/driver value rule?

The Formula/Drivers value rule enables you to build out custom mathematical equations to calculate the values in your forecast’s Profit & Loss.

You can use any chart of account line item, sub total, heading, driver, or typed value, combined with the 4 operators and parentheses to build out formulas for any chart of account line item on the Profit & Loss.

Through formulas, you can acknowledge the relationship between P&L accounts and incorporate non-financial data into your forecast through drivers.

Drivers can be set up from the Fathom main grid and are essentially ‘non-financial’ metrics or leading indicators that are used to calculate the value of accounts.

Examples: A driver for the 'Number of patients' can be used in a forecast for a dental or medical practice. A construction company could use a 'Number of contractors' driver. A SAAS company could use cancellations or churn rates as drivers.

Creating a Formula/Drivers Value Rule

To create a new 'Formula/Drivers' value rule or edit an existing one:
​

  1. Click into the value cell for the account you wish to apply the formula.
    ​
    ​

  2. Select the ‘Three dot’ icon to add a new rule or edit an existing rule.
    ​
    ​

  3. Choose ‘Formula/Drivers’ from the dropdown menu to open the formula builder.
    ​
    ​

  4. Enter in the formula you want to use to calculate the forecast values.
    ​
    ​

  5. To include a driver in the formula or account in the formula, type in the name of the driver or account or use the ‘@’ symbol to choose a one from a menu.
    ​
    ❗Note: P&L accounts derived from the Balance Sheet (e.g. accounts classified as Depreciation/Amortisation or Interest Income/Expense) cannot be used as variables in value rule formulas. Headings housing these types of accounts also cannot be used as variables.
    ​
    💡Pro Tip: While percentage drivers are formatted as percentages in the Drivers Grid (e.g. 4%, 7%, etc.), they are treated as whole numbers in a Formula/Driver value rule. Therefore, we recommend adding ÷ 100 to your formula to ensure the formula calculates as expected.
    ​
    ​

  6. Select the green ‘Save changes’ option to save the formula.
    ​
    ​

  7. Click the green ‘Create Rule’ button to apply the ‘Formula/Driver’ value rule to your forecast.

💡Pro Tip: Formulas can be edited or overridden at any point, by opening the properties panel to the right of an account and using the ‘Three dot’ icon in the value rule section to bring up the editing options. You can edit the rule to change the formula or start a new rule to override it.

Examples

A couple of the many ways you can use the ‘Formula/Driver’ value rule to determine your forecast’s values, include:

Linking the performance of one account to another

With a 'Formula/Drivers' value rule, you can base the forecast performance of one account, heading, or classification off of another account, heading, or classification.

Example 1: A company knows that its ‘Materials’ expense account trends at 35% of its Sales. This can be represented in the forecast by building out the following formula for the 'Materials' account:

‘Sales’ x .35

Using a driver as a variable in your formula

You could set up and import data for drivers and use these drivers as variables in a formula. Some common forecast drivers include ‘Number of Units Sold’ and ‘Average Sales Price’. Using a formula you would be able to reference these drivers to forecast sales according to the following formula:

‘Number of Units Sold’ x ‘Average Sales Price’

Example 2: In the example of a coffee shop, the data for the number of 'Hot Coffee Purchases’ and ‘Iced Coffee Purchases’ could be entered into the Drivers Grid. Additionally, drivers for the ‘Average Price per Hot Coffee’ and ‘Average Price per Iced Coffee’ could be created. Then, using a ‘Formula/Driver’ value rule, the Sales account could be calculated according to the following formula:

(‘Hot Coffee Purchases’ x ‘Average Price of Hot Coffee’) + (‘Iced Coffee Purchases’ x ‘Average Price of Iced Coffee’)

Additional knowledge & common questions:

Did this answer your question?