Join international Excel guru, Dianne Auld, as she demonstrates how to create and maintain pay structure and pay scale charts using exponential regression, pay scale formulas, scatter charts, combo charts and dynamic arrays. 

In this highly practical workshop for those with intermediate to advanced Excel skills, who are involved in creating or maintaining pay structures, Dianne will demonstrate how to:

• Create a scatter chart and use exponential regression to create a pay midpoint line taking into account internal employee and market pay data;
• Calculate the pay ranges for this pay line and plot the pay ranges as lines on the scatter chart;
• Create a scatter chart using the same data, but with traditional rectangular pay ranges and alpha or alpha-numeric grades on the X axis. (This is a complex chart to create since it involves combining a scatter chart, lines and stacked columns in a specific order. Once created, delegates will be able to use the chart for all their pay structuring work.);
• Calculate the median market data per grade, using traditional array formulas or Microsoft 365 / Excel 2021 dynamic arrays, and add this to the chart;
• Use Microsoft 365 / Excel 2021 dynamic array SORT, UNIQUE and FILTER functions together with the spilled range operator and data validation to create the same chart, allowing a drill-down per department; and
• Create a dashboard of department scatter charts all linked to one source data sheet.

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 demonstrated functionality with their own data after the class.  Limited seating is available.