Learning Objectives

What You'll Learn

By the end of this module, you will be able to:

Section 1

The Problem: Why Quarterly Data Matters

Understanding data availability gaps in financial modeling

πŸ’­
Think About It

You're building a DCF model for an Indian retail company. You have quarterly income statements but only annual balance sheets and cash flow statements. How do you build a quarterly model?

In India, listed companies must file quarterly results (Regulation 33, SEBI LODR), but quarterly balance sheets and cash flow statements are often incomplete or unavailable for many companies.

πŸ“Š The Data Availability Reality

βœ…
Income Statement

Quarterly available

Flow Variable
⚠️
Balance Sheet

Annual / Half-yearly only

Stock Variable
⚠️
Cash Flow Statement

Annual / Half-yearly only

Flow Variable
🎯 Why This Matters

Quarterly models are essential for:
1. Rolling forecasts β€” More granular predictions
2. DCF with quarterly periods β€” Better near-term accuracy
3. LBO/PE models β€” Need quarterly debt service coverage
4. Seasonality analysis β€” Capture intra-year patterns
5. Quarterly covenant testing β€” Bank loan compliance

Section 2

Stock vs. Flow Variables: The Key Distinction

Why the nature of each line item determines the conversion method

🏦 Stock Variables (Balance Sheet)

Measured at a specific point in time (e.g., "as of March 31, 2025")

ItemNature
CashStock
Accounts ReceivableStock
InventoryStock
PP&E (Net)Stock
Debt OutstandingStock
Retained EarningsStock
Balance Sheet₁ = Balance Sheetβ‚€ + Changes

πŸ“Š Flow Variables (Income & Cash Flow)

Measured over a period (e.g., "Q1 FY2025" or "FY2025")

ItemNature
RevenueFlow
COGSFlow
Net IncomeFlow
DepreciationFlow
Cash from OperationsFlow
Capital ExpenditureFlow
Annual Flow = Q1 + Q2 + Q3 + Q4
⚠️ Critical Insight: The Balance Sheet is a STOCK variable β€” you cannot simply divide annual numbers by 4 to get quarterly values. A balance sheet on March 31 is a snapshot on that date, not one-fourth of the year. However, you CAN derive quarterly balance sheet snapshots using the quarterly income statement and working capital ratios.
Section 3

Converting Annual Balance Sheet β†’ Quarterly

Three methods with step-by-step worked examples

1 Working Capital Ratio Method (Recommended)

Use quarterly income statement drivers to estimate quarterly balance sheet items via activity ratios (DSO, DIO, DPO).

Quarterly A/R = (Quarterly Revenue Γ— DSO) Γ· Days in Quarter
Quarterly Inventory = (Quarterly COGS Γ— DIO) Γ· Days in Quarter
Quarterly A/P = (Quarterly COGS Γ— DPO) Γ· Days in Quarter
πŸ“‹ Complete List: Which BS Items Use Ratio Method?
Balance Sheet Item Ratio Method? Driver Ratio Alternative Method
Accounts Receivable βœ… Yes Revenue DSO β€”
Inventory βœ… Yes COGS DIO β€”
Accounts Payable βœ… Yes COGS DPO β€”
Prepaid Expenses ⚠️ Sometimes SG&A/Revenue % of driver Linear interp.
Accrued Expenses ⚠️ Sometimes SG&A/COGS % of driver Linear interp.
Deferred Revenue ⚠️ Sometimes Revenue % of Revenue Linear interp.
Cash ❌ No β€” β€” Plug
Net PP&E ❌ No β€” β€” Roll-forward/Linear
Intangibles ❌ No β€” β€” Linear
Debt (All) ❌ No β€” β€” Debt schedule
Retained Earnings ❌ No β€” β€” Roll-forward
Common Stock ❌ No β€” β€” Constant

3.1 Calculating DSO, DIO, DPO from Available Data

When ratios aren't directly provided, calculate them from annual financials:

DSO = (Avg A/R Γ· Annual Revenue) Γ— 365
DIO = (Avg Inventory Γ· Annual COGS) Γ— 365
DPO = (Avg A/P Γ· Annual COGS) Γ— 365
Avg = (Beginning + Ending Balance Sheet Item) Γ· 2
Example: Retail from Practice Data (β‚Ή Crores)
RatioFormulaBeginningEndingAvgAnnual DriverCalculated Ratio
DSO (Avg A/R Γ· Rev) Γ— 365 530 490 510 15,000 12.4 days
DIO (Avg Inv Γ· COGS) Γ— 365 1,800 1,500 1,650 10,500 57.4 days
DPO (Avg A/P Γ· COGS) Γ— 365 600 700 650 10,500 22.6 days
⚠️ Note: Practice data shows DSO=55 (vs calculated 12.4). This is intentional β€” use **industry benchmarks** when company-specific ratios seem unrealistic (e.g., retail DSO typically 30-60 days).

3.2 When Ratios Aren't Directly Available

Follow this hierarchy:

  1. Company Historical Average β€” Use 3-5 year avg from past annual reports
  2. Industry Benchmarks β€” Retail: DSO 40-60, DIO 50-70; IT: DSO 60-90, DIO 5-15
  3. Peer Group Median β€” Calculate from 5-10 comparable companies
  4. Sensitivity Analysis β€” Test Β±20% on ratios, see impact on cash flow
Sensitivity: DSO Β±10 days β†’ Cash impact = Β±(Rev/365 Γ— 10)
πŸ’‘ Pro Tip: Document your ratio source and perform sensitivity. In interviews, be ready to justify: "I used industry median DSO of 55 days for retail, sensitivity-tested Β±10 days."
Step-by-Step Process (Retail Example β€” β‚Ή Crores)
StepActionQ1Q2Q3Q4
1Quarterly Revenue (Given) 3,2003,5005,8002,500
2Quarterly COGS (Given) 2,2402,4504,0601,750
3Days in Quarter 90909090
4A/R = Revenue Γ— DSO Γ· 90 (DSO=55) 1,9562,1393,5441,528
5Inventory = COGS Γ— DIO Γ· 90 (DIO=58) 1,4431,5792,6161,128
6A/P = COGS Γ— DPO Γ· 90 (DPO=24) 5976531,083467
-- Q1 A/R Calculation --
A/R (Q1) = Revenue (Q1) Γ— DSO Γ· Days in Q
A/R (Q1) = 3,200 Γ— 55 Γ· 90
A/R (Q1) = β‚Ή1,956 Crores

