Planning the work and building a simple model
Turn the question into a workplan with owners and dates, and build a spreadsheet model in four parts: inputs, calculations, outputs and checks.
Key takeaways
- A workplan turns the question into pieces of analysis, each with an owner and a date.
- Numbers hidden inside formulas: A formula like =B4*0.6*300 hides two assumptions where nobody will find them.
- Write the question as a decision: "Should the hospital group open a day-surgery centre in Pune next year?"
- Split it into three or four smaller questions that together answer it: is there enough demand, can it make money, can we run it, what could go wrong.
- For each smaller question, write your current best guess, the analysis that would test it, the data you need, who does it and by when.
Key idea
A workplan turns the question into pieces of analysis, each with an owner and a date. A simple model turns your assumptions into an answer that anyone can check and change.
Build a workplan from the question
- 1Write the question as a decision: "Should the hospital group open a day-surgery centre in Pune next year?"
- 2Split it into three or four smaller questions that together answer it: is there enough demand, can it make money, can we run it, what could go wrong.
- 3For each smaller question, write your current best guess, the analysis that would test it, the data you need, who does it and by when.
- 4Ask of each piece: could the answer change the decision? If not, drop it.
- 5Agree the plan with your manager before you start, and report against it each week.
| Smaller question | Best guess now | Analysis and data | Owner and timing |
|---|---|---|---|
| Is there enough demand? | Yes, about 20 procedures a day | Referrals from the group's clinics; local population and insurance data | Analyst, week 1 |
| Can it make money? | Yes, if it runs above about 13 to 14 procedures a day | The simple model below: price, costs, volume | Analyst, week 2 |
| Can we run it? | Yes, with 4 more anaesthetists | Staffing plan with the medical director | Manager, week 2 |
| What could go wrong? | Slow ramp-up in year one | Volumes at the group's last two openings | Analyst, week 3 |
A simple spreadsheet model in four parts
- Inputs: every assumption in one place, one number per cell, with its unit and where it came from. Colour input cells so nobody mistakes them for results.
- Calculations: formulas only, which point to the inputs. Never type a number inside a formula; if 0.6 matters, it is an input.
- Outputs: the few numbers the decision needs, at the top or on their own tab: profit, breakeven, payback.
- Checks: cells that must come out true, such as two ways of adding the same total, and a flag that turns red when one does not.
This is how careful analysts work everywhere. The UK government's AQuA Book (2025 edition), its guidance on quality analysis, asks that all analysis has documentation, including a record of the checks that were done to confirm it is right. A model with a checks tab is easier to trust and much easier to hand over.
Worked case
A day-surgery centre: a model in four parts
The prompt
A hospital group in India is testing a day-surgery centre in Pune. Build the simple model: inputs, calculations, outputs and a check.
Inputs: 20 procedures a day, 300 working days a year, INR 60,000 revenue per procedure, variable costs (consumables, drugs, staff paid per case) of 60 percent of revenue, and fixed costs of INR 9.6 crore (96 million) a year. All figures are illustrative.
The structure
- Does the centre make money, and how much room is there?
- Inputs: volume, days, price, variable cost share, fixed costs
- Calculations: procedures, revenue, contribution, profit
- Outputs: profit a year and breakeven volume
- Check: profit worked out a second way
Working it through
1. Procedures a year
20 a day for 300 days.
Procedures a year:20 × 300 = 6,0002. Contribution per procedure
Revenue minus variable costs of 60 percent: 40 percent of INR 60,000.
Contribution per procedure (INR):60,000 × (1 - 0.6) = 24,0003. Profit a year
Contribution on 6,000 procedures minus fixed costs, in INR millions.
Profit a year (INR millions):6,000 × 24,000 ÷ 1,000,000 - 96 = 484. Check: profit a second way
Revenue minus variable costs minus fixed costs must give the same answer.
Revenue minus all costs (INR millions):6,000 × 60,000 ÷ 1,000,000 - 6,000 × 36,000 ÷ 1,000,000 - 96 = 485. Breakeven a year
Fixed costs divided by contribution per procedure.
Breakeven procedures a year:96,000,000 ÷ 24,000 = 4,0006. Breakeven a day
Spread over 300 working days.
Breakeven procedures a day:4,000 ÷ 300 = 13.33
The recommendation
Open the centre: at 20 procedures a day it makes about INR 4.8 crore (48 million) a year, and it breaks even at about 13 to 14 procedures a day, so demand can fall by about a third before it loses money. The model ties out: profit is the same worked out two ways. The main risk is a slow first year, so the next step is to check how fast the group's last two openings reached their volumes.
Risks: A slow ramp-up in the first year keeps volumes below breakeven.
Next steps: Check the ramp-up of the last two openings; Agree the staffing plan with the medical director.
A formula like =B4*0.6*300 hides two assumptions where nobody will find them. When someone changes the days in the inputs, this cell will not move, and the model will quietly give a wrong answer. Put every assumption in the inputs and point to it.
A model's total revenue on the outputs tab does not match the sum of revenue by month. What should happen?
Sources for this lesson (2)
- UK Government Analysis Function and partners, The AQuA Book: guidance on producing quality analysis for government (2025 edition)
- Recognized public explanations of case-interview concepts and terms
My notes on this lesson
0 of 5,000 characters. Saves automatically.
Try the 1 remaining check or drill above to complete this lesson (0 of 1 done).
Spotted something wrong or out of date? Report a mistake. We check every report and correct the page.