UPDATED: 07/06/2021 (Amount Paid column is new. I’ve also updated the sample data in my free spreadsheet to reflect the workflow in Part Three.)

How are you doing with healthcare reimbursements? I’m chugging along with the new P&A procedures and a load of paperwork. Learning how to clip along on this new line and how to share this with you has been a long haul. I hope this post is just the ticket for you.

Staying on Track with a Free Reimbursement Spreadsheet

Why would you want to use a spreadsheet? Maybe you prefer to enter items to reimburse when they occur. That’s the simplest way to go, especially, if you don’t have many medical expenses.

I do recommend my spreadsheet if you have a lot of expenses and you’ve found it hard to track details. If you’re dealing with medical coverage, mileage, and manual submissions, you’ll need a little help so you aren’t derailed. Additionally, you need a place to follow your pending expenses or make notes. A spreadsheet will keep you on track.

If you don’t do spreadsheets that much, give this one a try. It’s all set up for you and is especially useful if you’re Cru staff.

Other e4e blog readers are, of course, welcome to use this spreadsheet. (You might prefer an older version of the spreadsheet that’s not specific to P&A. Read this post: Free Reimbursement Spreadsheet and How to Use It.)

Both spreadsheets are formatted for US dollars and for American calendars. (Here’s how to change the formatting for currencies and dates.)

Your Folders and PDF Files

Before we head down the tracks, you’ll be following the rails already laid down in Organizing Reimbursements. (I tweaked part one of this three-post series to make sure of accuracy.) The part-one post goes into more detail for you about:

• Gathering, scanning, and filing any papers.
• Saving PDF files using a sortable naming system.

If you haven’t taken care of the above steps, backtrack to part one, Organizing Reimbursements, and then return to this post.

I’ll have more helpful tips for taking care of your healthcare expenses in part three of this series.

Ready? All aboard!

Get Your Free Spreadsheet

Download your free reimbursement spreadsheet. Save a copy to your own Google Drive. I replaced the older version because the new spreadsheet reflects our new workflow with the P&A Group.

Tabs

(I’ll discuss columns a little further down the track.)

You’ll notice I have three tabs across the bottom. These are separate pages for medical/dental, for ministry, and for addresses.

Get the tabs set up that work for your family. The template has the minimum. (I have two separate his and hers tabs for medical/dental. I also have two ministry tabs because I have both kinds of Cru credit cards.) You could create separate tabs for dental or for different family members, if you prefer. I’ve done this, especially when our children had extra dental work.

The ministry tab has similar layout to the medical/dental tab, but is simpler. I’m not explaining the ministry tab in this post. You should be able to apply directions from this post to tracking your ministry expenses. If needed, feel free to send any questions to me at e4e at cru dot org.

The third tab, Addresses, is convenient to store the places you visit often: your pharmacy, the printers, your physician, etc. Fill the data in this tab as you make trips to these places. You shouldn’t have to look addresses up each time you fill out a claim. Record them here. I’ll write more about this in part three.

I name my spreadsheets by year, making a copy in January for next year’s reimbursements. This is helpful so I don’t have to re-enter addresses in next year’s spreadsheet.

The rest of this post focuses on the medical/dental tab.

I mentioned in the previous post about entering data into your reimbursement spreadsheet. Since I’m only now giving you the new spreadsheet template, take time to enter expenses before submitting claims. P&A will “time out” on you if you do other things besides submissions. Do all screenshots, PDFs, the mileage form, etc. before you start submitting. This data entry is a lot to do. In the future, you’ll be filing PDFs and recording expenses in your spreadsheet closer to the day they occurred.

I’m going to assume you’re starting with expenses from April 1, 2021, and onward. Any expenses before that were either already processed or you’ll send them in as a manual submission in P&A. (I will discuss manual submissions.)

Recording Your Reimbursement on the Spreadsheet

Record an EOB in Your Spreadsheet

