14.6 Full-Scale Real Data

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97

Problem. Given historical 10K financial statements for Nike, Inc., forecast their financial statements

over the next three years.

Solution Strategy. Modify the financial statement spreadsheet developed for the fictional firm Cutting

Edge B2B Inc. by adding an additional level of detail found in the actual 10K financial statements of

Nike, Inc. Then forecast the financial statements in the same way as before.

FIGURE 14.9 Historical and Forecasted Assumptions and Income Statement for Nike, Inc.

How To Build Your Own Spreadsheet Model.

1. Open the Ratios Spreadsheet. Open the spreadsheet that you created for Corporate Financial

Planning - Ratios and immediately save the spreadsheet under a new name using the File | Save

As command.

FIGURE 14.10 Historical and Forecasted Balance Sheet for Nike, Inc.

2. Add and Delete Rows. Add rows using the Insert | Rows command and delete rows using the

Edit | Delete | Entire Row | OK command.

o Add two rows below Selling, Gen & Adm Expenses and label them Other

Income/Expense, Net and Restructuring Charge, Net.

o Add three rows below Inventories and label them Deferred Income Taxes, Income

Taxes Receivable, and Prepaid Expenses.

o Delete the rows for Property Plant & Equip. (PPE) and Accumulated Depreciation.

o Add one row below Net PPE and, on the two blank rows below Net PPE, label them

Intangible Asset & Goodwill and Deferred Inc. Taxes & Other.

o Move the Accounts Payable row by selecting the entire row, clicking on Edit | Cut,

then selecting the cell under Short-term Debt and clicking on Edit | Paste. Label the

row where Accounts Payble used to be as Current Portion of L.T. Debt. Relabel

Short-term Debt as Notes Payable.

o Add two rows below Accounts Payable and label them Accrued Liabilities, and

Income Taxes Payable.

o Add four rows below Paid-in Capital and label them Common Stock Class B , Capital

in Excess of Stated Value, Unearned Stock Compen., and Accum. Other Comp. Inc.

Relabel Paid-in Capital as Common Stock Class A.

3. Update The Summary Lines.

o EBIT: Enter =C15-SUM(C17:C20) in cell C21 and copy it across.

o Total Current Assets: Enter =SUM(B36:B41) in cell B42 and copy it across.

o Total Assets: Enter =B42+SUM(B44:B46) in cell B47 and copy it across.

o Total Current Liabilities: Enter =SUM(B51:B55) in cell B56 and copy it across.

o Long-term Debt: Enter =B61-B56-B59-B60 in cell B58 and copy it across.

o Total Shareholders' Equity: Enter =SUM(B64:B69) in cell B70 and copy it across.

4. Enter Historical Data. Enter the historical financial statements for Nike, Inc. Enter three years

of historical Income Statements into the yellow input sections of the range C13:E24 (see Figure

14.1). Enter the shares outstanding into the range C26:E26. Enter dividends into the range

C30:E30. Enter four years of historical Balance Sheets into the yellow input sections of the range

B36:E69 (see Figure 14.2). Enter the observed market price / share into the range B75:E75. All

of the data shown in Figures 14.1 and 14.2 come from Nike's 10K financial statement, which is

available on the web. To obtain Nike's 10K financial statement, go to Yahoo! Finance at

quote.yahoo.com, enter Nike's symbol NKE in the input box, click on the Get Quotes button, in

the More Info part of the table click on Research, then click on Financials, then at the very

bottom of the page click on EDGAR Online, then scroll down the list of financial statements,

click on 10K, and next to Online HTML Version click on the Click Here button. The 10K

statement that you get has a table of contents window on the left. Scroll down this window and

you will see the Income Statement, Balance Sheet, Cash Flow Statement, etc. Click on the item

that you want to look at.

5. Income Statement Percent of Sales. Given the rows that have been added to the Income

Statement, the Percent of Sales section needs to be updated. Add 2 rows to the Income Statement

Percent of Sales section someplace below row 79 using the the Insert | Rows command. To

carry the updated labels down, enter =A13 in cell A79. Then copy all of the formulas down by

