Francis J. Clauss, Ph.D., teaches in the MBA program at Golden Gate University’s School of Business. He earned his Ph.D. from the University of Michigan and has both technical and management experience in the steel, chemical, and aerospace industries. He lives in Palo Alto, California.

CORPORATE FINANCIAL ANALYSIS with MICROSOFT EXCEL

By FRANCIS J. CLAUSS

The McGraw-Hill Companies, Inc.

Copyright © 2010 The McGraw-Hill Companies, Inc.
All right reserved.
ISBN: 978-0-07-162885-3

Contents

Chapter One

Corporate Financial Statements

CHAPTER OBJECTIVES

Management Skills

• Identify the three key financial statements of corporations (i.e., the income statement, balance sheet, and statement of cash flows) and describe their contents and purposes.

• Follow the standard formats for organizing items on financial statements.

• Interpret the items on financial statements and recognize how they’re related.

• Recognize when errors have been made in financial statements.

Spreadsheet Skills

• Create spreadsheets for financial statements.

• Organize the content of spreadsheets in logical formats.

• Label rows and columns to communicate clearly as well as to calculate correctly.

• Enter data values to show the basis for calculated values.

• Formulate and enter expressions to calculate values.

• Wrap text in rows or columns.

• Use cell references in expressions for calculated values that link the cells to other cells with data or other calculated values.

• Format values.

• Hide rows or columns of financial statements so that only selected ones are displayed.

• Link worksheets so that entries or values on one worksheet can be used for calculating values on another worksheet in the same workbook.

• Use Excel’s Formula Auditing tool to examine cell linkages.

• Where possible, include tests that automatically detect errors or validate results.

Overview

A firm’s financial health is summarized in three key financial reports: (1) the income statement, (2) the balance sheet, and (3) the cash flow statement. These reports summarize detailed information on a firm’s financial actions during the preceding fiscal year and its financial position at the end. The Securities and Exchange Commission (SEC) requires every corporation to include these reports in its annual stockholders’ report for at least the two most recent years.

Annual statements cover one-year periods ending at a specified date. For most firms, the ending date is the end of the calendar year. Many large corporations, however, operate on 12-month cycles (or fiscal years) that end at times other than December 31. In addition to annual reports to stockholders, corporations usually prepare monthly statements to guide a corporation’s executives, as well as quarterly statements that must be made available to stockholders of publicly held corporations.

Financial statements are based on values from a firm’s cost accounting system. The statements follow the generally accepted accounting principles (GAAP) recommended by the Financial Accounting Standards Board (FASB), which is the accounting profession’s rule-setting body. In addition to the financial statements, annual stockholders’ reports usually contain the president’s letter and historical summaries of key operating statistics and ratios for the past five or ten years.

The information in financial statements is used in several ways. Regulators, such as federal and state security commissions, use it to enforce compliance by providing proper and accurate disclosures to stockholders and investors. Lenders or creditors use the reports to evaluate the credit rating of firms and their ability to meet scheduled payments on existing or contemplated loans. Investors base their decisions to buy, sell, or hold the corporation’s stock on the information in the reports. Corporate financial managers use the information to ensure compliance with regulatory requirements, to satisfy creditors and shareholders, and to monitor the firm’s performance. They also use the information to determine the value of other firms they are thinking of buying, or the value of their own firms as a basis for negotiating a selling price. Employees peruse financial statements to assess how well their firm is doing and to compare its current performance with earlier periods. Corporate executives and boards of directors often view their annual stockholders’ reports as tools for marketing the company and its products and for building or improving their image.

This chapter shows how to use Excel to prepare financial statements. It defines the meanings of the financial entries and identifies the formulae for using data values of some to calculate values for others. The printouts in this chapter include column headings, row numbers, and grid lines to help identify the cells where the formulas are entered. These can be eliminated when printing the spreadsheets in reports. (Use the Sheet tab on File/Page Setup to show or hide them.)

The three financial statements have interlocking relationships to one another. Excel makes it possible to link cell entries in one worksheet to cell entries in another so that changing data values on either worksheet changes related entries on the other.

Preparing a spreadsheet begins with understanding its purpose: who will read it, what items it will contain, and how the items are related. Values for the items will be either data values or calculated values.

The Income Statement

Income statements provide a financial summary of a firm’s operation for a specified period, such as one year ending at the date specified in the statement’s title. They show the total revenues and expenses during that time. An income statement is sometimes called a “profit and loss statement,” an “operating statement,” or a “statement of operations.” Essentially, it tells whether or not the firm is making money.

Note that the income statement does not show cash flows or reflect the company’s cash position. (The cash flow statement does that.) Certain items, such as depreciation, are an expense although they do not involve a cash outlay. Some items, such as the sale of goods or services, are recognized as income even though buyers have not yet paid for them. Other items, such as purchased materials, are recognized as expenses even though the firm has not yet paid for them. Such income and expense items are recorded when they are accrued (e.g., when sold goods are shipped), not when cash actually flows.

General Format

