A Simple System for Managing Medical Expenses

For years, I just paid the medical provider bills and hoped that things were accurante (and for the most part, honest). Then, back in 1998, I had surgery and was faced with a pile of bills and insurance statements that I couldn't make heads or tails of. To try and make some sense of the madness, I created a very simple system to manage this information, to track medical visits and procedures, to have some idea of what bills and insurance statements to expect, and to match up medical visits and procedures with bills and insurance statments to keep them honest.

The system consists of a Microsoft Excel spreadsheet (the download is in Excel 2000 format) and a file folder to hold paper records. Data about visits/procedures, insurance statements, bills, and payments are entered into the spreadsheet with one or more lines per visit/procedure as required. The previous statement recognizes that some events generate more than one insurance statement and/or bill. For example, surgery may generate bills from the hospital, surgeon, anesthesiologist, radiology, and lab. The system handles each one of these as a separate transaction, with transactions generated either by medical visits/procedures, insurance statements (sometimes you don't expect something associated with a visit), or a medical provider bill.

The Excel Spreadsheet

There is one transaction per spreadsheet row. The blue section contains info about individual visits/procedures. As stated before, creation of a new transaction record is triggered by a medical visit or procedure, or receipt of an insurance statement or provider bill. The meanings of columns in the blue section are as follows:

• The "Ref #" column contains the transaction number that will be used to match records in the spreadsheet to paper records in the file folder. The blank spreadsheet contains transaction numbers for 2000 transactions. More can be added by making copies of the last row, then using the Excel Edit|Fill menu comand to automatically number the ref # column downward. Between 1998 and 2004, we used only 130 rows, but your mileage may vary.
• The "Date" column contains the date of the visit or procedure. The for those unexpected items that are separate from specific visits, like some labwork, the date may come from an insurance statement or a bill, depending on which arrives first.
• The "Who?" column refers to the name of the person who received the service, so that same spreadsheet can track medical expenses for several people.
• The "Provider" column contains the name of the provider. This doesn't need to be exact, but only enough information to be able to match up with insurance statements and bills.
• The "Description of Services" column is a short description of services provided, again to help match up with insurance statements and bills.
• The "We Paid on Date of Visit" column contains the the total amount of money paid directly to the provider "out of pocket" when services were provided. This is used to calculate how much you owe the provider, so don't include expenses not directly billed and paid to the provider on the day of service. This means that if your doctor gave you a presciption, don't include money paid to your pharmacy here.

• The "Paid by Insurance" column contains the total amount of money that the insurance statement says they paid to the provider. This column isn't used for calculations and is for reference only. Over time, it will give an idea of how much you are paying versus the insurance company. This will give an idea of how much value your insurance premiums are buying.
• The "Insurance Sez We Owe" column (forgive the slang) contains the total abount of money that the insurance statement says you owe the provider. This is used to compute the total amount the provider should bill. Note that because insurance company agreements with providers sometimes reduce the amount that the providr can charge, this amount may be less than the provider bills. It's between you, your insurer, and your provider to work this out, but you can generally trust that the provider will honor any reductions by the insurer. If they don't report them to the insurer.
• The "Paper Balance" column is a calculated column that subtracts the amount from the "We Paid on Date of Visit" column from the amount in the "Insurance Sez We Owe" column. Do not enter any data in this column! This is the amount that you should really owe the provider. Note that when you enter a transaction after a visit or procedure where you paid money at the time of service, but before the insurance statement is entered, this column will hold a negative number, indicating that you've already paid some money.

• The "Amount Provider Billed" column contains the total amount the provider says you owe. Since providers sometimes bill several visits together, you can either split these between transactions or leave them lumped together, as long as it makes sense to you (this is what the "Remarks" column is for).
• The "Date We Paid" column contains the date you paid the bill.
• The "Amount We Paid" column contains the amount of money you paid the provider.
• The "Check Number" column is an optional column to allow you to match a transaction to a cancelled check in case there is a payment dispute.
• The "Remarks" column is for any information you wish to record about the transaction. It will wrap when you type more text than the width of the column, and can contain up to 255 characters. The row height should automatically expand and contract to fit the amount of text entered.

The File Folder

How the System Works

Unless you're having surgery or procedures, the normal sequence (such as a doctor visit) will be visit, then insurance statement, followed by a bill, followed by a payment. Here's how such a visit would be entered:

• You visit the doctor, make a copayment, and receive a receipt an office statement (The paper showing what was done and the diagnosis. The outprocessing desk at you doctor's office uses it to record what was done and to determine your copayment and next appointment).
• When you get home, you open the spreadsheet and enter the blue section of a new row, including your copayment. Here's where the file folder comes in: record the number in the "Ref #" column on the office statement and place it in the file, behind any existing documents, so that papers are stored in "Ref #" order. I place the "Ref #" in the upper right hand corner and store the documents in the folder such that I can thumb through the documents and see the handwritten "Ref #" without pulling out documents.
• A week or two goes by and you get an insurance statement. Open the spreadsheet and fill out the gray section. If a benefit check was received, state that in the "Remarks" and enter the check in the "Amount Provider Billed" column as a negative number to indicate a receipt vice a payment. Write the "Ref #" on the insurance statement. Open the file folder, pull out any documents for that "Ref #", staple them together (I usually put the insurance statement at the front to indicate that some action has been taken when I see the documents in the file. In my case, the insurance statement is often the only 8.5" x 11" document I get, so having it in front makes all the handwritten "Ref #" visible from the top of the stack of documents in the folder.). Put the documents back in the file folder, in the place they came from to preserve the "Ref #" of documents in the folder.
• A few weeks go by and the bill comes in. Here where the payoff for the organization comes in:
  • Open the spreadsheet and fill out the "Amount Provider Billed" column. If you've had several visits recently, you'll have to determine how or how not to record the info in other existing columns. As long as it makes sense to you it's OK. If you put them all under one transaction, you can use the "Remarks" column in the others to reflect that the bill was paid under "Ref #" such and such.
  • Compare the "Paper Balance" column against the amount the provider billed (you'll have to look at this column in more than one row if the provider billed for multiple visits on one bill) and determine how mch you owe or are owed. Then determine what to pay the provider.
  • Make your payment and record the info in the "Date We Paid", "Amount We Paid", and "Check Number" columns, as well as "Remarks" column if desired.
  • Remove existing documents from the file folder. Take the part of the bill you keep, staple it to the back of the documents, and return them to the folder, maintaing the "Ref #" order of documents.

That's all there is to it! If a dispute arises later with a provider or insurance company, you can locate the transaction in the spreadsheet and use the "Ref #" to find the documents to deal with the problem. Simple and easy! Using the logic above, you should be able to determine what happened in the transactions in the sample spreadsheet shown above.

Computer System Requirements

This web page is provided in the download as an Adobe Acrobat file, If you do'nt have adobe reader, you can download it from Adobe using this link.

A Few Ending Notes

Once in a while, something wierd happens and I edit the spreadsheet to state something that wouldn't be obvious. For example, one provider didn't bill me or the insurance company for 4 months. The resulting insurance statement indicated that I didn't owe the provider anything because they breached their agreement to submit to the insurance company within 30 days. I used the Excel "cell merge" function to merge all cells in the light green section and typed in what was on the insurance statement. When the provider billed me, I opened the spreadsheet, saw that I owed them nothing, and contacted them and worked it out without cost.

Using this system, I've also found providers that billed me without deducting my copayment. I was too chicken to haggle about the first one, but never again!

I hope you find the system as useful as I have. Since I created the system in 1998, I've yet to have medical transactions that I couldn't figure out.

Download