Home > Excel Solutions > 

Management Reporting

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
August, 2008

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.

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 balances.

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.

Begin 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 Depreciation.

Now, enter the two formulas shown for the specified cells:

E3:  =IF($H3*($C3-$B3)>=0,"",-$H3*($C3-$B3))
F3:  =IF($H3*($C3-$B3)<=0,"",$H3*($C3-$B3))

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 number.

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 selection.

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.