Objectives

Section Learning Objectives

5.1

How Working Capital & Capex Link to Financial Statements

🔗 Key Concept

Working capital and Capex affect all three financial statements:
• Income Statement: Depreciation expense
• Balance Sheet: NWC components, Net PP&E
• Cash Flow Statement: Change in NWC, Capex, Depreciation (add-back)

📈 Income Statement

Depreciation

Operating Expense

↓ Reduces Net Income

💰 Cash Flow Statement

+ Depreciation (Add-back)

- Change in NWC

- Capital Expenditures

Operating & Investing

🏦 Balance Sheet

A/R, Inventory, A/P (NWC)

Net PP&E

Retained Earnings

Assets & Equity

5.2

Complete Statement Links

📋 NWC & Capex Links Across Statements

Item Income Statement Cash Flow Statement Balance Sheet
Depreciation Operating Expense (↓ Net Income) Add-back in CFO ↑ Accum. Depreciation (↓ Net PP&E)
Capex — (No impact) Outflow in CFI ↑ Gross PP&E (↑ Net PP&E)
A/R Change — Outflow if ↑ (in CFO) ↑ or ↓ A/R Balance
Inventory Change — Outflow if ↑ (in CFO) ↑ or ↓ Inventory Balance
A/P Change — Inflow if ↑ (in CFO) ↑ or ↓ A/P Balance
5.3

Recommended Model Structure

📁 Excel Tab Structure

Tab # Tab Name Contents
1 Assumptions All inputs: growth rates, margins, DSO/DIO/DPO, Capex %
2 NWC Schedule A/R, Inventory, A/P forecasts; Change in NWC calculation
3 PP&E Schedule Gross PP&E, Accum. Depr., Net PP&E roll-forwards
4 Income Statement Revenue through Net Income; includes Depreciation
5 Balance Sheet Assets, Liabilities, Equity; includes NWC and Net PP&E
6 Cash Flow CFO, CFI, CFF; includes Change in NWC, Capex, Depr. add-back
7 Checks Balance Sheet check, Cash flow tie-out, Error flags
5.4

Model Quality Checks

✅ Balance Sheet Check

Assets = Liabilities + Equity

=IF(ABS(Assets - (Liab+Equity)) < 0.01, "OK", "ERROR")

✅ Cash Flow Tie-Out

Net Change in Cash = BS Cash Change

=IF(CF_Change = BS_Cash_Change, "OK", "ERROR")

✅ NWC Change Sign

Positive NWC growth = Outflow

=IF(NWC_Increase, "Outflow", "Inflow")

✅ PP&E Roll-Forward

Net PP&E = Gross - Accum. Depr.

=IF(Net_PPE = Gross - Accum_Depr, "OK", "ERROR")
⚠️ Common Integration Errors

1. Change in NWC sign reversed (should be Beginning - Ending)
2. Forgetting to link Depreciation to all three statements
3. Capex not flowing to both Cash Flow and Balance Sheet
4. Beginning Balance not linked to Prior Year Ending

5.5

Walkthrough: How One Transaction Flows Through All Statements

📊 Example: ₹100 Depreciation Expense

Let's trace how ₹100 in Depreciation impacts all three financial statements. This is the key to understanding integration!

📈 Step 1: Income Statement

Depreciation Expense: ₹100

↓ Reduces Operating Income by ₹100

↓ Reduces Net Income by ₹100

↓ Reduces Tax by ₹25 (25% × ₹100)

Net impact: -₹75 to Net Income

💰 Step 2: Cash Flow Statement

Net Income: -₹75 (from IS)

+ Depreciation Add-back: ₹100

Net Income already includes -₹100 depr.

Add back ₹100 (non-cash expense)

Net cash impact: +₹25

(Tax savings of ₹25)

🏦 Step 3: Balance Sheet

Cash: +₹25 (from CF)

Accum. Depreciation: +₹100

Net PP&E = Gross - Accum. Depr.

Net PP&E decreases by ₹100

Retained Earnings decreases by ₹75

Assets: +₹25 - ₹100 = -₹75

Equity: -₹75 ✓ Balances!

✅ Key Insight

Depreciation is a non-cash expense. It reduces Net Income but is added back in Cash Flow. The only real cash impact is the tax savings (₹25 in this example).

Remember: Depreciation links ALL three statements!
IS (expense) → CF (add-back) → BS (accumulated depreciation)
Excel Lab

Hands-On: Build Integrated Model

📁 Step 1: Download the Template

Download the Integration template file to get started:

⬇️ Download Integration Template (CSV)

Instructions: Open the CSV file in Excel and save it as an Excel workbook (.xlsx). The template has all sections pre-structured with historical data filled in.

🔗 Step 2: Understand the Key Links

These are the critical connections that make your model integrated:

