Tabellab

Team Cost Budget: Salaries, On-Costs and Cost per FTE

Lesson 1 of 20Lab

All lessons
  1. Team Cost Budget: Salaries, On-Costs and Cost per FTELabFree
  2. Budget vs Actual: Variances and Lines to ReviewLab Locked
  3. Shift Rota: Coverage per Day with COUNTIF and COUNTIFSLab Locked
  4. Holiday Requests: Workdays and Overlap CheckLab Locked
  5. Workload and Capacity: Who Is Overloaded?Lab Locked
  6. KPI Tracker: Targets, Scores and Traffic LightsLab Locked
  7. Performance Reviews: Weighted Scores and Rating BandsLab Locked
  8. 1:1 Meeting Tracker: Days Since and Overdue CheckLab Locked
  9. Training Budget: Prioritise Requests Within the BudgetLab Locked
  10. Overtime Cost Check: Hourly Rates, Premium and BudgetLab Locked
  11. Hiring Cost and Time to ProductivityLab Locked
  12. Mid-course Project: Monthly Team Report – Unit Costs, Productivity and a ChartLab Locked
  13. Staffing by Hour: Contacts, Handle Time, Advisors Needed & the Gap to the RotaLab Locked
  14. Next Year’s Team Budget: a Pay Rise from April, a New Hire from May & Monthly PhasingLab Locked
  15. What Meetings Cost: Person-Hours, Money per Year & the Share of Team TimeLab Locked
  16. Team Absence Rate: Available FTE Days, Monthly Rates, a Rolling Three Months & Year to DateLab Locked
  17. Complaint Reasons: Shares, Cumulative Share & the Vital Few That Cause 80%Lab Locked
  18. Backlog Burn-Down: New vs Closed Tickets, the Backlog Trend & Weeks to ClearLab Locked
  19. Fair Weekend Rota: Saturdays Worked, a Fair Share by FTE & Who Is Asked NextLab Locked
  20. Final Project: The Quarterly Team Pack – Service Level, Cost per Ticket & Absence vs TargetsLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

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.

From salary to cost per FTESalary × 22%On-costs→Salary + on-costsTotal cost→Sum of the team447,374→÷ 8.4 FTE53,258.81
The budget in four steps, with Maya’s team totals.

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.

Brightwater Energy customer service team: yearly cost (example figures)
NameRoleFTESalaryOn-costs (22%)Total cost
AishaSenior advisor152,00011,44063,440
BenAdvisor144,0009,68053,680
ChloeAdvisor143,5009,57053,070
DevAdvisor0.834,4007,56841,968
ElenaSenior advisor154,00011,88065,880
FaridAdvisor142,0009,24051,240
GraceCoordinator147,00010,34057,340
HugoAdvisor0.625,8005,67631,476
IslaApprentice124,0005,28029,280
Team total8.4366,70080,674447,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.

The formulas behind the budget
CellResultFormula
E2 (copied to E10)11,440 for Aisha=D2*$B$13
F2 (copied to F10)63,440 for Aisha=D2+E2
C118.4 FTE=SUM(C2:C10)
D11366,700=SUM(D2:D10)
E1180,674=SUM(E2:E10)
F11447,374=SUM(F2:F10)
B14 Cost per FTE53,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.

What the team really costs per year0130,000260,000390,000520,000366,700Salaries80,674On-costs447,374Total team cost
On-costs add 80,674 to a salary bill of 366,700, so the real cost of the team is 447,374 in this example.

How to build it in Excel

  1. In A1:F1 type the headers: Name, Role, FTE, Salary, On-costs, Total cost. Make them bold (Ctrl+B) and add a bottom border.
  2. 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.
  3. In A13 type On-cost rate and in B13 type 22%. Typing the percent sign stores it as 0.22 and formats it as a percentage at the same time.
  4. 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.
  5. In F2 type =D2+E2 and press Enter.
  6. 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.
  7. 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.
  8. In A14 type Cost per FTE and in B14 type =F11/C11.
  9. 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.
  10. Press Ctrl+` to display the formulas. Every cell in column E should show $B$13. Press Ctrl+` again to return to the values.
