Managing ratios for financial reporting

The Ratio Rule Manager utility allows you to add and edit rules for financial statistics and ratios. There are two sections to the utility: the upper section is used for entering new rules, and the lower section is used for editing existing rules.

The following applies when adding or modifying a rule:

  • Use the blue cells for manual entry.
  • Update the information in the gray cells by either double-clicking the ellipses to the left of the cell and then selecting a property, or in the case of the Rule Enabled column, clicking the cell to select Trueto Falsefrom the drop-down.
  • You can enter filters manually, but we strongly recommended that you use the Filter Wizard. The filters must use the fully qualified names of the column they are referencing, which the wizard generates automatically. The Filter Wizard also allows you to save and reuse filters. For instructions on how to properly create filter syntax statements, see Filter criteria syntax.

Adding or editing a ratio rule

Keep in mind the following when adding or editing ratio rules:

  • When adding a new rule, you must enter a new account number that does not already exist in the Account dimension.
  • The utility will only add an account to the Account dimension and save the rule when you select an option from the Record Type column, and complete all of the required property settings.
  • When you save a new rule, the utility adds the record to both the RatioRules table and to the Account dimension. The utility then performs a refresh, adding the rule to the existing rules section of the table and clearing the new rules section line.
  • Accounts added directly to the Account dimension will not save as ratio rules until all of the property settings have been completed. This allows you to add a list of financial statistic and ratio accounts to the Account dimension, and then complete their setup as time permits.
  • When editing an existing rule, the system updates the Account dimension when you change the Description field or Financial Account Type field.

NOTE: The Ratio Rule Manager includes accounts in the lower section of the utility where the AcctType column is KPI in the ACCT dimension, whether they are added from this utility or added manually.

