Structure roles, salary bands, employee positions, and promotion history in one workbook so compensation decisions can be reviewed against a consistent internal framework.
The workbook is designed for HR leaders, people managers, payroll teams, and business owners who need an organized view of workforce compensation. Users define job functions and salary ranges, register employee position information and promotions, then review calculated summaries, position-level salary statistics, and employee-level compensation views.
Build a role and salary structure
Map each job function to a department, position category, salary range, and salary so the compensation framework is documented in a consistent format.
Track employee movement
Record starting or previous functions, current roles, expected promotions, contract types, and promotion events to keep position histories connected to compensation data.
Review pay distribution
Use summary metrics, salary charts, and position reports to compare headcount and salary levels across the positions represented in the workbook.
What does the workbook help you analyze?
The template connects compensation setup with employee records and reporting. Its visible worksheets support both the administrative side of maintaining role data and the analytical side of reviewing how salaries are distributed across positions.
Define job functions and departments. Set up functions with department, position category, salary range, and salary fields.
Establish five salary reference ranges. Maintain Range I through Range V for each function and review the calculated average range shown in the salary-range table.
Maintain employee position records. Capture employee name, previous function, contract type, expected promotion, current function, and current salary.
Record promotion events. Log the employee, previous function, department, promotion date, and new function to preserve a simple history of internal movement.
Compare compensation by position. Review job count, share of employees, average salary, median salary, highest salary, and lowest salary for each position category.
Review company and employee dashboards. See total employees and salary summary statistics at company level, then inspect an individual employee's current position and salary relative to the workbook's salary distribution.
What is inside the workbook?
The visible workbook navigation separates setup, employee records, promotions, and reporting. Yellow input areas identify the fields used to build the compensation structure, while report and dashboard views consolidate those entries into management information.
Compensation inputs
Input Jobs and Salary Ranges organize functions, departments, position categories, five salary bands, and salary values.
Employee and promotion records
Employee Position and Promotion views capture current workforce assignments, expected movement, and dated promotion changes.
Dashboards and reports
Summary, employee, and jobs-by-position views present headcount, salary statistics, charts, and role-level comparisons derived from the recorded data.
The job input table links each function to a department, position category, salary range, and salary.
Set the job and compensation framework
Start with the role structure. The job input view provides a common record for function names, departments, position classifications, assigned salary ranges, and salary values. This creates the reference structure used when employee positions and compensation are reviewed elsewhere in the workbook.
The promotion log records the employee, prior function, department, date, and new function for each promotion event.
Keep promotion history tied to roles
The promotion view records dated changes from a previous function to a new function. Used alongside the employee-position data, it gives managers a structured way to document internal movement rather than relying on disconnected notes or separate role lists.
The summary dashboard shows employee count, average, highest, and lowest salary plus position mix and average-salary charts.
Review the compensation picture at a glance
The consolidated dashboard turns the entered workforce data into management-level indicators. It displays total employees, average salary, highest salary, lowest salary, a jobs-by-position chart, and an average-salary chart, making it easier to spot how headcount and pay are distributed across position categories.
The position report compares headcount share with average, median, highest, and lowest salary for each position category.
Compare salary statistics by position
The jobs-by-position report provides a more detailed table for compensation review. For each position category, it shows the number and percentage of jobs together with average, median, highest, and lowest salary, supporting discussions about internal pay distribution and role mix.
How do you use the template?
Define job functions.
Enter the functions used in your organization and assign their departments, position categories, salary ranges, and salary values.
Set salary ranges.
Populate the five salary reference ranges for each function so roles can be reviewed against a consistent pay structure.
Register employees.
Add employee position information, including previous and current functions, contract type, expected promotion, and current salary.
Log promotions.
Record promotion dates and role changes as employees move from one function to another.
Review dashboards and reports.
Use the summary, employee, and position views to assess salary levels, role distribution, and individual placement within the compensation structure.
Who is this template for?
This workbook is suitable for HR managers, founders, department leaders, payroll teams, and small-business operators who need a structured internal record of roles, salary bands, employee positions, and promotions. It is especially useful when compensation discussions require both employee-level context and a consolidated view of salary distribution by position.
