# 📊 Working Capital & Capex Modeling
## Practice Exercises Guide - Lecture 6
### Financial Modeling Course | MBA Term 3 | Woxsen University

---

## 📁 Files Included

| File | Description | Use For |
|------|-------------|---------|
| `lecture-06-nwc-data.csv` | 5-year working capital data for 5 Indian companies | Exercises 1 & 2 |
| `lecture-06-capex-data.csv` | CAPEX, PP&E roll-forward & asset class data | Exercises 3 & 4 |

---

## 🎯 Learning Objectives

After completing these exercises, you will be able to:

- ✅ Calculate and interpret DSO, DIO, DPO, and Cash Conversion Cycle
- ✅ Build working capital forecasts using the days-based method
- ✅ Create depreciation schedules with Excel's SLN function
- ✅ Construct complete PP&E roll-forward schedules
- ✅ Integrate all schedules with quality checks

---

## 📝 Exercise 1: Calculate Working Capital Metrics

**📁 File:** `lecture-06-nwc-data.csv`  
**⏱ Time:** 30 minutes  
**🎯 Goal:** Calculate DSO, DIO, DPO, and CCC for all companies

---

### Task 1.1: Calculate DSO, DIO, DPO

**Step 1:** Open `lecture-06-nwc-data.csv` in Excel

**Step 2:** Add new columns for DSO, DIO, DPO after the existing data:

| Company | Year | Revenue | COGS | A/R | Inventory | A/P | **DSO** | **DIO** | **DPO** |
|---------|------|---------|------|-----|-----------|-----|---------|---------|---------|

**Step 3:** Enter the formulas:

```
┌─────────────────────────────────────────────────────────────────┐
│  METRIC        │  FORMULA                    │  WHAT IT MEASURES │
├─────────────────────────────────────────────────────────────────┤
│  DSO (Days)    │  =(A/R ÷ Revenue) × 365     │  Collection time  │
│  DIO (Days)    │  =(Inventory ÷ COGS) × 365  │  Inventory hold   │
│  DPO (Days)    │  =(A/P ÷ COGS) × 365        │  Payment time     │
└─────────────────────────────────────────────────────────────────┘
```

**Excel Formulas (assuming data starts in row 2):**
```excel
DSO:  =(E2/B2)*365    ' A/R in column E, Revenue in column B
DIO:  =(F2/C2)*365    ' Inventory in column F, COGS in column C
DPO:  =(G2/C2)*365    ' A/P in column G, COGS in column C
```

---

### Task 1.2: Calculate Cash Conversion Cycle (CCC)

**Formula:**
```
┌────────────────────────────────────────────────┐
│                                                │
│   CCC = DSO + DIO - DPO                        │
│                                                │
│   (Time to collect) + (Time holding) - (Time to pay)
│                                                │
└────────────────────────────────────────────────┘
```

**Excel:**
```excel
=H2+I2-J2    ' Assuming DSO in H, DIO in I, DPO in J
```

---

### Task 1.3: Expected Results - TCS

Verify your calculations against these expected values:

| Metric | 2021 | 2022 | 2023 | 2024 | 2025 | Trend |
|--------|------|------|------|------|------|-------|
| **DSO** | 122.1 | 120.5 | 118.8 | 117.2 | 115.6 | 📉 Improving |
| **DIO** | 45.6 | 45.8 | 45.9 | 45.7 | 44.9 | ➡ Stable |
| **DPO** | 69.8 | 67.5 | 65.2 | 63.8 | 62.1 | 📉 Declining |
| **CCC** | 97.9 | 98.8 | 99.5 | 99.1 | 98.4 | ➡ Stable |

---

### Task 1.4: Analysis Questions

Answer these questions based on your calculations:

1. Which company has the **lowest** Cash Conversion Cycle?
2. Which company has the **highest** DSO (slowest collection)?
3. Which company has the **highest** DPO (best supplier terms)?
4. Is TCS's working capital efficiency improving or worsening?

<details>
<summary>📋 Click for Answer Key</summary>

1. **Lowest CCC:** HDFC Bank (banks collect cash faster)
2. **Highest DSO:** TCS (IT companies have longer enterprise payment terms)
3. **Highest DPO:** Reliance (strong supplier negotiating power)
4. **TCS Trend:** Slightly improving (CCC stable, DSO decreasing)

</details>

---

## 📝 Exercise 2: Build Working Capital Forecast

**📁 File:** `lecture-06-nwc-data.csv`  
**⏱ Time:** 45 minutes  
**🎯 Goal:** Build a 5-year NWC forecast for TCS

---

### Task 2.1: Set Up Assumptions Sheet

