How to Build a Three Statement Model for a Modeling Test
You build a three statement model in a fixed order: the income statement first, then the balance sheet without cash, then the cash flow statement, whose ending cash links back to the balance sheet so both sides match. That order works because each statement needs a number the one before it produced.
I went to Harvard and worked at McKinsey, and I've run a lot of mock technical interviews with students headed into banking.
A three statement model is the base layer of most valuation work, so once yours balances, see how it feeds into the DCF walkthrough here. Modeling tests usually sit alongside technical and fit rounds, and how to prepare for the full interview process is covered here.
What a Modeling Test Gives You and Expects
A three statement modeling test gives you the company's financials and some of the assumptions in a prompt, and asks for a linked model that balances, usually under a time limit somewhere between 30 minutes and three or four hours. Mergers & Inquisitions (M&I), in its walkthrough of a 90-minute test, sorts the tests into three kinds:
Blank sheet with a time limit: you build everything, including the layout, from an empty workbook.
Template with a time limit: the layout is given and you fill in the formulas.
Take-home: there's no strict limit, and you might get several days or a week.
M&I notes that the longer the time limit, the more the test asks for, so read the prompt's list of outputs before deciding how much to build.
How the Three Statements Link
The three statements connect through six links, and when every link is in place with the right sign, the balance sheet balances on its own.
Net income: It's the last line of the income statement and the first line of the cash flow statement, which starts from accounting profit and adjusts it to cash. It also flows into retained earnings, the profits the company has kept: ending retained earnings equal beginning retained earnings plus net income minus dividends. A company that earns $100 and pays a $25 dividend starts its cash flow statement at 100 and adds 75 to retained earnings.
Depreciation and amortization (D&A): D&A spreads the cost of long-lived assets over the years they're used, so it's an expense that lowers operating profit. No cash leaves the company when it's recorded, since the cash left when the asset was bought, so the cash flow statement adds it back. On the balance sheet it lowers net property, plant, and equipment (PP&E). D&A of $44 lowers operating profit by 44, comes back as 44 on the cash flow statement, and cuts net PP&E by 44.
Capital expenditures (capex): Money spent on long-lived assets is an outflow in the investing section and raises PP&E by the same amount. It skips the income statement in the year it's spent, because its cost reaches profit later, a slice at a time, through depreciation. That gives you the whole PP&E schedule in one line: ending net PP&E equals beginning net PP&E plus capex minus D&A.
Working capital: Receivables (money customers owe the company), inventory, and payables (money the company owes suppliers) turn balance sheet movements into cash. When receivables or inventory grow, cash has gone into the business ahead of profit, so the cash flow statement subtracts the increase. When payables grow, the company is holding on to suppliers' cash for longer, so the cash flow statement adds the increase. Receivables up $10 and payables up $4 show as negative 10 and positive 4 in the operating section.
Debt: Borrowing $100 shows as positive 100 in the financing section and raises debt on the balance sheet by 100, and a repayment reverses both. Interest on that debt is an income statement expense calculated from the balance sheet's debt, which is where a circular reference comes from when interest uses the year's average balance.
Cash: Operating, investing, and financing cash flows add up to the net change in cash. Beginning cash plus that change is ending cash, and ending cash becomes the cash line on the balance sheet.
A correct model balances because every link moves both sides of the balance sheet by the same amount, or moves two assets by offsetting amounts. Profit raises retained earnings on one side and, through the cash flow statement, cash on the other. Capex lowers cash and raises PP&E, so total assets don't change. Borrowing raises cash and debt together. More generally, each line on the cash flow statement is part of the change in exactly one balance sheet line other than cash (D&A and capex together are the change in PP&E, and net income and dividends together are the change in retained earnings), which is why Wall Street Prep calls the cash flow statement "a pure reconciliation of the year-over-year changes in the balance sheet." So once every line is linked, ending cash is exactly the amount that makes assets equal liabilities plus equity. That's why a check row reading zero tells you the links are complete, and a check row that doesn't read zero tells you one of them is missing or has the wrong sign.
Where the Inputs Come From
The historical numbers come from the company's financials, usually its 10-K, where each input sits in a predictable place, and the prompt gives you some of the projection assumptions while you set and justify the rest. When the test hands you the filing, as M&I's 90-minute case does, its walkthrough says entering the historical numbers and setting up the layout and formatting usually takes at least 30 minutes, so knowing where to look saves real time:
Input | Where it is in the 10-K | What to watch for |
The three statements | Item 8, Financial Statements and Supplementary Data | A large company's filing usually has three years of income and cash flow statements and two years of balance sheets |
D&A | Cash flow statement, operating section | Many companies don't show depreciation as its own income statement line, since it sits inside cost of goods sold or operating expenses |
Capex | Cash flow statement, investing section | Usually labeled purchases of property and equipment |
Debt, rates, and repayments | The debt footnote | Each loan's rate and maturity, and the principal due in each of the next five years |
Tax rate | The income taxes footnote | The reconciliation from the 21 percent federal statutory rate to the effective rate |
What drove past results | Item 7, Management's Discussion and Analysis | Management's own account of the year's results, useful when you choose growth and margin assumptions |
Three habits keep the historical numbers traceable once you've entered them:
Collapse the income statement: about a dozen lines (revenue, cost of goods sold, gross profit, SG&A, EBITDA, D&A, EBIT, interest expense, interest income, pre-tax income, taxes, and net income), with a notes column that says what you combined, so you can answer "where did that number come from?" in one click.
One sign convention: show costs as negative numbers, so every subtotal is a plain SUM of the lines above it and the sign stays visible in the cell.
One unit: write "$ in millions" once at the top and keep every number in millions, because a company that reports in thousands will hand you revenue of 1,000,000 where you wanted 1,000.
Before projecting anything, add a check row under the historical balance sheet: total assets minus total liabilities and equity. A filed balance sheet always balances, so anything other than zero means you mistyped a number, and you want to find that before you build three years on top of it.
The Assumptions That Drive the Projections
Every projected number should come from a small assumptions block at the top of the sheet, with each driver based on its historical ratio so you can defend it in a sentence. Compute the ratio for the last historical year first, then choose the projection. Keeping every driver in one block also means you, or anyone reading the model, can change one assumption and watch it flow through.
Revenue growth: this year's revenue divided by last year's, minus one. Project a rate for each year.
Gross margin: gross profit divided by revenue. Hold it flat unless the prompt gives you a reason to move it; cost of goods sold is then revenue times one minus the margin.
SG&A: a percentage of revenue, since operating costs tend to scale with the business.
D&A: a percentage of revenue is the quick version. A percentage of beginning PP&E is closer to how depreciation actually works, since it comes from the assets the company owns.
Capex: a percentage of revenue. Read it next to D&A: capex above D&A means the asset base is growing, and capex below it means the asset base is shrinking.
Working capital days: each balance becomes a number of days of revenue or cost, held steady and turned back into a balance (table below).
Tax rate: the effective rate, applied to pre-tax income. Wrapping the tax formula in MAX(0, ...) keeps taxes from turning negative in a loss year.
Interest rates: the rate on debt from the debt footnote or the prompt, and a small rate on cash if the prompt gives one.
Debt repayment and dividends: scheduled repayments from the debt footnote's maturity table, and dividends as a share of net income (the payout ratio) or a flat amount.
Minimum cash: the cash the company keeps on hand to run the business. The revolver in the build exists to protect this number.
Ratio | Formula, using year-end balances | Projected balance |
Days sales outstanding (DSO) | Receivables divided by revenue, times 365 | Revenue times DSO, divided by 365 |
Days inventory outstanding (DIO) | Inventory divided by cost of goods sold, times 365 | Cost of goods sold times DIO, divided by 365 |
Days payable outstanding (DPO) | Payables divided by cost of goods sold, times 365 | Cost of goods sold times DPO, divided by 365 |
Year-end balances keep the projection formula simple; some guides average the opening and closing balances instead. Receivables of 125 on revenue of 1,000 give a DSO of 45.6 days, so 45 is an easy assumption to defend.
Build the Model in This Order
Build in six steps, and get the check row to zero after linking cash in step 5, before you add the revolver in step 6. Cash and the revolver come last because both are calculated from every other line in the model.
Step 1, the income statement: Revenue is last year's revenue times one plus growth, and each cost line comes from its assumption, down to EBIT. Calculate interest expense on last year's ending debt and interest income on last year's ending cash, because both are already known, so the model has no circular reference. Then pre-tax income, taxes, and net income, with a dividends line underneath so retained earnings has what it needs.
Step 2, the PP&E and working capital schedules: Two small tables under the income statement. The PP&E schedule is the roll-forward from the capex link: beginning PP&E plus capex minus D&A. The working capital schedule turns the days assumptions into receivables, inventory, and payables balances, with a line under each for the change from last year, which the cash flow statement will use.
D&A arrives already negative from the income statement (=E27), so ending PP&E is a plain SUM, and the working capital changes in rows 46 to 48 carry the signs the cash flow statement needs.
Step 3, the balance sheet, except cash and the revolver: Receivables, inventory, PP&E, and payables link to the schedules. The term loan is last year's balance minus this year's scheduled repayment. Common stock stays flat unless the prompt has buybacks or new shares. Retained earnings is last year's balance plus net income minus dividends. Leave cash and the revolver blank, because cash comes from the cash flow statement and the revolver from the rows you add in step 6.
Step 4, the cash flow statement: The operating section is net income, plus D&A, minus the increase in receivables and inventory, plus the increase in payables. The investing section is capex as a negative number. The financing section is the term loan repayment, dividends, and the revolver draw and repayment, which stay at zero until step 6. The three sections add up to the net change in cash.
No number here is typed: net income comes from the income statement, D&A is the income statement line with its sign flipped, and ending cash is beginning cash plus the net change.
Step 5, cash back to the balance sheet: Ending cash is last year's cash plus the net change, and the balance sheet's cash cell links to it. Add the check row (total assets minus total liabilities and equity) and confirm it reads zero in every year before you build anything else.
Both totals are plain SUMs of the rows above them (rows 51 to 54 and 56 to 60), so the check is a single subtraction, =E55-E61, copied across.
Step 6, the revolver: A revolver is a credit line the company draws when cash runs short and repays when cash comes back, so in the model its job is keeping cash from falling below the minimum. It takes four rows. Cash before the revolver is beginning cash plus every cash flow line except the revolver; keep it as its own row, so the draw and repayment formulas stay short enough to check. The draw is =MAX(0, minimum cash minus cash before revolver), which borrows exactly the shortfall and nothing when there isn't one. The repayment is =MIN(beginning revolver balance, MAX(0, cash before revolver minus minimum cash)), which pays down with any cash above the minimum but never more than is owed. The ending revolver balance is beginning plus draw minus repayment, and it links to the balance sheet, while the draw and repayment link into the financing section. Then read the check row again.
Row 85 pulls cash before the revolver from the memo row at the bottom of the cash flow statement (=E81), so the draw reads only E84 and E85, and the repayment adds just the beginning balance in E86.
Cash and the revolver are often called plugs because you don't project either one directly: cash is whatever the cash flow statement produces, and the revolver is whatever borrowing keeps cash at the minimum. A number typed in to force the check row to zero is a different kind of plug, since it hides a broken link, while cash and the revolver depend on every link working. If you run out of time with the check off, a note such as "check off by 20.9 in 2026 and growing, likely the dividends link" is honest and shows you know where the problem is.
A Worked Example That Balances
Here's the whole build on a made-up company, in $ millions, with one historical year and three projected years, laid out as it would be in Excel, so the cell references in the formulas above match the row numbers here. Figures are rounded to one decimal for display while the model keeps full precision, so re-adding a column by hand can land 0.1 off. In this example, the 2027 expansion capex leaves cash 33.6 below the 50.0 minimum, so the revolver draws exactly 33.6 and repays 19.0 of it in 2028.
Blue cells are typed assumptions, the 2025A column holds the historical ratio each one is based on, and the circularity toggle at the top is set to 0.
The 2027 jump in capex is an expansion year, there so you can watch the revolver work. The 20 percent tax rate keeps the arithmetic easy to follow; in a test, use the company's effective rate.
Costs show as negatives in parentheses, and only the 2025A column is typed in blue, so every projected number is a formula.
Cash sits at the 50.0 minimum in 2027 and 2028 because the revolver is doing its job, and the check row reads 0.0 in every year.
Beginning and ending cash sit under the net change, so the 2026 ending cash of 86.9 is visibly 80.0 plus the 6.9 increase.
2026, step by step: Revenue is 1,000 times 1.10, or 1,100. Cost of goods sold at 60 percent is 660, leaving gross profit of 440. SG&A at 25 percent is 275, so EBITDA is 165, and D&A at 4 percent is 44, so EBIT is 121. Interest expense is 6 percent of the 300 term loan, or 18.0, and interest income is 2 percent of the 80 of beginning cash, or 1.6, so pre-tax income is 104.6. Taxes at 20 percent are 20.9, net income is 83.7, and dividends at a 25 percent payout are 20.9. On the balance sheet, receivables are 1,100 times 45 divided by 365, or 135.6; PP&E is 400 plus capex of 55 minus D&A of 44, or 411.0; and retained earnings is 255 plus 83.7 minus 20.9, or 317.8. The cash flow statement then turns that 83.7 of net income into a 6.9 increase in cash, as the bridge below shows line by line, so cash ends the year at 86.9. That's above the 50 minimum, so the revolver stays at zero.
Operations turn 83.7 of net income into 112.8 of cash, then capex, the term loan repayment, and dividends take 105.9 back out, leaving cash 6.9 higher at 86.9.
2027, the revolver draws: Capex jumps to 142.6 (12 percent of 1,188). Cash before the revolver is 86.9 plus 125.3 from operations, minus 142.6 of capex, 30.0 of repayment, and 23.2 of dividends, which leaves 16.4. That's 33.6 below the minimum, so the MAX formula draws 33.6 and ending cash lands at exactly 50.0.
2028, the revolver repays: Cash before the revolver is 69.0, which is 19.0 above the minimum, so the MIN formula repays 19.0 of the 33.6 balance and leaves 14.6 outstanding. That year's interest expense, 16.4, is 6 percent of 273.6: the 240 term loan plus the 33.6 revolver balance from the end of 2027, so last year's draw shows up in this year's interest just as the links say it should.
Interest and the Circular Reference
The most accurate interest calculation uses the average of beginning and ending debt, since the company carries different balances through the year, but that creates a loop. Ending debt includes the revolver, the revolver depends on cash before the revolver, that cash depends on net income, and net income depends on interest. In Excel that's a circular reference, a formula that depends on its own result. Interest on average debt makes the model circular, so in a timed test either calculate interest on beginning balances or build a toggle that can switch the circularity off.
Excel can solve the loop if you turn on iterative calculation (File, Options, Formulas, "Enable iterative calculation"). It then recalculates until the numbers stop changing, by default for up to 100 passes or until the change falls below 0.001. The risk is an error value. Once a #REF! or #VALUE! appears in a cell inside the loop, it flows into every other cell in the loop, and the model may not recover even after you fix the cell that caused it.
The toggle, which Wall Street Prep's debt schedule guide calls a circuit breaker, breaks the loop on demand. Put a cell labeled "Circularity" at the top of the assumptions block with a 1 or a 0, and write interest expense as the rate times IF(toggle=1, AVERAGE(beginning debt, ending debt), beginning debt). With a 1, interest uses the average and the model is circular. With a 0, interest uses only last year's known balance, so no circular reference exists. When errors flood the model, set the toggle to 0, let them clear, fix the broken cell, and set it back to 1. Calculating interest income on beginning cash, as the worked example does, keeps the cash side out of the loop.
The toggle sits at the top of the assumptions block, and with a 0 in D5 the IF picks beginning debt, so the model has no circular reference.
The average changes the answer only a little. In the worked example, 2026 interest expense is 18.0 on beginning debt and 17.1 on the average (6 percent of 285, since the term loan falls from 300 to 270 during the year), and net income rises from 83.7 to 84.4. Across all three projected years, the toggle moves interest expense by at most 1.5 and net income by at most 1.2, and the balance check reads zero either way. That's why beginning-balance interest is a sensible default for a timed test: the average buys a small gain in accuracy at the cost of a model that can fill with errors when you have the least time to fix it. If you do use the average, build the toggle first and set it to 1 only after the check row reads zero.
How to Read the Balance Check
The shape of the balance check tells you what broke: a constant difference means something broke once, and a growing difference means a recurring link is missing or has the wrong sign.
In 2026 the two gaps are 12.0 and 44.0, but by 2028 the rising one is more than ten times the flat one, which is why the trend tells you more than any single year.
A constant difference: If the check reads 12.0 in 2026, 2027, and 2028, something happened once and the model carried it forward. Breaking Into Wall Street's troubleshooting guide says a constant difference usually means an item wasn't linked in a single year. In the worked example, typing 2025 net PP&E as 412.0 instead of 400.0 produces the same pattern, and the historical check row would show it first. Other causes are a first-year link pointing at the wrong starting cell, or a one-time item that reached one statement but not the other.
Every input except the blue 412.0 in net PP&E matches the working model, and total liabilities and equity don't move at all, so the extra 12.0 sits on the asset side from the 2025A column on.
A growing difference: If the check reads 44.0, then 91.5, then 141.9, a flow that happens every year isn't reaching both sides. Compare the first year's difference with the lines on the cash flow statement: 44.0 is 2026's D&A, and the growth matches each year's D&A, so the PP&E schedule isn't subtracting it. When the first year's difference equals a line item, that line is missing a link. When it's exactly double a line item, that line probably has the wrong sign, because a flipped sign moves cash by twice the amount: adding 20.9 of dividends on the cash flow statement where you should subtract them leaves cash 41.8 too high.
Every line except net PP&E, total assets, and the check row matches the working model, so the gap lives in the PP&E schedule, and each year it equals all the D&A that schedule has failed to subtract so far.
A difference that starts late: If the expansion capex in 2027 went into PP&E but never onto the cash flow statement, the check would read 0.0 in 2026, then jump to 142.6 in 2027 and stay there in 2028. That's the constant pattern, starting in the year the one-time item happened.
Once you've read the shape, find the year where the difference first appears, match its size against the lines on the cash flow statement, and press F2 on the suspect cell to see what it references. Fix that one link and watch the check row. If the difference changes size instead of disappearing, there was a second error, and the new number is its size.
Format the Model So a Grader Can Read It
Format the model so anyone can tell typed inputs from formulas at a glance: blue for hardcoded numbers, black for formulas, one formula across each row, and no numbers typed inside formulas. A reader who can see which cells are inputs can check your logic without clicking every cell, and the same habits help you catch your own mistakes.
Colors: blue for hardcoded inputs (every historical number and every assumption), black for formulas, and green for links to other sheets if your model has more than one, which is the convention Wall Street Prep's three statement model guide uses.
One formula per row: write the 2026 formula once and fill it right, so every projected year in a row carries the same formula shifted one column. A year that differs is either deliberate, such as a one-time item, or a cell fixed by hand that never got filled across. Select the row and press Ctrl+\ in Excel for Windows (the shortcut for Go To Special, Row differences) to highlight the cells that don't match the rest.
No hardcodes inside formulas: =E22*1.08 hides the 8 percent where no one can see, change, or check it. =E22*(1+F6), with 8 percent typed in blue in F6, puts it in the assumptions block.
Round only the display: show one decimal with Excel's number format and keep full precision in the cell, because a rounded number copied into another formula is enough to keep the model from balancing.
Errors the Balance Check Won't Catch
A model can balance and still be wrong, so before you hand it in, check for negative cash, a SUM range that misses a line, mixed units or periods, and outputs the prompt asked for that you never built.
Negative cash: a model with no revolver can balance with cash below zero, which a company can't actually have. A revolver, or at least a look at the cash line, catches it.
A SUM range that stops one row short: inserting a line above a total can leave the new line out of it, so check every total after you insert a row.
Mixed units or periods: revenue in thousands next to debt in millions, or a trailing-twelve-month figure in a fiscal year column.
The wrong outputs: five years asked for and three built, or a free cash flow line the prompt wanted and you never calculated. Rereading the prompt's list of outputs at the end catches it.
What to Build First in a Two-Hour Test
In a two-hour test, build the whole linked model first (the three statements, the PP&E and working capital schedules, one term loan, the check row, and the revolver) before anything optional, because a simple model that balances answers the prompt and an unfinished ambitious one doesn't. If time is left after that, add extras in this order:
A free cash flow memo row (cash from operations minus capex).
A short outputs block at the top: final-year revenue, EBITDA, net income, ending cash, and ending debt.
A small sensitivity table showing how a final-year output moves across two or three revenue growth and margin cases.
Unless the prompt asks for them, skip segment builds (use one revenue line), deferred tax and loss carryforward schedules (use one effective rate), multiple debt tranches with different rates, share count roll-forwards and EPS, quarterly periods, and scenario switches, since a sensitivity table answers the same question in less time.
Here's one way to split two hours:
Time | What to do |
0:00 to 0:10 | Read the prompt twice, list every requested output, and sketch the layout: assumptions on top, statements below, schedules at the bottom |
0:10 to 0:30 | Type in the historicals and the assumptions in blue, compute the historical ratios, and add the historical check row |
0:30 to 0:50 | Income statement down to dividends, then the PP&E and working capital schedules |
0:50 to 1:10 | Balance sheet without cash and the revolver, and the cash flow statement with the revolver lines at zero |
1:10 to 1:25 | Link ending cash to the balance sheet, add the check row, and fix it until it reads zero in every year |
1:25 to 1:40 | The revolver, linked into the balance sheet and the financing section, then the check row again |
1:40 to 1:50 | A sanity pass (margins steady, capex and D&A sensible together, cash never below zero, and no debt balance below zero), then a stress test |
1:50 to 2:00 | Formatting pass, outputs block, and a save under the filename the prompt asked for |
The 0:10 to 0:30 slot assumes the prompt gives you summarized historicals. If you have to pull them from a 10-K, take the extra time from the sanity pass and the formatting pass, keep only the outputs block from the extras, and do the stress test only if a few minutes are left.
The stress test is the quickest way to see whether the links are real. Make one large change to an assumption, such as setting revenue growth to zero, predict how the outputs should move, check that they do and that the model still balances, then change it back. Financial Edge recommends an extreme change for this because the effect is easy to see.
Save every fifteen minutes, so a crash near the end costs you a few minutes of work. For a shorter test, compress every stage in the same proportions and drop the stress test and the formatting pass before anything else. For a longer one, give the extra time to whatever the prompt adds.
Practice the Build Against the Clock
Build a model from a blank sheet, timed, more than once before the test, because the links only get fast once you've typed them under a clock and found where your own errors come from. M&I's 90-minute case on Otis Worldwide posts the prompt, the 10-K, and the completed Excel file with no signup, so you can build it against the clock and then compare your file with theirs. After that, pull another company's 10-K and build its model without looking at your first one. If the accounting behind the six links still feels slow, WSG's 50 accounting interview questions drill it one question at a time.
Comments