Mind Tools: Applications and Solutions
An Evaluation & Forecasting Technique
Download complete article as PDF file
This section describes the fundamental EPS equation, shows how the critical ratios are integrated into it, and traces those ratios back to the bank's annual report.
The EPS equation. A bank's annual EPS can be calculated using the equation shown in Exhibit 2. The equation is set up as a spreadsheet of fifteen cells. The cells are organized into six groups: each group is indicated by a vertical line that borders its cells; the last cell of one group is also the first cell of the next.
Operating margin. The first group (cells 1 through 5) calculates the operating margin (18.766%). It does this by setting total revenue equal to 100% and subtracting from it the percentages of revenue consumed by the interest expense (32.285%), the provision for loan losses (4.120%), and the non-interest expense (44.829%).
Profit margin. The second group (cells 5 through 7) calculates the profit margin (12.609%) by subtracting the percent of total revenue consumed by taxes (6.157%) from the operating margin (18.766%).
Return on assets. The third group (cells 7 through 9) calculates the return on average assets (1.443%) by multiplying the profit margin (12.609%) times the asset turnover (11.444%).
Return on common equity. The fourth group (cells 9 through 11) calculates the return on average common equity (22.299%) by dividing the return on assets (1.443%) by the common equity ratio (6.471%).
Return on common equity after preferred charge. The fifth group (cells 11 through 13) calculates the net return on average common equity (21.338%) by subtracting from the return on common equity (22.299%) the preferred charge to common equity (0.961%).
Earnings per share. And the sixth group (cells 13 through 15) calculates the earnings per share of common stock ($2.76) by multiplying the net return on common equity (21.338%) times the book value ($12.93).
Integrating the nine critical ratios into the EPS equation. Exhibit 3 adds a new column to the left of the EPS equation. Included among the twelve cells of the new column are the nine critical ratios, shown as white cells with double-lined borders. Where necessary, the new column mathematically converts certain of these ratios into a form that allows them to flow into the EPS equation (right column), as explained below. The equation then calculates the dollar effect of the critical ratios' combined values.
Banking industry expense ratios. In order to calculate the operating margin in the EPS equation (right column, cell 5) it is necessary to express the interest expense, the loan loss provision expense, and the non-interest expense as percentages of total revenue. However, the banking industry finds it useful to express these expenses as percentages of other things.
In Exhibit 3's left column we re-express these same expenses in a banking industry format. Interest expense is presented as the ratio of interest expense to average liabilities; the provision for loan losses is presented as the ratio of the loan loss provision to interest income; and non-interest expense is presented as the efficiency ratio (i.e., the ratio of non-interest expense to total revenue less interest expense).
Despite their usefulness to bankers, these industry expense ratios are mathematically troublesome. Because each has a different denominator, they won't work together in the EPS equation unless we can find some way to standardize them.
Standardizing the expense ratios. In Exhibit 3's left column you will find the average interest rate, the loan loss provision rate, and the efficiency ratio in cells 1, 3, and 5, respectively. Each of these is associated with a particular "compensating ratio," which appears in the cell directly below it. When the two associated ratios are multiplied together, the troublesome denominator is eliminated and the expense is converted into a percentage of total revenue—making it usable in the EPS equation.
Compensating ratios. The compensating ratios needed to pull off this little mathematical trick are themselves interesting. Cell 2 is the number of years of revenue needed to cover the bank's liabilities. Cell 4 is the percent of total revenue that comes from interest income (and is itself one of the critical ratios). And cell 6 is the percent of total revenue left over after interest expense is paid. Notice that cells 2 and 6 are darkened. Their values are dependent on the values of other critical ratios. We'll say more about these two cells later.
Tax rate. The tax rate of 32.809% appears in cell 7. When the tax rate is multiplied times the operating margin of 18.766% (right column, cell 5), the result is the percent of total revenue consumed by tax, 6.157% (right column, cell 6).
Preferred equity ratio. The preferred equity ratio (left column, cell 9—note the dotted border) is not used at this point, but will be needed later when we determine the effect of each ratio's year-to-year change on EPS
Other ratios. The other ratios—asset turnover, common equity ratio, preferred charge to common equity, and book value—require no adjustment and flow directly into the EPS equation.
Determining the critical ratios' values. Exhibit 4 introduces two more columns. The first, Column A, contains selected annual report figures from the latest fiscal year; the second, Column B, identifies the figures and groups them into ratios. All the values discussed above are derived from these figures. Because of rounding, a few values in Exhibits 2 and 3 differ slightly from their counterparts in Exhibit 4, Columns D and E. (Exhibit 5 is set up exactly like Exhibit 4, but gives figures for the previous fiscal year.)
In Column A, eight of the figures are found in the Income Statement: interest income, total revenue (i.e., the sum of interest income and non-interest income), interest expense, provision for loan loss, non-interest expense, pretax income, income taxes, and average number of common shares outstanding. Four others are found in the Average Balance and Net Interest Analysis: average assets, average liabilities, average preferred equity, and average common equity. And one is found in the Statement of Shareholders' Equity: preferred dividends paid.
Deducing average preferred equity and average common equity. Sometimes a bank that has issued both preferred and common stocks will not specify their respective dollar amounts in its average balance sheet, but will only report their combined dollar value as "total equity." In that case, you must deduce their dollar amounts. Somewhere in its annual report the bank will state its return on average common equity. Dividing this percentage into its net income before preferred dividends will give the dollar amount of its average common equity. And subtracting that amount from the average total equity will give the dollar amount of average preferred equity.
Adjusting for large unrealized securities gains. A bank with an unusually high unrealized gain on investment securities may use asset and equity amounts different from those given in the average balance sheet when it computes asset turnover, return on average assets, common equity ratio, return on average common equity, book value, and EPS. In such cases adjust the average balance sheet's asset, equity, and liability amounts as follows and use the adjusted amounts in your calculations.
• Adjusted average total assets: Use average total assets less the gross unrealized gain on investment securities.
• Adjusted average common equity: Use average realized common equity (which should be given as a component of average equity).
• Adjusted average liabilities: First, subtract the average balance
sheet's net unrealized gain on investment securities from its gross unrealized
gain on investment securities. (The difference is the part of "other
liabilities" attributable to unrealized gains on investment securities.)
Then, subtract this difference from the average balance sheet's total liabilities.
Use the result for average total liabilities.
Setting Up a Spreadsheet to Evaluate Ratio Changes
To calculate the effect on EPS of each critical ratio's year-to-year change in value, use the spreadsheet format and formulas shown in Exhibit 6. Exhibit 6 is divided into three parts. The first part corresponds to Exhibit 4 and calculates EPS for the latest fiscal year. The second part corresponds to Exhibit 5 and calculates EPS for the previous fiscal year. The third part uses information from the other two parts to isolate the EPS effect of each ratio's year-to-year change in value.
Identifying the cells. The individual cells of Exhibit 6 are designated by their column and their order within that column. The top cell of a column is number 1. Thus, in Column A the eighth cell from the top is designated A8.
Because the spatial layout of cells differs from column to column, cells that have the same number may have different vertical locations within their respective columns. For example, cell A8 is higher on the page than cell C8. This numbering system is used to simplify the instructions. Your computer spreadsheet program will number the cells differently, and you must take that into account when setting up your own spreadsheet.
Entering values in the cells. For a cell that contains a dollar sign ($), enter the appropriate dollar figure from the annual report. For a cell that contains a number sign (#), enter the average number of common shares outstanding. Set cells D1, H1, and J1 equal to 100%, which represents total revenue.
For all other cells, enter the given formulas. Be sure to correct the column-and-row designation of each cell in the given formula to reflect that cell's location on your spreadsheet.
(Although the pretax earnings figure could be derived with a formula, we enter the dollar amount in order to simplify some later tax calculations.)
Minimizing rounding errors. At times a change in a ratio's value can have an EPS effect of less than one cent a share. This can be a significant amount when total earnings are just a few cents. Calculating to five decimal places will minimize rounding errors in procedures described below.
Column I reformulations. Cells I2 and I6, have been formulated differently than their corresponding cells in Parts 1 and 2. Rather than deriving their values from dollar amounts (as before), we now derive them from other ratios. We do this because the values of I2 and I6 depend on the values of other cells that we shall be adjusting; any change in those cells should be automatically reflected in I2 and I6. Here is how the new formulations were derived.
Cell I2 corresponds to cells C2 and G2, which we have previously defined as the ratio of average liabilities to total revenue. A little algebra proves that average liabilities ÷ total revenue is equal to [100% - preferred equity ratio - common equity ratio] ÷ asset turnover. Thus, we set the value of I2 equal to [100% - I9 - I10] ÷ I8. One hundred percent represents total assets.
Similarly, cell I6 corresponds to cells C6 and G6, which we have previously defined as the ratio of total revenue less interest expense to total revenue. And total revenue less interest expense ÷ total revenue is equal to [100% - the percent of total revenue consumed by interest expense]. Thus in Column I, we set the value of I6 equal to [100% - J2]. Here, one hundred percent represents total revenue.
Column J. The equation in Column J differs slightly from those in Columns D and H. Cell J14 has been re-labeled Adjusted EPS, and two new cells have been added below it: Previous Year's EPS (J15) and $ Effect of Adjustment (J15).
Substituting values. We are now prepared to isolate the effect on EPS of each critical ratio's year-to-year change. In Column I substitute a Latest Year critical ratio value for a Previous Year value—one critical ratio at a time (except in the case of the common equity ratio, as explained below)—then note the effect on cell J15 ($ Effect of Adjustment).
Recall that the values for the Previous Year are in Column G, while the values for the Latest Year are in Column C. Further recall that Column I's values are currently equated with those of Column G. To make a substitution, reset a Column I cell equal to its corresponding cell in Column C.
Column I. As an example, take cell I1, the average interest rate.
First, reset I1 equal to C1. (Until now, it has been equal to G1.)
After making the substitution, look at cell J16, $ Effect of Adjustment, and jot down the value appearing there.
Next, return I1 to the Previous Year value (again letting it equal G1).
Then proceed to the next critical ratio—in this case, the loan loss provision rate—and repeat the process. (Do not adjust the darkened cells I2 and I6, which are not critical ratios. Their values will automatically change when the critical ratios of which they are a function are changed.)
When you reach cells I9 and I10, the preferred and common equity ratios, treat them as a unit. Substitute the Latest Year values for both at the same time and record their combined effect. Then return both ratios to the previous year's values and continue on as before, changing one critical ratio at a time.
After recording the $ Effect on EPS of each ratio's change, add all the positive and negative changes together (as shown for our example bank in Exhibit 1, Column B, lines 1 through 10). This gives a preliminary earning change figure, which will be used to calculate a coefficient of ratio interactions.
Coefficient of ratio interactions. The actual year-to-year EPS change—wherein all critical ratios necessarily change at once—differs from the EPS change which results when we alter the ratios one at a time and sum the effects. For our example bank, the difference between the Latest Year's EPS of $2.75809 (B13) and the Previous Year's EPS of $2.45198 (B11) is $0.30611, while the summed effects of the one-at-a-time ratio alterations is $0.32669 (B10). Let's see why this is so.
The EPS equation is mathematically equivalent a series of four multipliers: (1) the common stock profit margin (i.e., net profit less preferred charge divided by revenue), (2) the asset turnover, (3) the reciprocal of the common equity ratio and (4) the book value. An isolated change to either the turnover ratio or the common equity ratio alters two multipliers and leaves two unchanged. An isolated change to any of the seven other critical ratios alters just one multiplier and leaves three unchanged.
Remember that in our above substitutions we changed only one critical ratio at a time, which always left two or more multipliers unaffected. But in the real world all the ratio values change together, and all the multipliers change along with them. The outcome is this: the effect of any one multiplier's change on EPS is mediated by the effects of the other multipliers' changes—which work together to amplify its impact on EPS.
This multiplication effect must be taken into account when we isolate the net impact of each ratio's change on EPS. To determine the net EPS effect of each ratio's change in value we use a two-step procedure:
First, we find the coefficient of ratio interaction (B15) by dividing the actual EPS change of $0.30611 (B13 minus B11) by the preliminary change of $0.32669 (B10); in this case 93.7%. (In situations where the actual change exceeds the preliminary change, the coefficient will exceed 100%.)
Then, we multiply each ratio's isolated EPS effect by the coefficient. For example, the increase in our bank's interest rate, examined in isolation, had a gross EPS effect of -$0.29022 (B1). Multiplying this amount by the coefficient 93.7% (C1) shows the net effect to be -$0.27194 per share (D1).
Multiplying the gross EPS effect of each ratio's change times the coefficient reveals the actual effect of that ratio's change on the EPS.
© 2000-2003 ThinkingApplied.com