The Date column

The value contained in the Date column is the one that will validate the forecast.

• If there is no date, the transaction will be taken into account at the beginning of the planning.
  If you require a forecast indicating a period, the amounts will be taken into account in the balance value at the beginning of the period.
  Due to the fact that it does not fall within the period, no amount will be displayed in the Total column.
• Dates in the accounting period set in the file properties.
  These are the ones commonly used. The total amount will be indicated in the Total column, taking into account repetitive movements, within the accounting period.
• Dates prior to the period.
  You can enter movements that precede the forecast period. However, you must be careful that they do not conflict with the opening balances entered in the account table.
  Due to the fact that it is not within the period, no amount will be displayed in the Total column.
• Dates after the period.
  If you make forecasts over several years, they allow you to indicate transactions for the years to come.
  Due to the fact that it is not within the period, no amount will be displayed in the Total column.

The End date

This is used in combination with repetition, to indicate the last date beyond which there is to be no repeat.

• Must generally be left empty.
  If you enter a date when it is not necessary (for example the end of the accounting period) the forecasts for the following years will not include this operation.
• For leasing transactions.
  Indicate the date on which the payment of the last installment will be made as the end date.
• For loan repayment.
  Indicate the last expected payment date.
• For changes in the amount at set deadlines.
  In the case, for instance, that the amount of a recurring transaction is adapted at certain deadlines (salary increase).
  • Create a transaction row with repeat "M" and End date as per the last payment before the increase.
    Create transaction rows with Dates when the increase begins.
  • If the increase follows a precise and regular automation, this can also be programmed with formulas.

Repetitions

For recurring expenses or income, the repetition code is recommended (refer to Documentation on the columns).

• When calculating the forecast, the program creates copies of the operation and progressively increases the date, taking into account the indicated frequency.
• If there is no end date, the program will generate internal copies of the records for the entire period of the forecast indicated at the time of the report when calculating the forecast.
  • If the start date is January, the frequency is monthly
    • If the forecast period is the year, it will generate rows from February, 1 original row and 11 automatic rows, for a total of 12.
    • If the forecast period is 10 years, it will generate rows starting from February, 1 original row and 119 automatic lines (11 the first year + 12 * 9 for the following ones), for a total of 120 lines.
• If you want the program to automatically calculate forecasts for subsequent years.
  • For recurring operations, indicate the relative repetition code.
  • For transactions that occur only once a year (for example depreciation at the end of the year), indicate the repetition code "Y", so that the depreciation is also calculated in the following years.
  • Don't use the repetition only if the operation will not occur in the following year.

Total column of the Budget table

The Total column is calculated automatically and represents the sum of the amounts of the current row and the repetition amounts that fall within the accounting period. The Total column is empty if the transactions have an earlier date or extend beyond the accounting period.

Schedule with precise date and monthly logic

Forecast movements are entered in the Budget table indicating the date on which these are expected to occur.

However, it is not always possible to predict all revenues and expenses with a daily precision. When making a forecast it is therefore useful, in some cases, to use approximations, generally reasoning on a monthly basis. In any case, it is useful to always follow a specific logic so that reliable liquidity forecasts can also be obtained in the short term:

• Punctual operations (capital payment, investments) are indicated with the date on which they are expected to occur.
  If there is no precise date, it is useful to indicate them on the 15th day of the month in which they are expected to occur. 
• Recurring charges that have a precise payment date are to be set with the expected payment date and the relative repetition code:
  • Bank charges, interest, amortization are to be indicated at the end of the month, quarter or year that they will take place.
  • Rentals on the due expected date of payment.
  • Salaries and social security charges
    • For the calculation and monthly payment on the day that wages are paid.
    • For thirteenths, bonuses or whatever at the moment they are paid.
    • Payments for advances and adjustments of social security charges on the expected payment date.
  • Payments and VAT adjustments on the typical payment due day.
