Financial Statement Comparison

A typical part of the analytical review process is to compare financial statements at two points in time in order to identify which element has fluctuated the most and quantify the extent of the fluctuation. Often this procedure may identify a particular balance for greater audit focus.

How this is done manually

The steps to perform this analytic review using Excel, would be as follows:

1. For the two points in time, foot the amounts for each and note them at the bottom of the column of financial information.

2. Establish two additional columns, each of which will contain a running subtotal of the columns of financial information. The running subtotal may be computed using Excel with a formula adding the current amount to the prior subtotal amount.

3. Establish two additional columns, and for each compute the cumulative percentage by dividing the cumulative amount by the total.

4. For each row in the two columns of cumulative percentages, subtract the amounts and take the absolute value. This provides a measure of the extent of the cumulative difference.

5. Note the largest cumulative difference, expressed as a percentage. This is the row of the financial statements which contributed the most to the difference between the two periods.

How this is done with software

1. Highlight the financial data to be analyzed using different colors. In this example, the names of the accounts will be highlighted in light green, the first period account balances in light yellow and the second .period account balances in yellow.

2. On the Command Bar type the command "KS". Then specify the names of the colors used for each components, as well as the name of the sheet which is to contain the results of the analysis:

acct = color used for the account names

col1 = color used for the first period

col2 = color used for the second period

sheet= name of the worksheet where the data resides

recap = name of the sheet to contain the results of the analysis.

In the worksheet named QS.xls (included with the software distribution, there is a sheet named "Pinnacle West"). The command for the analysis of selected portions of the income statement would then be:

ks sheet="Pinnacle West" acct="light green" col1="light yellow" col2=yellow recap=$Pw

Note: the color values can also be entered as their color index value, see details

So the alternative command is:

ks sheet="Pinnacle West" acct=35 col1=36 col2=6 recap=$PW

An example - Income Statement

In the first example, we look at selected portions of comparative income statements for the years 2004 and 2003. We highlight the portion of the income statement of interest and enter the command "KS" (without the quotes).

The software reports that the largest difference is for the account "Operations and maintenance" and quantifies this with a "d-statistic" of .00865. The d-statistic is from the Kolmogorov-Smirnov test (K-S) which measures the difference between two distributions. The d-statistic can range from as little as 0 ("perfect match") to as much as 1 - ("perfect mismatch"). Generally, values of .05 or under indicate that there is a great degree of similarity, i.e. no significant fluctuation between the two. This is the case, here, i.e. there is very little fluctuation between the periods. Thus, the analytic review would indicate nothing unusual here.

Results of the analysis

The analysis is written to the recap sheet and contains both a text and a chart. The text of the worksheet is shown below (chart is further down)

Chart values are shown as follows on the worksheet:

The chart indicates that the account "Operations and maintenance" contributed the most to the difference in the periods. Note that the d-statistic is only 1.237% which indicates that the difference between the periods is not significant.

Main Form