Recently some of our clients had the need to provide payroll and benefits data in compliance with IRS form 1095 and the Affordable Care Act (Obamacare). These particular clients are using Greatland’s software to submit their 1095 data.
Our clients, the payroll companies, have their data contained in an SQL database – using Time Plus – a payroll management software. The benefits data however, is not held by them. Instead, it is held by their client or their client’s benefits management company.
Our clients needed a way to simply and uniformly gather the data from their client’s or their client’s benefits company – in order to quickly and efficiently bring that data into the Greatland product.
We built a data aggregator tool using Excel. It allows the payroll company to select which client needs to provide 1095 data. It builds a custom macro spreadsheet with protected sheets and data. That spreadsheet is provided to their client and contains a form and automation to help the company or their benefits company fill in the necessary form 1095 data.
Once complete, the company sends the sheet back to the payroll company. Based on their input, the 1095 data and import to Greatland is automatically created and available for upload.
See the image below for a visual idea of what we built.
Our primary tools for achieving this:
- Microsoft Excel VBA with custom forms
- SQL Server
This is a relatively simple solution that provides a dramatic savings of time and effort for all the parties. Additionally, it allows for rapid corrections of data should data be bad or missing.
Due to privacy issues, we cannot demonstrate the product in use. We are working on some dummy data and may provide a video of the process shortly.
Image: The Process:
(click image to open in a new window)