 Personal finance practice problem using Excel. Life insurance using personal financial statements, part number three, income statement, cash basis. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to it, that's okay because we've basically been building this from a blank sheet starting in prior presentations with a balance sheet and an income statement on an accrual method. And now we're continuing forward with the income statement on a cash method. If you do have access to this worksheet, three tabs down below, example, practice and blank, example in essence being an answer key. Let's take a look at it now. Information's on the left hand side. We're constructing the personal financial statements, balance sheet and income statement, thinking about the resources you might have in order to do that as we go. We then made the accrual income statement last time. Now we're thinking of an income statement more on a cash basis purpose. And we'll use these two income statements and think about the differences between them then to construct our life insurance needs using multiple methods. We will then construct our loan amortization table which can help us to get more detail on our income statement if we so choose and our balance sheet. And we'll also break that out on a year by year basis and use all those tools to have some more complex or nuanced calculations for our life insurance possibly breaking out the year by year needs, possibly thinking about more complex life insurance that might say decline in the insurance beneficiary needs as time passes. So the second tab over here is the practice tab has some pre-formatted sales on the right hand side so that you can work through the practice problem with less Excel formatting. The third tab, we're gonna be dealing with Excel formatting as we go here. So last time we put together the balance sheet and then we made an income statement on more of an accrual basis method method. Now we're looking at a cash basis or more of a cash basis method and a couple of different reasons. One, you might just, the way you're constructing the income statement, you might be doing it more in a cash basis method for example, because you might be basing it on strictly the cash flow through your checking account using something like a QuickBooks that might have the bank feeds kind of option to it. And two, there's some differences between how we might think about our life insurance needs on an accrual and cash basis method. For example, we might first think about paying off our liabilities on the balance sheet and that could have some impact on, what are gonna be our cash flow needs going forward. Or we might try to say, I wanna think about the cash flows, including these liabilities broken out as they're currently structured in terms of the payments that are gonna be needed going forward when we start to think about what the periodic needs will be for our spouse, for example, after we die in this scenario. Okay, keeping that in mind, I'm gonna put my cursor on column K here, make a skinny column that I'm gonna put over in column O. I'm gonna do that by going to the home tab, clipboard, we're gonna get the format painter paint brush and paint brush it right there on column O's. We got the skidio, skidio. And then we're gonna say, sounds like a breakfast cereal or something. These are the skinny O's. They're not gonna make you unskinny. These are skinny O's. This is gonna be from column C. We're gonna go from column C and hide some columns over to N on, but we're gonna take them off by right-clicking and hiding them. So we're gonna hide those column. Don't delete them, just hide them so that we got it going from AB to O because there's some hidden columns there. And then we're gonna make an income statement in P1. Income statement. And I'm gonna call it cash flow. More of a cash flow type of income statement. I'm gonna make column P a little bit wider. Widening up column P. Let's make some black and white for the headers from P1 to R1. Home tab, font group. Let's make that black on the background, white on the letters, black and white. And then we're gonna have our income as the start of the income statement. Now remember, when we're thinking about income, you might have a couple different sources you're getting the income from. You might have a W2. You might have your tax return. You might have the paycheck stubs. You might be using something like a QuickBooks. If you're using something like a QuickBooks, it's likely that you're just gonna try to use the cash flow bank feeds method to record your income and everything that's a deposit to your bank account is gonna be income, which means it's after withholdings. So you're probably recording them on what would be the net income after withholdings, which includes things like social security, Medicare and federal income taxes. So that's where we'll start. For one, because logistical reasons, that might be the way that you're setting it up and then we'll think about those differences later. As we do a comparison to what we did in the prior section where we have the growth. But just remember, in the paycheck stubs, you've got really your gross earnings minus what they took out, which includes the payroll taxes, which are really your responsibility. They're really kind of like an expense to you. And then you also have the benefits that they might have taken out, which are really not an expense, but they're gonna be assets, but they might be a cash flow kind of component. So it gets a little bit tricky on that. So that's, we'll talk more about that when we do the comparison between the two. But we're gonna take the net amount here, imagining that we're just gonna take the amounts that hit the bank account. And so we're gonna go on down and say this is gonna be our net income, the smaller of the two. And then this is gonna be for the spouse ones net income. And then spouse two net income, spouse number two net income, spouse one and two is the net income. And so, and we're gonna say there it is. You're right, I'm scared to say husband and wife. I don't wanna name either one of them or take any sides on anything here. But in any case, there's the two. Spouse number one, spouse number two. Now we're concentrated on spouse number two here because they have a higher income. And so the ones that we're gonna say if they die then what are gonna be the life insurance needs? That's where kind of our focus is. I'm gonna do an underline here, home tab, font group and put an underline here. And then we're gonna call this total income, total income. Put this on the outside equals the S-U-M of those two items, there's our total income. Again, that's lower than the gross income because really what they earned would include the withholdings, which would be social security, Medicare. And then you also end this federal income taxes. And then you also might have on top of that the benefits that were taken out, which kind of confused the picture a bit. But that's the cash flow that actually came through after those kind of expenses were taken out on the employer side of things, right? All right, so then we're gonna end it. I'm gonna select these three and we're gonna go to the home tab. Alignment and debt, let's indent this one again. Alignment and debt again. And then we're gonna have the expenses, expenses, expenses. And this is really cash flow, cash flow on the expense side of things because I'm gonna scroll down and this is something as we saw in the prior presentations that we would have to get possibly from looking at our checking account in some way possibly from using something like a QuickBooks, which might be able to take the outflows on our expenses and help us to categorize them into relevant categories. Now here I'm gonna try to take these outflows for like mortgage and these credit card payments and show them as cash flows, cash outflows, whereas that's not exactly right from a bookkeeping standpoint because really they're paying down the liability on the balance sheets or most of them. So they have an interest component to them and then they have the principal component to them. But if we think about them as just a cash flow component and we think, hey, look, I've locked into that to paying these and having a set structure of payments. So I'm gonna then show them as a necessary kind of cash flow. So that's why it's more of a cash flow type of income statement that we're looking at now. These are monthly amounts. So I'm just gonna multiply them times 12 to get to the yearly amount. So I'm gonna go back up top. I'm just gonna say this equals this amount on the mortgage tab. I'm gonna pick up the amount then on the mortgage which is gonna be equal to the 1027 times 12. I'm just gonna take that amount times 12 and enter. And I can just copy the relative format down. So I'm gonna select those two sales, put my cursor on the fill handle, just copy them down, copy them down. And so there they go. I went too far. So I'm just gonna delete these last two. So it goes down to that 1740. There we have it. So now we're gonna total that up. So I'm gonna say total expenses or expenses are we gonna say cash flow because we have the kind of non-expenses in there that are still cash flow items and sum that up in the outer column equals the SUM shift nine. We're gonna pick all this up from the 1740 up to the 12324 and enter. There we have it. Let's do some indentation. We got the colon. These are subcategories. I'm gonna select this entire thing, go to the home tab, alignment and dent. And then we'll go down here to the total expenses and alignment and dent again. So then we're gonna say that that is the, I'll give it the same kind of category net income before, well, let's just call this net income. Let's call it net income. And so then I'm gonna say this is gonna be equal to the 72, 250 minus the 41, 220. That's gonna give us the 31, 30. Now note again, net income, and this is kind of cash on a cash flow, cash flow in essence basis. But notice that that's a little bit deceiving because remember that this net income up here already has kind of expenses. So if we were gonna break this out, we would probably like to say I'd like to have the gross income minus the taxes that were taken out already. But again, if we're going on a cash basis method and using our bank statement to calculate it, it will already have been taken out of top. So that's from a bookkeeping standpoint. We kind of wanna be aware of that. All right, so let's put an underline here. Let's go to the font group and underline it. And this is gonna be the net income. Now to get an idea of what is happening between these two income statements, let's do a reconciliation between the two so you can get an idea of what the differences are between the two things we did accrual versus kind of more of a cash flow. So I'm gonna put my cursor on B up top. We're gonna unhide some cells. I'm gonna go from B to P, B to P, bop. We're gonna right click on bop and we're gonna say unhide. And if I scroll on over, you'll see that last time we had the 50,410 and now we've got the 3130 for our net income here. Let's just think about the difference between the two. Difference between the two is equal to this 50,000 net income minus the 3130. What is that difference? Let's do a reconciliation. Reconciliation. And I'm gonna select these. I'm gonna put a black and white header, home tab, font group, black and white. And let's say the difference between those two are of course the amount that we had for the mortgage up top because part of that should be, should be at least principal, at least part of it should be principal. So that's gonna be the mortgage, which is a huge cash flow, but again, a lot of it on an accrual basis would be going to the principal although we should have an interest component to it. Same with the credit card payment. So again, that's a cash flow payment that we're making, but some of it should be principal and the car loan. So the liabilities that we have here. Now, if I had a perfect accrual basis method, I would be breaking out the interest component to those payments here, but I didn't even break out the interest components. We just have those loans not included here, here that are included as a cash flow component. So that's the difference. So if I sum this up, that's what the difference would be. Fought group underline, let's say this is the difference. I'll just say this equals the difference. So there we have it. Let's put some blue and borders around this. We're gonna say Fought group, border and blue. If you don't have that blue, it's down here. Standard, blue. And then we'll make this one blue and bordered, blue and bordered. And then this whole income statement, I'll make that blue and bordered, home tab, Fought group, border, blue. And we'll put a double underline here, maybe. Fought group, drop down, double underline. This one needs some border around it. Fought group, border, not just blue, border blue. Border blue. Okay, so now we can think about how is this gonna help us with our life insurance calculation? Remember, if we look at the balance sheet, we might say, okay, here's my assets and my liabilities. It would be nice if I can get my life insurance to the point where I could just pay off the liabilities, right? If that was the case, I might say I'm gonna calculate my life insurance to pay off the liabilities and then think about maybe what my cash flow needs would be once the liabilities are paid off, which means it would be more on this basis, more like this accrual basis because these calculations are not including the amounts of cash flow for the liabilities. Or I might say, well, that's gonna be too expensive for the life insurance. Maybe I'm just gonna say these loans are already structured and they're on the books. And so maybe I just think about my payments and look at them from a cash flow basis and just budget them into the cash flow needs going forward since they're already structured, which means I might think about my year by year cash flow needs on more of this cash flow basis, which includes the payments of liabilities like the mortgage and the credit card and the car loan in this case and think of them as cash flow needs, kind of going forward and pay off the liabilities in the way they're structured currently that they're gonna be paid them off basically as they go into the future. So now when we think about the life insurance needs, when we think about the month, the year by year cash flow needs, we might take that from the standpoint of like our income, the 60,000 and use some kind of variant on that. Or we might look at it from an expenses standpoint and think about the needs of the expenses and we might even take a fraction of the expenses or subtract out the amount that is earned by the spouse, for example. Although if we passed away, then they might need a lot more time to take care of the five kids in this case, right? So they might have, and then over here, or we might try to think about it in terms of the expenses more on a cash flow basis to think about those year by year needs. And then we might tack on top of that calculations based on other targeted needs, like college, for example, or like saving for an elderly parent's needs or something like that for medical needs or retirement or those kinds of things. So those are a couple of things we might use these different calculations for. Next presentations, we'll start to do that. We'll start to think about different life insurance calculations.