-- Q1 Inventory Calculation --
Inventory (Q1) = COGS (Q1) Γ— DIO Γ· Days in Q
Inventory (Q1) = 2,240 Γ— 58 Γ· 90
Inventory (Q1) = β‚Ή1,443 Crores

-- Q1 A/P Calculation --
A/P (Q1) = COGS (Q1) Γ— DPO Γ· Days in Q
A/P (Q1) = 2,240 Γ— 24 Γ· 90
A/P (Q1) = β‚Ή597 Crores
πŸ’‘ Key Insight

Notice how A/R, Inventory, and A/P spike in Q3 for the retail company (β‚Ή3,544 A/R vs β‚Ή1,528 in Q4). This reflects the festive/Diwali season in India where sales β€” and thus working capital β€” are much higher. The Working Capital Ratio Method naturally captures this seasonality because it uses quarterly revenue/COGS as the driver.

πŸ“ Student Exercise: Build Quarterly Working Capital

Download the CSV below and calculate Q1–Q4 quarterly Accounts Receivable, Inventory, and Accounts Payable for RetailCo using DSO = 55, DIO = 58, and DPO = 24.

⬇️ Download Exercise CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute quarterly A/R, Inventory, and A/P using the ratio formulas.
  3. Compare Q3 and Q4 values to observe the effect of seasonality.
  4. Check whether the pattern is reasonable relative to revenue and COGS.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
A/R = Revenue Γ— DSO Γ· 90
Inventory = COGS Γ— DIO Γ· 90
A/P = COGS Γ— DPO Γ· 90
Step 2: Q1 Solution
A/R = 3,200 Γ— 55 Γ· 90 = 1,956
Inventory = 2,240 Γ— 58 Γ· 90 = 1,443
A/P = 2,240 Γ— 24 Γ· 90 = 597
Step 3: Q2 Solution
A/R = 3,500 Γ— 55 Γ· 90 = 2,139
Inventory = 2,450 Γ— 58 Γ· 90 = 1,579
A/P = 2,450 Γ— 24 Γ· 90 = 653
Step 4: Q3 Solution
A/R = 5,800 Γ— 55 Γ· 90 = 3,544
Inventory = 4,060 Γ— 58 Γ· 90 = 2,616
A/P = 4,060 Γ— 24 Γ· 90 = 1,083
Step 5: Q4 Solution
A/R = 2,500 Γ— 55 Γ· 90 = 1,528
Inventory = 1,750 Γ— 58 Γ· 90 = 1,128
A/P = 1,750 Γ— 24 Γ· 90 = 467

2 Linear Interpolation Method (For Non-Working Capital Items)

Use this method for non-working capital items that move gradually across the year, such as PP&E, intangibles, deferred assets, and long-term debt. It is most useful when the change is expected to be smooth rather than driven by quarterly revenue or COGS.

Interpolated Value at Quarter n = Beginning Value + (n Γ· 4) Γ— (Ending Value βˆ’ Beginning Value)
πŸ’‘ When to use: Use linear interpolation only when you know the beginning and ending values for the year and the line item changes gradually. Do not use it for items driven by operations, seasonality, or discrete transactions.
πŸ“‹ Applicability Checklist: Where Linear Interpolation Fits
Item Applicable? Condition / Notes Preferred Alternative
Net PP&E βœ… Yes Smooth movement due to capex and depreciation; no major asset sale/acquisition Linear interpolation or PP&E roll-forward
Intangible Assets βœ… Yes Amortization-driven decline or steady increase; no one-time impairment Linear interpolation
Deferred Charges / Other Non-Current Assets βœ… Yes Small, gradual changes with no discrete event Linear interpolation
Long-term Debt ⚠️ Sometimes Only if repayments are smooth and scheduled throughout the year Debt schedule
Prepaid Expenses ⚠️ Sometimes Use only if no better operating driver exists and balances change steadily Ratio method or linear interpolation
Retained Earnings ❌ No Must be driven by quarterly net income and dividends Roll-forward
Accounts Receivable ❌ No Driven by revenue timing, collection cycles, and seasonality DSO / ratio method
Inventory ❌ No Driven by COGS, stocking cycles, and seasonality DIO / ratio method
Accounts Payable ❌ No Driven by purchase timing and supplier credit cycle DPO / ratio method
Cash ❌ No Balancing item; derived after all other line items Cash plug
Example: Net PP&E (Retail β€” β‚Ή Crores)

Beginning Net PP&E = β‚Ή3,500  |  Ending Net PP&E = β‚Ή4,000

Quarter Interpolation Factor Formula Value
Q1 End 25% 3,500 + 25% Γ— (4,000 βˆ’ 3,500) 3,625
Q2 End 50% 3,500 + 50% Γ— (4,000 βˆ’ 3,500) 3,750
Q3 End 75% 3,500 + 75% Γ— (4,000 βˆ’ 3,500) 3,875
Q4 End 100% 3,500 + 100% Γ— (4,000 βˆ’ 3,500) 4,000
-- Q1 Net PP&E --
Net PP&E (Q1) = 3,500 + (1 Γ· 4) Γ— (4,000 βˆ’ 3,500)
Net PP&E (Q1) = 3,500 + 125 = β‚Ή3,625

-- Q2 Net PP&E --
Net PP&E (Q2) = 3,500 + (2 Γ· 4) Γ— (4,000 βˆ’ 3,500)
Net PP&E (Q2) = 3,500 + 250 = β‚Ή3,750

-- Q3 Net PP&E --
Net PP&E (Q3) = 3,500 + (3 Γ· 4) Γ— (4,000 βˆ’ 3,500)
Net PP&E (Q3) = 3,500 + 375 = β‚Ή3,875
πŸ“˜ Modeling Insight

