Excel is the quiet backbone of every IB analyst seat, and interviewers use it as a proxy for whether you can actually build and check a model under time pressure, not just talk about one. This deck covers the linking discipline, formatting conventions, and modeling habits that come up constantly: keeping a 3-statement model tied together, handling circular references cleanly, building sensitivity tables, and the small checks that separate a clean model from a fragile one. If you've mostly used Excel for basic formulas so far, start here.
18 cardsInvestment bankingFree, no signup
Card 1 of 18
It’s your first week and a VP tells you to take a first pass at the model for Calder Foods. You’ve never opened one. What actually is a financial model?
A model is a spreadsheet that turns a handful of assumptions into a projected set of financials.
Strip away the formatting and a model is a spreadsheet that takes a small set of assumptions, how fast revenue grows, what margin the business holds, how much it spends on equipment, and projects the company’s financial statements forward five or ten years. Every projected number is a formula that traces back to one of those assumptions, so changing a single input rewrites the entire forecast in front of you. That’s the point of building one: it lets a deal team ask what a business is worth, or how much debt it could carry, and show exactly which assumptions are driving the answer.
Card 2 of 18
How should a model be laid out across tabs, and why does that structure matter more than it sounds like it should?
Inputs on one tab, calculations in the middle, outputs at the end, with information only flowing forward.
The convention is to move left to right: an assumptions tab holding every input the model needs, then the calculation tabs, the three financial statements plus supporting schedules for debt and fixed assets, then an outputs tab carrying the handful of numbers that actually end up in the deck. Information should travel in that one direction, so a calculation tab never reaches forward into an output tab for a value. It matters because models get handed around constantly, and the first thing anyone opening yours will do is hunt for where the inputs live.
Card 3 of 18
Why do banker-built models color hardcoded inputs blue and formulas black, and what does a green cell usually mean?
Color-coding cells by type lets anyone audit a model's logic at a glance, without opening the formula bar.
On the Street, font color is a substitute for documentation: blue marks a hardcoded input typed directly into the cell, black marks a formula that calculates from other cells, and green marks a link pulling in a value from another worksheet or workbook. That convention lets anyone glance at a tab and instantly separate the assumptions someone is allowed to change from the calculations that shouldn't be touched by hand. Breaking it, a blue cell that's secretly a formula, or a formula colored blue, is one of the fastest ways to look sloppy in front of an MD, because it hides where the model's real control panel is.
Card 4 of 18
Your associate opens your revenue build for Ferrow Retail and finds =1200*1.05 typed into a cell. The number it produces is correct. Why is she still unhappy?
A number typed inside a formula is an assumption nobody can find and nobody can flex.
The 1.05 is a growth assumption, and burying it inside a formula makes it invisible: the cell displays black like every other calculation, so a reviewer scanning the tab has no way of knowing an input is hiding in there. When the MD asks to see 3% growth instead, you have to hunt through formulas one cell at a time rather than retyping one labelled number. The fix is to put 5.0% in its own blue input cell and write =B4*(1+$B$2). Reviewers genuinely go looking for buried hardcodes, and finding one costs you the benefit of the doubt on everything else in the file.
Card 5 of 18
You drag your growth formula across the forecast row and every year after the first comes back as zero, even though the growth rate is sitting right there in cell B2. What happened?
Copying a formula shifts every reference along with it unless dollar signs pin it in place.
Excel references are relative by default, meaning they describe a direction rather than an address: a formula in C4 pointing at B2 really says “two rows up, one column left,” so dragging it right turns B2 into C2, then D2, both of which are empty, and anything multiplied by an empty cell is zero. Typing dollar signs, $B$2, locks the column and the row so the reference stays put wherever the formula lands, and F4 cycles a highlighted reference through the variants. The mixed forms matter too: B$2 locks only the row, which is what you want when the same formula gets copied both across and down a grid.
Card 6 of 18
Why do IB analysts build models almost entirely with the keyboard, and which shortcuts should you know cold?
Mouse-free navigation is faster and steadier, and interviewers watch for it directly in a live Excel test.
Analysts spend most of the workweek inside Excel, so a second saved on every cell-to-cell move compounds fast, and reaching for the mouse breaks the train of thought while building a formula. Interviewers watch how a candidate moves around the sheet almost as closely as whether the final number is right, because mouse-heavy navigation is a tell for inexperience. Know cold: Ctrl+Arrow to jump to the edge of a data block, Ctrl+Shift+Arrow to select to that edge, F4 to cycle a reference between relative and absolute, Ctrl+Alt+V for Paste Special, and Ctrl+` to toggle formula view. These aren't optional polish, they're assumed baseline fluency by the time you're in the room.
Card 7 of 18
Walk me through the errors you run into most often in a model, #REF!, #DIV/0!, #VALUE! and #N/A. What is each one actually telling you?
Each Excel error names its own cause, so the text in the cell tells you where to start looking.
#REF! means a formula is pointing at a cell that no longer exists, almost always because someone deleted a row, a column or a whole tab, and it’s the most destructive of the four because it spreads to everything downstream. #DIV/0! means you divided by zero or by a blank, which turns up constantly in margin and growth lines for a period with no data yet. #VALUE! means you did arithmetic on text, usually a pasted figure carrying a footnote marker or a stray space. #N/A means a lookup ran but found no match, typically a name spelled differently on the two tabs.
Card 8 of 18
In your Northbay Logistics model the company should only repay its loan in years when it actually throws off spare cash. How do you write that, and where do IF statements start to get dangerous?
IF chooses between two outcomes based on a test, but MIN or MAX usually reads better than nesting them.
An IF takes three arguments, a test, the result if the test is true, and the result if it’s false, so =IF(D18>0,D18,0) says sweep the year’s spare cash into the loan if there is any, otherwise pay nothing. You’d also want to cap that payment at the loan still outstanding, and that’s where MIN, which simply returns the smaller of two numbers, is cleaner than a second IF nested inside the first: =MIN(D18,D25). That’s the real point. Two or three IFs stacked inside each other become almost impossible to audit, so reach for MIN or MAX whenever the logic is really just a cap or a floor.
Card 9 of 18
You need to pull a comp company's EV/EBITDA multiple from a Comps tab into your output page, but the row order won't stay fixed as companies get added or re-sorted. What's the safer approach?
INDEX/MATCH or XLOOKUP survives inserted rows and reordering; a hardcoded row reference does not.
Use INDEX/MATCH, or in modern Excel, XLOOKUP, rather than pointing directly at a specific row, because comps lists get re-sorted, re-ranked, or have companies added mid-deal. MATCH, or XLOOKUP's lookup argument, finds the row position of the company name, and INDEX, or XLOOKUP's return array, pulls the multiple from that row in whichever column you choose, so the formula still finds the right value even after the list changes shape. XLOOKUP is the newer single-function version of the same idea, defaults to an exact match, and can search in either direction, but INDEX/MATCH is still worth knowing cold since not every bank's template or Excel version has XLOOKUP available.
Card 10 of 18
You've pulled comps output for Ferrow Retail into a summary page using live formulas, and now need to send a static snapshot to the deal team that won't change later. What's the move?
Paste Special > Values converts live formulas into fixed numbers, severing the link to their source cells.
Copy the range, then Paste Special (Ctrl+Alt+V, or the classic Alt+E, S, V) and choose "Values" instead of a normal paste, which pastes the calculated result as a static number and strips out the underlying formula along with any link to the source cells or tabs. That's the standard way to freeze a snapshot before sending a model externally, turning a live comps pull into a fixed exhibit that won't silently change if someone edits the source tab later, and it's also the way to break a link when copying data between workbooks so the destination file doesn't stay dependent on a file that might get moved or deleted. It gets used constantly inside a model too, for example hardcoding a final negotiated purchase price so it stops moving every time a live market-data pull updates.
Card 11 of 18
Your VP asks you to build the debt schedule for Northbay Logistics. How should each year be laid out so the balances genuinely carry forward?
Every schedule reads beginning balance, plus and minus the period’s activity, equals ending balance.
Every roll-forward uses the same four lines: a beginning balance, the additions during the period, the reductions during the period, and an ending balance that is just those three added together. What makes it a schedule rather than a table is that the next period’s beginning balance is a direct reference to the prior period’s ending balance and nothing else, no retyping and no arithmetic in that cell. The identical block works for debt, where the activity is borrowings and repayments, for fixed assets, where it’s capital spending and depreciation, and for share count. Interviewers like it because any wrong number traces back to one line in one year.
Card 12 of 18
How would you build a clean scenario toggle in a model, say switching between a Base Case and Downside Case set of assumptions, without breaking anything downstream?
A single toggle cell, driven by one dropdown, should be the only thing every downstream formula reads.
The clean pattern is one dedicated toggle cell, built with a data validation dropdown. Number the cases, 1 for Base and 2 for Downside, and every assumption with more than one version can be written as =CHOOSE($B$2,BaseValue,DownsideValue). If you would rather the dropdown showed the words, use =IF($B$2="Base",BaseValue,DownsideValue) instead, since CHOOSE needs a number from 1 upwards and errors on text or on zero. Either way every such formula reads that same single toggle, so flipping the dropdown once cascades through the entire model instantly. The discipline is that no downstream formula ever hardcodes its own scenario choice; everything points back to the one switch, so there's exactly one cell to check to know which case the model is currently showing.
Card 13 of 18
Your associate flags that changing the D&A assumption in your Ferrow Retail model doesn't move the ending cash balance. What's the likely problem?
In a properly linked model, one input change ripples through all three statements to the cash balance.
In a correctly built model, D&A hits the income statement, reducing pre-tax income and taxes, flows into the cash flow statement as a non-cash add-back, and reduces the PP&E balance on the balance sheet; the cash flow statement's ending cash then becomes the balance sheet's cash line. If changing D&A doesn't move ending cash, a link is broken somewhere in that chain, most often the CFS add-back is hardcoded instead of pulling from the income statement, or the balance sheet's cash cell isn't actually pointing at the CFS's ending-cash line. Trace it cell by cell with Ctrl+[ to find precedents rather than guessing, confirming each statement genuinely pulls live from the one before it.
Card 14 of 18
Before sending your Bramwell & Co. LBO model to the MD, how do you sanity-check that it isn't secretly broken?
A visible balance check and error traps catch a broken model before your MD does.
Every balance sheet should carry a check row, typically =Total Assets - Total Liabilities and Equity, wrapped in something like =IF(ROUND(Assets-Liabilities,0)=0,"OK","ERROR") so it's impossible to miss the moment the sheet stops balancing. Beyond that, wrap fragile formulas, lookups, divisions, anything that depends on an input existing, in IFERROR so a missing value produces a visible flag instead of a #N/A or #DIV/0! buried in a cell nobody's looking at, and scan the model with Ctrl+` or Excel's formula-auditing tools before circulating it. The habit that actually matters in an interview is naming this discipline unprompted: a model that runs without errors but was never checked for balance is not considered done on the Street.
Card 15 of 18
While building the debt schedule for the Bramwell & Co. LBO, Excel throws a circular reference warning. Why did that happen, and how do you handle it?
Interest on an average or closing debt balance creates the loop; interest on the opening balance avoids it entirely.
The loop appears when interest expense is calculated on a debt balance that the interest itself helps determine. Compute interest on the average or closing balance for the period and you get a genuine circle: interest reduces the cash flow available to repay debt, the repayment changes the closing balance, and the closing balance changes the interest. Excel cannot resolve that on its own, so it throws a circular reference warning, or returns zeros and errors. It is worth being precise here, because it is a modelling choice rather than something inherent to debt schedules: calculating interest on the opening balance breaks the loop completely, which is why plenty of models do exactly that and never see a circular reference at all. If you do want average-balance interest, turn on iterative calculation (File > Options > Formulas > Enable iterative calculation) so Excel recalculates until it converges, and build in a circularity breaker switch, a cell or checkbox that can force interest expense to zero, so an unrelated error elsewhere does not spin the whole model into cascading #REF or #VALUE errors.
Card 16 of 18
Your MD wants a table showing the Bramwell & Co. LBO's exit IRR across a range of exit multiples and entry leverage levels. What Excel feature builds this in one shot?
A two-variable data table recalculates one output across every combination of two input ranges automatically.
Excel's Data Table, under What-If Analysis, recalculates a single output formula across every combination of two input ranges without you writing a formula in each cell. You lay out a grid with one variable's values down the left column (say, exit multiples), the other across the top row (leverage levels), put the output formula, like IRR, in the top-left corner cell, then select the whole grid and tell Excel which cell each axis should feed as the row input and column input. Excel substitutes each combination in turn and fills the grid, which is also why large data tables can noticeably slow down recalculation. It's the standard way to hand an MD a range of outcomes instead of a single point estimate.
Card 17 of 18
The MD wants to know what gross margin Calder Foods has to reach for EBITDA to break even. The model is built, but it only runs forwards. What do you reach for?
Goal Seek runs a model backwards, solving for the one input that lands an output on a chosen target.
Goal Seek, under What-If Analysis, asks for exactly three things: the cell holding the output, the value you want that cell to equal, and the single input cell Excel is allowed to change. It then iterates until the output hits the target. Think of it as the mirror image of a data table: a data table shows you many outputs across a grid of inputs, while Goal Seek fixes the output and hands you back the input. Two cautions worth saying out loud in an interview, it overwrites the input cell permanently, so note the original value first, and it solves for one variable only.
Card 18 of 18
An analyst rolls off the deal and you inherit her half-finished Ferrow Retail model the night before it goes to the client. How do you get comfortable with it?
Find the inputs first, then trace one important output all the way back to them before touching anything.
Orient before you edit. Read the tab order to see how the model is meant to flow, then find the blue cells, since those are the control panel and they tell you what the builder treated as an assumption. Pick one number that matters, ending cash or the final value, and walk it backwards with Ctrl+[ or Trace Precedents until you land on those inputs, which tells you quickly whether the plumbing is live or partly typed in. F5 then Special then Constants will surface hardcoded values typed loose into cells. It will not catch a number buried inside a formula, because that cell counts as a formula rather than a constant; for those, Ctrl+` toggles Show Formulas so you can scan a row and see them. Finally, flex an assumption whose direction you already know and confirm the outputs move the right way.
Reading is not the same as remembering. Study this deck with scheduled review so the cards come back before you forget them.