Excel Masterclass Creating Grading Maps with Dianne Auld

Date: 2 June 2022
Time: 09:00 – 11:00
Presenter: Dianne Auld
Venue: Zoom
Cost: SARA Paid-Up Members: R990 | Non-SARA Members: R2 400
CPD: 2 points

Grading maps (or matrices) are useful tools used by Reward teams and management to display and audit grades by grade and by function (or job family, discipline, business unit, division, area, company). These maps enable equity and consistency checks:

•    Horizontally – similar grades across different functions; and
•    Vertically – grade differences within each function.

The grading map presents the job grade data in a user-friendly and accessible format for viewing of grades and determining of career paths, but is usually high maintenance and error prone, as:

•    Organisation structures and function / division names often change;
•    Jobs change constantly – new jobs, obsolete jobs, changes to job title or grade;
•    Reward practitioners need to remember to update the matrix when jobs / grades / functions change;
•    The update is tedious and time consuming; and 
•    The number of rows in the matrix constantly has to be adjusted to cater for more or less jobs in each grade. 

During this Excel masterclass, Dianne will demonstrate how to transform this time-consuming and error-prone process into a streamlined, accurate, low-maintenance approach. Delegates will be shown how to:

•    Use Power Query to create the source data for the grading map from payroll / HRIS data;
•    Create a grading map that updates automatically from a list of functions, jobs and grades;
•    Add an extra dimension / drop-down to the grading map, so that the organisation can have a separate grading map for each company, geographical area or business unit, all reading from the same list of jobs;
•    Record a macro that will adjust every grading map in the file to show the required number of rows per grade;
•    Update all grading maps from payroll / HRIS data with two clicks; and 
•    Create the grading maps using formulas (for legacy Excel users) or dynamic arrays (for Microsoft 365 users).

Delegates will be provided with the session recording, Excel spreadsheets demonstrated and detailed instructions after the session.  This way they can focus on Dianne’s demonstration and explanation of the methodology during the masterclass, and then replicate the functionality with their own grading data after the class.