Linear interpolation gives you a clean quarterly bridge between two annual snapshots. In practice, it works well for items like PP&E, intangibles, or long-term borrowings when there is no major acquisition, disposal, refinancing, or one-time event. If such events exist, override the interpolated line with the actual transaction timing.

⚠️ Limitation: Linear interpolation assumes smooth, even change. If a company makes a large acquisition in Q2, PP&E will jump suddenly. In that case, you need to adjust for the specific event. Always cross-check with CapEx and depreciation from the quarterly income statement.
πŸ“ Student Exercise: Linear Interpolation for Non-Working Capital Items

Download the CSV below and calculate the quarter-end values for Net PP&E, Intangible Assets, and Long-term Debt using linear interpolation. Then identify the item that should not be interpolated and explain why.

⬇️ Download Interpolation Exercise CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute quarter-end values using the interpolation formula.
  3. Check Q1–Q4 values for each item.
  4. Identify which line item should be excluded from interpolation and use a roll-forward instead.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
Interpolated Value = Beginning Value + (Quarter Γ· 4) Γ— (Ending Value βˆ’ Beginning Value)
Step 2: Net PP&E
Q1 = 3,500 + 25% Γ— (4,000 βˆ’ 3,500) = 3,625
Q2 = 3,500 + 50% Γ— (4,000 βˆ’ 3,500) = 3,750
Q3 = 3,500 + 75% Γ— (4,000 βˆ’ 3,500) = 3,875
Q4 = 3,500 + 100% Γ— (4,000 βˆ’ 3,500) = 4,000
Step 3: Intangible Assets
Q1 = 500 + 25% Γ— (450 βˆ’ 500) = 488
Q2 = 500 + 50% Γ— (450 βˆ’ 500) = 475
Q3 = 500 + 75% Γ— (450 βˆ’ 500) = 463
Q4 = 500 + 100% Γ— (450 βˆ’ 500) = 450
Step 4: Long-term Debt
Q1 = 1,500 + 25% Γ— (1,200 βˆ’ 1,500) = 1,425
Q2 = 1,500 + 50% Γ— (1,200 βˆ’ 1,500) = 1,350
Q3 = 1,500 + 75% Γ— (1,200 βˆ’ 1,500) = 1,275
Q4 = 1,500 + 100% Γ— (1,200 βˆ’ 1,500) = 1,200
Step 5: Retained Earnings Check
Retained Earnings should not be interpolated.
Use roll-forward instead: Beginning RE + Net Income βˆ’ Dividends

3 Retained Earnings Roll-Forward

Use this method for retained earnings because it accumulates quarterly net income and is reduced by dividends. It should not be interpolated or linked to revenue/COGS ratios.

RE (End of Qn) = RE (End of Qn-1) + Net Income (Qn) βˆ’ Dividends (Qn) + OCI / Other Equity Adjustments
πŸ“‹ Applicability Checklist: Where Retained Earnings Roll-Forward Fits
Item Applicable? Condition / Notes Preferred Alternative
Retained Earnings βœ… Yes Driven by quarterly net income and dividend timing; may also include OCI / equity adjustments Roll-forward
Share Premium / APIC ⚠️ Sometimes Roll forward only if there are new equity issuances or buybacks Equity schedule
Other Comprehensive Income ⚠️ Sometimes Use if separately disclosed; otherwise keep in an equity plug line Equity roll-forward
Common Stock ❌ No Usually constant unless new shares are issued or repurchased Constant / equity schedule
Revenue / COGS / A/R / Inventory ❌ No Operating and working-capital driven; not equity accumulation items Ratio method
πŸ’‘ When to use: Use the roll-forward only when you have beginning retained earnings and quarterly net income. Dividends should be deducted in the quarter they are declared or paid, depending on the reporting convention. Any OCI or other equity adjustments should be added separately if disclosed.
Example: Retained Earnings (Retail β€” β‚Ή Crores)

Beginning RE = β‚Ή2,930  |  Quarterly Net Income = β‚Ή210 / β‚Ή244 / β‚Ή502 / β‚Ή131  |  Dividends paid in Q4 = β‚Ή509

Component Q1 Q2 Q3 Q4
Beginning RE 2,930 3,140 3,384 3,886
+ Net Income 210 244 502 131
βˆ’ Dividends 0 0 0 (509)
= Ending RE 3,140 3,384 3,886 3,508
πŸ“˜ Cross-Check

Ending RE (Q4) = β‚Ή3,508. The annual balance sheet may show a different ending balance if OCI, prior-period adjustments, share-based compensation, or timing differences in dividend recognition exist. Always reconcile retained earnings with the equity section and the annual report disclosures before using it as a plug.

🧠 Teaching note: Retained earnings should be modeled using a roll-forward, not interpolation. This is one reason financial modeling requires item-by-item judgment rather than one universal formula.
πŸ“ Student Exercise: Retained Earnings Roll-Forward

Download the CSV below and calculate quarterly ending retained earnings using Beginning RE, quarterly Net Income, and Dividends. Then explain why retained earnings should not be interpolated.

⬇️ Download Retained Earnings Exercise CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute ending retained earnings for Q1–Q4.
  3. Check how dividends affect Q4 only.
  4. Explain why retained earnings must be modeled using a roll-forward instead of interpolation.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
