Investment Banking Modeling Test Prep with AI: Take-Home Excel Test Guide 2026

Investment Banking Take-Home Modeling Test

The IB take-home modeling test is where many technically capable candidates fall short — not because they can't model, but because they run out of time, produce an unbalanced balance sheet, or submit work that doesn't reflect professional formatting standards. Claude with ClaudeFinanceLab helps you prepare by drilling the structure, error-checking logic, and output quality that top banks expect.

Model Architecture: Setting Up the File

• "Help me set up the tab structure for an IB take-home modeling test. The company is a mid-cap industrial manufacturer. I have 3 years of historical data and need to project 5 years forward with a DCF. What tabs do I need, in what order, and what goes on each tab? Include: cover page, assumptions, income statement (with historical and projected), balance sheet, cash flow statement, DCF valuation, comps summary, and output/summary. For each tab, what are the 3 most important things to get right?"
• "What is the correct color-coding convention for a bank-quality model? Standard: (1) hardcoded inputs in blue (assumptions like revenue growth %, EBITDA margin, capex/revenue); (2) formulas in black; (3) links to other tabs in green; (4) error checks in red if broken, green if passing. Never hardcode numbers inside formulas — all assumptions in one place. How do you enforce this discipline when time-pressured? What are the exceptions to the rule?"
• "How do I set up a professional header for an IB model? Include: company name, model type (3-statement / LBO / DCF), prepared by, date, confidentiality notice, version number. What font size, font family (use Calibri or Helvetica; avoid Times New Roman), row heights, and column widths should I use? How do I format numbers: (1) thousands or millions as the unit; (2) decimal places for EPS vs. EBITDA; (3) when to use parentheses for negatives vs. minus signs; (4) how to show percentages."

Income Statement Projection

• "I'm building the income statement projection for a manufacturing company. Historical (3 years): revenue $485M, $512M, $541M. Operating structure: COGS ~62% of revenue, SG&A ~14%, R&D ~3%, D&A included in COGS and SG&A. Help me set up a projection with: (a) revenue CAGR assumption (current consensus: 5.5%); (b) gross margin assumption with trend analysis; (c) SG&A as % of revenue with operating leverage thesis; (d) EBITDA margin trajectory. What should the 5-year revenue forecast and EBITDA margin range be, and how do I justify these to an interviewer?"
• "Walk me through how to calculate EBITDA, EBIT, NOPAT, and unlevered FCF from my income statement projection. Starting from EBITDA $97M (Year 1): (1) EBIT = EBITDA − D&A ($24M) = $73M; (2) NOPAT = EBIT × (1 − tax rate 25%) = $54.75M; (3) UFCF = NOPAT + D&A − capex − change in working capital = $54.75M + $24M − $32M − $8M = $38.75M. Now build this into a formula that flows automatically from my income statement and balance sheet assumptions. What is the most common error in UFCF calculation?"

Balance Sheet and the Most Important Error Check

• "Walk me through building the balance sheet projection, step by step. I have: historical balance sheet (3 years), income statement projection, and capex/D&A assumptions. In order: (1) Cash — plug from CFS ending cash; (2) Accounts receivable = revenue × (AR days / 365); (3) Inventory = COGS × (inventory days / 365); (4) Prepaid and other — grow with revenue; (5) Total current assets = sum. (6) PP&E = prior year PP&E + capex − D&A; (7) Goodwill — hold constant unless impaired; (8) Total assets = current + non-current. Now liabilities: (9) AP = COGS × (AP days / 365); (10) Accrued liabilities = SG&A × accrual rate; (11) Current portion of debt = from debt schedule; (12) Long-term debt = from debt schedule; (13) Deferred taxes — simplified: grow at 5% per year; (14) Total equity = prior year equity + net income − dividends + stock compensation. Balance check: total assets = total liabilities + equity."
• "I built a balance sheet that doesn't balance. Total assets: $724M. Total liabilities + equity: $698M. Difference: $26M. Walk me through a systematic debugging process: (1) What lines do I check first? (2) How do I trace the $26M discrepancy? (3) What are the 5 most common sources of balance sheet imbalance in a take-home model? (4) How long should I spend debugging before accepting a known error and noting it explicitly?"
• "Build me a balance sheet error check formula that: (a) compares total assets to total liabilities + equity each year; (b) shows the difference in a 'gap' row; (c) highlights the cell red if the gap exceeds $0.1M; (d) shows 'BALANCED' in green if within tolerance. Also add a model integrity check that confirms: revenue grows each year (no accidental declines), D&A is positive, capex is negative on the CFS. Write the Excel formulas."

