In an earlier post, I discussed how to have students collect information to model two insurance plans. The second step in modeling the total cost in the Medical Insurance Project is to get the students to create a table of annual medical charges and the corresponding total annual cost using the information they have gathered on their two plans. Using Excel with formulas and fills, they complete a table for each plan.
For 17 and 18 year old students, this is a daunting task. Not only are they struggling with using Excel, they are also struggling with the terminology. In the technology assignment handout, I take great pains to spell out exactly how to use Excel to create the table.
Technology Assignment: Calculate the Total Annual Cost DOC | PDF
As you can see, each step is carefully outlined. You would think that it would be a very simple task for students to create the table for each plan.
Oh how wrong I was about that! Students focus on the steps of using Excel and completely shut down thinking about where the numbers come from. Here are a few of the glaring errors I saw the first time I used this assignment.
- Students do not understand the difference between a medical charge and the cost they pay. Often I found the copay in the medical charges column and vice versa. In an aha moment, I realized that most of them had never seen a bill from a dentist or hospital or received a statement from their insurance company…their parents always got these!
- On the first page of the handout, it describes the assumptions for the table…two office visits, one specialist visit, and one generic prescription. Most student glossed right over the bulleted list and the statment that in the handout I would assume the office visits incur a charge of $100 each, the specialist visits incurs a charge of $150, and the generic prescription incurs a charge of $30 per month.
- There is a lot of confusion over the fact that one must pay the deductible before the charges are split with the insurance company. And once the coinsurance kicks in, they pay a percentage of all charges beyond the deductible and any charges for the visits. While this is not exactly true with all plans (in some you pay the deductible before the copays kick in on the visits), almost every plan my students chose worked that way.
- Some companies report their out of pocket maximum including the deductible and others report it without the deductible. For instance, Blue Cross / Blue Shield reported a deductible of $10000 on one plan with a $4000 out of pocket maximum. This confused students greatly until they noticed that the max did not include the deduictible. In effect, the most that a person might pay in a year under the plan was $10,000 + $4000.
- Students assumed that their first plan was like the Basic Plus Plan (coinsurrance but no copays for visits) and their second plan was like the Premier Plan (copays and coinsurance for visits). In actuality, almost every student chose plans with copays for visits (at least for the first three visits). I had to focus them on the Premier Plan so that they could fill out the tables for both plans.
After giving this assignment for the first time, I had to clean up the carnage so that they could continue working on the project successfully. I created another handout for them on the specific entries in the Premier Plan.
Handout: Deconstructing the Premier Plan DOC | PDF
In this handout I tried to link the formulas in the cells with the assumptions in the plan. Using this, they could modify the formulas to fit their own plans. It is not uncommon that an assignment does not work as anticipated. Sometimes it is just too much too handle in one assignment or your assumptions about your students is way off base. For this assignment, my students had no idea how insurance worked so assuming they could track the numbers through the handout easily was doomed. However, next semester this might work just fine for a group of older, more experienced, students.
Even after I had posted the Deconstructing the Premier Plan Handout, there were a few stragglers still fighting with their Excel spreadsheets. I decided to make a short video on putting together the table of values for total annual cost as a function fo the total medical charges.
Video Constructing the Table for an Insurance Plan
Next semester I plan to put together a short worksheet which the students can complete in class and a corresponding video for online students. This worksheet will give them three different charges (one from each of the three regions on the model) and ask them to caclulate the corresponding charge by hand. This way they will have some familiarity with the concepts and numbers before they start putting the formulas into Excel. I’ll post that worksheet once it is complete.