Ending RE = Beginning RE + Net Income βˆ’ Dividends + OCI / Other Equity Adjustments
Step 2: Q1
2,930 + 210 βˆ’ 0 + 0 = 3,140
Step 3: Q2
3,140 + 244 βˆ’ 0 + 0 = 3,384
Step 4: Q3
3,384 + 502 βˆ’ 0 + 0 = 3,886
Step 5: Q4
3,886 + 131 βˆ’ 509 + 0 = 3,508
πŸ’‘ Why not interpolate? Retained earnings accumulates net income and is reduced by dividends, so it changes due to earnings timing and payout policy. Interpolation would ignore those accounting mechanics and can produce misleading quarter-end balances.
πŸ“‹ Balance Sheet Coverage: Item β†’ Method Mapping
Balance Sheet Item Method Condition / When to Use Notes
Accounts Receivable Ratio method Use when revenue and DSO drive collections Quarterly revenue-based
Inventory Ratio method Use when COGS and DIO drive stock build Captures seasonality
Accounts Payable Ratio method Use when COGS and DPO drive supplier credit Usually tied to purchases/COGS
Prepaid Expenses Linear interpolation / ratio Use when balances change gradually and no direct operating driver exists Business-specific
Accrued Expenses Ratio / linear Use when accruals follow SG&A or COGS steadily Can also be scheduled if known
Deferred Revenue Ratio / schedule Use when revenue is received upfront or recognized later Often linked to sales or subscriptions
Other Current Assets Linear / specific driver Use when the balance is small and changes smoothly May require line-item judgment
Net PP&E Linear interpolation / roll-forward Use when capex and depreciation are steady; roll-forward if detailed capex is available Switch to roll-forward for major acquisitions or asset sales
Intangible Assets Linear interpolation Use when amortization or gradual changes dominate Avoid if impairment or acquisition occurs
Short-term Debt Debt schedule / linear Use debt schedule if repayments are known; otherwise interpolate only if smooth Prefer explicit repayment schedule
Long-term Debt Debt schedule / linear Use if refinancing and repayment schedule are available; interpolate only if smooth Do not use for lumpy borrowings
Common Stock Constant / equity schedule Use constant unless new shares are issued or repurchased Usually unchanged
Other Equity / OCI / APIC Equity roll-forward / schedule Use when separately disclosed or when equity transactions occur May sit in plug if not detailed
Retained Earnings Roll-forward Use beginning RE + quarterly net income βˆ’ dividends Β± OCI / adjustments Never interpolate
Cash Plug Calculated after all other balance sheet items Balances the sheet
βœ… Coverage Note: Every balance sheet line item shown in this lecture is now mapped to one of the supported quarterly conversion methods: ratio, interpolation, roll-forward, schedule/constant, or plug.

πŸ“‹ Complete Quarterly Balance Sheet β€” Retail Example (β‚Ή Crores)

Combining all three methods to build the full quarterly balance sheet

Balance Sheet Item Beginning Q1 End Q2 End Q3 End Q4 End Method
ASSETS
  Cash (Plug)8007265391,0201,200Plug
  Accounts Receivable5301,9562,1393,5441,528Ratio
  Inventory1,8001,4431,5792,6161,128Ratio
  Other Current Assets200213225238250Linear
Total Current Assets3,3304,3384,4827,4184,106
  Net PP&E3,5003,6253,7503,8754,000Linear
  Intangible Assets500488475463450Linear
Total Assets7,3308,4518,70711,7568,556
LIABILITIES & EQUITY
  Accounts Payable6005976531,083467Ratio
  Accrued Expenses300313325338350Linear
  Short-term Debt500475450425400Linear
Total Current Liabilities1,4001,3851,4281,8461,217
  Long-term Debt1,5001,4251,3501,2751,200Linear
Total Liabilities2,9002,8102,7783,1212,417
  Common Stock1,5001,5001,5001,5001,500Constant
  Retained Earnings2,9303,1403,3843,8863,508Roll-Fwd
  Other Equity (Plug)01,0011,0453,2491,131Plug
Total Equity4,4305,6415,9298,6356,139
Total L&E7,3308,4518,70711,7568,556
βœ… Balance Check: Total Assets = Total L&E for each quarter. The "Cash" line is the balancing plug β€” after computing all other items from the quarterly IS, cash fills the gap to make the balance sheet balance. This is the standard approach in financial modeling.
Section 4

Converting Annual Cash Flow β†’ Quarterly

Build quarterly cash flow statements using the quarterly income statement

1 Build-Up Method (Recommended)

Construct each quarterly cash flow line from the quarterly income statement and the quarterly balance sheet changes you just derived.

Quarterly CFO = Quarterly Net Income + Quarterly Depreciation + Quarterly Working Capital Changes
Quarterly Working Capital Change = (B/S Item This Quarter End) βˆ’ (B/S Item Last Quarter End)
Worked Example: Q1 Cash from Operations (Retail β€” β‚Ή Crores)
Line ItemCalculationQ1 Amount
Net Income (Q1)From quarterly IS210
(+) Depreciation (Q1)From quarterly IS125
(βˆ’) Ξ” A/R1,956 βˆ’ 530(1,426)
(βˆ’) Ξ” Inventory1,443 βˆ’ 1,800357
(+) Ξ” A/P597 βˆ’ 600(3)
(βˆ’) Ξ” Other CA213 βˆ’ 200(13)
(+) Ξ” Accrued Expenses313 βˆ’ 30013
Cash from Operations (Q1) (737)
-- Q1 Working Capital Changes --
Ξ” A/R = A/R (Q1 End) βˆ’ A/R (Beginning) = 1,956 βˆ’ 530 = +1,426 β†’ uses cash (negative)
Ξ” Inventory = Inv (Q1 End) βˆ’ Inv (Beginning) = 1,443 βˆ’ 1,800 = βˆ’357 β†’ frees cash (positive)
Ξ” A/P = A/P (Q1 End) βˆ’ A/P (Beginning) = 597 βˆ’ 600 = βˆ’3 β†’ uses cash (negative)

-- Total CFO Q1 --
CFO = 210 + 125 βˆ’ 1,426 + 357 βˆ’ 3 βˆ’ 13 + 13 = βˆ’737
// Negative CFO in Q1 because A/R spiked (festive season buildup)
πŸ“ Student Exercise: Build-Up Method for Cash from Operations

Download the CSV below and calculate Cash from Operations (CFO) for Q1–Q4 using Net Income, Depreciation, and quarterly working capital changes. Then verify the net change in cash after investing and financing activities.

⬇️ Download Cash Flow Build-Up CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute CFO for each quarter using NI + Depreciation + working capital changes.
  3. Calculate net change in cash after CFI and CFF.
  4. Check whether cash flows are negative in Q1 and Q3, and explain why.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
