14.1 Actual

К оглавлению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. Construct actual (historical) financial statements for Cutting Edge B2B Inc. in preparation for

forecasting their financial statements.

Solution Strategy. Enter actual values in the yellow input sections. Enter appropriate additions and

substractions to complete the Income Statement and Balance sheet. Then calculate the Key Assumptions

over the actual years.

FIGURE 14.1 Actual Assumptions & Income Statement for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

1. Set-up Row and Column Titles. Enter column titles, such as 1997, 1998, etc. in row 2 and

“Actual” vs. “Forecast” in row 3. Then, place the cursor in cell B4 and click on Window |

Freeze Panes. This freezes the top three rows as column titles at the top and freezes Column A as

a row title at the left. This step is essential to navigation in a large spreadsheet model.

2. Enter Actual Values. Enter three years of actual interest rates into the range C7:E8. Enter three

years of actual Income Statement values for Cutting Edge B2B Inc into the yellow input sections

of the range C13:E22 (see Figure 14.1). Enter the shares outstanding into the range C24:E24.

Enter dividends into the range C28:E28. Enter four years of actual Balance Sheet values into the

yellow input sections of the range B34:E55 (see Figure 14.2). Enter the observed market price /

share into the range B62:E62.

FIGURE 14.2 Actual Balance Sheet for Cutting Edge B2B Inc.

3. The Income Statement and Earnings Per Share. Some Income Statement items are based on

the Key Assumptions section, others are forecasted as a percentage of sales, and others are simple

additions or subtractions.

o Gross Margin = Sales - Cost of Good Sold. Enter =C13-C14 in cell C15 and copy it

across.

o Earnings Before Interest and Taxes (EBIT) = Gross Margin - SG&A Expense. -

Depreciation. Enter =C15-C17-C18 in cell C19 and copy it across.

o Net Income = EBIT- Interest Expense - Taxes. Enter =C19-C21-C22 in cell C23 and

copy it across.

o Earnings Per Share = Net Income / Shares Outstanding. Enter =C23/C24 in cell C25 and

copy it across.

o Change in Equity = Net Income - Dividends. Enter =C23-C28 in cell C29 and copy it

across.

4. The Balance Sheet and the Plug Item. A Balance Sheet, by definition, must balance. Therefore,

one line on the forecasted balance sheet must be a slack or plug item. There is some discretion in

what you choose to be the plug item. In this case, the plug item is Long-term Debt. It is the

residual item after everything else is forecast. Specifically, the Balance Sheet works as follows:

o Total Current Assets = Sum of the Current Asset Items. Enter =SUM(B34:B36) in cell

B37 and copy it across.

o Net PPE = Property, Plant and Equipment – Accumulated Depreciation. Enter =B39-B40

in cell B41 and copy it across.

o Total Assets = Total Current Assets + Net PPE. Enter =B37+B41 in cell B43 and copy it

across.

o Total Liabilities and Shareholders Equity is set equal to Total Assets. Enter =B43 in cell

B59 and copy it across.

o Retained Earnings on the First Date is set directly. Enter $91.81 in cell B56. Retained

Earnings on date t = (Retained Earnings on date t-1) + (Change in Equity from the

Income Statement). Enter =B56+C29 in cell C56 and copy it across.

o Total Shareholders’ Equity = Paid-in Capital + Retained Earnings. Enter =B55+B56 in

cell B57 and copy it across.

o Total Liabilities = Total Liabilities and Shareholders Equity - Total Shareholders’ Equity.

Enter =B59-B57 in cell B52 and copy it across.

o Total Current Liabilities = Accounts Payable + Short-term Debt. Enter =B47+B48 in cell

B49 and copy it across.

o Long-term Debt = Total Liabilities – Total Current Liabilities. Enter =B52-B49 in cell

B51 and copy it across.

5. Other Comparisons. Debt / (Debt + Equity) = (Short-term Debt + Long-term Debt) / (Short-term

Debt + Long-term Debt + Total Shareholders’ Equity). Enter =(B48+B51)/ (B48+B51+B57) in

cell B61 and copy it across. The formula for the firm’s External Funds Needed = (Increase in

Total Assets) – (Increase in Retained Earnings) – (Increase in Accounts Payable). Enter =(C43-

B43)-(C56-B56)-(C47-B47) in cell C63 and copy it across.

6. Key Assumptions. It is helpful to analyze key growth rates and ratios for past few years in order

to forecast those same items into the future.

o Sales Growth Rate(date t) = (Sales(date t) – Sales(date t-1)) / Sales(date t-1). Enter

=(D13-C13)/C13 in cell D5 and copy it across.

o Tax Rate = Taxes / (Before-Tax Income) = Taxes / (EBIT – Interest Expense). Enter

=C22/(C19-C21) in cell C6 and copy it across.

o Dividend Payout Rate = Dividends / Net Income. Enter =C28/C23 in cell C9 and copy it

across.

o Price / Earnings = (Market Price Per Share) / (Earnings Per Share). Enter =C62/C25 in

cell C10 and copy it across.

Now you are ready to Forecast the Financial Statements.

