Examples of the use of formulas in financial forecasts

In this article

Below you will find some examples of how to use formulas to automate financial forecasts.
Please refer to the specific documentations:

formulas

Parameters

When planning, it can be useful to define variables that can and will be used later.
It is useful to create a parameter section with a posting date of January 1st, or another date that corresponds to the first day of the Budget. The parameter variables will then be used in the following rows.

// 30%
costOfGoodsSold = 0.3 
// 5%
interestRateDebit = 0.05
// 2%
interestRateCredit = 0.02
// 10 %
latePaymentPercentage = 0.1

This way, all parameters that can be set are displayed instantly. When a parameter is changed, the forecast is recalculated.

Repetitions or values per month

By using the repeat column, you can plan for the whole year on one recording row.

If however, the activity has seasonal variations, it is recommended to use a forecast by month. For each month, a row is created with the sales amount for the month.
If you wish to make forecasts automatic for several years, it is useful to insert the repetition "Y" in this row, so that the row of the year is also used for the next one.

Prices and sales quantities

When making a sales plan it will be easier to enter values using the quantity and price column. For example, a restaurant can enter the number of covers served per day, week or month and the program automatically calculates the amount. By changing the number of covers or the price, the impact on liquidity and on the result for the year is instantly displayed.

Use of formulas with variables for growth

Use formulas, which are expressed in the Javascript language:

  • The variable must be defined before being used, hence the line, in which it is defined, must be a date preceding the the line in which it is used.
  • Thousands separators in numbers can't be used.
  • The decimal separator is always the "." (Full stop)
  • The names are different for uppercase / lowercase.

You can assign a value to a variable (this name can be freely chosen) and enter the variable name in the following lines to resume the value.
By changing the value assigned to the variable, all the lines, in which the variable is used, are automatically modified.

As an example, you may set the expected sales amount,  proceeding with the following months, by using a formula to increase the amount.

  • Create a "S" (sales) variable , by entering the following text in the Formula column.
    Sales=1000
    The value 1000.00 will be inserted in the amount column
  • In the following lines use the variable by simply inserting the variable name in the formula:
    Sales
    The value 1000.00 will be inserted in the amount column
  • You can add 10% to the amount (multiplying by 1.1)
    Sales*1.1
    The value 1100.00 will be inserted in the amount column (The value of the Sales variable does not change)
  • You can increase the value of S
    Sales=Sales+200
    The value 1200.00 will be inserted in the amount column (The value of the Sales variable will change)
  • If the variable is used in the following line, there will be the new calculation
    Sales
    The value 1200.00 will be inserted in the amount column

You can also define a variable to define the percentage of growth.

  • Percentage=1.1
  • Amount=200
  • In formulas you can use the variable name instead of the number
    • Sales*Percentage
    • Sales=Sales+Amount

Variables and repetitions

Let's say you expect the turnover to rise by 5% every month.

  • Enter two lines where you assign the value to the variable, without putting any account with the start date of the year.
    Sales=1000
    Increment=5
  • Then create a line with the repetition where you enter the accounts and the formula
    Sales=Sales*(1+Increment/100)

Each time the row is repeated the value of the variable S will be increased by 5% and consequently also the amount of the transaction. By simply changing the growth percentage, the forecast will be recalculated.

In a row you can also insert multiple Javascript instructions, by separating them with a semicolon ";"
Sales=1000; P=5

Use of budget formulas

There are functions that allow you to access the balances and movements of the accounts for planning up to the row that is calculated.You can enter a formula that through the budgetTotal function, recovers the value of the sales account "SALES" of the previous month.

credit( budgetTotal("SALES", "MP") )

The budgetTotal function takes account numbers and periods as arguments. An abbreviation can be used instead of the period.

  • MP stands for previous month.
  • QP stands for previous quarter.

Revenues show in Credit, therefore the value returned by the function will be negative and will not be accepted as an amount in the double entry accounting.
Therefore use the credit () function, which uses the negative value and turn it into a positive value.

Variables for monthly sales

Sales may vary for each month. In this case it is useful to use separate registrations for each month with the variable name.

sales_01 = 1000
cost_01 = sales_01 * costOfGoodsSold 
sales_01 = 1100

Deferred payments

If you want a very precise liquidity plan, it is useful to separate the sales, which are paid immediately and those that are deferred.
One approach may be to record all sales as if they were paid cash, assigning the value of the monthly variable to the amount.

sales_01 = 1000

A registration is then entered, which reverses the sales that are deferred, calculating the amount with the formula.

sales_01 * latePaymentPercentage

A payment registration with the same formula will then be inserted for the following month.

Selling costs

There are costs that can be related to sales (cost of goods) or to other costs (social charges, in relation to wages).

Cost calculation with variables

If the sales are defined with variables, the sales costs can also be indicated as a percentage of the sales.

  • You can define variable S for sales and variable C for the percentage of the cost.
    Sales=1000
    Cost=60
  • The formula for the calculation will be:
    Sales*Cost/100

You can also use the same approach to calculate social security charges.
This formula can possibly be entered in a repetitive row.

Sales cost calculation with budget functions

When the costs are related to sales, the budget formula can also be used.

credit( budgetTotal("SALES", "MC") )*60/100

When the costs related to sales, "MC" stands for the current month.
This formula will return the sales value of the current month, turn them into a positive and multiply them by 60 and divide by 100.

The date must be beyond the sales registration dates, obviously.

It may be used it in a repeat row, inserting the month end date. Therefore, the costs of the sale will automatically be calculated based on the sales entered with the transaction.