• Revenue Forecasting.
  The preparation of the forecasts depends on the type of activity.
  If you do not know the exact day, but you will know that it will happen in a certain month, it is recommended to indicate the 15th of the month
  • Punctual revenue.
    They are to be indicated on the date on which it is expected to take place or mid-month.
  • Recurring revenue.
    To be indicated on the date of entry or mid-month.
  • In many cases a monthly forecast is well suited. The revenue can be indicated on the 15th of the month.
    • If the revenues are recurring, the revenue can be entered with the monthly repetition. Using formulas you can predict growth.
    • If there are seasonal differences, it is helpful to have a sales forecast row for each month.
  • Forecast of projects or major works.
    If there is a calendar with receipts, a transaction row is to be indicated for each expected entry. It can be approximated by indicating the 15th of the month.
  • Forecast for customers.
    For a consultant or commercial advisor, who works both with budgets and projects, it can prove very useful to set up a detailed revenue forecast for each client, with the expected payment dates. This forecast will also be very useful to check if the customer has actually paid.
• Variable costs.
  • Constant expenses linked to the turnover (a restaurant for example ), are indicated with the same date as the turnover. With a formula you can also calculate as a percentage value of the turnover.
  • Costs can also be linked to other elements, such as the number of employees, rented premises or other.

Forecasting with the cash principle

Planning for small business and cash activities (such as shops, restaurants) it is useful to proceed with the cash principle, then indicate the revenues with the date on which they will be collected and the costs when paid.

For important operations, such as the purchase of a machine whose payment is deferred over time, it is however useful to insert forecast movements with precise details:

• Purchase of machinery (asset registration with suppliers) with date of purchase.
• Payment of the machinery, with the date(s) on which payment is expected.

Forecasts with the accrual method

In this case, the insertion of the operations takes into account when the payment will be made.

• Cash transactions.
  They are obviously registered normally.
• Transactions expected to be settled in the near future.
  For simplicity, the operations that fall within the forecast month or the one immediately following, it can be useful to use the cash principle.
• Deferred payments.
  If the dates are not known with precision, you can use the 15th of the month.
  • Transactions with precise payment terms.
    • One movement indicates the purchase date with the counterpart in the supplier account.
    • One of the other movements indicates payment on the scheduled dates.
• Deferred collections.
  • With precise payment terms, as is the case with a project:
    • A movement indicates the billing date and the customer account with the counterpart.
    • One of the other movements indicates payment on the scheduled dates.
  • Late payment.
    This is the case when a part of the revenues is collected in the short term, a percentage is collected later. It can be done like this:
    • Enter the revenue as cash collection.
    • With the same date, a movement is created that moves a part of the collection to the customer account.
    • At a later date, the cash collection is indicated with the client account of the counterpart.
• Use of variables for deferred collections and payments( see Example use of variables)
  When the same value must be reused at the time of payment, it can prove very useful to use the formula column and variables.
  • In the billing transaction, the amount is assigned to a variable.
  • In the payment movement, the variable is inserted so that the amount is automatically taken over.
  • Variables can also be used to define the percentage of the amount that will be deferred, for example.

Scheduling transactions are entered like normal entries, with the date, description, amount, debit and credit account.

In addition, the Repeat column is used, which allows recurring transactions to be entered on a single line.

Below are several examples, taken from the template below, to which we refer for further explanation.

• Double-entry accounting tutorial file budgeting and financial plan for a startup.

Start-up forecast registrations

These are forecast entries as those registered in the Transactions table.

The example shown here refers to the start of the activity, so it concerns transactions that are not repeated.

Monthly repetitive entries

Below are examples of entries with monthly repetition, code 'M'. The column Total shows the total amount for the year.

The first two entries refer to the rent, which from February to June is 1'000 monthly, while from July it is 1'200.

The date 2024, when the lease redemption amount must be paid, is also indicated. In this line, in the column Total, there is no amount, because the movement does not fall within the dates of the accounting period defined in the file properties.

The repetition code "ME" Month End is used for administrative expenses counted by the bank. For subsequent entries the day 28 is not used, but the last day of the month, thus March 31, April 30.

 

Quarter-end

Here we indicate some typical entries that repeat at the quarter end. We enter the 3ME repetition, which means repeat every 3 months, with end of month date.

End of Year

At the end of the year there are operations which are carried out. Use Repeat 'Y' so that these operations are also performed for subsequent years.

Forecast of income and purchase of goods

The income forecast is specific to each activity. In this case, the income and purchase forecast is shown month by month, with a specific amount.

Starting in March, the annual 'Y' repetition is indicated, so that these transactions are repeated in subsequent years.

