ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Compensation Analysis Template (Pave)
2
3
One row per employee. Fill the blue columns (A to J) from your HRIS and payroll export, the yellow columns (K to O) from your market data source and salary structure, and the green columns (P to V) calculate on their own. Fill the orange columns (W and X) by hand after review.
4
5
Formulas
6
Total cash = Base salary + Target bonus
7
Total compensation = Total cash + Annual equity value
8
Compa-ratio = Base salary / Range midpoint (1.00 = at the midpoint)
9
Range penetration = (Base salary - Range minimum) / (Range maximum - Range minimum) (0% = at minimum, 100% = at maximum)
10
Market ratio = Base salary / Market P50 base (1.00 = at market)
11
Adjustment to minimum = MAX(0, Range minimum - Base salary)
12
Flag = text built from the decision rules on the Thresholds sheet (edit the thresholds there, not in the formula)
13
14
Reading the results
15
1. Sort by Market ratio to see external competitiveness.
16
2. Sort by Compa-ratio to see position against your own structure.
17
3. Filter the Flag column to the rows that need a decision.
18
4. Group by Job title + Level and look at the spread of Compa-ratio within each group; spreads wider than about 20 points need an explanation in Tenure and Performance rating.
19
5. Fill Recommended adjustment and Rationale. The total at the bottom of the sheet is the budget ask.
20
21
The sheet ships empty: paste your own rows from row 2 down. Formulas are pre-filled for 100 rows.
22
Formulas are pre-filled for 100 rows; copy row 101 down for more.
23
24
Market P50 columns: paste the median for the matched job, level, and location. With Pave Market Data these reflect current pay from integrated HRIS, payroll, and cap table systems; with a survey source, note the collection date in your report.
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100