Compensation System Development

Overall Project Mission

Develop an application to capture and calculate various compensation vehicles (salaries & 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.

Challenges

Business Rules – One of the most difficult tasks in the project was the capturing and definition of business rules for the calculation of each type of partner compensation. The basic compensation calculations were simple enough, but each business rule had a number of exceptions based on job title, tenure, evaluation results, location transfers, and country location.

Pro-ration - The most complex business rule encompassed the pro-ration of active working time by Partners. Individual partner bonuses were 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.

Exception Handling - 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 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 needed a solution quickly and did not have the time to develop a complete enterprise 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:

1)  A central, relational database for storing Principal Compensation data

2)  A user interface to maintain/view Compensation Data

3)  A calculation engine to create Compensation information

4)  Standardized, automated reports using real-time data