The request
From Laura Bennett, Head of Finance
“Budget season. I need headcount and payroll by department and a sense of what people typically earn. Angela is also asking for a second Support Team Lead because she says her managers are stretched; I won't approve a new manager role without numbers. Can you have something for the budget review in two weeks?”
Your goal
Choose the version of the question you would agree with Laura before touching the data.
This case unlocks when you finish any one of:
The ticket and lesson are open to read now. Solve every level in one of those chapters, in whichever track you prefer, and this case opens.
What Laura gets, and why
The lesson
Why framing comes first
Laura’s message holds two requests: a budget summary and a decision about one role. Before you write a query, agree what an answer would look like. SMART gives the checklist:
- Specific: which people. The 60 current employees in the
employeestable, by department. - Measurable: which numbers. Headcount, total payroll, the median and the mean salary, and direct reports per manager.
- Achievable: can the data answer it? The table has department, title, manager, hire date and salary. It has no workload data, such as tickets per specialist, so it can say how many people a manager has, not how busy they are.
- Relevant: what decision does it feed? The 2026 people budget, and yes or no on a second Support Team Lead.
- Time-bound: a snapshot of today’s staff, answer by the budget review in two weeks.
If you cannot write the question in one sentence that contains all five, it is not ready yet.
Which measures, and why two averages
Payroll is a sum: it is what the budget pays. Headcount is a count. For “what does a typical person earn” there are two candidates. The mean adds every salary and divides; the median is the middle salary. In a team of seven with one head of department, the head’s salary moves the mean a lot and the median hardly at all. Report both, and lead with the median.
Span of control is the number of people who report directly to a manager. It is how you turn “my managers are stretched” into a number you can compare: if Support’s managers have fewer direct reports than other managers, the case for a new lead has to rest on something else.
Who may see it
People data has a rule that sales data does not: a summary can identify a person. A department of two has an average that gives away both salaries to anyone who knows one. Agree up front that department pay figures stay within Finance and HR, and that groups under five are not shown in any wider report.
What the plan looks like
| Step | What you do | Output |
|---|---|---|
| Check | Duplicates, managers not on the list, the CEO, salary range | one row of counts |
| Analyse | Headcount, payroll, mean and median by department; direct reports per manager | two tables |
| Show | One chart: direct reports per manager | a bar chart |
| Recommend | Budget figures and the team lead decision, with caveats | a short paragraph |
Interview note
“How would you check whether a team is understaffed?” comes up in analyst and HR analytics interviews. A strong answer separates structure (span of control, headcount) from workload (volume per person), uses medians for pay, compares like roles rather than whole departments, and mentions privacy: small groups are suppressed in any report that goes beyond HR.
Lantern lit!
Practised: smart goalproblem framingspan of controlmediandata privacy
- Solved
- No hints
- First run