Create a new sheet named **"Assumptions"** with these inputs (use BLUE font for inputs):

```
┌────────────────────────────────────────────────────────────────┐
│                    TCS FORECAST ASSUMPTIONS                    │
├────────────────────────────────────────────────────────────────┤
│                                                                │
│  Revenue Growth Rate              │  10%        │ (Input)      │
│  COGS % of Revenue                │  62%        │ (Input)      │
│                                                                │
│  ─────────────────────────────────────────────────────────────│
│                      WORKING CAPITAL ASSUMPTIONS               │
│  ─────────────────────────────────────────────────────────────│
│                                                                │
│  DSO - Days Sales Outstanding     │  100 days   │ (Input)      │
│  DIO - Days Inventory Outstanding │  175 days   │ (Input)      │
│  DPO - Days Payable Outstanding   │  58 days    │ (Input)      │
│                                                                │
└────────────────────────────────────────────────────────────────┘
```

---

### Task 2.2: Build Revenue & COGS Forecast

Create a new sheet named **"NWC Forecast"**:

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| Revenue Growth | - | 10% | 10% | 10% | 10% | 10% |
| **Revenue** | **289,456** | **318,402** | **350,242** | **385,266** | **423,793** | **466,172** |
| COGS % | 62% | 62% | 62% | 62% | 62% | 62% |
| **COGS** | **179,463** | **197,409** | **217,150** | **238,865** | **262,752** | **289,027** |

**Excel Formulas:**
```excel
Revenue (2026E):  =C2*(1+Assumptions!$B$1)
COGS (2026E):     =D2*Assumptions!$B$2
```

---

### Task 2.3: Forecast Working Capital Components

Use the **days-based method** to forecast A/R, Inventory, and A/P:

```
┌─────────────────────────────────────────────────────────────────┐
│                    DAYS-BASED FORMULAS                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  A/R       =  (DSO ÷ 365) × Revenue                             │
│  Inventory =  (DIO ÷ 365) × COGS                                │
│  A/P       =  (DPO ÷ 365) × COGS                                │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

**Excel Formulas:**
```excel
A/R:       =(Assumptions!$B$4/365)*D2        ' D2 = Revenue
Inventory: =(Assumptions!$B$5/365)*D3        ' D3 = COGS
A/P:       =(Assumptions!$B$6/365)*D3        ' D3 = COGS
```

**Expected Results:**

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| **A/R** | 80,394 | 87,233 | 94,067 | 101,384 | 108,950 | 117,246 |
| **Inventory** | 88,431 | 94,562 | 101,223 | 107,867 | 115,157 | 122,716 |
| **A/P** | 28,630 | 31,527 | 34,454 | 38,003 | 42,410 | 46,327 |

---

### Task 2.4: Calculate Net Working Capital

```
┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   NWC = A/R + Inventory - A/P                                   │
│                                                                 │
│   Change in NWC = Beginning NWC - Ending NWC                    │
│   (Positive = Cash Inflow, Negative = Cash Outflow)             │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

**Excel:**
```excel
NWC:           =A/R + Inventory - A/P
Beginning NWC: =Prior_Year_Ending_NWC    ' Link from prior year
Change in NWC: =Beginning_NWC - Ending_NWC
```

**⚠️ IMPORTANT:** Change in NWC = Beginning - Ending (NOT the reverse!)

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| **NWC** | 140,195 | 150,268 | 160,836 | 171,848 | 182,490 | 193,635 |
| **Change in NWC** | - | **(10,073)** | **(10,568)** | **(11,012)** | **(10,642)** | **(11,145)** |

**Interpretation:** Parentheses indicate cash OUTFLOW (more cash tied up in working capital)

---

## 📝 Exercise 3: Create Depreciation Schedule

**📁 File:** `lecture-06-capex-data.csv` (Asset Classes section)  
**⏱ Time:** 30 minutes  
**🎯 Goal:** Build depreciation schedule for multiple asset classes

---

### Task 3.1: Review Asset Data

The file contains 5 asset classes with different characteristics:

| Asset Class | Cost (₹) | Salvage (₹) | Life (Years) |
|-------------|----------|-------------|--------------|
| Buildings | 10,000,000 | 500,000 | 30 |
| Machinery | 5,000,000 | 250,000 | 10 |
| Computers | 2,000,000 | 100,000 | 5 |
| Vehicles | 1,500,000 | 100,000 | 7 |
| Furniture | 800,000 | 50,000 | 10 |

---

### Task 3.2: Calculate Annual Depreciation

