14.3 Cash Flow

К оглавлению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 and forecasted Income Statements and Balance Sheets for Cutting Edge B2B

Inc., create the historical and forecasted Cash Flow Statement.

Solution Strategy. Construct the cash flow statement by starting with Net Income from the Income

Statement and then picking up the year to year changes from the Balance Sheets.

FIGURE 14.6 Historical and Forecasted Cash Flow Statement for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

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

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

As command.

2. Cash Flow from Operating Activities. Start Net Income, then add or subtract year-to-year

changes in other Operating Activities items.

o Net Income = Net Income from the Income Statement. Enter =C23 in cell C116 and

copy it across.

o + Depreciation = Accumulated Depreciation (t) - Accumulated Depreciation (t-1). Enter

=C40-B40 in cell C117 and copy it across.

o - Increase in Accounts Receivable = -(Receivables (t) - Receivables (t-1)). Enter =-(C35-

B35) in cell C118 and copy it across.

o - Increase in Inventories = -(Inventories (t) - Inventories (t-1)). Enter =-(C36-B36) in cell

C119 and copy it across.

o + Increase in Accounts Payable = Accounts Payable (t) - Accounts Payable (t-1). Enter

=C47-B47 in cell C120 and copy it across.

o Cash Flow From Operating Activity = Sum of the Operating Activity items. Enter

=SUM(C116:C120) in cell C121 and copy it across.

3. Cash Flow from Investing Activities. Add or subtract year-to-year changes in Investing

Activities.

o - Investment in Plant and Equipment = -(PPE (t) - PPE (t-1)). Enter =-(C39-B39) in cell

C124 and copy it across.

o Cash Flow From Investing Activity = Sum of the Investing Activity items. Enter =C124

in cell C125 and copy it across.

4. Cash Flow from Financing Activities. Add or subtract year-to-year changes in Financing

Activities items.

o + Increase in Long-term Debt = Long-term Debt (t) - Long-term Debt (t-1). Enter =C51-

B51 in cell C128 and copy it across.

o + Increase in Short-term Debt = Short-term Debt (t) - Short-term Debt (t-1). Enter =C48-

B48 in cell C129 and copy it across.

o + Increase in Paid-In Capital = Paid-In Capital (t) - Paid-In Capital (t-1). Enter =C55-B55

in cell C130 and copy it across.

o - Dividends Paid = - Dividends from the Income Statement. Enter =-C28 in cell C131

and copy it across.

o Cash Flow From Financing Activity = Sum of the Financing Activity items. Enter

=SUM(C128:C131) in cell C132 and copy it across.

5. Cash and Equivalents. The last category sums up the cash flows from operations, investments,

and financing and "balances" the Cash Flow Statement by tying the sum of the cash flows to the

Change in Cash and Equivalents.

o Change in Cash and Equivalents = Cash Flow From Operating Activity + Cash Flow

From Investing Activity + Cash Flow From Financing Activity. Enter

=C121+C125+C132 in cell C134 and copy it across.

o Cash and Equivalents at the Beginning of the Year = Cash and Equivalents (t-1). Enter

=B34 in cell C135 and copy it across.

o Cash and Equivalents at the End of the Year = Change in Cash and Equivalents + Cash

and Equivalents at the Beginning of the Year. Enter =C134+C135 in cell C136 and copy

it across.

Notice that the $6.38 Cash and Equivalents at the End of Year 1998, which was obtained by summing all

of the cash flows from operations, investments, and financing together with the Beginning of the Year

balance for 1998, does indeed equal the $6.38 Cash and Equivalents at the Beginning of Year 1999. Thus,

the sum of the cash flows from operations, investments, and financing does equal the Change in Cash and

Equivalents. This balancing of the Cash Flow Statement is a direct consequence of the balancing of the

Balance Sheet. It is also a good way to check for possible errors in your spreadsheet.

Problem. Given historical and forecasted Income Statements and Balance Sheets for Cutting Edge B2B

Inc., create the historical and forecasted Cash Flow Statement.

Solution Strategy. Construct the cash flow statement by starting with Net Income from the Income

Statement and then picking up the year to year changes from the Balance Sheets.

FIGURE 14.6 Historical and Forecasted Cash Flow Statement for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

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

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

As command.

2. Cash Flow from Operating Activities. Start Net Income, then add or subtract year-to-year

changes in other Operating Activities items.

o Net Income = Net Income from the Income Statement. Enter =C23 in cell C116 and

copy it across.

o + Depreciation = Accumulated Depreciation (t) - Accumulated Depreciation (t-1). Enter

=C40-B40 in cell C117 and copy it across.

o - Increase in Accounts Receivable = -(Receivables (t) - Receivables (t-1)). Enter =-(C35-

B35) in cell C118 and copy it across.

o - Increase in Inventories = -(Inventories (t) - Inventories (t-1)). Enter =-(C36-B36) in cell

C119 and copy it across.

o + Increase in Accounts Payable = Accounts Payable (t) - Accounts Payable (t-1). Enter

=C47-B47 in cell C120 and copy it across.

o Cash Flow From Operating Activity = Sum of the Operating Activity items. Enter

=SUM(C116:C120) in cell C121 and copy it across.

3. Cash Flow from Investing Activities. Add or subtract year-to-year changes in Investing

Activities.

o - Investment in Plant and Equipment = -(PPE (t) - PPE (t-1)). Enter =-(C39-B39) in cell

C124 and copy it across.

o Cash Flow From Investing Activity = Sum of the Investing Activity items. Enter =C124

in cell C125 and copy it across.

4. Cash Flow from Financing Activities. Add or subtract year-to-year changes in Financing

Activities items.

o + Increase in Long-term Debt = Long-term Debt (t) - Long-term Debt (t-1). Enter =C51-

B51 in cell C128 and copy it across.

o + Increase in Short-term Debt = Short-term Debt (t) - Short-term Debt (t-1). Enter =C48-

B48 in cell C129 and copy it across.

o + Increase in Paid-In Capital = Paid-In Capital (t) - Paid-In Capital (t-1). Enter =C55-B55

in cell C130 and copy it across.

o - Dividends Paid = - Dividends from the Income Statement. Enter =-C28 in cell C131

and copy it across.

o Cash Flow From Financing Activity = Sum of the Financing Activity items. Enter

=SUM(C128:C131) in cell C132 and copy it across.

5. Cash and Equivalents. The last category sums up the cash flows from operations, investments,

and financing and "balances" the Cash Flow Statement by tying the sum of the cash flows to the

Change in Cash and Equivalents.

o Change in Cash and Equivalents = Cash Flow From Operating Activity + Cash Flow

From Investing Activity + Cash Flow From Financing Activity. Enter

=C121+C125+C132 in cell C134 and copy it across.

o Cash and Equivalents at the Beginning of the Year = Cash and Equivalents (t-1). Enter

=B34 in cell C135 and copy it across.

o Cash and Equivalents at the End of the Year = Change in Cash and Equivalents + Cash

and Equivalents at the Beginning of the Year. Enter =C134+C135 in cell C136 and copy

it across.

Notice that the $6.38 Cash and Equivalents at the End of Year 1998, which was obtained by summing all

of the cash flows from operations, investments, and financing together with the Beginning of the Year

balance for 1998, does indeed equal the $6.38 Cash and Equivalents at the Beginning of Year 1999. Thus,

the sum of the cash flows from operations, investments, and financing does equal the Change in Cash and

Equivalents. This balancing of the Cash Flow Statement is a direct consequence of the balancing of the

Balance Sheet. It is also a good way to check for possible errors in your spreadsheet.