Home > Articles > Business & Management > General Business

📄 Contents

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

This chapter is from the book

Business Analysis with Microsoft Excel, 3rd EditionBusiness Analysis with Microsoft Excel, 3rd Edition

Learn More Buy

This chapter is from the book

This chapter is from the book

Business Analysis with Microsoft Excel, 3rd EditionBusiness Analysis with Microsoft Excel, 3rd Edition

Learn More Buy

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.

Case Study: Marble Designs' Working Capital

To complicate the activities of Marble Designs in January 2007 with one additional transaction, assume that the company purchases $2,000 worth of inventory on January 1. The inventory is purchased on credit, and Marble Designs uses $500 of the inventory during January to deliver products to customers.

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.

Figure 5.3

Figure 5.3 The cash transactions undertaken by Marble Designs during January 2007 affect its working capital but do not fully define it.

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.

Figure 5.4

Figure 5.4 Marble Designs' trial balance, adjustments, and adjusted trial balance begin to focus on the company's working capital.

TIP

Figure 5.4 shows its six totals with double-underlines. Excel provides a special format for double-underlined totals as well as for single-underlined subtotals. Select the cell or cells whose values you want to underline and choose Format, Cells. (In Excel 12, select the Home tab on the Ribbon, click the Format drop-down in the Cells group, and choose Format Cells from the drop-down menu.) Click the Font tab and select Single Accounting or Double Accounting from the Underline combo box. Choose OK to return to the worksheet.

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.

NOTE

Couldn't you just divide $1,950 by 36 to get the $54.17 straight-line monthly depreciation? Sure. But by using the SLN() function, you make it explicit both that you are calculating depreciation and that you assume 36 months of useful life and a zero salvage value. A year from now, reviewing your income statement, you might appreciate that your calculation was unambiguous.

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.
  • AdjustDebits refers to D4:D18, the debit amounts in the adjustments.
  • AdjustCredits refers to E4:E18, the credit amounts in the adjustments.

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.

TIP

You can suppress the display of any zero amounts in a worksheet by selecting Options from the Tools menu, choosing the View tab, and clearing the Zero Values check box. (In Excel 12, click the Office button and then click Excel Options. In the Display for This Worksheet area, clear the Show a Zero in Cells That Have Zero Value check box.)

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

Figure 5.5

Figure 5.5 Marble Designs' income statement and balance sheet enable you to determine net income.

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)

The AdjustedCredits range occupies cells G4:G18 in Figure 5.4. Like AdjustedDebits, AdjustedCredits is a workbook-level name.

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.

NOTE

Recall that COGS equals beginning inventory (here, $0) plus purchases ($2,000) minus ending inventory ($1,500).

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.

NOTE

Notice that the computer asset is not involved in determining working capital. This is because the computer is not a current asset, one that can quickly be converted to cash in the normal course of business operations.

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.

Figure 5.6

Figure 5.6 An income statement and comparative balance sheet can clarify how your financial position changes over time.

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.

Figure 5.7

Figure 5.7 A laborious way to determine changes in working capital is to examine 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.

Figure 5.8

Figure 5.8 You can determine changes in working capital by comparing changes in current assets and in current 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.

Figure 5.9

Figure 5.9 Another way to determine changes in working capital is to examine current and noncurrent accounts.

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.6–5.8.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020