1.1 What a financial model is and who uses it
Financial Modeling Basics · 8 min
In this lesson
Before the big model, start small: the owners of Copper Kettle are deciding whether to run a coffee cart at the Saturday farmers market. A financial model turns their assumptions into a profit answer.
- A one-page coffee cart model: cups per day, price, cost per cup, rent, profit
Steps
- Read the assumptions block (blue cells)
- Calculate revenue, cost of goods sold and gross profit
- Subtract cart rent and wages to get monthly profit
- Calculate profit per cup and break-even cups
Key formulas
| Sheet | Cell | Row | Formula |
|---|---|---|---|
| Cart Model | D16 | Cups sold per month | =D7*D10 |
| Cart Model | D17 | Revenue | =D16*D8 |
| Cart Model | D18 | Cost of goods sold | =D16*D9 |
| Cart Model | D19 | Gross profit | =D17-D18 |
| Cart Model | D20 | Fixed costs (rent, wages, permits) | =SUM(D11:D13) |
| Cart Model | D21 | Monthly profit | =D19-D20 |
| Cart Model | D24 | Profit per cup sold | =D21/D16 |
| Cart Model | D25 | Profit margin | =D21/D17 |
| Cart Model | D26 | Break-even cups per day | =D20/((D8-D9)*D10) |
Check your answers
| Item | Cell | Expected |
|---|---|---|
| Cups sold per month | Cart Model!D16 | 3080 |
| Revenue | Cart Model!D17 | $14,630.00 |
| Gross profit | Cart Model!D19 | $10,626.00 |
| Monthly profit | Cart Model!D21 | $6,276.00 |
| Break-even cups per day | Cart Model!D26 | 57.3 |
Chapters
- 00:00 Welcome
- 00:20 The Lesson Guide
- 00:45 What a financial model is
- 01:07 Who uses financial models
- 01:28 The cart model on one page
- 01:50 Reading the sales inputs
- 02:16 Reading the fixed costs
- 02:40 Cups sold per month
- 02:59 Revenue
- 03:15 Cost of goods sold
- 03:34 Gross profit
- 03:53 Never type numbers into formulas
- 04:16 Fixed costs with SUM
- 04:34 Monthly profit
- 04:57 Profit per cup
- 05:14 Profit margin
- 05:34 Break-even cups per day
- 06:08 Break-even explained
- 06:29 Test an assumption
- 06:49 Restore the input
- 07:10 Your turn
Your turn
- Change the price to $5.25 - how much does monthly profit improve?
Practice files
Download Module-01-Financial-Modeling-Basics-Practice-Files.zip from the Practice resources tab and open the folder Lesson 1.1: Lesson_1_1_Practice.xlsx, Lesson_1_1_Solution.xlsx. The Practice file is where you start; the Solution file shows the finished result.
Copper Kettle Coffee Roasters and Bluebird Bakery Co. are fictional businesses; all numbers are for training only.
Practice resources
FREE EXCEL LESSONS BY EMAIL
Not ready to enroll yet?
Join the Academy email list for practical Excel and AI tips, free preview lessons and news when a new course opens. Unsubscribe at any time.