1. Go to the P&A Group from the StaffWeb.
2. Click on Show/Hide Account Details.
3. Click on the Carrier EOBs tab.
4. Open Blue Cross Blue Shield (BCBS) to confirm. (Match the P&A EOB and the Blue Cross EOB with the doctor visit, lab test, etc. listed in your spreadsheet.)
5. Record Columns E through I from BlueCross to your spreadsheet.
6. Make note of the date and copy the claim number on the P&A website.
   1. Find the date of service in your spreadsheet.
   2. Paste the claim number in column M (Claim #).
7. Enter “pending” in Column O (Done).
8. Click on “approve” in P&A (involves several clicks)

I use “pending” in Column O until I see the funds in the bank. At that time, type the date of the deposit in Column N (In the Bank) and change Column O (Done) to “yes.”

You’ll have to log in to BCBS to see a history of EOBs.

Record Manual Claims in Your Spreadsheet

You’ll fill in Columns K through R as you submit and follow-up claims. I explain what each column is for in this post.

Columns

Keep all your entries in chronological order (Column A).

All columns are in a particular order to follow a workflow from left to right. Also, this order avoids confusion with various dates, for instance. Feel free to add or delete any columns that are more pertinent to your situation.

At times, I hide columns for less distraction. For instance, I’ll hide J – G when working on a claim.

• A through J columns are obvious. Record data in these columns as they occur. At minimum, you’ll fill in Columns A through D and J right away.
• You’ll fill in Columns E through I and M when an EOB is ready to process.
• When you are submitting and tracking a claim, you’ll gradually fill in Columns K through R.

(If something happens that you don’t know where to record it, create a row for the day and make a note in Column Q or R. You’ll probably be able to clear that information later. For instance, I just put a note in for 6/22/2021 that the Doctor’s office says I owe $2.16 out of $211. I don’t know what office visit this refers to yet. I put “research” in the “Done” column.)

Column K – UPV #

Enter the UPV number for your submission to P&A. See next column.

Column L – Date Submitted

Enter the date of your submission.

Since P&A breaks up your submission into individual items, these two columns will help you when you want to reconcile deposit amounts.

Column M – Claim #

You’ll receive a claim number later when your manual claim is processed. I only record these when my submission is split into multiple payments.

If P&A shows it, I record BCBS claim numbers.

Column N – In the Bank

We get an email from the bank when the amounts are deposited. I enter that date here.

Column O – Done

You may choose whatever words you wish for this column. I use:

• “No” until you begin submitting.
• “Filing” after submitting to P&A.
• “Pending” if anything is not finished, such as the bill is not paid, the claim has not been deposited in the bank, and on down the line.
• “Yes” after complete.

Other possible flags for this column are research, denied, attempted, archived, . . . (“Archived” can indicate the PDF for that item is in the 2021 Healthcare Completed digital folder to help you find it. Learn about the folder system in Part One of this post series.)

My spreadsheet is huge. Whether or not yours is, I’ve included a filter on Column O. At different times you will want to use the filter to look at only “no” or “pending” items, for instance.

When it’s near the end of a year, using the filter will hide all completed expenses and it will be easier to see what hasn’t been resolved yet or what’s new and ready to reimburse. If you want all of your reimbursements submitted in their calendar year, this filter will help you get everything done before December 31st.

Column P – Amount Paid

This column is helpful when part of your claim is denied or the claim is approved and processed as several checks.

Column Q – General Notes

I’ve found this area useful to track other dates. Just now, I entered a date that I submitted an EOB for approval. In a few days, I’ll enter a deposit date in Column N and “yes” in Column O.

Column R – Date and Follow-up Action Taken

If I’m working on a denied claim or having an ongoing conversation with BlueCross, I use this column to write a date and a note to help me when I come back to the problem.

Get Your Free Spreadsheet

Again, here’s my spreadsheet in Google Drive. I have “dummy data” in it so you can see how to fill it out.

If you have trouble accessing the spreadsheet, reach me at e4e at cru dot org. I can send you an email invitation or an Excel sheet.

I hope that through learning through this blog post you see the light at the end of the tunnel.

I thought this was the end of the line, but I see a need to write again. Part three details will be helpful to understand these steps more. (This post is getting too long.)

Full steam ahead!

BACK TO TOP

NOTES:

• The P&A Healthcare Reimbursement Series:
  • Part One offers a way to organize and name your files and PDF documents. I give a brief outline of a workflow.
  • This post, Part Two, is where you download a free spreadsheet. I give a brief overview on tracking reimbursement details. I include steps to approve an EOB.
  • Part Three amplifies on the workflow and on the spreadsheet. Learn about tracking a manual submission. Download a free checklist to tone down all these details.
• Most of the embedded links in the spreadsheet only work for staff with access to the StaffWeb. Some of the links are for:
  • submitting online reimbursements
  • checking the rate chart
  • sending an email to OneCard or Staff Services
  • keeping an eye on 90-day limits with a date calculator
• Want to learn more about spreadsheets? I have lots of help on eQuipping for Ministry. Why not start with some easy training from my good friend, Bob MacLeod? Start with Excel / Google Sheets Tips & Tricks 2019.
• Photo by Roland Lösslein on Unsplash