Insurance and Benefits Processor


CaseStudyImage

Technologies:

ASP.NET Web Forms, VB.Net, IIS, Microsoft Sql Server, Microsoft Excel, Quickbooks

Overview:

Create an application to track employee enrollment in benefit plans, and match corresponding monthly remittance amounts to the employee enrollments for multiple contracted Employers, and generate reports in Excel format.

Details:

We were approached by “Client A” to create an application to track employee enrollment in benefit plans, and match corresponding monthly remittance amounts to the employee enrollments for multiple contracted Employers. Additionally, the application would allow users to document the difference between enrollments and remittances per employee and generate Excel files documenting the amount remitted by each employee to be sent to the insurance vendors along with the remittance check. A further requirement was the generation of Quickbooks .iif files documenting contributions to healthcare savings accounts.

This would be an ASP.Net Web Forms application with a Microsoft Sql Server back end. The employee enrollments are created by importing Excel files that list each employee and their dependents, and the benefits for each. The enrollments are automatically advanced every period so that they are automatically kept current. The periodic remittances are imported as Excel files as well, and occur once for each contracted Employer per month.

Challenges:

One of the main challenges we faced was designing a database structure that enabled the application to automatically advance the employee enrollments from one period to the next and was flexible enough to handle multiple payment period scenarios (monthly, weekly, bi-weekly, semi-monthly, etc). We met this challenge by creating a system that kept track of start and end dates for each period, and scripting the creation of these periods to meet the requirements of the payment scenario.

Equally challenging was addressing every remittance resolution scenario (refund, short, over, perfect) and creating Excel reports with line items that matched to the penny when dealing with report totals in the 6 figures.

Final Completion:

Upon final delivery of our solution we had a intranet-based web application that enables our client to document the enrollments and remittances of tens of thousands of employees across dozens of companies and generate reports that match to the penny across millions of dollars.


Back