 Hello, in this lecture, we're going to continue on our comprehensive problem. We are now going to make the financial statements, including the balance sheet, the income statement and the statement of owner's equity. We will be doing so using the adjusted trial balance here. So what we have done up until this point is that we have done the journal entries. We've recorded the journal entries for the month of May. We've recorded those to the general ledger over here. The general ledger was then used to create the trial balance of this being the unadjusted trial balance. We then took those unadjusted trial balances. We did our adjusting entries on this tab. So as of the end of the time period, we adjusted those accounts that need adjustments for the adjusting process to be on an accrual basis from the unadjusted trial balance to the adjusted trial balance. Now we're going to take this adjusted trial balance and create financial statements from it. We will then take that unadjusted trial balance and we will make financial statements from it. We are now on the financial statement tab. We have our unadjusted trial balance here. We can see that these numbers are coming from the adjusted trial balance up here in the formula bar. So we're just using these same numbers and we're going to make the financial statements from that adjusted trial balance. Now the first thing we're going to want to note is that all we are doing is taking the form of balance from one form and basically putting it into another form. We want to realize that since it is in balance in this form that it must work if we put it incorrectly in the other form. We know that it has to work so therefore when we approach this we're going to approach it from the idea that it has to work and therefore if it's not in balance then we've just got to figure out what we did wrong. It's not that the thing doesn't work. So for example we know that we are in balance over here because the debits minus the credits equals zero. That's how we know we're in balance. That's how the double entry accounting system is being represented in the trial balance. We see it represented up here the way we're going to represent it on the financial statements in that assets equal liabilities plus owner's equity. That is the balance sheet. We can see that we are in balance here and here. The assets are of course the green accounts, liabilities, orange and equity is all the blue. And so what we're going to do is put this information in the form of this formula in a more of course detailed area in the form of financial statements. I'm actually going to start off with the balance sheet because the balance sheet to me is really everything and so I want to see the accounting equation first and then drill down more on the equity section which we'll deal with this account here. You'll see that we already have some totals here. The ending numbers that we're going to come up to are total assets and total liabilities and equity. These two numbers will have to be the same. First thing I want to do is point out that the title of the balance sheet will go here and we're just going to put May 31st in this case and I'm just going to put the year being 2-0-X-1 and the point there being that there's only one date as of the balance sheet because we're as of a point in time. So even though we've been working for the month, May 1st through May 31st, when we're looking at the end here, in terms of the balance sheet, we only care about the end date because there is no time frame. That's where we are as of a point in time. That's what the balance sheet is. When we come down to the income statement, that will be different. We'll have a time range beginning to the end. So we have our assets, our liabilities, and our equity will be down here. We'll add the equity accounts down here. And so what we're going to do is put this information over here from the trial balance into the financial statements in a set format. First I'm going to just start off with the assets and we'll just go down from there. So when we think about assets first, within the asset category, we usually break it out into current assets, being our first asset class. And those are going to be assets that are more liquid, meaning they're more convertible to cash. And we're going to start off and they're usually in order on the trial balance in that fashion as well. So cash is a current asset, receivable is a current asset, supplies is a current asset, prepaid rents a current asset, prepaid insurance. Office equipment is not. So office equipment is going to be a longer lived assets, going to be larger pieces of equipment that will have a longer life. Note that we put current assets. We put a colon after it. That's going to mean that there's going to be a subcategory of current assets under this section. I'm going to copy from cash down to prepaid insurance. Right click. I'm just going to copy that. Once again, you could type it in here. I'm going to copy and paste it. Also note that I've made the format of the financial statements kind of funny that I kept with the colors here, hoping that that's somewhat helpful. If you change the color scheme, that's fine as well. But there's a black background so that the colors stand out more. So I'm going to right click on it. I'm going to paste it one, two, three. Again, if you paste the first one, it's going to change the color back form. And again, if you want to change it, that's fine. But if you want it to stay green for the assets and whatnot, then if you paste values, that will happen. Now, the other thing we usually do, and I'm going to look at some formatting, you have to do the formatting in this particular worksheet, but oftentimes we will have this indicated by an indentation as well. So instead of us going in here and double clicking and don't do this and hitting a space bar to show kind of an indentation, we will then just highlight these cells and indicate the indentation if we so choose by going to the home tab, Align them into group. And if you hit this increase indent, it'll indent within the cell. So now we're saying it's current assets. These are all current assets shown by the indentation. And then we're going to put the number of the current assets in the inner column here. Now, these inner column, although there's two of them, does not mean debit and credit. These are not debits and credits. There are no debits and credits on the financial statements. The whole point of the financial statements is that we're going to show them to people that don't need to know debits and credits. So this just means that we're going to group numbers on the inside, and then we're going to sum them out on the outside. So the assets, we're going to group them on the inside. I'm going to do that with a formula. I'm going to say equals. And I'm just going to go boom, there's the 34 and enter. I'm going to keep doing that. Equals, there's the 5, 8. Could we type them in there? Yes. But again, formulas would be far useful because formulas will help you to see if there's an error. Equals, the supplies equals the prepaid rent equals the insurance. Now, if you're wondering, is there a formula? Could we have done that an easier way in Excel? Yes, you could have done that an easier way. Delete this and we could highlight that first one. And auto fill or copy and paste because these formulas are proportional or relational to each other. So if we put our cursor on this little bar here, we use what's called the auto fill. So if you left click on that and drag down, auto drives it down and Dr. Phil does the calculation once you let go. And what it does is it assumes that we want to bring that formula down. And of course we do because that's typically what happens in a normal Excel worksheet. So that's the one major thing that's kind of weird in Excel that once you get used to is extremely helpful. All right. So now we're going to have the total current assets. They're going to say total current assets. Enter, I'm going to go off the cell and then I'm going to go back on it. I'm going to indent this one two times. So I'm going to go up to the Home tab, Alignment Group, and we're going to increase indent one and two times. So we get current assets indicated by a colon, indented, then double indent on total, and the total will go out to the outer column. So anytime we only add things up in one or the other column, this case we're going to add up these numbers in the outer column. We're going to do that with our most used function. You have to know the sum function. It's going to be the sum function. It's going to be equals SUM. You could just double click on it down here to get the sum function like so. And then I usually just highlight it's better to point and click for me, the cell range. So from here, highlight down to here. And then we could close it off. You don't have to. Control-Shift to close it. That's going to be the sum function. So we're going to sum the range of, in this case, I5 colon I9, range of I5 to I9. Now we hit Enter and it sums that up for us. And of course all we did if we had the calculator was four, three, two, five, zero, plus five, eight, zero, zero, plus seven, five, zero, plus one, six, oh, oh, plus one, two, oh, oh, and that's five to two, six. So that's the sum function. Got to know the sum function. You can also have it here. You can put this sum function up in your quick task bar, but I almost prefer just typing it in there. I think that might actually just be the fastest way to go. We're going to have another subcategory now because now, and we might want to say that we found a home for these. So I could highlight these and say, okay, I'm going to say I found a home for those. Once we find a home for all of these, then we should be in balance if we did it correctly. All right, so the next category is going to be called property, plant, and equipment. Colon, that's another category. And then within property, plant, and equipment, we're going to have, oops, this one, we haven't found a home for it. Right click, I'm going to unfind a home for that. We need to find a home for the office equipment and the accumulated depreciation now. That's going to be the second two assets. The next two assets, the only two left in the asset green area. So I'm going to copy this one. We'll also just copy and both highlight both of them. Right click, copy, then we'll come over here. Right click and paste one, two, three. So we've got the office equipment and the accumulated depreciation. I'm going to do the same thing. I'm going to indent those by having those highlighted. We're going to go to the home tab. We're going to go to the alignment group. We're going to increase the indentation. So we're saying this is another group of assets called property plan equipment, longer lived assets, and we have the office equipment, the accumulated depreciation. Now note that the accumulated depreciation is a negative number here, it's a credit. So what that means is it's really the credit half of these two accounts. So we're going to subcategorize those and this is going to be a decrease to the assets because of that. So over here, we have to indicate that I'm going to indicate it with words. That's the way I usually do it. So in my financial statement would look like this, we're going to say less to indicate it's a subtraction problem. And then I'm going to re-viate accumulated depreciation just so it's fitting here. So there we have accumulated depreciation. And then the numbers, I'm going to say this equals the 14-5. And here, I don't want to have that negative number even though it could be a negative representing the fact that it's a subtraction problem, not a credit in this case, but generally I represent it's a subtraction problem with the word less. So if it doesn't say less, then you add them up. If it says less, then you do a subtraction problem. Some financial statements will be slightly different. Some statements would assume that you know that some statements might put a negative in there. But one way to do that is to put the less in there when it is a subtraction problem in order to take away the credit then in Excel, instead of selecting equals, I'm going to put negative and then point to the cell and enter. So now what it did is it took that cell and it flipped the sign. So another way to look at it if I delete that and we just said equals this number and enter. And then I want to say, I want to get rid of that credit. I want to make that a positive number. Well, how can we make something a positive number? I could multiply it times negative one because anything times negative one will flip the sign. And then I'm gonna represent it as a subtraction problem by saying this minus this. Now, of course, this form, the minus negative one is kind of long. We don't need to do that in Excel. We could say, well, maybe I just get rid of this minus negative one. And since there's only one number, I want you to take what's in cell F11 and make it and flip the sign. So if I just put a negative, it'll flip the sign like so. So that flips the sign. And if you take that thought one step further, then instead of hitting equals, if we select negative, then Excel still thinks we're doing a formula, but it's gonna basically just think equals negative. And then point to the cell, 660 negative will now be 660 positive. So we're gonna have to flip the sign on all of these credit balances because we don't want credits over here on the financial statements. Okay, so then we're gonna have the total, total property, plant, and equipment. And I know it's taken over a little bit there, but I'm gonna do this with a formula. Once again, we're gonna say the 14-5 minus. So make sure you get the minus. It's gotta go the right way. It's gotta be minus the 660. And then we will come up with this 13840, which if I highlight these two, 13840, that's correct. We found a home for these two now. I'm gonna click on that and highlight those. Now we have accounted for all assets. If I highlight all the green numbers here, it adds up to 66440. And now I'm gonna see if that's the case over here in total assets. I'm gonna go to the total assets. I'm gonna add up just one column. We only add up one column at a time. This being the outer column, being the total current assets, plus the total property, plant, and equipment. I'm gonna do this with the sum function, even though there's blank cells here, like this equals S-U-M. And I'm just gonna highlight the blank cells as well as these cells. I'm gonna highlight all the way down and enter. So all we did, of course, was add those two numbers up. So we're just adding up the 52600 plus 13840 to 66440. And that is the same as if we highlighted these here. If it's not the same, most people mess up by not having this going the correct way, since it's a contra asset. All right, so then we're gonna go over here to the liabilities. Liabilities are in this orange area, and we have three of them. We've got accounts payable, we've got savage payable, we've got unearned fee. Now, on liabilities, we usually have this convention again as well. We have the current liabilities, and these are gonna be liabilities that we're gonna have to pay within a year. Now, in this case, they all happen to be current. So we're still gonna put current liabilities up here, but we're gonna do something a little bit weird. We're just gonna call them total liabilities in the subtotal because we have no other liabilities. If we had long-term liabilities, liabilities that last over a year, then we would have total current liabilities, and then we would add up the long-term liabilities. So let me show you what this looks like. If we have just current liabilities, such as we do here, we're gonna copy these. We're gonna go over here to K5, right-click, paste it 123. So instead of pasting it this way, we're gonna paste it 123. And then I'm gonna indent those. So I'm on the cell, I'm gonna go to the home tab, I'm gonna go to paste it 123. And then I'm gonna indent those. So I'm on the cell, I'm gonna go to the home tab, I'm gonna go to the alignment group. Gonna increase the indentation like so. And then we're gonna go into the inner column of these two. The inner column does not mean debit or credit. The inner column just means that we're gonna subcategorize indicated by the title, the colon, the indentation, then putting the numbers on the inside. Once again, I wanna flip the sign. I wanna make that a positive number. I do not wanna have negative numbers on my financial statements. One way to do that is to just, instead of hitting equals, just hit negative. And that will take whatever number I put in there and flip the sign. So negative of a negative 780 is a positive 780. So we're gonna do that same thing down here. I'm gonna say negative of the 200. Negative of a negative 200 is a positive 200. And negative of the one five negative of negative is gonna be a positive one five. So now we found a home for these now. I'm just gonna keep with the green. We just found a home for all of them. And then we're gonna subcategorize those outside. But instead of calling them total current liabilities, I'm just gonna call them total liabilities. And enter. And then I'm gonna go back on that cell and indent it one more time. So I'm in the home tab, alignment, indent one more time. And I'm gonna put that on the outside. So we're gonna put this on the outside at this time. Now, why is it total liability than that total current liabilities? Because the total liabilities, there are no other liabilities. So we're gonna do this with the sum function. So I'm gonna say equals SUM shift nine. And then highlight this area. So we've got L5 colon L7 and enter. And if we wanna double check that, we can highlight these and say this should be summed up. And if we just highlight them in the taskbar, it sums it up for us. We can also sum them up over here. And the nine five sums up over here as well. We know that this now ties out to that, even though these are credits, these are positive numbers. We're not dealing with debits and credits in this section. Now, the next section that we're gonna have is gonna be called owner's equity. And so we're gonna be in owner's equity. Now, owner's equity is gonna be one of the more confusing sections for this reason. We see that this whole thing is blue down here, but it's a slightly different blue we have over here. And there's only gonna be one owner's equity type account on the balance sheet. That type account will be called owner capital. So I'm gonna take the capital account and copy that. That's gonna be the amount on the balance sheet here. And note though, I'm gonna do this incorrectly and then show you why, that if I take the balance from this owner's equity section, I'm gonna flip the sign again by saying negative of this 42 300. So enter and that flips the sign to a positive. So now we've got liabilities and equity. And if we take total liabilities and equity down here, I'm gonna use the sum function to add those two up equals SUM colon and shift and highlight from the nine five to the 42 three and enter. And then again, if I highlight these cells, I get 51 eight, 51 eight. So that added up correctly. And if I highlight these, I can see that I found all those 51 eight. The problem is though, that my total assets do not match my total liabilities and my owner's equity. So the question is why is that? If this is the only account here, how is it that we are not in balance? And the reason for that is that all these are blue because basically all this is part of owner's equity. So we need to crunch all this into basically one number. If we crunch all this into one number, that should make it work because what we have here are assets here and up to 66, 440 liabilities and owner's equity, 66, 440. So it has to work, but we're missing in the balance sheet, all of this. And why is all of this gonna be included as part of equity? Because at the end of the day, it is part of equity. This is basically the beginning equity. This is the story of what has happened in the last month. So at the point in time, as of this point in time, we need to squish all this into one number. This account, although it doesn't say so, is really owner's equity as of the beginning plus any investments, not including any draws, not including any income or expenses. But over here, although it doesn't say the date on the capital account, it does say the date up here. This capital account means as of May 31st. Therefore, we're gonna have to sum up the entire blue section over here, and then it should work. So let's try that. I'm gonna delete this. Gonna put negative SUM and sum up from the 42.3 down to the miscellaneous expense of the 9.50. And when we hit Enter, see if that puts us in balance and it does. So that's what we wanna show you. So now we found a home for all of this, right? If I highlight all of this, we found a home. It's all going the right way. Why? How do we know? Because we're in balance. But we're in balance and went the right way. But we wanna have more about this. We wanna know more about that number right there. We wanna know how we did last year. Note what this, or last month in this case, note what this number means. Assets 66440 minus liabilities 95 gives us the book value of 56940. So this could be thought of as kind of the amount that we could walk away from if we sold the assets, got cash, then paid off the liabilities and then walked away from it. Of course, it wouldn't work perfectly that way in a perfect world, but in a perfect world, that's how it would work. So that's kind of the book value of the company. We wanna know how we did basically last year. So that's why these accounts are gonna be yellow because we're gonna see them in multiple places on the financial statements. We're gonna see this on another area in the financial statements that will be coming from the statement of owner's equity. So I wanna tell this story again. I wanna find a home for all of these accounts again in the form of timing. And our timing statements are called the income statement and the statement of owner's equity. Once we do those timing statements, we will then come up with the same end result number being this 56940. And let's see how that will work. Now, our timing statements are gonna be called not just the end date. The income statement will be for the month ended, in this case of May 31st. And it's a two X one. So it's for the month ended in this case, meaning it has a beginning and an end. If we talk about revenue, if I ask you how much money you make, you can't answer that by saying, just pull a number out without assuming, what do you mean a month? Do you mean a year, an hour? You need a time, friend. So in this case, it's gonna be the month. So we're gonna have revenue and expenses, the only two accounts on the income statement. We're gonna start off with revenue. So the first account is just gonna be a revenue. So I'm gonna copy this. I'm gonna paste it right here. Paste it one, two, three. And there's no subcategory because we only have one type of revenue. We do one thing. Therefore, we're not gonna bring it to the inside and then subtotal it on the outside. We only have one revenue account. We're just gonna put it on the outside. Once again, these two do not mean debits and credits. So this isn't a credit over here. This just means that we don't need a subgroup in the terms of revenue because there's only one revenue account. So once again, it's a credit here. I don't want a credit over here. So I'm gonna put a negative and then click on this 36.8 to flip the sign. Now all the other accounts on the income statement are expenses. So we're gonna go over here and I'm gonna make a subcategory because we have a lot of expenses and put a colon like we traditionally do when we have a subcategory. And then we're gonna put all these expenses underneath. Now note that in a lot of companies, we're gonna have more accounts on the trial balance than we may have over on the financial statements and it's our job to group those in a logical way on the financial statements. In this case, of course, there's so few accounts we're just gonna copy them all over onto the financial statement. So I'm just gonna highlight all these, salaries expense, rents expense, buy expense, depreciation expense, insurance expense, miscellaneous expense, let go right click and copy. Then you're gonna put that right underneath the expenses here, right click. Gonna paste it one, two, three once again. And you'll note that the indentation is already there because the sales were formatted already. That happened because if we do that, we would decrease the indentation and then increase the indentation like so. So we're indicating that there's a subcategory by the colon and then by the indentation and then by bringing them to the inside column. This is not the debit side of the column. This just means that it's a subcategory and we're gonna say this equals this 8800 for salaries, equals the 16 for the rent, equals the 81 for supplies, equals the 330 for depreciation, equals the 300 and equals the 950. Could we have done that in a more quick way using Excel? We could have, I'm gonna delete these. We're gonna say use the old auto fill. We're just gonna copy and paste the formula. So I'm gonna auto fill it down by putting our cursor right there. So it looks like that. And then auto fill down, auto drags it down. Dr. Phil does calculations and we have the numbers here. So, and once again, it does that because of the relational formula of the cells. And so you wanna get used to that in Excel. It takes a little while, but very handy to have that. So then we're gonna say that total expenses are gonna be on the outside. And once again, already indented for us over here, that's gonna be in the home tab. Alignment, these are the indentation groups. And we're gonna sum that up on the outside now. Once again, we only do things on one column at a time. So we're not gonna jump from here to here. We're only gonna sum up this column on the cell with equals SUM. And we're gonna take the 880 down to the 950 and enter. So there we have it. If I highlight again, I can see it's the 12, 160. In the task bar, we can highlight here and see it's the 12, 160 here. We have now found a home for all of these. And so now the bottom line on the income statement is called net income. And we calculate net income as revenue minus expenses, which should be this 24, 640. And so let's do that. Equals the 36, 8, which is gonna point to J20 minus the 12, 160 and enter. We get the 24, 640 equaling this number here. If I highlight these, that's the 24, 640. Now you can see it's yellow because we're gonna take that and we're gonna use that in our statement of owner's equity. The only two accounts that we haven't yet found a home for other than grouping it into this number here are the capital and the draws. So now we gotta take that into consideration and we're just gonna use this number to recalculate this whole number here being the 56, 940 that we have in the capital count on the balance sheet. So the statement of owner's equity is also a timing statement. So I'm gonna just copy this, paste it down here. It's gonna be for the month ended, May 31st. And what we're gonna have here is we're gonna have the capital accounts. I'm gonna copy the capital account, paste it one, two, three. But we're gonna show the timing of the capital accounts. So remember this capital account represents the beginning capital account. This is the capital account before we plug all this stuff into it before we close out the temporary accounts to this account. So I'm gonna put this on the outside or we're gonna call this the capital account as of the beginning of the period May 1st, 2-O-X-1. And we'll put that outside and I'm gonna put negative to flip the sign, point to that 42-3 and enter. Now, our capital account is going to increase by the bulk of the stuff, the whole income statement, which we already calculated and crunched it down to one number, that one number, 24640 being on the income statement in the form of net income. So we're gonna increase this by net income. And that's gonna equal, I'm just gonna point to this 24640 and enter. So that's where the income is. Now all of this is included in our statement of owner's equity in the form of one number, 24640 as net income. Then the only account we have an account for, we account for this now, is draws. So now we gotta take that draws. Notice it's a debit balance and the credits win here. If I highlight all these, credits win 56940 on the credit side. That 10,000 then is decreasing the winning credit side. So it needs to be a decrease. That also makes sense, of course, because this represents the amount of money owed to the owner and the draws represent the owner taking money out. Therefore, this number needs to go down by draws. So I'm gonna represent it going down by saying less draws. And so I'm gonna say equals and point to that 10. And I'm not gonna put a negative number there, I'm just gonna call it less draws to show that it's a subtraction problem. And then we have an increase in owners equity. And it kind of should be called a net increase, but oftentimes just called an increase. Why is it a net increase? Because we're gonna take this subcategory, these two numbers, and pull it out to this side. And this number is increasing equity. This number is decreasing equity. Therefore, there's basically a net increase over the time period of equals the 24, 640 minus the 10 here and enter. All right, and that accounts, of course, for this, all this information, 14, 640, there it is there. Then we're gonna add these up to come up with the owner capital account. I'm just gonna delete this. We're gonna take the capital account again. But now the capital account is gonna be as of the end of the period, which is May 31st, 20X1. And so now we're gonna go ahead and add these up. We're gonna use the sum function to do so equals the sum of the 42, 3 plus the 14, 640 gives us the 56, 940. If I highlight these two, task bar 56, 940, we highlight all the blue. We see that we get a credit of 56, 940. So this is correct. We've just flipped the sign now to be a plus and minus format. And we see it's yellow, so we should be able to see that somewhere else. That somewhere else is here. So that 56, 940 needs to match here. We could tie it out for sure by deleting it there. And so notice where that number's coming from. It's there, it's coming from the blue area. But, and you can also see that from these little guys up here. And those are located in the formula group and the formula tab, formula auditing group. And here they are. And then we can also delete that. And if we wanted to show that in this format, say that this should be equal to this number here and enter. And once again, that'll make us in balance. So now just to sum this up, the assets of course, should equal the liabilities plus equity. The equity here should be coming from the statement of owner's equity or tie out to the statement of owner's equity. And the statement of owner's equity includes the income statement, which is calculated, I'm sorry, includes net income, which is calculated on the income statement here. And that's how the financial statements will be tied out. The actual accounting equation is of course, the balance sheet.