Chapter 6Cash Flow |
| About This Chapter |
This chapter explains how to create Cash Flow reports using an Excel worksheet, and includes the following sections:
F Overview, page 98, introduces the concept of Cash Flow reports.
F Sources and Uses of Cash, page 99, presents the sources and uses of cash.
F Creating a Cash Flow Report in an Excel Worksheet, page 100, describes the steps needed to create a Cash Flow report using an Excel worksheet.
For example:
F Do Current Assets increase due to collecting Accounts Receivable balances or from slow Inventory sales?
F Has the company increased its Current Liabilities with short-term loans, which might result in a higher interest payment in the future?
The net Cash Flow is calculated by subtracting the uses of cash from the sources of cash.
Sources and Uses of Cash
Sources of cash include:
F Cash from Operating Activities: The net income as calculated in the Income Statement report (after income taxes and before Retained Earnings to the beginning of the year), adjusted by expenses not paid in cash during the reporting period, such as Depreciation and Amortization to Fixed and Other Assets.
F Decrease in Current Assets: A decrease in Current Assets, except for Cash in bank.
F Selling of Fixed Assets.
F Selling of Other Assets.
F Increase in Current Liabilities.
F Increase in Long Term Liabilities.
F Money Paid by Stockholders for Capital.
F Cash in Bank at the Beginning of the Year.
F Loss from operating activities: The net loss as calculated in the Income Statement report (after income taxes and before Retained Earnings to the beginning of the year), adjusted by expenses not paid in cash during the reporting period, such as Depreciation and Amortization to Fixed and Other Assets.
F Increase in Current Assets: An increase in Current Assets, except for Cash in bank.
F Investments of Fixed Assets.
F Investments of Other Assets.
F Decrease of Current Liabilities.
F Decrease of Long Term Liabilities.
F Buying Stock Back from Stockholders.
Creating a Cash Flow Report in an Excel Worksheet
Figure 6‑1: Cash Flow Report
The following sections present step-by-step instructions for creating a Cash Flow report.
Step 1:Adding a Third Period Column to the Balance Sheet Report
To calculate the increase or decrease between the balances of two reporting periods, add a third period (that is, a third column) to a copied worksheet 31 – Balance Sheet you created in Chapter 4, Balance Sheet.
Ø To add a third period column:
1. Copy worksheet 31 – Balance Sheetbyselecting the 31 – Balance Sheettab and dragging it to a new place between worksheets while pressing <Ctrl>.
2. Insert the appropriate formulas in the cells in column E, the formula in cell E11:
=SUMIF(TB_DB_Level3,A11,OFFSET(TB_DB_Level3,0,MonthSelectionNumber+2-24((
Step 2:Defining Names
The next step in creating a Cash Flow report is defining Names.
Ø To define the cell Names in the new three-column balance sheet:
F Define the cells' Names in column C (as shown in Step 1:Adding a Third Period Column to the Balance Sheet Report, page 101) using the text of the Account Types in column A, for example, Cash for cell C11, AccountsReceivable for cell C12 and Inventories for cell C13.
Ø To define cells' Names in a Income Statement report:
F Define the Name NetIncome for cell C39, which contains the net income, and define the Name Depreciation for cell C27, as shown in the figure below.
Ø To define a Name:
1. Select the cell, and then press <Ctrl+F3>.
2. In the Names in workbook box, type the desired text and click OK.
Step 3:Inserting Formulas into the Cash Flow Report
In this step, you will enter the formulas that return the balances from the Income Statement report.
Ø To insert the Net Income link formula:
1. Type the following formula into cell C11:
= NetIncome
2. Type the following formula into cell D11:
=OFFSET(NetIncome,0,1)
This OFFSET formula diverts the cell address (cell Name: netIncome) from the reference address by a certain number of rows and columns, and returns the value from the diverts cell address. In this example, the value to be returned is the Net Income for the previous reporting date, for which the year ends December 31, 2002.
3. Continue inserting formulas into cells C13 and D13 of the Cash Flow report using the Name Depreciation you defined in Step 2:Defining Names, page 102.
Ø To insert formulas to calculate the increase or decrease in the Account Groups balances in the three-column Balance Sheet report:
1. Type the following formula into cell C18:
=(AccountsReceivable-OFFSET(AccountsReceivable,0,1))*-1
This formula subtracts the Accounts Receivable balance from the Accounts Receivable balance for the previous reporting period.
2. Multiply the formula by -1. A positive difference in the Accounts Receivable over the two periods indicates a decrease in cash (that is, less money has come in).
3. Type the following formula in cell D18:
= (OFFSET(AccountsReceivable,0,1)-OFFSET(AccountsReceivable,0,2))*-1
This formula returns the subtracted figure from the next two periods, columns 2 and 3 (columns D and E in the three‑column balance sheet shown in Step 1:Adding a Third Period Column to the Balance Sheet Report, page 101).
4. Type the following formula in cell C22:
=Line_of_Credit-OFFSET(Line_of_Credit,0,1(
5. Type the following formula in cell D22:
=OFFSET(Line_of_Credit,0,1)-OFFSET(Line_of_Credit,0,2(
6. Type the following formula in cell C42 (cash at the beginning of the year):
=OFFSET(Cash,0,1)
7. Type the following formula in cell D42:
=OFFSET(Cash,0,2(
Ø To audit the Cash Flow balance:
F The figure in cell C48 in the Cash Flow report (shown below) is the Cash balance that appears in the Balance Sheet report. These figures should be equal to the balance of the Cash Flow report in cell C43 (as shown in the cells in row 43).
