# Lecture 8: Three-Statement Integration — Practice Exercises Guide

## Overview
This guide contains 5 hands-on exercises to master three-statement integration. Each exercise has a small CSV dataset in `practice-files/lecture-08/` and condensed instructions below. Full, step-by-step solutions are hidden by default — click "Show solution" to reveal.

---

## Files (data & templates)
- Data files (one per exercise):
  - `lecture-08-sec01-trace-revenue.csv` (Section 1: trace a revenue shock)
  - `lecture-08-sec02-working-capital.csv` (Section 2A: working capital)
  - `lecture-08-sec02-depreciation.csv` (Section 2B: depreciation / PP&E)
  - `lecture-08-sec03-rollforwards.csv` (Section 3: roll-forwards)
  - `lecture-08-sec04-cf-construction.csv` (Section 4: cash-flow construction)
  - `lecture-08-sec05-balancing-revolver.csv` (Section 5: balancing & revolver)
- Guide: this file (`Lecture-08-Practice-Exercises-Guide.md`)
- Archive (original unguided file): `archive/lecture-08-integration-data.csv`

---

## Exercise 1: Trace a Revenue Shock (Section 1)
Objective: Trace how a revenue shock flows through IS → BS → CF and impacts ending cash and retained earnings.

Instructions:
1. Open `lecture-08-sec01-trace-revenue.csv` in Excel.
2. Build an Income Statement (Revenue → COGS → Gross Profit → EBIT → EBT → Tax → Net Income) for 2023 + forecasts.
3. Forecast AR, Inventory, AP from DSO / DIO / DPO ratios and compute ΔNWC for each year.
4. Build Operating CF = Net Income + Depreciation − ΔNWC; Investing CF = −CapEx; Financing CF = Borrowings − Repayments − Dividends.
5. Compute Ending Cash and Retained Earnings (Prior_RE + Net_Income − Dividends).
6. Introduce a revenue shock (e.g., set 2024E revenue = Revenue × 0.9). Recalculate and observe differences in Ending Cash and RE.

<details>
<summary>Show solution (condensed)</summary>

- Compute AR = Revenue × (DSO / 365). Example: 2023 AR ≈ 500×(58/365) ≈ 79.45 → 80 (matches beginning).
- Inventory = COGS × (DIO / 365); AP = COGS × (DPO / 365).
- ΔNWC = Current NWC − Prior NWC. Operating CF = Net Income + Depreciation − ΔNWC.
- CapEx = Revenue × CapEx_Pct (if using % assumption) → Investing CF = −CapEx.
- Dividends = Net Income × Dividend_Payout → Financing CF includes dividends and net debt movements.
- Ending Cash = Beginning Cash + CFO + CFI + CFF. Retained Earnings = Prior_RE + Net_Income − Dividends.
- After applying a −10% revenue shock in 2024E recalc all derived items; you will observe lower Net Income, higher cash outflow from ΔNWC (if AR falls slower), lower Ending Cash and lower RE.

</details>

---

## Exercise 2A: Working Capital Forecast (Section 2)
Objective: Forecast AR, Inventory and AP using ratios and compute cash impact.

Instructions:
1. Open `lecture-08-sec02-working-capital.csv`.
2. Compute for each year:
   - AR = Revenue × (DSO / 365)
   - Inventory = COGS × (DIO / 365)
   - AP = COGS × (DPO / 365)
3. Compute change = Current − Prior and assign sign for CF:
   - Δ Asset ↑ → cash outflow (subtract)
   - Δ Liability ↑ → cash inflow (add)

<details>
<summary>Show solution (condensed)</summary>

Example (2024E):
- AR = 540 × (58/365) = 85.8 → ΔAR = 85.8 − 79.45 = +6.35 → cash outflow
- Inventory = 320 × (73/365) = 64.0 → ΔInv = 64.0 − 60 = +4.0 → cash outflow
- AP = 320 × (49/365) = 42.97 → ΔAP = 42.97 − 40 = +2.97 → cash inflow

Operating CF change from NWC = −ΔAR − ΔInv + ΔAP (apply signs consistently).

</details>

---

## Exercise 2B: Depreciation & PP&E (Section 2)
Objective: Validate D&A and build a PP&E roll-forward.

Instructions:
1. Open `lecture-08-sec02-depreciation.csv`.
2. Confirm Depreciation (given) and compute CapEx if needed.
3. PP&E roll-forward: Ending PP&E = Beginning PP&E + CapEx − Depreciation.
4. Verify Depreciation appears on IS (expense), on BS (reduces Net PP&E via Accumulated Depreciation) and on CF (added back).

<details>
<summary>Show solution (condensed)</summary>