selecting the range A79:I79 and copy it to the range A80:I97. Delete ranges that correspond to

blank sections of the Income Statement.

FIGURE 14.11 Historical and Forecasted Income Statement Percent of Sales for Nike, Inc.

6. Balance Sheet Percent of Sales. Given the rows that have been added and subtracted from the

Balance Sheet, the Percent of Sales section needs to be updated. Add 8 rows to the Balance Sheet

Percent of Sales section someplace below row 102 using the the Insert | Rows command. To

carry the updated labels down, copy the cell A97 to the range A100:A102. Then copy all of the

formulas down by selecting the range A102:I102 and copy it to the range A103:I138. Delete

ranges that correspond to blank sections of the Balance Sheet.

FIGURE 14.12 Historical and Forecasted Balance Sheet Percent of Sales for Nike, Inc.

7. Update The Forecast. Start with the Assumptions section. The Sales Growth Rate (which is a

key driver of the entire model) is a subjective category. One possible forecast is continued

recovery from the recent downturn with growth of 5.0%, 7.0%, and 9.0%. The tax rate, which in

reality reflects a variety of credits, exemptions, and adjustments, is forecast based on the average

realized tax rate in recent years. Enter =AVERAGE(C6:E6) in cell I6 and enter the average

realized rate 38.4% as the forecast. Interest rates are forecast to rise slightly over time. The

dividend payout rate is forecast to return to its previous level over time. The price/earnings ratios

is forecast to recover over time. All of the new lines added will be forecast using he "percent of

sales" method, so the "percent of sales" formulas from one row can be copied to the added rows.

Select the range F17:I17 and copy it to the ranges F18:I19, F39:I41, F45:I46, F51:I51, F54:I55,

F59:I60, and F67:I68. Turning to the Shares Outstanding on row 26, Nike's 10K is a little

unclear what about what corporate policy is. One possible forecast is flat at the current level of

269.6 million shares. Choose values for the equity choice variables: Common Stock A, Common

Stock B, and Capital in Excess of Stated. Enter values in the range F64:I66, which maintain a

"reasonable" debt / (debt + equity) proportion.

8. Cash Flow Statement. Theoretically, the cash flow statement is strictly determined by changes

in the balance sheet. As a practical matter, Nike's 10K cash flow statement has added a lot of

detail not directly observable from the balance sheet. Therefore, many rows must be added and

renamed in order to model the cash flow statement. Add rows using the Insert | Rows command.

o Add a row below Net Income and label it Income Charges (Credits) Not Affecting

Cash.

o Add three rows below Depreciation and label them Non-cash port. of restructuring

charge, Deferred Income Taxes, and Amortization and other.

o Add four rows below Inventories. Then move the Increase in Accounts Receivable

row by selecting the entire row, clicking on Edit | Cut, then selecting the cell under

Inventories and clicking on Edit | Past. Label the row where Increase in Accounts

Receivable used to be as Changes in Certain Working Capital Components. Label the

three blank rows below Increase in Accounts Receivable as Decrease (inc.) in Other

Current, Assets and Income Taxes Rec., and Increase (dec.) in Accounts Payable,.

Relabel Increase in Accounts Payable as Accrued Liab., & Income Taxes Pay.

o Relabel Investment in Plant and Equipment as Additions to Prop, Plant, and &

Equipment. Then add three row below Additions to Prop, Plant, and & Equipment

and label them as Disposals of Prop., Plant, & Equipment, Increase in Other Assets,

and Increase (dec.) in Other Liabilities.

o Relabel Increase in Long-term Debt as Additions to Long-term Debt. Then add a row

below Additions to Long-term Debt and label it Reductions in Long-term Debt.

o Relabel Increase in Short-term Debt as Increase (dec.) in Notes Payable. Relabel

Increase in Paid in Capital as Proceeds from Exercise of Options. Then add a row

below Proceeds from Exercise of Options and label it Repurchase of Stock.

o Add a row above Change in Cash and Equivalents and label it Effect of Exch. Rate

Changes on Cash. Relabel Change in Cash and Equivalents and label it Net Increase

(Dec) in Cash and Equiv. Add a row below Net Increase (Dec) in Cash and Equiv.