Figure 1-1 shows the basic elements of an annual income statement. It indicates the essential information that must be provided and the standard format. Annual income statements for large corporations are organized in the same format as Figure 1-1. However, they often have a number of subdivisions with additional detail for selected items.

The income statement is organized into several sections. The upper section (Rows 4 to 15 of Figure 1-1) reports the firm’s revenues and expenses from its principal operations. Below that (Rows 16 to 24) are nonoperating items, such as financing costs (e.g., interest expense) and taxes.

The so-called “bottom line” (Row 25) reports the firm’s net income, or the net earnings that are available to the firm’s stockholders. Holders of preferred stock are first in line to be paid from the firm’s net income. They receive dividends in an amount that is fixed by the terms of the preferred stock. What is left is the “net earnings available for common stockholders” (Row 27). The last item is also expressed as the earnings per share (Row 28).

Firms generally divide the net earnings available for common stockholders between retained earnings and dividends paid to common stockholders (Rows 29 and 30). Retained earnings are the amount held by the company for future uses. They are the difference between the net earnings available and the amount paid to common stockholders. Retained earnings accumulate from year to year and are often used for repaying loans or financing new facilities or equipment.

Some Guides for Using Excel for Financial Modeling

Financial statements are only a few of the many reports used for communicating important information about a firm’s activities. You will meet others in the chapters that follow.

Communicating as Well as Calculating

As you use Excel to create financial models, keep the importance of communicating before you. Don’t think of an Excel spreadsheet as simply a sophisticated tool for calculating. Think of it also as a tool for communicating with others. As a communication tool, it must satisfy the four C s of good communications: C lear, C orrect, C omplete, and C oncise.

Spreadsheets are widely used for making presentations at business meetings and for presenting information in management reports. To be useful, they must be understandable to others—that is, to the attendees at meetings or the readers of reports. Because you may not have an opportunity to explain your work to others, your spreadsheets should be able to “stand on their own.” Well-designed spreadsheets make it easy for others to understand them. Another benefit of spreadsheets is that they can help the programmer recognize and correct errors.

Titles

Add short descriptive titles at the tops of worksheets. Those shown in Figure 1-1 identify the company and the type and date of the financial statement. The first is typed in Cell A1 and the second in Cell A2. After typing, each title can be centered across Columns A and B by dragging the mouse from Column A to Column B and clicking on the “Merge and Center” button on the format toolbar. You can use the “Bold” button to emphasize the text. You can use the “Fill Color” button to add color to the cells, and the “Font Color” button to change the color of the type.

You can change the font type and size to help distinguish titles from other entries by using the “Font” and “Font Size” buttons near the left of the format toolbar.

Row and Column Labels

It is usually good practice to type row and column labels before entering data values or making calculations. Labels should be short, descriptive, and accurate. Avoid labels that can be misunderstood. Expunge ambiguities. If you don’t understand something, don’t cover it up with a label that could be misleading. A good rule to follow is this: “It is not enough to use labels that are so clear that others can easily understand them. Labels must be so clear that others canNOT easily MISunderstand them.” (Think Murphy’s Law: “If anything can go wrong, it will.”)

It is important that labels include not only a title but also any units. For example, the label in Cell B3 identifies the entries below as being expressed or measured in thousands of dollars, except for the value for earnings per share (Cell B28). The entry in Cell B4 is actually 2,575,000 dollars.

Excel’s format toolbar contains a number of buttons and pull-down menus that are useful for formatting worksheets. Skim over the following sections on first reading, and return to them when you need help to format a worksheet.

Long Row Labels

Many row labels will be too long to fit into the default width of the columns, which is 8.43 units. There are several ways to remedy this:

1. Move the mouse’s pointer to the separation line with the next column at the top of the spreadsheet, hold the left button down, and drag the line far enough to the right for the label to fit.

2. Double-click on the separation line with the next column at the top of the spreadsheet. This will change the width of the column to the minimum needed to fit the longest label in the column.

3. Hold the mouse’s left button down and drag it over the column or columns whose width is to be changed. Click on Format/Column/Width and enter the value for the column width. (This assumes you know beforehand how wide you want to make the column(s) selected.)

4. Wrap the text so that it appears on more than one line. (That is more than one line, not more than one row.) To do this, click on the cell with the label to activate it, select “Alignment” from the Format menu on the format toolbar, and then click on the Wrap text button, as shown in Figure 1-2.

Long Column Labels

Long column headings, such as “$ thousand (except EPS)” can be shown in a single narrow column by wrapping the text so that it occupies two lines or more in a single cell. To wrap text, click on the cell and use Format/Cells/Alignment/Wrap text. Adjust row height, as necessary.

Boldfacing and Adding Color for Emphasis

To boldface information to stress its importance, click on the cells, rows, or columns with the information and then click on the B (Bold) button on the format toolbar or press the Ctrl/B keys. Add color or shading by selecting the cells, rows, or columns and clicking on the selection on the “Fill Color” button menu, which is also located on the format menu. Caution: Too much color can be distracting. Dark backgrounds make it difficult to read labels or values with black fonts.

Distinguishing between Data and Calculated Values

