Excel Reporting Ideas
Use this Excel 'Cash Flow Balance Sheet' to See Sources & Uses
of Funds Between Two Periods
Use Excel to get a clearer picture of how changes in your
Balance Sheet affect your cash flow
by Charley Kyd, MBA
Microsoft Excel MVP
The Father of Spreadsheet Dashboards
For most businesses, cash flow is more important than profits and
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.
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.
Follow this link to learn 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.
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.
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.
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 G10: =IF($B10*($D10-$E10)<0,-$B10*($D10-$E10),0)
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:
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.
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:
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
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
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.
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.