CFO = Net Income + Depreciation βˆ’ Ξ” A/R βˆ’ Ξ” Inventory + Ξ” A/P βˆ’ Ξ” Other CA + Ξ” Accrued Expenses
Step 2: Q1 CFO
CFO = 210 + 125 βˆ’ (1,956 βˆ’ 530) βˆ’ (1,443 βˆ’ 1,800) + (597 βˆ’ 600) βˆ’ (213 βˆ’ 200) + (313 βˆ’ 300)
CFO = βˆ’737
Step 3: Q2 CFO
CFO = 244 + 125 βˆ’ (2,139 βˆ’ 1,956) βˆ’ (1,579 βˆ’ 1,443) + (653 βˆ’ 597) βˆ’ (225 βˆ’ 213) + (325 βˆ’ 313)
CFO = 106
Step 4: Q3 CFO
CFO = 502 + 125 βˆ’ (3,544 βˆ’ 2,139) βˆ’ (2,616 βˆ’ 1,579) + (1,083 βˆ’ 653) βˆ’ (238 βˆ’ 225) + (338 βˆ’ 325)
CFO = βˆ’1,385
Step 5: Q4 CFO
CFO = 131 + 125 βˆ’ (1,528 βˆ’ 3,544) βˆ’ (1,128 βˆ’ 2,616) + (467 βˆ’ 1,083) βˆ’ (250 βˆ’ 238) + (350 βˆ’ 338)
CFO = 3,144
πŸ’‘ Why this matters: The build-up method captures timing and seasonality. Q1 and Q3 are negative because receivables and inventory increase sharply, while Q4 turns strongly positive as working capital unwinds.

2 Proportional Revenue Allocation (Quick Method)

Allocate annual cash flow based on each quarter's revenue share. Useful when you need a quick estimate and don't have detailed working capital data.

Quarterly CF Item = Annual CF Item Γ— (Quarterly Revenue Γ· Annual Revenue)
Quick Example: Allocating Annual CFO of β‚Ή1,528 (Retail)
QuarterRevenue% of AnnualAllocated CFO
Q13,20021.3%326
Q23,50023.3%356
Q35,80038.7%591
Q42,50016.7%255
Total15,000100%1,528
⚠️ Warning: This method ignores the timing of cash flows. In the Retail example, Q1 actually has negative CFO because of working capital buildup, but this method allocates positive CFO to Q1. Use this only for rough estimates, not for formal models.
πŸ“ Student Exercise: Proportional Revenue Allocation (Quick Method)

Download the CSV below and allocate the annual CFO of β‚Ή1,528 across Q1–Q4 using each quarter's revenue share. Then compare the result with the build-up method and note where the quick method may be misleading.

⬇️ Download Proportional Allocation CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute each quarter's allocated CFO using revenue proportion.
  3. Check that the quarterly allocations sum to the annual CFO of β‚Ή1,528.
  4. Compare the Q1 allocation to the build-up CFO and explain the difference.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
Allocated CFO = Annual CFO Γ— (Quarterly Revenue Γ· Annual Revenue)
Step 2: Q1
1,528 Γ— (3,200 Γ· 15,000) = 326
Step 3: Q2
1,528 Γ— (3,500 Γ· 15,000) = 356
Step 4: Q3
1,528 Γ— (5,800 Γ· 15,000) = 591
Step 5: Q4
1,528 Γ— (2,500 Γ· 15,000) = 255
πŸ’‘ Interpretation: This method is simple and fast, but it misses seasonal timing effects. Notice how Q1 is shown as positive even though the build-up method showed negative CFO due to working capital investment.

3 Residual / Subtraction Method

If you can derive Q1–Q3 cash flows from the build-up method, then Q4 = Annual βˆ’ (Q1 + Q2 + Q3). Also useful when half-yearly data is available.

Q4 CF = Annual CF βˆ’ Q1 CF βˆ’ Q2 CF βˆ’ Q3 CF

This method is particularly useful when half-yearly (H1/H2) cash flow data is available but not quarterly. In that case:

If H1 CF is available: Q1 CF + Q2 CF = H1 CF
Use Build-Up for Q1 β†’ Then Q2 = H1 CF βˆ’ Q1 CF
Use Build-Up for Q3 β†’ Then Q4 = Annual CF βˆ’ H1 CF βˆ’ Q3 CF
πŸ“ Student Exercise: Full Cash Flow Statement (Build-Up Method)

Download the CSV below and build the complete quarterly cash flow statement for the retail example using the Build-Up Method. Then verify that the ending cash balance reconciles to the balance sheet.

⬇️ Download Cash Flow Build-Up CSV

Step-by-Step Instructions
  1. Open the CSV in Excel and identify the quarterly Net Income, Depreciation, working capital balances, and financing data.
  2. Compute quarterly CFO using: Net Income + Depreciation βˆ’ Ξ” A/R βˆ’ Ξ” Inventory + Ξ” A/P βˆ’ Ξ” Other CA + Ξ” Accrued Expenses.
  3. Compute CFI using the CapEx row and CFF using Debt Repayment, New Debt, and Dividends.
  4. Calculate Net Change in Cash for each quarter as CFO + CFI + CFF.
  5. Roll cash forward from the beginning balance to derive ending cash for each quarter.
  6. Check that the ending cash balance matches the cash plug / balance sheet cash.
  7. Use the residual method only as a cross-check for annual cash flow totals.
