The Excel techniques MBA and business students need for case competitions, coursework, and consulting interviews.
=NPV(discount_rate, cash_flow_range) + initial_investment
=NPV(0.10, B3:B7) + B2
-- B2 = initial investment (negative), B3:B7 = years 1-5 cash flows
-- Positive NPV = value-creating investment=IRR(all_cash_flows_including_initial)
=IRR(B2:B7)
-- B2 = negative initial investment, B3:B7 = positive cash flows
-- Compare IRR to cost of capital: if IRR > WACC, invest=initial_investment / annual_cash_flow
-- Simple payback (assumes equal annual cash flows)
-- For uneven cash flows, use a cumulative cash flow column:
=B2+C2 (cumulative cash flow)
-- Payback = when cumulative cash flow turns positiveBreak-even units = Fixed Costs / (Price - Variable Cost per Unit)
=B1 / (B3 - B2)
Break-even revenue = Fixed Costs / Gross Margin
=B1 / ((B3-B2)/B3)Data Tables let you see how NPV or profit changes across a range of assumptions without rebuilding the model.
1. Set up your model with input cells (e.g. discount rate in B1)
2. Create a column of alternative values (e.g. 8%, 10%, 12%, 14%)
3. In the next column, reference your output cell: =NPV_formula_cell
4. Select both columns → Data → What-If Analysis → Data Table
5. Set Column Input Cell = B1 → OK
Excel fills in the output for every scenario automatically-- Two-variable data table: vary two inputs simultaneously
-- Row input = one variable (e.g. price)
-- Column input = another variable (e.g. units sold)
-- Output shows profit/NPV for every combinationAlways build a separate assumptions section at the top of your model with clearly labelled inputs. Judges and interviewers will change your assumptions to test sensitivity — a well-structured model where you can update one cell and see everything recalculate immediately is far more impressive than a hardcoded spreadsheet.
Practice NPV, IRR, and scenario analysis in the Finance & Banking track. Free to start.
Start the Finance & Banking track free →