Excel Accounting–Adjusting Entries & Financial Statements

1. Introduction: Excel accounting, adjusting entries and financial statements is a project-based course where we will enter a period and adjusting entries. And after having done so, make the financial statements that being the balance sheet and the income statement into our accounting system using Excel, although you might be able to use Google Sheets as well, we have the downloadable worksheets here, and each of the worksheets will typically have at least two tabs, one with the answer to it, the second tab having a preformatted worksheets up to the point in time of the work being done in the current presentation. So you can complete that work along with the videos in a step-by-step process that completed worksheets being the completed project. 2. Adjust Worksheet for Adjusting Entries: Excel, accounting, practice problem, adjust worksheet for adjusting entries. Get ready because we're about to excel. But we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it, if you have access to it, There's two tabs on down below, an example tab and a practice tab. Practice to have starting out where we left off last time. Example tab in essence being an answer key. Let's stay here on the practice tab for now. Last time we were entering data for the second month of operations, we got a little bit fancy with the trial balance over here where we had the beginning balance for the month of February, the year to date information for the two months of activity that we had put in place the entries that were going to impact both the current month and the year to date information and then the ending balance for February, ending balance for the year to date. We saw some differences down here where the differences would be applied out on the income statement accounts, the temporary accounts as the income statement would be starting over from from when we're looking at just the month of February versus the year-to-date information, having the year-to-date information, this kind of change would be something that software could often do if you're running a balance sheet and an income statement by simply changing the date ranges. So you want to get accustomed to that component or that concept. We're now going to be converting this, however, to a worksheet for the adjusting entries. Now, adjusting entries are often something that you could think of as being done by an outside CPA firm or at least a separate process in your mind, do you want to be thinking of it as an essence, a separate process? Oftentimes, you'll be exporting the information from a software like QuickBooks and a trial balance format and setting up a worksheet similar to this in Excel, even if you're using accounting software. So that you can think about more clearly in just a couple of entries, the entries that you're gonna be needing for the adjusting entry process. So basically we're going to, we're going to adjust this worksheet to a worksheet typically seen an adjusting entry process which is similar to this. It's gonna look something like this over on the side where we're just going to be looking at the year-to-date information for our practice purposes here. So we're gonna get everything correct as of the cut-off date for the two-month period ended on February 28. And then we'll typically call the first column, the column that we might be printing from. If it was in a QuickBooks software, some accounting software, the unadjusted trial balance for the year to date numbers here always as of the cut-off as of the end of the period in this case that cut off the in February, it might often be the end of the year, December 31st. Oftentimes for a calendar year company still just entering that information directly into, say, QuickBooks or some worksheet so that you can then focus in on the adjustments that need to be made in the worksheet, possibly outside of the software, and then enter the information back into the software. Then we'll have our adjusting entries. So we're focusing in here just on those entries that are adjusting entries, period n entries. We want to keep a separation in our mind about those entries that are used for adjusting entries as compared to normal accounting process entries so that we know what we're doing with regards to those things that are done at the end of the period to make our financial statements as correct as possible on whatever basis we are using typically an accrual basis for reporting purposes. And then we'll have our adjusted trial balance at the end. So that's gonna be the format of the worksheet. Let's go back over to the practice tab here. So to do that, I'm just going to first, I'm not going to delete these cells, but I'm just going to hide the entire column. So I'm going to hide this column and select it and just right-click on it and hide that column. Well, before I do that, let's undo that first. Let's, let's take our ending balances and copy them over to the beginning balances. And then we'll clean up this middle section. So I'm going to take the ending balances where we left off last time. Just going to copy those Control C and paste them right here in my beginning balances. And this will be our starting point worksheet for the adjusting entries processed. And it'll just basically delete this activity in the middle. We will remove the activity in the middle. And so there is our worksheet now, I'm going to hide the February information and just look at the year-to-date information. So I'm going to hide the beginning trial balance for February, right-click and hide it. And then I'm going to hide the ending balance for February. Right-click and hide it. These ending balances then are simply taking the beginning balance here and then adding the entries to it. Then I'm just going to adjust the names of this thing. I'm going to call the first item the unadjusted trial balance. And you can think about that whatever accounting system you're using, you're thinking about that. That's the trial balance that have been done after the accounting department has done all the normal type of data input, and typically after the bank reconciliation has been completed. But we have not yet entered any of the adjusting entries to it. So there's no difference, in other words, from the trial balance to any other trial balance that would be created during the time-frame except for the timing of when it was created. It was created after the data input for the period. And after generally the bank the bank reconciliation, but the adjusting entries, the things that we have planned to do on a periodic basis at the end of the month or year have not yet been done. Then I'm going to call these adjusting entries, adjusting entries. And then this is gonna be the adjusted, adjusted trial balance. And that's gonna be the typical terminology that you will see. Now note that some worksheets that you'll look at for this kind of adjusting process will have a very large, very intimidating worksheet because they'll basically put two columns for the, for the unadjusted, two columns for the adjusting entries, two columns for the adjusted trial balance. That's because of course, you traditionally have the debits and credits on the left and right-hand side. But again, if you're working in Excel, that is a very tedious worksheet to look at and it really complicates the calculations of the formulas in that, in that format. So if you're working in Excel, you greatly simplify the problem by basically saying, I'm going to put the credits representing the credits as negative in the same column, allowing you to do the data input a lot more easily into your check figure more easily. So even if you have to manually create this typing in the accounts from something from a database program or a piece of paper. It's easy to check that your imbalanced by getting to the bottom down here and saying, I'm just going to use a simple, what mathematicians would call more elegant type of type of formula and easy formula, which is just the sum function to determine whether I'm imbalanced rather than summing up one column, summing up the other column, and then subtracting the two, which is a lot more steps. We can also do things a lot more easily this way by simply selecting, say, the income statement and looking at net income down here, which has a credit balance, just double-clicking on it, in other words, and you've got the net income calculation, I can add up the assets even with a contra-asset there, and just select those items and have the most simple type of calculation as opposed to taking the debits minus credits. So it's really in my opinion, and I think it's clearly, if you work with the two worksheets, you'll find that, you'll find this one. It's gonna be a lot faster than working with a worksheet that has six columns that does the same, same type of thing. And then having to use formulas to double-check things which are using not just addition. You'll use it a lot more complex formulas in order to calculate it. So this is the worksheet that I would recommend. It will also be a lot easier when we make the financial statements. If you've been working in the past, you've seen the financial statement creation. I think in this format of a trial balance in Excel is far and away easier then making financial statements from another format of the trial balance such as one, breaking out the debits and credits and not using credits in a negative format. Okay, So we have that. Then we're gonna do the adjusting entries. Now, remember the adjusting entries are always done at the end of the period. So we're gonna have our cutoff date here is going to be for the end of the month, their planned entries. They are not something that basically we're fixing we're fixing the accounting departments problem because they've messed things up. No. We want the Accounting Department to do the things that they did typically for normal kind of adjusting entries. And we're planning to do an adjusting entry at the end of the period in order to make the accounting data input easy and efficient as possible, as well as to be able to report and show things as of the reporting dates into the month and the year, typically on the most accurate accrual basis as possible. So they're planned type of activities. They will typically have a balance sheet account and an income statement account related to them. And they will typically not be including cash. Remember, we've reconciled cash. It's pretty good to go. These are going to be items that are not typically included in cash. They're typically going to have a timing component to them. Therefore, they're going to usually have an income statement account related to it and a balance sheet account related to it. Those are the general rules you can expect for the adjusting entries. So before we start as well, I'm going to adjust the columns on the left-hand side. I'd like to make a nice clean sheet that we're just gonna be working on, adjusting entries on. So I'm going to be copying from Z to C H. I'm going to copy that. I'm going to paste that into the skinny right here. I'm going to right-click and insert, not gonna pay. So I'm going to right-click on the skinny and insert the copied cells. And then I'm going to copy the skinny over here, copy the skinny. And I'm gonna put that right before the AD. So I'm going to add it right before the AD. Right-click and insert the copied cells. So there we have the skinny and then I'm going to hide everything to the left. So I'm gonna put my cursor on column on the skinny, go to the Z, right-click and hide all of that stuff. And then this stuff I don't need, It's not being posted anywhere. I'm going to select it. I'm going to go up to the, to the Format Painter. Format painted because I don't want these indentations to mess us up. And then I'll just paint, brush this all the way down. So we have a nice, clean blue slate that we'll be working on. And then I'm just going to delete the activity so that now we have this clean blue slate. So we'll be entering these transactions into the adjusting entry worksheet. That's typically how you'll see it if you're thinking about a traditional adjusting entry kind of system that might be done at the end of the year by CPA firm or something like that, and then possibly entering it back into the accounting software. But we will also be posting them to the GL because eventually you would then want them post it to the G L. So we have been posted to the geo on the right-hand side. We'll continue with the posting process to now, one more thing that I want to point out here is that we will have some reversing entries as well. So we're going to have some times that we need to do a reversing entry. And this is going to be important to the adjusting process because remember the goal here, we want to adjust things as of the cut-off date to make them correct on an accrual basis, but also not mess up the accounting department, which is doing things correctly as efficiently as they want to do them. We want the accounting department did not have to worry about these accrual adjustments when they're doing something like payroll, which is on whatever system the payroll is on. I don't want to make payroll more complicated. So that means that when we do our adjusting entries, there might be some of them which we should reverse to get back to the accounting department doing what the accounting department needs to do. We don't want to mess up the accounting department. So not all adjusting entries will need reversal, but some adjusting entries should and may need reversal in order to make that separation between the data input from the accounting department and the adjusting process clear. So what I'm gonna do is I'm going to add a couple more cells to the left here. And then I'm gonna, I'm gonna make these reversing interests. I'm going to select this skinny on over to AQ. I'm going to make two more columns. Right-click and let's insert those items. I'm going to insert those, I'm going to remove the formatting, selected this item and just remove the formatting if you don't see that, That's okay. It's because we're just going to format just like these two. So I'm going to select a M2 eo format, paint them home tab format paint. And then I'll just paint brushy that format right there. There we have it. And I'm going to call these than the reversing entries. Reversing. And then I'll just say these are going to happen. I'm going to put the date here. I'm going to put a colon apostrophe, whatever that is, so that it will show up 301, the first day of the next period. These are gonna be reversing entries. Let's do it this way, reversing entries. And then this is going to be as of that thing, 301, the first day of the next period because our cut-off date is February 28th in our practice problem, and this will be the trial balance. This will be the trial balance as of three, let's just say equals this thing 301. So then all I'm gonna do is say this is going to be equal to the adjusted trial balance. And then I'm going to basically add the blink cell to it. I want to copy that formula down. So I'm going to copy it down. I'm going to right-click on it and copy it. But I don't want to I don't want to paste it normal because that'll mess up my colors here. So I'm going to scroll down and I'm going to right-click and paste it. Just the, just the formulas, the formulas and enter. So there we have it. So that'll be our reversing columns. So we'll do our adjusting entries to get to our adjusted trial balance. Then some of those we will reverse. Now I'm not going to post the reversing entries, I'm just going to post the adjusting entries. So hopefully that'll make a little sense. When I say post, I mean post them to the G, L on the right-hand side. Hopefully that'll make more sense once we get going. But we'll do this one step at a time once we get, once we get on a roll here. And then I want to add another skinny. I need another skinny to give me some space. So I'm gonna put my cursor on this skinny. Let's put it on this skinny right here, this one. Copy that. And I'm going to insert that skinny before the, before here we're going to right-click and insert the copied cells. So there we have a skinny between the trial balance and and our general ledger. So that looks good. And then lastly down here on the income statement, I'm going to imagine that this happens on 31. All the income statement accounts should basically roll out into equity because now it's after the cutoff date. I'm running the trial balance for just one day. So I would like to basically close out everything on the income statement. So in other words, I'm going to 0 out everything on this side. Nothing on the income statement because it's going to close out. And if I copy my balances over these ending balances, I'm going to copy these over. I'm going to just copy them over. Now I'm out of balanced by the 4770 because that stuff should roll into equity. And I'm going to roll it into equity this way so that it will change as we change our data over here. So what I'm gonna do is I'm going to take this 77895, go to the end of it. I'm just going to say plus the sum of all of the activity on the income statement on the adjusted trial balance, which I'm just going to lump together in one lump sum number inequity, like it's closing out because we're closing it out to equity. So now we've got the AD2 665 on the trial balance after the cutoff, basically running it for one day on 31, having closed all of the income statement accounts into it, and we'll see the reversing entries will have an impact over here, and that'll cause us some problems, but we'll we'll explain why exactly. We will do the reversing entries the way we will do them. So there's, there's gonna be our worksheet now when we first start out when we're just doing the adjusting entries, we will hide columns a to R will hide them and just work over here. And then when we then go to the reversing entries, will unhide the reversing entries and then focus in on them as well. So I think that's it. I think we've got a nice, clean, nice, clean worksheet. We're ready. We're ready to go on next time. 3. Adjusting Entry Accrued Interest Part 1: Excel accounting practice problem, adjusting entry, accrued interests, part number one. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it, if you have access to it, There's two tabs on down below, an example tab and a practice to have the practice tab starting out where we left off last time. The example tab in essence being an answer key, let's take a look at it now. We're looking at the accrued expense and interest payable related to us incurring interest expenses for which we have not yet paid for, therefore, needing an adjusting entry at the end of the period as of the cut-off date, which for us is gonna be February 28, the end of the month of February. Note that interest expense is similar to save rent on say, like an office building. Meaning you incur the rent on the office building by using the actual office building. And the same is true with the use of money. So you incur the expense of interest expense when you're using the purchasing power of money that has been borrowed in some way, shape, or form, whether you have paid for it or not. On an accrual basis, we should be recognizing the expense when we have incurred it. So let's go back to the practice tab. What we're gonna do first is look at an amortization schedule to think about this concept, and to do that, we're going to make an amortized eight. We have two loans down below that are on the books. This first loan we put, we made an amortization table for a longer period loan that will come into play when we start to make our financial statements and breaking out the short-term and long-term portion of it. And so we'll talk more about that later. But then we've got the loan is the one that we're going to use this time where we're going to imagine that one of the payments that we've made has not yet been made and we have some interests that has accumulated upon it. Let's first clean up our worksheet a bit. So I'm gonna, I'm gonna hide the reversing entries. We don't need the reversing entries at this time, so I'm going to hide them. I'm going to select from AP to AR, right-click and hide those cells. So then I'm gonna go all the way to the right where we have some space because we have the entire general ledger over here. We have some other information, the subsidiary ledgers that we've been putting together to support that data input. We're gonna go all the way to the right. So before we get to the financial statements and add some cells. So here we got, here we've got our loan payable. This was the last loan payable for 72 thousand and this is our amortization table. Let's put the other one right next to this one here. So we're gonna be going on the right side. I'm going to make some space by putting my cursor on this skinny. And let's make a good amount of space going over to this cell right over here and say right-click and Insert. And then I'm going to get rid of the formatting, putting my cursor on the Format Painter and clear the formatting. I'm then going to format it the way I would like to format it, right-clicking on the selected area formatting it. Format in the sale. I'll typically if D choosing on the formatting, the currency, negative numbers be bracketed and red, no dollar sign. And we're going to remove the pennies, even though there might be pennies involved here, just to make it look a little bit cleaner. I also typically bold everything. Hopefully that makes it a little bit easier to see, especially in presentations. You may not want to do that for your own self, but I think that might make it clearer on the presentation. So then I'm going to make this one a skinny, skinny column. And we're going to imagine our loan data or detail which you would get from the bank. So this would be the information you would need to get from the bank. The loan agreement. She might have it. They might give you an amortization table, but they may not give you an amortization table to, if not, you would either have to create one or possibly depend on your accountant or CPA firm to help you create one in order to record the payments properly, as well as breakout the short-term and long-term portion, if necessary, which we'll talk about later with the loan on the left. And also to think about the accrued interest if there were to be any, as we do the adjusting entries, remember that these adjusting entries or something that you might not necessarily need to do every adjusted entry if you're a smaller type of company and you're not giving your financial statements for reporting purposes, like externally to a bank or to investors. However, you're going to still need to do some of the adjusting entry is at least to make sure that you're in compliance with whatever the tax code will be, adjusting entries such as depreciation, for example, even if you're on a cash basis, you can't really escape that one. So just keep that in mind as you're thinking about these, you might be saying, Well man, this is a lot to be, to be dealing with. It's really going to be dependent on where your area is. And also just realized that if you are a bookkeeper, your goal really is to try to think about what you're gonna do in the data input compared to what possibly a CPA firm or the tax preparer will do at the end of the period. Makes sure that you have a good understanding between who's in charge of what as you, as you do that interplay. In doing that, you could set up a really good system and focus on whatever it is you want to focus on whether that be in the tax side of things or the bookkeeping side of things, or if you want to do the whole thing in any case. So we're going to sit alone is gonna be four, we'll say 5 thousand months. I'm gonna make this only a three month loan. So I can try to make this a significant amount of the adjusting entry to make it relevant. So I'm just going to say it's a three month loan. The rate I'm going to say significantly high to make a significant amount, 0.35 or 35 per cent, I'm gonna go to the number group and percent of phi that. And so that would be that the payments, which I'm going to calculate using the payments function. This isn't really my focus to do a loan calculations. I'm doing this fairly quickly here, but if you have this information up top, you can calculate the payment. If you are missing any component, then you can back into any of the other components here. So if they didn't give you the rate and they gave you the payment for three month time period, then you can back into what the rate is and so on. So this is gonna be, I'm gonna say negative instead of equal. So it'll, it'll flip the sign to give us a positive number of PMT. Shift nine. That's our payment function. We're going to pick the rate up here. Normally, the rate is given on a yearly basis, not on a monthly basis. So I'm gonna take that and divide it by 12. That's a twig, tricky twist that you've got to take care of comma. We've got the number of periods. The number of periods is going to be three, and those periods are in months, not years. And that matches what the interest is n because we divided it by 12 to get the months and then comma, the present value is the loan amount of the 5 thousand and enter that gives us a payment of 1765. Now you could just type that in if you want. But we are using pennies, so it's really 1.8764 to about, but I'm going to round it to 1765 and use the rounding function here. That Excel rounds it even though you can't see the rounding because it's using a formula or function to calculate that out. If I use that cell to calculate somewhere else, it will actually not use the exact 1765, but the actual number. So now I'm gonna make this a skinny cell, the HW make that skinny. And we'll make an amortization schedule similar to the one on the right. I'm going to say we have the month and then the payments payments. And then we've got the interests and then I've got the loan reduction. Notice. I'm putting this on two separate cells instead of using the wrap function in one cell because that makes one kind of a wide row, which, which makes everything from the left to the right of it a little bit off. So if I'm not constructing a table or inserting a table, such as going to the Insert and making it into a table, then I will not. I'll try to use two rows and then just format it using my formatting instead of trying to wrap it. And then this is going to be alone bow lands. So then I'm gonna do my head or formatting, which is going to be selecting these items typically go into the font group. I'd like to make the headers black and white. Black and white on the header. We'll center this stuff. And then we're going to make our months. Now I'm going to assume that the loan started on 215. I'd like that to make it make that into a formula a month type of the cell. So I'm going to right-click on it, gonna go to the format of the cell. And so usually, if you go down to the to the date area, I don't really want the year, so I've got one right there, which is the 314. Sometimes you might not have that. You might have something like this and you'd like to remove the date. To do that. Just put your cursor on that one and then go to the Custom. And that'll be the last one that was in place here. And now you've got your custom formatting. And I'm just going to delete the years on. It's going to say that you'd get rid of the years. And then that should give us the format we want. I'm just going to say, okay, so now I've got the 215. And so there we have it. So there's our date. So it started on 215. Remember our cut-off date is 228. So this was a half a month here. Then I'm going to say that's when we took it out. We're going to imagine for our practice problem purposes to calculate the accrued interest. And then the next one is going to be on three-fifths. So I'm going to copy this formatting down. Copy this formatting down. This will be on 315. This one is on for 15 and this one's on 515. There's the winter or payments are going to be made. The payments will be for the amount of 1765 about and then the interest calculation is going to be this is the rent. Now we're calculating this is where our focuses were on the 5 thousand times the thirty-five percent, that would be the interest if it were for an entire year, divided by 12 to get the monthly interests, that would be about 146. And so then I'm gonna do it. Payment was 1765 minus 2146. Lone reduction then is gonna be the 1619. We've got the loan amount, 5 thousand minus the reduction 1619. I'll do the calculations a couple more times and then I'll show you how to copy that down. So the payment is going to be the same. The interests is a little bit different now, because now we have a different loan balance. So now it's going to be equal to the new loan balance of the 3381. It's going to be significantly different because there's so few periods times the rate of 35 divided by 12 to get the monthly amount, not the yearly amount tab that gives us the 99. This is going to be equal to the payment minus the interest. And now that's the loan reduction or loan without the 3381 minus the 1 sixth 66. And that is our balance. One more time. We've got the payment is the same. The interest is gonna be that 1715 times the thirty-five percent divided by 12. The loan reduction is the payment minus the interest. The loan balance should be at 0 at the end of the loan term. The prior balance minus the reduction taken us down to 0. Now I'm gonna do this one more time. This time, I'm going to basically set it up so we can copy this down easily. So I'm going to delete these items right here. Let's delete, let's delete the whole thing like this and do this first calculation. Again, keeping in mind that I would like to copy it down without having to do it every time. So the payment is going to be that 1765. I want to copy that down, so I want to make it absolute because anything that's outside of the data that I'm working in, I'll typically need to make absolute because I wouldn't want that cell to move down when I copy it down. To make it absolute, I'm going to select F on the keyboard dollar sign before the HV dollar sign before the four on the interests. I'm going to say this is going to be equal to the 5 thousand times the Thirty-five. That 35 is outside of my dataset or the area that I'm working. My table in essence, it's in my dataset on the left. So I wanna make it absolute because I don't want it to move down. And when I copy down F4 and the keyboard dollar sign before the HV and three, you only need a mixed reference, by the way, but an absolute one works. It's kinda easier, so I'll use that. And then the loan reduction is gonna be the 1765. Well, hold on a second. They'll double-click on that one. Then I need to divide that by 12, which is a hard-coded number, no absolutes needed there, of course. And then the loan reduction is going to be equal to the 1765 minus 146. There we have it. And then I'll take the loan balance was the 15 thousand minus 21619. Nothing's from our data outside of our actual table. So I don't need any absolutes. I want those to move down as I copy down. So I'm going to say Enter, select those four cells, use our fill handle, grabbing the fill handle, dragging down. And we should get to the same result, the bottom of the amortization table being 0. I'm gonna make this our blue or blue color, blue and bordered. Selecting this, I'm gonna go to the Font group, make it then our blue which is right there. But if you don't have it more colors, standard, there's the blue. Excel is fungi YouTube channel blue. And then we're going to go to the drop-down and the, and there's the brackets. So then we've got this item over here. We'll select these and do the, do the blue and borders on that too. So that looks good. So that's gonna be the table that we're going to use now, we're looking at the 228. So that means that half of this interests than has been incurred, but has not yet been paid. So we're going to take that interests in essence divided by two. We're taking this divided by two and that's gonna be our adjusting entry that we should record in the month of March, even though it hadn't been incurred till April. Now you might look at that and say, well, that's a pretty small amount. Is it really necessary? It may not be because it might be in material and this case, meaning it's not going to have a material effect on the decision-making process. But you can get the idea. It could be material if the loan was larger and so on. So you can get and you could have different loan terms that are, are different installment loans and so on. So it can, it could be material. Of course, we want to get an idea of why you would do that and when you would do this, basically adjusting entry. So next time we'll talk a little bit more about the adjusting entry that would be in place. And then we'll record this adjusting entry to our financial to our worksheet on the left-hand side. 4. Adjusting Entry Accrued Interest Part 2: So I counted practice problem, adjusting entry, accrued interest, part number two, get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it, if you have access to it, There's two tabs on down below an example tap into practice to have the practice tabs starting out where we left off last time, the example tab, in essence D and an answer key. Let's take a look at it now. In a prior presentation, we put together our amortization schedule. Now we're gonna be using it to construct our adjusting entry worksheet. Remember that all adjusting entry entries, the as of the end of the period, end of the month or year, typically, our cutoff date being the end of the month of February. So we're gonna go back to the practice tab, record this out. To do so, I would like to see my journal entry information on the left close to relatively close to where we add our worksheet that we put the schedule together. So what I'm gonna do is hide some cells. I'm going to put my cursor on this skinny cell right here on skinny. And we're gonna go all the way to the right until we hit that amortization schedule and practice hiding stuff, hide this stuff and then we'll have to unhide it when we want to post. I'm gonna go all the way back over here. We've got the subledger, So we got the inventory subledger. Just a whole bunch of stuff. We're just gonna go all the way on over the payroll and stuff. Here's the first amortization and there's the second one. I'm gonna, I'm gonna put my cursor right to H S here. And then I'm going to right-click and hide all that stuff. Just don't delete it. Just hide it. It's hidden. So now we've got our schedule right here. You'll recall last time we made our amortization schedule, we're imagining that this loan happen on to 15. We're not going to make the next payment until after the cutoff date on 315. And when we make that payment, that's fine. But that that's not going to pick up the fact that we accrued 15 days of interest during the 15 days of the current month that we haven't yet paid and we're not going to pay until next month. So that's what we're going to pick up here. We're going to pick up half of the interests. So the interest portion of the payment. Notice what I'm doing here, by the way, I'm not taking the full payment and taking half of the payment because I haven't incurred the payment isn't isn't what we're what we're looking at here. We're looking at the expense that has been incurred, the amount of the principal were paying down is just simply going to be paying down the loan balance, which is a balance sheet account. The interests is the expense that we've incurred that we have not yet paid. That came out to be the 146, so that 146.5 of it was incurred before the cutoff. So if I take half of it, it comes out to about 73. Now you're probably saying, hey, look, that's 73 is awfully small in terms of a dollar amount. Do I really need to do the adjusting entry? If it was that small, it probably wouldn't be the material a very material, in other words, relevant to decision-making on the financial statements. But the concept applies because you can imagine a situation, of course, with a loan with a lot larger and, or you had different kind of amortization schedules other than a standard fixed payment loan. So that's gonna be the idea of it. And again, some of these adjusting entries, you might look at them and say, maybe I'm not going to be on an accrual basis, maybe I'm more on a cash basis or a tax return basis. And you're saying I'm a smaller company and maybe I don't need to do some of these adjusting entries. Possibly you don't in those cases, but some of them you will not be able to avoid. So this one is an accrual type of entry, meaning we're recording something even though the cash had not been paid, were recorded at when the income or the expense was incurred, when it was used, which is an accrual concept. So we're on an accrual type of basis when we're thinking about this. And so just keep that in mind as well. We will also have a reversing entry that will be related to it because you can imagine that when we record this adjusting entry, it could throw off our normal accounting processes and then we'll do a reversing entry to reconcile that. So let's first do the first transaction. We're going to scroll to the right. Notice we have some hidden cells over here because we hid the reversing columns, which we'll take a look at later. And so we're going to record our transaction. It's going to be on to 28 as all adjusting entries are, you probably want to put in the memo somewhere that it's an a DJ or adjusting entry. So I would label it as in a DJ. You can possibly put it underneath, like here on the date range or maybe if it wasn't Excel, you can add a note to it to give more details. So you might then say this is going to be the interest expense down here. And then we're gonna go to the interest expense which is way down on the bottom because it's gonna be an other, we put it in the other category on our financial statements, which we will see more clearly when we do our financial statements at the end of the adjusting entry process, the other side then going to the payable, we don't have an interest payable, we're going to have to add it. So hold on a second. Couldn't just go down here. We need an interest payable. It's gonna be a liability account. So it's gonna be, let's put it above the loan. I'm gonna put it in alphabetical order within the liability accounts because that's possibly how it would show up, most likely. Accounting software, if you weren't using a count numbers. So if you're working, for example, in a worksheet and you're working along with someone that uses the accounting software and does not use a count numbers. It's nice to try to stack your worksheet up if you have to add new accounts in such a way that it'll be the same once they add those accounts to the accounting software. Because then it'll be easier from year to year because you'll have the same ordering of the accounts. So we're in the liabilities. And then within there it's gonna be in alphabetical order because I'm not going to be using account numbers here. If you have account numbers, you have more control over the ordering of it. I'm going to select these four columns or four cells, right-click and insert. And then shift these cells down. And so this is going to be then we're going to call it in tourist. Hey, now you can also call it, you might call it interests or accrued interests, because the interest has accrued upwards and you have not yet paid it. So that's another name that you can call it. And sometimes people, some people might think that's a more professional name, that's kind of a more maybe possibly traditional name. I like to use the payables for the liabilities. Because to me, I think it's kinda universal that it's easy to hear all payable. That means it's a liability in my mind. So I think that to some people might be more clear, if not as possibly sophisticated terminology. So pick your, pick your terminology and record it accordingly. And then we're gonna go up top and say this is going to be the liability that's gonna be going to the interest payable. And we will indent over here, go into the alignment. And in dense I'm going to indent the liability. I'm going to put both the debits and credits in place in my worksheet. But when I record it over here, I'm going to condense them down representing the credits, which is a negative number, which as we discussed when we put the worksheet together, logistically, is often much more easy to do. So we'll see that as we go. Hopefully, you will be convinced of that This is gonna be equal to, I'm going to pick up the amount from the table. It's gonna be that 146 divided by two, which is about because we rounded, I took the pennies off 73, although the pennies are still in there because if I was to add them in the number of group, it's calculating with the paintings, but I'm going to remove them. And then we're going to say this is negative of that number. So I'm putting the credit over here as a negative and in the credit side and we're indenting, look at how redundant that is to do all of those formats to indicate it's a credit, we only really need one format to indicate it's a credit, either in the credit column or indented or a negative number. When we post it, we're going to choose the negative to indicate that it is a credit that making the formulas a lot easier for us. So I'm going to post it out now. One more thing, just note that you might want to put notes into your worksheet in Excel is great to do that. So if you were to say this is an adjusting entry, I'd like to comment on that. I would like next year for people to be able to determine what I did this time and why. And you might put a more detailed note up top in order to do that. So you might like right-click on it and then I'll put a note down here, a note. And I'm going to say that to record like 1.5 month interest on loan, core Dean 2M or causation table or something like that. And you might say, I don't need a note because I know, I know what I'm doing and so on. But remember, a lot of times you're gonna be trying to, trying to transfer your work to somebody else, possibly that's gonna be working for you. And you'd like to make it as clear as possible and train other people to make it as clear as possible so that whoever is picking up the work next year can kind of pick that up. So if you could put the note there that can be useful. It's also useful if you are going to give this to the client and try to convince the client to enter these adjusting entries into their system in some way, shape, or form as well. So it's often a nice thing be able to do and a note doesn't get in the way too much as you're just working around the worksheet down here. We're then going to post it. We're going to say, okay, let's post this thing. We were going to post it out. So this is the interest expense. I'm going to scroll down to the middle column now. And notice in the middle column all I have to use is equals and pluses. I don't need to use any negatives. So I'm going to say equals and post to that 73. That brings the balance and the interest expense from five-ninths up by 7326694, add a balanced now on our debits and credits, notice how nice and easy that is to see. And then our net income was at income. This is, this is going to be the revenue minus the expenses of 4770. Then the interest went up by the 73, bringing down net income to the four-sixths 97. Notice how now fairly easy these formulas are relative to a few. Set the worksheet up differently with like six columns to see. Then the other side is on the payable. So the payable is going to be down here. Interest payable. We're in a M6 equals, and we'll scroll up to that payable, to the seventh three zeros, 0 increase in the payable to 77 D3. So there we have that. So now next time what we're going to end up doing is we're going to, we're going to have to reverse this because now note. That now that I have this into the system, it's going to make it a little bit more difficult for the accounting department to record their entry according to the amortization table. Because what they would normally do is just record the entry according to the next payment that happens. And now I've got this kind of adjusting entry item in place that can confuse them to some degrees. So we're gonna go back on over and clean that up before we finish up here, Let's unhide some cells. I'm going to go from A0 to HU, A0 to HU, right-click and unhide. And so there we have. Now notice this reversing columns. We'll use that next time. Notice I added this extra column here for some reason which isn't, isn't doing anything of use or of note. And also notice that when I added the new cells, I added it in such a way that it didn't, it didn't get added to the hidden columns, which I need to be more careful of. One, I'm going to clean that up by deleting the AQ column, selecting the column, right-click on it and deleting it. And then to the new column I, the new cell I put in place is right here. So I need to adjust my worksheet and move these cells down to meet that new sales. I'm going to select these two cells, right-click and insert, shift these cells down. And so there we have it. And now I got to adjust my own my formulas to make sure they're picking up the proper amount so they look good. This one's going to be the 73 plus any adjustment right there. So there we have it. The rest of them look like they're copying down properly. If I scroll down, my totals, still look good. Down below my total here, looks good. My total here looks good. That looks good. And then let's post this out to the general ledger now as well. So I'm gonna go back on over here. I'm gonna go to my interest expense. That's way, that's the last account down below elastic count. So I'm going to go all the way to the right to post the interest expense. This way, all the way to the right to the last GL account, posting it to the G L there it is. And I'm going to call it an ADJ instead of the date, just so we can distinct make them distinct in our general ledger account here. And so then in B in 19, I'm going to post it here by saying equals go all the way left to the wall, left until I hit the wall. I'm going to do this with a formula. You could type it in there, but the formulas are going to make it easier if there's an error to figure out what happened, Enter. You can do that by putting your cursor on this item using these little buttons here to then see where that number came from. Those buttons are in the data area and they are they are, I'm sorry, in the formula area there, right here. I right-click and add them to my ribbon up top. So that's useful. And then we're gonna go all the way to the left and record the other side to the GL, recording it to interest payable. That's going to be in our liability area. The third liability, scrolling to the right, picking up the liability in the interest payable. So the interest payable, which I have to add that was a new account. So I'm going to have to put it right here in the GL. So I'm going to remove this line. I'm going to copy the ones to the left of it from the skinny to be S Control C. I'm going to put that in, inserted in this skinny right-click and insert the copied cells. I'm going to delete the activity on them. So I delete the activity. The second, the second one I don't need at all, so I'm gonna remove it. I'm going to paint it to be a normal cell and then delete it. So I'm gonna go to the paint brush and just paint normal cell on it and then delete it. And then up top I'm going to record in cell B, u equals left till I get to my Gmail account. And I'll pick up then the amount or the account of the interest payable Enter. And then I'm going to call this an ADJ again instead of a date so that we can identify them distinctly from the other items on that date. And then a bv five equals left until I hit the wall. And then I'm going to pick up that account payable and enter. Now notice that post into the G L is something that you might not always do when you do the worksheet because you're focused in on kinda like the worksheet, but it is something that GL is. You do want to add it to the, to the book, the actual books. Oftentimes, after you're done with the worksheet. So we're going to add them as we go. We also need to add that account into our check figures. Up top. Double-click in the check figure, go into the end of it and same plus putting the cursor back down and picking up that GL account, the GL Account of the 73 and enter. So that puts us back in balance here. So that looks good. There we have it. So again, next time we'll do the reversing entry and we'll talk about how this adjustment could mess up the bookkeeping department and y then we would want to possibly do a reversing entry as the first day of the following period. 5. Reversing Entry Accrued Interest: Excel, accounting practice problem, reversing entry, accrued interests. Get ready because we're about to excel. We are in our Excel worksheet and prior presentations, we put together the worksheet from a blank sheet now continuing to enter transactions and to wait if you have access to it. There's two tabs on down below, an example tab and a practice tap the practice tab starting out where we left off last time. The example tab, in essence being an answer key, we're gonna be staying here on the practice tab this time in the prior presentation, we entered a transaction to record accrued interest or interest payable. Now we're going to be doing a reversing entry. So to consider that, let's first go back on over and see where we picked up this information. I'm going to hide some cells from the AIS column all the way over until we get to our amortization table we put together. So I'm going to scroll all the way to the right past the GL, the subledger for inventory, the payroll stuff. And then here's my one amortization table, and then I want this second one so to HV, right-click and we're going to hide all that stuff. Hiding that stuff I can hide H2, I don't need h ether hiding h, whatever that h something. There we have it. So now we have the information on the right-hand side. We basically said that half of this interests should have been recorded in the month of February, even though it's not gonna be paid until March because 15 days of it had been incurred in February, noting that that's a fairly small amount. But again, we can see the concept from a conceptual standpoint that it could be a larger amount depend on the size of the loan and so on and so forth. So that's what we did last time. Now, what we would like to do now is reverse it. What we're thinking of now is that going to mess up than my accounting department as they go through the normal process. Now remember what the accounting department is going to do with relation to the loan will typically be recording the next loan payment, which will be this item here. What's the journal entry? When they do that, they would normally say, Okay, I'm going to say cash is gonna go down and then I'm going to have interests, interest, expense, which would be a debit of normally the 146. And then we're going to say, we also have then the loan balance going down. So loan payable would go down by the amount of the 1619 about, and then the cash would be going out for the negative sum of this amount, that would be the payment. So that would be the normal journal entry. And that's a little bit it's difficult enough given the fact that there's not just two accounts impacted and the interest and the principal will be changing as the loan payments go down significantly. But now we've got this loan on the books. So if I left, if I lift this accrued item on the books, the bookkeeper might say, that's kinda throwing me off. What should I do with that? You could tell them just leave it there. Just leave it there and we'll fix it next time and just record your normal item. Or they could say, well, I'm going to fix it when I do the entry. So then the entry would be changed, you'd say, okay, well now I've got this, I know I've got this In Tourist payable that I got to take off the books. It's on the books at a at a credit. So I'm going to debit it to remove it. And then I'm going to have the end tourists expense, which would be it was gonna be the 146 minus the 73. So that's the current period's interest that we would record. And then you've got the loan payable, which is going to be still at the 1619, and then the cache, which is gonna be the negative some of these items. So you can see we added a pretty significant level of complication too. Calculation that's already a little bit more complicated than it needs to be or often would be for just a cash payment typically because there's three accounts affected. And now you've got the four accounts that they would have to pick up at the point in time that the payment was made if they were going to account for this seventy-three dollars and then properly put in the current time period, the interest expense not being the full amount of the 146 because they only incurred 15 days of it in the current period and the other 15 days should be in the prior period. Now the problem is we don't want to make the data input on the accounting side as easy as possible. So what we would like to do is say, I don't want the accounting department to have to worry about that. I want them to be able to enter the checks as fast as possible. So I'm going to reverse it. Now when I reverse it, I'm not going to reverse it as of 315, which you might think I should reverse it as of 315 because that's when the payment is going to be made and that would mean we're on an accrual basis or closer to it basically, for a longer period of time. Or in other words, you could think on a perfect accrual basis, we should be accruing every day. How much, how much expense we have incurred per day. We're not gonna do that because that would take way too much work for the benefit we would have. We could say, well, I should reverse it on 315 because that's when the payment would be made. But we don't want to reverse it on 315 because that would mean that I'd have my reversing entries scattered across the next two months. So what we're gonna do is do all of our reversing entries as of the first day of the following period so that we know where they are at. It will result in something looking funny as of the first day of the following period and it will net out and be correct as of in this case, 315 when the payment is going to be made and we'll have that 15 day. It's kinda, kinda funniness looking at the books, but it's inconsistency with what we're trying to do, which is to make the data input as easy as possible and the bookkeeping side and still periodically, monthly in this case could be yearly in some cases, make the adjusting entries that we need to do for presentation purposes. So to do that, let's see what it would look like. I'm going to post this over into the for column on the reversing side. I'm going to call this, remember the date needs to be 31. This time. I'm going to say REV for reversing entry. Now you could put the debit on top of the credit on the bottom, since it's, since it's only got two accounts. But a lot of times with these reversing entries, especially as they become more complex, it could be useful to just keep the same top and bottom account here and then just change your debit and credit, meaning I'm going to put the interest expense and the credit column and take the negative of this number. I'm going to put the switch should be interest payable down here and take the negative of this number. And that looks a little funny from a debit and credit standpoint. Because now I got the credits on top. But it'll be it'll be a lot easier, especially when you get to longer journal entries when you're reversing something like an invoice, which we will do later than the tediousness of having to put the debits on top can be confusing. You can always then rearrange it if you would rather do so for presentation purposes in another journal entry. But what you would like to do is stack it up and the way that it's clear to you. And oftentimes if I just take these same top to bottom accounts down here, it's more clear to me that what has been done, even though it looks a little bit funny in terms of the credit being on top. So that's how I will do it here. We're going to say the interest expense is gonna be a credit. That should look funny, that should look unnatural because expenses usually only go up in the debit direction. But that's why this is a reversing entry. We're going to go over here and a P38, this is going to be equal to, and we will then pick up the interest expense of the 73 and enter bringing the balance on back to 0. And of course we're out of balance in the API column as well. Then we're going to take the other side interest payable. We will reverse it, so it will then go back down to 0. We're bringing it back down to 0 here. For the first day of the following period. There it is, so that brings it back down to 0. So what did we do here? We made it correct as of the year to date, as of the cutoff, February 28, and then we reverse it after the first day of the following period. Which looks funny. This looks, this looks okay. The interest payable back down to 0, that doesn't, that shouldn't throw the bookkeeper off. The bookkeeper might say, well, what does this negative expense account? That doesn't look right? And we're just basically going to say, that's okay. It's a reversing entry. It will net out it will be correct. In other words, once you make the normal payment as you normally would according to the amortization table on the 15th. Why? Because when they make that payment, which is going to look like this that we looked like over here. They're going to be having a debit of the 146, the full amount on the interests for the next payment that has accrued, but 15 days of it should have been in the prior period when they post this 146 here. Let's just do it real quick, just to see it. It will then net out to be the proper amount. So we're going to say right there, they want this 1146, it will net out to be to be the proper amount of of 73, of 73. And I need to adjust my formulas here because these formulas should be picking up just the activity, this cell right there. So I'm not going to sum up the activity, I'm just picking up the blue cell, the light blue cell. So then if I bring that across, it should be just the 76. We're out of balance because the other side would be recorded as well. We would have to also record to the loan and the cash. But you can see that this then would be great because now we got half of the interests was actually incurred in the current time frame. Now you might say, Of course, the next payment that we would have the same problem because it's not going to be made till 415, but the interest is only 99. There's a significant difference In the interests. And again, loans could be set up in different ways. So you're not going to have the same adjusting and reversing entries from period to period. But we would have another adjusting entry possibly for half of the 99 that wouldn't be paid until, until the til the end of the following period. So that's how the system would go. I'm going to unhide this item down here, or let's remove the added one here so we can say, okay, there it is. And then bring it back, we're back in balance. And then again, you might want to put a note. You might want to put a note if you're working on these worksheets so that you want to be able to see a supervisor can see it. And also, if you are whoever is picking it up next year, you'd like them to be able to see what you did. So I'm going to not have a comment. I'm going to say let's put a note and say reversing entry for prepaid insurance as of 31, something like that. It's pretty self-explanatory, but the notes can, can be useful in your worksheets for yourself in the future when you go back to it for people that are gonna be working with you. And also so that you can convince your bookkeeper to come up with a good system between what you are doing in the adjusting process and what they are doing and the bookkeeping process, and which journal entries they need to input and how they might need to put into their system that you had to put together to make the financial statements, right. And or do the tax return. 6. Adjusting Entry Accounts Receivable Sales: Excel, accounting practice problem, adjusting entry related to accounts receivable, sales, revenue, or income. You get ready because we're about to Excel. Here we are in our Excel worksheet prior presentations, we've put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs down below an example, tap it a practice to have practice tabs starting out where we left off last time. The example tab, in essence being an answer key, Let's take a look at it. Now. We're looking at an adjusting entry related to the sales item and it will be tied to typically the accounts receivable as well. So we're usually when we're thinking about sales, were thinking about the cutoff. In this case, we're thinking about, are there items that have been entered but entered in the incorrect periods specifically, here, we're looking at invoices that possibly were entered after the cutoff date. In other words, after February 28th, the end of the month we're working on and in March, but for which the work was actually done in the current month. Therefore, on an accrual basis should be entered in the system in this mug. Now you can imagine this from a normal work process. It's easy to think about how this could happen, especially with a job cost type of system. It could happen in a database program, for example, if you're entering information into the system, for example, a database program like a QuickBooks or something like that, would record the revenue on an accrual basis when you enter the invoice, remember when you enter the invoice, usually increase the accounts receivable. The other side then go into sales or revenue at that point in time. It's done that way because the invoice is the form typically closest to the point at which the revenue was actually earned. But it may not be actually the same time period that the revenue was earned. For example, if you have a job cause type of system and you're basically building out for your time or the time of your staff. If you're a CPA firm, a bookkeeper, or if you are a law firm, then you gotta have the weaker so that they're going to work at least two weeks or a month. And then you're gonna be billing for the time that they worked on the time that they did. And that means you'll be building later on after the work was done. So it's quite possible in that case, then just send the invoice out in this case in March after the cutoff date when the work was actually done in February. And theoretically then you should be pulling that revenue back into the period in which it was earned, even though the invoice was not entered until March. So that's gonna be the idea. Now, if you add inventory in on this, which we will do, then you'd think it'd be more likely that the revenue would be in the proper period, but it's still could happen in that the invoice is gonna be after the cutoff date. Now, when is the revenue earned? If you have inventory, typically when the inventory is changing hands is now owned by the customer as opposed to you as the seller. So there you might be looking at shipping documents for example. So we're going to use the as if we sold inventory and the invoice went in place in the following period because it's a more complex transaction, even though it's easier to kind of visualize that kind of problem happening when you're talking about like a job cost system. And then we'll, we'll record the adjusting entry. Now, you can imagine this as well. You can say, well, what won't that completely mess things up because the invoice has already been put in place in March. And if you enter an adjusting entry which basically records it again as of the cut-off date as of the end of February in our case, by the time March gets here, it will have two times. And that's true. So we're going to have to also do a reversing entry so that that does not happen. So we on the adjusting side are gonna say I'm gonna do an adjusting entry to make the financial statements correct. Income proper as of the cut-off date in accordance with the accrual basis, this invoice should be included in there. And then I've got to make sure that I do have reversing entry and line it up so the bookkeeper doesn't have to make any adjustments. Now, you also might say, well, why don't I just take the invoice and like the QuickBooks system for example, and just adjust the date and just read date it back before the cutoff. You could possibly do that, you could, but you don't really want to mess up the accounting system for the accounting department. They're doing a system that works for them on a job called system or whatever they're using. They're basically if they're billing every week or so and they have a set date on which they do the billing. You don't want to just go in there and say I'm just going to adjust the due date back back a day. You don't usually want to change it. What we wanna do is then do another entry and then show the audit trail as to why we are doing it. And that's gonna be the adjusting entry. Okay? So in essence, this is going to come down to just basically doing a entry that will be mirroring and invoice. So we're gonna go back to the practice tab. And notice if you were doing this in accounting software, although it is mirroring and invoice, we wouldn't actually enter an invoice. We would do it with journal entries just like we're doing here, because we want to be able to see it as an adjusting entry. So let's do that now. It's gonna be on to 28. To 28. And so the normal envoys journal entry would be accounts receivable. Accounts receivable would be impacted. And then we would have this sales tax payable. Sales tax payable, which we have. We're going to have to deal with another complication, which is why we want to deal with inventory. So we can deal with that complication of having to do the sales tax, which would also be impacted. And then the sales number down here, which would be impacted. And then we'd also have the cost of goods sold, cost of goods sold and the inventory. So this is our normal kind of transaction. If we're tracking inventory and having to deal with sales tax on a perpetual inventory method. I'm going to hit the indent here and these two are going to be indented up top. And then we're going to pick up the amount we're going to say, and this would be supported by the subledger. Now note if I go all the way to the subledger on the right hand side. We also want to consider what's going to happen with all these sub ledgers because now I'm dealing with accounts receivable and I'm dealing with inventory. If I go over here, we're selling, I think an ELP, it's gonna be an ELP. So you might say, well then I should adjust the subledger. And you could you could have just a subledger if you're reporting it. But really what I'd like to do is not mess up the subledger, not touched the subledger because again, I don't want it to mess up the behind the scenes stuff that's happening in the QuickBooks system. I would like to not have QuickBooks or whatever the accounting system they are using to have to see my adjusting entry and reversing entry in the subledger because that's gonna be confusing to them. So what I'd like to do if possible, is to note it as as just an adjusting entry, not have to post it to the subledger and then reverse it. So that will be back in balance subledger timeout to the trial balance after the reversal has happened so that my adjustment doesn't mess them up on the subledger. We're going to be imagining that we're selling this four hundred four hundred dollar ELP. So I'm not going to adjust the subledger this time. We're going to keep that in mind as we look at the reversing entry and what's going to happen with the subledger though. And I'm going to say that this is gonna be 400 cost of goods. The other side's go into inventory and then we've got the, the sales, which we're going to just say it's 500. The sales not being related to the sub to that subledger because the sales price might be a markup from the cost of goods sold, but it's gonna be different than the cost. We're going to imagine that the sales tax is a generic 5%. So 500 times 0.05 would be the sales tax. That means the accounts receivable would be the negative some of these items to plug number that we would be getting to the 525. We could of course, match this out to the transaction that we're imagining what's put in place by the system in March. We're just trying to do the same thing the system would have done if they entered an invoice in March, but do it before the cutoff. So it's as of the end of the current period, let's record it out. We're going to say the accounts receivable is going to be up top the AR. Let's go ahead and hide the reversing entries too, just so we don't get confused by that API to AQ, I get confused easy. And then right-click and hide. So there we got that. Okay, so now we've got the accounts receivable is an AIM. Six. Gotta get up early in the morning to record this one and the a M6 and enter. And then we're down here in the sales tax payable, down below sales tax payable, we're in a m equals. We're going to be scrolling over to that $20. That's going to bring the balance up to the 579 here. Then we're at the sales on down below. We're going to add the sales on the income side of things. We are in a M 2626 making our money and am area. So now we're at the 585451. Then we're going to record the cost of goods sold, the four hundred, four hundred down here. Cost of the goods that are sold in a M 28, a M twenty eight. Twenty eight. Twenty eight year older than that. M 28. And then we've got inventory. Inventory up there is going to be right here in am seven equals. And we're going to be picking up that one. That brings the inventory down to the 43543246. So we can see down here the impact on the net income is the difference between these two for this adjusting entry of $100 down here on the net income, Here's the total impact of the net income of our adjusting entries thus far. Now, let's post it to the general ledger. There's two points of concern. There's actually three points of concern here. If you were to do this entry in a journal entry fashion into like accounting software, one is the accounts receivable because like QuickBooks software won't let you, for example, enter data into the accounts receivable without Internet customers so we can track it on the subledger, just like with the inventory. I would like not to put it on the subledger because I don't want it messing up the subledger, but QuickBooks forcing you to do that. It's kinda nice most of the time because that makes it impossible for you to basically are nearly impossible to not have your subledger tie out. So that's one area we have to deal with. The sales tax payable is kind of like an accounts payable account. So that's something that we often track within, like a widget type of thing in accounting software. So that's another area where I'm a little worried to post to the accounts payable because I don't want to mess up their sales tax accounts payable subledger tracker tool that they're going to be using. Then I've got the inventory which also has a subledger to it that we are going to be a little bit worried about. So we want to keep those three things in mind. So I'm going to record this one to the subledger So we can see it. So let's go and record this out. We're going to say let's go record it to accounts receivable first and the general ledger. I'm going to make this green. I'm gonna make this green. And then let's go to the right-hand side. We're looking at accounts receivable, which is going to be here. There's the a to the r, This is on to 28, were in BB, BB 19 equals left to the wall. The B19, and we got the 525 and B19. And that gives us the 227 O one that matches the 227 O one on the trial balance. We're out of balance on the general ledger. We're looking for the sales tax, the sales tax which is down here on the GL, on the trial balance, same order on the G, L. So we got the assets, we've got the liabilities, we're looking for the sales tax and the CC on to 28 sales to actually, I want to put this in there as an ADG, ADJ to show they're all going to be at the end of the month on to 28, but let's show it as an ADJ and then CD28 three equals left to the wall. Then we're going to be picking up that sales tax is that it should be at the 579. Now. 579. Let's go back to the AR on my way back here, just down the way it's on the way. It's not a problem. It's just on the way home. So we'll just do that little errand. And then we're going to have down here the sales tax at the 579. And then we've got the sales down here, the sales here. Let's go all the way to the right. Same ordering, sail all the way to the right. And that's going to be healers called that ADJ. We're in C T ten, CT, CT ten. It's fun to say CT ten. And then we're at 500. They are picking up the 500, bringing it up to the 58451528451 also should be on the trustee t to the trial balance. There is 58451. Then let's ungroup. These are blue of phi them, whichever way you want to say it, we're going to hit the drop-down. You can find that blue. What's gonna be in the more colors area standard? We're looking that blue right there. And okay, now we're doing cost of goods sold. That's gonna be like the fourth account on the income statement. So going into the right assets, liabilities, and equity cost of goods sold in CW, I see the W, It's right there, ADJ. And then we're in cx. Cx equals left to the wall, left to the wall, 400. So that brings us to the 45954 that should match what's on the trustee TB. Once again, once again, 45954. And Bruno vase mass pour five or we're doing the inventory one more time inventory that's gonna be up top. So that's number four. Scroll until the right, picking up the inventory, I think that's the one that's hidden down here at the bottom. Hidden down here. It's in Ba 31 instant the ADJ. So we're in BB, BB F31 left to the wall. And we're scrolling up, it's like a record from BB. Bb is record the blues BB BB King for 346. That should be on the inventory. Inventory for 346, we're back in balance again. Back in balance again. So then we got the subledger. Now note that if you're talking about accounting software, like QuickBooks, quickbooks might force you, even when you're not eating an invoice, to use a customer, again, we would like not to do that because it's going to mess up the subledger and the tracking of the customer balances. But we might be forced to. Now, if you are, then you might one, you might say, well, I would rather not do that and I might just make another accounts receivable account called accounts receivable to possibly setting it up not as an accounts receivable type of account, but rather as an other other current asset so that you can record your adjusting entry without messing up the subledger. That's one option. Another option would be to set up another customer instead of putting it in the actual customer that you're dealing with, so that it will be in the subledger, but possibly it won't mess up the actual customers subledger. And the third option is just to put it in the proper customer subledger and then reverse it so it'll hopefully go in and out. Okay, So let's, let's actually post it to the subledger. I'm gonna, I'm gonna go to the right. The subledger is all the way to the right. It's going to be dealing with the accounts that are going to be by order of customer. Now let's assume that it was like Mr. Anderson that we actually actually wrote The invoice for. We could put it into Mr. Andersen's item, but then in the subledger, you're always going to have this adjusting entry, which we will reverse out. But the fact that it's done with a journal entry sometimes makes it difficult to tie out the two journal entries together like you would with an invoice. It's hard to match them out. It might show us open, for example. You might go in here and say, okay, maybe I'll just, maybe I'll just make another ledger, make another customer just call it. This is my ADJ customer, a DJ customer. So don't tell the accounting department, don't worry about this one. It's actually related to that invoice for Anderson, but I'm going to put it down here On. This is gonna be my ADJ entry and then I'll reverse it. So it's not in the detail for Anderson, you don't have to deal with it when you're trying to collect on the invoice. So epi 17 equals left to the wall. And then I'll pick that one up all the way to the wall. So I'm gonna post this accounts receivable to it here. So now we've got this other customer that we just made up. And so it doesn't mess up the actual customer. Okay, So that is that when that puts us back in balance, because if I add up my receivables, I'm back and bounced 2022, 701. I still need to reverse that. But that will take care of that kind of QuickBooks problem that matches out what's on the accounts receivable here, then you've got the sales tax payable. Same thing. They might use a widget which tracks the sales tax. And I'm a little skeptical to post to the sales tax because I don't want to mess up there, which I don't think it messes up the little widget thing. But if you're skeptical on that, depending on what kind of software they're using. Again, you might create another sales tax payable account and say this is my adjusting entry account, possibly making it a sub account of the sales tax payable. So you can kinda collapse them together as one without messing up the actual account that's being used to do the little widget thing to track the sales tax. That's one method you might use there. I'm not gonna do anything different on this entry here. And then the inventories, the other one. Now, even QuickBooks doesn't force you, like they do with the accounts receivable to enter something into the inventory subledger. So if they don't force you to do it, I would not do it. And then I'm going to note, I'm going to say, I expect my inventory to be out of balance now by the $400 meeting, this one's at the 4346. If I go to my inventory subledger because I'm not tracking the fact that I decrease the inventory at this point in time. By the 400, I should record a 400 decrease. Here. I'm out of balance by 400. I'm I'm at the 4746 and the subledger. I'm okay with that. And I'm going to notify the accounting department that the subledger is not going to die out because we have the adjusting entry, but I don't want it on the subledger because it's going to be correct once the invoice is put in place and we're going to do the reversing entry. And so I would rather not have it kinda muddy up the subledger for the accounting department. So I'm not going to record it. There will be back in balance once we do the reversing entries. So next time, that's what we will do here. And obviously you might want to note here. You might want to put a note and say this is a note. This is a, D D, J for invoice entered in March when work was done in February, and it was for and let's say customer was Anderson but created new cars summer to enter ADJ entry and not mess up sub ledger or something like that, right? And that can help again, someone else that's doing this in the following year to know what to do without without having to pick your mind for for it, Hopefully. So then we're going to say this is an adjusting entry, a ADJ. And I think that is it will do the reversing entry next time. 7. Reversing Entry Accounts Receivable Sales: Excel accounting practice problem, reversing entry related to accounts receivable, sales, revenue or income. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs down below an example tap into practice tab, the practice tabs starting out where we left off last time. The example tab, in essence being an answer key, Let's take a look at it now. And the prior presentation, we put in an adjusting entry, which is kinda like a cutoff type of adjusting entry focusing down here on sales, looking at a situation where an invoice had been put into the system after the cutoff date in March. In our case, the financial statements being created as of February 28 for which the work was actually done before the cutoff date. Therefore, under the accrual basis method, we should be pulling that revenue into the current period. We're going to do that and did so not by going into the accounting system like a QuickBooks system, for example, and just changing the date on the invoice because that would kind of adjust or mess up the normal accounting process. But instead making a journal entry and adjusting journal entry as of the end of the period to put the correct balance in place recording the revenue at that point and the related other accounts that we're going to be impacted, including the inventory and the accounts receivable and so on and so forth. Now, of course, we have the problem that the invoice and essence, or at least a journal entry related to them has been entered two times? It's correct. As of the cut-off date, which is March 28, the end of February, but will be entered twice as of the time that original invoice was put in place. And therefore, we're going to have to do a reversing entry. Reversing entries. We always want to put in there as of March. First, that's gonna be a reversing entry situation. Now remember, as we enter the adjusting entry, we had a couple of accounts which were more concerned with as we entered, we had the accounts receivable, which has the subledger that we gotta deal with by customers. Some accounting software such as QuickBooks will not let you post to the accounts receivable without posting also to the subledger, something we would like not to do because we don't want to mess up the subledger for the accounting department as they're tracking the invoices by customer. But if we're forced to do it, we put we added another customer in order to look at the subledger. We also have the sales tax payable that had that has a widget oftentimes to track the taxable items when you're dealing with taxes in software that we want to be careful about. And then we've got the inventory, which also has a subledger most accounting software and not requiring you to enter when doing a journal entry to an item in the subledger. Therefore, we did not do so. We're out of balance from the subledger by the $400 until we do the reversing entry, which we will do this time. So let's go back to the practice tab. And let's first just think about the journal entry that would be put in place with a reversing entry. It's kinda like a credit memo is similar to a credit memo type of thing. Whereas you're basically reversing what would happen on an invoice. So the way you want to do that typically is to actually first look at the adjusting entry, of course, and then reverse it line by line. And again, I would not try to put the debits on top just like we looked at with the last reversing entry. But instead just reconstruct this thing top to bottom, and then just put the debits into the credits, reverse the debits and credits. So first let's go ahead and unhide some cells on the right-hand side so I could see my my unadjusted my reversing entries. I'm going to go from A0 to AS or right-click and unhide those cells. This is where our reversing entries will go as of the first period of the next the next month. And then I'll just reconstruct my entry down here. This is gonna be as of 31 because it's a reversing entry. We went to and put REV for reversing entry. I'm just going to copy the same accounts. I'm not going to try to re-frame them for the debits to be on top. Just going to say the same thing, just copying these on down, top to bottom. I think this is the easiest way to do it, especially with a longer entry like this. And then we're going to reverse the debits and credits. So this was a debit up top two accounts receivable. Now it's gonna be a credit. But that's weird. You've got the credit on top. That's okay because it's easier to see this way. It's okay. Then we've got the sales tax payable. It was a credit before, so we're going to debit it now. So I'm going to say negative of that number. And then we've got the sales. It was a credit before. Now we're just going to debit it. Doing the opposite thing to it. Cost of goods sold before was a debit. Now we're going to credit it, doing the opposite thing to it. And then the inventory was a credit. So we're going to debit it, doing the opposite thing to that one. So there we have it. And so now let's just record this out as I record it, I'm going to record it into the outer column on the right-hand side, into the reversing area. I'm going to select this one just to keep our eyeballs on it. We're focusing in on the top set of accounts. We're going to go up to the accounts receivable, the AR, make sure we're in the proper area, the reverse, and you could hide these first two columns so you don't mess up and post to the wrong column m. It might be, I won't, I won't do it now, but just be mindful that you could do that. We're gonna go over here to the API and just try not to mess it up. And we'll scroll down. This is AR Try not to mess it up. There. We have it. So it was at the 20 to 176 and then we increased it to the 227 O one and then we decreased it back down to the 22176. And then we've got the sales the sales tax payable. Sales tax payable is going to be down here. Sales tax payable will post it into this column API. This is going to be equal to, and we're gonna be picking up the 25. So we will pick up the 25. So we put the 25 in for the adjusting entry, bringing it up to the 579. Now we're going to take it back on down to the 554. And then we have this sales down below. The sales is going to be down here. We're in API 20 equals the sales line item Enter. So now we had we had it from the 57951, we credited it to bring it up to the 58451 and then we closed out the income the income accounts, you can see here they're closing out here to the equity section. So now when we look at just what is happening for the month of March, we've got a negative sales in there. Again, that looks funny. You're going to say, well, it shouldn't be a negative sales and sales should go up in the credit direction. But it will be correct as of the point in time that the invoice was entered because the invoice was actually entered some time in March. We're not going to try to reverse it as of the day that the invoice was entered in March to try to get it more precise because we want all of our reversing entries to be in there as of 31. But you can imagine this entry up top is the entry that was the actual journal entry for the actual invoice entered in March. Once this has been entered, the 500 here will net out to the 500th there, and it'll bring it back down to 0, which is where it should be because we actually recorded it and the current month when the work was actually done before the cutoff date. And the same is going to be with the cost of goods sold. So let's unblock this item up top or ungraded. I'm going to make it blue. Cost of goods sold is going to be down here. Same kind of thing were in API 28 is going to be equal to and we're going to be picking up than the 400. Now this one, we were at the cost of goods sold 45554. Then we brought it up because it's an expense to the four or 5954 before the cutoff, making it correct as of the point in time that we made the financial statements, then we brought it back down, which is weird to have a negative to cost of goods sold because it usually only goes up. And then now you've got this negative number and cost of goods sold because we closed out the income statement to the equity section. So if we're looking just in the month of March, we've got this negative cost of goods sold, which is funny looking, but it'll be correct once we actually record the normal invoice, which will be the debit to the cost of goods sold, netting out at that point in time to 0, making the actual recording in the period that it should be recorded because that's when the transaction was made or the revenue was earned, which is in the prior period. Okay, so that's the whole thing. Then we need the inventory. Inventory. Lastly. Lastly, the inventory is going to be in AP seven equals scrolling down and we pick up the inventory. So we have the inventory was that the 4746 and then we brought it down to the fourth 346. Then we brought it back up to the 4746 and then it'll go back down again. When the actual invoice was entered in March, whatever the date was in March. But as of now, at this point in time, we're back to where we started from. So now let's just review the subledger. We might not post to the subledger because because we're going to keep the subledger basically as of 228. But let's just consider the accounts receivable where we stand now, remember the accounts receivable, we kinda mirrored what you would expect to happen in the QuickBooks software, meaning we posted that first adjusting entry way over here to the AR subledger broken out by customer. We didn't put it into Anderson here, but instead, we put the adjustment down here to this customer. Then if I had the reversing entry verse reversing entry, we would record it right here and that would net out. Now again, notice the problem, like you might say, well, why don't I just put that in Anderson, it will net out and it'll be fine. Well one, it'll show up on Anderson's detail, which could be confusing when you're trying to run a detailed report for Anderson, the customer, on what the activity is. And then two, it's a little bit more difficult to match out the two journal entries in accounting software like you would with an invoice and payment. Meaning if you have an invoice, you make a payment connected to that invoice. The invoices now closed. So the journal entry can cause problems when it's not an actual invoice. I won't actually record it right now as the reversing because we'll keep it as of 228. And you'll note that as of the 228 date, we are at we're at the 227 O one. So if I go all the way to the left, all the way to the left, the 227 O one matches the year-end date, the 228 and then and then we're doesn't match the three. Oh, one because I didn't record the reversing entry because I'm going to keep the adjusting at the period end date. Then on the inventory we have a similar kind of problem. Notice the accounts payable. You could have a similar problem on the sales tax payable. I mean, because you have that sales tax widget, so you gotta be careful with that. You might make another sub account for that one, just so you don't mess up the sales tax widget that might be in an accounting software to help you to calculate the sales tax. And then down here, let's make this indented. We have the inventory. Now the inventory, even QuickBooks does not force you, even if you're doing a perpetual inventory system, to enter an item. When you enter when you enter like a journal entry, meaning if I if I did this transaction up top, which is mirroring what happens with an invoice. The system doesn't know what inventory unit, actual physical inventory, the unit of inventory to track with it because you're not using item to do that. So that's going to throw off your ledger enter subledger. And we're okay with that because because we're going to reverse it and then we'll be back in balance. So the inventory when I did the adjusting entry, we didn't adjust a subledger at all. So let's take a look at the inventory notice I'm at 4346 after the adjustment and then 4746 after the reversal. So if I go back to the inventory subledger, way on over to the right. Over here. You can see we're off from as of as of the end date to 28 by the $400 were at the 4746 which is the place that we should be after we do the reversing entry. So this subledger, I didn't enter the detail which would be a decrease here of the inventory for the adjusting entry. But we just left it off and then reversing it afterwards, we just left it off the subledger. So that again, it doesn't show in the detail, doesn't mess up the inventory tracking. And once we do, the reversing entry will be back and we'll be back in place. The subledger will basically line up At that point in time. So we're just going to leave this one as is, those are the things you got to be concerned with anytime you're doing adjusting entries related to like an account, anything that has a subledger like that, the accounts receivable, accounts payable, sales tax payable, possibly inventory, whether it'd be a correction of something or some kind of accrual adjusting entry like this. And you're then going to ask the accountant to put it into the accounting system. You got to think about, okay, how's the accounting system going to deal with the subledger and how can I make that as easy as possible, not to mess up the accounting department and have them angry at me because I did what I needed to do to get the financial statements correct. For presentation purposes, but then it messed up their sub ledgers and so on. So here's where we stand with regards to the trial balance for the year to date, the Indian period as of a cutoff date, and then our reversing entries as of 31, the first day afterwards. Thus far. Thus far. 8. Adjusting Entry Prepaid Insurance: Excel, accounting practice problem, adjusting entry, prepaid insurance, get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs on down below, an example tab and a practice tab, practice tabs starting out where we left off last time. The example tab in essence being an answer key, let's take a look at it. Now. We're gonna be doing a transaction related to insurance record in the insurance expense and prepaid insurance. Now, insurance is a little bit different than other types of expenses we typically are paying for. So for example, when we pay for the telephone expense or the utilities, at the point in time, we'd get the bill and pay for the bill. That's pretty close to the point in time that we actually incurred the activity, the use of the telephone and the use of the utilities. They had to actually track the usage in order to create the bill. Therefore, we expensive at that point in time that we basically pay it typically or when we enter the bill into the system. But when we talk about insurance, we're talking about something which by its nature and no matter what type of insurance, whether it'd be whether it'd be liability insurance, car insurance and so on. We're gonna be paying for it before we get the coverage. Noting, insurance can be a little bit abstract because you, because you might say, well, I don't really get anything unless something bad happens, I get in a car accident or something, that's when the insurance pays out. But that's not generally the case because you're getting coverage, you're getting the liability protection. But that liability protection is really happening in the future. So if you're paying for it on a month by month basis, then you might be able to get away with just saying okay, I'm just going to I'm just going to expense it at the point in time I paying for it. I want to stick with in essence, they cash-basis type of system. But if you're paying for like six months of insurance at 1 in time or like a year's worth of insurance, then it can cause you a problem if you were to expense it at the point in time that you pay for it. Because if you were to do so, then if you compare January to February and you're paid for a whole year's worth of insurance in January and 0 in February, it would skew the comparison because you would look like you had a worst month in January when in actuality you have this big expense that just paid for that's gonna be benefiting multiple periods into the future. Therefore, on an accrual basis, what we will typically want to do is put it on the books as an asset and then allocate it over the useful life. Note that this is the same concept that you might do in like furniture and equipment. So when we get to depreciation, It's really a similar kind of concept. You also might say, well look, insurance, I pay for it monthly or, and, or I'm on a cash basis. And therefore, I'm not going to worry about the prepaid insurance. You might be able to get away with that. You've got to deal with the pros and cons of it, then you just got to realize that you're looking at two different numbers and what's the impact when you're judging your performance and so on and so forth. And you also want to consider the tax applications. Do you have to do it for taxes? But it's a useful concept to understand because when we get to the furniture and equipment, you won't be able to get away from it, most likely because if you're buying large pieces of equipment like a car or a building, you have the same concept in play and the tax code will force you generally to put it on the books as an asset do the accrual type of thing for the same reason. Because when you purchase a large piece of equipment or a building, even if paying cash for it, you didn't actually get the benefit from it at the point in time you paid for it, you're going to be benefiting future periods and therefore, you should put it on the books as an asset, then allocate the cost over the life. And this could also apply to any other thing that's prepaid insurance to classic example because it's always prepaid. So every time you see insurance, that should be a question in your mind. Anything else that you might have a pre-payment amount if you prepaid rent or any other type of expense, then you got a similar question that should be running through your mind as to whether you should put it on the books as an asset and then allocate the expense over the useful life that the acid is going to be used for. Once you've determined that you need to have this prepaid insurance, the next question is, well, how do I do that? How do I set that up? And typically what you do is you say, I'm going to do the accounting process and not actually extensive at the point in time that we pay for it. Instead, we're just going to set it up to go to the asset account of prepaid insurance. So you can see that's what we did here. So that means every time you write a check to the insurance company, it should be automatically in like a QuickBooks system or something. It just automatically goes up to the prepaid insurance instead of going into the insurance expense. And then we plan periodically to go into this account and say, okay, how much that would be at the end of the month or the end of the year to determine how much time has passed in accordance with the coverage of the insurance policy and determines how much of this insurance has been consumed at this point in time and how much is has not yet. Been consumed. So that's what, that would be the general concept. Now, you might have some people like if you do an adjusting entries that just expensed the insurance and you have to adjust for it, then you just do the opposite. You say, okay, they expense the full amount of insurance. How much of it should be prepaid in accordance with the coverage of the policy for the amount of the policy which has not yet been incurred, and then you can adjust it that way. But if you're setting up the system, this is how you would typically want to set it up. Having every time they write a check it goes to pre-paid insurance. Okay. So let's go back to the practice tab. If this is the case and we look at our books and we say, okay, there's the 12 thousand. All we have to do at that point in time is determined how much how much of that insurance should have been consumed. We would look at the insurance policy. I'm going to imagine the insurance policy here was a twelv month policy which we paid $12 thousand for to make it nice and easy. And we incurred the first month, we're going to say in February. So we're imagining we pay for it some time at the end of January or February. And then we're going to basically have the coverage from February of the current period, 12 months out to the following February, the following period. So that means that we have one month that has passed as of this point in time. If we take that $12 thousand for a year's worth of policy divided by 12 to get the one-month that has now passed, it would be $100. So I'm going to say, Alright then let's do our journal entry down here. We're just going to call this on to 28. As of the cut-off date, we're going to be debiting insurance expense, which I might not have yet. I don't think I have insurance. I going to add insurance expense. It's going to be right under the banking. And note, again, if you're putting this into a software like Excel and you expect it to go into accounting software. You'd like to try to add the accounts as you expect them to be seen when you add them to the QuickBooks software. And also realize that just because you might want to look at the software to and see if they have an insurance expense that's in the software that hasn't been used and see what the name is because the report that you have might not have anything in insurance expense. And if you create another account, then you're creating unnecessary accounts when they actually recorded into there, like QuickBooks system or whatever. And when you do add accounts than if you don't have account numbers, it's generally going to be in alphabetical order within the expenses. So I'm going to try to keep that convention. So as the years pass, we don't have the expense accounts and all these different orders when we're trying to work on our worksheet. So I'm going to say Insert, we'll insert our cells here, shift these cells down. I'm just going to call this insurance expense, insurance expense. And we're going to say 0 here. And then I'm going to copy down the formula in the adjusting. I'm actually going to, I'm actually going to try to hide the reversing entries right now so it doesn't mess this up. Select these two right-click and hide those. So that doesn't get in our way. Notice when I do add an account, like if I was to add that account, again, I would want to make sure to pick up this other little cell over here so that it takes up the hidden cells up top. I sometimes forget to do that. But when you hide the cells, you want to kinda keep that in mind if the hidden cells are right next to when you're trying to insert something new. Okay, so now we can go up top and I can say, okay, and now we've got the insurance expense is going to be the debit insurance expense and the credit is going to be the prepaid insurance. Prepaid insurance. And I'll do the calculation. Let's indent down here. We'll do the calculation again, which is simply going to be equal to 12 thousand. The policy amount divided by 121 month has passed, which would be $1 thousand. So I'm going to make that the debit and the credit. So there's the debit and the credit. Let's post it out. Here's the insurance expense that's down here in insurance expense. Right there. Am 3AM 30. Scrolling over to the insurance expense, increasing it. That of course, increases the net income puts us out of balance. Then the insurance, prepaid insurance, which had 12 thousand in it, we're in a eight equals scrolling down to the 1 thousand and interpreting the 12 thousand down to 11 thousand, which makes sense because we're basically 1 thousand per month. There's 11 months left. So 11 times the 1000s should leave us with 11 thousand prepaid that we're going to consume as the policy proceeds into the future, continuing the adjusting entry $1 thousand per month, lowering the prepaid insurance as time passes and recording it into the current month. There's our adjusting entry notice. This one is a permanent adjustment, so we will not have any reversing entry related to it because it's a permanent type of adjustments. Then we're going to go up top and let's post it to our GL as well. So we got the insurance expense. So that's the new account. We're gonna have to add that new account in our g, l because it's new. It's gonna be over here somewhere in the in the general ledger. It's going to be right after the bank account. We've got this space down here already, just waiting for it. So that was yeah, that's right where it needs to be two. It's just perfect. So we'll copy this one. Almost like you planned. I didn't even plan that. You might think, well, that's because you planted, but no, I didn't plan it that way at all. That's just the way it happened. Then we're in da, da 14 equals. And we'll point this over to the expense account over here. Way over here, way over here, which is going to be the prepaid insurance. I'm trying to get the name. Not prepaid. This is just the insurance expense. Insurance expense. There we go. There's the name. This is an ADJ. We're going to call it ADJ, which is as of the end of the time period to 28, we're on beat and DB 17, dB Deborah 17 debit. So this is going to be equals left to the wall, and we will then be picking up the 1 thousand. There it is. We also need to add this account to our check number up top. So it's picking up our check number. Cheque number should be out of balance right there in that green, it should be not in balance. I'm going to double-click on it, all this stuff in it, plus it's a mess. But I know how it works. I know where everything is. Just like my desk. I'm not a mess. Everything's in its place, that thing. So then we're gonna be down here and we're picking up the office. No, not the prepaid insurance. The insurance expense in C, D C, D 24. Enter back in balance again. Now we also need to record that in this check number, which is tying out the GL net income numbers to the net income down here. So I'm going to double-click on this 4797 and add to the end of it's a plus. And this is the insurance expense. Let's get it right this time. Get it right, get it right there it is down here. I'm going to go to the bottom of it that 1 thousand and Enter. There it is. So that one's good because now we're up to 3797, that matches our net income on the year-to-date stuff down here. Looks good. Other side going to the prepaid insurance. That is the fourth asset account. So it's gonna be the fourth account in the GL asset accounts as well. So we've got the prepaid insurance and B, it's in B, bf, BBF. So we're gonna say this is an ADJ, be bf, five, BBF five, B&B, F5. We're gonna go back to the left, scrolling down. And then we'll pick up the prepaid insurance, bringing the balanced down from 12 thousand down by 1 thousand to 11, that 11 thousand should match out what's on our trusty trial balance as indeed it does. So that puts us back in balance with the GL up top. We're in balance on the trial balance down below it had an impact on net income. Here's our full impact. Thus far, our net income, 4770 goes down by the 9733797. Notice that a lot of these adjusting entries often has a decreasing, like a lowering effect on net income, which could be good or could be bad depending on what they're for. Meaning if you're doing this for taxes, that could be good. If you're doing this for if you're doing this for someone's trying to get alone than they're trying to look good because they're trying to convince the bank that their credit worthy and so on, and lowering their net income will make, make them unhappy. You can't win as the adjusting accountant over here because they want it to go up, up for when they want alone and down for taxes. And those are opposite directions that you can't, you can't go opposite ways at the same time. Generally. Let's go, let's add a note here. We can then say, let's add a note, say new node. And we're going to say this is to adjust for insurance expense. We had one month of 12 month month. Paula. See. So let's leave it at that, something like that. So you might want to put the actual amount of the policy, the date, the term of the policy, and then kinda how you calculated the one-month. Although we can also see the calculation right here. But you could put that into the notes to make it more clear to yourself in the future, to somebody else or to the next person that's gonna be gonna be doing these adjusting entries possibly in the following year or following month. So this is where we stand now with our adjusted balances thus far. Thus far. There we go. Thus is very far. So we're at the 3797. 9. Adjusting Entry Depreciation: Excel accounting practice problem, adjusting entry related to depreciation. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet. Now continuing to enter transactions in two ways, if you have access to it, There's two tabs down below, an example tab and a practice to have the practice tabs starting out where we left off last time. The example tab, in essence being an answer key, let's take a look at it now, recording the transaction related to depreciation, the actual journal entry, fairly straight forward journal entry. However, there are some more confusing to consider with regards to depreciation, including we want one depreciation accounts are multiple depreciation accounts. How do we want to be recording the property, plant, and equipment on the balance sheet and the trial balance. We want to net them together or be breaking out the book value for multiple categories. What should those categories be? Where are we going to get the subledger? Where should we store the data for the subledger? How should we be basically compiling our property plant and equipment over time? So let's take a look at a couple of those kinds of questions up top first, considering the furniture and equipment and the machinery and equipment. These are our two categories of depreciable assets that we have put in place. Now, note, first question is, what should the categories be for the property, plant, and equipment? In other words, should I just have one category that's going to basically have everything, property, plant and equipment being long term type of assets typically include buildings, machinery, furniture, and so on and so forth. Or do I want to be breaking them out into multiple categories? And if I break them out into multiple categories, then How, which are the category names that I should be using? And oftentimes you might be working with your CPA firm or tax preparer to help break out the categories that you will be using. Because the supporting data that you will often have with regards to property, plant, and equipment needs to be trapped in a subsidiary ledger similar to what we saw in the inventory or in the accounts receivable and accounts payable more similar to the inventory in that we actually have physical units of these items that we're talking about generally with the property plant and equipment. And we want to be able to track the actual physical units, as well as the dollar amounts that are gonna be put on the financial statements. Where are we going to track those? You'll note that we don't have a subledger that we put onto this worksheet. And that's because oftentimes you're going to track that in tax software. And the reason it's often useful for many companies to track it within the tax software is that the tax software is going to be required. You're gonna be required, in other words, to record the depreciation on a tax basis in the tax software. So that means they need the whole subledger in the tax software, breaking the information out by category to apply tax depreciation schedules. If you already have the information in the tax software, then you can also use the tax software usually to make a book depreciation schedule if you choose to do that as well. In other words, your question for small businesses is often, do I want to have a separate depreciation for book purposes versus tax purposes or do I want to just be on the tax basis to make it easier because I'm going to have to use a tax basis anyways. And either either way, the tax software is usually a good place to go to get that subledger that will be backing up or supporting the amounts that are gonna be on the trial balance. So the subledger might look something like this. This is just a mock subledger from tax software where we have our two categories. So now I can ask the accountant and say, well, what categories do you use? Does your tax software used as the tax code and essence used or you use within it in order to group the property, plant and equipment. And then I'll try to mirror my books in order to match the tax software in some way to make my adjusting entry process as easy as possible. And you could do that with just a total if you just wanted to say I got total property, plant and equipment and I'll just use the total that comes from the tax schedule and then you have less detail on your financial statements or you can break out by category. In this case, we've got the furniture and fixtures and the machinery and equipment. So I will then mirror those categories on, in my books. And then notice what we have the details including the actual items, sofas, chairs, recliners, and so on and so forth, machinery, we've got a forklift and the office printer and so on down in that area. We don't need to have that detailed information reported on our financial statements. So but we do need the backing, supporting data on the subledger. And that's going to make it possible for us to calculate the depreciation properly. It's also going to make it possible for us to do the disposals and sales properly, which is going to be important because it's tempting when you do the subledger, for example, to group some of these items together. If I bought the sofa and the chair in the recliner at the same time to put them on one lump sum into the subledger. But if I do that and then I sell something in the future, now my sub ledgers got this big one, lump sum. It's gonna make it difficult for me to make the sale because I mean, I have to break it out because I only sold a component of it. So typically you want the detail broken out item by item of the furniture and fixtures on the subledger. The subledger also often being done on the tax return. Also note. That large purchases like this are not things that happen every day within the business. So you can usually track it a little bit more easily than it might seem when you look at this long, kind of confusing subledger, because we didn't buy all this in one year. Oftentimes if you're looking at a long depreciation schedule and the stuff that we bought in prior years, we'll already be on the books from prior years and we're going to still be using it because that's the nature of property plants and equipment. Therefore, all we have to deal with are the new additions and the current year. Make sure that we provide those to the tax preparer, as well as the disposals that happened in the current year, making sure we provide that to the tax preparer so they can then update the subledger. Then we can use that to calculate the depreciation. So let's go back on over to our schedule here. Then, with regards to the recording of depreciation, remember that when we record these furniture and equipment and the machinery and equipment, for example, on the books when purchasing it, whether we finance it or pay cash for it, we put it on the books as an asset as opposed to expensing it at the point in time that we purchase it. This is an area that even if you're on a cash basis, you will not be able to get away from the accrual method in this case because by us putting it on the books as an asset, that is an accrual method where we would expense it if we, if we paid cash for it, for example, if it was a Cash method, we can't do that because the tax code is going to force us not to do that and we wouldn't really want to just expense it generally because it's such an extreme case of something that we purchased in the current period that's going to be benefiting many periods into the future that typically we're going to be forced to move away from a cash basis to an accrual basis methods. So this is one area where you're going to have an adjusting entry pretty much no matter what, if you have property, plant, and equipment. Then the next question when you have the category setup, is that you could then record all of your accumulated depreciation is a contra account into one account, for example, for even though you have different accounts for the asset accounts, you might just say, Well, I just want one accumulated depreciation account. That means that you're not going to have the book value per category, but you'll just have the book value for the total property, plant and equipment. And you can get that kinda like from the bottom line of your schedule. And that would make it easier but gives you less detail. Oftentimes, people will make an accumulated depreciation account for each of the categories of property, plant, and equipment, allowing you to then see the cost, the amount that has been depreciated, the difference between the two, between the book value. So that's what we will do here. Then the other question is, well, what about the expense side of things? You could just have one expense for all categories of depreciation expense, which is again, the easiest thing to do. Or you can have multiple expense accounts that would then be by category. And the expense, oftentimes more people might just say add, just need one expense account, the expense side of things. But you could break that out as well. We're just going to use the one expense account here. So that's some of the options that you want to basically be considering when you're thinking about the depreciation. Then just note that the recording of the adjusting entry is gonna be very similar to what we saw with the prepaid insurance, except that we have this other account being a contra account. So in other words, when we put the prepaid insurance on the books, we said we're going to put it on the books as an asset instead of expensing it. Because we have this prepayment that's gonna be benefited in future periods. That's the same for the property, plant and equipment, same kind of concept. And then once time had passed, once we consumed part of the insurance, we then reduced it with the adjusting entry and recorded the insurance expense down below. We're gonna do a similar thing here, but then we do something funny. We don't just decrease like the furniture and fixtures, for example, itself, we create a contra asset account, which is an asset account that decreases the asset balance, has an opposite, has a credit balance to it. And then we create that account. It's gonna go up in the credit direction and in the other side's gonna go to depreciation expense down here. So that's a little bit different. You might say, well, why don't I just why don't I just credit the furniture and fixtures directly, given us the book value directly then and then debit something like furniture and fixtures expense. We could do that, but one reason we don't is because we're trying to say, Hey, look, this is an estimate. This is an estimate. I don't know how long the furniture and fixtures will last. I really don't know exactly what the value of it is at this point in time. All I'm doing is allocating the cost over what I estimated the useful life to be. And therefore, I'm going to put it on the books at the cost and make another account estimating the decrease, allocating the cost over the useful life. And you could see both that detail and you can then subtract the two out to get the net book value. So it gives more information, although it's also a bit more confusing and convoluted to do that. And that's often the trade-off that you will have. Between more data and more confusion. Noting that we're gonna go back to the practice tab and we're going to enter the journal entries, which are pretty straightforward. We're gonna go down here and say this is as of 228, we're going to debit depreciation expense. Depreciation expense, which we don't have. I've got to add depreciation expense. So let's do that. Let's add depreciation expense. That's going to be an alphabetical order. I'm keeping it in alphabetical order. It's going to be after the the bank service charge. I'm going to select, I'm gonna make sure I select over here to this skinny cell because I have hidden cells. And I want to make sure that my, my other columns over there, which are going to be the reversing entries, are fixed as well. They're going to move down as well. So I'm going to right-click on those items and insert, shifting the cells down. And then we're going to name this depreciation expense 0, starting point and endpoint in place. Then let's record this out. We're in a F15 equals the depreciation. And then the other side I'm going to go, I'm going to start out with the depreciation, accumulated depreciation for the furniture and equipment. So this is going to be the accumulated depreciation for the furniture and equipment. I'll indent that second account, alignment and indent. Then we'll go over to our table. This is, we're imagining from the tax software. And again, this is the book depreciation. You might then have tax depreciation as well. The software can give you tax depreciation and you might determine that you want your books on cash depreciation on I'm sorry, not cash depreciation tax or tax depreciation schedule for tax purposes. And you might determine that you want your depreciation on a tax basis to make it easier. But we're going to use the book depreciation here. So we're going to assume this is our scheduled to record it. We've got our total assets lining up to 98 thousand furniture and equipment that should match what we have here. So we've got the 98 thousand in that category. That looks good. And then we had the prior depreciation, was that these 7,500. Now we're going to record the current period. Now notice that we're doing this problem on a monthly basis. So I'm going to pull up the trusty calculator and the depreciation schedules and Taxol two are typically designed to be on a yearly basis. So we're gonna have to take the yearly depreciation over here and break it down to the monthly depreciation for one month. I'm going to take that actually two months and pass now January and February. So 14001 divided by 12 times two will give us our 23332334 about. So I'm gonna go back on over here and say, all right, let's put that in place. We're going to say this is going to be 2334 about on the debit and the credit side of things, recording that out depreciation expense on down below. So the depreciation expense will be right here. This is going to be equal to picking up the 2334 and then the other side. And notice we were at before the 7,500. That's the beginning. That's this depreciation in the prior periods, 7,500 current period, and we're only picking up then the two months of depreciation. So then I'm going to go into the accumulated depreciation. This equals the credits of the 233 for bringing the contra account from 7,500 up 23349834. So now ending balance, this is what we purchased it for. This is how much we have allocated out to expenses. The differences between the two is the 88166 that being the book value. Then we're gonna do this again on to 28 to 28, depreciation expense. Same debit. Notice that we didn't make too depreciation expense accounts for the different categories, you could do that, but we're not as concerned down here as making the categories up top. So it will then be broken out on the balance sheet. So I'm gonna go up top and say the second account is accumulated depreciation for the machinery and equipment, debit, or seeing that. And then we're going to say that's this category down here. We have these two items in it. It adds up to a total of 5 thousand for the total cost that should match what's on our books, 5 thousand here. And then we had the current depreciation at the 833. That's a yearly amount. We got to break it down to two months. A33 divided by 12 times two gives us the eight of the One, 39, about, 139, about. So let's scroll on down and put this one, 39 on the debit and the credit. So that looks good. Then we're going to record the depreciation expense on down below in a M 30 somethings in it. So I'm gonna say F2 plus F2. I didn't hit the other F2, F2, and then pick up that 139, Enter. So now at the 2474 and then the machinery and equipment up top, accumulated depreciation is an am 13 equals scrolling down, picking up that 139. So there's the 139 there. So now this was on the books. We bought it for 5 thousand. We have we have expanded. 139 in the form of depreciation, the difference between the 21486. Now this, when we do the actual financial statements and break this out on the balance sheet, we also have some confusion. This is where the most confusion is in terms of how do I how do I record this or how should I group this is how it, what kind of indenting should I have? How should I share the book value? Should I combine accounts together, contra accounts and the acid area. So often a place of confusing when you're putting together and thinking about how you want to display the balance sheet. So we'll think about that when we put together the financial statements. So that's that. And now let's record it to our general ledger. I'm going to record both of these at the same time. I'm going to record the depreciation expense account first, so I'm going to make that green. And we got it, we got that down here. So depreciation expense, I think we have to add it to so it's going to be after the bank service charges on the right. So we're gonna go all the way to the right and say, bank service charges is there. And we need to add Do we have any blank spaces that I should I should take care of? I got this blank space over here, I noticed, but it's it's kinda far away. Let's, let's go ahead and fill out the blank space. So I'm gonna, I'm gonna try to move this blank space over so I can fill out the blank space. So what I'm gonna do is I'm going to say this one right here. I'm going to cut it. You could just kind of grab it and move it. It's the same thing. Just say Control C or cut. And I'm gonna move that right here and Control V paste. And then I'm going to move this one up. I'm going to take that one, say Control X, Control X, control C cut in it, and then Control V paste in it. Let's take this one, I'm going to say Control X to cut and Control V paste. And then this one needs to move up. I'm going to say Control X, cut, control the paste, and then this one needs to be moved down there. I'm going to say Control X, Control V, paste. This one needs to be moved up. So I'm going to say, isn't that Where's my bank service charges? The bank service charges should be after the cost of goods sold. I'm actually going to move this one over. I'm going to say Control X cut, put this one right there, Control V. And then I'm going to put this one up top, Control X and control V. There. We got it. So now it's gonna be after the depreciation. So I'm going to copy this one to get the formatting not, not control X, control C, and control V to copy the format and delete this stuff in it. Delete the content. And then we're in cell CW 14 equals I'm gonna go left until I get to my depreciation account. So all the way on the trial balance to Trustee t, to the B trustee trial balance. Picking up the depreciation expense. Looks good. The dates can be we're going to call it an ADJ. And then in CX 17 equals left until we hit the wall. And then scrolling down and picking up that 2334. Then underneath that we have another ADJ and Cx equals holding the left arrow down until we hit the wall and then scrolling down to that second one. So there we have it. So now we're up to the 2473, that 2473 should match what's on the trustee TB trial balance. And so does that is that what is here? 2473. Looks good. We also have to add it to our check numbers up top because we added that depreciation. So I should be out of balance here. And this one, I need to fix two. So I'm going to double-click on our check figure, go to the end of it and say plus, and then put the cursor back down and this big mess of a check fig, the check fig. And then we're gonna go on down and say this is going to be the depreciation expense, the 2473 Enter. So now we're out of balance there. This one is to check figure matching the GL net income to the trial balance net income down here. So I need to add it there too. I'm going to double-click on it, go to the end of it and say plus, and then scroll over to that depreciation expense again and add that to the check fig. Check fig. So we're going to, where did it go? There it is. I went too far. You've gone too far this time. This time you've gone too far. Go back. There, we have it so that one that 1324 matches matches the 1324 down here. That, that looks good. Now we've gotta record the other side to the AAC, C, D, three. So I'm going to ungroup unify these two. And now I'm focused like a beam of laser on this one. I'm like a laser beam focused. Here we go. Accumulated depreciation. That's going to be this account right here for furniture and fixtures. So we're gonna go to the right green account. Right. Furniture and fixtures were in the ACC D pre. This is an ADJ entry. We're in the J 17 equals left until we hit that wall, scrolling down, we're going to pick up the ACC de Pree furniture fixture, bringing the balance up to the 98484834 should match what's on the trial balance here? The 9834 right there. That looks good. And then we'll record the last one down here. I'm not even going to make it green, I'm just going to green of five, this one or blue or PHI it. And then this one, the ACC machinery and equipment, That's the last green account on the trial balance last screen account therefore as well. If I put it in order, which I had that last one out of order, but if it's an order on the GL, it should be the last screen account to the asset account we're in bn muy Bien. 1717. Hippies are just being man. But I wanna be muy, muy Bien. That's what I'm talking about. So we've put that in cell B and B in 17, bringing the balance up to the 139139, scrolling back on over to the left. And we've got then we've got then R13 nine there. So I think we're tied out. The check numbers are checked out and we're good on the down low down here. So there is our trial balance. This is where we stand at this point in time. If we do not have any reversing entry related to depreciation because it's a permanent a difference. Note that we do have a significant impact on the financial statements from recording depreciation. And so that's one that oftentimes could either make people happy or unhappy, depending if you're talking about taxes, we usually want to look bad. Having net income go down significantly to lower taxes. Or if they're trying to get a loan or something like that when they're trying to look good or trying to impress investors and stuff, where they're going to say, Do we really have to depreciate? I didn't pay the cash. Cash didn't go out, depreciate, but you can't win as the bookkeeper. But the accountant or whatever. There it is. There's our net income. 10. Adjusting Entry Unearned Revenue Customer Deposit: Excel, accounting practice problem, adjusting entry related to unearned revenue or customer deposit. Get ready because we're about to Excel. Here we are in our Excel worksheet. In prior presentations, we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs down below, an example tab and a practice to have the practice tap starting out where we left off last time. And the example tab in essence being an answer key, let's take a look at it. Now. We're focused on the unearned revenue. And if you've seen unearned revenue in accounting classrooms, we're gonna be doing an adjusting entry that looks a little bit different. It would traditionally be recorded because we're trying to mirror what is often done in accounting software. Let's see if we can explain this in a bit more detail before we move forward here. Now note that the unearned revenue is often one of the more confusing adjusting entries because it's a type of account that you won't see all the time. It's gonna be the type of account that's gonna be there in place for certain types of industries. Types of industries that are gonna get paid before they do the work. In other words, usually you're going to do the work at the same point in time. Think cash register, for example, making the sale within the store, like selling a guitar within the store. In our case, you get paid at the same point in time, or you do the work beforehand. You can think about like a law firm, bookkeeping firm, or something like that where they build out the time that they had worked for and then they're gonna get the money at a later point in time. Some people, however, get the money beforehand. And so certain types of industries will be more focused in on the unearned revenue. Although those industries are more rare, possibly they're increasing in nature however, because the classic examples of magazine, company or newspaper where they pay, you get a year's worth of subscription upfront and then you deliver the newspaper. Therefore, you get paid before you do the work. Computer applications are now often on a subscription model. So that's a growing area where you have the same kind of phenomenon getting paid first before you've actually earned the revenue. So traditionally than in a normal book problem, you would think you would want when you earn the revenue or get the revenue to increase unearned revenue here as get the money if you're in a normal subscription model. And then periodically you go through and determine how much of that money you have actually earned, lowering the unearned revenue, and then recording the revenue at this point in time. So normally in an adjusting entry for a book problem, what she would be doing is saying, Okay, how much of this unearned revenue? Remember if all of my revenue was from subscriptions, then I would set up my accounting system to just basically have the unearned revenue increasing every time cash was received from a customer. And then try to determine on a periodic basis how much of that revenue had actually been earned, moving it from unearned revenue than revenue at that point in time. Now that works great in theory, but there's a problem with this unearned revenue account in that you're trying to track the revenue that you have earned from customers and a liability account, which isn't a natural thing to do because normally when you're tracking payments from customers as well as what is owed, you do that in the accounts receivable account. In other words, if you're trying to design accounting software, you have a subledger for the customers supporting the accounts receivable account. And the customers are typically tied to the accounts receivable account. Therefore, when you when you get a prepayment or deposit, a little bit more difficult to determine when the revenue has been earned or invoice the client at a later point in time when you're using this liability account, which isn't typically tied to the customers instead of the accounts receivable account. What will often happen in practice and there's, there's different kinds of workarounds that you will see. But accounting software. Using accounting software, one method to deal with this and be able to track how much has been owed and tie-in out the invoice to the prepayment is to create a negative accounts receivable instead of a positive liability account. We saw that we talked about this when we did the data entry portion of this. So now we've got these negative kind of receivables in place. The reason that's useful if I go over to the subledger to the right-hand side, if I go all the way to these sub ledger, then what's going to happen is the total balance in accounts receivable is still a positive. But you have certain, certain people, certain customers that have these negative balances in their meaning. That's a prepayment, that's us getting paid before we did the work, instead of us recording it in a liability which is more difficult to then track in this subledger to, we've recorded it in a negative receivable, that which is often the case. You might see an accounting software. Now at the end of the period where we want to determine if there's still some of these negative amounts that are outstanding and do an adjusting entry for them. So the adjusting entry here is not is not going to be to recognize the revenue at that point in time, but rather to increase the accounts receivable to what it should be without having these negative or prepayments in it. And the other side go into then the unearned revenue liability account. So that's gonna be that's gonna be what we will do this time. And then when you actually, in our, in our scenario that we have here, we're saying we're going to say that we have a prepayment that took place for eg guitar, and that's another area that you might have a prepayment. We've talked about rental property where you might have a prepayment. Meaning if you have a rental, then you might get the deposit first, which is kinda like an unearned revenue acts in a similar way, you owe the money back. So it should be basically a liability. And if you are going to be having inventory that's custom made in some way and you have to order it or make some kind of custom formatting for it, then it's often the case that you're going to ask for a down payment upfront. And then at a later point in time, you're going to then give, give the item in our case, that custom guitar, the guitar that we had to order at the point in time that they want to reserve the guitar. They're gonna give us a down payment of some kind. For example, here's Smith guitars gave us a down payment. We didn't record a positive liability at that time. We've recorded that the negative receivable so that we can record it in the subledger here. And you can see this happened over here for Andreessen guitars. And then at a later point, when we deliver the guitar, that's when we would record the invoice. And the invoice is the point in time under this kind of system, under this method where the actual revenue would be created. So we would then post the revenue properly when the invoice is created. And then you would have this unearned and these two would basically net out at that point in time. This also makes it easier to take to take the prepayment and tie it to the invoice. In other words, if I look at the subledger here, I can see, okay, yeah, these two things net out here, but also within accounting software, like to tie this payment to this invoice, which again is a lot easier to do if the payment is in the same subledger as the invoice that is going to be made. So that's gonna be the, the basic idea of it. So if we go over to the practice tab, we're going to record this out. The general ideas. We've got these negative items in the accounts receivable. The accounts receivable is still positive, but we recorded some of these negative items that I'm going to have to search in the subledger and then I'm going to increase the accounts receivable to accommodate for that, to get it to where it should be. And then we're going to create a increase in the unearned revenue account. So to do that, let's do the journal entry first. I'll go to the journal entry down here. It's going to be at 228. We expect then unearned revenue to go up in accounts receivable to go up. So it's gonna be a debit to AR or accounts receivable, and there's gonna be a credit to the unearned revenue. That's gonna be our journal entry, straightforward journal entry, although again, it's a little bit different than you might see an accounting textbook due to that. Then we're gonna go to the right-hand side, looking up a subledger. Analyze the subledger to see if we have any of these deposits that are in there. Noting that once once the actual invoice has been put in place, then I'm not at a problem anymore. In other words, at this point in time, when I got the prepayment and I recorded a negative receivable, It's not exactly correct because I should have a positive liability, not a negative receivable, but once the invoice is completed, then I'm back in a good position. Everything is back to where it should be because I have a positive receivable. You don't have this negative receivable problem. If I have a situation where I got a deposit and then I had not yet created the invoice as of the cut-off date, the date we want to make the financial statements. That's when we have the problem. So we have a problem here with this Smith, Smith and I have a problem with the customer, customer one here actually these two net out somehow over here. And then we've got a problem with the Eric music. So that's gonna be the 450. So we've got the 450 from these two these two customers that we need to be dealing with. Now, the other thing we've got to consider is that what I need to do then reduce the receivable down to 0. When I reduce the receivable, if you're using something like QuickBooks software, they're going to force you to put something to the customer item. I don't really want to put something to the customer items here or here because I don't want to mess up the normal bookkeeping process to normal bookkeeping process works fine this way. It's just for display purposes. I need to make it correct as of the end of the period. So what I'm gonna do, instead of posting something to these items, I could either create another accounts receivable account that's an other current asset account that doesn't touch these items. Or I can create another customer which we did before and make it our adjusting entry customer. And just put our adjusting entries into this area so that we don't mess the actual customer detail up, especially in a situation where you've got this prepayment, which is a little bit more confusing. So let's do that. I'm gonna go all the way to the right and record this out. And the journal entry is gonna be fairly straight forward. It's just gonna be four that 450 for those two customers that have the deposit that had not yet been invoiced out for it because we haven't received the guitar yet. We're going to be debiting accounts receivable. So I'm going to go up top accounts receivable needs to go up, double-clicking on it, go to the end of it, and plus, scrolling down, we're going to pick up that 450. So accounts receivable goes up to the 23151 and then we're gonna go down to the unearned revenue. And this equals, and we're going to be picking up that 450, bringing it up to 450. So now for demonstration purposes or for presentation purposes, this is done correctly. Instead of having a negative receivable inside of this receivable balance, we broke it out to a positive liability. So then if I enter this into a QuickBooks software again, anytime I do something to accounts receivable, if you give this to say the accounting department say I need you to enter these adjusting entries. They're going to say, Oh no, you hit accounts receivable. I don't know what to do with that because it's going to mess up my subledger, So I don't want to enter that into my system. My system works fine. I don't care if you had to do what you gotta do to get the financial statements, correct. But if I enter that into my system, it's going to mess up my sub ledgers. So what we wanna do is if we have to enter it and we could make another account or an other current asset instead of accounts receivable type account. Or we can put it into this other, this other customer so that we don't mess up the actual customers. So when we post it to the subledger, that's what we'll do. So let's, let's post it out first. We're going to say accounts receivable. Let's post it to the G, L, to the GL first. I'm getting ahead of myself. I'm way back here, but then like myself is way up somewhere else because I'm ahead of myself. Adj were in BB. Let's record it in dB equals left to the wall. Scrolling down and four or 50. Now the total here is gonna be at the 22315123151, matching what's on the trustee trial balance and we're back in balance up top. Then we're gonna go down and say that we've got the unearned revenue is the next one. That's the last liability account in the trustee treat TB trial balance. So it's going to be the same in the trustee GL and the GL. I need some another word other than trustee does something GL. So we're gonna go over here to the GL. Last one, unearned revenue. This is gonna be ADJ were in CHF chest seven. Just seven equals left to the o. And scrolling down to the unearned revenue, you didn't earn that revenue. You didn't earn that revenue. You didn't earn it. That brings it up to the 450. That should put us back in balance, back in balance on the GL, back in balance again. And then down here we're at 450. So now we'll go to the subledger and do our subledger thing. She's like I said, if you didn't have to hit the subledger, then that might actually be good because this is one that we're gonna do a reversal for. We will reverse it out. This is just something that we want to make correct for presentation purposes, but then reverse it out so it doesn't mess up the accounting department, which is doing it in a way that works for them. So instead of decreasing these two items to 250 and the 200 here, I'm going to put it into my other customer over here and I'm going to just tell tell the adjusting department, Hey, look, this is my adjusting entry customer and it should net out after the reversing area. So hopefully that doesn't mess you up to bat. And this is an epi, epi 19 equals left until we hit the wall. And then we're going to scroll down to the and revenue. We're picking up the four or 50 Enter. So there we have it. That brings this down to 75. If I add up my customers and the subledger subledger, we get then this because I picked up the wrong number. Hold on a second. Something went horribly, horribly wrong. I picked up the wrong, I picked up the credit and I need to debit Dona. I need to debit Dona. Let's do it again. E p equals left to the wall until we now are going to pick up the correct number. This time for 50. That's what the check figures are for. That was a check figure demonstration demo test. Then I'm gonna go back on over here and say that we've got it looks like it matches out that 23151 is what adds up my customers. 23151. That should be what's on the trustee TB as well as the G, L. Let's check it out. So there it is. That looks good. Now, again, this is one of those ones that we want to reverse as of the first day of the following period, and we will do that next time. So this is where we stand at this point. Notice there's no impact on the income statement from this transaction. And if you did it, if you were doing a method similar to a book problem, there would be an adjustment because what she would do there, you'd be trying to determine how much of the unearned revenue has been earned. That wasn't our problem now, when we earn the unearned revenue under the method we're using, will know it because we'll actually issue an invoice. So that's not the problem. The problem is just simply the reporting between a negative receivable and a positive liability in this case. So this is where we stand at this point in time. 11. Reversing Entry Unearned Revenue Customer Deposit: Excel accounting practice problem reversing entry related to unearned revenue or customer deposit. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the Excel worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs down below, an example tab and a practice tab, practice tab starting out where we left off last time. The example tab in essence being an answer key, let's take a look at it. Now. We're focusing in on the unearned revenue. Now looking at the reversing entry last time we did the adjusting entry notice it's a little bit different of an adjusting entry that you might see in a book or texts problem. In which case, you would typically have the unearned revenue increasing when you record the sales for a type of industry where you get paid before you do the work. And therefore, the unearned revenue would just be simply increasing as a liability type of account, then you would have to determine periodically at the end of the month, presumably, of how much of that revenue has been earned, reduce the unearned revenue, and record the revenue down below. Now, in practice, we noted that we could have a situation where instead of having the unearned revenue increased as we have the sales team being made, we might have a different type of problem than simply recognizing when the revenue is created. And that's gonna be tracking the invoice that we expect to create later on for the deposit, the advanced payment we received in advanced, for example, the advanced payment for something like a guitar we got in advance. We're then going to be, we're then going to be invoicing at a later time. If we were to make at the point in time we got the advanced payment and increased to a liability. We couldn't easily track it within an accounting system like QuickBooks, as we can if we made a negative asset account and then later when we create the invoice, recognizing the revenue at that time, we can tie out and match out the advanced payment to the invoice. So our problem then is that if we have unearned revenue, meaning advanced payments in this case, at the end of the time period, then we need to basically account for that so that we can present our financial statements correctly on an accrual basis by increasing the accounts receivable for those, for those negative amounts and then recording them as a positive and a positive liability. But we only want to do that for the presentation purposes. So if I enter that into, say, my accounting system, like a QuickBooks system, that will mess up the accounting department's job and their job. They're doing fine with their method, which is simply to have a negative, a negative receivable. So what we would like to do is reverse it back out of unearned revenue, back to that negative receivable portion or method so that the accounting department can continue doing what they're doing, which is working fine for what they are doing. So that's gonna be our objective this time, noting that as we do the reversing entry, we're going to reverse it as of the first day of the following period. One question you might ask because you might say, Well, shouldn't I reverse it at the date that we're going to receive the payment, for example, so that I reverse it and I'm closer to an accrual basis for a longer period of time. The point of our adjusting and reversing interests, however, is not to be closer to an accrual basis during longer periods of the month, but rather to make the end point when we're making the financial statements shown on more of an accrual basis or the accounting method that we need to present the financials in and for the rest of the time period, we want to basically make the accounting process as easy as possible. So to do that, the cut-off date, we're going to make a cutoff date as as possible at the end of the period, all adjusting entries and in the first day after that, in this case, March 1st will be all of our reversing entries, so that if you're looking for our adjustments and reversals, they will be easy to find. And so that's gonna be what we'll be doing here. So we'll go back to the practice tab. Now, this is where we left off. We've got unearned revenue up by the 450. Let's take a look at the subledger just to get a better idea of that subledger related to accounts receivable, undo that. Related to accounts receivable all the way on the right-hand side, you'll recall that we have these two customers, Smith and and Eric music. We had to look at the subledger to determine if we had any of these advanced payments in the accounts receivable. We determined that we did for 450. Then we increased the accounts receivable by that amount and then increase the liability by that amount to adjust for it. But if we record this into the accounting system, they're going to force us to hit the subledger or may like a QuickBooks would make us hit the subledger if we used an accounts receivable account. And bookkeepers are always afraid of that. If you're working with a bookkeeper and you're saying I'm trying to make the financials correct. For presentation purposes, would you enter my adjusting entries? They're going to say, Oh no, they put something to accounts receivable. And I don't wanna do that because it's messing up the subledger. So you want to be able to recognize that and say, okay, we're not going to hit the subledger for those actual customers possibly, but possibly make another customer so that we entered into the subledger over here as of the end of the period for these two adjusting entries we did and then we would reverse it the day after. This customer hopefully will clear out and it won't be an actual customer that will mess up the accounting department. Or you could create another account called accounts receivable, but not have it in accounts receivable type of account so that you don't need the accounts receivable subledger for it. So those are two methods you can use. So I'm gonna go all the way to the right or the left, I should say. And we'll do our reversing entries. So the reversing entry, we're just gonna do the exact opposite of what we did before. We're going to make it as of the first day of the next time period, 31. And we could we could since there's only two accounts, put the debits on top. But I'm going to keep with the method of the reversing entries being the exact opposite. Going from top to bottom, same accounts but just changing the debits and credits. Because when you get a longer transaction, I think that's the easiest method to do so I'm just going to keep the AR on top, the unearned revenue on the bottom, but I'm going to represent the accounts receivable as a credit with an indentation in the credit side. And it's gonna be a negative representing a credit on top. And then the unearned revenue is going to be a debit on the debit side, in the debit column positive number. Let's post it out. We've got the accounts receivable. That's gonna be the AR. Now, I need to unhide a couple of cells up top because I've got my my reversing columns in here. So I'm going to go from A0 to right-click and unhide those cells. So I'm looking for accounts receivable right here, double-clicking on it, go into the end of it and saying plus, putting the cursor back down, we're picking up the unearned revenue or the accounts receivable. And so there we have it. We were at the 2277176 and then we had these two adjusting entries that we put in place, both of them being reversed than these two reversing entries. And that brings us back to where we started at after, after the reversing entries on three One the other side, then it's gonna go to the unearned revenue. Unearned revenue. That's going to be here. We are in API 21 equals scrolling down. We're going to be picking up the unearned revenue of the 450. That brings it back down to 0. So all we did here, we did an adjusting entry to increase the accounts receivable for those negative deposits that we had in place. And then, and then we reversed it, getting us back to the starting point. We put those into the unearned revenue for presentation purposes and then simply reversed it back out. No impact in either the adjusting or reversing entries to net income items that being different than the book type of problem where you would see unearned revenue continually increasing. And then the problem there with your adjustment would be determine how much of the unearned revenue has been earned. Which would mean you would decrease the unearned revenue and record the sales. That's not then you would not have a reversing entry in that case. That would be a permanent difference, a permanent change in our problem that we had here with the adjusting process. Then we're going to reverse it out so we get back to what the accounting department is gonna do because their problem is it recognizing the revenue? Their problem is tying out the prepayment to the invoice, which is going to record the revenue at a future point in time. Now I'm not going to record these to the general ledger because I'm going to keep the general ledger at basically the year-end, the 228 information, but obviously in the software, you can then record the added detail to the general ledger and then you would have to be careful on the subledger as of 31. Remember that the subledger now if I go all the way back to the subledger, and that's going to be way to the right. We basically did a reversing entry and again, we didn't put the reversing entry. I wouldn't apply it to these actual customers if the software forced me to use the subledger, then I'm thinking I'm gonna put it over here into this into the adjusted customer. I'm not going to put it in this place at this point because I'm going to keep our GL and the subledger as of the cut-off date timeframe. So that's going to add up to the 23151 at this point in time on the subledger, that should tie out to the trial balance of the 23151 for the year to date, and then we changed it or or brought it back down for the reversing entries as of 31. So this is where we stand at this point in time. So this column of course, being our year-to-date, this being the reversing entries after the first day of the following time period. So the, the same down well, we have the balance sheet accounts down to here, and then the income statement accounts here for the two the two month timeframe. And then we rolled out the income statement up to here. You will recall when we're talking about the entries or the reversing entries that happened for March after the cutoff. Then we're looking at the income statement only for the month of March here. There was no impact on the income statement, however, from this particular reversing or adjusting entry. 12. Adjusting Entry Loan Payable Short Term & Long Term Portion: Excel, accounting practice problem, adjusting entry, loan payable, short-term, and long-term portion. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs on down below, an example tab and a practice to have the practice tabs starting out where we left off last time. The example to have in essence being an answer key, let's take a look at it. Now. We're gonna be doing an adjusting entry related to the loan payable, breaking out the short-term and long-term portion, noting that the loan payables can be a bit confusing in terms of how we're going to set up the loan payables and how we're gonna be doing this breaking out between the short-term and long-term portions. So let's take a look at it now. When we have the loan payables that are going to be going on the books. It's quite possible that we might have multiple different loans depending on the type of industry that we are in. So our first option when we put this information into our bookkeeping system is to have, for example, just one account that has the multiple loans in it, which would be called loan payables. And then have the supporting documentation such as the amortization table to support the amounts in the loan payable account. However, I would think that most bookkeepers would like to basically an accounting departments would like to have an account for each of the loans, breaking out each loan separately, possibly having sub accounts in accounting software such as a QuickBooks, meaning a parent account called loan payable, then sub accounts for each of the individual loans that can then be shown on the financial statements individually and if needed, collapsed into the parent account of the loan payable. For presentation purposes. As you break out the loan payable accounts, then you would want to make them distinct in nature, possibly calling them loan payable, and then given the last four digits of the low number or possibly the institution, if that's something that would be a differentiating factor. So you can easily tie in the actual loan you're looking at to the loan amortization table, the last four digits of the loan number often being something that could be a distinguishing factor between the different loans. Then we have the problem of breaking out the possibility of having loans that have a short-term and long-term portion. Many loans being constructed or formatted in such a way that they're going to have monthly payment loans, installment type of loans. If they extend on beyond a year, then you could have a current and a long-term portion. In other words, the current liabilities are those which are going to be due within a year. It's kinda like just that arbitrary cutoff. The long-term liabilities are those that are going to extend beyond a year. If you have a loan which are making monthly payments on that extends beyond a year, the payments that you're gonna be making or at least the principal portion of them for the next year are going to be short-term. The ones that are beyond the year would be long-term. That would mean that we've got to cut one loan pieces to break out the short-term and long-term portion. Now again, what are the options you could use to do that? One way is you could say, well, I'm just going to take my trial balance and then when I construct the financial statements, that's when I'll break out the short-term and long-term portion. However, I would generally like to do it generally on the actual trial balance and then just pull the information to the financial statements when we construct it. So I would then break out the short-term and long-term portion of any loan that needs to be broken out, you once again have the same option where you could just make the long-term portion net of all the loans which have a short and long term portion. But I would think it would be easier to tie everything out and review it. You made a separate account for each of the loans have in a short-term and a long-term portion, which you could tie out to the amortization tables and then group them together. The long-term and short-term loans as you create the financial statements, which we'll see in a future presentation. Once you break these two things out, then you have a problem that it makes it more difficult on the bookkeeper or the accounting department because they like to see the load in one account so that they could tie that one account to the amortization table when they make the loan payments, which is difficult enough given the fact that there's principal and interests portions that change over the life of the loan. Therefore, we will enter a reversing entry so that we don't mess up the accounting department while still being able to break out the short-term and long-term portion. Okay. So let's start, Let's do it. We're gonna go to the practice tab over here. Well, I want to look at the amortization. So let's go ahead and hide some areas. I'm going to highlight first the reversing trial balance. We don't need the reversing entries, so I'm going to hide from AP to AQ, will get back to them later, right-click and hide that stuff. And then I'm going to hide everything until we get to our amortization table. So I'm gonna go from the skinny here, going from the skinny all the way to the right. Until we get to the amortization table past the general ledger passed a subsidiary ledgers for inventory, accounts receivable, accounts payable, pass the payroll stuff. And then there we are. I'm going to leave that skinny right there, right-click and hide all that stuff. Hide it. Hide it. Don't delete it, hide it. So then we've got our amortization table and we're focusing in on payment to. That's where we left off. The second payment was at the end of February. So I'm going to highlight that one. That's as of the end of February, we've made that payment and we're just going to assume that 12 payments are gonna be made basically for the next 12, 12 month timeframe that we're going to count 12 payments up from there. So let's give us some space to work here. Let's put our cursor on the hx and then I'm gonna go to the right. Just give us a little bit of work in room. Right-click and let's insert some cells here. I'm going to make them a bit wider. Let's make these a bit wider so we can work on them. So there we have it. So notice also that the second loan over here, we're not going to be dealing with that because it's all short-term. We don't have a short-term and long-term portion, so that's not going to be a problem. We're working over here with this amortization table. From a bookkeeping standpoint, it's nice to be able to tie into this one balance, the 69878 to the amount that's on our books in the trial balance right here at the 69878. So that's what we would like to do from our basic. It's just a bookkeeping standpoint so that it is easy for us to enter the transactions and not have to deal with the short-term and long-term portion, but we also need to break out the short-term and long-term portion for presentation purposes. So what would be the long term portion? We would count like 12 periods out. I'm going to count 12 periods up from here. So we could say 123 and bring it on down to 12 with the auto-fill, auto-fill down to 12 right about there. So that's gonna be the end point from the green to the green. That's where we're gonna go. Then I'm going to add up what the actual principal payments are. Now you might say, Hey, look, the short-term portion that we're going to be paying is clearly going to be this 1359, which we're going to pay each month times 12 because there's going to be 12 payments. So you would expect it to be 1600305. And that would make sense except that we're including interests in that amount. And you might say, well, who cares, I'm including interests, I've signed the obligation, I'm going to pay that amount. That should be the short-term portion of what I know I'm going to pay in the next 12 months. But the reason that the interest is not included in there is because it would be kinda like the same thing as if you have an office building that you are working in and you know, you signed a lease that you're gonna be paying rent in the next year, you're not going to put the rent on the books as a liability now because you didn't actually occur it even though you've made a commitment to consume the space of the office building in the future. You haven't actually incur the expense until you consume the space of the office building. The same is true with the interest portion. We're going to pay it because we've made a commitment to do so, but we haven't encountered it yet because we haven't used the money during that time period, like rent in order to incur that incur that cost yet. So it's not actually a liability. The only liability portion is the balance here, not the interests. So we gotta make sure that pickup that loan balance, which is more difficult because the loan balance changes from period to period. So I can't just take one amount of the loan balance times 12. I have to actually add up these amounts on the amortization table, which is somewhat tedious. So we're going to say, okay, that means that the that the short-term portion is going to be, let's put it down here. The sum the sum of these items are the short term right there. Hold on a second. It didn't it didn't, I didn't put an equal sign, equals and then some of these items down to the green. So there we have it. Then if I was to take that's gonna be the short-term. Short-term. And notice my current balance is up here at the 69. Let's actually put this up top. I'm going to cut it and put it up top so we can see it. The current balance is, and let's actually put it on getting picky here, right there. There's the shirts, here's the full balance, there's the short-term. And then if I subtract this out, the 69878 minus the 13109, we get to the 56770 that matching the 56770 that we would be at at the end of another year, that being the long term portion that would be due after that point in time, That's what we need to break out. We need to break out the short-term and the long-term portion. We put everything into one account, which is basically in the short-term account. So we've got to break out the 56770 to the long term, which will make the short-term be at the 13th, 109. Let's see what that would then look like over here. I'm going to say, okay, what are we gonna do then? What do we got to do? This is going to be on to 28. To 28. We're going to be decreasing the short-term, which is a liability account. So I'm going to debit it to decrease debit, to decrease. This time, not always, even though it sounds cool with the double DES, but because this is a credit balance account, we're gonna debit to decrease. And the other side, I don't have another account yet. I have to add another account. So I'm going to add another account. It's gonna I'm gonna put it down here because I'm going to indicate that it's in the long term area, so it's a different type of account is not a current asset, it's a long-term asset. So it's gonna be after all these accounts, which are all current assets, current liabilities. This is a long-term liability, not a current liability. So I'm going to put it underneath, even though from an alphabetical order standpoint, it would be up top because it's a new category of accounts. So it's gonna be down here. That's what would happen if you were to put it in QuickBooks or some accounting software as well because you would name it as another type, not as a current liability but long term. So I'm going to right-click here. We're going to insert some cells, shifting these cells down, shift them down. This is gonna be, I'm just going to call it long or loan payable. I'll name it one number one, and just call it LTE for long-term, long-term portion. And then we'll sum this across as is our normal process. And I would do it for any other loan that had a long-term portion as well, but that's the only one that does, so we just got that one account. So now we can use it. We can go down here and say, let's pick that one up for the long term. For the credit side of things. For the credit side of things, we got the debit, those, the credit side. So then I'm going to indent this. The amount is going to be equal to the thing we just calculated in our tape. And the tape old the amortization table. In other words, we want to break out the long term portion because everything is currently in the current portion. So the 56770, there's the debit there is negative of that 56770, the credits. Let's post it out and see if it does indeed do the thing that we planned it to do. Let's go up top and C. So we're going to be in cell am 17 equals scrolling down, picking up the 56770, that brings the balance down to the 13108. Does that match what we have over here for the short-term 13108109, It's off by a penny rounding errors. I'm okay with that. I'm okay with that. Second half is going down here in the am. 22. Got to get up early in the AM to post these transactions. So we're going to say this is gonna be the 56770 and enter. So there we have it. So now we've got the 56770 on the long term. Does that match what we have over here on the long-term? It should. And it does. It should. And it does. So there we have it. Let's posted out to the G, L as well. So I'm going to unhide some columns to do so from a o to o h, right-click on that stuff, unhide. So there we have it. And so what I'm going to post this to the G, L. So the first one was the loan payable. That's gonna be the loan payable here. That's gonna be the first one up top. So let's go all the way to the right on the G, L, G, L, loan payable. Number one, I'm going to call it a DJ and by BZ, by BZ. And then we're in BZ seven equals left to the wall, scrolling to the left to hit that wall scrolling down to get that account, picking up that last account, 56770, bringing the balance to the 13th, 108, that should put us out of balance up top as it does right there. And we see that that 13108 matches the short-term portion of the loan payable, then we're gonna go to the second half, which is going to be that long term loan, which I'm going to have to add a new account for that's new. That's new stuff. So that's gonna be the last liability account. I've got this gap right here, so I'd like to use that. I'm going to move this over until we get to that, to that space. I'm going to just do some cutting and pasting, some cut and pasted maneuver. I'm going to take this, I'm going to say Control X instead of control C to cut it, and then paste it right there, Control V. And then we're going to take this one, and I'm going to cut that one with a Control X and put that one up top in here and PGY2 Control D. And then we'll take this one payroll liability and cut it Control X, put that right here in B14 and Control D. Then we'll take the sales tax payable and we'll pick that one up and Control X to cut it. And then we're gonna put that up top here and see C2 control V. And then finally we'll take the unearned revenue Control X. I'm going to put that down here in C, C 14 Control V. Then I'm going to copy this one so that we have the formatting up top. We're going to add the New Account, Control C. We're just going to paste it this time. Control V. We copied and pasted that time and then I'm going to delete the stuff that's in it. And then in C g to this is gonna be equal to left until we get to our trial balance to Trustee TB down until we get the loan payable number one, long term. Enter there we have our new account. We got the ADG, ADJ. And this is in C, G, as five were in CH50. Five sharp five equals left to the wall. That's not an actual word, but five, where you were at. Five. So five scrolling down, we're going to pick up the 56770. So there we have it. So there's the 56770. I'm going to add that then also to our check number over here. So the check number we'll check out to double checks in the check number. So this negative amount or this red number double-clicking on that go into the end of it in say, plus putting the cursor back down and picking up our new account. The new account way to the right is the 56770 and enter, that puts us back into balance again, back in balance again. So that's gonna be it. This is where we stand at this point in time. No impact on the income statement, you'll note down below. Oh, look at what I did. I didn't add the cell to the to the to the to the all the way across for the reversing entries. So when I added this cell up top here, I didn't select the skinny cell. I wasn't careful to add that up. So now I've got this staggering going on here. So I'm going to fix that. The way to fix that, I added this cell here and then I didn't go all the way across to pick up the hidden cells. So I'm going to select from here to here, API to AQ. I did that on purpose, of course, just so I can do this demo on how to fix that problem. Not really, But series here we're gonna go insert, insert, shift the cells down and enter, moving them down, everything lining up once again, back in balance. We've got to copy this one down so that we can be back in balance and I could start singing. Copy that down. Back in balance again. There we got it. So there's going to be where we stand. There's no impact on the income statement numbers. We got the short-term long-term portion. Next time, we're going to do the reversing entry because we don't want our adjusting entry to mess up. The accounting department. We're trying to keep them happy as well as get the financial statements to reported the way they were supposed to report. We're trying to make everyone happy here. The bank that wants to financial statements to the IRS, the owner, the accounting department. It's a big task. 13. Reversing Entry Loan Payable Short Term & Long Term Portion: Excel, accounting practice problem, reversing, entry loan payable, short-term, and long-term portion. Get ready because we're going to Excel. Here we are in our Excel worksheet and prior presentations, we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs on down below, an example tab and a practice tab, practice tab starting out where we left off last time. The example tab, in essence being an answer key, We're gonna be staying on the practice tab this time. In the last presentation, we did the adjusting entry, breaking out the short-term and long-term portion of the loan, recalling that we discussed a few different loan options or options for the tracking of the loan. The primary objective bean, that from the bookkeeping standpoint, we would like to make things as easy as possible to enter the payments and possibly tie out to the amortization table as we go. I would think generally in my perspective, that is often to have one loan account per loan, possibly in the current area, in a current liability type of account, and then be able to track the payments to the actual amortization staple statements on a per loan basis, possibly also having sub accounts for those loans to a parent account, allowing you in accounting software like QuickBooks or something like that, to be able to collapse them into the parent account or extend them as the needs might be for presentation or internal purposes in the usage of them. And then on a periodic basis, monthly or at the end of the year when we're presenting the financial statements, then break out the long-term and short-term portion with an adjusting journal entry as we did last time, breaking up the short-term and long-term portion. And then after we have done that, we don't want to mess up the accounting department. So then we're going to collapse it with a reversing entry, once again, bringing us back to the one account per loan, making it easy for the accounting department. So let's give a quick recap on where we stand with the amortization table. I'm going to hide from the skinny on the left-hand side, going all the way to the right till we get to our AM or ties Asian table, which is way past the subledger the payroll stuff. There it is. It's an HIM right-click and hide all that stuff. So last time we broke out the short-term and long-term portion thusly, thusly. So now we're just going to reverse it back out so that we're back to the starting point where we, where we were last time. So here are those short-term and long-term, short-term, long-term that we saw in the amortization table. And then we'll get back to here, which is the balance as in accordance with the amortization table. If we were to just look at it when I count. Okay, So then the adjusting entry, a straightforward adjusting entry, we're just gonna do the exact opposite. I'm gonna put it down below. I'm going to format some cells down here so we can just fit it down here, selecting some cells, going to hit the paintbrush and put some blue paint right there, paint some blue so that we have some room. And then on to this is on 31. This is a reversing entry. And by the way, while we're here, you might want to, of course, be put in our notes. I kind of stopped doing the notes. The notes up top, like this note. I might want to put a note here and say this is, this is a note for, this is a to record the depreciation depreciation cord in according to add more time, according to the pre deviation schedule from tax software, something like that to show us where it came from and so on. The accounts receivable and the unearned revenue, you might want to note there. So we might want to say, we're going to make a note, new note to increase customer deposits in a are for prepayments and record on related Related on earned revenue. I probably spelled that wrong, but just to give you an idea of the note could be helpful, of course, for future people working on this and then this loan. We can say, we're going to say make a note to break out short term and long term portion according to add more. Ties, a table, something like that. And then we could reverse it here. And we're going to say note new node, reverse. Adjusting entry for loan. So that let's say accounting. The parts meant the part has only one account. They can agree to amortization table, something like that, and then I'm going to reverse it. There's only two accounts so I could put the debit on top. But I like I'm just going to keep with the system we've been doing here to just keep the same order from top to bottom and reverse the debits and credits, which I think is going to be easier in the event that we have those longer accounts that I'm going to make this the credit up top now, looks a little bit funny, but I think it's kind of easier to see what's going on and putting it in the credit side instead of the debit side. And then which is reversed this this transaction and then reverse the debits and credits, keeping the order of top to bottom, changing the credits and debits. That could either be that could be. I think that's easier to see oftentimes because that could mess up when you're doing these adjusting entries. You also have to consider who you're providing these four. So if you're providing them to a supervisor or something like that, that's going to review your work and they can't stand having credits on to off or something like that, then you're going to have to do whatever, whatever the common system is that you're working in At that point in time. But that's what I'm gonna do now. We're going to say here, is the loan payable, the loan payable up top the short-term portion. So we are in API 17 equals it will scroll back on down and we're going to be picking up than that 56770. That brings us back up to the 69878. And then we're gonna go down here and say in a P20 to this is going to be equal to then the, the loan payable here and enter, bringing it back down to 0. So what we did then is in the adjusting department, we started here the 69878 that ties out to the amortization table. If we had just one balance as of that point in time. And then we did an adjusting entry, taking it down to the 13108, which is the current portion according to our amortization table calculation. And then we reversed it, getting us back to that point in time. So the accounting department doesn't have to deal with two accounts when they're entering the transactions. We added this new account down below. Want to be careful when you add the new accounts in the event that you're going to have the Accounting Department put them in place so that you have them in the right order. You want to kind of be aware of that because that could cause problems down the road. And then we basically did adjusted the long term portion, making it easier to do the financial statements based on this worksheet, which we will do shortly, and then we reversed it back out, bring it on back down to 0, no impact on the income statement. We're not going to be recording these two. The GL the general ledger is basically reflecting where we stand as of the point in time at the end of the timeframe at this time. So that's where we stand as of now. Here's the here's the trial balances. Year to date and the day after the year to date, the cutoff and the day after. 14. Financial Statements with Every Account: Excel, accounting practice problem. Financial statements with every account included within them, get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the Excel worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs on down below, an example tab and practice tab. Practice tabs starting out where we left off last time. The example tab, in essence being an answer key, we're going to keep on here at that practice tab. We're going to be constructing the financial statements from the trial balance. We're gonna be using year-to-date trial balance, not the reversing item at trial balance. To construct them, we will start out then by Heidi in cells a, p and a q, we don't need those cells to construct our trial balance. I'm going to right-click and hide, will hide those. And then I'm also going to hide the unadjusted and adjusting entries so we just have our numbers. So from ALL to am selecting those columns, right-click and hide them too. Now I'm going to hide everything until we get to the financial statements on the right-hand side which we had constructed. So we've got an outline of the financial statements, but we have included a few accounts as we have been going through the adjusting department. We will also go through and create the financial statements from scratch again after doing these adjustments. And when we do those adjustments, we'll think about the combining of some accounts that we might often need to do for presentational purposes when displaying the financial statements. And we'll discuss also, if you're using accounting software, what kind of those adjustments might be able to be picked up by the accounting software by using sub accounts and which of those adjustments might use still need to do a little bit more cleanup work, even when using accounting software for presentational purposes to be more in alignment with standard kind of presentational type of goals. Let's go back up top. I'm going to go from H0 and we're gonna go all the way to the right till we have constructed our financial statements on the right-hand side. So there they are. There they are. So we're right here to Jay. I think I got another. So I'm going to go to J right-click and hide all of that stuff. So we had our financial statements we put together in a prior presentations where we had the balance sheet for the year to date, the income statement and the equity, and then we did it again. The second one was just for the the current period or the one month we're not going to do the one-month financial statements. We're just going to focus this time on the year-to-date information for the balance sheet and income statement. So basically, we've constructed this already. We already have in essence, our outline that we're going to pick up and we're just going to add a few accounts. And again, we'll construct the whole thing from scratch in a future presentation. But I wanted to just look at this outline first. This outline is designed to basically be drawn from the financial statements in a similar way as accounting software can basically be put together strict, great, straight from the data input and the general ledger type of activity. And notice that accounting software has a few other little tricks that I could use that make the reporting purposes a little bit different, like sub accounts. But from a general standpoint, this is gonna be kinda similar to what we would have in accounting software. And then again, next time, we'll try to take a step beyond that and say, What other kind of clean-up work might you do for presentation purposes, for standard presentation. So again, we've got the checking account, we've got an deposited funds. These are pulling over. We might actually go from here and use our little tool right here to see is that when pulling over, it is this tool, by the way, is in the Data tab and it's going to be, it's going to be no, it's in the Formulas tab. It's in the Formulas tab, it's right here. And I put this into my quick tool bar up top so that I can use them. And then I'm going to say I'm even going to include this 0. That's one of the things that we might clean up if we were to put this into our books, also, into a more clean format of financial statements. Also note, it's not typically the case that we want to report these items as checking account and deposited funds, but possibly something like cash and cash equivalents, which again, you can't really do so much in a lot of accounting software is oftentimes because it's gonna be in there by the designation of the account, the account type under the checking account. Typically if you're looking at something like QuickBooks. So those are some things we'll look at next time to clean up. This one is going to the accounts receivable and then this one is going to go to the inventory. So all of these accounts are being picked. We've got the short-term investment, that one is now at 0. Sometimes accounting software can pick that up if you put an option on to say Don't pull in 0 balances. But we'll keep the 0 balances in our case because we're gonna be using all accounts here so we can see how we can construct this in Excel with all the accounts. And then we're looking at the furniture and equipment which is pulling over. That looks correct. Remember that the furniture and equipment is one of those areas that property, plant and equipment, for example, that you might have different preferences on how you would set it up. So these are things that we talked about. You can, in accounting software, you can do a little bit more of this for the The way the balance sheet will be constructed by using sub accounts. So we talked about the idea that we might want just one section for all the property, plant and equipment, and one account for accumulated depreciation. Or we might want to separate accumulated depreciation account for each of the categories of the property, plants and equipment possibly tying out to the amortization schedule possibly done in the tax software. And using that as basically our guide for breaking this information out. So when accounting software, you might make this like a parent account and this is a sub account. And that would give you this kind of breakout of the furniture and equipment and then the accumulated depreciation given you the net of the two, which would then be the book value. So this is often an area that's messed up that people don't do quite right or have problems with because of these contra, accounts which actually bring down the, the balance of the assets. And that's often something that throws people off when they're adding this two. And they're also kinda get confused as to how they want to display. How do we want to display the book value and the cost and so on. What's going to be the best format there? We got those here. They are going to add up this way. So that looks looks good. And then we've got our total property, plant and equipment, which is the sum of the two book values for the two categories that we have, total assets than being the current assets and the property plant and equipment. I can double-check each of these categories by taking inches, adding up, or selecting these items. There's the 133756, here is the 93027. And notice how nice that is to do by having the credits be negatives here, formula wise. And then if I do the whole thing, summing up the whole thing, we get to the two to 6783. So that looks correct. I'm going to undo all these and let's do the same thing for the liabilities. I'm just gonna go, there's current liability accounts payable, looks good. It's picked up over here and the current liabilities, we've got the visa. The visa is something that we might not put in place as simply a credit card. A Visa. We might call it something like a credit card or other current liabilities are some generic category like that. If we had multiple credit cards, we might combine them together. You could possibly do that with accounting software by having a sub account possibly to clean that up. And then we're going to say that this one is going to two. This one isn't included. So I gotta have the interest expense. I'm going to add that here. I'm going to select these four rows is going to right-click or four cells and insert shifting down, shifting down. And then we'll just add that account into our liabilities. Into the liabilities, the interest payable, which you might call it accrued interests. I'm going to say negative instead of equal to flip the sign, picking up then that 73, there we have it. Then we're on the loan payable. And I've got those two, so that's being picked up here. This is another area where you might you might want on presentation purposes not to have to loans the actual loans, possibly including the low number at the end of it. For external presentation purposes, you might be able to clean that up in accounting software by having a parent account, sub accounts that can collapse. For external presentation purposes, we'll talk, we'll adjust that in a future presentation when we do the second financials as we condense them, the payroll liabilities, the sales tax, and the unearned revenue. So there is that stuff. And then we don't have yet the second long-term loan, which I'm gonna put down here. So all the current liabilities are up top. Long-term liabilities do after a year's worth of time, I'm going to say negative. Picking up to that long-term liability, being nice and easy to do. So given the fact that we broke it out in a separate account, instead of us having to break those two things out as we construct a financial statement. So that's one way you might do that. So I'm just going to keep it in one account and then break out when I make the financial statements. So this So we got that. And then that gives us our current and long-term liabilities are here. We add those two up to give us the total liabilities. I can double-check the total liabilities by simply summing up, just selecting those accounts that adds up to the 8364 tine out to the 8364 here. Now next, I'm just going to take the full equity from our trial balance, which is really easy to do if you have the trial balance is set up this way and then I'll then I'll back into that by doing the income statement and the statement of equity. So in other words, this equity account, instead of taking it from the statement of equity, I'm just going to say negative sum of all of the equity accounts from here on down. And look how easy that is to do simple formula to pick that up because the debits are positive and the credits are negative. And you just use the negative sum formula and you pick up that plug, which is the 143719, that matching out. So now my assets equal. Liabilities and equity. So then I'm going to move to the income statement, do the same thing. I'm going to hide from, from J, S and scroll on over to Jay. Why right-click and hide hide that stuff and work on the income statement. So I'm gonna do the same thing down here. We'll start with the revenue, different kinds of revenue that we have up top. And this is another area where we could condense the revenue into one account, for example. Or we might just have multiple revenue accounts on the income statement. And so we're going to say that one's going there, that looks good. This revenue account is going up top. This one is being picked up. Cost of goods sold is being picked up. So that then we have the revenue, total revenue growth, then we've got the cost of goods sold. And the difference between total revenue cost of goods sold is that subtotal of gross profit, then we have all the other expenses. Now, we made some changes to the other expenses like the bank service charge and the depreciation or two of them. The easiest way to pick that up is just, let's just delete this whole thing right here. Just delete that whole thing. I'm going to add a couple more, couple more stasis so that we have enough room for the new accounts. Right-click and insert, shifting those cells down. And then I'm going to say this is going to be equal to and we will pick up then the bank service charge. And I'll put that in KB ten equals the bank service charge of 35. And now know that that account is a pretty small ones. So we could, as we're making the financial statements, tried to condense some of these accounts. We might say, Hey, look, I'm going to put some of those small ones into other expenses are miscellaneous or something like that. Possibly. We could do some more condensing if we so choose here, I'm gonna grab that and then just copy it down to as many expense accounts as we have. We're gonna go down to utilities. Now. I don't need I don't need these last two because these are going to go into another category of other income and expenses. So there we have it. I'm going to delete this last cell or this last row. Putting my right-click on it and delete VAT, moving it up, shifting up, put the underline here, going to say Home tab underline, and then makes sure what my total is picking up the right column. Looks good. If I double-check my numbers here, if I went from the income down to cost of goods sold, that should give me my gross profit. That adds up to the 20th, three to 57. There's the 20th three to 57. I then have all my expenses which are going to be from the 35 down to the utilities. That's the 2151421514. So then we've got our net operating income. I can double-check by taking everything from the revenue down to those expenses. 1743. So that's the 1 seventh for three here, which was my gross profit minus these expenses. And then we've got our other income and expenses, these being income and expenses that aren't part of normal operations that I'm gonna put down in this other category, we're combining both the income and expenses. So I would like the income to be going up, positive numbers, expenses to be going down, we're picking up then our gains positive number. I had to flip the sign with a negative of that number, make it positive. And then the expenses negative of that number to pick it up as a decrease, adding them up, then we've got the net decrease between the two of them. We then get our net operating income minus that other item. Takes us to the 1324, which I can double-check here by just selecting all of the income accounts, 1324, which we did down here with our check figure down here. And it looks good. Then we can go to our equity accounts. So the equity account is going to start off with the beginning equity, which is going to be this this 77895, remembering that that is the most confusing number on the trial balance because the trial balance is as of the end of the period, but that equity number is really the beginning equity because, because everything else is going to roll into equity and that beginning equity doesn't have you shouldn't have any activity in the equity account or normally you would not unless say investments, for example, or draws were posted to the equity account which we did not. We posted those here. Now, note also the equity account should tie out to the prior periods ending equity account. If it does not, then that would be an indication that draws or investments have been posted into it did look into the GL to see if that is the case or it's an indication that someone did something to the prior period, they deleted a check or voided something or something like that. And then you gotta deal with that beginning balance problem. So we're gonna say that's the beginning number. And then we've got these two things that happened. Draws were taken out and the owner put in 65 thousand. So we're gonna say, okay, there's the investment of 65 thousand and then we had draws. I got to add the draws. So I'm going to select these cells, right-click and insert, shifts cells down. And I'm gonna say this is going to be draws is here. And I'm gonna make that a negative because it's going to be a decrease to the equity. So negative of the draws. So there we have that. Then this is our beginning balance. It increased by 65 thousand investment of the owner into the business. And then it decreased by the draws, the money the owner took out. And then we have the net income, which was down here from the income statement. That's the change in the equity. So we had the beginning equity and the change which is an increase in this case, that takes us to the 1 fourth C37 19. I should be able to double-check that by simply selecting all the blue accounts, which you'll recall we did when we looked at the balance sheet, 143719. Now let's unhide the balance sheet. Putting my cursor on the skinny here, go into J, right-click and unhide. I'm gonna go all the way to the right to find my financials. Again. Find my financials. Where are my financials? So here's our balance sheet. We're in balance. Assets equal liabilities and equity. This equity account, I'm now going to change, instead of pulling it from the trial balance, I'm going to say this equals from the equity statement. From the equity statement, boom, we're still in balance. Still in balance. Didn't mess anything up, right? No. So then we got the income statement income statement down here, getting bottom line, net income, that net income tine out to the statement of equity. The statement of equity. I'm making a statement about equity round here. So if we sum this up, there we go. So there's our statement of equity, so you can see how those are going to tie together. So next time, we're going to start to make our financial statements and we'll see some of those things we talked about, about grouping some accounts together and so on. We'll start to do that and see how we can do that in Excel and try to think about what kind of things you can do possibly and accounting software to help you with the presentation purposes versus internal use of the financial statements and what type of things that, even if you use an accounting software like a QuickBooks, might you still want to do to clean up the financial statements for external presentation purposes. 15. Balance Sheet Condensed Format: Excel, accounting practice, problem, balance sheet, condensed format. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions into it if you have access to it, There's two tabs on down below, an example tab and a practice tab. The practice tabs starting out where we left off last time. The example tab, in essence being an answer key, we're gonna be staying over here on the practice tab side of things. In a prior presentation, we took our adjusted trial balance, we created our financial statements from it as we did. So we constructed something that can basically be transparent as being something that has been created from the data input by adding every account in the financial statements that are included in the trial balance. We also included 0 balances so that it can then also be changed quite easily as we make changes to save the financial data. Now we want to consider a more condensed type of financial statements, starting with the balance sheet, where we might make some alterations that would combine some accounts possibly. And some of these combinations are some of these changes that we make. We want to point out that they could be done for accounting software as well. Meaning software could accommodate some of these changes within the formatting and structure of entering the data in the system. For example, with the use of sub accounts. And some of them, the accounting software will not exactly be able to pick up. Oftentimes, accounting software like QuickBooks. And you would still possibly need to take your financial statements and you could still clean them up a bit for external purposes if you so choose to do this, let's go ahead and hide some cells. I'm going to be hiding from H0 and I'm gonna go all the way to the right past our financial statements. So we're going to build new financial statements. I'm gonna go all the way out here to Kw and going to right-click and hide that stuff. I'm going to make another skinny column over here so I could try to unhide this stuff without basically unhide in everything. So I'm going to make another skinny. So the first thing of course we could, I'm not going to get detailed on the header of it. I'm just going to call it a balance sheet. So balance sheet, it might, you might call it a condensed balance sheet or something like that, versus the longer balance sheet, the standard balance sheet versus that a condensed balance sheet or something like that. I'm just going to call it a balance sheet. I'm gonna make that and format it up top with the black and white formatting as we've been using for our header. So I'm gonna go up top and say, let's make this black and white. So black and white, I'm going to make them the assets. Assets. And I'm going to format that with our colors still, which again, you might, you might not want to use the colors for external reporting purposes, but I still want to tie it in so we can group it in a color-coding way to our financials over here. So I'm gonna, I'm gonna select these two up top and say, let's make this the color of a dark green. And then the texts are going to be the light green, the dark and the light green. That looks so nice. Look at that, That's amazing. Then we're going to say current assets, current assets. Colon for the Sub-Category, Let's add a little bit more space. Here, adding a bit more space. Then I'm going to pull in my account. Now, this is where we're going to start to group things together because these first two accounts are the checking account and deposited funds. And note that oftentimes an accounting software, the and deposited funds might be located in some other area as like an other current assets as opposed to a checking account. In a QuickBooks type of software, this checking account will be labeled under a checking account type. So they're going to have a checking account category of types of accounts. And that's not normal financial accounting categories. So oftentimes for financial accounting, we would call it something like cash and cash equivalents. Cash equivalents or something like that. It should be called cash, not checking account. So that's what we will first I'll combine, combine that and I'm going to sum up these two columns, though, these two items now note there's a 0 here, so not a big deal. But you can see how when we start to sum things up over here, it can cause confusion, confusion of bit. Also note that you might say, well, I can fix that in accounting software, not the name of the checking account, but possibly having these two accounts combined together with a sub account. But this is one area where you can't do that because in like accounting software, this one would be a different account type. It would be a checking account. This would be in other current assets, so you can't really do a sub account. So again, the software, this is an area where you might have some more cleaning up to do. And you might say, I'm going to sum this up and call this cash and cash equivalents or something like that. If you wanted to be more formal in your presentation. And I'm not going to get, we're just going to go over some general ideas that you might do to get to clean things up and possibly be a little bit more formal if you wanted to. From like accounting software. So then we got the accounts receivable in software like QuickBooks, they often have a separate category for the account type of accounts receivable, which results in a subcategory up top. Which is something that can be a little bit messy, that you can remove the subcategory. You could possibly do that by doing a different type of financial statement. Unlike accounting software, we're going to bring that down. If you had something else that was involved here like allowance allowances, then you'd have to deal for the allowances to but we won't get into that. Then we'll pick up the inventory. Inventory is fairly straightforward. We'll pick that up. We've got the prepaid insurance, the prepaid insurance notice. If you had multiple prepaid insurance, prepaid accounts and other kind of other current assets, you might group them together as simply other current assets. So for example, these two, you might say, well the short-term investments or a small amount. And possibly in the prepaid insurance, if that's gonna be a small amount, we might group those together and call this other current assets. And then group them together equals the sum so that we can condense our financials given the fact that obviously the short-term investments in this case are not relevant in terms of dollar amount and that can clean things up and shorten things up. Let's do an indentation here, selecting these assignment and indent. So there we have that. Let's put an underlying their font group and underline, let's call this the total, total current assets. This is the total current assets. That's what I called it. I just called it the total currents. Let's indent this two times, indent, indent, sum it up and the outer cone equals the sum. There we have it. So there's summing that up. So that's the 133756 on that. And then let's call this now, this one we could be calling it in accounting software. These items might be called fixed assets. I think oftentimes for financial reporting, it might more often be called property plant and equipment or something like that, PP&E Property plan. So let's let's call it that. Let's call it let's call it property. Plants and equipment. Equipment. Your property plant and equipment. There I called it I called it property plant and equipment. So then we're going to say we got the furniture and fixtures. This is the area where we got to say, how do we want to break out the book value and the cost we want to be combining together the accumulated depreciation in one account or breaking them out. I'm gonna do it in a similar fashion we did before. I'm going to have the costs of top up here. And then we'll put the accumulated depreciation under it. Oftentimes I used to kinda, kinda, I'm gonna make this a little bit larger now notice this is a contra asset account. So now your question is, do I want to represent the contra asset account as a negative on my financial statements or as a positive because they might actually say less accumulated depreciation. So for example, I might say, let's copy this and paste this 123, just the values, and I might say less ACC D3. We also might have an issue with this abbreviation right here. I might say maybe I should not even have this ACC de Pree inches, call it a cube. You, let's say less our accumulated depreciation. So I'll change the name and then I'll end. Then we will pick that up and I'm going to flip the sign here to make it now a positive number because I put less here to indicate it's a subtraction. Then the question is do I not another account down here, another item to have the book value or do I want to just put it on the right-hand side? I'm going to put it on the right-hand side this time I have to subtract them. 98 thousand minus the accumulated depreciation to get the 88166. I'll do the same for the machinery and equipment. I'll say machinery and equipment and then equals this less accumulated depreciation. We'll do the same thing here. This is going to be the 5 thousand. I want to, I want to show this as a positive number that we will then subtract out by saying negative of that 139. And then we'll take the equals to 5 thousand minus 2139. So now we're showing the machinery and equipment and less given the world telling you you want to subtract those to get you the book value, which we didn't put another subcategory, but rather put right to the right here so that we could see it, which is a little bit more condensed way of displaying it. I'm going to then indent these, selecting these items go to the alignment and indent, we're going to call this total prop for T, plant and E. And then we'll sum up those two equals the sum of those two. Let's put another underline here. Underline, put an underlying there. Let's put an underline here, a lot of lines, lot of lines going under. And then let's indent this, the double indent alignment, double and dance. And that's gonna be total assets then. So total asks, sets are going to be equal to the current assets plus the property, plant, and equipment total assets. Now we can check all these numbers. By simply selecting these, these are current assets, 133756 ties out here. It's a little bit more difficult to see due to the fact that I made some combinations here, but we still get that double-checking factor pretty easily in the trial balance. If I select these, that's gonna give us the 9327. There it is right there. Even though we format it a little bit differently with the contra assets and whatnot. And if I select the whole green thing, all the assets, 226226783, which matches here. Everything looks like it should. So let's go and let's put an underline here. Let's put a double underline there and move on to the liabilities, the liability side of things. Let's make a skinny. I'm gonna make an skinny and LB, LB needs a skinny. And then we're going to call this liabilities lie and build T is your liabilities. There are cold it liabilities. That's what you are liabilities. And then we're going to go to the font group and we're going to hit the drop-down. And let's make this dark orange with the light colors mirroring what we did over here and the trustee TB. Then we've got the current liabilities, current liabilities, colon. Then we've got our accounts payable, the visa, the interest. Now the accounts payable will pull that in, like we normally would in accounting software like QuickBooks, they often have a subcategory for accounts payable because it's an account type. So we can kinda clean that out. We don't really need the subcategory there. Typically, I need to flip the sign. So every time I put a mount an amount in, I'm going to say negative, for example, in L D for negative of that 4,008. So then the visa here is a fairly usually it's a fairly small amounts. So I might put that in, maybe like other liabilities. Maybe I've got, I've got two lone payables down here, which might be more significant than the visa and the interests. So maybe I want to move that up top. And the payroll liability, maybe I move that up top sales tax payable. Possibly I can group that into other. So let's, let's try to, try to do some grouping here. And I'm going to say, maybe, maybe these two lone payables should be up top. I'm not going to put two separate loan accounts with two separate loan numbers in the presentation to external users. I'm just going to call it loans payable. Loans, payable, payable. And we might then say like current portion. We don't really need to say current portion there. It's a little bit redundant because it's under the current liabilities, but I'll do it anyways equals the sum. And then we'll pick up this stuff just in case anyone's kinda dense. This is the current portion because the long term portion is going to be down below in the long term area, we're going to add those two together. I got to flip the sign, make it a positive. So I'm going to put a negative before the settlement. We pick both of those items up in one line item. Now the next one I want to pick up here, the next most important one, it looks like it's gonna be the payroll liability that's significant. So I want to break it out on its own. It needs to be broken out on its own, possibly payroll liabilities. So I'm going to say, okay, negative of the payroll liabilities. Like so, like so, like so what? And then here, like this one, this, this, this, and the sales tax and the unearned revenue, possibly those are not the most significant dollar amount. So we might say, I'd like to just put them all into like other current liabilities. Instead of breaking them out. Again, that we might break them out. It depends what how much detail you want, but like the seventy-three dollars probably not worth worth putting into its own category. So maybe I'll just put all the rest of them into other current liabilities. And this is, this is a aesthetical choice. I gotta put negatives to pick up these multiple items here. So I'm going to say negative of the VSA minus the interests, minus the sales tax, minus the unearned revenue and Enter. So we've got all those added together here at the to1, O2, which makes it a little bit more difficult to tie out to the trial balance, but we're cleaning this thing up hopefully to make the presentational purposes easier. So alignment in dent or let's indent this stuff. Alignment indent. And then let's call this total current. Bill at tease, your total current liabilities. That's what I called it. And then we'll put this on the outside. We're going to say this is the equals, the sum of these four. And we will end dent font group and let's underline. And then we can double-check that number by just selecting all the current liability is not included in this last one. And does that work because there's way more accounts over here, 26 to twenty two ninety four. Twenty-six. It does. It's right. I know what I'm talking about. I think most of the time. And then we'll say this is going to be long term. Liabilities, colon. And then we only have that one account. I'm not going to call it like the name over here that would be tied down to a specific account and account number. I'm just kinda call it loan payable or something like that. Loan payable, long term or short and the long-term portion thing being redundant once again. But you'll often see it will be redundant. I'm going to put this directly into the outer column because, well, let's make it, let's make a subtotal. Sometimes you wouldn't want to make another subtotal of it. Well, let's actually, I'll put this in the auricle. This is really how you'd normally see it most of the time because there's only one thing in it. And so to have another subtotal would be super redundant. So I'm just going to say there's our long term liabilities and indent and have that there. And then this is gonna be the total liabilities. And we'll just sum up the outer column equals the SUM of these two that comes out to the 8364. We can say, does that tie out if I was to add up all my liabilities by just selecting them 8364. Indeed it does. Indeed it does. Let's put an underline here, font group underline. And then now we just need the equity side of things. Equity. Let's put that, Let's skip a line. Equity. And I'm going to make this blue to match our blue stuff down here. So I'll make it blue font and let's make it, I think it was like this one that we use dark blue. And then the lighter blue, wonderfully contrast and with the double blue, double blue. And this is just gonna be the equity over here. Now known a lot of people will tell you you can't make the balance sheet until you make the income statement first. But if you've got a well-designed trial balance, that's not true, that's not true. You could just take the negative sum of everything in the equity area. And that way you can construct your financial statements from top to bottom, assets, liabilities, equity income, and then expenses the way most people would probably do it if they weren't told otherwise. And so there we have the 143719. Now, if you have an ugly, unformatted trial balance that isn't, is it wonderfully designed? Then? It might be a little. Then you could see the argument that you might do it with the income statement first, but I like to do the balance sheet first. Don't answer that on a test question. If someone's asking you on a CPA question, which statement do you need to make first, but practice, you would do what you think is, right, right. That's equity. So here we go. Total liabilities and equity will sum this up, equal the sum. Summing these two up. We'll put the underlying here, font group and underline, put a double underline there. And you can see that we are in balance. And we can check that equity number a couple of different ways. Now, note, we could say, well, it's gonna be the total liabilities, debits minus credits, 143719, or it's gonna be the assets minus liabilities, debits minus credits. Well, that's way too hard to calculate. I gotta pull up my ten key to get that out. No, you don't because we've formatted the trial balance in a way that it's really easy to do. 14379143719. So now let's just clean this thing up a bit because we're back, we're in balance. Assets equal liabilities plus equity. It's a little bit, a little bit more condensed. Have a format. Let's do our blue and borders. Let's make, let's pull this green thing out. Let's pull this, these two out. One more by getting our paintbrush, putting our paintbrush and just paintbrush in that right there. Then Let's actually pull this black part all the way over to the end, like there. Then I want to put this balance sheet in the middle. So I'm going to select these. And one way you could do that is you could go to the Home tab and the alignment, and you can do that. But then you've got this one fat cell that's literally wide sale. And I don't want it I don't want to insult the cell, but I don't like it doing. I'd rather do it this way. Select that area and right-click and then go to the Format Cells and go to the alignment, horizontal alignment. And we want to center across the selection. Then you get the same thing, but you don't. That causes its own problems, but I like to not have that one cell Bush kinda causes problems when you try to hide things and do stuff like that. So then we're gonna take this one. Let's paintbrush that, that orange with our paintbrush has put that on our paint brush here and the clipboard and paint that cell with it. And then I'll make this whole thing blew and bordered. Let's take this whole thing going up to the Font group. I like to put it on that nice light blue and the more colors standard, the light blue Excel is fungi channel blue. Okay, and then font group drop-down. We're gonna go to the borders. So there we have that. Then let's put that same border blue right here. Let's do it down to here maybe. And then go to the blue and bordered. And then finally down here, border blue, border blue. Let's do a spell check just on these cells and see if I blatantly made a blatant error. There's one. Liabilities. It's not even in the spell check. That's bad. Liabilities. Fixed it, okay, So there's where we have it. So we'll do this we'll do a similar process then next time to the income statement. 16. Income Statement Condensed Format: Excel, accounting practice problem, income statement, condensed format. Get ready because we're about to Excel. Here we are in our Excel worksheet and prior presentations we put together the worksheet from a blank sheet now continuing to enter transactions in it, if you have access to it, There's two tabs on down below, an example tab and a practice to have the practice tab starting out where we left off last time. The example tab, in essence being an answer key, we're going to stay here on the practice tab this time. Last time we put together a balance sheet, we're focusing now on the financial statements in a bit more of a condensed format, a format that might be more appropriate for external presentation purposes. So we're going to be grouping some of the accounts on the trial balance into, say, single accounts eliminated the 0 balances on our trial balance, possibly changing some of the names to be better categorized within our financial statements from the trial balance, some of these adjustments, possibly the condensing of accounts, for example, could be taken care of in accounting software, possibly with the use of subcategories allowing you to collapse an uncollapsed some of this information and some of them may not be able to be taken care of with accounting software. And therefore, even if using accounting software, you might, if you're doing a formal presentation of it, need to do a little bit more clean up if you want to do a little bit more formal of a job. Okay, so we're gonna, I'm gonna hide some cells. I'm going to hide the adjusting and reversing entries. I'm going to hide those because we don't need those. I just need the trial balance. Actually, I need this trial balance here, the adjusted trial balance. I'm going to hide these reversing and the period here. So I just want the adjusted trial balance, YTD year-to-date. So I'm gonna hide this. I'm going to hide everything here. I'm going to leave the skinny because I want a couple of skinny so I can hide and unhide when I get to my financials. Now remember that we hit we made a bunch of financials on the right-hand side. So we're gonna go to the last financial statement where we left off with a balance sheet. And now we're going to be moving on to the income statement. So I'm gonna go all the way to the right. We did the bank reconciliations. Here's our financial statements. And then we left off with this balance sheet at the end of this balance sheet right here. So I'm going to hide that now. I'm going to hide over to here, right-click and then hide all that stuff. Then we're just going to focus on the income statement. I'm going to make another skinny right here, skinny column. That is, that's what I mean by the skinny. And we're now going to be recreating the income statement and the statement of equity. So if we go down here and just analyze what we have here, we've got basically the basis of the income statement is right here in the trial balance and we're just going to take the revenue minus the expenses. There's our bottom line, net income right there. It's basically in order. But when we go to the actual form, we might want to say more of a multiple step income statement, in which case we have a couple of steps along the way. For example, we might take our revenue accounts here. Now note when we look at our revenue accounts, we might put them in there. We might have more revenue accounts, for example, on our trial balance. Then maybe we want on our income statement, maybe we want to condense some of those revenue accounts and have less line items. That's one thing to consider. We'll put them on all three revenue accounts. We've got the cost of goods sold. That's one of those big, those big expense accounts related to inventory. If you're in a business that sells inventory, which often is a major component where people want the multiple step income statement, breaking out that step total along the way of gross profit. And then we've got all the expenses. Now if you look at some of the expenses here, you might pick up some of the smaller ones and say, Hey, maybe this smaller one, I don't want to separate charge on my income statement because it's pretty small of an expense. Maybe I should group that in with some other expenses, calling them. Other expenses are miscellaneous expenses or something like that. Then if we go down and got the insurance, the Internet Internet, It's pretty decent side then we've got the miscellaneous maybe we put this into miscellaneous to or other expenses. We've got the office supplies, payroll is gonna be significant. The taxes, the telephone, utilities. Then we're gonna put these two items down here into their own category. So we don't have a whole lot of condensing in this particular worksheet because, because we didn't get too extravagant in our subcategories. But notice if you're working in larger type of companies, it's quite possible that you start getting up a lot more. Subcategories are a lot more types of categories within the expenses. For example, you might be breaking out your expenses like by location or something like that. And then you've got different expense expense line items. You could have different expense accounts for basically the the different depreciations, in which case you might want to group them together when you actually put them on the financial statements. You might have different expense accounts for the different types of insurance that you have. And for many of those different categorizations. For example, if we had different variants of depreciation or different variants of insurance than we might make up parent account colon, that just insurance expense and then have our subsidiary accounts underneath it. And that in that way in accounting software, the software can often take care of that by either presenting in an extended format where we see the sub accounts or condensing them. And that's one of the major types of things you end up doing with the income statement. The other thing you might do. With an income statement, if, if, if you've got this from accounting software, it's going to be in order by just alphabetical order within the expense area unless they use account numbers, even if they use a count numbers. You might further go back in here and say, I'd rather try to maybe organize my expense accounts by the largest to the smallest or something like that. The more relevant accounts up top as opposed to whatever order they happened to be in. Because that might make it look a little cleaner and that might also make it more relevant in that she got the more relevant expenses up top. We're going to we're going to create this thing. I'm just going to call it an income statement. Income statement. And I should do the date, date, and whatnot, but I'm just going to keep it generic income statement. I'm going to highlight a couple, a couple of snakelike three of these do our header thing with the font group, make this black and white, black and white on the header, as has been our custom, making this a little bit larger now, dragging it to the right. And then we'll make our subcategory for revenue. So oftentimes when you're looking at the income statement, if you look at a QuickBooks or something like that, they often group the income accounts under the subcategory of income. I think the revenue might be more professional type of named than income or sales. Sales might be an account, but she'd often group it under income also will often call it an income statement as opposed to a profit and loss. If it's more of a formal presentation, you can't change that. You can change, in other words, the income statement header and a lot of accounting software, if it's presented as a profit and loss, you can't really change the subcategories for revenue. Oftentimes it's gonna be called income up there. Not a big deal, but that's something you could do differently if you so choose if you wanted to, try to make it sound a little bit more formal than we're going to pick up the equipment equipment rental income. I'll just copy this down. Copying that down. I'm going to indent now alignment and indent. And then I'm going to call this total revenue. Total revenue, your total revenue. That's what I called it. That's what I'm going to call it. A line MET, increase the indenting two times. I'm going to put this in the inner column because it's a subcategory. Their credits down here, I don't want credits on my statement because this is a plus and minus thing. So I'm going to flip the signs by putting a negative of the equipment of the 2000s to six. And then I'll copy that down. Copying that down. Now notice that once again, within this subcategory, it put, it put the one in alphabetical order up top because that's how we're mirroring what would happen in the accounting software if account numbers were not used. So let's see if we can shift this around a bit. I'm gonna, I'm gonna make some space. I'm going to put my cursor here. Now you can move this by putting your cursor on it. Or you could say Control X cutting it, which is probably faster, and then control V. And then I'm going to take this top one right there. I'm going to bring it to the bottom because that's the smallest number, Control X. And put that down here, control V. It'll keep the formulas that way. So we're still pulling the formula over. That's still good. And then I'm going to take this whole thing and Control X and control The put it right up top. So now it's an order like kind of the most important ones up top there, equals the sum in the outer column. So there we have it. Let's put an underline here, font group and underlined, by the way, if it's a long income statement, you might want to put the whole income statement together first and then start manipulating the ordering so that you know that you got to the proper bottom-line number and you're not then trying to figure out what you missed when you're trying to reorder things. So you might want to get to the balance first and then go back in and clean some stuff up in such a way that you know, you're not gonna be out of balance and wants imbalanced. So now we're gonna go back down again. We're going to say the cost of goods sold is going to be the next item here. Cost of the goods that are sold. Important for inventory related, related companies are going to put this in the outer column. This is going to be equal to u, that expense account of P45. Whoops, I missed it. I put the skinny. That's the skinny column with nothing in it, not the skinny. We're taking the 45954 Enter. There, we have that, that's gonna give us the gross, the gross profit. This is gross profit. I want profit, that's not gross. I don't know why the news popped up there. This will go to this minus the 45 Nine 45 will put up a underline here. So there we have it. And then we're going to get, we're going to say these are gonna be the other, the other operating up. I think I haven't been put in capitals and operating expenses. Maybe I should put a colon at the end of it. Now you might want to start off by just pulling over all of the expenses in the same order and then we can get into adjusting them. So that'll be the, maybe that'll be the easiest thing to do. Let's say this is equal to. And then we'll pick up. The bank the bank service charges. And then I'm just going to auto-fill down until we get to utilities, not including these last two because that's gonna go into another category. So Enter. And then I'm going to say this equals in the inner column, the, the amount of 35 Enter. And then I'm just going to select those two and auto fill it on down. Auto-fill. Auto-fill, down to utility. Look how perfect that was. I'd went right down to where it's supposed to go. And this is gonna be the total total other operating expenses. And then we'll sum this up in the outer column equals the sum of these items. We can double-check that number now, 2514 by just adding up these and says Did I get the same numbers I should because I pulled them directly from there. 67 for 68. But no, because I have I have I have cost of goods sold. Hold on. Do it again. Do it better. 2151421514. There it is. So now I can say, okay, what do I wanna do with this? Maybe the bank service charges, maybe I should include that and miscellaneous. So how about I get rid of the bank service charges? I'm going to try to do it in a way where I stay in balance here. And I can even get myself a double-check number, maybe by taking this minus the sum of these of these to give me herself that check number. So that when I do stuff, I don't do anything, it's going to mess me up. If that goes from 0, we have a problem. Houston. Houston, okay, here we go. Deleting this, we're going to delete that. And maybe I put that into miscellaneous here, double-clicking plus. And then we're going to pick up the bank service charges and they're putting us back so we're back matching out. So there's the depreciation, insurance, internet, miscellaneous, maybe I put the Internet into utilities because that's kinda small right here. Maybe I'm like, Yeah, maybe that should go into Utilities. Let's take this out and put it into utilities down here. Make it a little smaller. We'll say Internet is now in utilities, and then miscellaneous office supplies, payroll breaking up the payroll taxes, we could say maybe I should just have it in one account, payroll expense, including payroll taxes. Now. I'll break it out. Okay. Now let's mess with the order. So let's say what looks like the payroll is the biggest one. So let's move that up top. I'm going to say Control X. Move that up here. And then I'd like maybe the payroll taxes to be right underneath it because that just makes sense. Even though it's not the next biggest ones. I'm going to move this down, cut Control X, and paste it right here. And I'm going to move this one underneath the payroll expense control x put in right there, Control V, and then depreciation. And then maybe utility should go after that. So I'm gonna, I'm gonna select these and say Control X and put that one down, Control V. And then take my utilities here, Control X and control V. And so that looks good. And then miscellaneous is typically on the bottom. So let's take actually the supplies. I'm going to move the supplies down Control X, Control V. Let's take the utilities should be underneath. The miscellaneous should be at the bottom, Control X, Control V. And then I'll take these three. I think that messed up my subtotal here because but that's okay. Hold on. Because this is not this needs to be taken that into consideration, but that's okay. Well, let me do it. There. Now we're back. We got the 0. We didn't do anything funny. We're going to take this whole thing Control X, right there. So there we've got a bit more condensed thing looks a little looks a little nicer, doesn't it? I feel like feel like it's nicer. So then I'm gonna take control X and put that up here. We'll get rid of our Xero thing on the right. Looks very nice. So then I'm going to then let's do a selection of these items and go to the alignment and indent, and then select this item alignment and indent. Let's put an underline under the 185 font group and underline. There we have our total other operating. So then we're gonna give R, that's gonna get us to our other. Or let's call it, let's call it net operating income. Operating income will subtract out the gross profit minus the other total other operating expenses. So we're looking 1743. I can double-check that number now I could just select all this stuff down to here and say, does that check out, do I get kinda do a double-check on that 11743? Check check. Double-check has been passed. And then I'm gonna put an underline on this one. And then I'm going to put these last two because they're not part of normal operations at another category, other income and expenses, you could break them out into other income and other expenses, but I'm going to group them together. So it's a little bit more condensed. Other income and expenses. Now because I have both income and expenses, I want the income to be positive, the expenses to be negative. So I'm gonna pick this up and maybe, maybe down here, the expense is more important. But let's, let's first pull them in before I start messing things up. And so I need to flip the signs because I need to say negative of the gains because I want the income to be positive. And it's a credit down here. And then if I copy that down, there's our expenses which are going to be negative. In this last category. I'm going to do an indent here. We're going to indent. And then I might say, well the negative one is more important. Maybe it's bigger, maybe I put that one on top. Maybe I've reversed this control X, put that underneath control v. Take these two control X and put that up top Control V. Maybe I'll do that and this is going to be total other income and expenses. There's no Maybe about it. I did do that. It's not a possibility. It's a certainty. We're going to indent twice here. We'll put an underline here, font group underlying, summing it up and the outer section equals the due to the m shift nine. Picking those two up, we're at the 419. And that will finally get us to the bottom line called net income. Net income will put an underline here, font group and underlying our last stop along the way was the net operating income will add that to the for 19, which will be a subtraction because that's a negative number. That gives us our 1324, which we could tie out down here because that's just summing up our column. And because we have such a well crafted and beautifully constructed trial balance, then we can check it. Check it out with a double-check right there. Easy. And I just say check, check, boom, boom. Double-checked. And then we'll put a, we'll put a two underlines, font group, double underline, W, underline. There we have it. Let's make it, let's do our blue and border thing, will go around this thing and say, let's blue, blue and border rise it. We'll go up top font group blue, right there and borders. Let's check the spelling while we're here today, I do any spelling, some horrible, horrible spelling thing. No. Shockingly, no. Apparently at least spellcheck says, I trust you spell check. You failed me in the past, but usually you do a pretty good job. So then I'm going to make another skinny. We're gonna do the statement of equity, statement of equity date, mets of t. Increase the indentation here. We're going to then do a black and white Cells Up top font group and making this black and white. And then we're going to say that this is going to be a beginning balance, the beginning act quit t. The beginning equity is going to be from the trial balance. That's gonna be our line item here. And usually there's nothing posted to it unless usually if there's anything that is both to two, it's gonna be draws or investments and we broke them out into separate accounts. So we're going to start off with that beginning equity number, which is going to be negative of because I don't want it to be a negative up here. I need to flip the sign negative of the 77895. We're then going to say that we have the change in equity, change in t colon, which is going to be equal to the owner invested, the owner invested. Let's do it this way. Draws. And then the owner investments. Owner investments. I'm going to flip the signs here with a negative kind of flip the signs. And so there's the 500. I'm going to copy that down, copy that down, copy that, Roger out copy 104. And then we need this one. Maybe, maybe the owner investments should be on top because it's larger. So now I might say, okay, let me cut this control X and put that down here, control V. And then I'll take these two control X and put them up, chop controlled b. And then maybe we then put the total down here, total change in equity. Do some indentation, Home tab. Alignment in debt and then indent once again underline under the 500 font group underline. And I'm missing net income. That's kind of important. Let's take these two control X and I put it down here, control V, and put net income, which is coming from the income statement. Income statement. The income statement makes a statement about income with a loudspeaker. And then we'll go to alignment and indent, pick up the net income. That's important. Movie in Port tante. So now we're in LM seven equals the SUM shift nine. Adding those up, we're at the 65824. That'll give us the Indian Act quit T, which will then sum up in the outer column, the SUM underlined here, double underline there. We can double-check that last number, the 1439 or 719, by just selecting these items in the income statement below, we just select them and we say, and that should add up to hold on a second. All the blue all the blue. Every blue component should be the 1437194319 double-check. There's two checks have happened, otherwise known as the double-check. So let's go ahead and blue, make this borders and blue, border blue. Let's put this in the middle now, so we'll put the statement of equity in the middle. We could do that by going to the Home tab and then doing this. But that makes that one fat cell, which I don't like that. I don't like that. So I'm going to undo that. I'm going to right-click and I'm going to go to the Format Cells. We want to go to the alignment horizontal center across the selection. So much better. Mucho, mucho met hoarder. And then we're gonna do it here as well, selecting those items, right-click. We're gonna go to the Format Cells and then alignment horizontal center across the cells and enter. And there we have done it. Let's unhide the balance sheet and check double-check our totals here. The total is totally need to be double-checked. Unhide. And we're way over on the end. Okay, so if we look at the balance sheet, we're in balance. We're in balance. The total assets equals liabilities plus equity. This equity though, needs to become it from my statement of equity. So I'm going to say if I pull this from my statement of equity, does that throw me out of balance? My out of balance now, know, still in balance. Still in balance. The income statement ending at net income, that's gonna be tied into the statement of equity, which is right there. So at times out. And we know that the Indian statement of equity number was on the balance sheet. So that ties out. Now remember our accounting software also. When you get to this statement of equity, they might not have it really, they might not really like generate a statement of equity and like a QuickBooks software. Because what they do is they stuff like the income statement number into this area, the equity section of the balance sheet. And that's not exactly that's not generally how you would you would report for presentation purposes. It's kind of nice for bookkeeping purposes to kinda see how the income statement is tied out and whatnot. So that's another area where if you want to make it look a little bit cleaner, you kinda, kinda have to basically in some softwares depending on how they do it, kinda do clean that out and then basically do the same thing with the equity over here. And you can see it's basically the same kind of concept. You would take the equity before the net income and then add the net income to it. You would also on the on a normal like a QuickBooks type of report, have in the equity section included other equity accounts which would be the owner investment and draws, which again, oftentimes on the balance sheet, you wouldn't really have that detailed inequity. You would just have the one account. And it could in the same thing would apply if you had, if you had dividends for a corporation, you might not put the dividends like in the equity section. You might put them, you might put them in a statement of equity with more with more details. So those are some other options to kinda clean up that she might not have the capacity to do to make those changes. If you want to, within the accounting software, you'd still have to basically clean them up if you want to make it look a little bit more formal. For presentation purposes, a lot of the income statement changes we did where we're basically things that we can collapse the accounts. But one of the things we did that was a little bit different is ordering, ordering the ordering of the account, which you can do with account numbers in accounting software. But even when using the account numbers, you don't always get to the point where the most relevant accounts are on top every every month because it might change from month to month. So you could still do some cleanup work just in the ordering of accounts. That's more difficult to do in accounting software then. So it's something that you could if you want to make it look a little bit more formal, export the reports and even accounting software and then reorder them in what you think is the most appropriate order into clean things up there. You also might have some preferences in terms of how you like your indenting to do what's your font, your favorite formatting is in those areas as well. Which again, you can do obviously in Excel, most database programs like QuickBooks having the ability to export reports to excel. And then, and then do any more formatting you would like from that point in time or taking the trial balance and then basically constructing the financial statements from it.