Creating Self Updating Grading Matrices
Course Information

EXCEL FOR REWARD – MASTERCLASS #2

Creating Self Updating Grading Matrices

Part of our Excel for Reward Masterclass series: click here for more details about the six afternoon sessions.

Date: 7 October 2024
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm. 90-day access to video recording of Dianne’s live presentation.
Venue: Your PC

Download brochure: Click here


Once your jobs have been evaluated, using either a job content or market pricing approach, your reward team and management need to audit and view the grades by grade and by function (or job family, discipline, business unit, division, area, company).

To conduct a meaningful audit, the jobs need to be in a matrix form rather than in a long list. This enables an equity / consistency check:

  • horizontally – similar grades across different functions
  • vertically – grade differences within each function

This matrix also presents the job grade data in a user-friendly and accessible format for viewing of grades and determining of career paths, as shown in the screenshot.

macros image

All too often, these grading matrices are high maintenance and error prone, as:

  • jobs change constantly – new jobs, obsolete jobs, changes to job title or grade
  • organisation structures and function names often change
  • reward practitioners need to remember to update the matrix when jobs / grades / functions change
  • the update is tedious and time consuming, as each job needs to be placed in the right function and grade, old jobs need to be moved or deleted, checks need to be done for duplicates
  • the number of rows in the matrix constantly has to be adjusted to cater for more or less jobs in each grade; lots of blank rows left in the matrix make it difficult to read and print

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

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

Attendees will be provided after the session with the Excel spreadsheets demonstrated and detailed instructions, so that they can replicate the functionality with their own grading data.

Who should attend?

Reward and HR practitioners who work with grades and would like to know how to transform data from a list to a matrix.


Live-streamed masterclasses. Attend from your home or any location. No travel required. Recordings of each masterclass available for 90 days after the virtual class.

Dianne Auld is an international guru who makes tricky reward-related Excel tasks transform into solutions in a blink of an eye. Dianne will show reward professionals a range of Excel time-saving skills. Her five masterclasses will dramatically increase your productivity and present spectacular ways to analyse mountains of reward data in seconds with professional looking charts, dashboards and Pivot Tables.


Fees

Per masterclass: GBP £195 + VAT per delegate. Attend all six masterclasses: GBP £995 + VAT per delegate.

To ensure an effective learning experience, there are just 20 seats available for each masterclass.

Cancellations: Please note that delegates will be held liable for the full fee if registration is not cancelled by 2 Jan 2023 or if the delegate is a 'no show'.

Dianne Auld, GRP, CCP, CSCP
Dianne Auld, GRP, CCP, CSCP
Senior Consultant

Dianne is the founding member of Auld Compensation Consulting, operating out of Cape Town, South Africa. Her consulting work around the world covers all areas of total rewards, including reward strategy, base pay management, job evaluation, pay structuring, incentive design and sales compensation. She has developed and taught Excel and rewards courses in Africa, the Middle East, Asia, Europe, and the United States.

As a WorldatWork faculty member, Dianne teaches the GRP reward qualification courses in Southern Africa. She is the author of Dianne Auld’s Excel Tips, 3rd edition. Dianne has developed and recorded several e-learning products for WorldatWork, including Excel Skills, Advanced Excel Skills and Pay Structures. She is an honorary life member of the South African Reward Association (SARA) and an accredited Master Reward Specialist.

Location

Live streamed from Dianne's office in Cape Town, via Zoom

Start time & end time

1pm to 3pm / 4pm (UK)

How does this masterclass work?

Dianne suggests that you don’t try to follow along with her demo on your computer. She will proceed at a reasonably smart pace and you might get lost. Just watch and take notes if desired.

Recording of live-streamed class

You will have access to a video of the masterclass for 90 days from the date of the masterclass so that you can replay at your leisure and practise the steps.

Documentation

Attendees will be provided with detailed instructions for replication of the functions demonstrated by Dianne after the masterclass.

EXCEL FOR REWARD – MASTERCLASS #2

Creating Self Updating Grading Matrices

Part of our Excel for Reward Masterclass series: click here for more details about the six afternoon sessions.

Once your jobs have been evaluated, using either a job content or market pricing approach, your reward team and management need to audit and view the grades by grade and by function (or job family, discipline, business unit, division, area, company).

To conduct a meaningful audit, the jobs need to be in a matrix form rather than in a long list. This enables an equity / consistency check:

  • horizontally – similar grades across different functions
  • vertically – grade differences within each function

