14.1 Actual
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 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.