Compensation Planning: Have a R.A.R.E. Experience
By: Michael Gerthe & Lane Transou, CCP, GRP, SPHR
Long nights updating spreadsheets; Managers that modify spreadsheets beyond recognition; Last minute data changes; Ongoing adjustments to make a 3% salary budget meaningful; Inadvertent errors created from editing formulas.
Before you pull out a legacy spreadsheet and start tweaking it, consider the time you spend working to fit your data into legacy spreadsheets that become more inflexible. In other words, you are working to fit the spreadsheets rather than having the spreadsheets working for you each year. You may want to consider a better way.
A Better Way
We have recently been introduced to an approach to building Excel reports coined as “R.A.R.E.”
Repeatable – Updates are easy
Adaptable – Organizations are dynamic why not your spreadsheets
Reliable – Minimize the chance of error
Extensible – Additions can be made without a major rebuild
Because R.A.R.E. is an approach, a thought process you apply to building Excel Reports, it is also perfect for Compensation Planning spreadsheets too!
Break Old Spreadsheet Habits
Building before designing, inserting columns to mix in formulas with your data, filtering and changing your data; these are all habits with consequences. The risk in this is that you are creating a spreadsheet that cannot be easily updated. If you build in “steps”, you must reapply them when you need a newer, fresher set of spreadsheets. Instead, spend time thinking about what data you need. What data needs to be “remapped” for display values? What data values may change as decisions change? Once you think you have all the information needed…keep thinking. The key to an adaptable spreadsheet is that you have considered everything, including:
- Data you will need even AFTER compensation planning is complete;
- Values that will likely change BEFORE the compensation planning process is done;
- What data might be needed for payroll in the end.
In other words, think about all that you will need from beginning to end before you start building.
Serving it up R.A.R.E
To show a complete Compensation Planning Sheet Design would take longer than space allows in this article, but we can give you some concrete examples of RARE that you can leverage right away in building Excel Compensation Planning Spreadsheets.
R – Repeatable
Build with the knowledge you will be creating this again. Do not intermix your base load data AND formulas. This is a common mistake because most people build their compensation spreadsheets how they want to see them rather than treating it like a mini application and building something that can be maintained…i.e. repeated. In the image below, consider your Compensation Planning sheet. The Orange is data from your HRMS. The Blue, are your formulas that point to and use that data to calculate things like Compa Ratio, increase guidelines, etc. In the Typical approach many steps are required to change out the data. In the RARE approach the data can be dropped into place and ready to go in a snap.
A – Adaptable
Having identified data “sets” that can stand alone and may change, you place these on supporting tabs that can be hidden from your managers. Protect them to lock your values from those “curious” managers who like to experiment. In the example below, next year’s ranges could be in flux as well as the final budget decisions and your planning sheet will adapt to changes without rebuilding formulas.
R – Reliable
A big part of making your Excel Compensation Planning Spreadsheets reliable is to simply reduce the number of steps required to recreate them. Constructing your formulas to use data kept in one central location (like the Salary Ranges in the image above) is an example. Let’s say the midpoint changes for a salary grade. You would update it in only one place rather than having to locate impacted employees, cut & paste or key in the new value for each. This also applies when you want to “remap” values. Rather than filtering for the value you want to change and performing a manual cut and paste to reset all those values, put a simple support tab in place with a vlookup table and let formulas auto-map to this with every data load. It will always provide the same consistent results back when the data changes. Now you have avoided a time consuming manual process that creates opportunities for human error. (See example below remapping regional codes)
E – Extensible
Extensibility applies not only to the specific task at hand, but related activities as well. Because of the design in place, adding Market Data to the Compensation Planning Sheet is easily accommodated without causing a big impact on the existing, tested Excel workbook. Beyond the pure planning exercise, the data from the planning spreadsheet and all other supporting tabs can be leveraged to construct reports.
Adopting RARE starts with thinking ahead and designing before building. Start with the data, think about the total process and what might happen based on your past experience. Build knowing you will refresh and modify multiple times, decisions will change, what you start with on day one will not be the final product on day 31. This way you can reduce your human error opportunities when you trust your memory to modify compensation planning sheets. In summary, do it one time, do it thoroughly so it is Repeatable, Adaptable, Reliable and Extensible. It will return to you accuracy in reporting and time saved.
Lane and Michael will be presenting together at the HR Southwest Conference in Oct. 2013.
For more information:
You can contact Lane Transou, CCP, GRP, SPHR at Lane@LaneTransou.com or 713-882-0185. Lane is an independent compensation consultant, an active member of the SHRM Texas State Council and serves in an advisory capacity to the newly launched CompViaExcel.com.
Michael Gerthe can be contacted at Mgerthe@Dartican.com or 281-645-6477. Michael is a past president with the Houston IHRIM Chapter (International Human Resources Information Management), has been a speaker on HR Technology at national IHRIM conferences and presented “Death by Spreadsheets” at the 2011 Texas Total Rewords Conference in Dallas.