Problem. Construct actual (historical) financial statements for Cutting Edge B2B Inc. in preparation for

forecasting their financial statements.

Solution Strategy. Enter actual values in the yellow input sections. Enter appropriate additions and

substractions to complete the Income Statement and Balance sheet. Then calculate the Key Assumptions

over the actual years.

FIGURE 14.1 Actual Assumptions & Income Statement for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

1. Set-up Row and Column Titles. Enter column titles, such as 1997, 1998, etc. in row 2 and

“Actual” vs. “Forecast” in row 3. Then, place the cursor in cell B4 and click on Window |

Freeze Panes. This freezes the top three rows as column titles at the top and freezes Column A as

a row title at the left. This step is essential to navigation in a large spreadsheet model.

2. Enter Actual Values. Enter three years of actual interest rates into the range C7:E8. Enter three

years of actual Income Statement values for Cutting Edge B2B Inc into the yellow input sections

of the range C13:E22 (see Figure 14.1). Enter the shares outstanding into the range C24:E24.

Enter dividends into the range C28:E28. Enter four years of actual Balance Sheet values into the

yellow input sections of the range B34:E55 (see Figure 14.2). Enter the observed market price /

share into the range B62:E62.

FIGURE 14.2 Actual Balance Sheet for Cutting Edge B2B Inc.

3. The Income Statement and Earnings Per Share. Some Income Statement items are based on

the Key Assumptions section, others are forecasted as a percentage of sales, and others are simple

additions or subtractions.

o Gross Margin = Sales - Cost of Good Sold. Enter =C13-C14 in cell C15 and copy it

across.

o Earnings Before Interest and Taxes (EBIT) = Gross Margin - SG&A Expense. -

Depreciation. Enter =C15-C17-C18 in cell C19 and copy it across.

o Net Income = EBIT- Interest Expense - Taxes. Enter =C19-C21-C22 in cell C23 and

copy it across.

o Earnings Per Share = Net Income / Shares Outstanding. Enter =C23/C24 in cell C25 and

copy it across.

o Change in Equity = Net Income - Dividends. Enter =C23-C28 in cell C29 and copy it

across.

4. The Balance Sheet and the Plug Item. A Balance Sheet, by definition, must balance. Therefore,

one line on the forecasted balance sheet must be a slack or plug item. There is some discretion in

what you choose to be the plug item. In this case, the plug item is Long-term Debt. It is the

residual item after everything else is forecast. Specifically, the Balance Sheet works as follows:

o Total Current Assets = Sum of the Current Asset Items. Enter =SUM(B34:B36) in cell

B37 and copy it across.

o Net PPE = Property, Plant and Equipment – Accumulated Depreciation. Enter =B39-B40

in cell B41 and copy it across.

o Total Assets = Total Current Assets + Net PPE. Enter =B37+B41 in cell B43 and copy it

across.

o Total Liabilities and Shareholders Equity is set equal to Total Assets. Enter =B43 in cell

B59 and copy it across.

o Retained Earnings on the First Date is set directly. Enter $91.81 in cell B56. Retained

Earnings on date t = (Retained Earnings on date t-1) + (Change in Equity from the

Income Statement). Enter =B56+C29 in cell C56 and copy it across.

o Total Shareholders’ Equity = Paid-in Capital + Retained Earnings. Enter =B55+B56 in

cell B57 and copy it across.

o Total Liabilities = Total Liabilities and Shareholders Equity - Total Shareholders’ Equity.

Enter =B59-B57 in cell B52 and copy it across.

o Total Current Liabilities = Accounts Payable + Short-term Debt. Enter =B47+B48 in cell

B49 and copy it across.

o Long-term Debt = Total Liabilities – Total Current Liabilities. Enter =B52-B49 in cell

B51 and copy it across.

5. Other Comparisons. Debt / (Debt + Equity) = (Short-term Debt + Long-term Debt) / (Short-term

Debt + Long-term Debt + Total Shareholders’ Equity). Enter =(B48+B51)/ (B48+B51+B57) in

cell B61 and copy it across. The formula for the firm’s External Funds Needed = (Increase in

Total Assets) – (Increase in Retained Earnings) – (Increase in Accounts Payable). Enter =(C43-

B43)-(C56-B56)-(C47-B47) in cell C63 and copy it across.

6. Key Assumptions. It is helpful to analyze key growth rates and ratios for past few years in order

to forecast those same items into the future.

o Sales Growth Rate(date t) = (Sales(date t) – Sales(date t-1)) / Sales(date t-1). Enter

=(D13-C13)/C13 in cell D5 and copy it across.

o Tax Rate = Taxes / (Before-Tax Income) = Taxes / (EBIT – Interest Expense). Enter

=C22/(C19-C21) in cell C6 and copy it across.

o Dividend Payout Rate = Dividends / Net Income. Enter =C28/C23 in cell C9 and copy it

across.

o Price / Earnings = (Market Price Per Share) / (Earnings Per Share). Enter =C62/C25 in

cell C10 and copy it across.

Now you are ready to Forecast the Financial Statements.