Database Design and Implementation
1. Corporate Overview
The Delves Group, LLC was a boutique consulting firm specializing in executive compensation and corporate governance. The company was founded in 2001 and was based in Chicago, Illinois. In 2013, The Delves Group was aquired by Towers Watson & Co.
Annually, The Delves Group published a Bank Compensation and Benefits Survey. The survey provided community banks and credit unions with valuable competitive compensation data so they can keep pace with today’s continuously evolving financial environment.
2. Challenge
The Delves Group had conducted the Bank Compensation and Benefits Survey for approximately 10 years. Historically, the survey data was distributed, housed, and analyzed in Microsoft Excel workbooks. However, as the number of survey participants continued to grow, the company encountered a sustainability challenge. This created control and usability issues for the analysts.
When I was assigned as Co-Director to the project in 2012, the survey consisted of 123 bank positions, including both executive and ordinary service positions. The questionnaire issued to banks required respondents to answer detailed questions concerning the institutions’ compensation practices, employee benefits, annual incentives, long-term incentives, and total compensation plans for each of the 123 positions. One hundred and fourteen institutions responded and supplied meaningful data and information for the survey. In total, each questionnaire contained 993 data points for a sum of 113,202 data points to be analyzed.
3. Solution
Although I was involved in all aspects of the survey project, including the sales, marketing and pricing analysis, I predominately worked on process improvement. I recognized that an Access database would be most appropriate solution for a project of this size.
Beginning with database design, I built 16 tables to house the compensation data, which included base salary, annual incentive, long-term incentive, and benefit data. Then, I overhauled the questionnaire that was issued to banks and wrote a macro to appropriately import the data into the Access database. I also built two queries: one to export a cut of the data for analysis and another to arrange the data for reporting.
In the final stages of the project, I designed an Access report to enumerate all collected questionnaire data and analyzed this data according to the outlined parameters of asset size and geographic region classification. Survey results were then reported by bank asset category at the national and regional level. Completed tables for each of the 114 bank positions were published in a user-friendly PDF report that was available for purchase.
4. Impact
Using an Access database, I was able to create a sustainable process for analyzing and reporting large quantities of compensation and benefits data for community banks and credit unions. I wrote step by step documentation on how to recreate this report. In turn, this has immensely helped The Delves Group and its associates as they have continued to reproduce the report annually. Besides my main goal of process improvement, the team and I were also able to generate $60,000+ in sales for The Delves Group by preparing the results for PDF and print distribution.