**Straight-Line Formula:**
```
┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   Annual Depreciation = (Cost - Salvage Value) ÷ Useful Life    │
│                                                                 │
│   Excel: =SLN(cost, salvage, life)                              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

**Excel:**
```excel
=SLN(B2, C2, D2)       ' Using SLN function
' OR
=(B2-C2)/D2            ' Manual calculation
```

**Expected Results:**

| Asset Class | Cost | Salvage | Life | **Annual Depr.** |
|-------------|------|---------|------|------------------|
| Buildings | 10,000,000 | 500,000 | 30 | **316,667** |
| Machinery | 5,000,000 | 250,000 | 10 | **475,000** |
| Computers | 2,000,000 | 100,000 | 5 | **380,000** |
| Vehicles | 1,500,000 | 100,000 | 7 | **200,000** |
| Furniture | 800,000 | 50,000 | 10 | **75,000** |
| **TOTAL** | **19,300,000** | **1,000,000** | - | **1,446,667** |

---

### Task 3.3: Build 10-Year Depreciation Schedule

Create a schedule showing depreciation by year:

| Year | Buildings | Machinery | Computers | Vehicles | Furniture | **Total** |
|------|-----------|-----------|-----------|----------|-----------|-----------|
| 1 | 316,667 | 475,000 | 380,000 | 200,000 | 75,000 | **1,446,667** |
| 2 | 316,667 | 475,000 | 380,000 | 200,000 | 75,000 | **1,446,667** |
| 3 | 316,667 | 475,000 | 380,000 | 200,000 | 75,000 | **1,446,667** |
| 4 | 316,667 | 475,000 | 380,000 | 200,000 | 75,000 | **1,446,667** |
| 5 | 316,667 | 475,000 | 380,000 | 200,000 | 75,000 | **1,446,667** |
| 6 | 316,667 | 475,000 | **0** ⚠️ | 200,000 | 75,000 | **1,066,667** |
| 7 | 316,667 | 475,000 | 0 | 200,000 | 75,000 | **1,066,667** |
| 8 | 316,667 | 475,000 | 0 | **0** ⚠️ | 75,000 | **866,667** |
| 9 | 316,667 | 475,000 | 0 | 0 | 75,000 | **866,667** |
| 10 | 316,667 | **0** ⚠️ | 0 | 0 | 75,000 | **391,667** |

⚠️ **Note:** Computers fully depreciate after Year 5, Vehicles after Year 7, Machinery after Year 10

---

## 📝 Exercise 4: Build PP&E Roll-Forward Schedule

**📁 File:** `lecture-06-capex-data.csv` (TCS section)  
**⏱ Time:** 40 minutes  
**🎯 Goal:** Build complete PP&E roll-forward with quality checks

---

### Task 4.1: Build Gross PP&E Section

**Roll-Forward Equation:**
```
┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   Ending Gross PP&E = Beginning + Capex - Disposals             │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

**Excel Structure:**

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| Beginning Gross PP&E | 3,630,000 | 3,993,000 | 4,392,300 | 4,839,330 | 5,339,363 |
| + Capex | 363,000 | 399,300 | 447,030 | 500,033 | 553,036 |
| - Disposals | 0 | 0 | 0 | 0 | 0 |
| **= Ending Gross PP&E** | **3,993,000** | **4,392,300** | **4,839,330** | **5,339,363** | **5,892,399** |

**Excel Formulas:**
```excel
Beginning:  =Prior_Year_Ending_Gross    ' Link from prior year
Capex:      =Revenue*10%                ' Or link from assumptions
Ending:     =Beginning + Capex - Disposals
```

---

### Task 4.2: Build Accumulated Depreciation Section

**Roll-Forward Equation:**
```
┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   Ending Accum. Depr. = Beginning + Depreciation - Disp. Depr.  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| Beginning Accum. Depr. | 1,878,000 | 2,095,800 | 2,335,380 | 2,574,918 | 2,814,456 |
| + Depreciation | 217,800 | 239,580 | 239,538 | 239,538 | 239,538 |
| - Depr. on Disposals | 0 | 0 | 0 | 0 | 0 |
| **= Ending Accum. Depr.** | **2,095,800** | **2,335,380** | **2,574,918** | **2,814,456** | **3,053,994** |

---

### Task 4.3: Calculate Net PP&E

```
┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   Net PP&E = Gross PP&E - Accumulated Depreciation              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

| Line Item | 2025A | 2026E | 2027E | 2028E | 2029E | 2030E |
|-----------|-------|-------|-------|-------|-------|-------|
| Gross PP&E | 3,993,000 | 4,392,300 | 4,839,330 | 5,339,363 | 5,892,399 |
| - Accum. Depr. | 2,095,800 | 2,335,380 | 2,574,918 | 2,814,456 | 3,053,994 |
| **= Net PP&E** | **1,897,200** | **2,056,920** | **2,264,412** | **2,524,907** | **2,838,405** |

