Sheets Sheet Type Description Links

Model Assumptions Description of Sheets Model

List Assumptions Lists and model assumptions List
HR Input Sheet Revenue metrics HR
Staff Input Sheet Expense metrics Staff
Calcs Calculations sheet Primary calculations page Calcs
Chart Calculations sheet Primary chart page Chart
Check Error trapping Checks and balances for the model Check
Summary Output Sheet Primary output page Summary
List of Areas Actual Months Menu Date aAll Date CY
Group Budget Jan IT 1/1/2019 1/1/2020
England Forecast 1 Feb Finance 2/1/2019 2/1/2020
Scotland Forecast 2 Mar HR 3/1/2019 3/1/2020
Wales Forecast 3 Apr Strategy 4/1/2019 4/1/2020
Northern Ireland May 5/1/2019 5/1/2020
Jun 6/1/2019 6/1/2020
Jul 7/1/2019 7/1/2020
Aug 8/1/2019 8/1/2020
Sep 9/1/2019 9/1/2020
Oct 10/1/2019 10/1/2020
Nov 11/1/2019 11/1/2020
Dec 12/1/2019 12/1/2020
1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
12/1/2020
Checks
Model OK
Check Model
Region City FTEs Overtime Hrs Avg Svs Length Avg Salary Avg Bonus Avg Overtime
England IT 108 976 3.5 65,455 6,873 3,273
England Finance 55 433 2.4 48,978 5,143 979.56
England HR 33 200 4.8 46,550 4,888 931.00
England Strategy 43 187 5.3 49,864 5,236 2,493
Scotland IT 33 198 6.8 41,234 4,330 2,886.38
Scotland Finance 23 133 5.9 37,886 3,978 5,682.90
Scotland HR 13 87 3.8 35,985 3,778 719.70
Scotland Strategy 12 90 2.9 33,459 3,513 1,673
Wales IT 58 236 1.8 28,567 3,000 285.67
Wales Finance 35 177 4.9 29,889 3,138 2,092.23
Wales HR 16 88 3 31,009 3,256 2,170.63
Wales Strategy 9 34 2 27,056 2,841 270.56
Northern Ireland IT 44 184 3.9 36,789 3,863 1,839
Northern Ireland Finance 21 104 6.7 29,889 3,138 2,690.01
Northern Ireland HR 8 65 4.6 32,029 3,363 4,804.35
Northern Ireland Strategy 7 54 5 27,998 2,940 1,959.86
AvgLeave Balance Spend Per Hire Survey Q1Survey Q2Survey Q3Survey Q4 Train Q1 Train Q2
22 8,692 25 45 55 76 40 50
32.00 3,144.85 27 39 55 78 44 47
15.00 6,547.41 35 45 55 65 39 50
6 5,605 18 28 38 48 20 31
17.00 7,680.00 30 40 50 60 33 44
28.00 4,678.00 45 55 65 75 50 61
15.00 3,790.00 70 77 88 98 77 85
28 5,789 33 43 77 86 37 48
21.00 6,787.00 35 45 60 70 39 50
12.00 4,567.00 25 35 66 70 28 39
18.00 3,896.00 25 50 73 83 28 55
23.00 2,978.00 25 60 70 88 28 66
19 3,890 18 40 50 80 24 52
9.00 4,671.00 50 70 80 99 65 91
12.00 5,467.00 25 50 66 76 33 65
15.00 4,925.00 10 20 30 60 13 26
Train Q3 Train Q4 Gender FM % Q1 Gender FM % Q2 Gender FM % Q3 Gender FM % Q4
61 84 10 15 20 18
61 86 50 50 42 45
61 72 80 72 77 70
42 53 55 60 65 60
55 66 20 25 28 30
72 83 45 30 35 30
97 108 67 67 70 72
85 95 54 54 57 59
66 77 42 42 40 40
73 77 45 45 50 53
81 92 21 21 25 29
77 97 59 61 63 60
65 88 8 12 15 18
94 98 45 45 50 45
86 99 67 70 65 63
39 78 59 59 67 67
Rating Q1 Rating Q2 Rating Q3 Rating Q4 FTE/Contractor Q1 FTE/Contractor Q2
3.3 3.5 3.5 3.7 40 30
4 4 4.1 4.2 53 56
4.2 4 3.8 3.7 91 92
4.8 4.5 4.3 4.5 80 77
4.2 4 4.1 4.4 35 31
3.7 3.6 3.4 3.2 45 71
4.3 4 4.2 4.1 85 88
4 3.8 4 4.2 80 83
2.8 3 3.5 3.3 43 46
3.7 3.9 3.4 3.2 53 56
4 3 3.5 3.9 93 96
4.2 4.8 4.1 4.4 88 89
3 3.5 3.6 3.4 50 37
3.7 3.5 3.9 3.7 60 63
3.8 4 4 4.1 100 99
3 3.2 3.4 3.6 95 98
FTE/Contractor Q3 FTE/Contractor Q4 Overtime Hrs Q1 Overtime Hrs Q2 Overtime Hrs Q3
24 27 976 642 625
67 63 433 400 383
95 99 200 256 309
72 78 187 220 262
28 27 198 249 299
62 72 133 209 277
85 83 87 179 257
79 77 90 169 194
49 49 236 284 313
51 49 177 226 220
82 72 88 144 199
87 92 34 114 198
31 27 184 183 246
57 63 104 174 253
100 100 65 133 200
87 87 54 127 185
Overtime Hrs Q4 FTE Q1 FTE Q2 FTE Q3 FTE Q4 Open Positions Avg Perf S
553 108 144 150 156 17 4.5
370 55 53 45 48 12 4.3
362 33 30 32 31 7 4.2
308 43 45 45 42 4 4.1
349 33 38 35 30 6 4
340 23 15 18 16 5 3.4
331 13 13 14 13 4 3.8
274 12 12 13 13 3 3.6
368 58 55 52 52 6 3.9
308 35 34 28 29 5 4.3
275 16 16 18 21 3 3.7
257 9 8 9 7 2 4.5
254 44 45 44 40 4 4
304 21 20 23 21 1 3.1
274 8 9 9 9 1 3
192 7 7 8 8 2 3.1
19-25 26-35 36-45 46-55 55+
21.6 32.4 32.4 10.8 10.8
13.75 6.6 11 12.1 11.55
2.64 6.6 6.27 4.62 12.87
14.19 12.47 3.01 3.87 9.46
8.91 3.3 4.95 2.64 13.2
4.14 2.99 5.52 7.59 2.76
2.47 2.21 1.43 1.3 5.59
0.96 2.52 3.6 3.6 1.32
17.4 4.64 6.38 6.38 23.2
10.85 9.1 4.2 7.7 3.15
4.8 1.76 4.48 2.88 2.08
1.53 2.34 1.89 1.98 1.26
8 15 14 6 1.2
5.88 1.89 2.31 4.83 6.09
2 2 3 1 0
0.84 1.96 1.26 2.45 0.49
Region Month Consultants Open Positions Casual Staff Termination New Hires
England 1 Jan 19 87 33 27 -3 2
England 1 Feb 19 93 34 28 -4 1
England 1 Mar 19 80 34 24 -2 4
England 1 Apr 19 74 31 23 -1 2
England 1 May 19 70 27 21 -3 2
England 1 Jun 19 100 42 30 -2 4
England 1 Jul 19 79 32 24 -1 2
England 1 Aug 19 98 40 30 -4 8
England 1 Sep 19 98 36 30 -6 4
England 1 Oct 19 64 26 20 -5 10
England 1 Nov 19 82 28 25 0 0
England 1 Dec 19 97 38 30 0 1
England 1 Jan 20 58 21 18 -2 7
England 1 Feb 20 80 31 24 -3 4
England 1 Mar 20 74 31 23 0 2
England 1 Apr 20 70 27 21 -3 3
England 1 May 20 87 33 27 -3 3
England 1 Jun 20 93 34 28 -3 2
England 1 Jul 20 100 42 30 -3 2
England 1 Aug 20 58 21 18 -2 4
England 1 Sep 20 67 26 21 -3 1
England 1 Oct 20 84 34 26 -3 4
England 1 Nov 20 75 29 23 -3 1
England 1 Dec 20 87 35 27 -3 2
Scotland 1 Jan 19 44 17 14 -2 4
Scotland 1 Feb 19 47 17 15 -2 1
Scotland 1 Mar 19 40 17 12 -2 4
Scotland 1 Apr 19 37 16 12 -2 4
Scotland 1 May 19 27 10 9 -1 2
Scotland 1 Jun 19 25 10 8 -1 2
Scotland 1 Jul 19 21 7 7 -1 2
Scotland 1 Aug 19 13 5 4 -4 8
Scotland 1 Sep 19 41 15 13 -5 7
Scotland 1 Oct 19 47 18 15 -2 4
Scotland 1 Nov 19 59 24 18 -6 9
Scotland 1 Dec 19 53 20 16 -2 4
Scotland 1 Jan 20 61 24 19 -2 4
Scotland 1 Feb 20 69 26 21 -2 4
Scotland 1 Mar 20 45 18 14 -2 4
Scotland 1 Apr 20 57 20 18 -5 8
Scotland 1 May 20 68 27 21 -3 2
Scotland 1 Jun 20 41 15 13 -1 2
Scotland 1 Jul 20 56 22 17 -1 1
Scotland 1 Aug 20 44 17 14 -2 4
Scotland 1 Sep 20 47 17 15 -2 4
Scotland 1 Oct 20 40 17 12 -2 4
Scotland 1 Nov 20 37 16 12 -2 4
Scotland 1 Dec 20 27 10 9 -2 4
Wales 1 Jan 19 22 9 7 -1 2
Wales 1 Feb 19 24 9 8 -1 2
Wales 1 Mar 19 20 9 6 -2 3
Wales 1 Apr 19 19 8 6 -1 2
Wales 1 May 19 4 3 2 -1 2
Wales 1 Jun 19 3 1 1 -1 2
Wales 1 Jul 19 22 10 7 -1 2
Wales 1 Aug 19 18 7 6 -1 2
Wales 1 Sep 19 31 12 10 -1 2
Wales 1 Oct 19 33 12 10 -1 2
Wales 1 Nov 19 28 12 9 -1 2
Wales 1 Dec 19 26 11 8 -1 2
Wales 1 Jan 20 19 7 6 -1 2
Wales 1 Feb 20 29 11 9 -1 2
Wales 1 Mar 20 33 13 10 -1 2
Wales 1 Apr 20 42 17 13 -2 4
Wales 1 May 20 37 14 12 -2 4
Wales 1 Jun 20 43 17 13 -2 4
Wales 1 Jul 20 48 18 15 -2 4
Wales 1 Aug 20 22 9 7 -1 2
Wales 1 Sep 20 24 9 8 -1 0
Wales 1 Oct 20 20 9 6 -1 2
Wales 1 Nov 20 19 8 6 -1 2
Wales 1 Dec 20 4 3 2 -1 0
Northern Ir 1 Jan 19 11 5 4 -1 2
Northern Ir 1 Feb 19 12 5 4 -1 2
Northern Ir 1 Mar 19 10 5 3 -1 0
Northern Ir 1 Apr 19 10 4 3 -1 3
Northern Ir 1 May 19 17 9 6 -1 2
Northern Ir 1 Jun 19 9 6 3 -1 2
Northern Ir 1 Jul 19 26 11 8 -1 3
Northern Ir 1 Aug 19 23 9 7 -1 2
Northern Ir 1 Sep 19 16 6 5 -1 3
Northern Ir 1 Oct 19 17 6 6 -1 2
Northern Ir 1 Nov 19 14 6 5 -1 1
Northern Ir 1 Dec 19 13 6 4 -1 1
Northern Ir 1 Jan 20 13 7 4 -1 2
Northern Ir 1 Feb 20 8 24 3 -1 2
Northern Ir 1 Mar 20 14 9 5 -1 2
Northern Ir 1 Apr 20 14 7 5 -1 2
Northern Ir 1 May 20 12 7 4 -1 3
Northern Ir 1 Jun 20 14 5 5 -1 2
Northern Ir 1 Jul 20 20 8 6 -1 2
Northern Ir 1 Aug 20 11 5 4 -1 2
Northern Ir 1 Sep 20 12 5 4 -1 2
Northern Ir 1 Oct 20 10 5 3 -1 1
Northern Ir 1 Nov 20 10 4 3 -1 2
Northern Ir 1 Dec 20 17 9 6 -1 2
Group 1 Jan 19 62 0 0 0
Group 1 Feb 19 64 0 0 0
Group 1 Mar 19 63 0 0 0
Group 1 Apr 19 57 0 0 0
Group 1 May 19 46 0 0 0
Group 1 Jun 19 57 0 0 0
Group 1 Jul 19 59 0 0 0
Group 1 Aug 19 59 0 0 0
Group 1 Sep 19 68 0 0 0
Group 1 Oct 19 61 0 0 0
Group 1 Nov 19 69 0 0 0
Group 1 Dec 19 74 0 0 0
Group 1 Jan 20 58 0 0 0
Group 1 Feb 20 89 0 0 0
Group 1 Mar 20 69 0 0 0
Group 1 Apr 20 69 0 0 0
Group 1 May 20 80 0 0 0
Group 1 Jun 20 70 0 0 0
Group 1 Jul 20 88 0 0 0
Group 1 Aug 20 50 0 0 0
Group 1 Sep 20 55 0 0 0
Group 1 Oct 20 63 0 0 0
Group 1 Nov 20 55 0 0 0
Group 1 Dec 20 54 0 0 0
Group
Region Dept Category Survey Q1 Survey Q2 Survey Q3
England HR Satisfaction 35 45 55
England HR Training 39 50 61
England HR Gender 80 72 77
England HR Rating 4.20 4.00 3.80
England HR FTE/Contractor 91 92 95
England HR Overtime 200 256 309

