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 been a bit more bulky, but the financial beans 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. And the reason I think most textbooks would say that is because if we do this by hand, then it would, 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, 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 gonna 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 versus the balance sheet is everything balance sheet is the double entry accounting system. And it also starts out with the ordering that will be on the trial balance meaning assets than liabilities than 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 myself cursor in K five, and just say equals and point to this cache 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 income The indenting. Now this might already be done for you at the indentation. So but it's going to give you some idea of how you can format these as we go. So then we can pull the number over and say the cache 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 will bring over the number. So this sales 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 36 900 and there that is next account prepaid and Also a current asset. So we'll do the same thing. We're here 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 Home tab, alignment, increase indent, 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, well. I'm not gonna 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 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 calculation to show how it works. So I'm going to delete this, these three cells from accounts receivable down to supplies, and delete, and show how we could just enter this first format, and then autofill them down. So I'm going to highlight these two cells put our cursor right on that little box, this is the autofill box.

And then we'll drag this down doctor for auto drives it down. Dr. Phil does the calculation autofill. 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, make it lowercase current assets, and we're going to pull this to the outer column and 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 thousand plus 36. Nine was the 11 was the 1050. And that should be the 616 950.

But in Excel, we'll use the sum function. Most use function, we have to learn this function. So we're going to say equals, if you double click the sum function and highlight the 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, plant and 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 equipment.

Note that I'm putting a colon after the equipment here so that we can indent these, then we'll pull over the amounts. So we're going to be here in the first one's 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 go into the home tab alignment, increase the indentation, then we'll go over here and pull the numbers so we are in L 11. We're going to say equals and then school 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 K 12. 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 Go to the Home tab alignment and increase the indentation there, we're going to go here to L 12. equals and point to the amount of the 135 300. The next account is going to be one of the most tricky accounts is going to be the accumulated depreciation reason it's tricky is because it's an asset account, typically having asset accounts have 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 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 we've 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 cells.

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 then we'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 get out of balance. So to do that in Excel, we'll have to do that in the calculator. Of course, it would be the 112 500 plus 135 300, minus the 9908. Nine Oh, 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's 112 500 plus pointing to that 135 300 minus pointing to that 9950. 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 765 seven.

That's what this sum function should be. We're going to say equals s u m, 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 okay. 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 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 and say this plus 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 707 65 700. So that component looks good, we can move on to the next one, that being the liabilities. It's also sometimes useful that go through here and some 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 gonna break this out once again to current versus long term liabilities, current liabilities, those do Within a year, and so we're gonna scroll down here and we only have the current liabilities here, these are all current, and those are gonna be in orange, we got the accounts payable, the wages payable and the unearned revenue. Scrolling back up, we're gonna do the same thing when it pulled 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 worksheets. So we're going to go to the Home tab, alignment, increase the indentation, then we are in Oh, five, and what we want to do is bring over this 12,001 50 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 to 12,001 50. Then the next one As 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 could see it in 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 oh six, within oh six, 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 2500 and enter. So that will be the credit are I 18. Next one, we're going to go to the unearned revenue. So we are in oh seven equals we're going to point to this unearned revenue, and enter.

So there's the unearned revenue, their home tab alignment group, increase the indenting Then we're going to go to oh seven, we're going to pull over that 8002 50. Once again, flipping the sign, however, so within oh seven, 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 will 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, so in 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 of 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 it Highlight, we'll add them up in a calculator would be this was this was this, we're going to use the sum function in p eight equals su m, double click that sum function, highlight from the 12 150, down to the 1002 50, 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 20 to 900. So there's that we can say, okay, 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 in 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 line item there. Now we need to do the equity section. So I'm going to say we found a home for these, I'm 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 that 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 I 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 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, it 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 us that sum function again, as you m, double click the sum function, highlight the 22, nine down to the 663 820. And that will give us the total here. I'm also going to underline this go into that 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 can say Hmm, assets if I highlight those add up to 765 707 65 700. Liabilities add up to 22 920 to nine, and then the equity we're saying whom 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 in this case 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 740 to 803, highlight the bottom, we get that 740 to 800. 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 or 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 get 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 balances of expenses.

If we calculate that in Excel, then it's going to take take that negative minus the positive plus the negative, minus all the positive numbers, meaning the negative numbers will be winning. By the 740 to 800. So there's our 740 to 800. 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 np 11.

So I'm going to just sum this up, but instead of using n equals, we're going to use a negative sign, and then we'll sum this up, so it's negative su M. And then we'll double click the sum function and just highlight we want to pick up the 663 820 down to the 1100 and Enter. And that'll give us our balance here. So now we got the 742 800. 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 the net amount of the of the company. It gives us this at 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, 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 at 8980. So we're going to put our cursor in key 20. Well, first, we should say that the income statement starts with the income statement for the month ended December 31.

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 1 to December 31. 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 debits and credits, but because we don't have any other subcategory, we're gonna 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 sub categories. So we're going to go directly to the outer columns. 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 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 gonna 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, or nk 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 now. 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 40 to 375. And enter. I'm going to increase the indenting here. So on utilities Home tab, alignment, indent, and then we are in a key 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. Have 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 case 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 autofill function. So I'm just gonna delete this real quick. We're going to do the same thing, just gonna delete that. Now once we have these done with formulas, we could just put our cursor on the autofill 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 gonna delete this last one. And so note What's happening here saying, huh, if you pull that down, you're probably gonna 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 in 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 su m, 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 net income will be calculated as revenue, minus expenses, revenue minus expenses. So we're only gonna do things in one column, we're not jumping to this column over here, just the outer column, and we're gonna 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, there's a credit minus all the debits means that we have net income of 88 980 89 ad.

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 form 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 1, 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 was 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 Under capital, we post the detail to the temporary accounts, which include draws in 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 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 m 33, 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 here. 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 draw. 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, and I 21 and Enter. And there we have that.

So then we'll have the subtotal increase in the end the equity increase in owner's equity. Now the increase is going to be the difference between these two. So we're gonna 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 so m 36, we're going to say equals and point to this 88 980 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 123. And I'm just going to change the date now. So it's not the 10th December 1, it's December 31. Now, and then we have our Indian 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 some of the outer columns. That's where we started.

This is the detail giving us this number. So we'll say equals su m, the sales, this and that, and enter. So we just highlight these, that 740 to 800. That's if we add those two up 74 to 800. We now have found a home for everything. If we want to double check that last number, we could 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 means that at my step, my debit gives us this 740 to 800.

And that's going to be of course, this 740 to 800. And that 740 to 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 duty, just double check it by reporting 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, 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 balanced, we're like, Whoa, we know everything in 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 Indian balance in the statement of equity should be the same number as on the balance sheet.