This matrix also presents the job grade data in a user-friendly and accessible format for viewing of grades and determining of career paths, as shown in the screenshot.

macros image

All too often, these grading matrices are high maintenance and error prone, as:

  • jobs change constantly – new jobs, obsolete jobs, changes to job title or grade
  • organisation structures and function names often change
  • reward practitioners need to remember to update the matrix when jobs / grades / functions change
  • the update is tedious and time consuming, as each job needs to be placed in the right function and grade, old jobs need to be moved or deleted, checks need to be done for duplicates
  • the number of rows in the matrix constantly has to be adjusted to cater for more or less jobs in each grade; lots of blank rows left in the matrix make it difficult to read and print

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

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

Attendees will be provided after the session with the Excel spreadsheets demonstrated and detailed instructions, so that they can replicate the functionality with their own grading data.

Who should attend?

Reward and HR practitioners who work with grades and would like to know how to transform data from a list to a matrix.

Virtual classroom 07/10/2024 - 07/10/2024
THE FOLLOWING TERMS AND CONDITIONS APPLY TO THE DELIVERY OF EDUCATION COURSES BY E-REWARD.CO.UK LTD (INCLUDING MASTERING REWARD COURSES; WORLDATWORK GLOBAL REMUNERATION PROFESSIONAL CERTIFICATION [GRP] COURSES AND EXAMINATIONS [PROGRAM]; IN-HOUSE AND CUSTOMISED TRAINING COURSES).

These terms and conditions apply between the person, firm, company or other entity specified on your booking form and E-reward.co.uk Ltd (company number: 4281768). Registered in England and Wales. Registered office 33 Denby Lane, Heaton Chapel, Stockport, Cheshire SK4 2RA, United Kingdom for delegate registrations for the course (including Mastering Reward courses, GRP courses with exam and in-house training courses) specified on your booking form (‘Course’).

Please read these terms and conditions carefully as they contain important information.

By submitting your registration to attend the Course you agree to be bound by these terms to the exclusion of all other terms. If you do not agree to be bound by these terms E-reward.co.uk Ltd will be unable to accept your registration.

Delegate cancellations

Delegates who advise E-reward.co.uk Ltd of their cancellation in writing via email (to paul@e-reward.co.uk) 30 calendar days before the start of the Course will have their fees refunded – less an administration charge of 20% of the Course fees.

No refund will be made for cancellations received less than 30 calendar days before the start of the Course.

Failure to attend the Course will be subject to the same terms.

  • GRP courses featuring pre-recorded online videos – If your GRP course includes access to pre-recorded video training, once this access has been provided to you (at least four weeks prior to the accompanying virtual classroom day), no refund of delegate fees will be payable.
  • In-house training – You will not have any right to cancel a Contract for the booking of any customised/in-house training courses. For the avoidance of doubt, no refund will be payable

Delegate transfers

Transfers may be made to any other Course in the UK organised by E-reward.co.uk Ltd subject to space availability. All transfer requests must be made in writing via email (to paul@e-reward.co.uk) 30 calendar days or more before the start of the Course.

There will be no fee charged for such transfers. One free transfer can be made for each course booking after which there will be a £100 administration charge for each further request.

Please note: We are unable to accept any transfer requests received less than 30 calendar days before the start of the course and no refunds will be made.

We are unable to accept any transfer requests once you have received access to GRP pre-recorded learning videos.

Substitutions with employees from your organisation are welcome at any time at no extra charge. You must email any substitutions (to paul@e-reward.co.uk) prior to the date of the Course.

Please note: We are unable to accept any ‘substitutions’ once you have received access to GRP pre-recorded learning videos.

Payment

You will pay E-reward.co.uk Ltd any fees specified in your booking form for the Course. Payment to E-reward.co.uk Ltd in GBP British Pounds is by BACS, wire transfer, cheque or credit card. It is a condition of booking that payment must be received by E-reward.co.uk Ltd before the start of the Course or no later than 30 days from the date of invoice, whichever is sooner.

If payment of your fees in full is not received before the Course, E-reward.co.uk Ltd may (at its sole discretion) either require such payment as a condition of your entry to the Course or refuse you entry to the Course.

Prices for each Course, where applicable, are correct at the time of publication. We reserve the right to change the prices at any time but changes will not affect registrations which have already been confirmed by E-reward.co.uk Ltd.

Each ticket covers attendance for one person only for the duration of the Course, and includes coffees, teas and light snacks during the lunch hour, as well as course materials.

