Reviewing financial statistics and ratios

The Generate Ratio Engine calculates and saves financial statistics and ratios to the GL data or Plan table. You can use the engine to review how individual ratios and financial statistics are calculated.

The utility opens to the CalcRatios sheet, which includes all of the processing logic. This is also where you review the calculations.

IMPORTANT: Ratios are only generated from financial statistics, which is why you should process all of the ratio rules before reviewing. The system calculates and saves ratios to the GL table for the DEPT column selected. The selected DEPT column remains effective until you select a new column.

Reviewing financial statistics

To review financial statistics:

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

  2. In Review Ratio Calculations section, click Run to launch the Generate Ratio Engine utility.

    NOTE: The utility opens in the Desktop Client.

  3. In the Main ribbon tab, click Refresh Data.

  4. In the Refresh Variables dialog, complete the following, and click OK:

    Option Description
    Process a Reporting Table or a Plan Table

    Do one of the following:

    • To report on GL data, click Actual.
    • To report on a budget or forecast plan file, click Plan.
    Select Reporting Table/Select a Plan File Group

    Do one of the following:

    • If you are reporting on GL data, select the reporting table.
    • If you are reporting on a budget or forecast, select the plan file group ID.
    Select the type of record (also used for multipass)

    Do one of the following:

    • To generate the report using DetailStats, select DetailStat.
    • To generate the report using SummedStats, click SummedStat.

    TIP: For more information, see Understanding DetailStats and SummedStats below.
    Select a record to process Select the rule to process.
    Select the DEPT column to process Select the column from the Department dimension to process.

The Generate Ratio Engine displays the resulting calculations and key information about the financial statistic, in this case, net non-interest income, in the grey area. In this case, the data type is MTD, so the data is aggregated from the MGTGL2019 table and then annualized using the Act_Act accrual basis. As shown, there are three sets of records highlighted in the screen shot: MTD, QTD, and YTD.

NOTE: MTD data types are income statement accounts and do not have annualization factors. In those cases, the system uses the factor set in the rule as either Act_Act or Thirty_360.

The monthly data queried from the MGTGL2019 table displays in white and is summed by the grouping values from the AcctType.AcctTypeLvl3 column. Debit amounts are netted against credit amounts based on DR/CR multipliers that are set to positive or negative based on the rule’s financial type. In this example, the utility generates an income account, so debits are subtracted from credits.

NOTE: The engine includes 72 data columns to accommodate the standard PLAN table configuration. While only the first 12 columns are turned on for GL tables, the engine uses the Terminal Year file group variable to determine how many columns are active for a Plan table.

The following example shows the calculation of net interest income. In this case, the data type is ANNYIELD, so the data is simply aggregated from the BUD2019 table for MTD, QTD, and YTD with no annualization because the source data is already annualized.

As shown in the example above, this is an income rule (credit balance) where the CR multiplier is set to negative. The reason for this is “ANN” type records have Asset and Liability account numbers and use the DR/CR designation for those balance sheet accounts. In this case, the Interest Earning Asset line (a debit type) contains the sum of interest income records. And similarly, the Interest Bearing Liability line (a credit type) contains expense.

The following screen shot shows results for a SummedStat rule. This particular rule, called Net Income-Annualized, sums two DetailStat rules, Net Interest Income and Net Non Interest, to generate net income. Both SummedStats and Ratios queries sum by the ACCTType.DRCR. In this case, only one total record is returned.

Understanding DetailStats and SummedStats

A DetailStat is a statistical record that the Axiom system creates from GL data. In the following screen shot, Total Assets is a DetailStat account for the Non-Interest Bearing Asset account type. This information is located in the AcctTypeLvl1 column of the AcctType table.

A SummedStat is a statistical record that is a roll-up of specific DetailStats types. This information is stored in the AcctTypeLvl3 column of the AcctType table. In the following screen shot, the DetailStat named Total Assets rolls up to a SummedStat named Other Assets.

Processing and reviewing ratios

Ratios are only generated from financial statistics, which is why you must process financial statistics first. The system calculates and saves ratios to the GL table for the DEPT column selected. The selected DEPT column remains effective until you select a new column.

To review ratios:

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

  2. In Review Ratio Calculations section, click Run to launch the Generate Ratio Engine utility.

    NOTE: The utility opens in the Desktop Client.

  3. In the Main ribbon tab, in the File Options group, click Refresh Data, or press F9.

  4. In the Refresh Variables dialog, complete the following, and click OK:

    Option Description
    Process a Reporting Table or a Plan Table

    Do one of the following:

    • To report on GL data, click Actual.
    • To report on a budget or forecast plan file, click Plan.
    Select Reporting Table/Select a Plan File Group

    Do one of the following:

    • If you are reporting on GL data, select the reporting table.
    • If you are reporting on a budget or forecast, select the plan file group ID.
    Select the Type of Record (Also Used for Multipass)

    Select Ratio.
    Select a Record to Process Select the rule to process.
    Select the DEPT Column to Process

    Select the department to process.

    TIP: Do not use DEPT.DEPT for ratio reporting. Either use the DEPT column you selected when generating the statistics, or use DETAIL as your SUM BY variable

The following example is a snapshot of the Net Interest Margin ratio. The numerator is net interest income, which is an income type rule. The ratio selected is also an income type and, because of this, the numerator includes a positive CR multiplier. Denominators are intentionally set to positive numbers in all cases. The engine only uses the DRCR multiplier to net denominator records, if needed, and returns the absolute value of that calculation. Note that the system uses the account number of the statistic.

NOTE: Both the ratio and its denominator are rounded to six decimals to avoid overflow errors caused by extremely small values.

IMPORTANT: When saving ratios and financial statistics to summary-level departments (like company or region), Axiom will always select the last DEPT mapped to each summary-level department member. Ratios are saved with a detail value that identifies the summary level (i.e., Ratio:Company or Ratio:Region). This allows you to generate, save, and report on ratios for multiple summary columns, if meaningful.