In this article
Budget amounts entered in the Accounts table
In the Accounts table, there is a Budget column where the budget for the current year can be entered.
The Budget table, on the contrary, offers many more possibilities, and allows you to enter budget transactions, creating a detailed planning of your financial and cash (or cash equivalent) situation.
For more information and examples please visit the Financial planning page.
Adding the Budget table
If your accounting file doesn't have the Budget table yet, proceed as follows.
- Go to the Tools menu
- Add new functionalities command
- Choose Add Budget table
Attention: this operation cannot be undone; if you want to go back to the previous situation, keep a copy of the pre-existing file.
Thereafter, the program will:
- Add the Budget table, carrying forward the budget values indicated in the Accounts table.
- Lock the Budget column in the Accounts table.
The values of this column are being calculated according to the budget transactions, using the period defined in the accounting tab basic data.
Preparing the budget
- Enter the individual budget transactions in the Budget table
Enter future operations as if they were normal transactions, but with a future date.
For example:- If you want to have a budget for the monthly sales, enter a sale transaction: enter your cash or cash equivalent account in the Debit account column and enter the sales account in the Credit account column, for each month. As date you can enter a date around the end of each month and as amount you can enter the amount you expect it to be.
If you want to have a budget for each product sale, enter some sale transactions for each product with your expected sale volume. - For your expenses budget, enter some transactions entering your expense account in the Debit account column your cash or cash equivalent account in the Credit account column.
If there are recurring operations, you can use the Repeat column to specify the repetition pattern.
- If you want to have a budget for the monthly sales, enter a sale transaction: enter your cash or cash equivalent account in the Debit account column and enter the sales account in the Credit account column, for each month. As date you can enter a date around the end of each month and as amount you can enter the amount you expect it to be.
- Display the budget data
- Go to the charts of accounts, and then into the Budget view, where you can view the total budget for your accounting period.
- You can also use the Balance sheet with groups print option and check the Budget column option, which will display budgeted date next to the actually registered data.
Budget table columns
The Budget table columns are very similar to the Transactions table columns. In the following example, to make it easier to understand, we used accounts with letters. Normally you would use your own account numbers.
In order to prepare a budget is normally enough the amount column.
For more elaborate budgets you can also indicate the quantity, the unit prices, or you can enter a formula. The program will automatically calculate the amount in the Amount column.
Entering the Budget transaction rows
In the Budget table, the transactions can be entered in exactly the same way as in the Transactions table, indicating the date of the operation, the Debit and the Credit accounts and the amount.
You will here find the budget columns explanations (not all columns are visible in the image above):
- Date column
The future date when you expect the operation to take place:
If you cannot precisely define the date, for example a monthly sales budget, enter the end of the month date. - Repeat column
A repetition code can be entered, possibly preceded by a number
(3M quarterly, 6M per semester, 7D weekly, 3ME quarterly end of month)- Empty: no repetition is taking place
- "D" for a daily repetition (Day)
- "W" for a weekly repetition (Week)
- "M" for a monthly repetition (Month)
- "ME" monthly repetition, but with a date at the end of the month.
If started the 28.02.2017, the next date will be 31.03.2017 - "Y" for a yearly repetition (Year)
- "YE" yearly, but with a date at the end of the month.
If started the 28.02.2015, the next date will be 29.02.2016.
- End column
The date beyond which no more repetition should appear is being indicated. - Variant column
A possible variant form of the budget can be indicated here, in combination with the Apps. - ForNewYear column
Here you can indicate how the transfer is to take place when Creating the New Year- No value: the date is being augmented by a year
- "1" The date remains the same
- "2" The operation is not being transferred to the new year
- Debit account and Credit account, CC1, CC2, CC3 columns
Just as in the Transactions table, you use these columns to define on which accounts the operation should be registered.
You can also use segments and cost centers, to obtain a budget by segment or cost center. - Quantity column
In this column you should enter the quantity description, for example sqm, ton, pc, .. - Price/Unit column
The unit price that, multiplied by the quantity, should give the total amount.- In the Income & Expenses accounting files, you should enter the price unit in negative (with a minus sign), so the amount will be entered in the Expenses column
- Amount column (in basic currency)
The amount to be recorded.
If the row has a repetition, the amount is indicated only in the first transaction; the amounts of the following transactions is shown in the account card.
The program will automatically calculate the amount in the following cases:- If there is a value in the Quantity or Price/Unit columns, the amount is automatically calculated according to the content of these two columns.
- In a double-entry accounting file the result will be a positive amount
- In an Income & Expenses accounting file, if the result is positive it will be considered an income, if it is negative it will be considered an expense.
- In case a formula was entered, the amount will be the result of the formula.
Formulas have priority over quantity and price/unit.
- If there is a value in the Quantity or Price/Unit columns, the amount is automatically calculated according to the content of these two columns.
- Total column
This is the sum of the amounts of the repetitive rows that are part of the specific accounting period.
To display the different amounts, use the Account cards command. - Formula column (in basic currency)
This column gives the user the possibility to enter calculation formulas, of the javascript language, plus the programming functions of the Banana Apps as well.
If there is a formula (or whichever text) the value of the Amount column is being set up according to the result of the formula. See below how to use it.- In a double-entry accounting file the formula result must always be a positive amount
- In an Income & Expenses accounting file, if the result is positive it will be considered an income, if it is negative it will be considered an expense.
- Amount in account currency column
This is the transaction amount in account currency (see multi-currency transactions).
This amount is necessary to calculate the value in basic currency, at the indicated exchange rate.
If there is a formula, the value is the result of the calculation of the formula.
In case of repetition, the first amount is being indicated. - Formula amount in account currency column
A calculation formula can be entered.
In case of repetition, the first amount is being indicated; the amounts of the following transactions is shown in the account card. - Total amount in account currency column
This is the sum of the amounts in account currency of the repetitive rows that are part of the specific accounting period.
If there is no date or if the initial or final date are not part of the accounting period, this column will be empty.
Functions you can use in the Formula column
If you want to see examples about formulas, please visit the following pages:
- Transactions template with use of Quantity and Formula columns.
- Transactions template for the multi-currency accounting with use of the Formula column in basic currency.
In the Formula column you can enter a formula in JavaScript language, plus the Banana Apps programming functions.
If there is a formula (or any text), the value in the Amount column is set according to the formula result.
It allows you to enter calculation formulas, in the JavaScript language, plus the Banana Apps programming functions.
- Last operation result.
The formula is executed and the result displayed.
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 - Decimal separator.
As decimal separator JavaScript only uses the point "."
If you use a different separator, the number is likely to be truncated. - 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.
- row.value ("JRepeatNumber") returns the progressive of the repetition.
- budgetCurrent
It is a table that contains the budget rows after the repetitions creation. - budgetExchangeDifference (account, [date, exchangeRate])
This formula recalls the Banana.document.budgetExchangeDifference function. - BudgetGetPeriod(tDate, period)
Returns to the start date (startDate) and the end date (endtDate) relative to the date and the period where the period can be:- "MC", "QC", "YC" to indicate the month, the quarter or the current year.
- "MP", "QP", "YP" to indicate the month, the quarter or the previous year.
- t = BudgetGetPeriod('2015-01-01', 'MP') returns
t.strartDate // 2014-12-01
t.endtDate // 2014-12-31
- The following functions are similar to those available with Banana.document, however, with the added possibility to indicate as startDate the period of BudgetGetPeriod so that the function uses the current date and as a start date and end date the date returned of BudgetGetPeriod.
budgetBalance('1000', 'MP'); //returns the balance of 1000 to the end of the previous month
budgetTotal('1000', 'MC'); //returns the total movement of the 1000 account for the current month.- budgetBalance(account, startDate, endDate, extraParam)
- budgetOpening(account, startDate, endDate, extraParam)
- budgetTotal(account, startDate, endDate, extraParam)
- budgetBalanceCurrency(account, startDate, endDate, extraParam)
- budgetOpeningCurrency(account, startDate, endDate, extraParam)
- budgetTotalCurrency(account, startDate, endDate, extraParam)
- budgetInterest( account, interest, startDate, endDate, extraParam)
- credit(amount)
If the amount is under 0 returns the amount in positive, if not 0 returns. - debit(amount)
If the amount is greater than 0 returns the amount, if not 0 returns. - 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.
- include "file:test.js"
Variables
It is possible to define and use variables directly in the rows.
The variable must have been previously defined.
price = 10; total = price * 5;
User defined functions
The user can define personal function with the JavaScript language, and recall them in the formulas.
It is possibile 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
function Taxcalculation(profit) { var percentage = 10; if (profit > 50000) percentage = 10; else if (profit > 100000) percentage =20; return profit * percentage / 100; }
Calculation and recalculation sequence
Each time that you change an amount in the Budget table, or that you manually recalculate the file (Shift+F9), the program:
- If it exists, first of all the program executes the "_budget.js" document content.
- It recalculates the Budget table rows:
- The program generates the repetitive row according to the Initial Date, Final Date and Repetition columns.
If there is in January a row with a monthly repetition, 12 identical rows will be created, each one with a date of a different month. - The budget rows are sorted by date (if they have the same date they will be listed in the order they were entered)
- The transaction amount is calculated according to the quantity and unit price or, if there is a formula, according to the formula result.
- For the multi-currency accounting, the program executes first the formula in the account currency, and then in the basic currency.
If there is no formula for the basic currency, the program will use the historic exchange rate and calculates the value in the basic currency. - For the accounting files with VAT, the VAT is calculated according to the transaction's amount.
- Rows previously processed will be used for the calculation of the following rows.
The accounts balance, at the budget row's date, will include the amounts previously processed.
If for example, in a February transaction you enter a formula to calculate the balance for the whole year, you will only get the balance up to the end of February.
- The program generates the repetitive row according to the Initial Date, Final Date and Repetition columns.
- It recalculates the budget values in the Accounts column, according to the budget transactions and to the opening balances.
- It updates the Total column amount in the Budget table.
If the initial or final date of the accounting is modified, as well as other values that are used for the calculation of the budget amounts (for example the VAT table), you need to operate a manual recalculation (Check accounting command from the Account1 menu).
If you have many rows, with many recurring transactions and with a long calculation period, the program may slow down when recalculation the Budget table. In this case we suggest you to uncheck the manual recalculation in the File & accounting properties (File menu).
Printing a budget
In order to print your budget, use the Balance sheet with groups command from the Account1 menu. In the Columns section, activate the Budget column.
If the budget was entered in the Budget table, you can also print the budget by period.
Creating the New Year
When creating the New Year, the program automatically recreates the budget transactions for the new year, according to the values of the New Year column in the Budget table. If this column is not visible you can activate it using the Columns setup command from the Data menu.