# Working Capital and Cash Flow Analysis in Excel

1. Matching Costs and Revenues
2. Broadening the Definition: Cash vs. Working Capital
3. Analyzing Cash Flow
4. Summary
• Print
This chapter is from the book

## Broadening the Definition: Cash vs. Working Capital

So far, we have discussed funds in terms of cash only. A broader and more useful way of looking at the availability of funds involves the concept of working capital.

How does your company create income? If you manufacture a product, you use funds to purchase inventory, produce goods with that inventory, convert those goods into accounts receivable by selling them, and convert accounts receivable into cash when you take payment. If you are a merchandising firm, the process is basically the same, although you probably purchase finished goods instead of producing them.

Each of the components in this process is a current asset, such as an asset that you can convert into cash in a relatively short period (usually, but not always, one year) as a result of your normal business operations. Inventory and accounts receivable, for example, are not as liquid as cash, but your business expects to convert both to cash before too long.

Current liabilities, on the other hand, are obligations that you must meet during the same relatively short time period that defines your current assets. Notes payable, accounts payable, and salaries are examples of current liabilities.

### Determining the Amount of Working Capital

Working capital is the result of subtracting current liabilities from current assets. It is a measure of a company's solvency, its capacity to make large purchases and take advantage of bulk discounts, and its ability to attract customers by offering advantageous credit terms.

#### Accounting for the Cash Transactions

Figure 5.3 shows the cash transactions that occur during January. Because they are all cash transactions, they don't include the inventory purchased on credit.

Jean Marble establishes capital for her new firm by investing \$9,000 at the outset. Of the \$10,000 in sales that she makes during the first month, she receives cash payment of \$2,000: Her cash account is now \$11,000. Out of that \$11,000, she makes cash payment for the following:

• \$3,500 for her salary
• \$900 for the monthly office lease
• \$96 for the telephone line
• \$2,000 for office supplies
• \$1,950 for a computer

#### Getting an Adjusted Trial Balance

These transactions are shown in a format that helps Marble move to a trial balance and then to an adjusted trial balance (see Figure 5.4), and subsequently to an income statement and balance sheet.

On the left (or debit) side of the trial balance are the ending cash balance of \$2,554, the accounts receivable of \$8,000 (recall that sales of \$10,000 were made and \$2,000 in cash payments were received), the inventory purchased with the \$2,000 loan, and the office supplies, computer, salary, lease, and telephone service paid for with cash.

On the right, or credit, side of the trial balance are Marble's initial capital investments into Cash, the \$10,000 in sales, and the \$2,000 borrowed to purchase the inventory.

The adjustments to the trial balance include \$54.17 in depreciation on the computer during the first of 36 months of its useful life, and the \$166.67 worth of supplies prorated during the first month. The \$54.17 in depreciation is found with this formula:

`=SLN(B8,0,36)`

SLN() is the Excel function that returns straight-line depreciation. You will learn more about Excel's depreciation functions in Chapter 16, "Fixed Assets." This particular entry computes the monthly depreciation on the value in cell B8 (\$1,950), assuming that its eventual salvage value will be \$0 and that its useful life is 36 months.

The value of Office Supplies consumed during the month is \$166.67: the value of \$2,000 in cell B7 divided by 12.

These adjustments are combined with the trial balance to arrive at the debit amounts in the adjusted trial balance. The worksheet accomplishes this by means of this array formula in cells F4:F18:

`=IF(DebitBalance>=0,DebitBalance,0)`

In the formula, DebitBalance is the name of another formula, which is defined as follows:

`=(TrialDebits-TrialCredits)+(AdjustDebits-AdjustCredits)`

The formula for DebitBalance uses four named ranges (the cell references are to the worksheet shown in Figure 5.4):

• TrialDebits refers to B4:B18, the debit amounts in the trial balance.
• TrialCredits refers to C4:C18, the credit amounts in the trial balance.

The steps involved in defining the name DebitBalance are listed here:

1. Choose Insert, Name, Define. (In Excel 12, select the Formulas tab on the Ribbon and then click Name a Range in the Named Cells group.)
2. In the Names in Workbook box, type DebitBalance. (In Excel 12, it's the Name box.)
3. In the Refers To box, type this formula:
`=(TrialDebits-TrialCredits)+(AdjustDebits-AdjustCredits)`
4. Choose OK (or, if you're ready to define more names, choose Add).

For each of the fourth through eighteenth rows, the formula subtracts any amounts in the Credit column of the trial balance from any amounts in its Debit column. It then adds any adjusting debits and subtracts any adjusting credits. The result is the debit balance—thus the name of the formula.

As noted earlier in this section, DebitBalance is used in this array formula, entered in cells F4:F18 in Figure 5.4:

`=IF(DebitBalance>=0,DebitBalance,0)`

To illustrate the array formula's effect, consider cell F7, the adjusted trial balance for the Office Supplies account. The trial balance provides a debit amount of \$2,000 and no credit amount. The adjustments include no debit amount, but a credit amount of 166.67. So the named formula DebitBalance returns (2000-0) + (0-166.67), or 1,833.33. Because that value is greater than zero, the condition in the array formula in cell F7 is satisfied and returns the value 1,833.33.

Before you continue, you might want to be sure that you understand why no nonzero value appears in cells F9:F12.

Cells G4:G18 contain this array formula:

`=IF(CreditBalance>=0,CreditBalance,0)`

It's similar to the array formula in cells F4:F18. The sole difference is that it relies on a different named formula, CreditBalance. Using the procedure described earlier to define the DebitBalance formula, CreditBalance is defined as follows:

`=(TrialCredits-TrialDebits)+(AdjustCredits-AdjustDebits)`

Notice that the formula reverses the relationship between debits and credits used in DebitBalance. Its purpose is to combine trial and adjusting credits and debits so that if credits exceed debits, the formula returns a positive value. Then the array formula in G4:G18 returns values greater than or equal to zero only.

You might have noticed that these various formulas call for Excel to return a zero amount if a condition is not met. For example, the array formulas in F4:F18 and G4:G18 return a DebitBalance or CreditBalance amount when the balances equal or exceed zero, but a zero when they do not. The figure does not show these zero amounts, primarily to avoid visual clutter.

#### Structuring the Income Statement

With the cells in the Adjusted Trial Balance section completed, their revenue and expense information is carried over from the adjusted trial balance to the income statement and balance sheet (see Figure 5.5).

For the income statement, information on revenues and expenses is needed. The debit amounts are obtained by means of this formula:

`=IF(OR(AccountType="Revenues",AccountType="Expenses"),AdjustedDebits,0)`

AdjustedDebits, a workbook-level name, refers to the range that occupies cells F4:F18 in Figure 5.4. The range named AccountType occupies cells A4:A18 in Figure 5.5 and contains values that identify the type of account: revenue, expense, asset, or liability. By testing the values in AccountType, the formula can limit the amounts brought into the income statement to debits from Revenue or Expense accounts. Similarly, the credit amounts in the income statement are obtained by means of this formula in cells D4:D18:

`=IF(OR(AccountType="Revenues",AccountType="Expenses"),AdjustedCredits,0)`

Two additional amounts are needed to bring the income statement into balance: Ending Inventory and Net Income. Both these amounts are as of the statement date, January 31, 2007.

Ending Inventory is included as a credit amount of \$1,500, accounting for the unused remainder of the initial \$2,000 purchase.

Net Income is included in the Debit column, as the difference between the income statement's total Credits and its total Debits. The figure returned is \$4,783.17. As a check, notice that this is the result of the following:

• \$10,000.00 (Sales)-\$500.00 (COGS)-\$4,716.83 (Total Operating Expenses) = \$4,783.17

Including the Ending Inventory and Net Income brings the income statement into balance.

#### Structuring the Balance Sheet

Finally, the Debit and Credit columns of the balance sheet are obtained by formulas similar to those used for the income statement. This formula returns the debit amounts:

`=IF(OR(AccountType="Assets",AccountType="Liabilities"),AdjustedDebits,0)`

This formula returns the credit amounts:

`=IF(OR(AccountType="Assets",AccountType="Liabilities"),AdjustedCredits,0)`

Now Marble is in a position to calculate her working capital. Recall that this is defined as the difference between current assets and current liabilities. As of January 31, 2007, Marble's current assets are as follows:

• \$2,554.00 (cash)
• \$8,000.00 (accounts receivable)
• \$1,833.33 (office supplies, a prepaid expense)
• \$1,500.00 (ending inventory)

This adds up to \$13,887.33. Marble's current liabilities include only the \$2,000 note payable. Her working capital, therefore, is \$11,887.33, or \$13,887.33-\$2,000.

### Determining Changes in Working Capital

From a management perspective, it is important to understand how the amount of working capital changes over time. A comparative balance sheet is useful for this purpose. Figure 5.6 shows an income statement and a balance sheet for Marble Designs, in a somewhat different format than the one used in Figure 5.5.

The balance sheet section of Figure 5.6 lists Marble Designs' assets and liabilities. On January 1, 2007, when the firm began operation, it had no liabilities and \$9,000 in assets, consisting entirely of cash. Its working capital was, therefore, \$9,000. By the end of the month, the sum of the firm's assets was \$15,783 (as, of course, was the sum of its liabilities and owner's equity).

Not all these assets and liabilities are current, however. The current assets as of January 31, 2007, include cash, accounts receivable, inventory, and office supplies, which total \$13,887.33. The sole current liability is the note for the purchase of the beginning inventory, for \$2,000. The difference between the total of the current assets and the current liability is \$11,887.33, which agrees with the amount arrived at using the information shown in Figure 5.5.

So the change in working capital from the beginning to the end of the month is \$11,887.33 -\$9,000.00, or \$2,887.33.

You can calculate changes in working capital in several different ways. It's useful to look at them both to understand working capital from different perspectives and because you might want to calculate working capital in the context of different worksheet layouts.

#### Using Transactions to Calculate Changes to Working Capital

Figure 5.7 shows one way to do so, using information about individual transactions.

During January, the sale of products for more than they cost increased working capital: The gross profit was \$9,500. Placing \$2,000 worth of materials in inventory also increased working capital. These are both current assets, totaling \$11,500.

Acquiring a loan of \$2,000, the note payable that was used to purchase the inventory, decreased working capital. Working capital was also decreased by the payment of cash for the computer, various operating expenses, and the use of office supplies. These are all current liabilities, totaling \$8,612.67.

The net effect of the increase of \$11,500 in working capital and the decrease of \$8,612.67 in working capital is \$2,887.33. During the month of January, Marble Designs increased its working capital by this amount. Note that this is the same figure as was determined by the analysis in Figure 5.6 (\$11,887.33-\$9,000).

Normally, you would not determine changes in working capital by examining each transaction that occurred in a given period: There are quicker ways. Also, many transactions occur that affect current assets and current liabilities to the same degree and, therefore, have no net effect on working capital.

For example, when you collect payment for a product or service, this transaction has no effect on working capital. Doing so merely increases one current asset (cash) and decreases another current asset (accounts receivable) by identical amounts. When you write a check for an account payable, you decrease both a current asset account (cash) and a current liability account (accounts payable) by equal amounts. There is no net effect on the amount of working capital.

Therefore, the example shown in Figure 5.7 could have ignored the transaction involved when Marble Designs acquired \$2,000 in inventory. This transaction increased a current asset, inventory, and increased a current liability, notes payable, by identical amounts.

In general, transactions that involve only current asset or current liability accounts do have an effect on individual working capital accounts, but they do not have a net effect on the amount of working capital.

#### Examining Changes to Current Assets and Liabilities

Again, working capital is the difference between total current assets and total current liabilities. Similarly, the change in working capital is the combined effect of changes in current liabilities and in current assets. Figure 5.8 shows how you can quickly determine the change in working capital by examining changes to the accounts that make up the current assets and liabilities.

As it happens, the only current asset that declines during January is the Cash account. All other current asset accounts increase in value. One current liability account, Notes Payable, increases by \$2,000.00. The change in working capital can then be determined by subtracting the net increase in current liabilities from the net increase in current assets: \$4,887.33-\$2,000.00 = \$2,887.33.

#### Checking the Sources and Uses of Working Capital

Another means of determining changes in working capital is to compare its sources with its uses. Recall that transactions involving only current asset and current liability accounts have no net effect on working capital. The same is true of transactions that involve only noncurrent accounts. For example, when Marble records \$54.17 as the month's depreciation on the computer, she adds \$54.17 to a noncurrent account, with no net effect on working capital.

However, a transaction that involves a current account and a noncurrent account does affect the amount of working capital. Suppose that Marble invested an additional \$1,000 in her business, recording it in both the capital account (noncurrent) and the cash account (a current asset). This transaction would increase working capital by \$1,000.

Therefore, when determining changes to working capital, it can be convenient to limit the analysis to transactions that affect only current accounts and noncurrent accounts. Figure 5.9 gives an example.

The sources of working capital, in this case, consist solely of net income. What is depreciation doing there? Recall that depreciation is a noncash expense that, for the purposes of the income statement, acts as an offset to gross profit in the calculation of net income. But no funds change hands as a result of recording depreciation. Therefore, when you use net income to calculate your sources of working capital, it is necessary to add depreciation back—in other words, to reverse the effect of subtracting it in the calculation of net income.

The cash portion of net income, a noncurrent account, is deposited in the cash account, a current asset. Therefore, in combination with the act of adding depreciation back, it can be used to calculate the change in working capital.

The sole use of working capital shown in Figure 5.9 is to purchase the computer. It is also used to determine the change in working capital. Funds from the cash account, a current asset, were used to purchase Equipment, a noncurrent asset.

The difference between the total sources of working capital and the total uses of working capital is, once again, \$2,887.33, just as was found by means of the analyses in Figures 5.65.8.