Skip to main content
All CollectionsForecastingPredictions
Create a 'Formula' based prediction
Create a 'Formula' based prediction

How to manually create a prediction using the 'Formula' method.

Updated over a month ago

When you manually create a prediction, one of your prediction option methods is Formula. This method provides you with a wide range of customisation options to support simple and complex future events.

You can combine certain accounts, reference certain periods, and even use the powerful 'If' option to create variable predictions based on real-life results.


Create a prediction using the Formula method

  1. In a forecast, click New prediction in the top right of the window.

  2. Click Manual prediction, then on the Prediction method drop-down, select Formula.

    GIF showing a user click the Manual prediction option, then showing the prediction method options from its dropdown.


Build your prediction with the formula method

The formula you build is up to you, but there are several steps you need to run through.

Enter your prediction name

Give the prediction a name. This is how the prediction displays within the forecast, or any reports you build referencing it.

Account

Select the account you want to set against the prediction.

VAT/GST/sales tax rate

If you don't enter a VAT/GST/sales tax rate, it will use your default rate set in your settings.

If you want the amount to be different for this prediction, enter a different VAT/GST/sales tax rate.

GIF showing a new VAT rate being entered instead of the default rate.

Output Frequency

Set the output frequency i.e. how often the prediction will be made.

  • Daily

  • Monthly

  • Annually

Output on day

Select the date the prediction is to be stored on.

Build your formula

This section is where you build the formula itself for your prediction.

Selecting what to reference

To locate what you'd like to reference in your formula, type @ to bring up the entire chart of accounts for the relevant organisation. You will also see other predictions that you can reference.

Screenshot showing an example of typing '@' and then being presented with a list of option.

Using the information in the screenshot above as an example, you can locate:

  • Individual accounts such as 'Wedding Sales'

  • Account categories such as 'Income'

  • Pre-made account groups like 'Online Sales'

📌 TIP: Create account groups in the Chart of Accounts.

  • Individual predictions

Basic operators

You can enter the following with your keyboard or with shortcuts in Futrli.

  • Add ( + )

  • Subtracting ( - )

  • Multiply ( * )

  • Divide ( / )

Alongside these are:

  • LASTMONTH

  • LASTYEAR

  • ON DATE

📌 TIP: Use the LASTMONTH option to reference the data from the previous month. You could then, for example, multiply by 1.1 to add 10% to last month.

Entering a formula of '@INCOME LASTMONTH*1.1'

Conditionals

The next level of formula forecasting is creating variable outcome 'if' predictions.

To create an 'if' prediction, click the IF option. This creates the base of the formula, ready for you to override:

IF(condition=true, option A, option B)

EXAMPLE:

In the example below, the formula is going to calculate 20% commission if Income is greater than or equal to £50,000. However it will calculate 10% if it's less than £50,000.

The screenshot below is for comparison purposes only. The first line is what will replace the second line.

Example of a formula. 'AVG(@Income, DATE(2022,01), DATE(2022,06))' then on another line it says 'AVG(Reference, DATE(year, month), DATE(year, month))'
  • The condition is @Income

  • The variable replaces the equals, in this case greater than or equal to

  • True for this example is £50,000

  • Option A is the variable if the condition is hit, so Income multiplied by 0.2 to give 20% of the total of Income

  • Option B is if the condition isn't hit, so Income multiplied by 0.1 to give 10% of the total of Income

Functions

A short step to finding commonly required formulas.

To use these pre-built functions, click the relevant option in question. This creates the base of the formula. You can then add the specifics. The options available are:

  • SUM

  • AVG

For each, override the condition with what you're looking to reference, then set a date range.

EXAMPLE:

For this example, it's the average of Income between January and June 2022 that you'd like to see.

The screenshot below is for comparison purposes only, the first line is what will replace the second line.

Cash flow payment treatment

Set the cash flow payment treatment. The following options are available:

  • Single day payment

  • Single payment - Set the date for payment

  • Multi: in days - Set various dates from different percentages on payment

  • Non-cash transfer - To remove any cash flow impact, select the balancing account

    📌TIP: For more details on non-cash transfers, use your guide, Create a no-cash transfer.


'Accounts impacted' and 'Formula values used' tabs

These two tabs at the bottom of the prediction window enable you to see exactly what the impact of the prediction is going to be.

Screenshot of the create prediction window, At the bottom is an 'Accounts impacted' tab and a 'Formula values' used tab. The 'Accounts impacted' tab is currently selected in this image.

Accounts impacted

The Accounts impacted tab shows you all accounts that will see movements. This ranges from the initial P&L or Balance Sheet account created against the prediction to the VAT/GST/sales tax, bank, and accounts receivable/payable.

📎NOTE: The Accounts impacted tool will display once you give the prediction a Name, select an Account, and begin populating the formula.

Formula values used

The Formula values used tab displays any account referenced in the formula. This is very helpful for sense checking.

Did this answer your question?