Blog · Territory, capacity and quota planning · Finance and FP&A teams
Build a monthly headcount workbook with hiring dates, exits, FTEs and loaded staffing cost. Compare the completed plan with actual staffing and expenses by department.
Headcount planning in Excel connects each position's hiring and exit dates to monthly staff counts and loaded labor cost. Keep the roster separate from the monthly department summary, then compare a saved plan with actual staffing and expenses. A closing headcount alone cannot explain a month's cost because people can work only part of the month.
Use one row per filled or approved planned position. Give a replacement hire its own position episode so an exit and a later start remain visible. The broader headcount planning process covers how managers decide what staffing to request; this layout turns those decisions into checked monthly numbers.
Put these inputs in columns A:G. The example is a small US business planning October 2026; salary figures are annual full-time rates, even for the part-time position.
| Position | Department | Start date | Exit effective date | FTE | Annual full-time salary (USD) | Employer load |
|---|---|---|---|---|---|---|
| F01 | Finance | January 1, 2026 | Blank | 1.0 | 96,000 | 25% |
| F02 | Finance | October 16, 2026 | Blank | 1.0 | 84,000 | 25% |
| O01 | Operations | January 1, 2026 | October 16, 2026 | 1.0 | 72,000 | 25% |
| O02 | Operations | January 1, 2026 | Blank | 0.5 | 60,000 | 20% |
The exit effective date is the first day the person is no longer employed. O01's last paid day is therefore October 15. These loading rates are illustrative company planning assumptions, not statutory tax rates. They cover salary-related employer costs only; recruitment fees and equipment would need separate lines.
Add version and approval fields when several managers contribute. Keep unapproved requests out of the approved baseline, but retain them in a separately labeled scenario. If hiring depends on shipment or service demand, link the assumption to demand planning in Excel rather than hiding it inside the salary formula.
Enter October 1, 2026 in N1 as a real date. In N2 calculate the month end, and in N3 the number of calendar days:
=EOMONTH(N1,0)
=DAY(N2)
Add columns H:K to the roster. Their formulas for row 2 are:
| Column | Output | Formula in row 2 |
|---|---|---|
| H | Days employed in month | =MAX(0,MIN(IF(D2="",$N$2,D2-1),$N$2)-MAX(C2,$N$1)+1) |
| I | Closing headcount | =--AND(C2<=$N$2,OR(D2="",D2>$N$2)) |
| J | Closing FTE | =I2*E2 |
| K | Monthly loaded cost (USD) | =F2*E2*(1+G2)/12*H2/$N$3 |
Copy down through row 5. The cost convention divides annual cost into equal monthly amounts, then prorates each month by calendar days. It is a planning convention; confirm how your payroll and expense accruals differ before comparing actuals.
To summarize Finance's loaded cost, use:
=SUMIFS($K$2:$K$5,$B$2:$B$5,"Finance")
Microsoft's SUMIFS documentation explains that the function sums cells meeting multiple criteria. Add a month criterion when your calculation sheet contains several months instead of this single-month example.
October has 31 days. F02 works October 16 through October 31, or 16 days; O01 works 15 days. Keep full precision in formulas and display money to two decimal places.
| Position | Days employed | Full-month loaded cost (USD) | October cost calculation (USD) | Closing headcount | Closing FTE |
|---|---|---|---|---|---|
| F01 | 31 | 10,000.00 | 10,000 × 31 / 31 = 10,000.00 | 1 | 1.0 |
| F02 | 16 | 8,750.00 | 8,750 × 16 / 31 = 4,516.13 | 1 | 1.0 |
| O01 | 15 | 7,500.00 | 7,500 × 15 / 31 = 3,629.03 | 0 | 0.0 |
| O02 | 31 | 3,000.00 | 3,000 × 31 / 31 = 3,000.00 | 1 | 0.5 |
| Total | 21,145.16 | 3 | 2.5 |
For example, F02's full-month amount is 84,000 × 1.0 × 1.25 / 12 = $8,750. O02's amount is 60,000 × 0.5 × 1.20 / 12 = $3,000. Do not halve that salary again after applying the FTE fraction.
Changing only the month control to November makes F02 a full-month employee and removes O01's cost. November's total is 10,000 + 8,750 + 3,000 = $21,750, while closing headcount remains three. The higher cost comes from timing, not an additional closing employee.
Count starts and exits within the month. For Finance starts, the formula is:
=COUNTIFS($B$2:$B$5,"Finance",$C$2:$C$5,">="&$N$1,$C$2:$C$5,"<="&$N$2)
Use the same pattern with column D for exits. Microsoft documents COUNTIFS as counting cells that meet multiple criteria. Require actual dates; blanks are not exit events.
| Department | Opening staff | Hires | Exits | Closing staff | Plan cost (USD) | Actual cost (USD) | Actual minus plan (USD) |
|---|---|---|---|---|---|---|---|
| Finance | 1 | 1 | 0 | 2 | 14,516.13 | 10,000.00 | -4,516.13 |
| Operations | 2 | 0 | 1 | 1 | 6,629.03 | 6,900.00 | 270.97 |
| Total | 3 | 1 | 1 | 3 | 21,145.16 | 16,900.00 | -4,245.16 |
The identity is 3 + 1 - 1 = 3. Match those closing employees to the month-end roster, and tie $16,900 of actual expense to the same cost accounts and departments used by the plan.
Here the actual roster shows Finance's new hire started November 1, leaving actual October closing headcount at two companywide. Finance's cost gap matches the missing planned partial month. Operations still needs an expense explanation; an unchanged closing count does not prove its cost is correct. Keep this reconciliation beside the worked measures you can check by hand.
Covirage is not affiliated with Microsoft.
Bring your completed headcount workbook and actual staffing and expense files. Covirage maps the columns; its tools compute and check department counts, loaded costs and variances, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See capacity planning for the supporting business context.
Keep both. Headcount counts each active person once, while FTE expresses their scheduled capacity relative to a full-time position. A part-time employee can add one person to headcount and less than one FTE to capacity and salary cost.
Define the employer costs included in your plan, such as salary, payroll taxes and benefits. Use company assumptions rather than a universal loading percentage. Keep costs that do not vary with salary, and one-time hiring costs, in separate columns.
Include the person in closing headcount if employed at month end. Prorate their cost using the planning convention you state, such as calendar days employed divided by calendar days in the month. Actual payroll can use a different convention, creating a legitimate variance.