Many of you will be gearing up for another year end review process still stuck using spreadsheets to administer your compensation programs. While we cannot transform your experience without software like COMPOSE in place, it helps to have a checklist handy for manual tasks.
Here’s my list of useful tips to help you avoid the limitations and common pitfalls imposed by spreadsheets:
1. When updating/rolling over from last year’s file to now, are your naming conventions still correct? Ensure column headings are valid especially if you do not have specific dated headings and are using columns headed as current salary, prior salary, last increase, etc.
2. When comparing the historical file and current downloaded file with same populations or adjusted populations (removing terms, transfers out) make sure that expected totals such as 2017 bonus are equal.
3. Update your Model so that it reflects your new organizational structure and for managers who will be involved in the review and approval process.
4. Ensure security by updating passwords frequently, as well as protecting and locking cells that you do not want anyone to revise.
5. Review all formulas and ensure that the data being pulled into calculations are from the correct fields.
6. Update foreign exchange rate tables/conversion rates.
7. Check that data uploads are being pulled into correct fields.
8. When combining files and/or uploading files check calculations to ensure data totals have correctly loaded (sums in combined files need to equal data from individual files).
9. Update and load current market data information.
10. If possible, have someone with fresh eyes do a careful review of the formulas and the data results being calculated. Check the logic of your formulas.
11. Be extra careful when working with large files or workbooks where you are applying formulas to multiple cells, linking multiple spreadsheets or connecting to other workbooks.
12. Always, always, always maintain a clean copy of your final historical files. Do not amend or overwrite them. In case of any errors updating fields, you will want and need a clean reference file to go back to.
Whew! On top of all this, while Excel is flexible and powerful, it lacks data integrity! No matter how careful you are, no matter how many times you check for typos and broken formulas, there’s a chance you’ll miss something.
That’s a lot of sheet to worry about!