All bookings are subject to value added tax (VAT) at 20%. You shall, on receipt of a VAT invoice from E-reward.co.uk Ltd, pay to E-reward.co.uk Ltd such additional amounts in respect of VAT as are chargeable on the supply of the Course.

Late payments

We understand and will exercise our statutory right to interest and compensation for debt recovery costs under the late payment legislation if we are not paid according to agreed credit terms. Under the Late Payment of Commercial Debts [Interest] Act 1998 – as amended and supplemented by the Late Payment of Commercial Debts Regulations 2002 (to incorporate the features of European Directive 2000/35/EC) – we are entitled to charge:

  • Debt recovery costs – £40 on debt up to £999.99; £70 on debt between £1,000 and £9,999.99.
  • Statutory interest – 8% above the Bank of England reference rate (fixed for the six-month period within which date the invoices became overdue).

Joining instructions

Final Course details will be emailed to delegates at least three weeks before the Course.

Course documentation

You will receive your course materials by email ahead of the Course.

Hotel accommodation

Delegates are responsible for their own accommodation.

Course cancellations and amendments

The Course programme is correct at the time of going to press.

E-reward.co.uk Ltd reserves the right to change the format, tutors, venue location and programme or any other aspect of the Course at any time and for any reason, whether or not due to a Force Majeure event, in each case without liability/refund.

‘Force Majeure Course’ means any event arising that is beyond the reasonable control of E-reward.co.uk Ltd including (without limitation) speaker or participant cancellation or withdrawal, supplier or contractor failure, venue damage or cancellation, health scares, industrial dispute, governmental regulations or action, military action, fire, flood, disaster, civil riot, acts of terrorism or war.

E-reward.co.uk Ltd reserves the right to change the date or cancel a Course at any time and for any reason without notice. Where circumstances force E-reward.co.uk to cancel a Course, E-reward.co.uk Ltd shall offer you the option of attending any rearranged Course that E-reward.co.uk Ltd chooses to organise.

If you do not wish to accept this offer, then you will (as your sole remedy) be entitled, at your discretion, to receive either a credit note or a refund in respect of your fees received by E-reward.co.uk Ltd for that particular Course.

However, where the Course is postponed for reasons due to a Force Majeure event, any of your fees received by E-reward.co.uk Ltd shall be applied to any rearranged or rescheduled Course and all these Terms and Conditions shall apply to any such transferred booking.

To the fullest extent permitted by the applicable law, E-reward.co.uk Ltd shall not be liable to you for any loss, delay, damage or other liability incurred resulting from or arising in connection with the cancellation or date change of the Course howsoever arising or any venue change.

For the avoidance of doubt, you acknowledge and agree that should the Course date be changed or cancelled, E-reward.co.uk Ltd is not liable for any travel or accommodation costs you may have incurred.

How we use your registration information

E-reward.co.uk Limited is a registered data controller under number Z7317541.

Your Course registration information will be added to our marketing database but will not be given to third parties without your prior consent.

E-reward.co.uk Ltd uses email addresses for its own purposes of keeping customers informed about Courses and providing product, service and information announcements.

If you supply us with your postal address online, you may receive periodic mailings from E-reward.co.uk Ltd with information on new products and services and upcoming Courses.

If you do not wish to receive information about products and services, please let us know by email (to paul@e-reward.co.uk).

Disclaimers

To the fullest extent permitted by the applicable law, E-reward.co.uk Ltd excludes:

  • all liability for loss, injury or damage to persons or property at the Course;
  • all indemnities, warranties, representations, terms and conditions (whether express or implied); and
  • any actual or alleged indirect loss or consequential loss howsoever arising suffered by you or any loss of profits, anticipated profits, savings, loss of business revenue, loss of business, loss of opportunity, loss of goodwill, or any other type of economic loss (whether direct or indirect).

If E-reward.co.uk Ltd is liable to you for any reason, its total liability to you in relation to the Courses (whether under these terms or conditions or otherwise) is limited to the amount of your fees received by E-reward.co.uk Ltd.

Views expressed by the Course instructors are their own. All advice provided at this course is for general guidance only. Any course delegates relying on information or advice given in the course of such training do so at their own risk.

E-reward.co.uk reserves the right to amend these terms and conditions from time to time. However, you will be subject to the terms and conditions in force at the time you submit your registration.

Further information

If you have any questions, please email us at paul@e-reward.co.uk