• Forecasting Working Capital

    Working capital consists of the current assets and liabilities that the company requires to operate on a daily basis. These accounts include:

    • Accounts receivable
    • Inventory
    • Marketable securities
    • Other current assets
    • Accounts payable
    • Accrued expenses
    • Other current liabilities

    Unless there is a material change in a company’s operations, these accounts are maintained at a ratio to the appropriate drivers. A company will want to minimize its accounts receivables (so that it can receive cash sooner) and inventory (so that the chances of being stuck with obsolete or spoiled inventory are reduced), whereas it is in its best interests to keep accounts payable high (delaying payments increases the cash that a company can work with).

    Concept Checker

    What are the appropriate drivers are for each of the accounts?

    • Accounts receivable
    • Inventory
    • Marketable securities
    • Other current assets
    • Accounts payable
    • Accrued expenses
    • Other current liabilities

    Answer

    Account receivable – this what customers owe the company for services and goods so it will be linked to revenues

    Inventory – a company wants to minimize inventory while ensuring that there are is enough stock to meet demand. Cost of goods sold is the appropriate driver here.

    Marketable securities – you can use some discretion here when determine what the drivers will be. One argument could be made that it will simply scale with revenues. You could also use your discretion and see what the trends are and straight-line it if appropriate.

    Other current assets / Other current liabilities – this will depend on what is in these accounts. Read the footnotes for additional information. In absence of any other information, choose a metric that you think is reasonable.

    Accounts payable– this account is used to pay for inventory so you may choose to forecast it based on inventory or on the driver of inventory, cogs.

    Once you have completed forecasting the balance sheet items, link it back to the cash flow statement under the appropriate accounts.

    Example

    A company has the following select balance sheet items in year 1 and 2. Calculate the effect on cash flow.

    Year 1 Year 2
    Accounts Receivable $100 $200
    Inventory $400 $200
    Accounts Payable $500 $800

    Remember that if assets increase, then there is an outflow of cash. Because accounts receivable is higher, it means that customers aren’t paying as quickly so in year 2, the cash impact will be ($100). On the other hand, the company was able to take some of its inventory and convert it into cash so the impact there was +$200. Finally, accounts payable went up so the company had to outlay less cash to the tune of $300.

    The overall net impact on cash flow then is ($100) + $200 + $300 = +$500

    Example: Forecasting Cisco’s Working Capital

    Step 1: Create a new sheet to forecast the working capital accounts. The accounts we will be setting up are:

    • Investments
    • Accounts Receivables
    • Inventories
    • Other current assets
    • Accounts Payable
    • Other current liabilities

    Step 2: Link the accounts from the balance sheet. Remember that figures linked from other sheets are colored green.

    Step 3:  Add the working capital account drivers. For now, omit investments as it will be treated differently.

    • Accounts receivable: This is driven by how quickly a company converts its revenues into cash. What we’re going to look at is the ratio of A/R to Revenues.
    • Inventories: In this example, we have kept the COGS the same for the next few years so the number for inventory is the same whether you use revenues or COGS as the driver. Since we want to add flexibility to the model, we are going to use COGS as the driver. This way, if the margins change, so too will the working capital requirements.
    • Accounts Payable: This account is usually used to pay for suppliers so the same drivers as inventory would likely apply here.
    • Other Assets: The breakdown of the other assets is found on pg 53 of the annual report. The breakdown consists of things that would presumably be linked to revenues.
    • Other current liabilities: Cisco does not get into what this account consists of so we will be using revenues as the drivers.

    To summarize, we are going to use the following drivers:

    • Net income
    • Net income growth
    • AR/Net income
    • COGS growth

    Step 4: Link the drivers from the income statement. We will forecast the AR/Revenue ratio at 10% based on historical figures.

    Step 5: Forecast all the accounts except for investments.

    Step 6:  We’re now going to forecast the investments account. We’re going to have to dig a little to find out a little more about what is in this account. Page 55 of the FY2010 AR gives a full breakdown of the securities which we see is mostly comprised of fixed-income securities.

    To set up the spreadsheet, we’re going to begin each year with the previous year’s balance.

    Step 7: Link the purchase, sales and maturities of investments from the statement of cash flows. Be sure to reverse the signs.

    Step 8: The ending balances do not match the starting balances for the next year. To reconcile these differences, the ”Other” line item will be used.

    Step 9: Add the formulas for the starting and ending balances for the forecast. Note that we are using subtotals in the formulas to avoid making errors should we need to add lines.

    Step 10: The full breakdown of the maturities of the securities can be found on page 57 of the FY2010 AR.

    Enter those into the forecast.

    Step 11: Forecasting the sale and purchases of new securities will require a bit of analysis. Looking at the historical trends, we can see that the company sold an average of $18,620 in investments per year. On the bottom line, the ending investments balance increased by between $5,997 to $8,239.

    We’re going to straight-line the sale of investments at $18,620 for the next three years. To determine the purchase of the investments, we are going to conservatively assume that they will aim to increase their investments by approximately $6,000 each year.

    Step 12: Link the ending balance of the investments.

    Step 13: Link the asset balances back to the balance sheet.

    Step 14and 15: Link the changes in working capital to the statement of cash flows.

    About

    Leave a Reply

    Your email address will not be published. Required fields are marked *