Purchases and next year sales

The months of January and February, in the first year, were not considered significant for the following years, so they did not have a repeat.
For the first two months of the second year, we have to set sales. We also set the annual 'Y' repetition, so that we can repeat the income in the following year.

The Quantity, Unit and Unit Price columns of the Budget table allow you to prepare forecasts faster.

The value of the Amount column is calculated by the program by multiplying the Quantity by the Unit Price (under the condition that Formula column is empty).

The Quantity, Unit Price columns are set as visible in the Formula View.

Advantages of using the Quantity and Prices columns

The quantity and price columns are useful for making predictions based on quantities. For example:

• In the Unit column you can indicate what the price refers to.
• The Quantity column indicates the number of seats served daily.
• The Price column indicates the estimated revenue for each cover.
• The Amount column will be calculated automatically based on the values indicated.

This approach offers several advantages:

• All elements of the planning can be precisely detailed in the Budget.
• We remember the quantities and prices used to make the estimate.
• Changing the schedule is very simple, you can change the element you want only.
• How the profit varies, can be seen with a change in the quantities sold or in the price.
  Break-even analysis.

 

The addition of Javascript formulas in the Budget table of Banana Accounting Plus, opens up several possibilities.  

The use of formulas is available in Banana Accounting Plus with the Advanced plan only. 
See all the advantages of the Advanced plan.

Formula column 

The Formula column allows you to enter calculation formulas. The estimate amounts can thus be calculated on the basis of other values of the same budgeting (see Examples of formulas).

• Indicate the cost of goods based as a percentage of sales.
• Increase your sales budget based as a percentage of growth.
• At the end of the year, calculate the depreciation based on the value of the investments made.
• Quarterly, calculate interest on the bank account based on actual usage (days / 365).
• Monthly, calculate interest on the bank account based on actual usage (days / 365).

If you enter a value in the Formula column, the Amount column is calculated by the program based on that formula.

Formula Begin Column

In the Formula Begin column you can enter any formula, that is pre-pended to Formula and resolved (see documentation of the table Budget):

It is useful to specify  a variable name and the assignment operator, so that you can easily change amounts:

• Instead of writing in the Formula  "Price= 100", you can write
• Formula Begin "Price="
• In Formula "100" , so you can easily see the amount and change it

Formulas in Description column

You can also enter formula in the column text, so that the text is completed automatically when you request an account card  (see documentation of the table Budget).

The Formula in the Description should be entered within curly brackets preceded by the dollar sign. 

• Assuming that in the formula you have defined the variable Price "Price= 100"
• In the Description text you can use the variable price 
  "Sell of goods at price ${Price}" will results in the account card in 
  "Sell of goods at price 100" 

Within the Description column you can use any Javascript formula, but you should avoid use assignment or other calculations, and use only the Description to for retrieving values. 

Example files

For examples of the formulas, refer to the following explanations:

• Template with transactions  using the Quantity and Formula columns.
• Template with transactions for Multi-Currency accounting using the Formula column in the base currency.
• Also refer to Examples of using formulas in financial forecasts.

Calculation formulas in Javascript

• The formula must be expressed in the Javascript language (not to be confused with the Java language).
• If there is a formula (or any text), the value in the Amount column is set according to the formula result.
• You can use all the functions of the Javascript language, plus the APIs provided by Banana.

Decimal separator

In JavaScript only the point "." is used as a decimal separator
If you use a different separator, the one used for numbers in the local format, the number is likely to be truncated.

Amount = result of the last instruction

In Javascript the semicolon ";" is used to separate expressions.
If the Javascript formula contains multiple expressions separated by ";" the value of the Amount column will be the result of the last executed expression.

• 10*3 //30 will be returned
• If there is a sequence of several operations separated by a semicolon ";", the last operation will be resumed.
  10*3;7; //7 will be returned
• If there is a return, the value is resumed after the return.
  return 10; // 10 will be returned.

Variables

Javascript variables are the most powerful elements of programming, as they let you give a name to a value stored in the computer's memory, so you can easily save, reuse, and update that value in your formulas.
Variables do not exist in Excel formulas, but are similar to the name of the cells, with the difference that the name can be freely assigned.

What is a variable?

A variable is like a labeled box where you store a value. For example:

price = 10

