Lesson Objective: To construct the working capital schedule that projects the operating assets and liabilities (AR, Inventory, AP) based on operational efficiency metrics (DSO, DIO, DPO), and to correctly phase these cash flows into the operating section of the Cash Flow Statement.

In-Depth Notes:

1. The Working Capital Roll-Forward Mechanics:
Working capital is the lifeblood of the operating cash flow. The balance sheet items (AR, Inventory, AP) must be forecast using their respective efficiency ratios, not by simple percentage of revenue growth.

• Accounts Receivable (AR) Calculation: Forecast AR = (DSO / 365) * Forecast Revenue.

  • Critical Check: If DSO remains constant but Revenue doubles, AR doubles proportionally. However, if the company is offering extended payment terms to attract customers, the modeler must specifically increase the DSO assumption.

• Inventory Calculation: Forecast Inventory = (DIO / 365) * Forecast COGS.

  • European Consideration: Under IAS 2, inventory must be carried at the lower of cost and net realizable value (NRV). If the model projects a decline in the selling price of a product, it must trigger an inventory write-down, which flows through COGS.

• Accounts Payable Calculation: Forecast AP = (DPO / 365) * Forecast COGS.

  • Business Strategy: If the company is facing a liquidity crunch, it may stretch its DPO (pay suppliers later). This generates a temporary boost to cash flow but can damage supplier relationships. The model must flag any DPO extension beyond 90 days.

2. The Balance Sheet Balancing Logic (The “Uses” and “Sources” of Cash):
The working capital schedule directly feeds into the Cash Flow Statement via the “Changes in Working Capital” section.

• Change in AR: ΔAR = Forecast AR - Prior Period AR. If ΔAR is positive, it means the company has made more sales on credit than it has collected cash; this is a use of cash (negative operating cash flow). If ΔAR is negative, the company is collecting cash faster than making credit sales; this is a source of cash (positive operating cash flow).

• Change in Inventory: ΔInventory = Forecast Inventory - Prior Period Inventory. A positive ΔInventory means the company is purchasing/stockpiling inventory; this is a use of cash.

• Change in AP: ΔAP = Forecast AP - Prior Period AP. A positive ΔAP means the company is delaying payments to suppliers; this is a source of cash.

• The “Working Capital Investment” Check: The model calculates Working Capital Investment = ΔAR + ΔInventory - ΔAP. This represents the total cash tied up in day-to-day operations. If this investment grows faster than revenue, it indicates the company’s cash conversion cycle is deteriorating, a major red flag.

3. Phasing and Seasonality (Quarterly and Monthly Models):
In sub-annual models, working capital must be phased to reflect seasonal peaks (e.g., retailers building inventory before the Christmas season).

• The “Sales to Working Capital” Relationship: The model uses a seasonal index derived from historical data. For example, if the 4th quarter historically accounts for 40% of annual revenue, the model phases 40% of the revenue forecast into Q4, which then drives a corresponding 40% of the AR balance in that quarter.

• The Cash Flow “Staircase” Effect: The phasing of working capital creates a “staircase” pattern in the cash flow statement, where cash is consumed during periods of high production and inventory build-up (e.g., Q1 and Q2) and is generated during periods of high sales and collection (Q3 and Q4). Failure to incorporate this phasing is a common error that leads to false liquidity warnings.