 Personal finance practice problem, using Excel. Comprehensive problem, part number three, income statement, accrual versus cash basis. 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 built this from a blank sheet in prior presentation since you can go back there and construct it from, in essence, a blank sheet. If you do have access, then we got three tabs down below. We got the example tab, the practice tab and the blank tab, the example tab in essence being an answer key. Let's take a look at it now. In prior presentations, we built our amortization schedule, breaking it out on a year by year basis, using that in part to help us out to construct our balance sheet. Talked about a few ways you can construct the balance sheet and tools that could help to do so. Now we're looking at the income statement. We're gonna do it a couple different ways, discussing tools and why you might have a couple ways you might construct the income statement and then we've got the estimated property insurance that we'll take a look at next time. Let's take a look at the second tab, the practice tab. It's got some pre-formatted cells so you could work through the practice problem with less Excel formatting. The blank tab is where we're gonna do the Excel formatting as we enter the data. Let's take a quick look at where we are now which is the balance sheet is what we did last time. We're gonna continue to the income statement, the two major financial statements, balance sheet and income statement. The balance sheet, you can construct a few different ways like we talked about last time. You can actually construct it as of a point in time using the resources available such as the financial statements statements that you have or you can link it to software which isn't accounting software but is finance kind of software that will help you construct a balance sheet. You could use something like a personal capital and or you could use like a quick in instead of quick books. Those will not help you as much however to construct the income statement. They could pull the data from the financial statement but they're not as easy to construct an income statement. So those are useful to get those ending balances in and they're useful for things like investments and those types of things that fluctuate in value. And then for the income statement the easiest thing to use is like accounting software if you were trying to automate the system that you would connect to the bank. For example, but it wouldn't be pulling in the ending balance as these softwares could but rather you would be entering the data into the system and verifying the ending balance that helps you for the income statement. So something like a quick books could do that. I think there's a free one called Wave that you can look into do your own research but you can take a look at it possibly to try to make this as easy as possible by automating it. And so you could use both of these softwares too they both have their own particular needs but the income statement you typically have to do some accounting that is gonna have to be involved in some way shape or form. So let's go ahead and hide some of these cells. I'm gonna make the skinny AA first. So I'm gonna go to skinny S, gonna go up top to the home tab, hit the format painter and I'm gonna make the skinny AA, the skinny AA and then it'll put my cursor on the Z column. Gonna drag all the way over to the C column, C to Z and then right click and hide those cells. So we're gonna hide those cells. We've got our income statement data on the left hand side. So we're gonna imagine that we've got this information which we can get from our paycheck stubs. We're gonna construct it on that way or you might see this information flow through to your bank accounts as well. And then we've got our expenses. These are items that you could automate using accounting software, but it would be listed out by who you paid rather than the expense category which you would then need to categorize which you can get to pretty good at automating if you wanted to use accounting software to do that. We might dig into that a little bit more later. We're gonna make the income statement a couple different ways. So I'm gonna make column AB a little bit wider. I'm gonna put my cursor between AB and AC, make it a little bit longer here, fatten it up. You need to fatten that cell up. We're gonna put some income statement. Statement, it's gonna be gross income, gross income. I'm gonna record this at gross income. This will be more of a cruel kind of statement. And I'm not trying to dive too deep into a cruel versus cash. I just wanna touch on a couple things that could be different in terms of the tools you're using to construct your income statement. I'm gonna make column AD a little skinnier, a skinny column, put my cursor on this skinny. And I don't even know what it is anymore because it's so skinny, I can't even see the name. I'm gonna put the format paint, make the AE skinny. And this is gonna be an income statement. But this is gonna be the cash flow, more of a cash flow income statement. Brackets, or I could call it like the net income on the income statement. So I'm gonna make this a little bit larger. And so there we have it. Let's put the headers up top. So I'm gonna select these two, make that black and white font group, make it black and white, black and white. This one too, same thing, same thing. We're gonna go up top, font group, black and white. Okay, so then I think I have one column too many. I don't need this AD, why do I have that there? That's useless. I'm gonna put my cursor on AD, right click and delete it. All right, so now we're gonna start with the income side of things. On the income side of things, we're gonna imagine we have two incomes, possibly a couple, or possibly we have, we have possibly one person with two incomes. And so those are gonna be, we could think about it two different ways. We might try to get this from our W2, for example. If you get it from the W2, note there's multiple boxes. Box one is probably the lowest box in terms of your income because it might be decreased by things like a 401k. And then there's box three, which is your social security. And box five, the Medicare, one is probably the highest income. So you might look at that on a yearly basis. You might try to look at your paycheck stub. Your paycheck stub will have your gross income. And then all the withholdings, which include taxes, which is social security, Medicare, and federal income taxes, as well as benefits. But what actually hits your bank is gonna be the net amount. So if you're looking at what actually hits your bank, you've got the net amount. So if you're trying to automate this as easily as possible using software like a QuickBooks that connects to your bank, that tries to record everything on a cash basis when it hits your bank, then the thing is it's gonna take up the net amount and you'd have to do a little bit more work to get it up to the gross. Let me show you the difference. So if I go up top, if I was gonna say we're gonna pick up the W2 gross amount, and it doesn't mean it's like nasty gross, it's just the gross versus the net income. This is the gross income. The gross income is gonna be equal to, I'm just gonna pick up the gross income of the 60 and this is gonna be the 79. We're gonna pull that from our data set down below. And then on this side, we've got the income. If I was pulling this from the bank and seeing what actually hit the bank statement, I would see deposits in the bank statement, which we're gonna say would add up to the net income that would actually hit our bank account, which would be the easiest thing to do, but it already has some of the other items taken out of it, including the taxes. So it would look like this and there we have it. Now, you can use kind of either of these methods. Like I said, if you used a QuickBooks and you connected to the bank and you just recorded your deposits as income because they're deposits from your employer, then it would be recorded at net, but you just gotta recognize that that's net. You've already taken out of your earnings, your actual earnings, at least taxes and possibly benefits whatever's in the withholding. Or you can try to, as you put this into the QuickBooks system, adjust these amounts to line up with your paycheck stub, which will give you the detail about your federal income taxes, your social security, your Medicare and your benefits. So let's add these both up. I'll show these out and we'll reconcile these two formats at the bottom so we can see the difference. So this is gonna be the total income, we're gonna say. This is the sum. Now this is the actual total income on this side because it does not include our taxes because we took the gross amount, which we could get from the W-2, or from the paycheck stubs. And this will be total income. And this is gonna be the net income, which we would get just at what hit our bank account. Obviously the net income being lower because we already took out some of the stuff. Let's do some indentation stuff. I'm gonna select these three. We're gonna go to the Home tab, Alignment, Indent. And then I'll double indent this one, Double Indent. And then let's select these three and let's Format Paint that, go into the Home, Format Painter and Paint Brush it right there. The Paint Brush has done its magic, even though it wasn't, there wasn't actually paint or color in it, but it was Format Painted, Format Painted. So this is gonna be the expenses. I'm gonna say these are like the normal expenses, normal expenses, and then we'll have other expenses we'll put down below, which is gonna be the taxes that we'll have, which will be the difference between those two. That'll be one of the reconciling items between these two setups that we have here. And on this side, we have the expenses, which we're gonna do on a cash outflow basis, more of a cash flow basis type of thing. So this is similar, more like a cash flow statement in some degrees, because we're gonna show some cash flows. I'll show you what I mean when we get to them. So we're gonna go over here, and then we've got our expenses. So we've got the property taxes. So I'm just gonna say equals property taxes. Now, if you used software, then the property taxes would be fairly straightforward. Because when you paid the property taxes, then it would be showing, then you'd be paying the property taxes, and you would show it at that point in time if you're using a cash basis system. If you were using more of an accrual-based system, even more of an accrual-based, you might actually put the property taxes on there as a prepayment or something like that. You might allocate the property taxes, in other words, to the period that the property taxes have actually covered, because you usually only pay them once or twice a year. So, and they're going to be covering the entire year. So what you would like to do on a more accrual-type of system is allocate the amount over the timeframe that is covered, not when you paid it. But if you're doing a cash-based system, I'm imagining that we're using something like a QuickBooks or a Wave, and we're using a cash-based system, Ither Hue, which means it would be recorded when you paid the property taxes, and the system can automate that fairly well because it would be able to see the property taxes when it was paid. And then we have the utilities. Again, the utility's fairly straightforward. It wouldn't say utilities on your bank statement. It would say Edison or whoever your utility company was, but it would be something that you can pull on either of these methods. I'm just going to pull the same number over here. And so those are fairly straightforward. That we can automate accounting system on more of a cash-based system to pick both of those up. The food, if we paid for the food with some kind of electronic item like a credit card, which we can use bank feeds to pick up, or with a debit card, then it would hit the bank and we would then see not food, we would see basically the restaurant that we went to to buy the food, and then we can categorize it under food or groceries or whatever in that format. Next, we have the gas and maintenance. I'm going to say gas and maintenance. This again, when we paid for the gas and maintenance should be fairly straightforward. We would see it going through the system for the gas company or the gas station that we went to. And then we would allocate that to the expense category of gas and or maintenance or auto or car expense, whatever you would want to call it. And then this is the one that gets a little tricky. We've got credit card payments down here. From a cash flow statement, you might think of the credit card statements as some like a minimum payment that you're going to be paying each month. For example, if you're not paying off the full balance, same with the car loan payments. You might say, hey, look, that's a cash flow payment I have each period, but those payments actually have a balance sheet component and an income statement component to it. So if we were to record those properly, we should be recording them not to the income statement on more of a cruel statement income statement, we should be recording the decrease to the loan and just recording the expense for the interest expense. But on a cash flow kind of income statement, if I'm trying to budget to see how much cash I'm spending, then you might record them as an expense. And that's really the difference that we're looking at here. If you were to use accounting between these two systems, another difference that and the income items. If you were using accounting software, then these would come out of the credit card, for example, as, and you can connect that to the bank. So you'd see the payment in the credit card and it would naturally link, you would want to link it to the other side, which would also be on the bank feeds coming through the bank because it would be a payment that came out of your checking account. So it wouldn't really hit the income statement, even though it was kind of like a cash outflow. But when you're constructing it this way, you might construct it from a budgetary standpoint as a cash outflow to get your cash flow into order. And with the car loan, when you pay the car loan, you would see that coming out of your bank account. And to do it properly, you would allocate it according to the amortization table between, which will be similar to the loan, which we'll take a look at shortly for the home between the interest and the principal. But from a cash flow statement perspective, you might see it as just the full amount being paid. Okay, so that means on this side, I'm gonna record these two over here from a cash flows perspective. I'm gonna put these two down here, credit card payment, and I'm gonna copy that down. And over here, we'll put the amounts. So we've got the credit card payment and I'll copy that down for the car loan, copying it down. I'm not gonna put it on this side, but instead, I'm just gonna imagine we've figured out what the interest on the car loan and credit card were. And that's $75. So of these payments that we made, 75 of it was interest, I'm going to imagine here, we would get that from the amortization table, which we will see shortly how to do when we look at the mortgage payment. So that's gonna be that one. So there's the interest. And then we've got the interest on the mortgage. So then the interest on mortgage, or let's just say home loan. So the interest on the home loan. So I'm gonna unhide some cells so we can see that. I'm gonna put my cursor on B to AB, right click and unhide so we can look at our amortization stuff now. So on the home loan, whenever we make a payment, we've got our amortization table. So if you were to use accounting software for a loan, and this would be the same thing with the car loan, most likely, then you'd see the payment coming out, for example, each payment would be $1,315 for this loan, but you'd have to break it out between interest and principal to record it properly on a cruel type of basis. So that kind of puts a bit more complexity into our accounting system. It's a little bit less to automate, especially because the interest and loan amounts differ from year to year. So we're just gonna pick it up over here, and we're gonna say that the interest portion, because this is a year's worth of data, interest portion was the 12,166, and the loan decrease for year one was 3614, total payments for the whole year, which would be this 1315 times 12 was the 15,780. So I'm gonna say, all right, let's go down here. That makes perfect sense, doesn't it? Makes perfect sense. Perfect sense, and then we're gonna say this is gonna be the 12,166, and then on this side, I'm gonna do the same thing that we did with the gas, with the credit card and the car payment. I'm just gonna say this is the home loan payment, and I'm gonna take the full amount and just look at it from a cash flow perspective. So we're gonna say the whole amount was cash flow that we're paying the 15,780, but really, 3614 is paying down the loan. So from a cash flow perspective, when I'm trying to figure out if I got enough cash flow, if I'm making my income statement for that perspective, then I'm gonna use the full payment amount when I'm trying to think about it in terms of more of an accrual accounting, then I'm gonna use the amount that is just interest, because the other side is really decreasing the balance sheet. And so we might dig into that a little bit more later on using accounting software to get a little more depth in those details. So this is gonna be the total normal expenses, and this will be equals the S to the U to the M, otherwise known as the sum. And this one over here is gonna be the total, I'm gonna call it the total expenses from a cash outflow perspective. That's too much words, the wordings are too long. Put it outside, let's put a equals to the S to the U to the M, summing that up, we'll put an underline here, we're gonna go to the home tab font group underline, we'll put an underline there, font group and underline. And so there we have that. And so then I'm gonna put down below, this is, I'm gonna call this one net income before taxes, and I'm gonna say and withholdings. So this is gonna be a bit of our reconciliation, making that sell a little bit wider between this top difference up top. So I'm gonna then subtract this out. So now we've got the income right here minus the expenses, but we're not taking into consideration the fact that we had the withholdings up top here that we gotta do now. And then down here, this is just gonna be, I'm gonna call it net income or cash outflow, or cash flow, let's say. So this is gonna be equal to, this'll be the 113 minus the 18185. There's the difference, let's put an underline here, font group underline, let's do an indentation. Let's correct the spelling here. Let's put a colon at the end of this thing. Let's do some indentations here for, I'm gonna stop saying here, I'm gonna stop saying that in case you're getting bothered by it because it's bothering me, I'm gonna stop doing it. I'm gonna put one there instead of here, there, I'll put it there, that's the same thing. Now you're just saying there, I'm gonna do that. On this side, we're gonna select these items, home tab, and then we're gonna alignment indent, and then indent it again, alignment and indent. Let's put an underline on the 1491, font group underline, so there we have it. So now we've got this difference between the income line items that this difference wouldn't be on our bank account because we've recorded these on the gross up top, which we could have got from the W-2, for example, or when we enter it into the system, when we see that deposit entering into the system and say QuickBooks from our bank account, we might then add the detail at that point in time, which we would have to take the actual pay stub and look at the withholdings that would be involved in it, or here what I'm gonna do is I'm gonna take the difference between these two and assume it's withholdings, which could be federal income taxes, state taxes, social security, Medicare, but it could also include other benefits, the other benefits like a 401k, and that 401k stuff could be a balance sheet, have a balance sheet impact. So that gets a little bit complex because we've got this activity happening that's happening without hitting our bank. It's going directly from our employer to the people we owe taxes or to our 401k plan, for example, and therefore we can't, it didn't hit the bank, so we don't have the bank to help us with that transaction, so we'd have to record it in some way, shape, or form, even if we're trying to record all of our system on a cash basis from the bank transactions. So I'm gonna call this taxes, I'm just gonna say taxes and withholdings. Taxes and withholdings are gonna be equal to, I'm gonna take this 139 that was on the gross basis, minus the 113, the net basis, so we got 26,000 on the taxes and withholdings, and so that's gonna give us our net income after net income, net income after taxes, and withholdings, withholdings. Okay, there should be an S on it, it needs an S. Okay, so this is gonna be the 124.909 minus the 26,000. There we have it, let's put an underline here. Underline, let's put a double underline there, and let's put a double underline here. So notice we have a difference between these two, and you might say, well, I'd like to know the difference, let's do a little reconciliation to see what the difference is. So I'm gonna say the difference, so the difference, whoa, what happened there, that was funny. This is gonna be the difference, it's gonna be equal to the 98.909 minus the 94.815, so there's the difference. The difference isn't between these two because we took care of that there. This difference, this minus this, has been taken care of by this number, so that's not a problem. What we have then that for, let's do a reconciliation, let's call this a reconciliation on this difference. We're gonna say font group, black and white just to get an idea of what's happening. So the main difference we have here is that when we recorded it more on the proper recording on this side, we recorded just the interest of the loan payments, whereas over here we recorded the home loan payment totally. In other words, if I look at my amortization schedule that I broke out on a year by year basis for the home, if we recorded the payments, we'd have a cash outflow for the year of the 15, 7, 80. But really, 12,166 was interest, which was an expense. The other 3,614 is paying down, would be paying down this loan amount here to get this loan balance to be the proper amount there. So really that's one of the difference. If I looked it on the cash flow basis, I'd have the larger number. So let's put this as the loan balance, let's say home loan, home loan balance reduction. And I'm gonna pull that from the table. I could take the difference between these two. I could take this minus this, but I'm gonna pull it from the table so we could see it over here as well. So there's the 3,614. So that's the big difference. We also had the fact that we put the same similar kind of scenario, these credit card payments in the car loan. We put the full outflow here instead of break it out just the interest portion. Same kind of concept, although we don't have the amortization table to fully flush it out for those two, but that's the same type of thing. So those two were there, whereas on this side, we recorded just the interest related to it. So just the interest on this side. So I'm gonna say negative of this number because I want the difference between the two. And just like this was the difference between these two. I'm taking the difference between what we did on these two and that's gonna be our reconciliation. Let's put an underline like that. And this is gonna be the reconciliation, reconcile equals the sum. And then you're supposed to bring things together with the reconciliation, but it starts with rec. That seems like you're gonna put the wrecking ball to something, doesn't it? When reconcile in it, I don't know. Home tab, we're gonna go to the font group, border blue, border blue looks good. And so there we have it. Let's do some border blues on this stuff. Gonna select this stuff and do border blue. And this one, we'll just do border blue, border blue. And then this last one, one more time, border blue. Let's do a spell check, review, ABCs, withhold deans. Withhold deans. I think that should withhold whatever. Withhold deans. I can't spell it every time. Every time with that word, it's wrong. So there it is. And I think so we got multiple tools to help us out with the income statement. It's kind of more complex statement because it's a timing statement and so on. So different tools we can work on to put that together. But if you're using software, which we might dive into later just to practice with that, you might use like a QuickBooks or a Wave. And you might use that in conjunction with the other types of software, like a personal capital that can help you to put those indie and balanced type of stuff together. But there's no perfect world you can't escape. Even if you link QuickBooks or Wave and this other software to the bank, you can't really escape some accounting concepts that are gonna mess things up and just not make it completely automated. So we might dive into those in a future presentation.