Case Study: Unwavering Commitment to a Complex Database Project
A myriad of legal rules added unforeseen complexity to a fixed-price database project ZurelSoft performed for McGregor & Associates, a third-party administrator for health insurance trusts.
A trust is a group of employers who purchase health insurance collectively to lower costs. As administrator, McGregor & Associates verifies participants’ eligibility and facilitates their enrollment in the health plans held by these trusts.
Drowning in Excel Spreadsheets
Paul Bergman, VP of IT for McGregor & Associates, approached ZurelSoft for a custom software solution because his company needed to fix an unwieldy process tracking individual healthcare accounts of more than 40,000 retirees from eight different trusts.
“All of that was done in Excel spreadsheets,” Bergman said, “which means that these Excel spreadsheets, over time, got really, really large and more and more cumbersome. So we needed a client-server database application in order to manage that. We had to move away from Excel.”
Bergman also wanted to reduce the number of phone calls — and the associated costs — required to administer each claim for these accounts by creating an online system that retires could access.
What complicated the project were the structural rules defined by each trust that dictate how people earn credits toward their retiree healthcare accounts. These credits determine how much money is deposited into an account.
“Sometimes you earn a credit for every two weeks you work. Sometimes you only earn credits until a certain age. Sometimes after a certain age you earn twice as many credits. Often, if you were hired before a certain date, the number of credits you earn is more or less. And all of that factors into what [retirees] are able to pull out of their account on a monthly basis.”
Another issue was the data from various trusts more that sometimes was provided via CSV files, and these types of files tend to have a lot of errors. The software had to handle a variety of complexities.
A powerful, customizable solution
While the project turned out to be more complex than either ZurelSoft or McGregor & Associates anticipated, the teams worked closely together to decipher those rules and implement a process to capture those rules. ZurelSoft created a customizable module or algorithm for each trust that would handle the specific vesting criteria of that trust. A robust file-import mechanism was developed to validate and load data from the employers and protect the system from errors. Also, members were provided a secure method of getting up-to-date account balances without the need for phone calls.
And that unwieldy process that once took days via Excel takes just hours using a custom software solution that McGregor & Associates has named Titan.
“The Titan system is really very powerful,” Bergman said. “We can now deliver everything that the trusts need. Plus, the SQL database has opened up lots of valuable reporting options that allow us to exceed customer expectations.”