βœ… Validation checks: Cross-foot the quarterly cash flows, verify the balance sheet cash line, and confirm whether any quarter requires a revolver if cash becomes negative.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: CFO Formula
CFO = NI + Depreciation βˆ’ Ξ”A/R βˆ’ Ξ”Inventory + Ξ”A/P βˆ’ Ξ”Other CA + Ξ”Accrued Expenses
Step 2: Q1 CFO
CFO = 210 + 125 βˆ’ (1,956 βˆ’ 530) βˆ’ (1,443 βˆ’ 1,800) + (597 βˆ’ 600) βˆ’ (213 βˆ’ 200) + (313 βˆ’ 300)
CFO = βˆ’737
Step 3: Q2 CFO
CFO = 244 + 125 βˆ’ (2,139 βˆ’ 1,956) βˆ’ (1,579 βˆ’ 1,443) + (653 βˆ’ 597) βˆ’ (225 βˆ’ 213) + (325 βˆ’ 313)
CFO = 106
Step 4: Q3 CFO
CFO = 502 + 125 βˆ’ (3,544 βˆ’ 2,139) βˆ’ (2,616 βˆ’ 1,579) + (1,083 βˆ’ 653) βˆ’ (238 βˆ’ 225) + (338 βˆ’ 325)
CFO = βˆ’1,385
Step 5: Q4 CFO
CFO = 131 + 125 βˆ’ (1,528 βˆ’ 3,544) βˆ’ (1,128 βˆ’ 2,616) + (467 βˆ’ 1,083) βˆ’ (250 βˆ’ 238) + (350 βˆ’ 338)
CFO = 3,144
Step 6: CFI and CFF
CFI = (375) each quarter
CFF = Debt Repayment + New Debt βˆ’ Dividends = (100) + 25 βˆ’ dividends
Step 7: Net Change in Cash
Q1 = (737) + (375) + (75) = (1,187)
Q2 = 106 + (375) + (75) = (344)
Q3 = (1,385) + (375) + (75) = (1,835)
Q4 = 3,144 + (375) + (584) = 2,185
πŸ’‘ Interpretation: CFO is strongly negative in Q1 and Q3 because receivables and inventory increase sharply. Q4 turns positive as working capital unwinds and the annual cash flow recovers.
πŸ“ Student Exercise: Residual / Subtraction Method

Download the CSV below and compute Q4 CFO as the residual using the annual CFO and the first three quarters. Then verify that all four quarters sum back to the annual figure.

⬇️ Download Residual Exercise CSV

Student Tasks
  1. Import the CSV into Excel.
  2. Compute Q4 CFO using Annual CFO βˆ’ Q1 βˆ’ Q2 βˆ’ Q3.
  3. Verify that Q1 + Q2 + Q3 + Q4 = Annual CFO.
  4. Explain why this method is a reconciliation shortcut, not a forecasting method.
πŸ”’ Instructor Solution (Hidden)
Show step-by-step solution
Step 1: Formula Setup
Q4 CFO = Annual CFO βˆ’ Q1 CFO βˆ’ Q2 CFO βˆ’ Q3 CFO
Step 2: Compute Q4
Q4 CFO = 1,528 βˆ’ (βˆ’737) βˆ’ 106 βˆ’ (βˆ’1,385)
Q4 CFO = 1,528 + 737 βˆ’ 106 + 1,385 = 3,544
Step 3: Verification
Q1 + Q2 + Q3 + Q4 = βˆ’737 + 106 βˆ’ 1,385 + 3,544 = 1,528
πŸ’‘ Interpretation: The residual method is a convenient check when annual totals are known, but it does not forecast behavior on its own. It is best used after you have already estimated Q1–Q3 by a more detailed method.
πŸ“‹ Cash Flow Coverage: Item β†’ Method Mapping
Cash Flow Item Method Condition / When to Use Notes
Cash from Operations (CFO) Build-Up Method Use when quarterly Net Income, Depreciation, and WC changes are available Best practice for formal models
Capital Expenditure Schedule / management guidance Use company guidance, historical spend pattern, or capex budget Do not allocate purely by revenue unless no better data exists
Debt Repayment Debt schedule Use explicit principal amortization or refinancing schedule Can be linear only if repayments are smooth and consistent
New Debt Debt schedule / financing plan Use when borrowings are known from management guidance or funding plan Often linked to revolver or acquisition financing
Dividends Schedule / policy-driven Use declared dividend policy, payout ratio, or annual dividend plan Usually paid in specific quarters, not evenly
Net Change in Cash Residual calculation Computed after operating, investing, and financing cash flows are built CF subtotal / bridge item
Cash β€” Beginning Carry-forward Prior quarter ending cash becomes current quarter beginning cash Matches the balance sheet opening cash
Cash β€” Ending Cash plug / reconciliation Use after all cash flow lines are computed Must reconcile to balance sheet cash
βœ… Coverage Note: Every cash flow line item shown in this lecture is now mapped to a supported quarterly method: build-up, schedule, residual, carry-forward, or cash plug.
πŸ’‘ Cash Flow Modeling Note

For cash flow statements, the key distinction is between operating flows (use build-up), investing/financing decisions (use schedules or management guidance), and the ending cash balance (use the cash plug to reconcile). This is the same decision logic used in professional three-statement models.

πŸ’° Complete Quarterly Cash Flow Statement β€” Retail (β‚Ή Crores)

Using the Build-Up Method with quarterly IS and derived quarterly BS changes

Cash Flow Item Q1 Q2 Q3 Q4 FY2025
Operating Activities
  Net Income2102445021311,088
  (+) Depreciation125125125125500
  (βˆ’) Ξ” A/R(1,426)(183)(1,405)2,016(40)
  (βˆ’) Ξ” Inventory357(136)(1,037)1,488300
  (+) Ξ” A/P(3)56430(616)100
  Other WC Changes00000
Cash from Operations (737) 106 (1,385) 3,144 1,528
Investing Activities
  (βˆ’) Capital Expenditure(375)(375)(375)(375)(1,500)
Cash from Investing (375) (375) (375) (375) (1,500)
Financing Activities
  >(βˆ’) Debt Repayment(100)(100)(100)(100)(400)
  (+) New Debt25252525100
  (βˆ’) Dividends000(509)(509)
Cash from Financing (75) (75) (75) (584) (809)
Net Change in Cash (1,187) (344) (1,835) 2,185 (781)
Cash β€” Beginning 800 539* 1,020* 1,200* 800
Cash β€” Ending 539* 1,020* 1,200* 1,200* 19*

* Values may differ slightly from BS-derived cash due to rounding and the equity plug. This is normal β€” the model should be iterated to converge.

Section 5

Practice Exercise: Linear Interpolation for Non-Working Capital Items

Apply interpolation to quarterly forecast lines using a simple annual bridge

πŸ“ Practice Problem

Using the data in practice-files/lecture-04/quarterly-conversion-practice-data.csv, complete the following:

⬇️ Download Practice Data CSV

