Financial modeling practice: build a model one piece at a time, with every formula checked

What good financial modeling practice looks like

A model is not one big formula. It is a few dozen small patterns repeated across years: grow a line from an assumption, take a cost as a share of revenue, roll a balance forward, charge interest on the opening balance, discount a cash flow. Someone who can write each pattern without thinking can build a model quickly and audit someone else's. The 45 Modeldrill exercises below cover those patterns in the order a model is built, each on a small sheet with the inputs filled in and the answer cells empty.

So the useful unit of practice is the single piece, done on a live grid, with the answer checked. Three rules make it stick. Write the formula once and fill it across; if you have to retype it per column, the references are wrong. Never type a number into a formula cell; every assumption lives in its own labeled cell. And change the assumption after you finish: if the row does not follow, the formula is not finished.

Start with the revenue build and cost drivers

Every model begins with a few driver rows: revenue grown from a rate or built as units times price, cost of goods sold as a percentage of revenue, gross margin, and year over year growth as a check row. The formulas are short; the skill being practiced is reference discipline, which part of a reference moves when you fill right and which part stays locked.

• Grow a line from one assumption: in C5, =B5*(1+$B$2), then fill right. B5 slides to C5 and D5; $B$2 stays on the growth rate.
• Units times price: =B5*B6, filled right, with the price row itself growing from its own rate.
• Cost as a share of revenue: =B5*$B$2 where B2 holds the COGS percentage; gross margin is =(B5-B7)/B5.
• Year over year growth: =C5/B5-1, starting in the second year, formatted as a percentage.
• One formula for a whole block: =$B5*C$4 mixes a locked column with a locked row, so a single formula fills a units-by-price grid in both directions.

1. Grow revenue from one assumption Free
2. Price times volume Free
3. Cost as a share of revenue Free
4. Gross profit and gross margin Free
5. Year over year growth Free
6. One formula for a whole block Pro
7. Payroll from headcount and raises Pro
8. Revenue mix by product Pro

If the dollar signs still feel random, do these first and repeat them until Fill right never breaks a row. Everything later in the model depends on it.

Practice the schedules: depreciation, interest and tax

Below gross profit the income statement is fed by schedules: depreciation turns capex into an expense over an asset's life, the fixed asset roll-forward links opening PP&E, capex and depreciation to closing PP&E, interest is charged on the opening debt balance so the model does not go circular, and tax applies only to profits, with losses carried forward.

• Straight-line depreciation for an asset bought in a given year: =IF(AND(C$4>=$B5,C$4<$B5+$D5),$C5/$D5,0), where B5 is the purchase year, C5 the cost and D5 the life in years.
• PP&E roll-forward: closing = opening + capex - depreciation, and next year's opening links to this year's closing.
• Interest on the opening balance: =B10*$B$2, where B10 is the opening debt and B2 the rate. Using the closing balance creates a circular reference.
• Tax only on profits: =MAX(0,B12)*$B$3. With a loss carryforward, NOL used = MIN(opening NOL, MAX(0, pre-tax income)) and taxable income = MAX(0, pre-tax income - NOL used).
• From EBITDA to net income: EBITDA - depreciation = EBIT; EBIT - interest = pre-tax income; pre-tax income - tax = net income. Each line is its own row, never one long formula.

1. Fixed and variable operating costs Pro
2. EBITDA and EBITDA margin Pro
3. Straight-line depreciation by asset Pro
4. Depreciation from the year of purchase Pro
5. Fixed asset roll-forward Pro
6. Interest on the opening debt balance Pro
7. Tax only when there is a profit Pro
8. Tax losses carried forward Pro
9. From EBITDA to net income Pro

The depreciation schedule and the PP&E roll-forward each have a reference page on this site at /guide/depreciation-schedule and /guide/ppe-roll-forward, with worked numbers you can copy into a real sheet.

Debt and cash: the part of modeling tests people fail

Loan math and the cash roll-forward are where a model either balances or does not. PMT, IPMT and PPMT handle fixed-payment loans. A revolver draws when cash falls below a minimum and repays from excess cash. Cash flow from operations starts at net income, adds back depreciation and subtracts the increase in working capital, and closing cash links to the next opening cash.

• Monthly payment on a loan: =PMT($B$2/12,$B$3*12,-$B$4), with the annual rate in B2, years in B3 and principal in B4. The minus sign makes the payment positive.
• Interest and principal split: =IPMT(rate, period, periods, -principal) and =PPMT(rate, period, periods, -principal); the two add up to the PMT result every period.
• Revolver change: =MAX(min cash - cash before revolver, -opening revolver). Positive is a draw, negative is a repayment capped at the balance.
• Working capital from days: receivables = revenue / 365 * DSO, inventory = COGS / 365 * DIO, payables = COGS / 365 * DPO; the change in net working capital reduces cash when it grows.
• Debt service coverage: cash available for debt service divided by interest plus scheduled principal, which lenders want above 1.0x with a cushion.

