Process Innovation through Technology — the Compensation Worksheets™
the Compensation Worksheets™
Sector: Business service
Timeframe: 1984 to 1993
Affiliation: Thorne Stevenson & Kellogg (and successor firms)
Conceived, created, packaged, maintained and supported the Compensation Worksheets™, an early business app to assist with job evaluation and compensation administration.
BACKGROUND
In the early 1980's large scale salary and benefit surveys were tabulated manually, comparative data was collected by telephone call, and market trend lines were eyeballed.
On a summer work term with Thorne Stevenson & Kellogg in 1981, Michael seized an opportunity to add computational rigour and to apply emerging information technologies to job evaluation and compensation administration.
In 1982 his undergraduate thesis "A Database System for Compensation Administration" established a vision that the introduction of Lotus 1-2-3 would later help to fulfil.
PAIN POINTS
- The Aiken Plan, a point-factor ob evaluation system used at TS&K was highly regarded for objective outcomes — but its computational requirements were prone to error
- The analytical work of creating pay ranges was more art than science due to the effort needed to examine more than one or two alternative structures
- Market trend lines established by eyeball looked good on the data — but did not lend themselves to quantitative estimates of accuracy that were needed for clients
OUTCOMES
- The Compensation Worksheets™ were used from 1984 through 1994 in pay studies conducted across Canada by Thorne Stevenson & Kellogg and its successor firms (Stevenson Kellogg Ernst & Whinney and Peat Marwick Stevenson & Kellogg, later KPMG).
- Clients received the software and support following the conclusion of a pay study, and used the software to maintain the recommended pay plan by incorporating new and modified positions.
- The Compensation Worksheets™ were instrumental in completing pay equity work at large institutions, notably in a multi-year initiative at the University of Alberta.
- The broad success of the Aiken Plan accompanied by the Compensation Worksheets™ led to the use of the software by allied consulting firms in countries in addition to Canada.
CONSULTING RESPONSE
In 1984, combining his undergraduate thesis design with the capability available in Lotus 1-2-3, Michael began creating a suite of integrated, macro-driven spreadsheets. The Compensation Worksheets™ quickly evolved to six integrated 1-2-3 worksheets:
- New Jobs — Providing a flexible structure for recording positions, the Aiken Plan factor evaluations and associated position coding data required and optional for the analysis.
- Job Eval — Building the formula to compute total points based on the factor evaluations, and to format and transfer data for use in subsequent worksheets.
- Factor — Building factor ladder charts for any or all ten factors in the Aiken Plan, grouping positions in columns by areas determined by the user.
- Ladder — Establishing the internal trend line for the positions under review; creating, assessing and fine tuning a level structure based on bands of total point scores; and building or updating a ladder chart by grouping posititions into columns by areas determined by the user.
- Survey — Identifying positions under review for a pay survey; building a template for recording comparative results for organizations being surveyed; and establishing the market trend and descriptive statistics needed in the pay study and reported back to survey participants.
- Results — Applying the market trend to build or update a pay structure, by positioning the structure at, above or below market and establishing pay bands; applying the new or revised pay structure to positions under review; and applying the results to incumbents in those positions.
To facilitate use and application of the Compensation Worksheets™ Michael maintained a comprehensive manual and training program for use by clients and consulting colleagues, and assisted colleagues with providing support to clients using the software.