Cash Flow Statement

• "Walk me through building the indirect method cash flow statement from my income statement and balance sheet projections. Operating activities: start with net income → add D&A (non-cash) → add stock compensation → adjust for working capital changes (increase in AR is a use of cash, increase in AP is a source) → add deferred tax change. Investing: capex (negative), acquisitions if any. Financing: debt issuance / repayment, dividends, share repurchases / issuances. Ending cash = beginning cash + total cash from all three sections. This ending cash must equal the cash balance on the balance sheet. If it doesn't, why not, and how do I fix it?"
• "I have a circular reference in my model: I use a revolver to fund cash shortfalls, but drawing the revolver adds interest expense, which reduces net income and cash flow, which affects whether I need the revolver. How do I set up iterative calculation in Excel (File → Options → Formulas → Enable Iterative Calculation) and what maximum iterations should I use? What are the risks of circular references in bank models, and how do professional modelers handle this?"

DCF Valuation in a Timed Test

• "I have 30 minutes to build a DCF. Walk me through the fastest correct approach: (1) Pull UFCF from my model — already built above; (2) WACC: use this quick build — risk-free rate 4.2% (10-year Treasury), equity risk premium 5.5%, beta 1.1 (from Barra or comparable companies), Ke = 10.3%. Capital structure: 30% debt at 6% pre-tax, tax rate 25% → Kd after-tax = 4.5%. WACC = 70% × 10.3% + 30% × 4.5% = 8.56%; (3) Terminal value: use Gordon Growth at 2.5% terminal growth — TV = Year 5 UFCF × 1.025 / (WACC − 2.5%); (4) Discount all to present; (5) Add PV of UFCF + PV of TV = enterprise value; (6) Subtract net debt → equity value → price per share; (7) Build a 2×2 sensitivity: WACC on one axis, terminal growth on the other."
• "What sanity checks should I run on my DCF output? (1) Implied EV/EBITDA — does my DCF output imply a multiple that is in the range of comps? If my DCF implies 15x and comps trade at 8-10x, something is off. (2) Terminal value as % of total EV — if TV is >90%, the model is very sensitive to terminal assumptions; verify WACC and growth rate. (3) Implied perpetuity growth rate from exit multiple — if you use an exit multiple for TV, back into the implied growth rate: g = WACC − (UFCF / TV). Is it reasonable (typically 1-3%)? (4) Share price vs. current market — is the DCF premium/discount to market price consistent with your investment view?"

Formatting and Submission

• "I have 20 minutes left before the modeling test deadline. Give me a final submission checklist: (1) Does the balance sheet balance every year? (2) Does ending cash in the CFS match the BS? (3) Are all input cells blue (hardcoded assumptions)? (4) Is the DCF sensitivity table formatted with conditional formatting? (5) Is there a summary tab with key outputs (entry price, IRR, implied multiple, WACC)? (6) Are all tabs named and organized (Cover → Inputs → IS → BS → CFS → DCF → Summary)? (7) Is the file named correctly ([Your Name] — [Company] — Financial Model)? (8) Is the Excel file size under 10MB? What should I do if I have a known error I didn't have time to fix?"
• "How do I write a one-paragraph model assumptions memo to include with my submission? It should cover: (1) revenue growth rationale (top-down market sizing or bottoms-up driver); (2) EBITDA margin trajectory and operating leverage thesis; (3) capex/D&A assumptions; (4) WACC components and source; (5) terminal value methodology and growth rate; (6) key risks to my central case. Keep it under 200 words and write it in a way that shows investment judgment, not just mechanical assumptions."

Related Articles

• Investment Banking Interview Technical Prep
• Three-Statement Financial Model: Full Walkthrough
• LBO Modeling with AI
• WACC Calculation Step-by-Step
• Sensitivity Analysis and Scenario Modeling with AI
• AI for Excel Financial Modeling