14.3 Cash Flow
К оглавлению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. 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.