Region Dept Category FTE Q1 FTE Q2 FTE Q3

England IT FTE 108 144 150
England Finance FTE 55 53 45
England HR FTE 33 30 32
England Strategy FTE 43 45 45

Region Dept Spend per Hire Open Positions

England IT $ 8.7 17
England Finance $ 3.1 12
England HR $ 6.5 7
England Strategy $ 5.6 4
Total $6.0 k 40

Region Dept Consultants Open Positions Part Time

England Jan 20 58 21 18
England Feb 20 80 31 24
England Mar 20 74 31 23
England Apr 20 70 27 21
England May 20 87 33 27
England Jun 20 93 34 28
England Jul 20 100 42 30
England Aug 20 58 21 18
England Sep 20 67 26 21
England Oct 20 84 34 26
England Nov 20 75 29 23
England Dec 20 87 35 27

Toal 933 364 286

Selection TRUE TRUE TRUE

Region Dept Avg Salary Avg Bonus Avg Overtime

England IT 65,455 6,873 3,273
England Finance 48,978 5,143 980
England HR 46,550 4,888 931
England Strategy 49,864 5,236 2,493
Avg 52,712 5,535 1,919

Region Dept AvgLeave BalancePerf Score

England IT 22 4.5
England Finance 32 4.2
England HR 15 4.0
England Strategy 6 3.8
 Avg 18.8 4.1