---

## ✅ Exercise 5: Quality Checks

Add these validation checks to your model:

### PP&E Schedule Checks

| Check | Formula | Expected |
|-------|---------|----------|
| Net PP&E ≥ 0 | `=IF(Net_PPE<0,"ERROR","OK")` | OK |
| Accum. Depr. ≤ Gross | `=IF(Accum>Gross,"ERROR","OK")` | OK |
| Beginning = Prior Ending | `=IF(Beg=Prior_End,"OK","ERROR")` | OK |

### Working Capital Checks

| Check | Formula | Expected |
|-------|---------|----------|
| DSO in range (0-365) | `=IF(AND(DSO>0,DSO<365),"OK","CHECK")` | OK |
| DIO in range (0-365) | `=IF(AND(DIO>0,DIO<365),"OK","CHECK")` | OK |
| DPO in range (0-365) | `=IF(AND(DPO>0,DPO<365),"OK","CHECK")` | OK |

---

## 🏆 Challenge Exercise: Integrated Model

Combine all schedules into an integrated model:

### Required Tabs:
1. **Assumptions** - All inputs in blue font
2. **NWC Schedule** - Working capital forecast
3. **PP&E Schedule** - Fixed asset roll-forward
4. **Income Statement** - Include depreciation expense
5. **Balance Sheet** - Include NWC and Net PP&E
6. **Cash Flow** - Include Change in NWC, Capex, Depr. add-back
7. **Checks** - All validation checks

### Integration Checklist:
- [ ] IS Depreciation = PP&E Schedule Depreciation
- [ ] CF Depreciation add-back = IS Depreciation
- [ ] CF Capex = PP&E Schedule Capex
- [ ] BS Net PP&E = PP&E Schedule Net PP&E
- [ ] BS A/R = NWC Schedule A/R
- [ ] BS Inventory = NWC Schedule Inventory
- [ ] BS A/P = NWC Schedule A/P
- [ ] CF Change in NWC = NWC Schedule Change in NWC
- [ ] BS balances (Assets = Liabilities + Equity)
- [ ] CF ending cash = BS cash change

---

## 📋 Excel Best Practices Checklist

Before submitting, verify:

- [ ] All inputs in **blue font**
- [ ] All formulas in **black font**
- [ ] Years run **left to right** (oldest to newest)
- [ ] Use **absolute references** ($B$1) for assumptions
- [ ] No **#REF!** or **#DIV/0!** errors
- [ ] All quality checks show **"OK"**
- [ ] Consistent number formatting (commas, decimals)
- [ ] Clear section headers with bold formatting

---

## ❌ Common Mistakes to Avoid

| Mistake | Wrong | Correct |
|---------|-------|---------|
| NWC Change Formula | Ending - Beginning | **Beginning - Ending** |
| DIO/DPO Base | Revenue | **COGS** |
| Net PP&E | Gross + Accum Depr | **Gross - Accum Depr** |
| Capex in IS | Include as expense | **Not in IS (only in CF & BS)** |
| 365 Reference | Relative (A1) | **Absolute ($A$1)** |

---

## 📊 Answer Key Summary

### TCS Working Capital Metrics (2025):
```
DSO = (A/R ÷ Revenue) × 365 = (80,394 ÷ 289,456) × 365 = 101.5 days
DIO = (Inventory ÷ COGS) × 365 = (88,431 ÷ 179,463) × 365 = 179.7 days
DPO = (A/P ÷ COGS) × 365 = (28,630 ÷ 179,463) × 365 = 58.2 days
CCC = DSO + DIO - DPO = 101.5 + 179.7 - 58.2 = 223.0 days
```

### TCS PP&E Schedule (2025):
```
Gross PP&E = Beg + Capex - Disposals = 3,630,000 + 363,000 - 0 = 3,993,000
Accum Depr = Beg + Depr - Disp Depr = 1,878,000 + 217,800 - 0 = 2,095,800
Net PP&E = Gross - Accum Depr = 3,993,000 - 2,095,800 = 1,897,200
```

---

## 💡 Tips for Success

1. **Color code your model:** Blue = inputs, Black = formulas, Green = cross-sheet links
2. **Build error checks from the start** - don't wait until the end
3. **Use consistent formatting** - makes your model professional and readable
4. **Test your formulas** with simple numbers first
5. **Save frequently** and keep backup copies

---

*Good luck with your practice! 📈*

*Remember: Working capital and Capex modeling are critical skills for understanding a company's cash conversion cycle and capital efficiency. Master these, and you'll build robust financial models!*