From To What Links Why
PP&E Schedule Income Statement Depreciation Depreciation is an operating expense
PP&E Schedule Balance Sheet Net PP&E Shows asset book value
PP&E Schedule Cash Flow Capex Cash outflow for investments
NWC Schedule Balance Sheet A/R, Inventory, A/P Working capital assets/liabilities
NWC Schedule Cash Flow Change in NWC Cash tied up in operations
Income Statement Cash Flow Net Income Starting point for CFO
Income Statement Cash Flow Depreciation (add-back) Non-cash expense adjustment
Cash Flow Balance Sheet Ending Cash Cash balance must match

📝 Step 3: Build the Integration Links

Task 3.1: Link Depreciation (PP&E → IS → CF → BS)

Depreciation must appear in three places:

1. PP&E Schedule (Calculate it):
Depreciation = Beginning Gross PP&E × 5.5%

2. Income Statement (Link to PP&E):
='PP&E Schedule'!Depreciation_Row

3. Cash Flow Statement (Link to PP&E):
='PP&E Schedule'!Depreciation_Row (same link as IS)

4. Balance Sheet: Links automatically through Net PP&E

Task 3.2: Link Capex (PP&E → CF → BS)

1. PP&E Schedule (Calculate it):
Capex = Revenue × 10%

2. Cash Flow Statement (Link to PP&E):
=-'PP&E Schedule'!Capex_Row (negative = outflow)

3. Balance Sheet: Links automatically through Gross PP&E roll-forward

Task 3.3: Link NWC Components (NWC → BS)

Balance Sheet Links:
A/R = ='NWC Schedule'!A/R_Row
Inventory = ='NWC Schedule'!Inventory_Row
A/P = ='NWC Schedule'!A/P_Row

Task 3.4: Link Change in NWC (NWC → CF)

Cash Flow Statement:
Change in NWC = ='NWC Schedule'!Change_in_NWC_Row

⚠️ Important: If NWC increases, it's a cash OUTFLOW (negative).
The formula in NWC Schedule should be: =Beginning_NWC - Ending_NWC

Task 3.5: Link Cash Flow to Balance Sheet

Balance Sheet Cash:
Ending Cash = ='Cash Flow'!Ending_Cash_Row

This is your final quality check! CF Ending Cash must equal BS Cash.

✅ Step 4: Verify Your Results (2026E)

Check your 2026E values against these expected results:

Statement Line Item Expected 2026E Value
Income Statement Revenue 3,993,000
Net Income ~920,000
NWC Schedule A/R ~87,233
Change in NWC ~(10,073)
NWC Ending ~150,268
PP&E Schedule Depreciation ~239,580
Net PP&E ~2,056,920
Balance Sheet Total Assets ~2,730,000
Cash Flow Ending Cash ~1,190,000

🔍 Step 5: Add Quality Checks

These formulas verify your model is correctly integrated:

Check Formula Expected
BS Balance Check =IF(ABS(Total_Assets-(Total_Liab+Total_Equity))<1,"OK","ERROR") OK
CF = BS Cash =IF(CF_Ending_Cash=BS_Cash,"OK","ERROR") OK
Depreciation Triple Link =IF(AND(IS_Depr=PPE_Depr,CF_Depr=PPE_Depr),"OK","ERROR") OK
🔧 Troubleshooting Integration Errors
BS doesn't balance → Check Retained Earnings formula: =Prior_RE + Net_Income - Dividends
CF ≠ BS Cash → Check Beginning Cash links to prior year Ending Cash
NWC Change wrong sign → Formula should be Beginning - Ending (not Ending - Beginning)
Depreciation mismatch → All three statements should LINK to same PP&E cell (don't recalculate)

📋 Final Integration Checklist

Links to Verify:
  • ☐ IS Depreciation = PP&E Depreciation
  • ☐ CF Depreciation = PP&E Depreciation
  • ☐ CF Capex = PP&E Capex
  • ☐ BS Net PP&E = PP&E Net PP&E
  • ☐ BS A/R = NWC A/R
  • ☐ BS Inventory = NWC Inventory
  • ☐ BS A/P = NWC A/P
  • ☐ CF Change in NWC = NWC Change
Checks to Pass:
  • ☐ Balance Sheet balances (Assets = L + E)
  • ☐ CF Ending Cash = BS Cash
  • ☐ All years have consistent formulas
  • ☐ No hardcoded numbers in formulas
  • ☐ All inputs in blue font
Summary

Key Takeaways

  • Depreciation links IS (expense), CF (add-back), BS (accum. depr.)
  • Capex is a Cash Flow outflow and increases Gross PP&E
  • Change in NWC goes in Cash Flow from Operations
  • Always include quality checks to catch linking errors
  • Model structure matters - use separate tabs for clarity
← Previous: PP&E Roll-Forward Back to Lecture Overview →