To add or edit a ratio rule:

  1. On the Process Data tab of the Financial Reporting page, click the Process Financial Ratios sub-tab.

  2. In Maintain Financial Ratios section, click Run to launch the Ratio Rules Manager utility.

    NOTE: The utility opens in the Desktop Client.

  3. Complete the following columns:

    Column Description
    ACCT

    For new rules, type an account number that does not already exist in the Account dimension. Existing account numbers cannot be changed.

    NOTE: This column cannot be edited for an existing rule.
    Description Type a description for the ratio or financial statistic. This information saves to the Description column of the ACCT dimension.
    Detailed Description Optional. Type more detailed information about the rule, as needed. For example: “Annualized Net Income using Act_Act for non-interest accounts and account specific annualization for interest bearing accounts.”
    Record Type

    To the left of the column, double-click the ellipses, and select one of the following options from the drop-down:

    • DetailStat – This system calculates this value from GL data. It is an aggregation of GL account-level data that nets income and expense or assets, liabilities, and equity to provide a financial statistic for calculating ratios and for reporting.
    • SummedStat – The system calculates this value from both DetailStat and other SummedStat records. SummedStats are only needed if a financial statistic cannot be generated as a single DetailStat. For example, “Annualized Net Income” might be a SummedStat that combines a DetailStat named “Annualized Net Interest Income” which aggregates Axiom Yield report records, with a DetailStat named “Annualized NonInterest Income and Expense” that was annualized when processing financial ratios.
    • Ratio – The system calculates this value from both the DetailStat and SummedStat records.

    NOTE: The terms DetailStat and SummedStat are only used in rule building and calculations. They are generally referred together as financial statistics.
    Financial Account Type

    This selection is used to determine if the system calculates and saves a financial statistic as a debit (assets and equity) or credit (income and expense) value. When processing financial ratios, the system aggregates GL data and sums financial statistics by netting debits and credit. The resulting value is either positive or negative, depending on this selection.

    Select the financial account type for a ratio based on the data in its numerator. For statistical accounts, select a type that is consistent with the data.

    • If the statistic has a data type (DTYPE) of AVG or EOM, select either Asset or Liability/Equity.

    • If it is MTD, meaning the statistic will be summed for QTD or YTD, select Income or Expense.

    TIP: A debit or credit designation is generally meaningless for statistical data. Therefore, we recommend that you use Asset and Expense for statistics so that they default to debits. Use Liability/Equity and Income for specific cases where statistics are combined and must be netted. Remember that denominators are always set to positive for ratios and the numerator determines whether the ratio will be debit or credit.
    Statistic or Ratio Multiplier

    Use this value to adjust a financial statistic by a percentage or a multiple of its GL data. For example, you could use this option to generate a financial statistic that approximates the tax equivalent interest for tax exempt securities by multiplying interest by a factor such as 1.515. You can also use the multiplier to save a ratio as a rate (1.62) rather than a decimal value (.0162) by multiplying the ratio by 100.

    The multiplier you enter must include a non-zero value, so either enter a number or leave the default value as 1.

    Financial Statistic ACCT Filter

    Create or select a filter that identifies the accounts containing the data to include in the statistic. The system uses this filter when processing financial ratios to filter data queried from the source GL table.

    This field is required for DetailStat and SummedStat record types. Leave blank for ratios.

    TIP: We recommended that you use the Filter Wizard to populate this value. The wizard is very flexible and does not limit selections to only the ACCT dimension. However, the Ratio Rule Manager is designed for an ACCT dimension filter and adding other dimensions may or may not generate expected values. For instructions on how to properly create filter syntax statements, see Filter criteria syntax.
    Acct Filter DTYPE

    This setting is combined with the account filter to query GL data for DetailStat rules. It is also a key value for determining the calculation method used by the system to aggregate and annualize data. It is a required value for ratio rules and only one data type (DTYPE) is valid for each rule. For more information regarding DTYPE, see Mapping data types.

    The available data types in this field depend on the option selected in the Financial Account Type field:

    • Asset or liability/equity types can use the following standard data types:
      • AVG
      • EOM
    • Income or expense types use the following standard data types:
      • MTD, INT, FTP, FASB, FEE, FTPSPRD
      • ANNINT, ANNFTP, ANNFASB, ANNFEE, ANNFTPSPRD
    • Income or expense type can also use the following summary data types, which allows you to combine interest-related data types into one rule:
      • YIELD
      • ANNYIELD

    The system only generates SummedStat and Ratio from DetailStat records, which include a FINSTAT data type. (Financial Ratio is the available selection when clicking the ellipses.)

    NOTE: The STAT DTYPE is not used by the utility since it does not indicate to the system whether the statistic should be averaged, summed, or copied. Statistics, like FTE or number of transactions, must use AVG, EOM, and MTD data types and include ST in the AcctType column in the ACCT dimension.

    TIP: The system displays the following error message when an account filter does not include fully qualified values from the ACCT dimension. Correct the filter (preferably using the Filter Wizard) so that the dimension columns are fully qualified. For example, ACCT.AcctType.AcctType. The system displays a similar error if the numerator or denominator filters are not correctly qualified, or the department filter does not begin with DEPT.

    The following is an example of a processing error message with an incorrect account filter:

    Click image to view full size
    Annualization Method

    The system uses this selection to calculate annualized income and expense from GL data. The selection displays in this column are determined by the Acct Filter DTYPE column and include the following:

    • None – This is the default. For these records, the system simply aggregates the GL data. None is the only selection available for rules that include an Acct Filter DTYPE that begins with “ANN”.
    • Actual_Actual – Use for income and expense.
    • Thirty_360 – Use for income and expense.

    NOTE: Most standard ratios use Actual_Actual and Thirty_360 factors to either annualize all income and expense or to annualize non-interest income and expense. The system does not display other accrual factors as selections since they are specific to interest-bearing accounts and, if needed, are available as the yield reporting records generated by the Process Yield Records utility.

    By default, the Ratio Rule Manager utility uses the AVGBal from the GL. Axiom calculates the average for multiple periods as a days weighted average. Some organizations may use a simple average for certain ratios (End of month1+End of month 2)/2. You can use the Generate Ratio Engine to calculate simple average balances for Asset and Liability/Equity records. Available selections include:

    • None – This is the default. For these records, the utility simply aggregates the available GL data. For the AVG DTYPE, QTD and YTD balances are daily averages from the calculated GL table columns.
    • SIMAVG – When the Acct Filter DTYPE is EOM, the utility calculates a two-point monthly average from the prior and current month ends. If the Acct Filter DTYPE is AVG, the utility simply aggregates the monthly average balances from the GL. For both AVG and EOM types, QTD and YTD periods are generated from the monthly values using the Excel Average function.
    Department Filter

    The system automatically calculates and saves financial statistics for each department. The utility calculates ratios for all companies and for all the selected members of the Department dimension columns. These selections apply to all ratios. With the department filter, you limit certain financial statistics and ratios to a specific department or group of departments.

    This is an optional value. Leave blank if not used.

    IMPORTANT: The filter must include fully qualified values from the DEPT dimension. A filter containing dimension references that do not begin with DEPT. will generate a “Cannot join data tables” error during processing.
    Ratio Number Filter

    This setting is only used for ratios and must include one or more financial statistic accounts. This is a filter value, so we recommended that you use the Filter Wizard.

    This field is required for ratio record types. Leave this field blank for DetailedStats and SummedStats.
    Ratio Denominator Filter

    This setting is only used for ratio rules and must include one or more financial statistic ACCT. This is a filter value so we recommend that you use the Filter Wizard.

    This field is required for ratio record types. Leave this field blank for DetailedStats and SummedStats.
    Rule Enabled This field allows you to disable a rule that is no longer used but has historical data records.
    Run Order

    Multipass processes ratio rules in run order and by record type. The system automatically populates the value based on the record type:

    • DetailStats - 1000 to 1999
    • SummedStats - 2000 to 2999
    • Ratios - 3000 to 3999

    You can change the run order for existing records to accommodate any dependencies. The utility will not save rules that include duplicate run order numbers.

    NOTE: The system processes DetailStats, SummedStats, and ratios in run order for their separate multipass runs. Run order only applies to SummedStat records because they can depend on other SummedStats. DetailStats and ratios can be processed in any order within their multipass runs.
  4. After making your changes, in the Main ribbon tab, click Save.

Enabling or disabling ratio rules

While you cannot delete ratio rules, you can disable them from use by the system.

To enable or disable ratio rules:

  1. On the Process Data tab of the Financial Reporting page, click the Process Financial Ratios sub-tab.

  2. In Maintain Financial Ratios section, click Run to launch the Ratio Rules Manager utility.

    NOTE: The utility opens in the Desktop Client.

  3. For each rule to disable, in the Rule Enabled column, do one of the following:
    • To enable the rule, click TRUE.
    • To disable the rule, click FALSE.
  4. After making your changes, in the Main ribbon tab, click Save.