This means you’re giving the name price to the value 10. You can then use "price" in other calculations:

total = price * 5

This will multiply the value of "price" (which is 10) by 5 and store the result in a new variable "total".

You can also reassign the value of a variable later:

price = 20

Variable initialization

To create a variable, just write a name, an equal sign (=), and a value.

There are two different ways to create and initialize a variable, and they behave slightly differently:

• Variable with the word "var"

  • var price = 10

  • This creates the variable "price" and the value 10 is assigned to it.
  • The value will not appear in the Amount column. The cell looks empty.
  • The value will be used and displayed only when you refer to "price" in another row.
• Variable without "var"

  • price = 10

  • This also creates the variable "price" and the value 10 is assigned to it.
  • The value appears directly in the Amount column of the same row.
  • Useful when you want to both define the variable and see its value immediately.

You can define and use variables directly within the rows. By entering the variable name in the formula, the saved value is taken over.

 Nullish coalescing operator "??"

Sometimes, you want to give a variable a value only if it doesn’t already have one.
You can do this using a special symbol called the Nullish coalescing operator "??", in this case the value specified after the operator "??" is used only if the price has not been already assigned. 

price = price ?? 10;

This means: “If price doesn’t already have a value, set it to 10. Otherwise, keep the current value.”

Before using "??", make sure the variable exists. You can write:

var price
price = price ?? 10

Or simply:

var price = price ?? 10

The Nullish coalescing operator is useful for repetitions, due that it allows to assign an initial value, the first time only.  So you can than increment the value and by the next repetition the value will not be assigned again. 

Note: The Nullish coalescing assignment operator "??="  is not supprted.

Objects

Javascipt objects are variables that allow you to save multiple values, each indicated with a property.
The prices object is created below, indicating the curly brackets. To access and save the values, instead use the square brackets or indicate in the name of the property after the name of the object.

prices = {}
prices['car'] = 10;
prices.car = 10;
prices['computer'] = 20;
prices.computer = 20;

Array

Javascript Arrays are created using square brackets and also to access them. 
The first element of the array has index 0.

costs = [1,2,3];
costs[0]=3;
result = prices['car'] - costs[0];

Repetition and variables

For more information on the calculation sequence, see Planning Logic.

• All rows (including those created for repetitions) are sorted by date. If there are rows with the same date, the order is that of insertion in the table.
• After the sorting, the rows (including the formulas) are recalculated.
• In the formulas, you therefore only have access to the budget data up to that date.
• A variable must be defined in a row that has the date preceding the row where it is used.
• If the budget entry has a repetition the variable will be reassigned each time.
  sum = 10;
• If you want to calculate the grand total instead.
  • In an initial line create the variable with value zero.
    sum = 0;
  • In the line that repeats in the sum also include the previous value.
    sum = sum + 10;
    or use the "+="
    sum += 10
• Use the Nullish coalescing operator "??" for assigning a value to a variable that is not yet initialized.

Automatic variables

• budgetCurrent
  It is a table that contains the budget rows after the repetitions creation.
  These are used to record values in conjunction with the JRepeatNumber.
• DEBUG  is a variable that can be "true" or "false".
  If "true", in the messages, all the results of the formulas are being displayed.
• row
  Is a Javascript object that refers to the current row.
  • The values of the cells can be retrieved with the value function ("columnNameXml").
    row.value("date") returns to the date of the transaction.
  • row.value ("JRepeatNumber") returns the progressive of the repetition.
    The first repetition is 0.
• _totalPrice
  This is the value of the Quantity column multiplied by the UnitPrice column.
  Equivalent to the formula "row.value('Quantity')*row.value('UnitPrice')"

Budget Functions

In addition to the budget API defined in the accounting class API, there are specific functions.

budgetGetPeriod(tDate, period)

This function is used in combination with the use of repetition. 
When repetitions are indicated, it is advisable to refer to a calculation period and not to a precise date.

• Parameter tDate.
  The date to which the calculation of the period refers. Usually the date of the recording line.
• Period parameter.
  Abbreviations
  • For the current
    • "BC" current bi-monthly (2 months)
    • "DC" current day
    • "MC" current month
    • "QC" current trimester
    • "SC" current half-year
    • "YC" current year.
    • "WC" current week
  • For previous
    • "BP" previous bi-monthly
    • "DP" previous day
    • "MP" previous month
    • "QP" previous quarter
    • "SP" previous shalf-year
    • "YP" previous year
    • "WP" previous week
