The formula builder in Futrli allows you to fully customise forecasts or data in a report. Formulas are available when creating predictions in a forecast or when building specific metrics in a report.

📌 TIP: Use an 'if' formula to set predictions based on unknown circumstances.

Using a formula in a report

When building a report widget, you can use a formula to create specific KPIs.

Within the Builder tool, there is a Create formula button for you to select.

When you type an @ symbol, the chart of accounts displays. You can reference any account categories, groups, individual accounts or non-financial drivers as part of a formula.

Using a formula in a forecast

In any forecast, you can use the formula method when manually adding a prediction. This method gives the Futrli user the option to customise a prediction, with almost unlimited options.

Reference account categories, accounts, groups, non-financial drivers, or even other predictions by typing '@'.

Using Conditional Formulas in Forecasts

Conditional formulas allow you to adjust forecast behavior based on specific dates or conditions. In Futrli, you can nest multiple IF statements within a single formula to handle different scenarios. For example:

IF(DATE()>DATE(2025, 1), @Income LASTYEAR*1.1, IF(DATE()>DATE(2024, 1), @Income LASTYEAR*1.2, 0))

Explanation:

If the current date is after January 2025, the formula uses last year’s income multiplied by 1.1.
If the date is after January 2024 but before January 2025, it uses last year’s income multiplied by 1.2.
Otherwise, the formula returns 0. This approach can be extended with additional nested IF statements to handle more years or conditions.## Setting Forecast Formulas for Specific Time Periods To post values only in specific months (e.g., quarterly or bi-annual), use date-based IF statements. Each month’s condition can be placed on its own line within the same prediction or as separate predictions. For example:
```
IF(DATE() >= DATE([YEAR], [MONTH]), IF(DATE() >= DATE([YEAR], [MONTH] + 1), 0, [ACCOUNT]*[RATE]), 0)
```

Application:

For quarterly forecasts (months 1, 4, 7, 10), create four lines, substituting the month number for each.
For bi-annual forecasts (months 1 and 7), create two lines, one for each month. Replace placeholders like [ACCOUNT] with the relevant account reference (e.g., @Warehouse Costs) and [RATE] with your desired multiplier (e.g., 1.2 for VAT). Update [YEAR] for each year as needed.## Troubleshooting Common Formula Issues

LASTMONTH Formula Errors

The LASTMONTH tag cannot be used when the prediction’s output frequency is set to Annual. To resolve this:

Change the prediction’s output frequency to Monthly if you need to use LASTMONTH.
If an Annual frequency is required, use LASTYEAR instead of LASTMONTH. Additionally, always insert tags using the formula builder rather than typing them manually to avoid errors.## Best Practices for Using the Formula Builder

Use nested IF statements to handle complex conditions.
Clearly define start and stop date windows for time-specific forecasts.
Regularly review and test formulas to ensure accuracy.
Use the formula builder’s tag insertion feature to minimize errors.

Using formulas in Futrli