Objectives

Section Learning Objectives

2.1

Working Capital Forecasting Methods

📊 Percentage of Sales Method

Simple approach based on historical relationships

A/R = Revenue × Historical A/R %
Inventory = Revenue × Historical Inv %
Pros: Simple, quick
Cons: Less accurate, ignores efficiency changes
⭐ RECOMMENDED

📅 Days-Based Method

More accurate using DSO, DIO, DPO assumptions

A/R = (DSO / 365) × Revenue
Inventory = (DIO / 365) × COGS
A/P = (DPO / 365) × COGS
Pros: More accurate, scenario-friendly
Cons: Requires more assumptions
💡 Best Practice

Use the Days-Based Method for financial modeling.
It allows you to model operational improvements (e.g., reducing DSO from 60 to 45 days) and is more intuitive for scenario analysis.

2.2

Days-Based Forecasting: Step-by-Step

Step 1: Analyze Historical Trends

Before forecasting, analyze the historical DSO, DIO, DPO trends:

Metric 2022 2023 2024 2025 Trend
DSO 115.6 109.9 104.8 101.5 ↓ Improving
DIO 196.3 190.1 184.7 179.7 ↓ Improving
DPO 66.7 63.8 60.8 58.2 ↓ Declining

Step 2: Set Forecast Assumptions

Based on historical trends and management guidance:

Metric 2025A 2026E 2027E 2028E Rationale
DSO (days) 101.5 100 98 96 Improving 2 days/year
DIO (days) 179.7 175 170 165 Improving 5 days/year
DPO (days) 58.2 58 58 58 Stable (maintain relationships)

Step 3: Forecast Revenue and COGS

Revenuet = Revenuet-1 × (1 + Growth Rate)
COGSt = Revenuet × COGS %
Item 2025A 2026E 2027E 2028E
Revenue Growth 12.5% 10.0% 10.0% 10.0%
Revenue 289,456 318,402 350,242 385,266
COGS % 62.0% 62.0% 62.0% 62.0%
COGS 179,463 197,409 217,150 238,865

Step 4: Calculate Working Capital Components

Excel Formulas:
A/R = (DSO / 365) * Revenue
Inventory = (DIO / 365) * COGS
A/P = (DPO / 365) * COGS
Item 2025A 2026E 2027E 2028E
DSO (days) 101.5 100 98 96
A/R 80,394 87,233 94,067 101,384
DIO (days) 179.7 175 170 165
Inventory 88,431 94,562 101,223 107,867
DPO (days) 58.2 58 58 58
A/P 28,630 31,527 34,454 38,003
2.3

Calculating Change in NWC (Cash Flow Impact)

⚠️ Critical Concept

Change in NWC is a cash flow item!
When NWC increases, cash is TIED UP → Cash OUTFLOW
When NWC decreases, cash is RELEASED → Cash INFLOW

Formula: Change in NWC = Beginning NWC - Ending NWC

NWC Calculation Example

Item 2025A 2026E 2027E 2028E
A/R 80,394 87,233 94,067 101,384
+ Inventory 88,431 94,562 101,223 107,867
- A/P (28,630) (31,527) (34,454) (38,003)
= NWC 140,195 150,268 160,836 171,848
Change in NWC - (10,073) (10,568) (11,012)
Excel Formula:
NWC = A/R + Inventory - A/P
Change in NWC = Prior Year NWC - Current Year NWC

Example 2026E:
= 140,195 - 150,268 = (10,073) → Cash OUTFLOW
💡 Why Parentheses = Outflow?

When NWC increases (140,195 → 150,268), the company has MORE money tied up in receivables and inventory. This means LESS cash is available.

Think of it this way: If you lend ₹10,000 to a friend, your "receivable" increases, but your cash decreases by ₹10,000.

Excel Lab

Hands-On: Build NWC Forecast

🎯 Exercise: Build a 3-Year NWC Forecast

Task 1: Set Up the Model Structure

  1. Create a new Excel sheet named "NWC Forecast"
  2. Create an Assumptions section at the top
  3. Create a Forecast section below
  4. Use blue font for inputs, black for formulas

Task 2: Build Assumptions Area

Revenue Growth Rate 10% Input (blue)
COGS % of Revenue 62% Input (blue)
DSO (days) 100 Input (blue)
DIO (days) 175 Input (blue)
DPO (days) 58 Input (blue)

Task 3: Build Forecast Formulas

Revenue: =Prior_Year_Revenue*(1+$B$1)
COGS: =Revenue*$B$2
A/R: =($B$3/365)*Revenue
Inventory: =($B$4/365)*COGS
A/P: =($B$5/365)*COGS
NWC: =AR+Inventory-AP
Change in NWC: =Prior_NWC-Current_NWC

Task 4: Verify Your Results

Your 2026E Change in NWC should be approximately (10,073)

If you get a positive number, you may have the formula backwards!

Summary

Key Takeaways

  • Days-Based Method is preferred for NWC forecasting
  • DSO/DIO use Revenue/COGS correctly - DSO uses Revenue, DIO/DPO use COGS
  • Change in NWC = Beginning - Ending (not the reverse!)
  • Increasing NWC = Cash Outflow (parentheses in Excel)
← Previous: NWC Concepts Next: CAPEX & Depreciation →