1. Monthly payment for three loans Pro
2. Quarterly amortization table Pro
3. Split payments with IPMT and PPMT Pro
4. Revolver draw and repayment rule Pro
5. Cash and revolver, fully linked Pro
6. Cash flow from operations Pro
7. Working capital from days Pro
8. Cash roll-forward Pro
9. Debt service coverage test Pro

The revolver exercise is the one to repeat before any interview. The reference page at /guide/revolver explains why the formula uses both MAX and a negative opening balance.

DCF practice: from unlevered free cash flow to equity value

The valuation level walks the discounted cash flow chain in the order you build it. Unlevered free cash flow is EBIT times (1 - tax rate), plus depreciation and amortization, minus capex, minus the increase in net working capital. Each year's cash flow is multiplied by a discount factor of 1/(1+WACC)^n, or 1/(1+WACC)^(n-0.5) with the mid-year convention. Terminal value is either the final year's cash flow times (1 + g) divided by (WACC - g), or final year EBITDA times an exit multiple. Enterprise value is the sum, and equity value is enterprise value minus debt plus cash.

• Discount factor: =1/(1+$B$2)^C4, where C4 is the year number; present value is the cash flow times the factor.
• NPV with a time-zero outlay in B5 and later flows in C5:G5: =NPV($B$2,C5:G5)+B5. The Excel NPV function discounts every argument, so year 0 is added outside it.
• Terminal value by perpetual growth: =G8*(1+$B$3)/($B$2-$B$3), discounted with the final year's factor, not one year further.
• Equity bridge: =EV-debt+cash, then divide by diluted shares for a value per share.
• Dated cash flows: =XNPV(rate, values, dates) and =XIRR(values, dates) when the flows are unevenly spaced.

1. Unlevered free cash flow Pro
2. Discount factors and present values Pro
3. Mid-year convention switch Pro
4. NPV, IRR and profitability index Pro
5. Terminal value by perpetual growth Pro
6. Terminal value by exit multiple Pro
7. Enterprise value to equity value Pro
8. Dated cash flows with XNPV and XIRR Pro
9. Return on an investment with irregular dates Pro

Two reference pages cover the inputs this level assumes: /guide/wacc for the discount rate and /guide/cost-of-equity-capm for the equity piece of it. The whole chain, step by step, is at /guide/dcf-model-steps.

Scenarios, sensitivity grids and payback

The last level is what makes a model useful to the person reading it. A scenario switch lets one cell in the assumptions block flip the whole model between base, upside and downside: =CHOOSE($B$1,C5,C6,C7) picks a row by number, and =INDEX(C5:C7,MATCH($B$1,A5:A7,0)) picks it by name. A two-way sensitivity grid shows profit, NPV or terminal value across two inputs at once. Break-even, payback period, CAC payback and cohort payback are the operating metrics that come up in startup and FP&A work.

Payback is the first period in which cumulative cash flow turns positive, interpolated: full years before the sign change, plus the remaining negative balance divided by that year's cash flow.

1. Scenario switch with CHOOSE Pro
2. Scenario lookup by name Pro
3. Break-even by product Pro
4. NPV across discount rates Pro
5. Profit grid by price and volume Pro
6. Terminal value grid by rate and growth Pro
7. Payback period Pro
8. CAC payback and lifetime value Pro
9. Cohort payback by month Pro

A two-week financial modeling practice plan

• Days 1 to 3: model basics. Nine exercises, repeated until every one passes on the first try with Fill right, including the mixed reference block.
• Days 4 to 6: income statement and schedules. Depreciation with a purchase year, the PP&E roll-forward, interest on opening, tax with a loss carryforward, the walk from EBITDA to net income.
• Days 7 to 9: debt and cash. Loan payments and the amortization table, then the revolver and the cash roll-forward until the linked version balances without help.
• Days 10 to 12: valuation. Unlevered free cash flow through the equity bridge, both terminal value methods, then XNPV and XIRR on dated flows.
• Days 13 and 14: analysis, then timed mode across every level. Hints and solutions stay locked until the clock runs out, which is the closest thing to a modeling test.

Keep the printable cheat sheet at /financial-modeling-formulas-cheat-sheet beside you in the first week and try not to look at it in the second. If the Excel functions themselves are the obstacle, our sister site Formuladrill drills them on their own.