The formula can be combined with variables.

  • At the beginning of the year define the percentage of costs.
    Cost=60
  • Therefore, the formula C is used.
    credit( budgetTotal("SALES", "MC") )*Cost/100

When you change the contribution percentage or any sale, the schedule will be updated automatically.

Sales commission calculation at the end of the year

At the end of the year,  you calculate the commissions of 5% on the total net sales with this formula:

credit( budgetTotal("SALES", "YC") )*5/100

The bugetTotal function returns the movement of the sales account for the period "YC", current year. With the credit () function, the amount is turned into a positive value and then multiplied by 5 and divided by 100.

If you make forecasts over several years, remember to insert the repetition "Y" in the row, so that the same formula will be also calculated for the previous year. As indicated above instead of entering the 5 directly in the formula, you can assign it with a variable.

  • Commission=5
  • credit( budgetTotal("SALES", "YC") )*Commission/100

If the percentage changes from one year to the next, it is sufficient enter a transaction with the following year date that resets the variable for commissions.

Inflation with variables

If you want to forecast over several years, you can also take inflation into account.

  • At the beginning of the planning, assign a base variable for prices and inflation (2%).
    Base=1;Inflation=2
  • When you use the Sales variable, you multiply it by the inflation rate
    Sales=Sales*Base
  • At the beginning of the following year, with annual repetition, you increase the price base
    Base=Base+Base*Inflation/100

Depreciation calculation

Thanks to the formulas, the calculation of depreciation can be automated.
If you change the value of your investments in planning, depreciation will be automatically recalculated. Make sure that the date of the depreciation calculation line has a date superior to that of the investments. Date is generally 31st December .

Depreciation calculation on book value

To calculate the depreciation of the "EQUIPMENT" account, insert a line at the end of the year with the following formula and the debit and credit accounts appropriately set up to register the depreciation.

budgetBalance("EQUIPMENT")*20/100

The budgetBalance function returns the balance to that date. The amortization of 20% is then calculated on this.

Use the debit function, in case you think the asset account can go into credit.

debit(budgetBalance("EQUIPMENT"))*20/100

Calculation of depreciation on the initial value

To calculate the initial value of the investments, it is necessary to use variables to remember the value of the initial investment. 

Equipment=10000

If the depreciation is spread over 5 years, the formula will be inserted in the year-end depreciation line

Equipment=10000/5

The annual repetition "Y" and the end date, which corresponds to the date of the last installment of the amortization, will be inserted in the row to prevent the amortization from running on indefinitely.

For each investment you will have to create a variable and a specific row of depreciation. Numbers can also be entered in variable names.

Equipment1=10000
Equipment2=5000

Interest calculation

The budgetInterest( account, interest, startDate, endDate) function allows you to automatically calculate interest based on the actual use of an account.
The parameters are:

  • Account
    Whose movements are used to calculate interest, in case it will be the bank account or the loan.
  • Interest
    The interest rate in percentage.
    If the value is positive, interest on the debit balances is calculated.
    If the value is negative, interest on the credit balances is calculated..
  • Initial date, which may also be an acronym.
  • End date, which may also be an acronym.
  • The returned value is the interest calculated for 365/365 days.

Interest expense on the bank account

To calculate the interest expense of 5%, insert a line with the end date of the quarter and the repetition "3ME", which contains the formula

budgetInterest( "Bank", -5, "QC")

The interest rate is negative, because "QC" means current quarter. The debit and credit accounts must be the usual ones for recording interest expense. If the interest decreases the bank account balance will also be used in the registration. However, another account can be used if it is paid with another account.

It is important that the "3ME" repeat is used so that the date used will always be the last of the quarter.

To calculate the interest of the month use the abbreviation "MC"

budgetInterest( "Bank", -5, "MC")

Interest on the bank account

For interest income of 2%, use positive interest instead.

budgetInterest( "Bank", 2, "QC")

Interest on fixed-term loan accounts

For fixed-term loans, interest will be calculated and recorded on the specified date.

  • Create an separate account for each loan.
    Use the budgetInterest function indicating exactly the start and end dates .If the date is indicated as text, the notation "yyyy-mm-dd" should be used, then "2022-12-31"
  • Use variables.
    As indicated for depreciation, the loan amount can be assigned to a variable. The interest calculation will be done with a Javascript calculation formula,
    • Define the loan variable
      Loan=1000
    • 5% interest calculation, for 120 days.
      Loan*5/100*120/365.

Profit tax calculation

Profit is the total of the group's profit for the specified period.
To calculate a 10% profit tax, use the following formula.

credit(budgetTotal("Gr=Result","MC"))*10/100

  • Use the the budgetTotal function parameter in the "Gr = Result" group, which indicates that instead of an account it has to calculate the movements for the group.
  • MC, current month, is indicated as the period.
  • The budgetTotal function will return a positive value if there is a loss and negative (credit) if there is a profit.
  • the credit function takes only negative values into account, therefore if there is a loss the tax will be zero.

Payments with deferred or different deadlines

For deferred payments or with different deadlines, you can proceed in two ways:

  • Use variables to which payment amounts are to be assigned.
    Use the variable in question when recording the payment.
  • Create customer or supplier accounts for different credit deadlines.

Other cases

Please tell us about your other requirements, so we can add more examples.

 

 

 

 

help_id
Formulas Financial Forecasts

This documentation is outdated

The most complete and up-to-date documentation is the one of Banana Accounting Plus: Try it now

Share this article: Twitter | Facebook | LinkedIn | Email