Using Regression Analysis and Dynamic Arrays to Transform your Pay Structure Work in Excel
Course Information

EXCEL FOR REWARD – MASTERCLASS #5

Using Regression Analysis and Dynamic Arrays to Transform your Pay Structure Work in Excel

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

Date: Thursday 17th June 2021
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm
Venue: Your PC

Join International Excel Guru, Dianne Auld as she demonstrates how to create pay structures and pay scale charts using exponential regression, pay scale formulas, scatter charts, combo charts and dynamic arrays. This is a highly practical watch – it’s not only captivating but it’s also invaluable to anyone involved with creating or maintaining pay structures.

Dianne will demonstrate:

  • A quick overview of linear and exponential regression – when to use each one, the formulas used and interpretation of R squared.
  • How to create a scatter chart and how to use exponential regression to create a pay midpoint line taking into account internal employee and market pay data.
  • How to calculate the pay ranges for this pay line and plot the pay ranges as lines on the scatter chart.
  • Using the same data to create a scatter chart but with traditional rectangular pay ranges and alpha or alpha numeric grades on the X axis – this is a complex chart to create as it involves a combination of a scatter chart, lines and stacked columns. It has to be created in a very specific way.
  • Once learnt, you will use this chart in all your pay structuring work and wonder how you ever coped without it. Past students have loved the visual representation clearly showing how good a fit the pay ranges are relative to the employee pay scatter.
  • Calculating median market data per grade, using a traditional array formula or using Office 365 dynamic arrays (available to Office 365 users), and adding this to the chart.
  • Using dynamic arrays SORT, UNIQUE and FILTER formulas, array reference notation and data validation to create the same chart allowing a drill down per department.
  • How to create a dashboard of department scatter charts all linked to one source data sheet.

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.

EXCEL FOR REWARD – MASTERCLASS #5

Using Regression Analysis and Dynamic Arrays to Transform your Pay Structure Work in Excel

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

Date: Thursday 17th June 2021
Time: 1pm to 3pm (UK)
Duration: Two hours including five-minute break at approx. 2pm
Venue: Your PC

Join International Excel Guru, Dianne Auld as she demonstrates how to create pay structures and pay scale charts using exponential regression, pay scale formulas, scatter charts, combo charts and dynamic arrays. This is a highly practical watch – it’s not only captivating but it’s also invaluable to anyone involved with creating or maintaining pay structures.

Dianne will demonstrate:

  • A quick overview of linear and exponential regression – when to use each one, the formulas used and interpretation of R squared.
  • How to create a scatter chart and how to use exponential regression to create a pay midpoint line taking into account internal employee and market pay data.
  • How to calculate the pay ranges for this pay line and plot the pay ranges as lines on the scatter chart.
  • Using the same data to create a scatter chart but with traditional rectangular pay ranges and alpha or alpha numeric grades on the X axis – this is a complex chart to create as it involves a combination of a scatter chart, lines and stacked columns. It has to be created in a very specific way.
  • Once learnt, you will use this chart in all your pay structuring work and wonder how you ever coped without it. Past students have loved the visual representation clearly showing how good a fit the pay ranges are relative to the employee pay scatter.
  • Calculating median market data per grade, using a traditional array formula or using Office 365 dynamic arrays (available to Office 365 users), and adding this to the chart.
  • Using dynamic arrays SORT, UNIQUE and FILTER formulas, array reference notation and data validation to create the same chart allowing a drill down per department.
  • How to create a dashboard of department scatter charts all linked to one source data sheet.
Virtual classroom: Thursday afternoon 17/06/2021 - 17/06/2021