Change one number. Watch the whole plan update.
The story so far Remember your weekend bike tune-ups (Lesson 11.1)? You charge $40, parts cost $10, and your fixed costs (the market stall, tools and insurance) are $300 a month. You did 20 tune-ups in January, and you think you can add 5 more each month. What will you earn by June? You could work it out by hand, but every time a number changes, you'd start over. In a spreadsheet, you build it once and it updates itself. It's the tool every finance job uses every day.
A spreadsheet is a grid of cells that holds numbers and formulas. Excel and Google Sheets are the most common.
Formulas link the cells, so when one input changes, every answer that depends on it changes too. That lets you test "what if" in seconds.
Type your inputs into cells, then write formulas that point to those cells. Copy a formula down a column to repeat it for every month.
Financial analysts build budgets in them. Investment bankers build valuation models. Accountants use them to close the books.
What you're looking at: a tiny spreadsheet. Column A has labels, column B has numbers. The blue numbers are typed in. The green cell, B3, holds the formula =B1*B2 (shown in the bar on top), so it shows $40 × 25 = $1,000.
A grid that does the math for you.
A spreadsheet is a grid. Columns are named with letters (A, B, C…) and rows with numbers (1, 2, 3…). Each box is a cell, and its address is its column then its row: column B, row 1 is B1.
- B1
- Your price: 40.
- B2
- Tune-ups this month: 25.
Formulas start with =
A formula is a calculation inside a cell, and it always starts with an equals sign. Instead of typing numbers into it, you point to other cells. Spreadsheets use * for times, + and − for plus and minus, and the slash key to divide: =C2/B2 means C2B2.
- In B3, type =B1*B2
- It shows $40 × 25 = $1,000, your revenue.
- Change B2 to 30
- B3 updates by itself to $40 × 30 = $1,200. You never touch B3.
Functions: SUM and AVERAGE
A function is a ready-made formula. A range uses a colon: B2:B4 means B2, B3 and B4, three cells. B7:B12 would be six cells.
- B2:B4 hold January to March
- 20, 25 and 30 tune-ups.
- =SUM(B2:B4)
- 20 + 25 + 30 = 75 tune-ups.
- =AVERAGE(B2:B4)
- 753 = 25 a month.
Copying formulas and the $ sign
When you copy a formula, its cell addresses move with it: copy one row down and B4 becomes B5; copy one column right and B4 becomes C4. Usually that's what you want. But some cells, like your price, should never move. A $ sign locks them: absolute reference $B$1 always means B1.
- The sheet
- B1 holds the price, 40. B2 is empty. Tune-ups are in B4 (20) and B5 (25).
- Right: C4 is =B4*$B$1
- Shows $800. Copied down to C5, it becomes =B5*$B$1: 25 × $40 = $1,000.
- Wrong: C4 is =B4*B1
- Copied down, it becomes =B5*B2. B2 is empty, so C5 shows 0.
A model: inputs in, answers out
A financial model keeps its inputs, the numbers you assume, in their own cells, and everything else is formulas. Banks color typed-in inputs blue and formulas black, so anyone can see what's an assumption. Typing a number straight into a formula, like =B7*40, is called hard-coding. If the price changes, that formula stays wrong and nobody notices.
- Inputs (blue)
- Price $40 in B1, parts $10 in B2, fixed costs $300 in B3, tune-ups added each month in B4.
- Tune-ups each month
- January is 20. February is =B7+$B$4, copied down. With 5 added: 20, 25, 30, 35, 40, 45.
- Profit each month
- =B7*($B$1-$B$2)-$B$3. January: 20 × ($40 − $10) − $300 = $300.
- Change B4 to 10
- Tune-ups become 20, 30, 40, 50, 60, 70, and six-month profit jumps from $4,050 to $6,300.
What you're looking at: your tune-up business as a spreadsheet. Rows 1 to 4 are inputs (blue); rows 7 to 13 are formulas. Move the slider to change B4, and tap any cell to see its formula in the bar on top.
Finance functions: NPV
Spreadsheets have functions for finance too, like loan payments and growth rates. The one you'll use most is NPV (Lesson 13.2). Watch out: Excel's =NPV() treats every cash flow as arriving at the end of a year, so money you pay today goes outside it.
- The repair van (Lesson 13.2)
- Costs $10,000 today and brings $3,000 a year for 5 years, at 10%.
- =NPV(10%, 3000, 3000, 3000, 3000, 3000)
- Shows $11,372: what the 5 years of cash are worth today.
- Subtract today's cost
- =NPV(…)-10000 = $1,372, the same NPV you found by hand.
Two minutes. Just listen.
Tap any line to jump to it.
Formula check.
Eight questions about a spreadsheet. Each one shows what's in the cells. One try each.
What you're looking at: made-up spreadsheets. Each lists what's in the cells, then asks one question.
The logic, step by step.
To build any model, take four steps.
Step 1: Put every input in its own cell
- Inputs
- $40 in B1, $10 in B2, $300 in B3, 5 in B4.
Step 2: Write formulas that point to inputs
- Revenue
- =B7*$B$1, not =B7*40.
Step 3: Lock what shouldn't move
- Copied to row 8
- =B8*$B$1.
Step 4: Check one cell by hand
- January profit
- 20 × ($40 − $10) − $300 = $300. The sheet shows $300.
Worked example: is your friend's sheet right?
A friend built the same tune-up plan and says you'll make $5,850 in six months. Here are the facts:
- Their profit formula: =B7*($B$1-$B$2), copied down.
- Tune-ups: 20, 25, 30, 35, 40, 45, which add up to 195.
- Fixed costs: $300 a month.
- Check one cell by hand. January should be 20 × ($40 − $10) − $300 = $300. Their sheet shows 20 × $30 = $600.
- Find the gap. Their formula never subtracts the fixed costs: the -$B$3 is missing.
- Size the mistake. $300 a month × 6 months = $1,800 too high.
- The answer. Six-month profit is $5,850 − $1,800 = $4,050. One missing reference made the plan look 44% better than it is, which is why analysts always check a row by hand.
A honeycomb is a spreadsheet.
Honeybees build comb out of thousands of six-sided wax cells. Each cell holds one thing: honey, pollen, or a young bee growing up. And the bees keep a layout: young bees in the middle, pollen around them, honey on the outside. Any worker can find what it needs.
What you're looking at: an illustration, not data. A piece of honeycomb: pale cells with young bees in the middle, orange pollen cells around them, and gold honey cells at the edges.
Why this sticks. One thing per cell, in a layout everyone knows. A good model works the same way: one input per cell, the same rows every month, so anyone on your team can find the price, change it, and trust the answer.
The page you'd copy down.
- Cell address = column + row: B1. A range uses a colon: B2:B4 = B2, B3, B4.
- Formulas start with =. Point to cells: =B1*B2 = $40 × 25 = $1,000.
- SUM(20, 25, 30) = 75. AVERAGE = 753 = 25.
- Copied formulas move. $B$1 (absolute reference) stays put. =B4*B1 copied down becomes =B5*B2: wrong.
- Model: inputs in blue, formulas in black. No hard-coding (=B7*40).
- Tune-up model: +5 a month gives $4,050 in six months; +10 gives $6,300.
- =NPV(10%, five × 3000) = $11,372; subtract today's $10,000 outside it: $1,372.
- Always check one row by hand.
Fill in the blanks
Grab a friend or a study group.
Split the trip
Plan a group trip in one shared Google Sheet: gas, food and tickets as inputs, and a cell that shows what each person owes. Change the number of people and watch it update.
Break the model
One person hides a hard-coded number or a missing $ somewhere in the tune-up model. The others find it by checking rows by hand.
What if?
Take turns changing one input, like the price or the fixed costs. Before anyone looks, everyone guesses the new six-month profit.
Just you and your money.
These stay on this device. Nobody else sees them.
Exam-style questions.
Written the way a college exam would ask them. Each right answer on the first try is worth 10 xp.
On the job: you're a financial analyst at the bike chain
A coworker built the budget for the chain's new store and went on vacation. Your manager asks you to check it before it goes to the CFO. Here's what you find:
- B1 (price): 45. The chain just raised the tune-up price from $40 to $45.
- C8 (February revenue): =B8*40.
- C13 (total revenue, months in rows 7 to 12): =SUM(C7:C11).
- The store's NPV: =NPV(10%, -50000, 15000, 15000, 15000, 15000, 15000), which shows $6,238. The store costs $50,000 today and brings $15,000 a year for 5 years.
Your task: find the three mistakes, and write the fixed formulas.
See a model answer
Mistake 1, hard-coding: C8 still uses the old $40. Fix: =B8*$B$1, then copy it down the whole column.
Mistake 2, wrong range: C7:C11 skips June in row 12. Fix: =SUM(C7:C12).
Mistake 3, NPV timing: putting the −$50,000 inside NPV treats it as paid a year from now, so it gets discounted. Fix: =NPV(10%, 15000, 15000, 15000, 15000, 15000)-50000, which shows $6,862, not $6,238.
To your manager: three fixes, and the store looks a bit better than the sheet said. I also checked February revenue by hand to make sure the copied formulas match.
Where this leads.
What you're looking at: a map of where this lesson fits in the course. Read it left to right: the lessons it builds on, this lesson in the middle, and where it leads.
- Back · Unit 11Price, costs and profit
- Back · Unit 13Picking projects with NPV
- Back · Unit 15Valuing a company with DCF
- You are hereSpreadsheets for finance
- Coming soon · Unit 17Python for finance