What Each Hiring Route Costs Ted Square Ltd

Ted Square Ltd relied heavily on external recruitment and wanted to know whether that was financially sustainable, and how much a shift towards internal hiring would change spend without lowering the quality of hires.

Two sheets: a hiring record for 44 employees (department, position, hire date, source channel, hiring route, performance score and cost per hire) and a cost breakdown listing each recruitment cost line by route.

The date problem

The original report showed hire dates as numbers such as 42125. The Date of Hire column mixed two formats: genuine Excel dates, which load as serial numbers, and US-style text such as 10/27/2008. Power BI could not read the column as dates, so any time-based chart was wrong.

Table.TransformColumns(#"Promoted Headers", {{"Date of Hire", each
    if _ is date or _ is datetime or _ is number
        then Date.From(_)
        else Date.FromText(Text.Trim(Text.From(_)), [Culture="en-US"]),
    type date}})

Hire Year column

Hire Year = YEAR(Raw[Date of Hire])

A calculated column set to "Don't summarize", so the years sit on a chart axis instead of being added together.

A what-if parameter, Internal Hire Target, sets the share of hires filled internally, from 0% to 100% in steps of 10. The default is 70%, the target proposed in the original Ted Square recommendations.

Scenario Cost =
VAR t = 'Internal Hire Target'[Internal Hire Target Value] / 100
VAR n = CALCULATE([Hires], REMOVEFILTERS(Raw[Employment Method]))
RETURN n * (t * [Internal Cost per Hire] + (1 - t) * [External Cost per Hire])

Scenario Saving =
CALCULATE([Recruitment Cost], REMOVEFILTERS(Raw[Employment Method]))
    - [Scenario Cost]

What the scenario assumes

• Costs scale with volumeThe model applies an average cost per hire. In reality, much external spend is fixed (agency fees, job boards, adverts), so savings would not fall exactly in line.
• How cost per hire was allocatedThe dataset spreads each route's total cost evenly across its hires, and lists per-employee costs such as checks and travel once rather than per hire. The figures are used as supplied and would need checking against real invoices.
• Internal candidates existA 70% internal share assumes suitable people are ready to move. That depends on succession planning that is not yet in place.
• Backfill is ignoredAn internal move creates a new vacancy, which the model does not count.

IT/IS alone accounts for 79% of recruitment spend, almost all of it through external hiring.

• Hiring ran at a few people a year until 2014 (8 hires), then peaked at 21 in 2015, 20 of them external.
• Employee referral was the largest single source, with 10 hires, followed by internal moves (8) and Glassdoor (5).
• Performance ratings were similar across both routes. Internal hires are too few (8) to prove they perform better or worse.

• Small sample. 44 hires, only 8 internal, so route comparisons are indicative rather than conclusive.
• No time-to-fill data. The dataset has hire dates but no requisition dates, so speed of hiring cannot be measured.
• Case-study data. Figures describe a fictional company and are not results from an employer.

1. Build internal routes into IT/IS roles through succession plans and a skills inventory, since the department drives most of the spend.
2. Keep external hiring for skills the business lacks, and review agency use first, as the agency fee is the largest single cost line.
3. Grow the referral programme. It is already the top source, and total referral bonuses (£180K) were well below agency fees (£600K).
4. Set a phased internal-hiring target, for example 40% then 70%, and track it on the dashboard against actual spend.
5. Capture requisition dates and real invoice costs so time-to-fill and true cost per hire can be measured.

Download the Power BI file (TedSquare_Recruitment_Cost.pbix). It opens in Power BI Desktop with the data model and every measure described here.

1. Load both sheets and apply the date fix in section 02 to the hiring sheet.
2. Add the Hire Year column and set it to "Don't summarize".
3. Create the measures in section 03 and the parameter in section 04: 0 to 100, increment 10, default 70.
4. Use External Hires and Internal Hires as two series on the yearly chart.