Language:


Key Financial Ratios - Defining

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 the [Settings] button in the Navigation Centre and double-click '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 (or highlight it and click the [Definition] button). 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. To insert a row, highlight the row above which the new one is to be inserted (by clicking the row number) and press the Return key. To delete a row, highlight it and press the Backspace key. Click the [Save] button 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")
This command 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")
This command 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")
This command prints the total credit posting during the report period to the specified Account or Accounts.

DEBRESULT("Account Code")
This command prints the total debit posting during the report period to the specified Account or Accounts.

BALANCE("Account Code")
This command prints the closing balance for the specified Account. It therefore differs from the RESULT command in that balances brought forward from previous periods are taken into account.

The BALANCE command can print the sum of the balances of a range of Accounts or of a number of Accounts in the same manner as the RESULT command: please refer to the description of the RESULT command above for examples.

CREDBALANCE("Account Code")
This command prints the closing credit balance for the Account specified. It therefore differs from the CREDRESULT command in that balances brought forward from previous periods are taken into account.

DEBBALANCE("Account Code")
This command prints the closing debit balance for the Account specified. It therefore differs from the DEBRESULT command in that balances brought forward from previous periods are taken into account.

KEY("Code")
Use the KEY command to include the value of another Key Ratio in the calculation. You must already have defined the Key Ratio that you are referring to. If not, a zero value will be used. Therefore, in the example illustration, the GPP line (row 8) must appear below the GP and TURNO lines (rows 7 and 1 respectively).

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","Object Code")
This command is similar to RESULT, but in calculating the net change during the period in the specified Account or Accounts, only postings with the specified Object Code are taken into account.

The reference to the Object Code is not case sensitive.

The command can print the sum of the net changes in a range of Accounts or in a number of Accounts in the same manner as the RESULT command: please refer to the description of the RESULT command above for examples.

AVGOBJRESULT("Account Code","Object Code")
This command prints the average monthly posting to the specified Account or Accounts during the period, taking only transactions with the specified Object Code into account. For example, if the report period is one year, the command will print the OBJRESULT divided by 12.

OBJCREDRESULT("Account Code","Object Code")
This command prints the total credit posting to the specified Account or Accounts during the period, taking only transactions with the specified Object Code into account.

OBJDEBRESULT("Account Code","Object Code")
This command prints the total debit posting to the specified Account or Accounts during the period, taking only transactions with the specified Object Code into account.

VATRESULT("Account Code","VAT Code")
This command is similar to RESULT, but in calculating the net change during the period in the specified Account or Accounts, only postings with the specified VAT Code are taken into account.

The reference to the VAT Code is not case sensitive.

The command can print the sum of the net changes in a range of Accounts or in a number of Accounts in the same manner as the RESULT command: please refer to the description of the RESULT command 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).

VATBALANCE("Account Code","VAT Code")
This command prints the closing balance for the specified Account or Accounts, with only Transactions with the specified VAT Code taken into account. It therefore differs from the VATRESULT command in that balances brought forward from previous periods are taken into account.

AVGVATRESULT("Account Code","VAT Code")
This command prints the average monthly posting to the specified Account or Accounts during the period, taking only transactions with the specified VAT Code into account. For example, if the report period is one year, the command will print the VATRESULT divided by 12.

VATCREDRESULT("Account Code","VAT Code")
This command prints the total credit posting to the specified Account or Accounts during the period, taking only transactions with the specified VAT Code into account.

VATDEBRESULT("Account Code","VAT Code")
This command prints the total debit posting to the specified Account or Accounts during the period, taking only transactions with the specified VAT Code into account.

APDUE(Number of days)
This command 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)
This command is similar to APDUE above, but Purchase Invoices marked as On Hold will not be included.

APONACC
This command 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")
This command 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:
  1. The Supplier or range of Suppliers specified in the Contact field in the report specification window.

  2. Suppliers with the Contact Classification specified in the Contact Class. field in the report specification window.

  3. 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.
ARDUE(Number of days)
This command is the Sales Ledger equivalent of APDUE above.

ARDPDUE(Number of days)
This command is similar to ARDUE above, but Invoices marked as Disputed will not be included.

ARNRDUE(Number of days)
This command is similar to ARDUE above, but Invoices marked as No Reminder will not be included.

ARDPNRDUE(Number of days)
This command is similar to ARDUE above, but Invoices marked as both Disputed and No Reminder will not be included.

ARONACC
This command is the Sales Ledger equivalent of APONACC above.

ARRESULT("Account","Customer")
This command is the Sales Ledger equivalent of APRESULT 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 a description of an example Key Financial Ratios report definition, including details about flip B of the 'Key Financial Ratios Report Definition' window.

---

Pages describing the Key Financial Ratios report:

---

Reports in the Nominal Ledger:

---

Go back to: