Various Activities Going On
I am time crunched right now as I am working on the ACA, so I will stick to some quick notes on a couple of things.
How come I haven’t thought of this before?
Two or three weeks ago, I was reviewing some numbers, especially looking for things such as “#N/A” or “#VALUE!” or “#DIV/0!”. I either try to look for those errors using my eyes or use the filter method where I check to see if those errors are one of the options in a filter list. Either method is painful. The eyeball method really depends on my ability to spot those errors and the filter method requires checking each column’s filter to see if an error is listed. With 35 columns to go through, the filter method is a bit time consuming. And then, two or three weeks ago, I suddenly had an idea to use conditional formatting which is waaaaaayyyyyyy easier on my eyes. This week some errors cropped up and BAM!, I could see the error right away.
Why didn’t I do this before? It’s so simple. Just highlight the area that you want to check for errors, say “=$C$5:$AK$93”, go to Conditional Formatting in the Home tab and use ISERROR function as the formula:
ISERROR(C5)=TRUE
Designate the color you want to use in the conditional formatting and you’re set.
So you can see what I mean, the first picture is the "before" where I had no conditional formatting
Now the next one has the conditional formatting of blue to highlight the errors. It is soooo much easier to spot the errors.
1099 Project
Last year and the year before I worked on converting the 1095C form from PDF into Word so I could do a mail merge, using Excel to fill in the form. Once the PDF was converted into Word, I had to do some tabbing and spacing to insert the fields (name, address, code 1 series, etc.) for mail merge. It’s fairly easy, once you figure out how to do it. I thought once I nailed the 1095C, the 1099 Misc. project should be straightforward.
Except the online form found at the government site can’t be used. I tried converting the form into Word and right away I saw something wrong. It’s just not usable. Instead, you have to order the form from the government. Fortunately, accounting had ordered the paper forms so I used those forms to scan into PDF files and then converted the PDF to Word. Once I did that, the form looked way better but it still was different than the 1095C form. You just can’t tab to the proper fields for insertion of the mail merge field. It turns out the form has an upper layer that is kind of like a picture showing the form and the bottom layer as a typical blank Word. If you click on the form, you should see gray square “handles” around the four corners of the form. You can move the top layer to get to the underlying Word document. But it’s best not to move using your mouse; it is better to move by using the arrows on your keyboard. Moving with a mouse makes the upper layer click to a hidden grid as if the grid is a magnet and it will end up being slightly off. Using the arrows gets around the hidden magnetic grid. You have to do some playing around to map out where in the Word document to insert the
fields because once you move the upper layer, you lose sight of where the fields lie on the form (the picture has been moved). So you have to move the picture out of the way, insert your field, and then move back the picture to its original position so you can find the placement for the next field to insert.
And now it's ACA season!
Right now I’m now scrubbing through the data to make sure it makes sense because this year the data from our health provider is different – way different. So different that the new data layout necessitated re-doing the formulas which means a greater potential for errors. But, in some ways, the data does appear to be easier to understand than it did in the previous two years. I just wish there weren't so many retroactive corrections. The retroactive corrections are disturbing, so much so that I trust our hiring/termination data over the data from our health care provider.
And because I'm going to be doing the ACA for the next month or so, blogging will most likely be limited.
You must be logged in to post a comment.