 Personal finance, practice problem, using Excel. Get ready 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 scratch. We got three tabs down below, an example tab, a practice tab and a blank tab. The example tab and essence being an answer key. Let's take a look at it now. We've got the information on the left-hand side, calculations in the blue on the right-hand side and down below. We're looking at automobile insurance or car insurance, imagining an accident happening and what the possible insurance payout might be. The second tab, the blue tab, represents a tab with the pre-formatted worksheets so you can work through the practice problem with less Excel formatting. The third tab, the one we're gonna work on, is basically working the problem from scratch. If you don't have this and you wanna just build it from a blank sheet in Excel or some electronic spreadsheet, I would start off by selecting the whole sheet, right-clicking on it, format the cells first, go to currency, brackets, get rid of the dollar sign, get rid of the decimals. That's where I start with at least, I'm not gonna do it to this sheet, but that's what I would start off with if you don't have the sheet and then add your data on the left-hand side, like so, I do recommend doing this, especially with the numbers, because you want to be able to be drawing from something, a data set, that's just good practice and then we got a skinny C column and that's it, then we're ready to go. So we're gonna say we have insurance, auto insurance or car insurance, we've got the 80, 160, 90, these three numbers are often part of the complication, so we'll have to get into that. We got three people awarded injuries in an accident in which you were at fault, so we're gonna say we got in an accident, we were at fault in the accident. Now remember, the car insurance represents multiple kinds of insurance because when we think about insurance, we usually think about things like liability insurance or property insurance and stuff like that and we're kind of combining this under auto insurance, so these are all different kinds of insurances that are under like the act of driving or that could fall under, you know, driving or related to the car in some way. So we got multiple different kinds of insurance, we're kind of concerned here with liability insurance generally and that's the kind of insurance we're often required to have to some degree, depending on the states that we are in. So if we're at fault for the accident, if we were not at fault for the accident, hopefully the other person has insurance and hopefully that would be, you know, they would be liable. If we are at fault, then we're gonna end up being liable and hopefully our insurance will help us out and kick in at that point. So we've got the 80, 160, and 90, we got down here a little blurb, tells us what that means. The first number represents a bodily injury per person. So we're talking bodily injury, not property damage, which is usually the more costly item or could be possibly potentially could have more cost. The second number represents bodily injury per occurrence, meaning we've got so much per person for bodily injury, but it's gonna be capped if you've got multiple people at some point. And the last number represents property damage per occurrence. So we're talking still kind of liability insurance on both sides, but ones for bodily injury and the others for the property. So these two represent the bodily injury one, the last one, the property one. So that's the idea. So we're gonna go down here, let's kind of recap these three numbers too first. So let's call the first one. This is gonna be the max per person, max per person injured. So this is the bodily injury, max per person, meaning that 80 means 80,000. So there's 80,000 coverage for that one. So you could think about these in thousands, right? So the next one is gonna be the max per accident or event for injury, injury. So we're talking injury, not property damage here. So we're gonna say this is gonna be the 160,000. And then we've got the property damage, property damage liability, which we're not really focused in right now because we're talking about bodily injury, but that would be the 90,000 for like the damage to the car and whatnot. So I'm gonna select this one and go to the home tab and we're gonna go to the font group. Let's put some borders around that and let's make it blue. Border blue. Okay, so now that we have that, we can do our calculation so I can say, okay, well, if there were three people injured and they were all awarded 140,000, let's do our calculation in kind of a systematic way. We could kind of shortcut it for certain problems, but let's try to set it up and think it out logically. We're gonna say, all right, we've got the injuries per person, per person. And let's put the actual here, actual. And then the max and then the lesser of, see, lesser of the two. So that's gonna be our headers. I'm gonna make these three centered. Gonna go to the home tab alignment and centered. And then I'm gonna make all of these black and white as has been our custom. We're gonna go to the home tab font group and hit the bucket up top and make it black and white, black and white. So there we have it. So I'm just gonna say person one, and then person, person two and person three were all injured and they were all awarded, we're saying 140, which I'm gonna draw from my data set. I'm gonna say the actual awarding that they got was 140. So let's say that's gonna be the same all the way. I could do an F4 thing. Let's do an F4 thing. Let's do this and make it absolute and say F4 on the keyboard dollar sign before the B and the seven. So I could just copy that down just to practice our Excel skills. We're gonna grab that fill handle and grab it and drag it on down. So then the max though is 80, which I can't really pull from this cell. So that's why I think it's good to break that out down below and say, well, the max per person is 80. And that's the same all the way across. I'm gonna say equals to 80. And again, I'm gonna copy that or absolutize it, freeze in the cells F4 dollar sign before the B and the 13. You only need a mixed reference, but an absolute is kind of the easiest thing to do. So I'm gonna say, okay, and then I'll just copy that down. You could just double click on the fill handle, which I just call a fill button at that case. No one calls it a fill button, but it kind of acts like a button in that. I mean, I feel like it's a button when you're doing it that way. So then I'm gonna take the lesser of these two for each one of them, which would be, of course, 80, 80,000. So, but I wanna use the men function in Excel to do that. So equals to M-I-N, the men function, the lesser of the two brackets just like the sum, but instead of S-U-M, there's an M-I-N for the men. And then we'll select those two and that's it. It's just gonna take the 80,000 you would expect, right? You would expect it to take the 80,000. So then there it is. I'm just gonna copy that down and that'll copy the relative cells over. So I'm gonna copy that down and it's not copying that 80, it's doing the men there and it's doing the men there. So we took the men of those things. We can have our totals. Let's put our totals down below and use our trusty sum function equals the S-U-M, sum it up. And then we're gonna put a bracket up arrow once, holding down shift, selecting those. We could do some copying and pasting this time. Let's copy that one this time. I'm gonna go control C, do an all keyboard, right arrow, holding shift down, go to the right and then control V, pasting it fancily. And that of course pastes the sum function. If I double click on them, let's put an underline right here. We'll say home tab font underline. So we've got the actual was 420, but we're capping each one of them at 80. So that would be a cap of the 240 for the max. If they all hit the max and they did all hit the max, therefore we have the 240. So now we're gonna say that there's also a max per accident though. So there's a max per accident for injury. And that happens to be the second number, the 160. So 160, which we broke out down here, 160. Let's put an underline there, underline font group underline. And this is gonna be the insurance payout then. Insurance payment is gonna be the men of these two then. The men of those two then equals the men brackets of these two and then enter. So that comes out to the 160. So let's put some brackets around this. Let's select this whole thing and we're gonna go to the font group and bracket and border blue. Let's do a border blue thing. So you could probably see that. Like you could probably say, I didn't need to do this whole thing to figure that because I can see that 140, 140, 140 is gonna be greater than the 160 cap here. And you can kind of visualize that so you can get a good visual feel for it. But it can get a little bit complex when you start to think about it. So it's nice to have a table and it's useful to kind of be able to put these into a logical table because now if you were to adjust things and it would be even better if I put these three people out in a table on the left-hand side and drew them from the table. So if I was to say, for example, that I'm gonna draw my data from person, person one, person, person two and person three and say that we got then the three amounts which were 140, 140,000 and equals the one above it. And if I draw this data from that data source, this comes from here, this comes from here, this comes from here, then it might be a little bit easier for us, again, to change the data. What if this was different, right? What if person two was something like 60,000 that they were awarded instead of the 140? Let's say it was 60,000 and let's say the last one was 30,000. Well, then this top one would have been at the 140 that they were awarded, maxed out at the 80,000, it was capped. So it took the lesser of, took the 80. This next one, the actual amount was less than the cap of the 80,000. Therefore, the min function took the lesser of, that's not gonna be at the cap, it's gonna be the 60. And then this last one was at the 20,000 or 30,000 which is less than the cap of 80,000. So they got the 30,000. We add that up to 170,000 and that then is still limited by the amount per person of the 160 and therefore we took the difference of the 160. If we did that down a little bit more, if I took this and said this was like 5,000 on this last one, right now we've got the 140 capped at 80 at the 80, we've got the 60 versus the 80. So we took the lesser of the 60, we've got the 5,000 to 80. So we took the lesser of the 5,000, that adds up to the 145, which is less now than the total cap per person of the 160, so you get to the 145 in that instance. So it's nice to be able to work through a table even when you have a couple different variables because when you start to put them together it can get fairly complex fairly quickly and if you're running different scenarios and what if could happens, then it gets a little complex. So let's bring it back up to the original 140 and 140. I'll make this blue and border two. So it's part of our table, blue and bordered, there it is.