For most businesses, cash flow is more important than profits and losses.
The reason is clear. A profitable company with negative cash flows may not survive. But a money-losing company with positive cash flows can survive for as long as the cash lasts.
Reporting Cash Flows in Excel
We all know what cash is. But what’s cash flow?
One common short-hand definition of cash flow is net profit plus depreciation. This definition isn’t worth much, however. If your company is breaking even, your customers don’t pay, your inventory has doubled, and all your vendors have put you on COD, this formula would say that your cash flow is positive.
Public accountants define cash flow using a Statement of Cash Flows. That report reorganizes the Balance Sheet by degree of liquidity to show how the change in Cash can be explained by changes in the other items.
For management reporting, however, it’s often useful to show cash flows using the traditional method of Sources and Uses of Funds, as shown in this Excel report.
This statement, shown here, uses a balance-sheet format to present its cash flow information.
Unlike most balance sheets, this report shows balances at two different dates, and then categorizes the change in each item as a source or a use of funds.
Too often, non-financial managers ignore the balance sheet, with a shrug and the thought, “So what?”. Instead, they concentrate on the income statement, which provides a more obvious record of performance.
However, when a balance sheet includes a section of Sources and Uses of Funds, it answers the “so what?” question. It shows whether Cash has increased or decreased, and shows what brought that change.
Therefore, the primary advantage of this statement is that it gives managers important information about how their decisions about assets and liabilities can have a significant effect on the financial health of their company.
How to Create the Sources & Uses Report in Excel
The figure illustrates a section of the Sources & Uses report in an Excel spreadsheet. The rows not shown follow the same pattern explained on this page.
The first thing you might notice about this figure is its row and column headings, which make the documentation much easier to follow.
In Document Excel Worksheets with Pictures that Include Row and Column Headings I explain how to create similar figures for your own documentation.
Entering Your Data
Beginning in row 10, columns D and E contain balance sheet data. The way that you populate these columns depends on the technical resources that your company provides.
If your company uses an Excel-friendly OLAP database, then you can enter simple formulas to return this data from the OLAP database on your computer or on an OLAP server.
Otherwise, you must use the same method you always use to populate your Excel spreadsheets.
Print Area
The dashed line in the figures above or below shows part of the print area. All areas outside the dashed line will not be printed in the report.
To define the print area, select the area you want to print then choose File, Print Area, Set Print Area. Alternatively, you can define the name Print_Area. To do so, choose Insert, Name, Define, enter Print_Area as the name, then choose OK.
Sign Column
Column B contains the value 1 where the natural sign of the account is a debit, and minus 1 where the natural sign is a credit. That is, assets have a 1 in column B and liabilities have a minus 1.
Source-Use Formulas
Beginning in row 10, all values in columns D and E are positive for values that have their natural sign. That is, both assets and liabilities are shown as positive numbers if they have debit and credit balances, respectively.
The formulas for the Source and Use columns are key to this report. They use this logic:
- An increase in a debit account (typically, an asset) is a use of funds; a decrease is a source of funds.
- An increase in a credit account (typically, a liability or equity) is a source of funds; a decrease is a use of funds.
The formulas in row 10 follow this logic. You can copy them downward as needed.
Cell F10: =IF($B10*($D10-$E10)>0,$B10*($D10-$E10),0)
Cell G10: =IF($B10*($D10-$E10)<0,-$B10*($D10-$E10),0)
Error-Checking Formulas
It’s always a good idea to include formulas outside the print range to check for errors in your report. Here’s one such formula:
D3: =D15-D19-D23-D25
This formula merely returns the value of assets less liabilities, less equity, less year-to-date profits. If your accounts are in balance, this formula always should equal zero.
Copy the formula to cell E3.
Number Formats
Notice that cell C7 informs us that numbers are displayed in millions of dollars. The numbers in columns D through G use the following format to do this:
#,##0.0,,;-#,##0.0,,;
This format string begins with #,##0.0, which tells Excel to display positive numbers in the format shown. The two commas after #,##0.0 tells Excel to display numbers in millions. (Using one comma would display the numbers in thousands.)
The format string between the two semicolons tells Excel how to format negative numbers.
The format string after the last semi-colon tells Excel how to display zeros. Because no text is shown, zeros will not be displayed.
To use this format first select the range of cells that you want the format to apply to. Choose Format, Cells. Choose the Number tab, and then choose the Custom category. In the Type edit box, enter the format shown.
Underlines
The underlines in row 9 use Excel borders. But unlike most borders, these show a break between columns. These breaks are created by using a thick vertical, white border.
To create these breaks, select the range D9:G9. Choose Format, Cells. In the Border tab, click on the bottom border area as you normally would.
Choose the thickest line style. In the Color list box, choose the white option. Then, in the border-selection area, click on the left, middle, and right border options. You’ll see virtually no change in the Border dialog box. But when you choose OK your spreadsheet should display borders with the line-breaks shown in the figure.
Next Steps
After you populate columns D and E with data from your balance sheet, the Sources & Uses report provides a useful explanation of where your cash has come from in recent months, and where it’s gone.
However, the report has one critical flaw: It’s a manual report.
If you’re reporting only one company, each month you’ll need to take the time to update the balance sheet data with new values. If you’re reporting several divisions, you’ll need to update the data for each division, each month.
Manual updating causes two problems. First, it sucks up much of your time. Second, it’s error-prone.
This is why we’re so enthusiastic about Excel-friendly OLAP systems. With the right OLAP, we Excel users can improve the accuracy of our reports and significantly reduce the time we spend preparing them.
To learn more about the Sources and Uses report, and about OLAP, in several weeks the the following link will be active: How to OLAP-Enable a Sources and Uses Report.
In the meanwhile, good luck with your cash flow.
