In this article

    Tutorial file for using Formulas for Budgeting and Financial Planning

    Universal
    Double-Entry Accounting
    Education

    See examples and play with Javascript formulas and quantities for of interest calculation, revenue projections, depreciation and more. The possibility to enter formulas is available in Banana Accounting Plus only with the Advanced plan.

    Open in Banana WebApp

    Run Banana Accounting Plus on your browser without any installation. Customize the template, enter the transactions and save the file on your computer.

    Open template in WebApp

    Template documentation

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

    This file contains examples of how to use the Formula tab in the Budget table, as well as the Quantity and Unit price columns.

    For explanations about the use of the Budget table and columns consult Budget table page.

    Keep in mind that calculations are made in chronological order and not according to the sequence in which your budget registrations were entered.

    Use of Quantity and Unit price columns

    In the Formula view you will see the Qt. (quantity) and Unit/Price columns.

    • Doc 10. The Qt column. The number (Unit column) of coffees sold in one month at the price of (Unit/price column) and total income from coffee sales (Amount column).
    • Values in the Qt and Unit/Price columns can also be entered as a negative number, but the amount will be shown as a positive value.

    Formula in JavaScript language

    The Formula column allows you to enter calculations expressed in JavaScript language.

    • If the result of the formula is a number it will be shown as Amount in the respective column.
    • Doc. 21+22. You may specify numbers or mathematical expressions.
    • Doc 40 e 41. "//" indicates that a comment is entered.
    • Doc 42. Text entered between /* e */ are treated as comment and not part of the formula.

    Formula errors

    In case of errors, when you enter data:

    • The row containing the error is highlighted in red.
    • An error text is shown in the “message” window.

    In order to avoid constant acoustic signals and popping up of error message windows, the errors have been treated as a comment in the example file (they start with "//").

    Semicolon separator ";"

    JavaScript uses ";" to terminate each line of command. You may concatenate mathematical expressions into one line. The return value will be the result of the last expression.

    Comma separator ","

    In JavaScript, comma is used as a separator between the different expressions in a function. The result value will be the result of the last expression.
    Do not use the comma as a decimal separator, because in the formula it will be considered as an expression separator, and therefore numeric values will be truncated.

    Full stop separator for decimals "."

    JavaScript uses full stop "." as a separator for decimals. Regardless of the settings for decimals in your operating system preferences, full stop will always be used.

    Calculations with simple numbers

    You can compound mathematical calculations with simple numbers in the Formula column.

    Texts and variables

    • Text is contained between quotation marks, as quotes.
    • Variables are the elements that mention the values that have been assigned.
      • The name of the variable must start with a letter or an underscore.
      • First of all, variables must be defined via a “variablename = 100” assigned punctuation.

    Examples:

    • Doc 70.
      "Banana" between quotation marks is a text
    • Doc 101.
      A value of 100 is assigned to the variable.
    • Doc 102.
      The content of the Amount variable will be used.
    • Doc 103.
      The content of the Amount variable will be used, with an increment of 10.
    • Doc 104.
      The content of the Amount variable is assigned to the Amount variable with an increment of 10. The Amount variable will be equal to 110.
    • Doc 105.
      The Formula will be applied on a monthly basis “Repeat=M”
      • For the first time 110+10 = 120
      • For second time 120 + 10 = 130, and so on.
    • Doc 106.
      If no comment is entered, an error message will signal that Amount1 has not been defined.
    • Doc 110.
      The name “Banana” is assigned to the text Variable.

    Controls for if .. then .. else flows

    You can use all the conditional expressions of JavaScript. The subject is broad and we suggest you to refer to the documentation on the JavaScript language.
    In this regard we only want to point out that in JavaScript:

    • The equal sign "=" is used to assign a value to a variable "a = 10"
    • On the contrary, for comparison, two successive equal signs are used "==". If (10 == 10) {1};

    • Doc 200-203
      If the criteria given in round brackets after the word "if" is true "(10 == 10)", then the Amount column the result of the next expression contained in curly brackets "{1}" will be shown.
      If it is not true "(10 == 9)" the amount will be zero.

    Properties and Functions

    Predefined JavaScript functions, such as Module Math.library, can be used (see examples Doc 220-222).

    To call up a function, indicate it's name followed by the parameters of the function, indicated between brackets.

    • Doc 220.
      Math.min(10, 30, 2) will return the minimum value of the values specified between commas.

    “Row object” for current row

    Row object is of the Banana.Document.Row type an refers to the current row.

    Specifying the name of a column via the Value function, will return the content of that specific column.

    Predefined Debit and Credit functions

    Formula only allows for positive values.

    • The debit function returns the value entered as a parameter if it is positive.
    • The credit function returns the value with as inverted, if the entered value is negative.

    Accounting functions for the entire period

    Without indication of a defined period, the use of specific Budget functions will return the values relative to the present situation. 
    Please note and remember, that data in rows is processed sequentially, so there will be no subsequent values to the line being processed.

    Use of accounting functions for a defined period

    Use of accounting functions for a defined period.

    The budgetBalance function for the current period will always be the same as the function for the entire period, as the budgetBalance function returns the balance at the end of the period and the last row processed is also the last of the current period.
    he budgetOpening and budgetTotal functions, on the contrary, take the initial date into account so it's indication is necessary.

    Interest calculation function

    The budgetInterest function calculates interest at entering date, on the indicated account on an actual days count basis (365/365.
    When calculating interest for the current month, you must indicate month end as the date. 

    • Doc 500.
      The second parameter represents the interest rate, 5 meaning 5%. If debit interest is due, calculation will be effectuated for the corresponding days.
    • Doc 520.
      If credit interest is due, calculation will be effectuated for the corresponding days. This will show as a negative towards the BANK account.

    Functions for calculating tax on profit

    • Doc 600.
      The tax on profit is calculated on the movement in the "budgetTotal" period of the final group of the Profit & Loss statement (in this case the 02).
      credit (budgetTotal ("Gr = 02", "YC")) * 0.10
      • You obtain the group movement in the current year
        budgetTotal ("Gr = 02", "YC")
      • The credit () function returns the value only if it is negative and converts it to positive.
        credit (budgetTotal ("Gr = 02", "YC"))
      • The result is multiplied by 0.10 (percentage 10%)
      • In the Debit account, the Tax account of the Profit & Loss statement must be indicated.
      • In the Credit account, you must indicate the Bank account or the Treasury credit account.

    User-defined Functions

    In the Documents table, a "_budget.js" file is added where you can define functions in Javascript that can be called in the formula column.

    Calculating tax on profit with progressive rate

    • Doc 610
      The profit is first calculated and assigned to the variable
    • Doc 611
      The profit is passed on to the calcTaxes() function that applies the tax percentage based on the amount and returns the tax amount to be paid.

    Other user-defined functions

    • Doc 620
      The test () function is called, which is also defined in the budget_js document, which simply returns a value.

    Use of Variables for next calculations

    Variables are very useful for storing values for next calculations.

    • Doc 700-704.
      Indexation of sales.
      • Doc 700.
        Fixation of the index at 1 (100%).
        Debit A/C and Credit A/C are not used here, as this row is only used for Definition of the Price index.
      • Doc 701.
        pscosts variable is used for calculation of acquisition cost (0.2=20%)
      • Doc 702.
        Sales are a 1000 multiple of the price index value.
      • Doc 704.
        As per 1st of March the index will be increased by 0.1 (=10%).
        As per this date, value of sales will increase in a monthly progression.
    • Doc 720-722
      Monthly sales figures are assigned to months function. Sales commissions are then calculated

    JrepeatNumber Column

    When Rows contain a Repeat, insert the Repeat number in the Formula cell as JrepeatNumber.

    • First repeat value is 0.
    • Second repeat value is 1, and so on.

    Use JRepeatNumber when Repeat doesn't allow you to overwrite predefined Variables.
    By using JrepeatNumber as a parameter for your object, sequences are registered and can be called at a later stage,

     

    Template preview

    Luke's coffeehouse

    Basic Currency: EUR

    Double-entry

    Table: Accounts
    Group Description Sum In Gr1
        BALANCE SHEET    
        ASSETS    
      Cash Cash on hand 1  
      Bank Bank account 1  
      Customers Customers account 1  
      Furniture Furniture 1  
      Inventory Inventory 1  
    1   TOTAL ASSETS 00 1
             
        LIABILITIES AND EQUITY    
      Suppliers Suppliers 2  
      Loan Loan 2  
      Equity Owner equity 2  
    PL   Profit(-) or loss (+) from current year 2 1
    2   TOTAL LIABILITIES AND EQUITY 00 1
             
        PROFIT AND LOSS STATEMENT    
        INCOME    
      Sales Income from sales 4  
      Interest income Interest income 4  
    4   TOTAL INCOME 02  
             
        EXPENSES    
      Personnel Personnel expenses 3  
      Goods Cost of goods 3  
      Rent Rental and related expenses 3  
      Administration Administrative costs 3  
      Interest expense Interest expense 3  
      Depreciation Depreciation 3  
    3   TOTAL EXPENSES 02 1
             
    02   Profit(-) Loss(+) from Profit and Loss Statement PL  
             
    00   Difference should be zero (void)    
             
    Tell us how we can help you better
    If the information on this page is not what you're looking for, is not clear enough, or is not up-to-date, let us know.

    Share this article: Twitter | Facebook | LinkedIn | Email