Your first budget question as a new manager
Being promoted to team lead usually comes with a surprise: you are suddenly responsible for a budget. Sooner or later, often in your first month, someone from finance asks you to confirm your people costs for next year, or your own manager asks whether the team can afford another hire. Most new managers know everyone’s job but not what the team actually costs. They may know a few salaries, and they assume that the salaries are the cost. They are not. On top of each salary, the employer pays on-costs such as payroll taxes, pension contributions and benefits, and those can add a large share again.
This free lesson shows you how to build a clear team cost budget in Excel: one row per person with salary and full-time equivalent (FTE), on-costs calculated from a single rate, the total cost per person and per team, and finally the cost per FTE, the figure you need whenever you argue for a new role. It takes about fifteen minutes and only uses multiplication, addition and SUM, but it teaches the single most important habit in any budget sheet: keep an assumption in one cell and refer to it everywhere.
We follow Maya, who has just been promoted to lead the nine-person customer service team at Brightwater Energy, a fictional utility. The salaries and the 22% on-cost rate are examples. Real on-cost rates depend on your country, your pension scheme and your benefits, so always ask HR or finance for the rate to plan with. The interactive lab above uses exactly the same numbers.
The core concepts: on-costs, FTE and cost per FTE
On-costs are the employer’s costs on top of gross salary. Depending on where you work they can include employer social-security or payroll taxes, pension contributions, health or accident insurance and other benefits. Finance teams usually simplify this into one planning rate, for example 22% of salary. Then on-costs = salary × on-cost rate and total cost = salary + on-costs, which is the same as salary × (1 + rate).
FTE, full-time equivalent, measures capacity rather than headcount. A full-time employee is 1.0 FTE. Someone working four days a week is 0.8, and someone working three days is 0.6. Maya’s team has nine people but only 8.4 FTE, because Dev and Hugo work part-time. In the lab, the salary column already shows what each person is actually paid for their hours, so you do not multiply salary by FTE again.
Cost per FTE = total team cost ÷ total FTE. It tells you what one full-time position costs on average in this team, including on-costs. When you ask for one more advisor, this is the order of magnitude your manager will expect you to state. Dividing by headcount instead of FTE is the classic mistake, and the lab checks for it.
Worked example: what Maya’s team really costs
The table below is the finished budget. The salaries are in column D, the FTE in column C, and the on-cost rate of 22% sits once, in cell B13. Columns E and F are calculated.
| Name | Role | FTE | Salary | On-costs (22%) | Total cost |
|---|---|---|---|---|---|
| Aisha | Senior advisor | 1 | 52,000 | 11,440 | 63,440 |
| Ben | Advisor | 1 | 44,000 | 9,680 | 53,680 |
| Chloe | Advisor | 1 | 43,500 | 9,570 | 53,070 |
| Dev | Advisor | 0.8 | 34,400 | 7,568 | 41,968 |
| Elena | Senior advisor | 1 | 54,000 | 11,880 | 65,880 |
| Farid | Advisor | 1 | 42,000 | 9,240 | 51,240 |
| Grace | Coordinator | 1 | 47,000 | 10,340 | 57,340 |
| Hugo | Advisor | 0.6 | 25,800 | 5,676 | 31,476 |
| Isla | Apprentice | 1 | 24,000 | 5,280 | 29,280 |
| Team total | 8.4 | 366,700 | 80,674 | 447,374 |
Look at Aisha in row 2: her salary is 52,000, her on-costs are =D2*$B$13 = 11,440, and her total cost is =D2+E2 = 63,440. The same two formulas are copied down to row 10. Across the team, salaries add up to 366,700, on-costs to 80,674 and the total cost to 447,374. That is 80,674 more than the salary list alone suggests, which is why a budget built on salaries only is always too low.
| Cell | Result | Formula |
|---|---|---|
| E2 (copied to E10) | 11,440 for Aisha | =D2*$B$13 |
| F2 (copied to F10) | 63,440 for Aisha | =D2+E2 |
| C11 | 8.4 FTE | =SUM(C2:C10) |
| D11 | 366,700 | =SUM(D2:D10) |
| E11 | 80,674 | =SUM(E2:E10) |
| F11 | 447,374 | =SUM(F2:F10) |
| B14 Cost per FTE | 53,258.81 | =F11/C11 |
The last step is the cost per FTE: =F11/C11 = 447,374 ÷ 8.4 = about 53,258.81. In a conversation you would round it, for example to “roughly 53,000 a year per full-time position”. Note that this is an average across senior advisors, advisors, a coordinator and an apprentice. For a specific new role, the better estimate is the expected salary for that role × (1 + 22%). For a new advisor on 44,000, that is =44000*(1+$B$13) = 53,680.
How to build it in Excel
- In A1:F1 type the headers: Name, Role, FTE, Salary, On-costs, Total cost. Make them bold (Ctrl+B) and add a bottom border.
- Type the nine team members with role, FTE and salary in A2:D10. Enter FTE as decimals, for example 0.8 for four days a week.
- In A13 type
On-cost rateand in B13 type22%. Typing the percent sign stores it as 0.22 and formats it as a percentage at the same time. - Click E2 and type
=D2*B13. Before pressing Enter, click into B13 in the formula and press F4 once so it becomes$B$13. Press Enter. - In F2 type
=D2+E2and press Enter. - Select E2:F2 and double-click the fill handle. Excel copies both formulas down to row 10, and because of the dollar signs every row still uses B13.
- In A11 type
Team total. Click C11 and press Alt+= to insert=SUM(C2:C10). Do the same in D11 and E11, or select E11 and press Ctrl+R after copying from D11. - In A14 type
Cost per FTEand in B14 type=F11/C11. - Format D2:F11 and B14 with thousands separators (Ctrl+Shift+!) and make row 11 bold. Give B13 a light fill colour to show that it is an input.
- Press Ctrl+` to display the formulas. Every cell in column E should show
$B$13. Press Ctrl+` again to return to the values.
Cost per FTE and the head-count trap
Why does the lab insist on dividing by FTE? Because headcount and capacity are not the same. If Maya divided the total cost by her 9 people, she would get about 49,708 per person. That figure is not wrong as an average per head, but it is misleading when you talk about positions, because two of the nine people are part-time. A full-time role costs more than the head average suggests.
The same logic matters in the other direction. If someone asks you to reduce costs by “one head”, you should ask whether they mean one FTE. Replacing a 0.6 FTE colleague saves much less than removing a full-time position, and the budget sheet shows exactly how much.
Seeing cost by role
Once the per-person rows are in place, SUMIF gives you the cost by role without any extra work: =SUMIF(B2:B10,"Advisor",F2:F10) adds the total cost of every row whose role is Advisor. This is useful when you discuss the team mix, for example whether a second coordinator would take routine work off the senior advisors.
| Role | FTE | Total cost | Formula (Total cost) |
|---|---|---|---|
| Senior advisor | 2 | 129,320 | =SUMIF(B2:B10,"Senior advisor",F2:F10) |
| Advisor | 4.4 | 231,434 | =SUMIF(B2:B10,"Advisor",F2:F10) |
| Coordinator | 1 | 57,340 | =SUMIF(B2:B10,"Coordinator",F2:F10) |
| Apprentice | 1 | 29,280 | =SUMIF(B2:B10,"Apprentice",F2:F10) |
Turning the budget into a hiring request
The budget becomes really useful the first time you need to ask for something. Suppose Maya’s team is struggling with call volumes and she wants one more advisor. A request that says “we are busy, please give us another person” is easy to postpone. A request that shows the current cost, the cost of the new role and the new team total is much harder to ignore, because it answers the first questions finance will ask before they ask them.
- Copy the budget sheet (right-click the sheet tab › Move or Copy › Create a copy) so the current budget stays unchanged.
- In the copy, insert a row above the total row and add the new role, for example
New advisor, 1.0 FTE, with the expected salary for that role, say 44,000 as an example. - Check that the formulas in columns E and F were filled into the new row and that the totals in row 11 now include it. If not, copy E10:F10 down one row and widen the SUM ranges.
- Read off the new totals: the extra cost is the new row’s total cost, here 53,680, and the team grows from 8.4 to 9.4 FTE.
- Put the before and after figures side by side on one small summary: team cost, FTE and cost per FTE. That is the core of your business case. The rest of the argument, such as waiting times or overtime saved, comes from later lessons in this course.
=F11*G11/12, where G11 holds the number of months.
Common mistakes and how to spot them
- Relative reference to the rate.
=D2*B13without dollar signs becomes=D3*B14,=D4*B15and so on when copied down, pointing to empty cells. On-costs then show 0 for everyone except the first row. Check with Ctrl+`. - Typing 22 instead of 22%. The on-costs become 22 times the salary. If the totals look absurd, check that B13 shows 22% and contains 0.22.
- Dividing by headcount.
=F11/9gives 49,708 instead of 53,259. Always divide by the FTE total in C11. - Multiplying salary by FTE again. If the salary column is already the actual pay of a part-timer, multiplying by 0.8 counts the part-time effect twice. Check with HR whether your salary list shows actual pay or full-time equivalent salaries.
- Planning with salaries only. A budget without on-costs is too low by the whole on-cost amount, here 80,674. The difference between D11 and F11 is your check.
- Hard-coding the rate.
=D2*0.22works until the rate changes, and then you have to find and edit nine formulas. Keep the rate in B13. - Totals that miss a new starter. If you add a person in row 11, the totals in the old row 11 move down but their ranges may not include the new row. Insert new rows inside the list, above the total row.
Quick reference: functions and shortcuts used
| Function / shortcut | What it does here | Example |
|---|---|---|
$B$13 (absolute reference) | Keeps the on-cost rate fixed while the formula is copied down | =D2*$B$13 |
| F4 | Cycles B13 → $B$13 → B$13 → $B13 while editing | in E2 |
SUM | Totals FTE, salaries, on-costs and total cost | =SUM(F2:F10) |
| Alt+= | AutoSum: inserts SUM for the range above | F11 |
SUMIF | Total cost for one role | =SUMIF(B2:B10,"Advisor",F2:F10) |
ROUND | Rounds a result for a presentation | =ROUND(B14,-2) |
| Fill handle (double-click) | Copies a formula down to the end of the list | E2:F2 |
| Ctrl+R | Copies the cell on the left to the right | E11 → F11 |
| Ctrl+` | Shows all formulas to check them | whole sheet |
| Ctrl+Shift+% | Percentage format | B13 |
Real-world uses
- Annual budget round. When finance asks for next year’s people costs, update salaries for expected pay rises, apply the on-cost rate and send a total you can explain line by line.
- Business case for a new hire. Add a row for the planned role with its expected salary and FTE, and show your manager the new team total and the cost of the extra position, including on-costs.
- Part-time requests. When someone asks to reduce from 1.0 to 0.8 FTE, update their FTE and salary and see both the saving and the capacity you lose.
- Comparing team structures. Use SUMIF by role to compare the cost of the current mix with an alternative, for example replacing one senior advisor with an advisor plus an apprentice.
- Explaining your cost to others. Cost per FTE gives a single, honest figure to use when another department asks what it would cost to borrow one of your people for a project.
What you will build in the rest of this course
This first lesson is free. The remaining nineteen lessons are part of the full course ($9.90, one-time) and follow Maya’s team through the practical spreadsheets a new manager needs in the first year. Each one is a hands-on lab with step-by-step checks.
Budget vs Actual: Variances and Lines to Review. You compare the budget you just built with actual spending, calculate variances in amount and percent, and flag the lines worth a conversation with finance.
Shift Rota: Coverage per Day with COUNTIF and COUNTIFS. You count who is working each day and spot days where the team is under-staffed.
Holiday Requests: Workdays and Overlap Check. You count the workdays in each holiday request and check whether too many people are off at the same time.
Workload and Capacity: Who Is Overloaded? You compare assigned work with each person’s capacity in hours, using the same FTE idea as in this lesson.
KPI Tracker: Targets, Scores and Traffic Lights. You compare results with targets and turn them into clear green, amber and red signals.
Performance Reviews: Weighted Scores and Rating Bands. You combine several review criteria into a weighted score and map it to rating bands consistently for the whole team.
1:1 Meeting Tracker: Days Since and Overdue Check. You track when you last met each person and flag overdue one-to-ones automatically.
Training Budget: Prioritise Requests Within the Budget. You rank training requests and see which ones fit into the budget you have.
Overtime Cost Check: Hourly Rates, Premium and Budget. You turn salaries into hourly rates, add the overtime premium and compare the cost with the budget.
Hiring Cost and Time to Productivity. You estimate the full cost of a new hire, including the months before they are fully productive, which builds directly on the cost per FTE from this lesson.
Mid-course Project: Monthly Team Report – Unit Costs, Productivity and a Chart. Finally, you build a compact monthly report with unit costs, productivity figures and a chart to share with your own manager.
Summary
A salary list is not a budget. To know what your team costs, add on-costs with a single rate kept in its own cell and referenced with $B$13, calculate total cost per person as salary plus on-costs, add up the team with SUM and divide the total by FTE, not by heads, to get the cost per FTE. These few formulas give you a budget you can defend, update in seconds and use for every staffing decision.
Scroll up to the lab and build Maya’s team budget yourself: fill in the on-costs and totals for every person, the team totals in row 11 and the cost per FTE in B14. Then change the on-cost rate and watch the whole budget update.
Frequently asked questions
How do I calculate employee on-costs in Excel?
Put the on-cost rate in one cell, for example B13, and multiply each salary by it with an absolute reference: =D2*$B$13. Ask HR or finance for the rate that applies to your organisation.
What are on-costs?
On-costs are the costs an employer pays on top of gross salary, such as employer payroll taxes, pension contributions and benefits. What they include and how high they are depends on the country and the employer.
What is FTE and how is it calculated?
FTE means full-time equivalent. A full-time employee counts as 1.0 and a part-timer counts as the share of full-time hours they work, for example 0.8 for four days out of five.
How do I calculate cost per FTE?
Divide the total team cost, including on-costs, by the total FTE, not by the number of people. In the example the formula is =F11/C11.
Why do I need dollar signs in the formula =D2*$B$13?
The dollar signs make the reference to B13 absolute, so it does not move when you copy the formula down. Without them, the lower rows would point to empty cells.
How do I build a simple team budget in Excel?
List each person with role, FTE and salary, calculate on-costs and total cost per row, add the columns with SUM and divide the total cost by total FTE. Keep every assumption, such as the on-cost rate, in its own cell.
Keep going: Excel for New Team Managers: Team Budget, Schedules, KPIs & Reviews
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Budget vs Actual: Variances and Lines to Review
- Shift Rota: Coverage per Day with COUNTIF and COUNTIFS
- Holiday Requests: Workdays and Overlap Check
- Workload and Capacity: Who Is Overloaded?
- KPI Tracker: Targets, Scores and Traffic Lights
- Performance Reviews: Weighted Scores and Rating Bands
- 1:1 Meeting Tracker: Days Since and Overdue Check
- Training Budget: Prioritise Requests Within the Budget
- Overtime Cost Check: Hourly Rates, Premium and Budget
- Hiring Cost and Time to Productivity
- Mid-course Project: Monthly Team Report – Unit Costs, Productivity and a Chart
- Staffing by Hour: Contacts, Handle Time, Advisors Needed & the Gap to the Rota
- Next Year’s Team Budget: a Pay Rise from April, a New Hire from May & Monthly Phasing
- What Meetings Cost: Person-Hours, Money per Year & the Share of Team Time
- Team Absence Rate: Available FTE Days, Monthly Rates, a Rolling Three Months & Year to Date
- Complaint Reasons: Shares, Cumulative Share & the Vital Few That Cause 80%
- Backlog Burn-Down: New vs Closed Tickets, the Backlog Trend & Weeks to Clear
- Fair Weekend Rota: Saturdays Worked, a Fair Share by FTE & Who Is Asked Next
- Final Project: The Quarterly Team Pack – Service Level, Cost per Ticket & Absence vs Targets