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
In a forecast, click New prediction in the top right of the window.
Click Manual prediction, then on the Prediction method drop-down, select Formula.
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
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
Account
Select the account you want to set against the prediction.
VAT/GST/sales tax rate
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.
Output Frequency
Output Frequency
Set the output frequency i.e. how often the prediction will be made.
Daily
Monthly
Annually
Output on day
Output on day
Select the date the prediction is to be stored on.
Build your formula
Build your formula
This section is where you build the formula itself for your prediction.
Selecting what to reference
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.
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
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.
Conditionals
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.
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
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
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.
Accounts impacted
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
Formula values used
The Formula values used tab displays any account referenced in the formula. This is very helpful for sense checking.