Try a what-if before your budget meeting. Change B13 from 22% to 24%. The whole team cost rises by 2% of 366,700, which is 7,334, and the cost per FTE rises with it. If finance gives you a new rate, you change one cell, not nine.

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.

Cost per FTE: heads vs FTE015,50031,00046,50062,00049,708÷ 9 heads (wrong)53,259÷ 8.4 FTE (right)
Dividing by heads understates the cost of a full-time position by about 3,550 in this team.

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.

Cost by role
RoleFTETotal costFormula (Total cost)
Senior advisor2129,320=SUMIF(B2:B10,"Senior advisor",F2:F10)
Advisor4.4231,434=SUMIF(B2:B10,"Advisor",F2:F10)
Coordinator157,340=SUMIF(B2:B10,"Coordinator",F2:F10)
Apprentice129,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.

  1. Copy the budget sheet (right-click the sheet tab › Move or Copy › Create a copy) so the current budget stays unchanged.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
Be careful with start dates. A new hire who starts in month four only costs nine months of salary and on-costs in the first budget year. A simple way to show this is a column for months in the year and a formula such as =F11*G11/12, where G11 holds the number of months.

Team manager reviewing a spreadsheet with team budget, rota and KPI figures
The course follows Maya and her Brightwater team from the first budget to a monthly team report.

Common mistakes and how to spot them

  • Relative reference to the rate. =D2*B13 without dollar signs becomes =D3*B14, =D4*B15 and 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/9 gives 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.22 works 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

Quick reference
Function / shortcutWhat it does hereExample
$B$13 (absolute reference)Keeps the on-cost rate fixed while the formula is copied down=D2*$B$13
F4Cycles B13 → $B$13 → B$13 → $B13 while editingin E2
SUMTotals FTE, salaries, on-costs and total cost=SUM(F2:F10)
Alt+=AutoSum: inserts SUM for the range aboveF11
SUMIFTotal cost for one role=SUMIF(B2:B10,"Advisor",F2:F10)
ROUNDRounds a result for a presentation=ROUND(B14,-2)
Fill handle (double-click)Copies a formula down to the end of the listE2:F2
Ctrl+RCopies the cell on the left to the rightE11 → F11
Ctrl+`Shows all formulas to check themwhole sheet
Ctrl+Shift+%Percentage formatB13

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.

  1. Budget vs Actual: Variances and Lines to Review
  2. Shift Rota: Coverage per Day with COUNTIF and COUNTIFS
  3. Holiday Requests: Workdays and Overlap Check
  4. Workload and Capacity: Who Is Overloaded?
  5. KPI Tracker: Targets, Scores and Traffic Lights
  6. Performance Reviews: Weighted Scores and Rating Bands
  7. 1:1 Meeting Tracker: Days Since and Overdue Check
  8. Training Budget: Prioritise Requests Within the Budget
  9. Overtime Cost Check: Hourly Rates, Premium and Budget
  10. Hiring Cost and Time to Productivity
  11. Mid-course Project: Monthly Team Report – Unit Costs, Productivity and a Chart
  12. Staffing by Hour: Contacts, Handle Time, Advisors Needed & the Gap to the Rota
  13. Next Year’s Team Budget: a Pay Rise from April, a New Hire from May & Monthly Phasing
  14. What Meetings Cost: Person-Hours, Money per Year & the Share of Team Time
  15. Team Absence Rate: Available FTE Days, Monthly Rates, a Rolling Three Months & Year to Date
  16. Complaint Reasons: Shares, Cumulative Share & the Vital Few That Cause 80%
  17. Backlog Burn-Down: New vs Closed Tickets, the Backlog Trend & Weeks to Clear
  18. Fair Weekend Rota: Saturdays Worked, a Fair Share by FTE & Who Is Asked Next
  19. Final Project: The Quarterly Team Pack – Service Level, Cost per Ticket & Absence vs Targets

Buy course — $9.90 See the full course