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 that are involved in creating or maintaining pay structures, Dianne will demonstrate:
- 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, create a scatter chart 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, and once created will allow delegates to use the chart in all their pay structuring work);
- Calculating the median market data per grade, using traditional array formulas or Microsoft 365 dynamic arrays, and adding this to the chart;
- Using Microsoft 365 dynamic array ‘sort’, ‘unique’ and ‘filter’ formulas, array reference notations and data validation to create the same chart, allowing a drill-down per department; and
- How to 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.
Date: 25 August 2021
Presenter: Dianne Auld