• Returned values.
  An object composed of two dates
  • startDate
  • endDate

// example
t = BudgetGetPeriod ('2015-01-01', 'MP') returns
t.startDate // 2014-12-01
t.endDate // 2014-12-31

Specific budget functions

The following are similar to those available with Banana.document, but can be used without indicating the object Banana.document.

To be taken into account:

• Instead of the startDate parameter, you can use one of the abbreviations "MC", "MP", explained in the budgetGetPeriod.
• If you use an abbreviation, the function calculates the start and end date of the period, based on the date of the current registration.
• It makes sense to use the end date only if it is earlier than the row date.
  If it is equal or higher, it has no effect because the values after the current row are not yet available, because they have not been processed.
• If the registration row date is April 15th:
  • budgetBalance("1000","MP") the balance of the 1000 account returns at the end of March.
  • budgetBalance("1000","MC") returns the balance at the current time is the same as budgetBalance("1000").
  • budgetTotal("1000","QP") returns total of the movement for the previous quarter.
  • budgetTotal("1000","QC") returns the total of the movement for the previous quarter, up to the current date.

budgetBalance(account, startDate, endDate, extraParam)

The balance up to the current row.

budgetBalance('1000', 'MP'); //returns the balance of 1000 at the end of the previous month

• If budgetBalance returns a negative amount, the program generates an error, because it is not possible to enter negative values in the Amount column of the Budget table.
  • When the account balance is not known in advance, it may be useful to enter two transactions using the debit() and credit() functions.
  • Only one of the two transactions will show the correct balance, while the other will have an amount of 0, depending on whether the balance is a credit or a debit.
  • The two transactions must of course reverse the debit and credit accounts: for example, in the first transaction debit 2210 and credit 1000; in the second debit 1000 and credit 2210.

debit(budgetBalance('1000'));  //returns the balance of account 1000 on the day of the transaction, 0 if negative

credit(budgetBalance('1000')); //returns the balance of account 1000 on the day of the transaction, 0 if positive

budgetOpening(account, startDate, endDate, extraParam)

The balance at the beginning of the period.

budgetTotal(account, startDate, endDate, extraParam)

The difference between the debit and the credit movement of the period.

budgetTotal('1000', 'MC'); //returns the total movement of the 1000 account for the current month

budgetInterest( account, interest, startDate, endDate, extraParam)

Calculates the interest on an account, for the period indicated (at the maximum the current date)
If you use it to calculate interest on an account at the end-of-period, the row where the formula is shown should always be the last one for this date.

• Account parameter
  This is the account number on whose movements interest will be calculated
• Interest parameter,
  Indicates the interest rate in percent.
  • Positive (2.5, 4, 10) calculates the interest on the account's debit movement
  • Negative (-2.5, -4, -10) calculates the interest on the account's credit movement.

credit(amount)

• If the amount parameter is negative returns the amount as a positive
  credit(-100) // returns 100
• If the amount parameter is positive, returns 0 (zero)
  credit(100) // returns 0

This function is useful in conjunction with the other budgetBalance functions to work only on the balances you need.
If you want to calculate the percentage on sales, using this function is easier.
credit(budgetTotal('1000')) // enter the value only if it is a negative.

debit(amount)

• If the amount parameter is positive, returns the amount
  debit(100) // returns 100
• If the amount parameter is negative, returns 0 (zero)
  debit(-100) // returns 0

Useful if you have to make calculations using only the debit amount and avoid using the credit amount

include

Includes and executes a Javascript file, with the possibility to create its own functions and variables that can be recalled in the script.

• Include "file:test.js" 
  Executes the contents of the indicated file. The name refers to the file on which one is working.
• Include "documents:test.js" 
  Executes the contents of the text document contained in the documents table.
  This has to be a file of the "text/javascript" type.

Functions for multi-currency accounting

They can also be used for accounting without multi-currency, in this case the account is always in basic currency.

budgetBalanceCurrency(account, startDate, endDate, extraParam)

The balance in the account currency up to the current line.

budgetExchangeDifference (account, [date, exchangeRate])

