Excel Reporting Ideas
Add Cash Flow Information to Your Excel Balance Sheets
A simple change to your balance sheet created in Excel can provide key information to help you manage your cash flow more effectively.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
When most people look at financial statements they tend to
concentrate on the income statement, and ignore the balance sheet. The
most likely reason is that for most people, the balance sheet fails to answer the first
question they ask: "So what?"
Ignoring the balance sheet is big mistake because it can -- but
seldom does -- provide useful information about your
company's cash flow.
|Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more.
In the US, financial statements prepared by CPAs are supposed to
include Cash Flow Statements. To prepare these statements, CPAs find the
changes by line item between two balance sheets, and then rearrange them
in a way that makes sense to accountants.
But you can provide similar cash flow information without creating
that extra report. Just include an earlier balance sheet balance, along with a statement of
Sources and Uses, as shown in this figure.
Of course, there's nothing wrong with also creating a Cash
Flow Statement. In fact, by including Sources and Uses columns in the
balance sheet, you can help your audience to understand the link between
the two statements.
Understanding Sources and Uses of Funds
Column B shows the balance for an earlier period, and column C shows the
balance for the current period. Columns E and F show the Sources and
Uses of funds represented by the changes in the two balance sheet
A source of funds is represented by a decrease in an asset or
by an increase in a liability or an equity account.
To illustrate, Other Current Assets decreased by 1,195, and was thus
a source of funds. And Retained Earnings increased by 2,546, providing
another source of funds. (Retained Earnings increases by the amount of
Total Profits from the Income Statement.)
A use of funds is represented by an increase in an asset or
by a decrease in a liability or equity.
To illustrate, Accounts Receivable increased by 4,614, which was the
largest use of funds above. The next-largest use was to reduce Accounts Payable by 2,315.
You can see that by adding the Source and Use columns to the balance
sheet we easily can answer the "So What?" question. To illustrate, we no longer
need to ask "So what?" when we look the Account Receivable balance of
17,927. Instead, we can see that it increased significantly during the
past year, reducing
our cash flow by 4,614.
Creating the Enhanced Balance Sheet
It's easy to add the formulas that calculate Sources and Uses.
with a standard balance sheet in Excel. Insert a new column (here,
column B) to contain an earlier period. (Typically, the earlier period
is the ending period of your prior fiscal year, but it can be any period
you want.) Then enter data and formulas to
return information about the change from the earlier period.
Next, set up the Sign column, as shown in column H. Enter the number
1 for each line item that has a natural debit balance. In the balance
sheet, this typically includes only your assets.
Then enter the number -1 for line items with a natural credit
balance. This typically includes items that are liabilities and equity.
This also includes items that reduce your assets, the two most-common of
which are Allowance for Doubtful Accounts and Allowance for
Now, enter the two formulas shown for the specified cells:
Here's how to interpret the formula for cell E3: Calculate the
change between the two periods ($C3-$B3). Multiply by the sign ($H3),
which causes negative results to represent a source of funds, and
positive results to represent a use of funds. Because we're interested
in Sources in this cell, we need to ignore Uses. Therefore, if the
result is positive, return a null string (""). But if the result is
negative, switch its sign (-) and display the result as a positive
The formula in cell F3 uses nearly the same logic. Here, however, we
display positive results as a Use of funds.
In both formulas, I chose to display zero values as null strings.
Copy the range E3:F3 to the range E4:E25. Then erase the formulas in
rows that have no descriptions in column A.
Complete Your Balance Sheet
Format your balance sheet any way you want and set up your
Print Area to exclude the Sign column.
To set up the Print Area, first select the area in your balance
sheet that's equivalent to the shaded area above.
- In Classic Excel, choose File, Print Area, Set Print Area.
- In New Excel, choose Page Layout, Page Setup, Print Area, Set Print Area.
- Or, in either version, assign the name Print_Area to your
Finally, notice the date in cell F1 in the figure above. A simple
formula returns the date as text, rather than as a formatted number. We
must use text rather than a number because column F isn't wide enough to
contain the date as a number.
Enter the formula for the cell shown:
F1: =TEXT(C2,"mmmm, yyyy")
Here, cell C2 contains a date serial number. However, your balance
sheet probably will use a different cell to contain that information.