Study Method
Blended
Start Date
Mar 10 2022
Length of Course
TBA
Fees
195 per person
Location
TBA
Using Regression Analysis and Dynamic Arrays to Transform your Pay Structure Work in Excel
EXCEL FOR REWARD – MASTERCLASS #5
Using Regression Analysis and Dynamic Arrays to Transform your Pay Structure Work in Excel
Part of our Excel for Reward Masterclass series: click here for more details about the five afternoon sessions.
Date: Thursday 10 March 2022
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.
“Reward Blueprints: 'Just like to reiterate what a great service this is and look forward to another 12 months of highly useful research reports.'”
“ Excel April 2015: 'Good central location. Course content was well put together, very relevant to work and was made interesting by Dianne. I would highly recommend the course to any”
“ Annual conference 2014: ‘Good quality of speakers. Excellent day' . . . ‘Excellent networking. Good content. Thank you so much for a great event’ . . . ‘Very informative and thou”
“Annual conference 2015: ‘Both my colleague and I agreed that it was definitely worth the investment to fly over – a great selection of presenters, wonderful networking and thought p”
“ Annual conference 2015: ‘An excellent range of topical topics and good/expert presenters. Very good value for the price!’”
“Bespoke data: 'I appreciate the hard work that has gone into this. I would most certainly encourage others to buy this from you. Very grateful you got it to me so quickly.'”