Step-by-Step Instructions
  1. Open the CSV in Excel and identify the quarterly data for each company.
  2. For TCS, calculate the quarterly balance sheet using the Working Capital Ratio Method with DSO = 27, DIO = 2, and DPO = 18.
  3. For Tata Steel, choose the appropriate method for each line item:
    • Ratio method for working capital items such as A/R, Inventory, and A/P
    • Linear interpolation for smooth non-working-capital items like PP&E and intangibles
    • Roll-forward for retained earnings
  4. Build the quarterly cash flow statement for all three companies using the Build-Up Method.
  5. Perform validation checks:
    • Cross-footing: quarterly totals should agree with annual totals for flow items
    • Balance sheet balance: assets must equal liabilities + equity each quarter
    • Ratio reasonableness: confirm the derived working capital ratios are sensible
  6. Identify any quarter with negative cash and calculate the required revolver amount.
Item Beginning Ending Recommended Method
Net PP&E 3,500 4,000 Linear Interpolation
Intangible Assets 500 450 Linear Interpolation
Retained Earnings 2,930 3,508 Roll-Forward Only
Long-term Debt 1,500 1,200 Linear or Debt Schedule

1 Student Tasks

  1. Calculate Q1, Q2, Q3, and Q4 ending balances for Net PP&E.
  2. Repeat the same for Intangible Assets.
  3. Classify each item below as interpolate, roll-forward, or ratio-based.
  4. Explain why Retained Earnings should not be interpolated blindly.
  5. Decide whether Long-term Debt should use interpolation or a debt schedule.
Classification rule: If the item is driven by operations, seasonality, or a discrete transaction, do not interpolate it. Use a ratio method or a roll-forward instead.

2 Suggested Answers

Item Q1 Q2 Q3 Q4 Method
Net PP&E 3,625 3,750 3,875 4,000 Interpolate
Intangible Assets 488 475 463 450 Interpolate
Retained Earnings 3,140 3,384 3,886 3,508 Roll-forward
Long-term Debt 1,425 1,350 1,275 1,200 Interpolate / Schedule
Accounts Receivable 1,956 2,139 3,544 1,528 Ratio method
Inventory 1,443 1,579 2,616 1,128 Ratio method
Accounts Payable 597 653 1,083 467 Ratio method
βœ… Answer Note: The exact quarterly path is only appropriate if the decline or increase is smooth. If debt repayments occur in a specific quarter, replace interpolation with the debt schedule. Retained earnings should always use the roll-forward method, while working-capital items should be classified as ratio-based.
Section 6

Industry Deep Dive: Retail (Seasonal Business)

Companies like Trent (Westside), DMart, Reliance Retail β€” Strong seasonality with Diwali/festive spike in Q3

πŸ›οΈ

Seasonality Pattern

Q3 (Oct-Dec) is peak festive season. Revenue can be 2-3x Q4. Inventory builds in Q2, sells in Q3.

πŸ“¦

Working Capital Behavior

High inventory (DIO ~58 days). A/R spikes with Q3 revenue. Payables lag behind. CCC ~89 days.

πŸ’‘

Conversion Challenge

Quarterly BS shows massive swings. Simple linear interpolation fails β€” MUST use ratio method for WC items.

πŸ“Š Revenue Seasonality β€” Retail (β‚Ή Crores)

Q1
3,200
Q2
3,500
Q3 πŸŽ†
5,800
Q4
2,500
🎯 Retail Conversion Takeaway

For seasonal businesses, the Working Capital Ratio Method is absolutely essential. Linear interpolation would show inventory smoothly going from β‚Ή1,800 to β‚Ή1,500 β€” but in reality, inventory peaks at β‚Ή2,616 in Q3 (before Diwali) and drops to β‚Ή1,128 in Q4 (post-season clearance). Only ratio-based conversion captures this correctly.

Section 6

Industry Deep Dive: Technology / IT Services

Companies like TCS, Infosys, Wipro β€” Relatively even revenue, minimal inventory, high receivables

πŸ’»

Even Revenue Pattern

Revenue grows steadily 2-3% per quarter. No seasonality. Quarterly conversion is simpler.

πŸ’³

Receivables-Heavy

DSO ~27 days. A/R is the main working capital item. Minimal inventory (DIO ~2 days β€” mostly unbilled revenue).

πŸ“ˆ

Conversion Approach

Both ratio method and linear interpolation give similar results due to even growth. Ratio method still preferred for A/R.

πŸ“Š IT Services β€” Quarterly IS & BS Conversion (β‚Ή Crores)

Item Q1 Q2 Q3 Q4 FY2025
Income Statement (Given)
  Revenue15,00015,50016,00016,50063,000
  Net Income1,1251,1811,2381,3694,913
Balance Sheet (Derived)
  A/R (DSO=27)4,5004,6504,8004,950β€”
  Inventory (DIO=2)217224231237β€”
  A/P (DPO=18)1,9502,0152,0802,125β€”
Cash Flow (Derived)
  CFO1,3461,4731,6104,5548,983
  CFI(650)(650)(650)(650)(2,600)
  CFF(164)(164)(164)(163)(654)
🎯 IT Services Conversion Takeaway

IT services companies have the easiest quarterly conversion because: (1) Revenue is evenly distributed across quarters, (2) Minimal inventory eliminates complex DIO calculations, (3) Working capital changes are small and predictable. The ratio method and linear interpolation give very similar results for most line items.

Section 7

Industry Deep Dive: Manufacturing (Asset-Heavy)

Companies like Tata Steel, JSW Steel, Hindalco β€” Lumpy capex, high depreciation, commodity-driven revenue

🏭

Asset-Heavy Profile

Net PP&E is the largest asset. Capex is lumpy β€” maintenance capex quarterly, expansion capex in specific quarters.

πŸ”§

High Depreciation Impact

Depreciation (~β‚Ή2,800/qtr) can exceed operating profit. PP&E roll-forward is critical for quarterly BS.

πŸ“‰

Profitability Volatility

Commodity price swings cause large quarterly profit variations. Some quarters may show losses (negative tax).

πŸ“Š Manufacturing β€” Key Conversion Challenges (β‚Ή Crores)

