Key Financial Ratios - Defining
This page describes the configuration of the Key Financial Ratios report.
---
Before producing the Key Financial Ratios report for the first time, you should define how the figures in the report will be calculated and ensure the format of the report suits your specific requirements. This is known as "Defining" the Key Financial Ratios report.
To set or change the report definition of the Key Financial Ratios report, ensure you are in the Nominal Ledger, click (Windows/macOS) or tap (iOS/Android) the [Settings] button in the Navigation Centre and double-click or tap 'Report Settings' in the subsequent list. Then double-click 'Key Financial Ratios' in the list on the left-hand side of the 'Report Settings' window (Windows/macOS) or highlight it and press the [Definition] button (all platforms). The 'Key Financial Ratios Report Definition: Inspect' window will be opened.
The illustration shows many of the commands that you can use when defining Key Ratios.
Most of the 'Key Financial Ratios Report Definition: Inspect' window is taken up by a matrix. Use this matrix to define your Key Ratios: each Key Ratio will occupy a separate row in the matrix, and each Key Ratio will cause a separate line to be printed in the Key Financial Ratios report. This is shown in the illustration below:
You can add, insert or delete rows in the report definition as necessary. If you are using Windows or macOS, insert a row by clicking on the row number where the insertion is to be made and pressing Return key. To delete a row, highlight it and press the Backspace key. If you are using iOS or Android, you can add rows by tapping the + button below the matrix. Insert a row by long tapping on the row number where the insertion is to be made and selecting 'Insert Row' from the resulting menu. To remove a row, long tap on the row number on the left of the row and select 'Delete Row' from the resulting menu.
Click the [Save] button (Windows/macOS) or tap √ (iOS/Android) to save your Key Ratio definitions.
Each row in the Key Financial Ratios report definition should contain a separate Key Ratio. For each Key Ratio, enter the following information:
Flip A - Code
- Enter a unique code to identify each Key Ratio. You can use this Code to refer to the Key Ratio in other formulae, as shown on line 8 in the illustration above.
- Comment
- Enter a name of the Key Ratio, to be printed in the report.
- Definition
- Paste Special
Assisted Formula Entry
- The formula that will be used to calculate the value of the Key Ratio. The commands that you can use in the formulae are listed immediately below, while the 'Assisted Formula Entry' window is described here.
- Hide
- Paste Special
Choice of possible entries
- Enter "Yes" in this field if you do not want a particular Key Ratio to be printed in the report. This option allows you to use rows for hidden calculations that you can then include in the definitions in later rows (using the KEY command).
You can use the commands listed below in the formula in the Definition field. These commands are not case sensitive.
- RESULT("Account Code")
- RESULT prints the net change during the period for the Account specified in the brackets. Some examples of the use of this command are as follows:
- RESULT("100")
- Prints the net change in Account 100 for the specified period. For example, if Account 100 is a Sales Account, this will print the net sales posted to that Account during the specified period.
- RESULT("100:1999")
- Prints the sum of the net changes in Accounts 100 to 1999 for the specified period. The Accounts in the range will be determined using an alpha sort, rather than a numeric one. So, Account 1000001 will be included in the example range, while Account 200 will not.
- -RESULT("100:1999")
- As the previous example, but the sign of the final figure will be changed. This will be useful when displaying figures for sales, which are stored as negative figures in Standard ERP. Prefixing the RESULT command with a minus sign will cause sales to be displayed as positive figures in the report.
- RESULT("100,199")
- Prints the sum of the net changes in the individual Accounts 100 and 199 for the specified period.
- RESULT("100+120:1999")
- Takes the net change in Account 100 and adds it to the sum of the net changes in Accounts 120 to 1999.
- AVGRESULT("Account Code")
- AVGRESULT prints the average monthly posting during the report period to the specified Account or Accounts. For example, if the report period is one year, the command will print the RESULT divided by 12.
- CREDRESULT("Account Code")
- Prints the total credit posting during the report period to the specified Account or Accounts.
- AVGCREDRESULT("Account Code")
- Prints the average monthly credit posting during the report period to the specified Account or Accounts.
- DEBRESULT("Account Code")
- Prints the total debit posting during the report period to the specified Account or Accounts.
- AVGDEBRESULT("Account Code")
- Prints the average monthly debit posting during the report period to the specified Account or Accounts.
- BALANCE("Account Code")
- BALANCE prints the closing balance for the specified Account. It differs from RESULT in that balances brought forward from previous periods are taken into account.
- BALANCE can print the sum of the balances of a range of Accounts or of a number of Accounts in the same manner as RESULT: please refer to the description of RESULT above for examples.
- CREDBALANCE("Account Code")
- Prints the closing credit balance for the Account specified. It differs from CREDRESULT in that balances brought forward from previous periods are taken into account.
- DEBBALANCE("Account Code")
- Prints the closing debit balance for the Account specified. It differs from DEBRESULT in that balances brought forward from previous periods are taken into account.
- KEY("Code")
- KEY allows you to include the value of another Key Ratio in a calculation. You must already have defined the Key Ratio that you are referring to, otherwise the result will be a zero value. Therefore, in the example illustration, the GPP line (row 8) must appear below the GP and TURNO lines (rows 7 and 1 respectively). As this line illustrates, you can include KEYs in mathematical formulae with other KEYs and with hard-coded numbers.
- Although the KEY command itself is not case sensitive, the reference to the other Key Ratio is. Thus, in the example illustration, you can use key("TURNO") and KEY("TURNO"), but not key("turno").
- IFKEY("Comparison","print if true","print if false")
- IFKEY allows you to compare the values of two Key Ratios and to print the value of one of them, depending on the result of the comparison. You must already have defined the Key Ratios that you are referring to.
- The following examples illustrate the possible comparisons:
- IFKEY("KEY_A>KEY_B","KEY_A","KEY_B")
- If the value of KEY_A is greater than the value of KEY_B, the value of KEY_A will be printed. Otherwise, the value of KEY_B will be printed.
- IFKEY("KEY_A<KEY_B","KEY_A","KEY_B")
- If the value of KEY_A is less than the value of KEY_B, the value of KEY_A will be printed. Otherwise, the value of KEY_B will be printed.
- IFKEY("KEY_A=KEY_B","KEY_A","KEY_C")
- If the value of KEY_A is the same as the value of KEY_B, the value of KEY_A will be printed. Otherwise, the value of KEY_C will be printed.
- IFKEY("KEY_A>=KEY_B","KEY_A","KEY_B")
- If the value of KEY_A is greater than or equal to the value of KEY_B, the value of KEY_A will be printed. Otherwise, the value of KEY_B will be printed.
- IFKEY("KEY_A<=KEY_B","KEY_A","KEY_B")
- If the value of KEY_A is less than the value of KEY_B, the value of KEY_A will be printed. Otherwise, the value of KEY_B will be printed.
- OBJRESULT("Account Code","Tag/Object Code")
- OBJRESULT is similar to RESULT, but in calculating the net change in the specified Account or Accounts over the specified period, only postings with the specified Tag/Object Code will be taken into account.
- The reference to the Tag/Object Code is not case sensitive.
- OBJRESULT can print the sum of the net changes in a range of Accounts or in a number of Accounts in the same manner as RESULT: please refer to the description of RESULT above for examples.
- AVGOBJRESULT("Account Code","Tag/Object Code")
- Prints the average monthly posting to the specified Account or Accounts during the period, calculated from postings with the specified Tag/Object Code. For example, if the report period is one year, the command will print the OBJRESULT divided by 12.
- OBJCREDRESULT("Account Code","Tag/Object Code")
- Prints the total credit posting to the specified Account or Accounts during the period, calculated from postings with the specified Tag/Object Code.
- AVGOBJCREDRESULT("Account Code","Tag/Object Code")
- Prints the average monthly credit posting during the report period to the specified Account or Accounts, calculated from postings with the specified Tag/Object Code.
- OBJDEBRESULT("Account Code","Tag/Object Code")
- Prints the total debit posting to the specified Account or Accounts during the period, calculated from postings with the specified Tag/Object Code.
- AVGOBJDEBRESULT("Account Code","Tag/Object Code")
- Prints the average monthly debit posting during the report period to the specified Account or Accounts, calculated from postings with the specified Tag/Object Code.
- VATRESULT("Account Code","VAT Code")
- VATRESULT is similar to RESULT, but in calculating the net change in the specified Account or Accounts over the specified period, only postings with the specified VAT Code will be taken into account.
- The reference to the VAT Code is not case sensitive.
- VATRESULT can print the sum of the net changes in a range of Accounts or in a number of Accounts in the same manner as RESULT: please refer to the description of RESULT above for examples.
- In Transactions created from Sub Systems (e.g. Invoices and Purchase Invoices), the appropriate VAT Code will be copied to the V-Cd field in each Transaction row posting to a Sales or Cost Account. The V-Cd field is the mechanism that allows you to use the VATRESULT command with these Accounts. However, if you are using the Add VAT Code to VAT A/C rows option in the Transaction Settings setting, the appropriate VAT Code will also be copied to the V-Cd field in each Transaction row posting to a VAT Account. This will allow you to apply the VATRESULT command to the VAT Accounts (e.g. VATRESULT("830","1"), where Account 830 is the Output VAT Account).
- AVGVATRESULT("Account Code","VAT Code")
- Prints the average monthly posting to the specified Account or Accounts during the period, calculated from postings with the specified VAT Code. For example, if the report period is one year, the command will print the VATRESULT divided by 12.
- VATCREDRESULT("Account Code","VAT Code")
- Prints the total credit posting to the specified Account or Accounts during the period, calculated from postings with the specified VAT Code.
- AVGVATCREDRESULT ("Account Code","VAT Code")
- Prints the average monthly credit posting during the report period to the specified Account or Accounts, calculated from postings with the specified VAT Code.
- VATDEBRESULT("Account Code","VAT Code")
- Prints the total debit posting to the specified Account or Accounts during the period, calculated from postings with the specified VAT Code.
- AVGVATDEBRESULT("Account Code","VAT Code")
- Prints the average monthly debit posting during the report period to the specified Account or Accounts, calculated from postings with the specified VAT Code.
- VATBALANCE("Account Code","VAT Code")
- VATBALANCE prints the closing balance for the specified Account or Accounts, calculated from postings with the specified VAT Code. It differs from VATRESULT in that balances brought forward from previous periods are taken into account.
- APDUE(Number of days)
- APDUE prints the total in the Purchase Ledger that is overdue for payment by more than the specified number of days. The last day of the report period will be used as the due date. For example, APDUE(30) will print the total that became due more than 30 days before the end of the report period (i.e. the total that is overdue by at least 30 days on the last day of the period). You must specify a number of days: use APDUE(0) if the total is to include all overdue Purchase Invoices.
- This figure does not include open On Account Payments and Prepayments: use APONACC described below if you need this figure to be shown in the report.
- There is no need place the number of days parameter in inverted commas, but doing so will not affect the calculation. In other words, you can use both APDUE(30) and APDUE("30").
- APOHDUE(Number of days)
- Similar to APDUE, but Purchase Invoices marked as On Hold will not be included.
- APONACC
- Prints the On Account balance in the Purchase Ledger on the last day of the report period. This includes open On Account Payments and Prepayments.
- APRESULT("Account","Supplier")
- APRESULT checks any Purchase Invoices that were issued by the specified Supplier during the report period for any rows with the specified Account and prints the total amount posted to that Account. Some examples of the use of this command are as follows:
- APRESULT("240","")
- Prints the total amount posted to Account 240 in Purchase Invoices received from the following Suppliers:
- The Supplier or range of Suppliers specified in the Contact field in the report specification window.
- Suppliers with the Contact Classification specified in the Contact Class. field in the report specification window.
- All Suppliers, if the Contact and Contact Class. fields in the report specification window are both empty.
- APRESULT("200:299","503")
- Prints the total amount posted to all Accounts in the range 200:299 in Purchase Invoices received from Supplier 503.
- APRESULT("200:299","500:599")
- Prints the total amount posted to all Accounts in the range 200:299 in Purchase Invoices received from Suppliers 500:599.
- APRESULT("","503")
- Prints the total amount posted to all Accounts in Purchase Invoices received from Supplier 503.
- APDUEINPERIOD("periodtype","periodoffset")
- APDUEINPERIOD prints the total in the Purchase Ledger that will become due in a specified period. The first parameter, periodtype, can be "week", "month" or "year". The second parameter, "periodoffset", should be a number that specifies which week or month or part of the report period will be used as the specified period. For example:
- APDUEINPERIOD("week","0")
- Prints the total in the Purchase Ledger that will become due in the week containing the beginning of the report period. The week is a calendar week, so if the report period begins on a Wednesday, APDUEINPERIOD("week","0") will print the total that will become due between that Wednesday and the following Sunday.
- APDUEINPERIOD("week","1")
- Prints the total in the Purchase Ledger that will become due in the week starting on the first Monday after the beginning of the report period.
- APDUEINPERIOD("month","0")
- Prints the total in the Purchase Ledger that will become due in the month containing the beginning of the report period. The month is a calendar month, so if the report period begins on March 13, APDUEINPERIOD("month","0") will print the total that will become due between March 13 and March 31.
- APDUEINPERIOD("month","1")
- Prints the total in the Purchase Ledger that will become due in the month following the month containing the beginning of the report period. For example, if the report period begins on March 13, APDUEINPERIOD("month","1") will print the total that will become due in April.
- APDUEINPERIOD("year","0")
- Prints the total in the Purchase Ledger that will become due during the report period.
- APDUEINPERIOD("year","1")
- Prints the total in the Purchase Ledger that will become due during the report period but excluding the first calendar month. For example, if the report period runs from March 13 to June 30, APDUEINPERIOD("year","1") will print the total that will become due between April and June. APDUEINPERIOD("year","2") will print the total that will become due between May and June.
- ARDUE(Number of days)
- The Sales Ledger equivalent of APDUE described above.
- ARDPDUE(Number of days)
- Similar to ARDUE, but Invoices marked as Disputed will not be included.
- ARNRDUE(Number of days)
- Similar to ARDUE, but Invoices marked as No Reminder will not be included.
- ARDPNRDUE(Number of days)
- Similar to ARDUE, but Invoices marked as both Disputed and No Reminder will not be included.
- ARONACC
- The Sales Ledger equivalent of APONACC described above.
- ARRESULT("Account","Customer")
- The Sales Ledger equivalent of APRESULT described above.
- ARDUEINPERIOD("periodtype","periodoffset")
- The Sales Ledger equivalent of APDUEINPERIOD described above.
As you enter your Key Ratio definitions, remember that all calculations will use figures as stored inside the Standard ERP database. This means that credit balances (e.g. those for Sales Accounts) will be negative. If you want to print these balances as positive figures, place a minus sign in front of the formula, as shown in line 1 in the illustration above.
The value of a well-structured Chart of Accounts can easily be seen here. For example, if you have grouped your Income Accounts together in a consecutive series, totalling them using a range becomes simple.
In the first row in the illustration above, we have included a Key Ratio with the Code "TURNO". This Key Ratio contains a definition of "turnover" (in the example, "turnover" has been defined as the net change in the balance of all Sales Accounts over the report period). You must have a Key Ratio with this Code if you will use the % Turnover option when printing the Profit & Loss report, as this option needs a definition of "turnover".
Note that the syntax used in the Key Ratio formulae changed in version 4.1. If you are updating from a version earlier than 4.1 to a later version, you will therefore need to change your formulae as follows:
- All parameters should now be enclosed in quotation marks. For example, you should change RESULT(830) to RESULT("830").
- The order of parameters for the VATRESULT, VATBALANCE, VATCREDRESULT and VATDEBRESULT commands has been transposed. For example, you should change VATRESULT(1,100:199) to VATRESULT("100:199","1").
Please refer here for an illustrated example Key Financial Ratios report definition, including details about flip B of the 'Key Financial Ratios Report Definition' window.
---
Please follow the links below for more details about the Key Financial Ratios report:
---
Reports in the Nominal Ledger:
---
Go back to:
|
Introduction to Reports in the Nominal Ledger
