 Personal finance practice problem using Excel health insurance payment comparison prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we'll basically build this from a blank sheet. If you do have access, there's three tabs down below. There's the example tab, practice tab, the blank tab, the example tab in essence being an answer key. Let's take a look at it now. Information's on the left-hand side. I'm going to be comparing two policies and the costs related to them for doctor's visits. For example, the second tab will be a pre-formatted worksheet so you can work through the practice problem with less Excel formatting. The third tab will in essence be a blank tab. We will work the problem basically from scratch. If you don't have this worksheet, then you could just put this data on the left-hand side. I would start out by selecting the entire worksheet triangle up top, right-clicking on the worksheet, and then formatting the sales to put your baseline formatting down and I would go to currency and then put the brackets and no dollar sign and no decimal. That's the starting point. I'm not going to say okay, but just X out of it here because I've already done that. Enter the data on the left-hand side, which is the best practice that you want to do anyways so that you can practice pulling your information from the data on the left, make the sales, whatever reference they need to be, changing it, for example, to percent in this case and then make the skinny C column and then we're ready to go. So information says we got the health insurance. We've got one policy pays percent of physical therapy costs after deductible of 70%. The deductible is $400. So we got a deductible of $400. You got to clear that and then they're going to be paying the 70% after that point. We have an HMO policy where there's a cost per visit for physical therapy, which is just a flat $30. That's how much we can expect to pay. And then the number of visits then we're going to assume to be 80 and the cost of the actual physical therapist is charging is $80. So in other words, with the first policy up here, we would have to split on the 70, 30 on the payments of that $80. And then down here, we're going to just have the set fee of $30 and then the difference being paid up to the 80 by the insurance company. So let's just do some comparisons here with this data. We're going to say let's make the health. This is going to be the health insurance insurance policy personal costs. So what's going to be the cost to us or like the out of pocket cost? In other words, for this first item up here, where we have the deductible, I'm going to make this sell a little bit longer, put in my cursor between the D and the E left click and drag into the right. And that's good. And let's make this one black and white. I'm going to go from D1 to F1. And then let's make that black and white as has been our custom home tab font group. And we're going to go to the bucket and make it black and then the letters and make it white black and white. And so then I'm going to say let's calculate the cost. So I'm going to say the cost is going to be the number of visits times the cost per visit. So the number of visits, I'm going to say this equals and I'll put this in our data down below so that we can pull it from the data down below. And we can run different scenarios once we get that once we get our information set up in our table. So that's going to be 12 visits, we're going to say, and then we're going to say that it costs $80 per visit $80 per visit. So we'll scroll on down here to the $80 per visit. So there we have that. So that's going to give us our total cost. Let's call it total cost. The total cost is going to be equal to I'm going to put this in the outer column equal to I'm going to say up to the 12 times the 80 12 times 80 and enter. Let's put an underline here and say let's do an underline and let's do some fancy formatting. I'm going to select these three cells for the fancy formatting. We're going to go to the home tab alignment indent and then I'll indent this double time home tab alignment indent again. And then we're going to say, OK, so then we have a deductible. So we got to clear the deductible before the insurance starts kicking in on this first plan. At least we're going to say the deductible is is $400. So we're going to say $400 on the deduct. And so then we got the cost less the deductible cost less deductible or the amount over the deductible here that we have. This is going to be equal to the 960 minus the $400. So that means we got the 560 here. Let's put an underline under this one and let's go to the home tab font group and underline to do it. And then we've got the percent percent paid by individual individual. So let's do it this way. So we're going to say 70% was paid by the insurance company of the amount over the deductible one minus 70% or 30%. Let's do a quick little calculation. One or 100% minus the percent paid by the insurance company percent paid by company is going to be the 70%, which I'm going to pull from the data equals the 70% going to percentify that cell. So I'm going to go back on it here. I'm going to go to the home tab. We're going to go to the number group and make it a percent percentifying it. And then I'm going to go to the font group and underline it as well. And this will give us the percent paid by the individual. Let's get just keep that. And I'm just going to get rid of the colon on the end tab tab. And so I'll do the subtraction problem. That one of course is 100% or one. If we made it a percent, it would be 100. This equals one or 100% minus the 70%. And if I percentize that cell by going to the home tab number group, percentification, find it, percentify it. And then we're going to say that let's put an underline on that too. While we're here, home tab font group and underline. Then we've got the and let's do some indentations here. Hold on a second. Let's do some indentations. Let's take these three home tab alignment indent and then indent this one again. Home tab alignment indent double time. And so then we got the personal cost after deductible. So that means we have this amount that cleared the deductible and the insurance company is going to pay 70%. That means we're going to pay 30%. So the amount that we're going to pay is equal to the 560 times the 30%. So that's the 168. Then we also have the deductible that we have to deal with that we have to pay. So I'm going to we also pay the deductible. So that's going to be equal to the 400, the 400, which I could pick up here or there. So the total out of pocket. So the personal costs costs including deductible is equals the s to the u to the m of those two numbers. And that gives us the 568. Now let's make it a little bit more fancy. I'm going to put an underline here and let's we could imagine imagine for example, if you will, that this number was less than the deductible. So then I could say, well, what would happen? Let's say if if we didn't visit like 12 times, but let's make this. I'm going to make this yellow and I'm going to play with it. Let's say, okay, we had it at 12. There's a percent on that. We had it at 12. Let's say that we only went like three times. Then the 80 times three is less than the $400 deductible and my whole math has kind of messed up because it makes a negative number. I don't want it to go negative here. So what I'm going to do here is I'm going to use an if then function. I'm just going to practice our formatting to say if it goes below zero, don't go below zero. So I'll do it this way equals if brackets this number minus this number is less than zero. Then that's a comma. So I'm going to say comma. That's what I say when I say then comma. Then what do you want to do if well, if it's less than zero, I want you to put a zero because I don't want you going negative. Don't go negative people. It's bad. It's bad idea. Comma and then we got the false value. If so, that's going to be this minus this meaning if it's positive, I want you to do the subtraction problem. So there we have it. Let's close up the brackets and enter. So there we have that. So that that looks good. Cost less deductible. You know, non negative. I could adjust the wording, but I'll keep it at that. And in this one, notice down here, I have the deductible. So but really, it should be amount caught paid up to deductible. So something like amount paid up to deductible or something like that, because if it's less than the deductible, I'm not going to pay the whole deductible. I'm going to pay the lesser of these two. So let's do the equals the men here equals the men of those two. And so the men is just like the sum function, but with a with a men. And so and that'll pick up the smaller number. So now I can now I can play with it. If I'm below the deductible or going above the deductible and I can start to say, okay, what if this was five, right? What if this was six? So now I'm over and so on. And if I go back up to 12 and we should have something that helps us to work different scenarios now, which is nice. I'm going to delete this. Let's put some fancy formatting around this making it blue and bordered. I'm going to select this whole thing and border blue home tab font group border and then the bucket. I'm going to use that blue. But if you don't have that, you could go to the more colors more colors. We're in the standard item here. We're going to go to that blue right there. That's the one we want. That's the one I'm using. At least you could use whatever you want. It's not like a standard rule here that's etched in a stone somewhere and even if it was etched in a stone, that still doesn't mean you have to do it just because someone etched it in a stone, right? I mean, except maybe some things etched in stone are important. But okay, home tab. Let's go make a skinny column over here. I'm going to select this one home tab and then we're going to say this is paint brushy and we'll put that over here on column G. We got a skinny G. So this is the HMO now HMO personal cost. So now we'll do this calculation for this policy down here where we're just going to pay a flat $30 and we don't even have the deductible thing to deal with. So I'm just going to say, alright, let's make this one a little bit wider. That should be easy to do and let's do our header thing selecting these two columns or rows or cells. I should say home tab font group. Let's make this black and white for the header as has been our custom white on the lettering. And then this is going to be the cost per visit. I'll just call it that. And this one is just a flat $30. That's what they tell us. It's going to cost you $30. But how much do they charge? I don't know. They just put that I got to pay $30, but they're actually charging $80, but we're paying the 30. And then we're just going to take the number of the visits, the number of the visits. And this is because they have a deal and the HMO with of course the HMO and the network and whatnot. And that's going to be 12. So we're going to go up top and say font group and underline. And so this is going to be the personal costs this way which will simply be equal to the 30 times the 12 like so and let's make that blue and bordered. I'm going to put some fancieness around it and we're going to go to the home tab font group blue and then the borders blue borders. That's what we like to see. And so the difference between the two the difference is we got the 568 over here minus the 360. So we're saving the 203 with the HMO. Now note that the HMO the idea would be that they might be able to have lower costs because you're in the network but you might be more restricted because you have the network and you've got to be within that network and and so on. So we've talked about the pros and cons of a more flexible plan possibly that you'd have to measure that you might be able to travel more or something like that and not have to worry about so much maybe a network kind of issue versus an HMO which might be a little bit more restrictive and so on. But that's the and so and then of course you'd have to compare the premiums between the two how much you know the policies are costing to kind of make a decision if you're making a decision between the two but just to get an idea of how you might set up a worksheet. You can look at your past history and try to think about okay how much am I paying on this kind of stuff to get some bearing on a reasonable decision. Once set up then we can we can increase or decrease these numbers right I could say well let's do this 12 thing again. What if that 12 changed to like what if it was like for so then so then over here now we're under the deductible but we're still paying more because we're paying the whole $80 here we're over here we're only paying the $30 per visit we don't have to deal with the deductible. If we go then to six you know now we're over the deductible if we went back up to let's say the 12 the 12 so that's where we were before and if I increased it to some point if I increased it to like 50 or something like that now this one is higher than this one and you can find that kind of break even point if you wanted to play with this kind of thing you could say what number would this have to be to get these two numbers in essence to be the same right how many number of visits would I have to have to get that number to be the same to do a calculation like that you could go using the data tool over here and we could go say I'm going to click off of the cell I'm not going to go over here to the what if analysis and goal seek so I could say seek a goal what do I want to seek what's the goal that you are looking for the holy grail okay anyways we're going to set the cell we want to set this cell to know we want to set this cell to be zero that's what we're looking for and then we want to do that by changing this cell so we're saying Excel change this number to whatever it needs to be to make the difference zero using trial and error goal seek okay and so so there we have it is forty seven oh man what happened I just got the answer and then I deleted it let's do it again do it again we're going to goal seek so we're going to say set this cell to be zero by changing this cell poor father or don't mess it up okay so there did like a forty seven right so you can do something like that and you can also change of course this one if I bring this one back to twelve let's make this on yellow on yellow fire and now I changed this one and say okay what if my what if the doctor's costs go up to like you know forty like what if they were only what if they were only forty dollars on a doctor's cost right then you got a smaller difference with their fifty you know what if they the doctor's cost go up to a hundred and twenty or something like that and you can again change your your data and run run basically different scenarios based on that if you have your worksheet set up which can be useful so let's bring it back to where it was where was it I wrote down twelve I was what was it before what was it people okay eighty that's right thank you okay so there we have it let's do a spell check real quick does miss spell anything of course you did of course you did I'm just going to say alright that's good we'll keep it at that