A Simple System for Managing Medical Expenses
If you're like me, you've experienced the bewilderment and frustration that comes from trying to make sense of bills from medical providers (doctors, labs, hospitals, etc.) and statements from health insurers. They never seem to add up. Information is broken down in strange ways, and medical provider bills often lump together items that show up on several separate insurance statements.
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
This is what the spreadsheet looks like:
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 gray section deals with information from insurance statements. The system was conceived with the idea that each row is associated with one insurance statement, but sometimes this may not be the case. As a general rule, there should be no reason to ever need to combine insurance statements into one row, but exceptions do and will come up. I've never dealt with what to do in this case as it hasn't happend to me so far, but one option would be to add up info from the various statements and enter the result. The meanings of columns in the gray section follow:
- 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 light green section deals with provider bills and bill payments to providers. Again, the system assumes that you will receive only one bill from a provider per visit or procedure. If you receive more than one, you can either handle it as a separate transactions and split information between transactions (I've done a 110 and 110a transaction a few times by inserting a row under a transaction, then copying the formula in the gray "Paper Balance" to the new row), or you can use the Excel "Split" finction to split the light green section into more than one row and enter each bill separately. The meanings of columns on the light green section follow:
- 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
This is just an ordinary file folder is a file cabinet that's easily accessible. The reason this is a file folder is that you'll be accumulating a lot of letter size (8.5" x 11") papers that will need to be stored in an organized manner. Where you store it is up to you, but you'll need to access it every time you have a medical visit/procedure or receive an insurance statement or bill.
How the System Works
When you have a doctor visit or procedure, fill out the blue section with Date, Patient, Provider Name, Description of Services, and funds paid at the time of service. If the first thing you see is an insurance statement, such as for the surgeon's bill after surgery, you can fill out both the blue and gray sections. If the first thing to come in is a bill, as with the insurnace statement, you can fill out both the blue section and the "Amount Provider Billed" column of the light green section.
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 system assumes the Windows platform and Microsoft Excel. The Download section provides a ZIP file with the spreadsheet in both Excel 2000 format and in a format for Excel 5.0/Excel95 and later. If you do not use Windows or Excel, you should be able to import the Excel5.0/Excel/95 format file into whatever spreadsheet you have.
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
After a while you have a lot of records, so I tend to mark the text in previous years transactions in italics to set them apart from the current year.
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.
This ZIP file contains the spreadsheet (in Excel 2000 and pre-Excel 2000 formats) and this Web page in Adobe Acrobat format. Download and open the ZIP file to your computer. Make a Start Menu shortcut to the Excel Spreadsheet (and the instructions if you wish).
Copyright 2001-2009, Tim Sharpe. You are free to use this information for personal, non-commerical use without restriction. All rights reserved for commerical, organizational, or government use. Questions or comments to firstname.lastname@example.org. Flames to /dev/nul.