Working Capital and Cash Flow Analysis in Excel

This chapter describes the process of accounting for and analyzing cash flows. Using tools that are available to you in the form of different functions and links, you'll see how to use Excel to convert the information in a balance sheet and income statement to a cash flow statement.

Cash is the most liquid of all assets, so many managers are particularly interested in how much cash is available to a business at any given time. Because the flow of cash into and out of a business is mainly a matter of investing (purchasing assets) and disinvesting (disposing of assets), an analysis of cash flows can help measure management's performance.

This chapter describes the process of accounting for and analyzing cash flows. Although it doesn't replace them, the cash flow statement is a useful adjunct to income statements and balance sheets. Using tools that are available to you in the form of different functions and links, you'll see how to use Excel to convert the information in a balance sheet and income statement to a cash flow statement.

To set the stage, the next section discusses how costs are timed.

Matching Costs and Revenues

Several other chapters of this book mention the matching principle—the notion that revenue should be matched with whatever expenses or assets produce that revenue.

This notion leads inevitably to the accrual method of accounting. If you obtain the annual registration for a truck in January and use that truck to deliver products to your customers for 12 months, you have paid for an item in January that helps you produce revenue all year long.

If you record the entire amount of the expense in January, you overstate your costs and understate your profitability for that month. You also understate your costs and overstate your profitability for the remaining 11 months.

Largely for this reason, the accrual method evolved. Using the accrual method, you would accrue 1/12th of the expense of the truck registration during each month of the year. Doing so enables you to measure your expenses against your revenues more accurately throughout the year.

Similarly, suppose that you sell a product to a customer on a credit basis. You might receive periodic payments for the product over several months, or you might receive payment in a lump sum sometime after the sale. Again, if you wait to record that income until you have received full payment, you will misestimate your profit until the customer finishes paying you.

Some very small businesses—primarily sole proprietorships—use an alternative to accrual, called the cash method of accounting. They find it more convenient to record expenses and revenues when the transaction took place. In very small businesses, the additional accuracy of the accrual method might not be worth the effort. An accrual basis is more complicated than a cash basis and requires more effort to maintain, but it is often a more accurate method for reporting purposes.

The main distinction between the two methods is that if you distribute the recording of revenues and expenses over the full time period when you earned and made use of them, you are using the accrual method. If you record their totals during the time period that you received or made payment, you are using the cash method. As an example of the cash method of accounting, consider Figure 5.1.

Figure 5.1 The cash basis understates income when costs are not associated with revenue that they help generate.

Suppose that Jean Marble starts a new firm, Marble Designs, in January. At the end of the first month of operations, she has made $10,000 in sales and paid various operating expenses: her salary, the office lease, phone costs, office supplies, and a computer. She was able to save 20% of the cost of office supplies by making a bulk purchase that she estimates will last the entire year. Recording all of these as expenses during the current period results in net income for the month of $1,554.

Using the accrual method, Marble Designs records 1/12th of the cost of the office supplies during January. This is a reasonable decision because they are expected to last a full year. It also records 1/36th of the cost of the computer as depreciation. The assumption is that the computer's useful life is three years and that its eventual salvage or residual value will be zero. The net income for January is now $5,283, which is 3.4 times the net income recorded under the cash basis.

The net income of $5,283 is a much more realistic estimate for January than $1,554. Both the office supplies and the computer will contribute to the creation of revenue for much longer than one month. In contrast, the benefits of the salary, lease, and phone expenses pertain to that month only, so it is appropriate to record the entire expense for January.

But this analysis says nothing about how much cash Marble Designs has in the bank. Suppose that the company must pay off a major loan in the near future. The income statement does not necessarily show whether Marble Designs will likely be able to make that payment.