 Personal finance, Excel, practice, problem. Comprehensive problem, part number four, estimated property insurance needed. 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 basically started building this from a blank sheet in a prior presentation and you can start there. If you do have access, we got the three tabs down below. Example, practice and blank. The example tab in essence being an answer key. Let's take a look at it now. We've got the information on the left. We started out by creating the amortization table for a loan on the home. Then we made the year by year breakout on that amortization table, used it in part to help us to construct the financial statements of the balance sheet and the income statement. We talked about tools that we can use to help with that construction. We did two income statements, one more in a cash flow type of basis, the other more on a gross basis. We talked about the differences between those two and the tools that could be used. Then we're looking now at the estimated property insurance that may be needed given the current situation provided. Going all the way back to the left, we're then going to go to the practice tab. It's got some pre-formatted sales that can help you work through the practice problem with less formatting. The blank tab, that's the one we're going to work on where we're going to practice our Excel formatting as we go through the practice problem. Going to go all the way to the left here, all the way to the left where we're going to construct our new table. So I'm going to put a skinny cell. I want to put a skinny AG. I'm going to go to my last skinny column. I don't even know what it is. I think it's AD. We're going to go to the home tab. I'm going to make that a format painter and make a skinny AG. Then we're going to hide from AF on over. So I'm going to put my cursor on AF left click, go to the left, scroll into the left, all the way to the left until we get to column C. Then we'll right click and hide that stuff. Hiding all of that stuff. It didn't hide all of it. Did that hide all of it? What happened? Right click and hide. I think I unhid it. Hide. That's what I wanted. That looks better. Okay, so now we're going to make another table here. So I'm going to call this an estimated, estimated property insurance needed. This will just be a rough table to get a look at the property insurance that might be needed given the information. I'm going to make this go all the way out somewhere around here. So first we got the real estate. So we've got the real property property because we own the home. This portion would be not needed for rentors because we wouldn't be owning the real property in that case. I'm going to make this top portion. I'm going to select from AH to AJ and we'll go up top to the home tab font group and we'll hit the bucket drop down, make it black and white up top. Okay, so this is going to be the replacement cost of the home. So we put the home on there. We purchased the home and we said that the price was the 306 and now the replacement cost we're going to say is now at 325. So it's going up in value since the point in time we purchased it and we would like to be fully covered so that we have so we have the insurance at the replacement cost so that we can replace the property. So we're going to need the insurance up to the actual value which we're estimating at this point at the 325 not the 306 that we pay for 3062. So let's go up top and say this is going to be equal then let's put that over here. So we're going to say this is going to be the current replacement value of the home. I'll put that right into the outer column in AJ equals will scroll on down and say that's going to be equal to the 325 the 325 next let's put an indentation. We're going to go to the home tab and alignment and indent it. So then we got the personal property so personal property brackets and we're just going to give the estimate here on the personal property. This is stuff you'll note that we didn't include on the balance sheet so we've got things like the appliances furniture clothing household items we could include those on the balance sheet and you can see they're fairly significant in value the problem with including them on the balance sheet as we don't know exactly what they're worth at this point in time we can try to estimate it if we so choose we might want to group them all together and put an estimated number say on the balance sheet if we wanted to but and they go down in value typically if we're talking about normal household goods. So then we would have to reevaluate an estimate so you might put like a normal number on the balance sheet what you think the average is over time as you purchase and sell or you might try to list everything out on the balance sheet but that might be a little bit too tedious to do that. However you might listed out somewhere else you might try to list out another schedule that would possibly support a number on the balance sheet which you put one group number on the balance sheet like a 21,000 and then you group out all the items taking pictures of your TV and your other household goods your appliances so that in the event that you have to replace them for insurance you've got all the documentation necessary to do so. So you do want to have that backup supporting information and you might just group it in one number say on the balance sheet for example but we're going to include we're going to include that over here we're going to say this is going to be equal to then this and I'll say and the clothing I'll say this to so that long description here I'm going to indent both of these let's indent these home tab and alignment indent I'm going to indent the second one a double indent so I could see that these two are kind of related and then I'm going to say that's going to be let's put that in the inner column here it's going to be equal to the 21 because we also have that jewelry that we're going to be dealing with as well so that the normal household stuff is at the 21 it usually goes down in value over time you might have some other stuff some other valuables that go up in value such as say jewelry so then we got addition so we might have additional coverage additional coverage for items with limits on let's say standard personal coverage like jewelry firearms silverware I eat with silver spoons and photographic electronic and computer equipment so we might have to have other additional coverage for this kind of stuff so we got the additional coverage for this kind of stuff I'm going to indent these let's say we indent this indent and then I'll indent this here and then I'll put that on the inner column and I'm going to say this is going to be equal to we got jewelry of 2000 so if we wanted to cover that it might take some additional type of coverage if we're looking to do that within our plans we'd have to look into that all of our firearms that we have because we don't want to lose our guns I would be horrible there's a disaster that's why the guns are there and the events for the disaster so in any case so that's going to be the total we're going to say total personal property personal property outer column equals the sum of those and I'll indent that a couple times indent indent so let's indent one more time total personal property we'll do that there maybe just a double indent we'll keep it there okay so then we're going to say type of coverage so the I'll just list the type of coverage for personal property and we want replacement value replacement value instead of instead of the cost because the actual cost because we want the higher value so we're looking here to pick the higher you want to look at which would be best for you in those circumstances and that would have an impact of course on the premiums versus the kind of payout that you might get in the event of a problem the other categories you might consider we won't put anything in them but we could have personal liability so we might need some added personal liability add additional personal personal liability coverage for personal injury claims so right if someone gets a problem on our property and they try to sue us out for example we give them a cup of coffee and they do the classic McDonald's thing and they dump it on themselves and and then sue you for a million for burnt for being burnt or something like that anyways then you could have more coverage that you might need for that kind of thing maybe it depends on the neighbors or something so we're going to say but we're not going to have any added coverage for that I'm going to say zero there and then you might have flood or earthquake because you'll recall that these kind of things often aren't included in the normal coverage which you would think possibly that would be because if they were they would throw off the actual zero actuarial calculations for normal things that are outside of flood and earthquakes and flood and earthquakes are difficult to basically judge and so they might you might have separate insurance for that which is designed specifically for flood and earthquake and they can do an actuarial table for those things themselves and pay for it separate so we got the earthquake coverage excluded from home insurance policies policies so if you're in an area with earthquakes and whatnot like I am then you might need the additional coverage for the earthquake let's actually drag that down I'm going to drag that down I'm going to do a cut and paste control X and I'll paste that here and I'm going to that was control V to paste it and this is going to be I'm going to call this specialized cover edges for example so now we got the flood and earthquake if that's necessary and dent and dent I'm not going to buy any personally and then we're going to say that that's going to be the total property insurance needed okay and it's got that additional liability that would be in there if we had that as well so I'm going to sum this up equals the sum of these items the outer column and so we're looking at the 348 so that's just a general idea of how you might try to break down you know your insurance your insurance calculations and so on so I'm going to do is our blue and border here so we're going to say let's do some font group and make this bordered and then blue now obviously the big factors that could happen going forward to adjust this is you might have you might have changes to the value of your property because we picked up the property value here and so we so that's something to kind of keep keep an eye on and then we could have differences and say that personal property which could you know adjust the values that we have but we also want to make sure that we have the documentation and supporting documentation in the event that we need to have a claim on it so you might want to have pictures and documentation of that kind of property as well as as well as basically any kind of more expensive other kind of additional coverage items like jewelry firearms and so on okay so let's do a spell check on this one and spell check personal personal change that one silverware like my silver spoons earthquakes I still got that one which was in my mouth when I was born I kept that one man you spelled a bunch of things wrong renters renters renters replacement okay so there we go I'm going to unhide some cells over here from B to H a ha and then right click and unhide and so there we have it