 Personal finance practice problem using Excel. Estimated home price from monthly income, part number two. 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 in a prior presentation, we basically put this together from a blank sheet. So you can go back there and start there from the blank sheet. If you so choose, we're gonna be continuing on here with the practice problem. If you do have access to the sheets, 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. In prior presentations, we got the information on the left-hand side. We used it then to populate the data on the right-hand side. We first thought from the perspective of a financial institution using certain heuristics of the financial students' institution such as a bank to determine based on our gross income how much they think we might be able to afford for the home purchase process and then distilling that down to how much we can possibly afford just simply for the financing of the loan. We then use that to determine how much loan we might be able to purchase based on that information. And then we use that and the estimated down payment to get to the amount that we might be able to afford to purchase on the home and then we double checked our number. Now we wanna think about the financial statements. In other words, you might look at this first number from the bank and say, well, the bank is using certain types of heuristics kind of shortcuts, you might think, instead of using what you would think that they would use, which would just be the financial statements. And notice again, from the banking side of things, there's a couple of different reasons or there's multiple reasons they might use a different kind of shortcut type of system. One is that they might not be able to trust the financial statements from a bunch of different kind of individuals. They'd have to kind of verify them and there's costs related to verifying and so on, whereas they can fairly easily verify the income number with W-2 statements and stuff and like paycheck stubs and so on and so forth. So if they can use the paycheck stubs, then they have more verification. There's less kind of variables if they can use that as a heuristic from there. And then you also have the situation where there's gonna be regulations in terms of government regulations that they're complying with, banking regulations and so on. So you could be in a situation where you're saying, hey, look, the number that the bank is coming to may not be something that you think is appropriate based on your personal financial statements as you do your financial statements. Now the personal financial statements may be something that the bank requires or asks for and may not depends on kind of the circumstances, but we want to do our own income statement as well in any case. And remember when we're dealing with the financial institution, we have kind of two separate goals. The institution on their end, they're trying to determine if we can pay back the loan, of course, and they wanna be feeling quite secure that we can in order to give the loan. On our perspective with the financial institution, we're not going there for budgetary advice. I'm not going to the financial institution to give me budgetary advice. I'm going there to see how much to get the maximum amount of capacity for loan capacity that I think I might need in the event that I might need it. And then I'm going to do my own budgetary calculations with my own income statement to think about how much I can afford. So those are two kind of objectives. When you're talking to the bank, you typically want to look as good as possible, of course, and so that you get the highest loan possible that you want to be honest and upfront, but you want to give them the numbers that would provide you with the best loan possibilities. On our budgeting side for the personal use, that's when we actually want to budget and see what we can actually afford and how much loan we would want to be taking. Those are two different things. We're not just going to take how much loan the bank will offer. We're going to take the loans up to the amount that we could afford and we would like the bank to be willing to give us a more loan in the event that we needed it, right? So we want that number to be as high as possible, typically. So that's going to be the example, the practice tab. We're going to have the information that already has some cells populated over here so you can do less Excel formatting in it. And then the blank tab is where we built it basically from scratch and this is where we're going to be continuing on here. So now we're just going to construct in essence a financial statement and compare and contrast that to kind of the number we got down here so that we can practice our own budgeting. So let's hide some cells. I'm going to go back on over here. I'm going to make a skinny K first. I'm going to go to column G and I'm going to go to the home tab and we're going to go to the paint for us. I'm going to make a skinny K and then I'm going to hide the cells from C to J from C on over to J. I'm going to right click and hide those cells, not delete them, just hide them from CJ. I knew a CJ one time, someone that probably stood for something but CJ. Any case, here's the financial statement. So we got the balance sheet which shows where we stand as of a point in time but we're more focused oftentimes kind of like on the income statement. Possibly we want to break down our income statement on a year by year basis. Now note, when you're looking at your income statement, there's a couple of things you might, the ways that you might put this together. You might actually try to compile this information just from your paycheck stubs and your W-2s and then your bills and so on to get a good idea of it. That's one way, that's fine. Or you might be using software or something like that and basically going from the bank statement in essence, oftentimes being more on a cash basis method. Just realize when you're looking at the income side of things, it gets a little tricky because the income often has these withholdings involved in it. So if you're on a cash basis system and you have like a QuickBooks or other kind of software that's basically constructing your financial statements and pulling this stuff in from the bank feeds, from the bank, from the financial institution or if you are building your financial statements from the bank statement and your W-2 employee, the amount that hits your bank is actually the net check and not the gross check. Now remember when you talk to the financial institution, they often want the gross amount and that's often what you would like to give the financial institution because you want to give them the highest amount because that will give them the most likely result in the highest loan kind of value on their end. On our end, when we get down to our net income, we want to take into consideration whether or not we're calculating the income statement with the gross or net and then just be aware of that, be conscious of it as we do the financial statements. So that's one thing we'll take into consideration. So we've got our income, we're gonna assume this is like two W-2 incomes possibly by a husband and wife, married or possibly just someone working like two jobs kind of thing on the gross pay and then the net pay, we're gonna assume the difference between the two being the taxes that were withheld but there also could be benefits that were withheld too. And then we've got the expenses and we just listed out a couple expenses so we can practice putting together our income statement here. So let's just construct an income statement. We're gonna do it kind of more in a cash basis, cash flow kind of income statement. So this is gonna be a monthly income statement. And this I'm gonna make basically a cash flow, cash flow and then we'll make this black and white for the headers up top. Let's make this home tab. We're gonna go to the font group. We'll make it black and white. And then we're gonna go with our income. Income is the top of the income statement. And we've got the two incomes. I'm gonna use two columns here because I'm gonna kind of do the income on a gross basis and then on a net basis. Those are the two ways you might see the income statement. They're both okay as long as you know that how you're reporting the income. So I'm gonna use two different columns and I'm gonna assume the first one, we've got the gross amount. It's not really gross. It's just, you know, that's the top line after that it's not like it's icky. That's not like a slimy one or anything. And then this one, and let's put the amounts here. This is gonna be equal to the gross income, which we're gonna say the 47,000. That's for a year. I'm gonna divide it by 12 to get the monthly amount. Now you might get the gross income from your paycheck stub, for example. You might get it from your W-2. But if you look at your W-2, note that there's three income lines on it. There's box one, there's box three and box five. Box one really only has the income that is related or subject to federal income taxes. So that might even be reduced by something like a 401k plan or something like that. Box, the Medicare one, box three is for Social Security, box five is the Medicare. That's probably actually the highest one, which is closest to your gross income. And then your net income is gonna be over here, which if you're getting the information from like, I'm gonna take this divided by 12, if you're getting the information from like the bank, that's what actually hit the bank. So if you're constructing your financial statements from the bank, you're more likely to be using the net income over here. And you just wanna be aware of that because it's already net of at least taxes. And those taxes are your taxes, right? They're actually expenses to you. They're taxes that you paid that were taken from you by your employer. So we're gonna say down here, and then we're gonna say that we have our other income, the gross side of things, the gross income, so gross. Income is so gross. Why is income, income is good. Income is, that's the grossest income. So we're gonna go down here and then this one's gonna be the net income. And then this is gonna be equal to the net income on this one divided by 12. So there's our grossed net income on our income statement. I'm gonna go ahead and sum those up. Let's sum it up. And we're gonna call this total income. So we'll do with the gross side first, total gross income. Total gross. And then we're gonna say this is the total, the sum of the net income. We'll put some underlines here. Why not? Some home tabs, some font groups, some underline. Let's do some indentation. Selecting these items, we're gonna go to the home tab alignment and indent it. And then we'll indent this one again, double indentation. And then we'll put our expenses in place. Expenses, brackets. And I'm just gonna list them out here. These are things that you could, of course, get from their bills themselves or you might go to your bank statement and look at your expenses. If you have software like a QuickBooks or other types of softwares that can compile this, they might be drawing this from basically what clears your bank and or credit card funds, for example. And so we can go down here. And software's getting better and better at being able to construct this kind of stuff from banking and finance stuff. So it might be worth doing. I'm just gonna copy this down. And so I went too far. So you've gone too far, but that's okay. I did it on purpose because I can just delete those. Calm down, it's okay. You've gone too far. And then we're gonna say the utilities is gonna be over here is 120. I'm just gonna copy that down. So we're gonna pull this utilities. And again, you could get this from your bank statement, from the actual statements themselves, compiling them, but you might want software to kind of construct this stuff. It's useful. And so we'll copy that down. I'm gonna put some underlying down here. Notice I put the car loan payment as kind of an expense. So that's a little bit tricky because really, if you're on an accrual basis, it would be a decrease to the principal rather than an expense and so on. But we're gonna assume it's kind of like a cash flow item that we're spending each time. And we're trying to think about this on a kind of cash flow basis to think about how much added cash flow we would have if we were to make a home purchase and so on. So let's go over here and say we got the font group and underline. And then this is gonna be the total expenses. Total expenses equals the S to the U to the M otherwise known as the sum. And then we're gonna say this is gonna be, this will be the net income, income or cash flow. I'm gonna say before taxes, even though the taxes are included in one of them, but not the other and the net, but not the other. But I'm gonna say before taxes and this is gonna be equal to the income minus the expenses. That's how these things work. This will be the same income minus the expenses. And then the key point, I just wanna point out down here is you've got your taxes, which I'm assuming is the difference between the gross and net income. You might have benefits as well, but I'm assuming it's just the taxes. And so I'm gonna say the difference between these two is the taxes. So I'm gonna come out to the same number except that the taxes are kind of included up here, which would be if you're using it on the cash basis, this is what actually hit the bank because they took it out on the employer side of things, your taxes. Okay, let's put an underline here. Underline here. We'll put an underline there and an underline there and I'll put a zero there so it has something to underline. And that's gonna give, let's do some indentations. You're getting ahead of yourself. Stop getting ahead of yourself. But I'm so slow. I hate waiting for my, I hate waiting for my, you gotta wait, you gotta wait for yourself. This, I'm gonna indent this one. I'm gonna indent home tab, indent this one. And then this is gonna be the net income or cash flow after taxes. And this is gonna be equal to the total income minus this number here. Well, hold this. It's gonna be after tax, no, hold on a second. It's gonna be this number minus the taxes. That's what it's gonna be. That's what it's gonna be. And then this one is gonna be equal to this minus that. So we get to the same bottom line number. Let's put the double underline to indicate the bottom line. That's the bottom line which has two underlines. That's how you know. Okay, so then let's put some borders around it, blue and border it, blue and border. We're gonna go to the border blue. Border blue. Let's do a spell checking. Did you mess up any spelling? No, cause I just copied it over. I didn't even type any spelling. I can't mess it up cause unless the data over here was messed up. So that is it. So then down, so notice again, we get to the bottom line. The main thing I just wanna point out what one is that when you put together the income statements, you might be putting it together gross or net. And then you wanna think about your own kind of net income. We wanna think about how much we can afford as a separate kind of thing to what the bank is doing. We might use this, the bank might ask for something like this in which case we're gonna give them the information. But the bank might be doing something different. And again, we're not really talking to the bank in order to do our personal budgeting. We're not, they're not a financial planner for us. They're trying to determine whether or not they're gonna give us a loan. And we're trying to get as much access to as much loan as we can. Not that we're gonna take as much loan as they'll give us, but because we want the ability to take as much loan as we need. So, and so if I unhide my cells over here from B to L, bull and unhide, then you could see that their number here on the affordable amount might be substantially different. And I'm just, obviously, we just kind of made these numbers up. They might be whatever heuristic that they're using is based on whatever standards, complete averages, you know, it's just kind of, they're trying to use just a number that covers everyone. It might be completely different to what we actually come out with because our actual finances, our actual circumstances might be quite different. We might have a completely different lifestyle than other people on their normal, that their normal heuristic, their average numbers that they're using are using. So that, and that's fine. So we wanna basically, again, talk to the bank and use whatever format that they're gonna use so that we can make the estimate of how much loan could be available to us. Again, not so that we could actually take out that much loan possibly, but so that we can try to get as much access to financing as we can have. And then we're gonna do our own financing, our own budgeting to determine how much loan we actually want to take out, how much loan we can actually afford. And for that, we would like to have as accurate as possible actual financial statements, balance sheet and income statement. Next time we'll take this information and we'll build our tables from it, our amortization tables and our grouping of that table by year. And that, of course, can help us with the budgeting from this point. So once we have the income statement before the loan kind of process takes into consideration, then, of course, we wanna think about, well, what would happen after or if there was a change, what's gonna be the difference from the standard now to the change. And those things will include things like, what's gonna be the cost of the home and so on, it taking into the interest, tax implications and that kind of stuff.