(This page has been updated for processing year 2017. Key changes are namespaces and the no empty tags stipulation.)
Okay, this is the part that probably actually got you stumped - the actual conversion of Excel into XML. You can probably figure out everything else but if you have tried converting, you probably found out that you can't really convert Excel into XML - at least not directly. When doing the conversion of Excel data, you typically use a schema that contains the fields that you "map" onto your Excel data. You can try to do the same using the government's schema (if you know which one to use) but there is a little problem with parent-child relationships or nesting folders or whatever you want to call it. The bottom line is the government's schema is a little more complicated than what Excel can handle.
But there is a work around, as long as you don't mind the additional step. My method involves stripping down the schema file to the basic required information and then build up the XML using a macro. The real schema file is "IRS-Form 1094-1095CTransmitterUpstreamMessage" but my version is a stripped down version that will enable you to do conversion. The macro will add in the required lines to satisfy the government's schema.
First of all though, you must make sure your data format is in the right format as the government specifies it: no apostrophes, no strange characters, no double spaces. Use the crosswalk to tell you whether the format is a number or string and how many characters. For instance, addresses are limited to 35 characters (I'm pulling this off the top of my head so you need to check the crosswalk). Down below, I've embedded a template that helps with the formatting and has some business rules check. You should be able to click on the Download button but I'm not 100% certain. It worked on my machine but you never know with these things. You should get a download that contains 2 tabs: a "Data" tab and a "For electronic sub" tab. This file is a modified version that I used and it contains some fake data to get you started. You will have to adjust the number of rows of formulas in the "For electronic sub" tab. Put your data in the "Data" tab and the numbers should flow through to the "For electronic sub" tab with the correct formats. Please check to make sure you do not have strange characters that I might not have thought of or encountered. This file was set up based upon my situation. Off to the far right, you will see some business rules check: basically checking to see that if you have anything in the annual column, you shouldn't have anything in the monthly column and vice versa. You will have to deploy the filters to check on the business rules. I'm not covering all of the business rules; only those that I needed in my situation.
One change for 2017: last year I thought that for the code 2 series (2A, 2B, 2C, etc), you either had to have the annual filed filled or every single month filled. This year I learned that is not true. Normally, you want either one to avoid triggering a red flag, BUT in instances where you have a month(s) that your company did not offer minimum required coverage at minimum required value to at least 95% of your full time employees, then that month(s) should not have a safe harbor code (2H, 2I, maybe 2G, I don't remember exactly which ones). So the business rules in the file will have to be considered in light of this new information.
This file is to just get you started. It will not cover all of your situations and I don't claim to cover everything.
What this file does is to lay down the basic data you will need to convert. By the way, I need to repeat, this process does not cover self-insurance. You might have to adjust the file to cover for that situation. Once you have your data in the "Data" tab and you have checked the formats and business rules, you are ready for the next step.