Frame the decision and evidence · Lesson 2

Reconcile workforce data sources

6 min read

Lesson objectives

  • Check record grain, period, equivalent definition and table joins before calculating metrics.
  • Produce a reconciliation table that separates definitional differences from data errors and prioritises a decisive check.
Reading tools

The quality of advice depends on what each row means before it depends on arithmetic. ‘Employees’ may mean people active on the last day of a month in HR, funded posts in an annual finance budget, or hours actually delivered on a service channel over four weeks in operations. Different numbers do not automatically prove that one team is wrong; each report answers a different question. Begin with data grain: is a row a person, contract, team assignment, shift or service case? Then identify the time key: a snapshot on a date or a flow over a period? Do not combine month end vacancies with a four week average of work as if they covered the same period. Put the unit, period and inclusion rule above each reconciliation column. When the decision concerns capacity to handle demand, the decisive input is productive capacity in the relevant period and skill, not simply a name in the employee directory. Explain definitional differences to the sponsor before giving one apparently precise number.

Converting people into full time equivalents requires a declared denominator. If standard contractual time in the example is 40 hours weekly, a 20 hour contract represents 0.5 equivalent and a 40 hour contract 1.0. Overtime may increase temporary weekly capacity but does not automatically turn a permanent contract into 1.25 equivalents on the establishment record. If one person is assigned 0.6 to one team and 0.4 to another, their allocation should total 1.0, not two independent employees across the organisation. Check assignment start and end dates; the same person can appear in two extracts after a midperiod transfer. For average four week capacity, calculate each week's equivalent or shift hours and aggregate them rather than rely only on the final day's snapshot. Do not label leave or training as disappearance from the contractual workforce; reflect its effect on available productive hours at the correct layer. This lets the reader see whether change occurs in people, allocation, or productive time.

When joining person and case data, do not use a manually entered name as the first key. Spelling changes and names recur. Use an authorised internal identifier with record effective dates, and a governed crosswalk if systems use different identifiers. An executive memo normally needs aggregated capacity by skill and period, not individual identifiers; review access permissions, organisational policy and applicable law before sharing person level records. Count rows before and after each join. Multiplication can reveal a one to many relationship; missing rows can reveal incomplete matching. Set a missing data threshold according to its decision impact. A small missing percentage is not harmless if every missing record belongs to a scarce skill. Test the effect of a plausible worst case on the capacity gap and raise uncertainty if it could reverse advice. Never replace a missing value with zero unless zero is a verified description of reality.

A useful reconciliation log leaves an audit trail. For each source record the owner, extraction date, row grain, period, definition, raw count, conversion rule, and count relevant to the decision. Explain each difference rather than only its total: duplicate transfer, part time contract, allocation to another channel, older budget date, or productive time. If a difference remains unexplained, show a decision range. If channel supply could be 96 to 100 equivalents, at 26 hours available hours range from 2,496 to 2,600 and the gap against 2,700 spans 100 to 204 hours. Temporary cover may remain prudent throughout, but its size changes; obtain shift records before committing to a purchase or permanent post. Before sending the table check the sum of each person's assignments, overlapping effective dates, and consistent units for demand and supply. Treat disagreement among reports as information about the decision rather than a contest over a context free ‘right number’.

Lesson summary

Check record grain, period, equivalent definition and table joins before calculating metrics. Produce a reconciliation table that separates definitional differences from data errors and prioritises a decisive check.