 Personal finance, Excel, practice, problem, comprehensive problem, part number two, creation of the balance sheet. 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 in a prior presentation. We started to build this basically from a blank sheet so you can go back there and start from there if you so choose. If you do have access, there's three tabs down below. We got 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 the prior presentation, we use the information on the left to build our amortization table for the home, breaking it out year by year. Now we're gonna be working on the balance sheet and then we'll focus on the income statement and then we'll look at an estimated property insurance that might be needed. So I'm gonna go back to the left. The second tab here is the practice tab. So we've got some pre-formatted cells so that you can build this if you so choose with less Excel formatting. And the third tab, we basically started this from scratch and building this out from scratch. So what I'm gonna do is first hide some cells. I'm gonna go to the right and we're just gonna start building our balance sheet over here in column S. I'm gonna make a skinny S first and then hide some cells so we can see our data. I'm gonna put my cursor on the skinny end to make it the same skinny size. We're gonna go to the home tab, clipboard and hit the format painter and then we're gonna skinnerize the S, skinnerized it. And then I'm gonna go from R, put my cursor on R and I'm gonna drag all the way over because I'm gonna hide this stuff. So C to R, C, R, car, car, right click and it's gonna be hide. So now I'm gonna just be working here on our balance sheet. So we've got the information on the left. So quick note on where you might get this information. The balance sheets, you can get a couple different ways. You can just compile it from your actual statements at the end of the period or you could use a counting software which would help you to enter transactions to get to that ending balance. We might dive more into the different softwares you can use in a future presentation but just to get a quick look at them, just note there's kind of two kinds of software. One kind of software would be pulling in the ending balances such as like a personal finance or personal capital that has a software like this where you can actually connect to your financial institutions bank accounts and your credit cards and so on and it can actually pull in the ending balances in there and give you some more kind of analysis and so on but it'll basically give you a balance sheet for things that are financial account related. So in other words, you can't do that for like a car or something because that's not gonna be on the financial statement you would have to add that separately if you want that in there but most stuff that has a financial statement component you can compile that that would be similar to just looking through your bank statements at the end of the month and putting down the ending balances. You might have quick in I've not really worked with quick in since they were not, they used to be owned by Intuit the owner of QuickBooks but it used to be good. I'm not sure exactly I haven't worked for them for a while but I think they have a similar kind of component and then QuickBooks is something that's accounting software where you actually enter the transactions. You can connect to the bank with them as well and then you enter the actual transaction which is actually more helpful for the income statement which we'll talk about next time and then you're gonna verify the ending balance. So it's not quite as easy to compile the balance sheet but it's a system that would be good to look at the performance. And then you also have Wave which I think is a free like accounting system similar to QuickBooks so you can kind of check that out if you're looking for a budgetary option that can kind of connect to the bank and you might be able to automate things so do your research on it but those are just some examples. So we're gonna imagine this data we got from something like the ending balances and just compile this. You can get it like I say from the accounting software or we might get it which would compile it for us in the terms of a balance sheet if we use that or we can then use our financial statements or we can imagine using some other software that would compile the ending balances. So let's build our balance sheet here. We're gonna say this is gonna be the balance sheet and we'll probably dive into those softwares in a future presentation as well. So I'm gonna make this black and white up top. We're gonna go up top and say home tab font group make it black and white for our headers like we normally do. Assets are gonna be first up and I'm gonna break this out into current and non-current assets. So current assets those are the more liquid assets. So I'm just gonna pull my data. I'm gonna drag this T column out a bit so it's we're fattening up the T column, fatten it up. The T column needs to be fattened up a bit. It's feed it some cereal or something, some sugary cereal. I don't know, I don't know what I'm talking about. So we're gonna say this is gonna be equal to the account checking account. I'm just gonna pull in the data. That's a current asset which of course we can get from the bank statement. We got the savings account we can get from the bank statement. If we needed to do it that way we've got the emergency fund which if it were in a bank would be in the bank statement or if it's under your mattress then you can count it. And then we're gonna say that the IRA is something that you can't really pull out till retirement. So I'm gonna say I'm not gonna put that in current assets cause I can't really get it right now unless I wanted to be penalized. I'd have to have a penalty. So I'm gonna say this is gonna be the total current assets. And this is spelled wrong. Currenta, that's like my Spanish form. It's got a A on the end of it cause it's, I don't know. We're gonna say this is the sum of these items. Just do the thing, just do the thing. So this is gonna be an underline. Let's go to the home tab, thought group underline. Let's do some indentation cause we got the colons here. So I'm gonna select these items, gonna indent it. We're gonna select the total one down here and indent it again. And then I'm gonna put the other assets. I'm gonna put the other assets up top because are these, instead of putting them underneath like property, plants and equipment because I wanna group the items that you might get from the financial institutions themselves up top. I'm gonna put this under other assets. And the only thing we have in there is this IRA. So you might have that in something like an E-Trade or a Vanguard or something like that or your bank. But it's gonna be a long-term type of thing and you can't access it as easily. So I'm gonna put it down here. This is another thing that you could get of course from the bank statements or from software that pulls the ending balances. So I'm gonna put this in the outer column cause it's the only one I have. So I'm just gonna put that right into the outer column. This is gonna equal that 40,000. 40,000, let's do an indent there, alignment indent. Now this one also has an added kind of issue that it could go up and down in value when the stocks go up and down. And when you record the increase and decreases that you have not yet realized, that's a kind of a bookkeeping issue that we might dive into a little bit later when we get into just, we might just do some accounting on it. So then we've got the property, plant and equipment that I'll call it. So this is your property, we're gonna say property, plant and equipment. And so that's kind of like a business category or term, but this is your fixed asset type of stuff. So for example, you've got the home in here. So we're gonna put the home in here. And when I put the home in, I'm gonna put it in at cost. So I'm gonna say the home is at cost. I gotta unhide some cells to do it. Well, let's just, let's unhide some cells to do that real quick. I'm gonna unhide from B. Do I have the cost down here? Maybe I do. It's down here. You don't have to unhide anything. Hold your horses, hold your horses. Why? Because the horse looks like it needs a hug. You've been, it's been hard on the horse. The horse needs to be held for a second. So there's the home. And then we're gonna say the car. We got car one and car two. So I think those are down here. Where did I put the cars? Card one, car two, they're right there. I'm gonna put my cursor here and drag that down with the fill handle. So there's car one, car two. And this is the 14 and the 21. Now the reason these are a little bit more tricky because you can't get this stuff from the financial institution. So if I was to connect to the financial institutions with something like a personal capital software, they can't get the information for my home value. But they can estimate the home value using an estimator tool. So some of them have a nice tool to do that. And obviously my car, again, you don't know what it is. So traditionally from an accounting standpoint, you would put it on there at the cost and then you would allocate the, you would depreciate it. But a home, hopefully it's gonna go up in value. So home's a little bit tricky. The car we would expect to go down in value. So we would put it on there on the books at cost and then possibly depreciate it. Or you can try to do it like an appraisal from time to time. But you gotta be careful with that when you do the appraisals on these types of things. Cause for example, with the home, if you think it went up in value and you record it, you didn't really realize that increase in the value cause you didn't sell it. And therefore it's just a gain that's unrealized gains. So you gotta be kind of careful on that. So these three, we're gonna start and imagine we put them on there by cost, what we paid for them. And then we might adjust them periodically, possibly with the use of some kind of depreciation system or possibly doing a periodic kind of appraisal. And when you make the adjustment on these things, we have the same kind of problem, which is if I had a gain, where does the other side go? If the home went up in value, do I record it on the income statement? Do I record it in equity in terms of that gain in the value because I haven't yet realized it. So we might talk about that more later, but just keep that in mind. I'm gonna do some indentation here, alignment indent and I'm gonna call this total property, plant and equipment and put that in the outer column equals the SUM. Otherwise, notice the sum, our favorite form, ULA, our favorite form ULA, alignment, indent, indent, double indent. Let's make the T column a bit larger. Let's put it a little underline right here because that'll make it look better. Home tab font group underline and there's the property, plants and equipment. So that's gonna give us our total assets then. Finally, total assets, sum it up the outer column equals the SUM and we'll sum that up. There we have it. And so something looks a little bit different than I thought here. Now I think that's right. I think that's right. So I'm gonna put an underline here, font group and underline and let's put a double underline here, font group and double underline. We can put the liabilities in like the equity or the net assets underneath or we can put them on the right. I'm gonna put them on the right here to emphasize the balance sheet of the balance sheet. I'm gonna make a skinny W to do it. I'm just gonna let's take the skinny S and then home tab and format paint it and put that on the W, skin arising it. And so this is gonna be the liability side. I'm gonna put down here liability, that's not an I, liability, liabilities. And then we've got current liabilities, colon. And that's gonna be, we got the student loans. There's nothing in it, but I'll add it anyways because you might have student loans. For example, let's make X a little bit wider here. Put my curse between X and Y, between the X and Y chromosomes or something. And we're gonna say this is gonna be the credit card balance and then this is gonna be the car loans. Pulling up the totals, the totals are gonna be equal to the zero. I'm gonna just copy that down with the fill handle. Now note that the car loan might be like a long term. I'm gonna imagine it's less than a year that it's gonna be due. So I'm gonna keep it up here in the current liabilities section. Gonna select those three and let's do an indentation and then give us the total current liabilities. And then tab tab, put that in the outer column equals the S to the U to the M. Some brackets of these three enter. Let's put an underline under the 7,000. Home tab font group underline. Put a line underneath, call it an underline. Home tab, appropriately named. Alignment, double indent, double time. So there we have that. And then we've got the long term liability. Long term liabilities brackets. And we're gonna have the home loan now, which I don't think is over here. I think we might have to unhide some cells. Did I have that over here? Do I have the home loan? No, let's unhide some cells so we can pick that up. So I'm gonna put my cursor on B and I'm gonna drag over the T, BT. And then right click and then we're gonna unhide. We're gonna go all the way to the right to pick this up. And here's the home loan. We did that calculation right there. The two, and let's imagine that a year has now passed. So the home loan is now gonna be one year later, which will be, I'm gonna pull that from this schedule. And that's why it's kind of useful to have this amortization schedule. Now again, if you were to pull this from a financial institution and your loan was from the financial institution, you might get the statement, which would have your Indian loan balance on it. Or if you were using something like personal financial, it might then have your Indian loan balance on it that it would then be including. But if you're compiling it, you also could use a table like this. And this would also help you this table if you were doing the same process, creating a balance sheet and an income statement and projecting forward doing a budget type of perspective. So we're gonna imagine a year has passed. We've got the 241, 346, which you could get from your loan statement. You could get from your personal capital kind of calculation as well. So I'm gonna put that down here. This is gonna be, I'm gonna call it the home loan. And I'm gonna put that in the outer column because we have no other long-term liabilities. That's gonna be the 241, 346. Also note that the student loan, the credit card, and the car loans are also probably with financial institutions that you oftentimes can link with something like a software like this. So you can pull that information automatically in if you were to use a software like this on that Indian balance stuff. If you were to use QuickBooks, then you would verify it to the Indian balance because you would do the actual bookkeeping. So you can get the income statement side of things. And so that's some tools that we might look into more later too. We have some courses on those if you wanna check them out already. Home tab, alignment, and indent here. And so then let's see that's gonna be, what else do we have? What else do we have? That's gonna be the total liabilities. We're gonna say this is the total liabilities and we'll sum them up in the outer column equals the S to the U to the M. Brackets, summit, poor father, we'll put an underline here. Home tab, font group, and underline. Now oftentimes you'll have the bottom line of your personal balance sheet just called the net assets, which is kind of like the, which is similar to the equity term for a balance sheet. I still like to see it in balance at the end of the day. So I'll calculate my net assets up here, net assets. So if we were to construct it in this format, then we can get to our net assets by saying this is gonna be equal to my total assets minus my total liabilities. That would give me the net assets. And that's often how a software like this would have to compile it because they're just looking at the ending balance. If you looked at software like this, QuickBooks and you actually did the full bookkeeping and verified the ending balance, then you would see it in terms of an accounting equation because you would actually be entering the data. And so then you'd have a balancing kind of calculation over here and you would say then this would be total assets and liabilities. Bottom line would equal the sum of these two which would match out. This one has to be in balance, home tab, font group, we're gonna say underline. Clearly this is in balance because we forced it to be in balance by taking net assets as this minus this item up top. If you put it into accounting system, then the double accounting system would check itself. That would be the point of the double entry accounting system. So you wouldn't just kind of force in the net assets. But that's the general idea. We might dive into some more of those softwares at a later time. And it's fairly, it looks complex, but it's fairly easy to compile a balance sheet. So you can compile this stuff. You might also be thinking, well, there's other stuff that's not on there like we might have other household goods. I might go into my home and count like the television and all the other household goods which could add up to quite a bit of stuff that we put on there. But you may or may not want to put them onto a normal balance sheet unless you're trying to look like good as good as possible so that you can get a loan or something like that. Or obviously you want to list that stuff out as well in the event that you have an insurance situation and you got to see what the losses were because you can't really verify the television as easily in terms of what the current price is. You can put them on there at cost, but it's not a very liquid asset and you can't really, you can't tie it out to other things like the personal capital as easily because it's not coming from a financial institution. It will generally go down in value. So therefore it's going to be difficult to kind of track it on the real time. So you can kind of decide if you want to put other personal assets on the balance sheet depending on what your needs and how you're going to basically update those items from period to period. But to get this flat financial stuff on there, it could be fairly simple and you have different tools to help you to do that. Okay, so then let's go and format this. I'm going to make this whole thing over here. Let's make that black and white, home tab, font group, black and white. We could then merge these like this. So the balance sheets in the middle, but I don't like doing that because then it like makes that one wide sell when none of the other cells are like that. So that's, I'd rather right click on it and go to the format cells and then go to the alignment and horizontal. I'm going to center it across, center it across mucho mejor. That's much better. And then I'm going to say, let's select these ones. Let's do the blue border thing. And then I'm holding down control to have new two non adjacent items. You can do them one at a time too that I can blue border at the same time. So those two, we're going to go then font group, make it blue. If you don't have that blue, it's down here. It's in right there. There's the blue we're using and borders. So there we have the border blue and let's check the spelling on it. If we could spell check it. Liabilities, I never spell it right. How many times have you spelled that word? It's just ridiculous. So does everything else look pretty good? What do you guys think? Is it formatted at least up to par? It's not pissing anyone off. No one's raging. No one's getting upset at an obvious error that I have in some formatting capacity. Okay, that's good. Then next time we'll go into the income statement.