My eyes are starting to swim from looking at rows upon rows upon rows of data. I'm working on that ACA reporting again this year, using my Excel spreadsheet to assign the code 1 and code 2 series, based upon hire/term dates and medical enrollment dates. Last year's set of algorithms got me through about 75% of the data before I had to do it manually. We had 2000 or 2500 rows (I can't remember exactly). This year we have 5600 rows.
Late last year, my company changed their payroll system so I am reviewing the logic of the data. Most of it seems to hold but there is a set that I need to go over with our HR VP. The data from our medical provider also feels different so I tweaked the algorithms a little to fit this year's data.
Because of the changes, I'm reviewing all of the rows by batches: those who did not work in 2016, those who worked all of 2016 and enrolled, those who worked all of 2016 and waived, those who came on board in 2016, and so on. By approaching the review in batches like that, it makes it easier to review the codes. The algorithms for the codes seem to be holding up very well. In fact, it feels like they are doing much better than last year. It would be nearly impossible to assign codes if I did not have those algorithms set up. Oftentimes, the algorithms would catch something that my eyes did not.
I'm on my third round of reviewing the results. I'm hoping I'm understanding the logic of the data now. The first two times I had to re-do after I approached our HR VP with a sample to make sure my understanding of the data was correct. Each time I had to go back to the drawing board. Fortunately, I didn't have to re-do the algorithms that assign the code 1 and code 2 series. The problems centered on what were the true hire dates and the medical enrollment and medical term dates. Those algorithms are less complicated than those assigning the codes.
Tomorrow I hope to tabulate how much I've completed and lay out the questions that need to be answered. Hopefully, I'm at least 80% done. I would like to do better than last year.