Overall Project Mission
Develop an application to capture and calculate various compensation vehicles (salaries and bonuses) for Partners and Associate Partners within a Global Consulting Firm.
Originally, compensation calculations and data were generated and maintained in numerous individual Excel spreadsheets. Each compensation vehicle utilized a unique spreadsheet for every year of compensation information. These spreadsheets utilized numerous cross file data look-ups and excel functions to complete their required calculations. The utilization of these varied spreadsheets resulted in a number of issues.
- Data Integrity was poor due to the flexibility of the spreadsheets and compensation data was difficult to maintain accurately.
- Common shared data was distributed throughout the various spreadsheets making updates a labor intensive and error-prone process.
- Historical views of Partner Compensation were impossible to obtain without consulting numerous spreadsheets and manually gathering the required information for each report.
- The various spreadsheets did little to support the reporting requirements and other business needs of the Compensation and Taxation Organization.
The most difficult task was capturing, understanding and articulating the business rules around the calculation of each type of partner compensation. The basic compensation calculations are simple enough, but each business rule had a number of exceptions based on job title, tenure, evaluation results, location transfers, and country location.
The most complex business rule encompassed the pro-ration of active working time by Partners. One of the basic Business Rules called for individual partner bonuses to be paid out based on the percent of individual partner’s active working days within a business year. This meant that various leaves (paid and unpaid), partner elections, new hires, departures, sabbaticals and other calendar information had to be incorporated to calculate the exact percentage of the business year that each Partner would be eligible for bonuses.
Even with hundreds of business rules to account for nearly every variation of logic, compensation exceptions continually appear based on negotiated pay-out for various partners. As a result, every step in each of the compensation calculations allowed for a user to manually override the information in the database to change the resulting compensation values.
Addressing the Challenge
The only way to clearly overcome these challenges was to create detailed documentation of each business rule and their accompanying exceptions. This documentation was a product of team meetings, interviews, and code review of existing Excel files. The business rules were then reviewed with the client team and our consultants to ensure that every business variation was captured and described so that development could proceed quickly. The documentation then was used to develop test plans that could verify that each nuance was accounted for and working properly.
Solution – Rapid Application Development
The client did not have the time or budget to develop a complete compensation system, but simply needed a solution to integrate the disparate Excel spreadsheets being used for Partner Compensation.
A Microsoft Access Database was developed to store and calculate all Partner compensation data. This allowed for common shared information, such as profile information, evaluation results and tenure data to be stored, accessed and maintained in a single location. Automated data feeds allowed for updated information from back-office HR systems to be imported to ensure accurate base data.
By creating a Microsoft Access application with a relational database and a user interface, compensation calculations were streamlined and automated.
The application consisted of four major functional components:
- A central, relational database for storing Principal Compensation data
- A user interface to maintain/view Compensation Data
- A calculation engine to create Compensation information
- Standardized, automated reports using real-time data