 Hello, in this presentation, we will create the financial statements from the trial balance. The trial balance will be in terms of debits and credits. We know that the double entry accounting system is working because the total debits will equal the total credits represented by the green zero at the bottom, adding up or summing up the positive numbers here of the debits and the negative numbers of the credits. What we're going to do is convert that format to the financial statements which will be in a plus and minus format. Note that the trial balance has everything we really need. The financial statements actually being a bit more bulky, but the financial statements also being something that is going to be in a format that anybody can read. That's going to be the point of doing the financial statements. It's important before we start to remember that if we are in balance here, it has to work in the financial statements. So it's just a matter of putting the puzzle pieces together in the right format and it must be in balance. Many books will start off by saying that we want to start with the income statement, then the statement of equity, and then the balance sheet. I'm actually going to start with the balance sheet. The reason I think most textbooks would say that is because if we were to do this by hand, and we did it perfectly, then it would be the least amount of calculations for us to do the income statement first because the income statement will be used to create the statement of equity used to create the balance sheet. But in our case, we have Excel here, so we can do some calculations. And in practice, of course, it's not always perfect when you first start these things. We're going to have to keep on adding up some numbers, so it's not like we can minimize the amount of calculations by doing it perfectly, possibly, because we'll have to go figure something out. The advantage of doing the balance sheet first is the balance sheet is everything. The balance sheet is the double entry accounting system. And it also starts off with the ordering that will be on the trial balance, meaning assets, then liabilities, then equity. So we're going to start off top to bottom on the balance sheet, starting with the asset accounts. So if we go through this, we're just going to say the asset accounts, we're going to bring these over. We're going to start with the current asset accounts, and we're going to type in current assets and then a colon. And that's going to show us that we're going to pull over these current assets. Now we can do this a few different ways. We can retype them in here. We can copy them over. I'm going to use a formula and note we can use formulas, just an equal sign, basically a simple formula, even with text. So I'm just going to say equals this number. So we know that cash, I'm going to put my cursor in K five and just say equals and point to this cash right there. So equals and we're pointing to F 10 and then enter. Now again, you could type that in there, but I'm going to I'm going to do that to pull that over. And we want to format this, I'm going to indent it as well to show that it is a subcategory of current assets by going to the home tab, alignment and increase the indenting. Now this might already be done for you at the indentation. So I'm just going to give you some idea of how you could format these as we go. So then we can pull the number over and say the cash is going to equal. I mean, so L five, we're going to say equals and just point to that 568 and then enter and that'll bring over the number. So this cells is going to be equals I 10 enter and that'll give us that number. Now we're going to go straight down. We're going to say accounts receivable also a current asset. So I'm going to pull that over. We could type it, but I'm going to say equals point to the accounts receivable and enter. Then put our cursor back on it. I'm going to format it, which may be done for you already. Home tab, alignment group, increase the indenting. And the number then in L six, we're just going to pull that over equals that 36900. And there that is next account prepaid insurance also a current asset. So we'll do the same thing where you're in K seven equals. We're going to point to that prepaid insurance name and enter, put our cursor back on it. Formatting already done for you. Probably a home tab, alignment, increase in dent, then the amount L seven equals we're going to point to that 11,000 and enter next item. If we scroll back over here, we got the supplies. So that's going to be a current asset as well. So we're going to copy that or I'm not going to copy it. We're going to go back over here to K eight and equals point to the supplies and enter and go back on it. We're going to go back up top home tab, alignment, increase the indent. Bring over the number in L eight equals and we're going to point to the supplies. 1050 and enter. Now you might be thinking now that we could do this a lot faster using some Excel functions. And I'll show you that now, but we're still going to do them a little bit more of just showing all the calculations to show how it works. So I'm going to delete this. These three cells from accounts receivable down the supplies and delete and show how we could just enter this first format and then auto fill them down. So I'm going to highlight these two cells, put our cursor right on that little box. This is the auto fill box and then we'll drag this down. Dr auto drives it down. Dr Phil does the calculation auto fill and there we have it. And it'll do the same thing for us because of relative references. So it's relative references. So we want to get that idea down just to learn Excel. We're going to pull over most of the calculations here to learn the accounting side of it, just to make sure that we know where these numbers are coming from. I'm going to format this cell, which might be formatted for you already by going to the home tab and underlining. So we will underline that format and then we'll have total current assets. Let's make a lowercase current assets. And we're going to pull this to the outer column in the outside. So we could just add these up, of course, if we had the calculator. If we were doing this by hand, it would just be the 568,000 plus 369 plus the 11 plus the 105 and that should be the 16950. But in Excel, we'll use the sum function. Most use function, we have to learn those functions. So we're going to say equals SUM double click the sum function and highlight these cells. So there's that information and it'll just sum that up. Excel will also sum it up if you just highlight these cells and look down here. We see that we have that number being summed up. If we highlight them on this side and scroll down, we see that we have them summed up here as well. So that's going to be that information. Then we're going to go down to the property planting equipment. Note we stopped at land because these are no longer current assets. These are going to be property, plant, and equipment type assets. Longer term assets, assets used to generate revenue over a longer period of time. So we're going to say property, plant, and equi- Note that I'm putting a colon after the equipment here. So that's how we can indent these. Then we'll pull over the amounts. So we're going to be here in the first one going to be land. So we're just going to say that that equals and point to the land and enter, and we will indent that going to the home tab alignment, increase the indentation. Then we'll go over here and pull the number. So we are in L11. We're going to say equals and then scroll over and point to that 112, 500. And there we have that information. Next, we'll pull over the equipment. So we're going to be here in K12 and we're going to say that that equals and scroll down to the equipment and enter. Then we'll go back on that cell. We'll go to the home tab alignment and increase the indentation. Then we're going to go here to L12 equals and point to the amount of the 135, 300. The next account is going to be one of the most tricky accounts. It's going to be the accumulated depreciation. The reason it's tricky is because it's an asset account, typically having asset accounts of debit balances, but this one having a credit balance. And so we're going to have to change the sign here. And there's a couple of ways we could do this. Some formats will show a negative number or bracketed number, not because it's a credit, but because it's a subtraction problem. But a lot of times we'll express this in words. So we're going to have to tell our reader, hey look, we're going to subtract this. This is going to be reducing the property, plant and equipment. So I'm going to use the word less and then accumulated depreciation. And so I'm going to indent that with typed in less accumulated depreciation. It's going to go over this cell just a little bit, but that's okay. I'm not going to reformat the cell or anything. I'm going to type over it. So I'm on the cell and we're going to pick up this number, but we want to flip the sign. So I want to make it a positive number. So instead of hitting equals, I'm going to hit negative and that'll just flip the sign when we use this formula and then point to it. So negative of I 16 and then equals and it'll flip the sign for us. And then we're going to have the total property plant and equipment. So here's that. If I misspell anything while we go through this, I apologize for that. But we're going to indent this twice. It's already been indented. We went to the home tab alignment and increase the indent two times. Then we're going to go to the outer column and we're going to calculate these these items here. Now, when we do calculate it, we're going to have to subtract this last one. And that's really where a lot of times people mess up right there. We add those three up and then we'll get out of balance. So to do that in Excel, well, to do that in the calculator, of course, it would be the 12500 plus 135 300 minus the 99 0 8 or 0 9 0 given to us by the word less here in Excel. We're going to do that by saying equals in M 14 pointing to that 112 500 plus pointing to that 135 300 minus pointing to that 99 50. And that'll give us our balance. The next thing we'll do is sum up the total assets. We're going to go over here into the outer column for the total assets. And we'll just sum up this column here. We're going to use the sum function to do so to check it. We could highlight these items and check down here that we have this number 7657. That's what this sum function should be. We're going to say equals SUM then brackets. So some brackets. And I'm going to sum up just these two numbers. Even though there's a bunch of blank cells here, that's OK. We're just going to sum up those two numbers and enter. Note what we did not do. We didn't jump around from here to here, go back and forth. We're just doing something with one column at a time. I'm going to format a few things. I'm going to underline here, go into the home tab, underline, and we'll go here and underline this item as well. Now, if we want to double check this number, if we're in Excel, it's easy to do. We can just highlight and down here, it'll start summing up as we highlight. So if I highlight down here, it'll say this plus this, plus this, plus this, plus this, minus this, because it's a credit over here and that will give us the 765, 700, 765, 700. So that component looks good. We can move on to the next one, that being the liabilities. It's also sometimes useful to go through here and somehow indicate and say, we found a home for these. And I'm just going to highlight those and say we found a home for those. Once we find a home for everything, we should be done. So the liabilities will be up here in the liabilities area. We have liabilities. We're going to break this out once again to current versus long term liabilities. Current liabilities, those do within a year. And so we're going to scroll down here and we only have the current liabilities here. These are all current and those are going to be in orange. We got the accounts payable, the wages payable and the unearned revenue. Scrolling back up, we're going to do the same thing. We're going to pull this the names over with a formula and equal sign. We are in in five, I'm going to say equals and point to that accounts payable that brings the amount over here. Then we're going to indent that. It's probably already done on your format and your worksheet. So we're going to go to the home tab alignment, increase the indentation. Then we are in five. And what we want to do is bring over this 12,150. But we also want to flip the sign to do that. We're going to put a negative up top. So we're going to say negative and then go to that number. So negative of that number, we'll pull that number over and flip the sign. So there we have that 12,150. Then the next one is, of course, wages payable. We're just going to do the same thing here. We are in in six. We're going to say equals point to the wages payable and enter. So we put our cursor there. If you can see it on the formula bar, it's up here equals F 18. That's what's in in six. Then we're going to format it already done for you. Home tab alignment, increase the indentation. Going to put the number here in 06 within 06. We're going to say that equals or negative, we should say, to flip the sign because we don't want to bring the credit over of that 2,500 and enter. So that will be the credit or I 18. Next one, we're going to go to the unearned revenue. So we are in 07 equals. We're going to point to this unearned revenue and enter. So there's the unearned revenue there. Home tab alignment group, increase the indenting. Then we're going to go to 07. We're going to pull over that 8,250. Once again, flipping the sign. However, so within 07, we're going to say negative of that number. And that will pull it over and flip the sign. So there we have that. We're going to underline this now by going to the home tab, the font group and underline and we will then put the current, the total current liabilities here. So here we have total current liabilities. I'm going to indent that two times with probably be already done for your format. Home pay home tab alignment, increase two times. And so we have the same format we pulled that we put a colon here showing that it's going to be on the indented showing that it's going to be a subgroup that we will then add up and sum up to the right hand side. The outer column, the two columns, not representing debits and credits, but representing sum and total. So we're going to use the sum function here. Once again, we're just highlighting. If we highlight it, we'll add them up in a calculator would be this plus this plus this. We're going to use the sum function in P8 equals SUM. Double click that sum function highlight from the 12, 150 down to the 8,000, 250 and enter. So there we have it. We can double check that over here by highlighting these items. They will be credits, but it'll be a credit equal to what we have in our plus and minus format of 22, 900. So there's that. We can say, OK, we found a home for that and that is done. And note also, however, I'm going to format this a little bit differently because there's no long term liabilities. I'm just going to say this is total liabilities. So and that's a bit typically used, but a bit tricky of a formula. If we had long term liabilities, it would be total current liabilities and then and then have the other liabilities and then long term. If we only have current liabilities, however, we can use the shortest amount of terms by saying, hey, these are the current liabilities and then total column rather than saying total current and then saying, hey, we don't have any long term. We'll just say these are all the liabilities and just leave that in one light item there. Now we need to do the equity section. So I'm going to say we found a home for these. We're going to highlight these found a home for those. And now we need to do just the equity. Now there's a bit of a trick to the equity section, which is why a lot of times the book will recommend doing the income statement first. But I'm going to show us how to how to put that together and what the kind of trick is so that we know what it is right from the start, right from where we're going from. So I think you get a better idea starting with the balance sheet. So we're going to go here and we're going to say that the equity is really just going to be this capital account. So I'm going to say equals this capital account. And there's no other name. You know, we don't have any dates here other than the date that's up top here. So we're just going to pull over this number. That's all that's in the equity section, typically for the sole proprietor. So and it'd be similar for a partnership. We'd have capital accounts, multiple capital accounts, partners, equity and a corporation. We'd have a retained earnings account and it could have common stock. And a few things that could be a little more difficult, but the concept of the equity section as a total as a whole will be the same no matter what type of entity. So then we're going to we're going to pull over this number and that's going to be actually wrong at first. I'll show you why it's wrong, how we can see it's wrong and then what we do to make it correct. So we're going to pull that in the outer column. We don't need to we're going to put it directly in the outer column because there's no subcategory. So we'll put it right in the outer column. And I'm going to say flipping the sign, put a negative of that number in order to pull it over and flip the sign. Now to double check this and see if we are in balance, we'll add up the total liabilities and equity, adding up the outer columns here, liabilities and equity. So we are in cell P 16. We're going to say equals, use that sum function again, SUM, double click the sum function, highlight the twenty two nine down to the six sixty three eight twenty. And that will give us the total here. I'm also going to underline this going to the home tab, going to the font group and underline. And we can see that now the total assets do not equal total liabilities plus equity. And of course, there is a problem if we check everything off, as we did before, we could say assets. If I highlight those add up to seven sixty five seven hundred seven sixty five seven hundred liabilities add up to twenty two nine twenty two nine and then the equity we're saying it should be that. But really it's that's going to be the problem. So really the equity on the balance sheet is going to be all of this. It's going to be all of this combined. So we're going to combine it as we go through the rest of the process, the income statement and the statement of equity. But it's useful to note that the balance sheet is basically everything. The balance sheet represents the assets minus the liabilities, which will give us equity seven forty two eight hundred. If we highlight the bottom, we get that seven forty two eight hundred. So on the balance sheet, we're going to break this all into or group it all up into one number. Now, we don't have to go through and calculate the income statement, the statement of owner's equity to do it, especially if we represent the debits and credits this format. Excel will do it for us very easily. So we can see that the credits here are represented with negative numbers for Excel. So we got the negative number for the capital and then we've got the draws as a positive number for a debit negative for the revenues or credit and then the positive numbers for the debit bounces of expenses. If we calculate that in Excel, then it's going to take that negative minus the positive plus the negative minus all the positive numbers, meaning the negative numbers will be winning by the seven forty two eight hundred. So there's our seven forty two eight hundred. That's what's going to be here. We can do that all in one calculation and then go back and kind of break that number out into its components, that being the income statement and the statement of equity. So I'm going to delete this and we're going to do that here. We're in P eleven. So I'm going to just sum this up. But instead of using an equals, we're going to use a negative sign and then we'll sum this up. So it's negative S U M and then we'll double click the sum function and just highlight. We want to pick up the six sixty three eight twenty down to the one thousand one hundred and enter and that'll give us our balance here. So now we got the seven forty two eight hundred and that puts us in balance. So the key point here is that that that number picked up everything in blue down here. So we kind of found a home for everything down here and that puts us in balance. We found a home for everything and that puts us in balance. However, we want to see more detail about this number. This is really the book value of the company, meaning its assets minus liabilities equals this, meaning this is what the company has. This is the third party, other people that are owed to. If we subtract that out, this is how much of the assets that the owner has claimed to. So that's kind of the net amount of the of the company. It gives us this in one point in time, but now we want to see it in terms of breaking it out to how we got to that point in time through the timing statements to the income statement and the statement of equity. So we'll get to the same number now by scrolling down here and finding a home for all these blue accounts in our final two statements, financial statements that being the income statement and statement of equity. So we're going to start off with the income statement and that's going to include these accounts here. So there's the income statement. We can already see net income being this right there, 88, 9, 80. So we're going to put our cursor in K 20. Well, first, we should say that the income statement starts with income statement for the month ended December 31st. So it's not a point in time at this time. It's going to be a time range, a beginning and an end December 1st to December 31st. And then we'll start off. It's going to be revenue minus expenses. So within K 20, we're just going to say that equals and we'll point to this revenue amount here and pick that up and enter. And that'll give us our revenue. Then we're going to go into the outer column and record the amount here. Now, we're not going to record the amount in the inner column, not because we're dealing with debts and credits, but because we don't have any other subcategory. We're going to have the inner column being the summing them up or the amounts. And then we'll sum them up into the category totals in the outer column. But because this is only one number here, we don't have any subcategories. So we're going to go directly to the outer column. So we are in M 20 and we need to flip the sign. So instead of having a credit balance here, we want to have a plus and minus number here. So within M 20, we're going to say negative to flip the sign of that number and then enter. So that'll pull that number over and flip the sign. Then we're going to have a subcategory for all the expenses. We will represent that by typing in expenses. And then the colon here representing the fact that this will be a subcategory. All the rest of these accounts will be expenses. So we're going to put all these underneath. We're going to say start with wages were in K 22. And we're going to say equals point to the wages enter. I'm going to go back on that cell. We're going to go to the home tab alignment, increase the indenting. Then we'll put the number here in L 22 equals. And then we'll point to this 195 870 and enter. Now, if you're proficient with Excel, you can highlight this and just copy that down. Now, if you would like to do that, I'm going to do this manually so that we can kind of see what is happening. And then I'll show you how to copy that down afterwards if you would like to learn that Excel function. And so the next one is going to be utilities. So in K 23, we're going to equals utilities tab. And then in L 23, we're going to say that this equals and we'll pull over that 42 375 and enter. I'm going to increase the indenting here. So on utilities, home tab, alignment, indent. And then we are in K 24. We're going to say this equals. We're going to point to the insurance expense and enter. Go back on it. We're going to go to the home tab alignment, increase the indenting. Then we're going to go to L 24 equals and we'll point to that 1000 enter. And then we'll go to the next line on and we'll pick up the supplies. We're going to say equals. We're going to point to the supplies and enter. We'll indent that. Go into the home tab, alignment, increase the indenting. And then in L 25, we're going to say this equals and point to that 2009 25 and enter. Next line depreciation. So we're in K 26. We're going to say this equals point to the depreciation, enter, put our cursor back on it, home tab, alignment, increase, indenting. Then we're going to go to L 26 and say that equals point to this 1100 and enter. And there we have it. So we have all those expenses. Again, you could I'm going to delete this and just show you how to do this with the auto fill function. So I'm just going to delete this real quick. We're going to do the same thing. It's going to delete that. Now, once we have these done with formulas, we could just put our cursor on the auto fill tab here and highlight this down. And the relative references will then pull over those numbers properly. So we don't want this last one. I'm just going to delete this last one. And so note what's happening here. It's saying, hmm, if you pull that down, you're probably just going to want to go to the next one down. If you pull that down, you're probably going to want to go to the next one down. And that is typically the case. That's the relative references. Very useful to know that, to see that, to understand that. Then we're going to go and underline the depreciation, go to the depreciation, home tab, font and underline. And then we'll total these up. So we're going to say total expenses and sum them up to the right side. I'm going to increase the indenting here. So home tab alignment, increase the indenting. We're going to sum them up over here. So once again, we're not going to go from column to column. We're just going to sum up one column at a time. We're summing up this column into the side. To do that, we will use the sum function. So we're going to say equals SUM. Double click the sum function and highlight this column. So we'll take that column of numbers and enter. So we can double check that in a calculator. We would just say this plus this plus this plus this plus this. Giving us this 243, 270, this 243, 270. The same thing we can check over here. This plus this plus this plus this plus this gives us that 243, 270. Then we're going to have the bottom line number, which will be net incomes. We'll say net income and the net income will be calculated as revenue minus expenses, revenue minus expenses. So we're only going to do things in one column. We're not jumping to this column over here, just the outer column. And we're going to say equals point to the revenue minus. And then we'll point to the expenses and enter. So there we have it and we can double check that if our fine, if our trial balance is formatted nicely in this way. By just highlighting these, we can just say, hmm, there's a credit minus all the debits means that we have net income of 88, 9, 80, 88, 9, 80. That looks good. We can verify that we found a home for all of those numbers. Now, the only numbers we have not found a home for are the capital account on the trial balance and draws. We did use them in order to help get to this number, but we haven't really found a home for them individually. And that's what we will do now in the statement of equity. So we're going to scroll down to the statement of equity. And we're going to start off with the owner capital account as of the beginning of the time period. So we'll type in owner capital as of the beginning, and that's going to be December 1st. Why? Because this number represents the end of the time period. And this number, the end of the time period is going to be everything, all of this together. So this must be the beginning balance. Now, if there is something that's what's posted to this account, then we'll have to adjust for that. And what could be posted there would be something like investments. So if the owner put in an investment, we might put that directly to this account. How would we check it? We'd look at the general ledger, see if there's any detail. Typically, there isn't any detail because we don't post anything to the owner capital. We post the detail to the temporary accounts, which include draws and the income statement, revenue and expenses. So there we have that. And that's what we're going to pull over to the outer column here, and we're going to flip the sign. And this is the most tricky piece here is the fact that we have to kind of add the dates and the fact that this amount here does not show as of the end of the time period. That's really kind of the beginning of the time period. And we're going to pull that here. So in cell M33, we're going to say negative of that number. And that'll take that number and flip the sign. So there we have it. And then the main thing that's going to adjust this account will be net income. So net income will be yours. And of course, we're just going to type in net income. And we calculated that right here. So here's the calculation of net income. We're just going to pull that down to this form. So we're going to say this equals that number. And that's how these two will be linked and we'll say enter. And then we're going to say draws. So the only thing we haven't found a home for is draws. Now we found a home for this draws. Now, the tricky thing about draws is it will be a reduction. You can see here it's a positive number because it's a debit. But the credits are winning. The credit balances are going to win in all the capital accounts. So it's really bringing down capital. And you can also think of it as draws. If someone drew money out, the owner drew money out. The company has less money there or and therefore we're reducing what is owed to the owner, kind of like if we were reducing a payable by paying off the liability. So then we're going to go down here. We're going to say draws and we want to put in words. It's going to be subtraction. So we're actually going to put less draws like that. Less draws and we'll record this amount here. And we're going to just pull over that 10,000. So we are in L 35 equals and we're going to pull over this 10,000 here in I 21 and enter. And there we have that. So then we'll have the subtotal increase in the in the equity increase in owner's equity. Now, the increase is going to be the difference between these two. So we're going to have to subtract those out. When we say increase, we really mean kind of net increase, meaning we're saying it's going to be this minus. This is a net increase in the capital from this by whatever this increase will be. So we are in cell M 36. We're going to say equals and point to this 88, 9, 80 minus point of this 10,000 and enter. And that'll give us our net increase. Then we'll have the capital at the end. So I'm just going to copy this. We're going to put that on the bottom, pasting it one, two, three. And I'm just going to change the date now. So it's not the December 1st. It's December 31st now. And then we have our ending balance here, which will be the sum of the outer columns. We're not doing anything here. We've already done something there. We're just doing something to the outer columns. That's where we started. This is the detail giving us this number. So we'll say equals SUM of these cells, this and that and enter. So we just highlight these. That's 742, 800. That's if we add those two up, 742, 800. We now have found a home for everything. If we want to double check that last number, we can say, what if we just add up all these numbers? And when I say add them up, I mean take the credits minus the debits. So the credit minus the debit plus the credit minus debit, minus debit, minus debit, minus debit gives us this 742, 800. And that's going to be, of course, this 742, 800. And that 742, 800 is what we had up here on the statement of equity. So this number should tie out there. And we might want to just double do just double check it by re-putting this formula saying this should equal this number down here. So that's what I would how I like to basically see this. Look at the balance sheet. Look at the big picture. Look at our equity number. If we can calculate it easily as we can, if our if our numbers are in Excel. And even if they're not, it's we're going to have to add up these calculations and do them a few times probably in order to get this thing correct. So we can put that number there and see that we're in balance. And that really gives us some assurity. Once we're in balance, we're like, well, we know everything here is correct. And we know that we have to get to the statement of equity to tie out to that number. And then we can move to the income statement and step through that, put the income statement together, which will give us net income, a component of the equity, and then that will be used in the statement of equity to get to the ending balance. You always want to note what these yellow numbers are as we put this stuff together. That means that it's going to have to do with numbers you want to check here, meaning assets, equal liabilities, plus equity, that should always be the case. We know that the net income will be on the income statement and should also be pulled to the statement of equity. And we know that the ending balance in the statement of equity should be the same number as on the balance sheet.