Forecasting HR demand and supply: compare staffing gaps

Forecasting HR demand and supply means comparing the hours needed to deliver planned work with the productive hours your workforce can provide. The difference identifies a staffing shortage or spare capacity for a specific department, skill and period. A company total alone can hide a shortage in the team that owns a critical service.

Define the work and the available supply separately

Demand starts with an operating plan: calls to handle, claims to process or reviews to complete. Multiply the volume by an agreed standard time per item. Supply starts with the roster and expected movements, then converts available employees into productive hours.

The US Office of Personnel Management's workforce planning guidance connects current resources, expected turnover, future staffing requirements and the gaps between them. That sequence also gives a company a useful planning discipline. The arithmetic supports the judgment; it cannot establish whether one department's employees are qualified to do another's work.

Keep the demand estimate separate from the staffing response. If a manager reduces forecast work just to match the current headcount, the comparison stops identifying unmet service needs. The broader demand forecasting guide covers the operating-volume question; here, supplied workload assumptions become hours that can be compared with workforce availability.

Collect three small exports

Use a workload forecast by department and month, a roster summarized by department and working schedule, and a movement schedule containing expected exits, starts and transfers. Add standard task times and planned unavailable time from operations. Record who approved each assumption and when it was last reviewed.

Use the same month and organizational definitions in all files. A transferred employee must leave one department's supply and enter another's on the same effective date. A person who supports multiple teams needs an allocation, so that the same hours are not counted in full by every manager.

For the worked example, a fictional US benefits administrator plans one month of contacts, claims processing and quality review. Each available full-time equivalent has 160 scheduled hours, of which 28 are reserved for leave, training and other nonproductive time. That leaves 132 productive hours, or 82.5% of scheduled time.

Those are company planning assumptions, not an industry benchmark. Keep the time allowance explicit so a change in training or leave can be reviewed without hiding it inside a headcount figure.

Calculate demand in hours

Create a sheet named Demand with these columns in A through D. The standard time includes the work steps covered by the forecast and must use hours consistently.

In D2, enter this formula and fill down through D4:

=B2*C2

The total in D5 is:

=SUM(D2:D4)

Contacts require 26,400 × 0.50 = 13,200 hours. Claims require 2,800 × 1.50 = 4,200 hours, and quality requires 1,600 × 1.25 = 2,000 hours. Adding them gives 19,400 hours of work; adding the item volumes would be less useful because the tasks take different amounts of time.

Calculate supply after workforce movements

Create a Supply sheet with columns A through H below. In this example, all movements take effect at the start of the month, and all incoming employees are fully productive. Exits are positive amounts subtracted from opening supply; transfers are signed amounts.

In F2 and H2, enter the following formulas and fill down through row 4:

=B2-C2+D2+E2
=F2*G2

Contacts therefore have 100 − 5 + 8 + 2 = 105 available FTE and 105 × 132 = 13,860 hours. The company check is 148 − 8 + 12 + 0 = 152 FTE, with 152 × 132 = 20,064 hours.

In a live schedule, convert midmonth starts and exits into the hours available during the month before summing supply. Closing headcount is a point-in-time count; it cannot stand in for a full month's capacity. Training can also make a new hire's available hours different from an experienced employee's hours.

Compare the gap at the department level

Create a Summary sheet with department in A, required hours in B, available hours in C and the gap in D. The gap is demand minus supply: positive means a shortage.

Use these formulas in B2, C2 and D2, then fill down:

=SUMIFS(Demand!$D$2:$D$4,Demand!$A$2:$A$4,A2)
=SUMIFS(Supply!$H$2:$H$4,Supply!$A$2:$A$4,A2)
=B2-C2

Microsoft documents SUMIFS as adding amounts that meet specified criteria. Here the criterion is department; in a longer file, include month and skill criteria so unrelated capacity is not pulled into the comparison. Covirage is not affiliated with Microsoft.

The company has 664 spare hours, yet claims has a 372-hour shortage. At 132 productive hours per FTE, that shortage is 2.82 FTE, rounded to two decimal places. Hiring decisions should follow the claims requirement rather than the apparent company surplus.

Test an action without changing the demand

Suppose operations confirms that three contact-team FTE have the required claims skills and can transfer at the start of the month without training loss. Their transferable capacity is 3 × 132 = 396 hours.

Contact supply falls to 13,860 − 396 = 13,464 hours, leaving 264 spare hours against its 13,200-hour requirement. Claims supply rises to 3,828 + 396 = 4,224 hours, leaving 24 spare hours against its 4,200-hour requirement. Quality still has 376 spare hours.

The revised surpluses add to 264 + 24 + 376 = 664 hours, the original company surplus. Transfers changed the distribution of capacity, not its total. If the skill or timing assumption fails, retain the shortage and test a different response rather than assuming hours are interchangeable.

Operations owns workload and task standards, HR owns the roster and movement assumptions, and finance checks the budget implications. Managers agree the staffing action. For the separate question of materials availability, use supply forecasting or demand forecasting in supply chain, rather than mixing inventory and labor capacity.

Where it goes wrong

• Headcount counted as hours. Part-time work, partial months and unavailable time can make identical headcounts produce different capacity.
• Unapproved hires treated as certain. Separate confirmed starts from hiring scenarios and show the dates behind both.
• Transfers counted twice. Internal transfers should net to zero across the organization unless its reporting boundary changes.
• One total hides a skills shortage. Spare contact hours do not cover claims work until qualifications, timing and service constraints have been checked.
• Task standards ignore work quality. A faster assumption that increases rework may reduce the measured demand while increasing the real workload.

Compare your staffing files in Covirage

Bring a sample of your own workload, workforce movement and productive-hours schedules as a file or Google Sheet. Covirage maps the columns, its tools compute and check the department comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See forecast analysis for the supporting planning context.