Region Month Termination New Hires

England Jan 20 -2 7
England Feb 20 -3 4
England Mar 20 0 2
England Apr 20 -3 3
England May 20 -3 3
England Jun 20 -3 2
England Jul 20 -3 2
England Aug 20 -2 4
England Sep 20 -3 1
England Oct 20 -3 4
England Nov 20 -3 1
England Dec 20 -3 2
Dec 99 -31 35

Region Dept 19-25 26-35 36-45 46-55

England IT 22 32 32 11
England Finance 14 7 11 12
England HR 3 7 6 5
England Strategy 14 12 3 4

Region Speed to Hire Promo Rate Cust Satisfaction

England 27.00 9% 88%
Scotland 28.00 7% 84%
Wales 29.00 8% 82%
Northern Ir 30.00 10% 91%

England 27 9% 88%
 Choice Country
Survey Q4 Total 1 England
65 200
72 222
70 299
3.70 16
99 377
362 1,126

FTE Q4 Total
156 558
48 201
31 126
42 175
55+
11
12
13
9
Speed to Hire 2016
Good 15
OK 30
Actual 27
Target 32

Target Val 32.00

Target Cat 2

Desc Actual Poor OK

Grad - 15.00 15.00
Actual 27.00 - 3.00
Gradation2 - 15.00 15.00