Challenge Item Q1 Q2 Q3 Q4 Approach
Lumpy Capex CapEx (2,000) (2,000) (3,500) (2,500) Use guidance/estimates
PP&E Roll-Forward Net PP&E 30,000 29,700 30,400 25,800 Period-end known
Negative NI β†’ Tax Tax 0 0 0 0 No tax in loss quarters
Large Inventory Inventory 8,506 8,665 9,878 7,700 DIO ratio method
🏭 Manufacturing PP&E Roll-Forward
Net PP&E (Q End) = Net PP&E (Q Start) + CapEx (Q) βˆ’ Depreciation (Q) βˆ’ Asset Sales (Q)
PP&E Roll-ForwardQ1Q2Q3Q4
Beginning Net PP&E30,00030,20029,70030,400
(+) CapEx2,0002,0003,5002,500
(βˆ’) Depreciation(2,800)(2,800)(2,800)(2,800)
(βˆ’) Asset Sales (Net)1,0003000(4,300)
Ending Net PP&E30,20029,70030,40025,800
🎯 Manufacturing Conversion Takeaway

For asset-heavy companies, the PP&E roll-forward is the most critical step. Unlike retail where working capital drives the conversion, manufacturing requires careful quarterly capex and depreciation tracking. Use management guidance, analyst estimates, or historical quarterly patterns to estimate quarterly capex allocation. The annual total must reconcile.

Section 8

Validation, Quality Checks & Summary

Ensuring your quarterly model is accurate and reliable

βœ… Essential Validation Checks

For all flow variables (Revenue, COGS, Net Income, Depreciation, Cash Flow items):

Q1 + Q2 + Q3 + Q4 = Annual Total

If this doesn't hold, there's an error in your conversion. This is the most fundamental check.

ItemQ1+Q2+Q3+Q4AnnualMatch?
Revenue3,200+3,500+5,800+2,50015,000βœ“
Net Income210+244+502+1311,088βœ“
Total Assets = Total Liabilities + Total Equity (every quarter)

If it doesn't balance, check your "Cash" plug. The cash figure should be calculated as:

Cash = Total L&E βˆ’ (A/R + Inventory + Other CA + Net PP&E + Intangibles)

Check that derived quarterly ratios don't deviate wildly from annual averages:

RatioAnnualAcceptable Quarterly Range
Gross Margin30%25%–35%
DSO55 days45–65 days
DIO58 days48–68 days
DPO24 days18–30 days

If a quarterly DSO is 150 days while annual is 55, something is wrong with your A/R derivation.

Cash End of Qβ‚„ (from BS) = Cash End of Q₃ + Net Change in Cash (Qβ‚„ from CF)

This links the balance sheet and cash flow statement through cash. If the ending cash on the BS doesn't match the ending cash from the CF statement, there's a disconnect in your model.

If your model produces negative cash on the balance sheet in any quarter:

  • The company may need a revolving credit facility (short-term borrowing)
  • Or there's an error in your working capital assumptions
  • Check if A/R or inventory is unreasonably high for that quarter
🚨 Negative cash is a red flag. In real models, this triggers a "revolver" draw β€” the company borrows short-term to cover the shortfall. Always add a revolver facility to your model.

πŸ“Š Method Comparison Summary

Method Best For Accuracy Complexity Seasonality
Working Capital Ratios A/R, Inventory, A/P ⭐⭐⭐⭐⭐ Medium βœ… Captures
Linear Interpolation PP&E, Debt, Intangibles ⭐⭐⭐ Low ❌ Ignores
RE Roll-Forward Retained Earnings ⭐⭐⭐⭐⭐ Low βœ… Via NI
Revenue Proportional Quick CF estimate ⭐⭐ Very Low ⚠️ Partial
CF Build-Up Quarterly CF Statement ⭐⭐⭐⭐⭐ High βœ… Captures

🏭 Industry Comparison: Conversion Difficulty

πŸ›οΈ
Retail
Hard

High seasonality, inventory management, working capital swings

πŸ’»
IT Services
Easy

Even revenue, minimal inventory, predictable working capital

🏭
Manufacturing
Medium

Lumpy capex, PP&E roll-forward critical, commodity swings

Practice

Hands-On Exercise

Apply what you've learned

πŸ“ Practice Problem

Using the data in practice-files/lecture-04/quarterly-conversion-practice-data.csv, complete the following:

Tasks
  1. Calculate quarterly balance sheet for TCS using the Working Capital Ratio Method (DSO=27, DIO=2, DPO=18)
  2. Calculate quarterly balance sheet for Tata Steel using appropriate methods for each line item
  3. Build the quarterly cash flow statement for all three companies using the Build-Up Method
  4. Perform all validation checks (cross-footing, BS balance, ratio reasonableness)
  5. Identify which quarters show negative cash and calculate the required revolver amount

πŸ“š Key Terms - Click to Flip

Knowledge Check

Test Your Understanding

Let's see what you've learned!

Summary

Key Takeaways

πŸ“ What We Covered

  • Stock vs. Flow: Balance sheet items (stock) can't be divided by 4 β€” they require ratio-based or interpolation methods. Income/cash flow items (flow) can be summed across quarters.
  • Working Capital Ratio Method: The gold standard for converting A/R, Inventory, and A/P to quarterly. Uses DSO, DIO, DPO with quarterly revenue/COGS.
  • Linear Interpolation: Best for non-working capital items like PP&E, intangibles, and long-term debt that change smoothly.
  • RE Roll-Forward: Precisely derive quarterly retained earnings using quarterly net income and dividend timing.
  • CF Build-Up Method: Construct quarterly cash flows from quarterly IS + quarterly BS changes. Superior to proportional allocation.
  • Industry Matters: Retail (seasonal, hard), IT Services (steady, easy), Manufacturing (asset-heavy, medium) β€” each requires different emphasis.
  • Validation is Critical: Always cross-foot quarterly sums to annual, check BS balance, verify ratio reasonableness.
πŸ“š Next Steps

Lecture 5: Revenue & Expense Forecasting
Now that you can build quarterly historical models, the next step is forecasting quarterly revenues and expenses using top-down and bottom-up approaches. Read: Damodaran, Ch. 4; Pignataro, Ch. 3.