For discussion purposes and to distinguish them from calculated values, all data entries in Figure 1-1 have been italicized. This is done by selecting the data cell or cells and clicking the I (Italic) button on the format toolbar. You can also use color to distinguish between data and calculated values. (The author’s use of italics for data values is a personal choice. You may wish to use a different method. If you use italics, you can always change back to a normal font for printing the final copy of the spreadsheet.)

Formatting Values

Except for earnings per share, dollar values are entered with as many significant figures as available in the data. They are then usually formatted as either thousands or millions. For example, an entry of 12,345,678 might appear as 12,346 or as 12,346.78 if the income statement is given in thousands of dollars. You can use a custom format to express entries in thousands or millions of dollars. Formatting large values to minimize the number of decimal places that are displayed makes it easier to focus on what is significant. Even though some of the significant figures are not displayed, the precise value is carried in the cell so that there is no loss of accuracy by rounding the values for display.

Custom Formatting

Open a new spreadsheet and enter the value 12,345,678 in a convenient cell. Click on the cell and then click on Format on the menu bar. Select “Custom” from the list of categories on the Format menu and type #,###,;(#,###,) in the Type box, as shown in Figure 1- 3. This will cause the number 12,346 to appear in the cell, although the actual value is 12,345,678. (Note that the last digit shown has been rounded to 6 rather than 5.) If you use a minus sign so that the value in the cell is minus 12,345,678, the formatted value will appear as (12,346), with the value in parentheses. Try it.

If you wish to include one decimal place in the formatted values, change the custom format to #,###.0,;(#,###.0,). For two decimal places, change the custom format to #,###.00,;(#,###.00,). If you wish to add a dollar sign to the formatted value, with zero, one, or two decimal places, change the custom format to $#,###,;($#,###,), $#,###.0,;($#,###.0,) or $#,###.00,;($#,###.00,). Note the commas that are parts of the formats. Be sure to include them. You can use the “Format Painter” button on the standard toolbar to copy the format(s) of a cell (or range of cells) to another cell (or range).

You can also use the “Increase decimal” or “Decrease decimal” buttons on the formatting toolbar to increase or decrease the number of decimal places shown on the worksheet.

Indenting Subtopics in a List

Use the “Increase Indent” button on the format menu to indent selected labels or other text. You can also indent by pressing the spacebar before the text, but the indent button makes it easier.

Centering Entries

Use the “Center” button on the toolbar to center labels in the middle of a cell. Use the “Merge and Center” button to center labels in the middle of a group of cells next to one another.

Sheet Orientation

Sheet orientation can be either portrait or landscape. Portrait is preferable for spreadsheets that will be printed in reports because it avoids a reader having to twist the page in order to read it. Landscape is usually preferable for spreadsheets that will be used on projected slides.

Documenting

Documents often need to be traced back to their source. Adding your name and the date makes it easier to do this. A good place for the creator’s name and date is at the bottom of the worksheet on the right side. Use =today() to add the date in the cell to the right of the cell with your name. Or use the Header/Footer dialog box in the Page Setup menu to add this information at the top or bottom of printed copies of your worksheet. Another way of documenting a worksheet is to include a formal documentation sheet as the first sheet in the folder.

Column Headings and Row Numbers

In order to refer to particular cells in the spreadsheets shown in the text, both the row numbers and the alphabetic column headings are included, as in Figure 1-1. These can be removed before printing the worksheets in corporate reports. To do this, change the settings in the File/Page Setup/Sheet tab dialog box to omit them. Figure 1-4 shows the result.

The Items on an Income Statement

Once you have created a skeleton of the spreadsheet with a title, column headings, and row labels, you are ready to flesh it out with data and calculated values. These are the “meat and potatoes” of what the spreadsheet is all about. You may, of course, edit the spreadsheet later to improve your first attempt at creating the skeleton.

Cell entries will include both data values and expressions for calculated values. In Figure 1-1, the entries in Cells B4, B5, B8, B9, B10, B11, B14, B17, B18, B22, B26, and B29 are data values and are italicized to distinguish them from the calculated values in other cells. Note that the number you enter in Cell B4, for example, is NOT 2575.0, as it appears in Figure 1-1. The actual number entered is 2,575,000. It appears as 2,575.0 because it has been custom formatted that way. Even though the number appears as 2,575.0 on the spreadsheet, the column heading makes it clear that the actual values in the column have been formatted to appear as thousands of dollars (except for earnings per share, EPS).

Large corporations usually format values on their financial statements to millions rather than thousands of dollars. (See the preceding section for details on how to custom format numbers.)

Total Operating Revenues (or Total Sales Revenues) is the income earned from the firm’s operations during the fiscal year reported. Note that revenues are reported when they are earned, or accrued, even though no cash flow has necessarily occurred (as, for example, when goods are sold for credit or when services are rendered before being paid for).

(Continues…)

Excerpted from CORPORATE FINANCIAL ANALYSIS with MICROSOFT EXCELby FRANCIS J. CLAUSS Copyright © 2010 by The McGraw-Hill Companies, Inc.. Excerpted by permission of The McGraw-Hill Companies, Inc.. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.