Promo Rate 2016

Good 5%
OK 7%
Actual 9%
Target 8%

Target Val 8%
Target Cat 2

Desc Actual Poor OK

Grad 0% 5% 2%
Actual 9% 0% 0%
Gradation2 0% 5% 2%

Cust Satisfaction 2016

Poor 50%
OK 75%
Actual 88%
Target 85%

Target Val 85%

Target Cat 2

Desc Actual Poor OK

Grad 0% 50% 25%
Actual 88% 0% 0%
Gradation2 0% 50% 25%
Metric Country Area Survey Q1 Survey Q2 Survey Q3
Satisfaction England HR 35.00 45.00 55.00

Check - - -

Metric Country Area Train Q1 Train Q2 Train Q3

HR England HR 39.00 50.00 61.00

Check - - -

Metric Country Area Gender FM % Q1 Gender FM % Q2Gender FM % Q3

HR England HR 80.00 72.00 77.00

Check - - -

Metric Country Area Rating Q1 Rating Q2 Rating Q3

HR England HR 4.20 4.00 3.80

Check - - -

Metric Country Area FTE/Contractor Q1 FTE/Contractor Q2 FTE/Contractor

HR England HR 91.00 92.00 95.00

Check - - -

Metric Country Area Overtime Hrs Q1 Overtime Hrs Q2 Overtime Hrs Q3

