 Personal finance practice problem using Excel, life insurance, using personal financial statements, part number two, income statement, a cruel method. 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'll basically build this from a blank sheet which we started doing in a prior presentation. So you might wanna go back there and see how we constructed the balance sheet. We're now continuing on to the income statement. If you do have access to it, there's three tabs down below. An example tab, a practice tab and a 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. We're first gonna construct the financial statements, personal financial statements, balance sheet and income statement. Last time having done the balance sheet of this time income statement. We'll talk about a couple different ways that you might get this information as we go through the construction of the income statement. And then we'll think about some different calculations for our life insurance needs. We will then move into the calculation of our amortization on the mortgage, breaking it out on a year by year basis so that we can then do some more complex calculations on our life insurance needs using these tools for that more complex calculation. The second tab has a pre-formatted worksheet or pre-formatted worksheets so that you can work through the practice problems with less Excel formatting. The third tab is where we're gonna work this from a blank sheet. So last time we set up our data on the left-hand side or you can set up the data if you don't have access to it. We constructed our balance sheet from it. This time we're gonna take a look at the income statement. So we've got our information up top. We're focusing here, however, on the income statement information here and our monthly expenses. Now note there's multiple different ways you might compile this data a little bit more difficult than the balance sheet in some ways because the balance sheet, you could use something like a personal capital or just look at your financial institutions to see where your ending balances are to put together at least the financial components of the balance sheet. The income statement, you've gotta look at the timing of things, of transactions as they happen and categorize them in some way. So sometimes the top part of it is easier to do because you might be able to get that from pay stubs or you might be able to get that from the W-2 with the tax return or something like that. But then on the expenses side of things, we're gonna have to determine in some way the grouping of the expenses. And so you might use software like a QuickBooks or something like that, Accounting Software this time where you might just try to look through your bank account and compile your expenses and group them into relevant categories so that you could try to do a projection going forward because in essence, we're looking from a life insurance calculation standpoint what we think the projected future expenses will be so that we can think about how much life insurance would be needed in order to cover those needs, a bit more specific an approach than just using kind of like a heuristic or just using basically our income number that we're currently standing at, which you can do. It might be a little bit more sophisticated to try to think about what are my current financial needs, what do I think those financial needs will be on a cash flow basis possibly in the future and then use that information to try to consider what the life insurance needs would be. So we've got the information for the gross income here. We've got the net income. This is what you might see on your pay stub, for example, or you might see on the W2 where you might have the gross amount and then the net amount and the difference between the two being withholdings which might be at least taxes and you might have other things like a 401K that's being taken out of there. So then when we actually record the income statement, there's a question of, well, do we want to report it as gross or net? If you're taking this information from just basically your bank statements, you're gonna see it flow through on the net basis, but the taxes are kind of your obligation too. So we'll try to calculate that a couple different ways to see those differences. And then we've got our monthly expenses that we might group together just simply from the cash flow, from the checking account or using software such as a QuickBooks or something like that. So we did the balance sheet last time. Let's make some space on the right-hand side to add the income statement. I wanna make a skinny K first. So I'm gonna make the skinny C. I'm gonna want the same skinny size. So I'm gonna go over to the home tab. We're gonna basically take the format painter here and copy that skinny C, paste it on down on the skinny K. So there we have it, same skinniness. Then I'm gonna put my cursor on the skinny C and drag on over to the J's, C to J and I'm gonna hide these cells so we can see the income statement right next to the data. Right click and hide that stuff. Hide it, don't delete it, just hide it. So there we have it. So now we're going from B to K. There's some missing stuff there because they're hidden. Those are hidden, they're not gone, they're just hidden. So now we're gonna call this an income statement and L1 income statement. And this is gonna be more or less more of an accrual method. And we'll see this, you can't from a cat from a financial or life insurance perspective, we might think of it in terms of what we think the future cash flows would be or we might think of it in terms of basically the expenses and we'll get into kind of why after we construct the income statement, but you might see it on more of a cash flow basis versus possibly more of an accrual basis. I'm gonna make the column L a little bit larger, spreading out the column L and then we're gonna say these top two cells, let's make like three from L1 to N1 and we'll go to the home tab and font group and let's make it black and white, black and white as has been our custom. We'll start off with income first, so income on the income statement, I'm gonna say colon. Now we wanna think about do we wanna put the income in their gross or do we wanna put it in their net? So remember that if you're looking at the income stub from your pay stub or your W2, you're gonna have the gross number and they gotta give you the breakout of what was taken out of it, which is basically gonna be the withholdings on the net. If you're seeing what flows into your income statement, I mean into your checking account, it will be the net amount, but what was taken out of it, taxes and withholdings are gonna be basically your expenses or your cash flow or things that are actually your responsibility in essence, but they never hit your bank account because the employer was required to take them out first. So if you were putting this into something like a QuickBooks or something like that, you need to be careful with it because if you're just trying to do it on a cash basis method, you're only gonna see the net amount actually hit your checking account and so that's probably what you will be recording in that case, because you're gonna try to do things possibly as easy as possible, which means you're just gonna do a cash basis and you would be using the cash as it hits your account. If you're trying to pull this up from like the W2 or you could do it more complex in QuickBooks, by the way, you could obviously then adjust it for that transaction to include the gross and the amount that was taken out by actually looking at the paycheck stub that was used to hit the checking account, but if you're looking at the paycheck stub or you're looking at the tax return of the W2, then you gotta realize that that amount in box one of the W2 or the paycheck stub, the bottom line is the net amount, but the top amount is what you actually earned kind of in theory before they took out the withholdings. So generally, we're gonna start here to try to put it in there at the gross income, which would be your total income before the withholdings and then we'll record the withholdings below, which we're gonna assume being taxes in our case. So I'm gonna say we got income one gross, we're gonna say we have two spouses. So the income for the first spouse, we're gonna say is 25,000 and the income for the second spouse, I'm gonna say is equal to the gross on the second spouse and that's gonna be the 60,000. Now, when we're thinking about this from a life insurance perspective, you might have both individuals that have kind of life insurance, life insurance for them, life insurance needs, but we're gonna assume here that the life insurance for the higher earner is gonna be the one that we're more concerned about because they're gonna be the wage earner more so in this scenario. So that's what we're gonna kind of focus on, what if this individual died and then you got a cash flow needs that are gonna be happening because that person is looking to be more responsible for the income side of things, possibly the other and this relationship responsible for the childcare and that kind of stuff. So we're gonna put an underline here, fought group and underline, that's a double underline, I just want a normal underline. And so this thing is gonna be the total income. So I'll put total income, total income in the outer column. We're gonna sum this up equals the SUM, SUM of the 25 and the 60. So they're at 85,000 at the total. Okay, so let's do some formatting here. We're gonna select these three, do some indentation, home tab and then alignment and indent. Put my cursor on the total income and alignment indent again. And then we'll get into the expenses. So expenses, expenses. Now the expenses, like I said, you might get from a QuickBooks or something like that, you might try to take your bank account and break out the categories of the expenses to help you up their software to help. But it's a little bit more difficult than just getting those ending balances. You might just look at your bills, you might take a look at your checking account and try to compile your expenses into categories such as these categories here. Now, you also gotta be careful with some of the cash flow items, which are for liabilities. So like mortgage, for example, would be something that most of it is gonna be paid towards either interest and then paying down the principal on the loan. So if we were gonna record it like on an accrual basis, then we would just want the interest portion. I'm not gonna include either the interest portion or any of it because it will make the amortization table later, but that's gonna be one of the kind of tricky things when you're trying to think about how are you gonna be setting up your income statement. From a life insurance calculation perspective, we can look at this from a couple of different ways. We might wanna think about it in terms of our cash flow, in essence, our income statement, kind of on a cash flow basis method because that could help us determine on the method we're using for life insurance or we might wanna have more of a cruel method and we'll see the two methods and I'll reconcile the two later. So I'm not gonna include the mortgage interest this time. We'll include it on the more cash flow income statement, although we should have the interest, but I'm not including that here either. So then we got the property taxes. So property taxes is gonna be a general expense, right? And then we've got the homeowners insurance, that'll be an expense. We've got the utilities, we've got the food, we've got gas and maintenance, credit card payments. This is another one and the loan payments, which again, these are items that are really paying down part of the principle and we would have interest related to them, which we should be recording on an accrual basis, but I wanna just note that those two are a portion of it at least paying down the loan. So I'm not gonna record even the interest portion. This time I'm gonna record it without these items and then in the second income statement, I'll record it on a more of a cash flow basis showing these items and then we'll kind of reconcile the difference and talk about why that might be relevant from a life insurance perspective. When you get into the accounting of it, then these payments get a little bit tricky because to record them properly in something like a QuickBooks, you have to then say, I'm gonna break out the interest and principle portion related to them or come up to some method on how you're gonna treat that. We might get into bookkeeping more in future presentations. So I'm gonna go up here and I'm just gonna say, all right, the expenses are gonna be equal to, I'll pick up the property taxes. I'm gonna start with the property taxes here and then I'm gonna say that's the monthly amount. So I'm gonna say equals that monthly amount. We're gonna say that monthly amount times 12, 12 months in a year, so just times 12, which you could see up here. So it's B28 times 12. And so there's the 5,280. I'm gonna copy that down. I'm gonna select these two items, put my cursor on the fill handle and copy it down. So I went a little too far, so I'm gonna scroll back up on it. So there it is. And you can see it basically just took the next one down and multiplied it times 12, right? Food, it looks like it's getting what it should. And then these two items here, the credit card and the loan payment. I don't want those here. I'll deal with those on the cash flow basis. Again, I should have the interest, but I'm not gonna even include the interest on the accrual method. I'm just gonna show the difference kind of between the two this way. So I'm gonna right click on this. I'm gonna hide, I'm gonna delete this and shift the cells up, shift cells up. So there we have them. So now we've got the entertainment. So then I'm gonna say total expenses, total expenses on the outer column. It's gonna be equal to the sum of these items. We will sum that up, put an underline here. We're gonna say home tab, thought group, underline. Let's do some indentations on all the expenses. Selecting the expenses, alignment, indent, total expenses, alignment, indent, again. So then we're gonna have, I'm gonna call this net income before taxes and withholdings, with withholdings, with, or other withholdings, so I'm gonna increase this item here. And so notice, because we recorded this on the gross basis, we've got the withholdings that were taken out automatically by our employer, which are not gonna be included in our expenses if we compiled the expenses simply from our checking account in some way, because they never hit the checking account because the government, mostly the government forced the employer to take the money out before they gave us the money, those jerks. And so we're gonna say messing up my bookkeeping when you do it that way. So that's gonna be the 63, 160, and then I'm gonna say taxes and withholdings, withholdings, withholdings, are gonna be equal to the gross income minus the net check that we received. So in other words, if you were pulling this from the paycheck stubs, then you would have the gross amount, which would be the top line, the bottom line would be the amount that actually hit the checking account, the difference between the two being the withholdings, mainly payroll taxes, social security and Medicare, but also might have benefits like a 401k plan in it as well. If you're looking at the W-2 form, then line one is usually where people first look, but it might not be, that's gross income, but it might not be including something like a 401k plan, which has been taken out of the box. I think three of the Medicare wages might be the closest box to the full gross income. And so the difference between the gross income and what actually got deposited into your checking account for the wages because that was the net check would in essence be the withholdings, payroll taxes, 401k. Now that gets a little bit tricky as well because something, the payroll taxes we can say are an expense and in theory they are our expense related to our payroll taxes that we're paying, social security, Medicare and federal income tax, but they're being taken out by the employer and then the benefits are our benefits and those might be like a 401k plan. So really we should be, every time we record something, if we were to do it in QuickBooks for example, instead of recording the net check that actually hits our bank account, which is what we want to do or the easiest thing to do, we should be breaking out the paycheck stub and breaking out the gross pay that we earned minus and then recording properly the withholdings, which are expenses, but then the benefits like a 401k are actually assets that we're getting with the 401k. So it gets a little bit tricky to break that out. If you're gonna simplify it, you might say, okay, I'm just gonna take the difference between the gross pay and the net pay, the amount we actually basically received. So I'll do it this way. This equals, I'm gonna scroll down and we're gonna see then the pay here. We've got the gross pay. I'm gonna add up the two gross pays of this plus this, the two gross pays minus the net pays minus the 21, 250 minus the 51,000. The difference between those two then is gonna be the withholdings and the taxes. So I'll just kind of lump them together assuming most of them are the taxes, Social Security, Medicare, Federal Income Tax. Okay, let's put an underlying font group and underline. Let's put an underline here, font group and underline. And then I'm gonna call this net income. Net income and we'll say this is gonna be equal to, equal to the income before taxes and withholdings minus the taxes and withholding gets us to the 50,410. Let's put then an underline here, double underline, font group, double underline. So there we have that. And then let's make this whole thing blue and bordered. So I'm gonna say blue and bordered, font group, border and blue. And so there we have our one kind of method that we might use for the cash flow. Now from a life insurance perspective, then we're gonna try to map out into the future and say, well, what if like this income is gone, right? Then what are the needs gonna be? What are gonna be the changes to the expense needs that we might have here going forward? And it might be useful for us to look at this on a cash flow basis too, depending on how we're gonna treat basically our liabilities on the balance sheet. Or we're gonna try to set up our life insurance to pay off the liabilities. Cause if I, let's unhide some cells from B to L. Gonna select from B to L, right click and unhide. I hid them. I'm gonna go from A to M. Unhide, that's what I was trying to do. So we could set up our life insurance and say, can I set it up in such a way that they could just pay off the liabilities right here? And then they wouldn't be dealing with the cash flow component of these two that we didn't add, the mortgage and the credit card loans. That would be one way you can think of it. Or you could say, let's just keep these credit card and mortgage loans going and try to think about what the cash flow needs would be in order to pay those off, just as they're kind of structured at this point in time. So that's another way we can look at it. We might also say, okay, I would like to say, I would like to keep things where they are and base my calculation on the 60,000 that I'm earning and possibly even increase it over time due to the fact that I might have been thinking about, taking out debt based on increased earnings potential. Or we might take out like a needs approach and say, I want to look at the actual expenses, either the expenses after liabilities are paid off or the expenses including or the cash flow, including the liabilities and try to project forward what those needs approaches would be in the future and base my life insurance kind of calculations on them. So those are a couple of thoughts and a couple of methods that we might use going forward. You can remember in prior presentation, we used kind of generic approaches just taking like that 60,000 of earnings. We could do that or we could try to look at the expense approach or the cash flow approach. And we want to take that in conjunction with the liabilities. How many liabilities do we have and are we trying to structure our life insurance to pay off the liabilities and then think about the cash flow after that is happening or try to think about cash flow in general and what would be needed in order to pay off the liabilities as they're currently structured, for example. So next time we'll do an income statement on more of a cash flow basis, more of a cash flow kind of statement and include in that the cash flows for the loan amounts and then we'll use that for our life insurance calculations.