ACA Reporting“Good news is the due date for paper forms for the employees has been pushed back to March 31st and the due date to the government is not until June 30. The bad news is that we have to send the data to the government electronically.”

“Oh, I would think that is just an upload.”

“Hmmm, maybe. Somehow I’m getting the impression it’s not quite that simple.”

And so, the rush was on to beat the deadlines.

It’s late January 2016 and I’ve been handed the project of developing the Healthcare forms to send out to the employees and then send the data to the IRS. I only knew Obama care at a very high level: everyone was required to get health insurance and a lot of people hated it.

With just 2 months before the March 31st deadline, I had to research the healthcare, figure out how to “program” my file to apply the proper codes, and automatically fill out the paper forms.

But first some background: I work for a small company in property management that is rapidly expanding but is nowhere near to being a large company that it can afford to spend a lot of money. Healthcare was outsourced to a broker but not the governmental reporting piece. That was our responsibility.

As of the later half of January, we were behind the ball in terms of being prepared to do the healthcare reporting but our small size has one advantage: we did not have to report as many forms.

For the March 31st deadline, we made several tries as we worked through the data. After some fits and starts, by mid-March I was eventually able to fill out 75% of the forms automatically. The remaining 25%  had to be carefully scrutinized to make sure the data was clean. The biggest problem I had, aside from the data quality, was filling out the government forms automatically. There’s the conversion of PDF into Word which happened relatively smoothly, and then there’s performing mail merge. It’s that mail merge that caused me problems, but I  finally found a little trick in dealing with numbers in mail merge. With a bit of round the clock work, especially over the last weekend, we were able to mail out the forms about a week before the deadline.

The next deadline was June 30th and that was a bit more problematic. For starters, The upload is not just any old upload; the upload had to be XML which I had no clue. Fortunately, Excel does provide conversion-to-XML capabilities but unfortunately, the government’s schema was too complicated for Excel to handle. That Excel/XML complication was appearing to be a showstopper. I figured out a trick to get from Excel to XML and then build up the rest of the program to match the specifications of the government. But until I got the final acceptance from the government, I researched alternatives in case my trick was not successful. We pursued the idea of outsourcing the conversion of our data and submitting them but that turned out to be too expensive: about $25K as of end of April. We then pursued the waiver application for sending the data by paper rather than electronically but we did not get the waiver from the government in time. Finally, about a week before the deadline,  I received the acceptance with errors message from the government. So, now I know my creative process works.

If you are curious, the high level discussion of how to do the electronic submission starts here: ACA How-To Part I.