Another one Bites the Dust

In which I win another battle, in a different war.

You may recall that I'm engaged in a long time war with the dreaded Medical Professionals' Accounting System.  I still love our docs, but their billing system is so bad that even the folks in their accounting department hate it.  

As part of this war, I've been trying to maintain a spreadsheet that will let me see the following things clearly:

1. How much the Medical Professionals charge for each service (and how much they adjust that because of the contract with the insurance company.

2. When we have paid our deductible(s) so that the insurance company will pay their share of the bill.

3. How much the Insurance Company pays the Medical Professional for each service.

4.  How much I've actually paid to the Medical Professionals and

5.  How much of, and where the Medical Professionals have allocated the payments I've made.

and thus...

6.  How much we actually owe the Medical Professionals.

I'm keeping track of this stuff myself because (as we've discussed), the bills the Medical Professionals send do not clearly identify what I've paid, and they have a history of failing to allocate all of the money that I've paid.  The bills are particularly confusing since they don't deduct from the total they claim I owe the amount that they almost acknowledge that I've paid but they haven't allocated.

This would be simpler if the Insurance Company weren't in play, of course.  On the other hand, we likely couldn't afford the care we get without the Insurance Company.  Given that we have insurance in play, the spreadsheet gets more than a little complicated:  Each person has an individual deductible to meet before the insuranc company has to start paying; and there's also a family deductible -- once that's met, even if the individual hasn't met hers (or his), the insurance company has to pay anyway.  Of course, some things don't have to meet the deductible.  And ... some things have a co-pay, but others require "co-insurance".

?  That means that if there's a specified co-pay ($20.00 or $40.00 depending), you pay that amount, if not, you pay a percentage of the covered amount and the insurance company pays the rest.

So, in order to figure out how much I owe, I have to figure out how the insurance will be applied.  And, since I've long since learned that you can't necessarily trust the insurance company and/or the medical professionals to do the math right.  That meant that I had to work out some functions that would be able to yield the right numbers, based on whether the individual and/or family deductibles had already been met.  That's easy with one person (well, once you've figured out how to do if statements in your spread sheet, but I did that ages ago), but with three people involved, the if statements got more than a little complicated.

I do not want to discuss how long it took to master that if statement. I fussed with it, then left it, then came back for more another day, rinse, repeat.  But today -- I mastered that puppy.

The spreadsheet war is over (for now), and I appear to have won! I am now fully confident in my ability to create functions with nested if statements.  My favorite was one that went pretty much like this: if family deductible not yet met, then determine which individual's deductible we're working with, then if individual deductible not yet met, then deductible for this charge = the lesser of the covered amount or the family deductible minus the covered amount or the individual deductible minus the covered amount.

Time to go back to the paper wars so I can get that desk out of the guest room.

Comments

[FJ]

I am glad you have mastered that, as I couldn't really understand it, and I'm one of the causes of that. (indirectly) Sometimes I think the insurance companies and the medical profession do that just to make it SO confusing, people will pay more or whatever it is to get them bill out of the way. Good luck!

[Cheryl Mankin]

It's a full time job just trying to keep these people honest! Good for you! Maybe you should package your algorithms and sell it!