Skip to content
SheetRank
Deal FlowAcademyInterview PrepFor ProfessorsResourcesPricingSign InCreate account
Deal FlowAcademyInterview PrepFor ProfessorsResourcesPricing
Sign InCreate account

Circular 3-Statement Model (Average-Balance Interest)

Rebuild the linked three-statement model with interest charged on average balances instead of opening balances, the convention that makes a three-statement model genuinely circular. You'll build the fix the way a bank does with iterative calculation switched off: a five-pass Gauss-Seidel ladder, pass 1 built once with mixed references and passes 2–5 copied down, with a convergence check that measures the residual instead of eyeballing it. Then finish the income statement, cash flow and balance sheet off the converged pass, tie the two constructions of net income and cash to each other, balance the sheet, and price exactly what the convention was worth, since pass 1 of the loop *is* the opening-balance model. Every graded line is the engine's converged circular answer, not a first-pass approximation.

IB · Advanced · about 180 minutes · 52 guided steps

Lesson outline

Income Statement: Operating (Convention-Independent)

  1. Step 1: Customers
  2. Step 2: ARPU
  3. Step 3: Revenue
  4. Step 4: Gross Margin %
  5. Step 5: Gross Profit
  6. Step 6: SG&A
  7. Step 7: EBITDA
  8. Step 8: D&A
  9. Step 9: EBIT

Cash Flow Drivers (needed inside the loop)

  1. Step 10: CapEx
  2. Step 11: Change in NWC

Financing: Opening Balances

  1. Step 12: Cash Opening Balance
  2. Step 13: Revolver Opening Balance
  3. Step 14: Term Debt Balance
  4. Step 15: Term Debt Interest Expense

Circular Interest Solve (5 Gauss-Seidel Passes)

  1. Step 16: Seed — Revolver Ending Balance
  2. Step 17: Seed — Ending Cash
  3. Step 18: Average Cash Balance, Pass 1
  4. Step 19: Average Revolver Balance, Pass 1
  5. Step 20: Cash Interest Income, Pass 1
  6. Step 21: Revolver Interest Expense, Pass 1
  7. Step 22: Net Income, Pass 1
  8. Step 23: Cash Before Revolver, Pass 1
  9. Step 24: Revolver Draw / (Paydown), Pass 1
  10. Step 25: Revolver Ending Balance, Pass 1
  11. Step 26: Ending Cash, Pass 1
  12. Step 27: Iteration Pass 2 (Block Copy)
  13. Step 28: Iteration Pass 3 (Block Copy)
  14. Step 29: Iteration Pass 4 (Block Copy)
  15. Step 30: Iteration Pass 5 (Block Copy)

Convergence

  1. Step 31: Convergence Check (Δ Ending Cash, Last Two Passes)

Income Statement: Below the Line (Converged)

  1. Step 32: Net Interest Expense
  2. Step 33: Pre-Tax Income
  3. Step 34: Taxes
  4. Step 35: Net Income
  5. Step 36: Net Income Tie-Out

Cash Flow Statement

  1. Step 37: Cash Flow: Net Income
  2. Step 38: Add Back D&A
  3. Step 39: Cash Flow: CapEx
  4. Step 40: Cash Flow: Change in NWC
  5. Step 41: Free Cash Flow

Balance Sheet

  1. Step 42: Cash
  2. Step 43: Cash Tie-Out
  3. Step 44: Net Working Capital
  4. Step 45: PP&E
  5. Step 46: Total Assets
  6. Step 47: Term Debt
  7. Step 48: Revolver Ending Balance
  8. Step 49: Equity
  9. Step 50: Total Liabilities + Equity
  10. Step 51: Balance Check

What the Convention Was Worth

  1. Step 52: Δ Net Income vs Opening-Balance Convention