FIGURE 14.13 Historical and Forecasted Cash Flow Statement for Nike, Inc.

9. Update The Summary Lines of the Cash Flow Statement.

o Cash Provided By Operations: Enter =SUM(C142:C154) in cell C155 and copy it

across.

o Cash Used By Investing Activities: Enter =SUM(C158:C161) in cell C162 and copy it

across.

o Cash Used By Financing Activities: Enter =SUM(C165:C170) in cell C171 and copy it

across.

o Cash Provided By Operations: Enter =C155+C162+C171+C173 in cell C174 and copy

it across.

10. Enter Historical Data of the Cash Flow Statement. Enter three years of historical Cash Flow

Statements into the yellow input sections of the range C144:E173 (see Figure 14.5).

11. Cash Flow Statement Percent of Sales. Given the rows that have been added to the Income

Statement, the Percent of Sales section needs to be updated. Add 15 rows to the Cash Flow

Statement Percent of Sales section someplace below row 181 using the the Insert | Rows

command. To carry the updated labels down, copy A138 to the range A180:A181. Then copy all

of the formulas down by selecting the range A181:I181 and copy it to the range A182:I216.

Delete ranges that correspond to blank sections of the Cash Flow Statement.

FIGURE 14.14 Historical and Forecasted Cash Flow Statement Percent of Sales for Nike, Inc.

12. Update The Cash Flow Forecast. All of the new lines added to the Cash Flow Statement will be

forecast using he "percent of sales" method, so the "percent of sales" formulas from one row can

be copied to the added rows. Select the range F144:I144 and copy it to the ranges F145:I147,

F152:I152, F154:I154, F158:I161, F165:I166, F168:I169, and F173:I173.

13. Financial Ratios. Most of the financial ratios carry over without adjustment. The only change

that is needed is the Debt percentage where an addition category of debt, Current Portion of L.T.

Debt, was added. Enter =(C51+C52+C58)/C47 in cell C230 and copy it across.

FIGURE 14.15 Historical and Forecasted Financial Ratios for Nike, Inc.

The percentage of sales method does a good job for most purposes. Additional refinements would

increase accuracy of the forecast. For example, some items may be better projected as a trend, rather than

an average. Other items, such as the Restructuring Charge, may be one time events. The bottom line of

this forecast is a gradual recovery in Earnings Per Share from $1.75 to $1.95 to $2.15.

Problems

Skill-Building Problems.

1. Given historical financial statements for Global Impact P2P in the Excel file Fplanpro.xls or

shown below, forecast their financial statements for the next three years. Explore the company's

needs for additional financing as expressed by the following choice variables: debt and equity

(paid-in capital under shareholder's equity).

2. Given historical and forecasted Income Statements and Balance Sheets for Global Impact P2P,

create the historical and forecasted Cash Flow Statement.

3. Given historical and forecasted financial statements for Global Impact P2P, create the historical

and forecasted financial ratios.

Skill-Extending Problems.

4. Select a company with publically traded stock. Locate the historical 10K financial statements for

that company over the past few years. This data is available from Yahoo! Finance and EDGAR

On-line and can be obtained by following the procedure described in Step 4 of Corporate

Financial Planning - Basics. Forecast your company's financial statements over the next three

years.

Live In-class Problems.

5. Given the partial Actual spreadsheet FplanacZ.xls, do step 3 The Income Statement and

Earnings Per Share.

6. Given the partial Forecast spreadsheet FplanfoZ.xls, do step 4 The Income Statement.

7. Given the partial Cash Flow spreadsheet FplancaZ.xls, do step 5 Cash and Equivalents.

8. Given the partial Ratios spreadsheet FplanraZ.xls, do step 2 Profitability.

9. Given the partial Sensitivity spreadsheet FplanseZ.xls, do step 2 Data Table.

10. Given the partial Full-scale Real Data spreadsheet FplanfuZ.xls, do Key Assumptions part of

step 7 Update the Forecast.

FIGURE 14.16 Historical Assumptions and Income Statement for Global Impact P2P

FIGURE 14.17 Historical Balance Sheet for Global Impact P2P