- Use the provided Beg_PP&E_Gross and Accumulated_Depreciation to confirm Net PP&E beginning (Gross − Accum).
- Example 2024E: CapEx = 55; Depreciation = 27 → Ending Net PP&E = 500 + 55 − 27 = 528.
- Depreciation recorded as an expense reduces EBIT/NI; add back in CFO to reverse non‑cash impact.

</details>

---

## Exercise 3: PP&E & Retained Earnings Roll-Forwards (Section 3)
Objective: Build roll-forwards for PP&E and Retained Earnings.

Instructions:
1. Open `lecture-08-sec03-rollforwards.csv`.
2. PP&E roll-forward: Beg PP&E + CapEx − Depreciation = End PP&E.
3. Retained Earnings roll-forward: Beg RE + Net Income − Dividends = End RE.
4. Confirm checks: PP&E roll-forward equals reported Net PP&E; RE roll-forward equals BS RE.

<details>
<summary>Show solution (condensed)</summary>

- Example 2024E: PP&E: 500 + 55 − 27 = 528 (verify with CSV/workbook).
- Retained Earnings example: 300 + 80 − (80×20%) = 300 + 80 − 16 = 364.
- Ensure consistency: use the same Net Income cell for IS → RE roll-forward and CF starting point.

</details>

---

## Exercise 4: Cash Flow Statement Construction (Section 4)
Objective: Build the full Cash Flow statement using the indirect method and reconcile Ending Cash.

Instructions:
1. Open `lecture-08-sec04-cf-construction.csv`.
2. For each year compute:
   - Operating CF = Net Income + Depreciation − ΔAR − ΔInventory + ΔAP
   - Investing CF = −CapEx (plus asset sales if present)
   - Financing CF = Borrowings − Repayments − Dividends
   - Total CF = CFO + CFI + CFF
   - Ending Cash = Beginning Cash + Total CF
3. Verify Ending Cash flows back to BS cash.

<details>
<summary>Show solution (condensed)</summary>

- Example 2024E (from CSV): CFO = 80 + 27 − 10 − (−5) + 10 = 112 (note ΔInventory negative equals inflow).
- Investing CF = −55.
- Financing CF = 15 − 20 − 16 = −21.
- Total CF = 112 − 55 − 21 = 36 → Ending Cash = 50 + 36 = 86 (compare to workbook values; slight rounding differences possible).
- Always compare CF ending cash to BS cash and include a Cash Check row.

</details>

---

## Exercise 5: Balance Sheet Check & Revolver Logic (Section 5)
Objective: Implement cash checks and a simple revolver (automatic draw/paydown) to keep cash at or above minimum.

Instructions:
1. Open `lecture-08-sec05-balancing-revolver.csv`.
2. Compute Cash_Before_Revolver = Beginning_Cash + CFO + CFI + CFF.
3. Revolver Draw = MAX(0, Min_Cash − Cash_Before_Revolver).
4. Revolver Paydown = MIN(MAX(0, Cash_Before_Revolver − Min_Cash), Revolver_Beg).
5. Ending Revolver = Revolver_Beg + Draw − Paydown.
6. Ending Cash = Cash_Before_Revolver + Draw − Paydown (should equal BS cash).
7. Add Balance Check row: Total Assets − Total Liabilities − Total Equity = 0 and Cash Check row: CF_Ending_Cash − BS_Cash = 0.

<details>
<summary>Show solution (condensed)</summary>

- Compute Cash_Before_Revolver first. If it's below Min_Cash, draw the difference up to Revolver limit.
- Example 2024E: Cash_Before_Revolver = Beginning_Cash + CFO + CFI + CFF → then Draw = MAX(0, Min_Cash − Cash_Before_Revolver).
- Use Beg_Revolver when calculating the maximum available paydown.
- Verify both Balance Check and Cash Check rows are zero; if not, trace sign errors or missing links.

</details>

---

## Excel Tips & Quick Checks
- BS Check: `=Total_Assets - Total_L&E` → should equal 0.
- Cash Check: `=CF_Ending_Cash - BS_Cash` → should equal 0.
- Revolver Draw: `=MAX(0, Min_Cash - Cash_Before_Revolver)`
- Revolver Paydown: `=MIN(MAX(0, Cash_Before_Revolver - Min_Cash), Beg_Revolver)`
- Conditional pass/fail: `=IF(ABS(Check)<0.01,"✓","✗ ERROR")`

---

## How the hidden solutions work
- The lecture pages include a "Show / Hide Solution Steps" button that reveals a condensed solution for the corresponding exercise. This guide also uses collapsible sections (details/summary) so students who prefer to self-check can reveal the worked steps.

---

## Next actions for instructor (if you want full .xlsx templates/solutions)
- I can generate .xlsx templates for each exercise (student workbook with input area + empty formula cells) and completed solution workbooks. Confirm if you want templates (recommended) and/or completed solutions.

---