HR England HR 200.00 255.56 309.09

Check - - -

Metric Country Area FTE Q1 FTE Q2 FTE Q3

HR England HR 33.00 30.00 32.00

Check - - -

Metric Country Area Spend per Hire Open Positions

HR England 5,997.31 40.00

Check - 0.00 -

Country Date Consultants Open Positions Casual Staff Termination

England Jan 20 58 21 18 -2
England Feb 20 80 31 24 -3
England Mar 20 74 31 23 0
England Apr 20 70 27 21 -3
England May 20 87 33 27 -3
England Jun 20 93 34 28 -3
England Jul 20 100 42 30 -3
England Aug 20 58 21 18 -2
England Sep 20 67 26 21 -3
England Oct 20 84 34 26 -3
England Nov 20 75 29 23 -3
England Dec 20 87 35 27 -3

Total 933 364 286 -31

Check 0 0 0 0

Total Check Model OK

 Survey Q4 Check
65.00

- -

Train Q4 Check
72.00

- -

Gender FM % Q4 Check
70.00

- -

Rating Q4 Check
3.70

- -

FTE/Contractor Q4 Check
99.00

- -

Overtime Hrs Q4 Check

361.54

- -

FTE Q4 Check
31.00

- -

- 0.00

New Hires
7
4
2
3
3
2
2
4
1
4
 1
2

35

0 0

-
 18.8
Human Resources Dashboard 4.1 $6.0 k 40
Avg Leave Balance Staff Performance Cost Per Hire Open Positions Region England
Department >> HR Cons Open Part
England HR England HR Metrics ✘ ultant ✘ Position ✘ Time
Department FTE Qtly Trend Q1 Q2 Q3 Q4 Total Trend s Staff
s
IT 108 Employee Satisfaction % 120
35 45 55 65 50
Finance 55
100
Employees Trained #
HR 33 39 50 61 72 55.5
80
Strategy 43 Gender Diversity % M/F

Total Number
80 72 77 70 558 60
England Average Spend Per Hire $000s
Avg Staff Rating (0-5)
4.2 4 3.8 3.7 3.9 40

FTE/Contractor % 20
91 92 95 99 94.25

0
Overtime Hrs Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20
200 256 309 362 282

6
New Hires Termination Income Breakdown AvgLeave Balance Days
5
4 Strategy Strategy 6
3
2 HR
HR 15
1
0 Finance
-1 Finance 32
-2 IT
-3
k 10 k 20 k 30 k 40 k 50 k 60 k 70 k 80 k IT 22
-4
Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20
Avg Salary Avg Bonus Avg Overtime

Customer Satisfaction Actual Target

Age Demographics by Dept Open Positions by Dept Employee Score

35 IT Finance HR Strategy

30 0% 50% 100% 150% 200% 250%

4
25 Promotion Rate
7 17
40
20
4.5
15
4.2 0% 50% 100% 150% 200% 250%
10 12 4.0 Speed to Hire (Days)
3.8
5

- IT Finance IT Finance HR Strategy

19-25 26-35 36-45 46-55 55+
HR Strategy 0 5 10 15 20 25 30 35
Model Status Model OK