Building a Personnel Budget from Scratch
Getting Started
You are a budget analyst for the City of Riverdale, PA. Your task is to build the FY2026 personnel budget for the Department of Public Works (DPW) — 25 employees across four divisions. You will calculate loaded costs, aggregate by division, and project FY2027.
Setup Steps
Two Ways to Check Your Work
- Quick Check: Enter specific computed cell values and get instant feedback with error diagnosis
- Full Check: Upload your completed spreadsheet for a comprehensive scorecard
Each task has a progressive hint system — try to work it out yourself first!
Loaded Cost — The True Cost of an Employee
A budget that shows only salary is misleading. The real cost of employing someone includes health insurance, pension contributions, FICA (Social Security + Medicare), workers' compensation, and unemployment insurance. In public sector budgeting, this total is called the loaded costAlso called "fully-burdened cost" — salary plus all employer-paid benefits or fully-burdened cost.
Why Does This Matter?
If you budget $1 million for 15 employees based on salaries alone, you'll actually need $1.28–$1.32 million. That $280,000–$320,000 gap comes from benefits. Budget directors who forget this end up cutting services mid-year. The benefits rate in column G tells you exactly what multiplier to apply.
=F5*(1+G5)Worked Example — Martinez, Director (DPW-001, Row 5)
What to Do in Excel
=F5*(1+G5) — press Enter. You should see
$130,020.=F5*G5 gives you only the benefits portion, not the total.
=F5*(1+G5) uses relative
references so when you copy down, row 6 becomes =F6*(1+G6), row 7 becomes
=F7*(1+G7), etc. No absolute references needed here — the benefits rate in each employee's own
row is what you want.
H5 (Martinez, Director):
$130,020H19 (Martin, Parks Groundskeeper):
$54,180H29 (Hernandez, Admin):
$70,400
Enter values from these three cells in your spreadsheet (tolerance ±$1):
Division Subtotals — Who Works Where, and What Do They Cost?
Raw employee lists don't help a budget director make decisions. You need costs aggregated by organizational unit. SUMIFSums only the rows where a condition is true — like "Streets" in column C lets you sum a column only for rows matching a condition, such as all employees in the Streets division.
Why Does This Matter?
This is how real budget systems work. Every city's budget software breaks down personnel costs by department, division, program, or location. You're replicating that logic in Excel using SUMIF. Once you can build it by hand, you'll understand what the software is actually doing — and you'll be able to explain it to others.
=SUMIF($C$5:$C$29,"Streets",$E$5:$E$29)=SUMIF($C$5:$C$29,"Streets",$H$5:$H$29)Worked Example — Streets Division (Row 31)
Streets Division has 10 employees (DPW-001 through DPW-010). DPW-009 (Taylor) is a 0.5 FTE, so:
What to Do in Excel
=SUMIF($C$5:$C$29,"Streets",$E$5:$E$29) — Streets FTE total.=SUMIF($C$5:$C$29,"Streets",$F$5:$F$29) — Streets salary total.=SUMIF($C$5:$C$29,"Streets",$H$5:$H$29) — Streets loaded cost total.=SUM(E31:E34), =SUM(F31:F34), =SUM(H31:H34). Save your file.
$C$5:$C$29) to lock the lookup range. This way you can copy formulas from row 31 to rows 32–34
and only need to change the division name string, not the range references.Streets:
— | Parks: — |
Sanitation: — | Admin: —Department Total FTE:
—Enter three values from your subtotal rows (FTE tolerance ±0.1, dollar tolerance ±$1):
FY2027 Projection — What Does Next Year Cost?
Personnel costs almost never stay flat. Collective bargaining agreements, civil service step increases, and cost-of-living adjustments drive salaries up each year. Budget directors must project these increases before the fiscal year begins. This task applies a 3% across-the-board step increase and recalculates loaded costs.
Why Does This Matter?
A 3% step increase sounds small. For a department of this size, it adds approximately $55,000 to the annual personnel budget. That's the point — personnel costs are the largest and stickiest line item in any city budget. Once you hire someone, you're committed to escalating costs for as long as they're employed.
=F5*1.03=I5*(1+G5)Worked Example — Martinez, Director (DPW-001)
What to Do in Excel
=F5*1.03 — copy down through I29.=I5*(1+G5) — copy down through J29.📈 Budget Impact Calculator
Adjust the step increase to see how it affects the department-wide projected budget. This shows why even small percentage changes have large dollar impacts at scale.
=I5*(1+G5).I5 (Martinez FY27 Salary):
$101,455J5 (Martinez FY27 Loaded):
—J36 (Dept FY27 Loaded Total):
—
Enter three values from your FY27 projection (tolerance ±$2):
Visualizing Personnel Costs by Division
Charts in budget documents aren't decoration — they communicate resource allocation decisions to elected officials and the public who can't read a spreadsheet. A well-designed chart of personnel costs by division tells a story about organizational priorities.
⚠ The 3D Pie Chart Rule
Never, under any circumstances, use a 3D pie chart in a professional budget document. They distort proportions, are impossible to read accurately, and signal to your audience that you don't know what you're doing. A horizontal or vertical bar chart is almost always the right choice for comparing budget categories.
What to Do in Excel
Reference Chart — Generate for Comparison
Click below to generate a reference chart showing FY26 and FY27 loaded costs side-by-side for each division. Use the toggle buttons to switch views.
Discussion Questions
Key Points
- Calculate the incremental loaded cost of each upgrade, not just the salary. Taylor (Streets, 0.5 FTE, $46,000 salary, 26% benefits): upgrading to 1.0 FTE adds $46,000 × 1.26 = $57,960 to the annual budget.
- Robinson (Parks, 0.5 FTE, $43,000 salary, 26% benefits): upgrading adds $43,000 × 1.26 = $54,180.
- The FTE decision is often made by department heads or supervisors who only see headcount — not Finance, which sees the loaded cost. This information asymmetry is a common source of budget surprises.
- Consider: is the 0.5 FTE a scheduling choice or a budget constraint? The answer changes who has authority to convert it.
Key Points
- Streets has 10 employees (9.5 FTE) and the highest total loaded cost. Compare the per-FTE loaded cost across divisions — Streets costs more per FTE because it has more Directors and Engineers at the 30–32% benefits tier.
- Streets work (road maintenance, engineering) is inherently labor-intensive and requires credentialed staff. The cost level may be structurally inevitable.
- The more useful question is cost-per-outcome: lane-miles maintained, inspection cycles completed, response time to potholes. Raw cost totals without outcomes data don't tell you if the spending is appropriate.
- Consider: what would happen to service levels if Streets were reduced by 10%? What would be cut first?
Key Points
- A 5% cut on a budget where 73% is personnel means you essentially cannot avoid personnel reductions — cutting the other 27% entirely wouldn't be enough.
- Options: eliminate positions, convert full-time to part-time, freeze hiring (leave vacancies unfilled), defer step increases, reduce overtime.
- Lasswell's framework: "who gets what" reveals values. Protecting higher-paid positions has a larger dollar impact than cutting lower-paid ones — but the reverse (cutting lower-paid positions) has greater equity implications.
- Notice that cutting the two 0.5 FTE positions (Taylor and Robinson) only saves about $112,000 — roughly 6% of the total loaded budget. A 5% cut requires about $92,000 in savings. The math works, but at significant human cost for proportionally little gain.
Key Points
- Higher-tier benefits reflect negotiated packages, pension formulas, and risk-pooling structures — not arbitrary generosity. Directors often have defined-benefit pensions with richer formulas; operations staff may have lower pension tiers but higher workers' comp costs.
- The 6-percentage-point spread (26% vs. 32%) means a Director at $98,500 costs $31,520 more in benefits than if the rate were 26% ($25,610). That's real money.
- Connect to national debates: cities like Chicago and Detroit have faced severe fiscal stress partly because of defined-benefit pension obligations negotiated decades ago. The benefits rate today reflects those commitments.
- Is it fair? That's a normative question. Is it inevitable in the short run? For a city with existing CBAs — largely yes. In the long run, it's one of the levers cities use in contract renegotiations.
Key Points
- The council member is probably conflating headcount (25 people), FTE (23.5), and salary ($1.43M) with loaded cost ($1.85M). Walk through each distinction clearly.
- The answer: "The 25 employees represent 23.5 full-time equivalents, with total annual salaries of $1,434,500. But employer-paid benefits — health insurance, pension, FICA, workers' comp — add another $411,310, bringing the total to $1,845,810."
- This is a communications skill as much as a technical one. Budget analysts must translate spreadsheet numbers into narratives that non-technical decision-makers can act on.
- Bonus challenge: how would you explain to the council member why the loaded cost, not the salary, is the right number to use for budget planning?
Answer Key
Use this to verify individual values after you have attempted each task. Click to reveal.