Common Size Financial Statements

Financial statement analysis applies analytical tools and techniques to financial statements to determine the operating and financial success of a firm. The emphasis of the analysis depends on one’s viewpoint. A credit analyst extending a short-term, unsecured loan to a company might emphasize the firm’s cash flow and liquidity. An equity investor, on the other hand, may look closely at growth in sales, earnings and dividends. He would be interested in the variables that might have a significant impact on a firm’s financial structure, sales, earnings production and dividend policy.

In this installment of Spreadsheet Corner, we present a template that uses data from the balance sheet, income statement and cash flow statement to produce financial comparisons of interest to investors. We have attempted to make the line items industry-neutral so they are adaptable across companies in differing lines of business.

As the spreadsheet is very long, it is broken into four parts for easier viewing here, labeled Figure 1 Parts A through D. Raw data is entered from the balance sheet and income statement is shown in Part A and Part C. The calculations appear in Part B and Part D. Data for the next year can be added by copying the previous year’s formulas two columns to the right and entering the new data. You can download the spreadsheet here. The formulas and steps necessary to program the worksheet appear in Table 1.

Data Sources

These days, it is easy to locate detailed financial statement data online. Our comparison of the top online fundamental stock screening services appearing in this issue shows which of these offers statement data. A detailed table specifying the financial statement items provided by an expanded list of screening services is available at the online version of the comparison article at ComputerizedInvesting.com. For this article, we used data from Reuters.com, which offers five years of detailed financial statement data to registered users.

When looking at the raw financial statements a company provides in their annual or quarterly SEC-filed reports, be aware that the level of detail will differ from company to company. For example, some firms may only list a net property, plant and equipment figure on their balance sheet, while others will present a gross figure along with accumulated depreciation. Similarly, many companies do not break out depreciation and amortization expense on their income statement, opting to lump it in with selling, general and administrative expenses and report it on the cash flow statement. Lastly, some firms, especially financial companies, do not report current assets or current liabilities.

Principal Analysis Tools

The worksheet employs two principal tools for financial statement analysis—comparative financial statements and common size statements.

Comparative Financial Statements

Financial statements for a firm are easily compared by setting them up next to each other and examining how line items change from year to year. This statement data spanning several fiscal years and quarters is readily available on many investing websites.

When comparing the financial statements of a company, the goal is to identify trends and their rate of change. This can be accomplished by examining the statements over a number of years. It is vital to compare the changes of related items. For example, while sales may be increasing at a rate of 10% a year, cost of goods sold may be increasing at a rate of 15% a year. Perhaps material costs are rising and a new competitor is preventing the company from passing the higher cost on to customers. This will have implications for the earnings growth rate and, eventually, the share price, reshaping investor expectations.

Common Size Statements

Common size statements examine the proportion of a single line item to the total statement. For balance sheets, all assets are expressed as a percentage of total assets, while liabilities and equity are expressed as a percentage of total liabilities and shareholders’ equity. Income statement items are expressed as a percentage of revenues.

Common size analysis is sometimes called structural analysis because it examines the internal structure of the financial statements. For balance sheets, examining the asset side of things reveals how the firm has invested its capital to produce revenues. Examination of liabilities and equity reveals how the assets have been financed. Common size income statements reveal how well management is able to translate sales into earnings. Comparisons over a number of years are important, since no one year can capture the full dynamics of a firm.

Common size statements also allow for comparisons across companies in the same industry. This analysis disregards the absolute size of companies, and reduces all firms to a uniform format. These intercompany comparisons can identify weak or strong areas that might otherwise be overlooked through standard time series analysis across firms. Industry average ratios can be found at sites such as Reuters.com.

Figure 1. Financial Statement Analysis Spreadsheet

The formula for computing the proportion of one item to another is rather simple—the item being compared is divided by the comparison benchmark. For determining the proportion of cost of goods sold to revenues—also called the gross margin—the formula is:

% of sales = cost of goods sold ÷ revenues

When programming the spreadsheet template, the benchmark should be made an absolute row reference so that the formula can be entered only once and then copied down. To program the example above using Microsoft Excel, the following formula would be entered in cell D183:

D183: D150/D$149

The formula in cell D183 could then be copied down and across to compute the percentage of sales figures for other line items and other years.

Common size statements are usually prepared for the income statement and balance sheet, expressing information as follows:

• Income statement items expressed as a percentage of total revenue; and
• Balance sheet items expressed as a percentage of total assets.

Conclusion

When examining financial statements, we are trying to get a feel for management performance, capabilities, and even philosophy along with the company’s potential growth and profitability. Common size statements allow investors to compare the financial statements of different-sized companies in similar industries or examine trends in one company over time.

Click here to download spreadsheet.