This formula recalls the Banana.document.budgetExchangeDifference function.

budgetOpeningCurrency(account, startDate, endDate, extraParam)

The balance in the account currency at the beginning of the period.

budgetTotalCurrency(account, startDate, endDate, extraParam)

Starting with version 9.01, the following functions are also included : budgetCreditCurrency, budgetDebitCurrency.

User defined functions

The user can define personal function with the JavaScript language, and recall them in the formulas.
It is possible to define functions:

• Directly in a formula
• In a JavaScript coded attachment; this attachment must have as row id the "_budget.js" name
• In a text of the Documents table, that needs to be included with the Include command

Note:
In the user functions it is currently not possible to use functions that access current accounting data (currentBalance, ...).

function Taxcalculation(profit)
{
   var percentage = 10;
   if (profit > 50000)
      percentage = 10;
   else if (profit > 100000)
      percentage =20;
   return profit * percentage / 100;
}

 

Banana Accounting Plus GPT Budget formula creator

The GPT budget formula creator is only experimental: it might be subject to changes and improvements.

This Banana Accounting GPT was created using ChatGPT and is designed to generate calculation formulas to be used in the Budget table of Banana Accounting Plus.

Requirements

To use this GPT, you must have a ChatGPT Plus subscription. Without it, you can try the service for free for up to ten requests. After that, you’ll need to either upgrade to ChatGPT Plus or wait some hours before making additional requests.

How it works

• Open the Banana GPT from this link:
  • https://chatgpt.com/g/g-67efab70619c81918bab4f7bdf67a803-budget-formula-creator-gpt-banana-accounting
• In the Message field at the bottom, enter a sentence describing what formula you want to create. Explain what your purpose is. Generally, more specific requests will yield better results.
• GPT will process your request and return the corresponding result.
  Based on the input provided to the GPT, examples and text strings are generated containing the formulas.
• Copy the generated formulas.
• Paste the copied formulas into the Formula column of the Budget table in Banana Accounting Plus.

Examples of messages to enter in GPT

The following are examples of messages to enter into the GPT chat, along with their respective generated results:

Example 1

Input text:
I have the sales amounts for January, February and March 2024. Now I want to calculate the sales forecast for the same months in 2025. I predict that sales will increase by 20% compared to sales in 2024. Let me have the formulas keeping in mind that I want to see the calculated values right away, and then also the example records to enter.

Response:

Date	Description	Account Debit	Account Credit	Formula
2025-01-31	January sales expected 2025	3000	4000	var jan24 = 1000; jan24 * 1.2
2025-02-28	February sales expected 2025	3000	4000	var feb24 = 1200; feb24 * 1.2
2025-03-31	March sales expected 2025	3000	4000	var mar24 = 1500; mar24 * 1.2

 

 

The possibility to enter formulas is available in Banana Accounting Plus only with the Advanced plan. Update now!

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

• Javascript formulas in the Budget table
• Templates with transactions using the Quantity and Formula column

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

When you use formulas, remember that they 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.

Objects for incrementing sales or costs

Instead of using a single variable you can group the elements in an objects, for sale or costs.

s = {} // sales
s.p1 = 100 // product 1
s.p2 = 200 // product 2
c = {} // costs
c.rent = 500 
c.utilities = 200 
c.admin = 100

You can now use a function to increments all element by a value

// function to increment the values within an object by a percentage
function Increment(obj, perc) {for (v in obj) { obj[v]+=obj[v]*(perc/100)}}
// Increment all values
Increment(s,20) // Increments sales by 20%
Increment(c,10) // Increments costs by 10%
// Decrement use a negative number 
Increment(s,-10)// Decrements sales by 10%
Increment(c,-5) // Decrements costs by 5%

If you want to initialize the element in a repetition row use the Nullish coalescing operator "??"

// initialize objects
s = {} // sales
c = {} // costs
// repetition rows assign the value only the first time 
s.p1 = s.p1 ?? 100 
s.p2 = s.p2 ?? 200 
c.rent = c.rent ?? 500 
c.utilities = c.utilities ?? 200 
c.admin = c.admin ?? 100

// Begin next year increments values
Increment(s,20) // Increments sales by 20%
Increment(c,10) // Increments costs by 10%

You can see an example how to insert all data:

 

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.