QuickBooks Online 2021 reversing entry related to unearned revenue or customer deposit with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are in our great guitars practice file, we’re going to start off opening a couple of reports by going up top to the tab right clicking on it and duplicating that tab. We’re going to do it one more time, this time right click on that tab again, duplicate it one more time, we’re going to be opening up the balance sheet and income statement or p&l Profit and Loss I ran out of breath there, we’re going to go down to the reports on the left hand side to do so. And the first one we’ll open up is the P and L report.
00:42
So we’ll go to the reports left hand side selecting the profit and loss report the P and L we’re going to scroll up top arrange change o two to eight to one and run. And then we’re going to go to the hamburger and close that up, hold down Control scroll up just a bit, then we’re going to go to the next tab on over and we’re going to go to the reports and this time the BLS balance sheet is the one that we want selecting the balance sheet, and then we’re going to range change it Indian at Oh 22821 and run and then we’re going to go and close up the hamburger.
01:19
So in a prior presentations, we’ve had been entering the adjusting entry related to the unearned revenue, that adjusting entry a little bit different than the standard unearned revenue adjusting entries, you’ve seen a book problem. In our case, we have the negative accounts receivable here in the accounts receivable that we took out of the accounts receivable and then recorded it down here in the unearned revenue to reflect the fact that it shouldn’t be really a negative receivable for financial reporting purposes, but rather a positive liability.
01:49
To see the detail on that let’s look at the customer balance Detail Report. Right clicking on the tab to the right again, I’m going to duplicate it again. So that we can open another report. And this is going to be a customer who owes you type report. That’s how the QuickBooks categorizes these things. So we’ll go to the reports on the on the left again, and we’re going to do it, we’re looking for the category of who owes you, that’s what they categorize it under who owes you and then we’re going to go to the customer balance detail, customer balance customers owe us and then I’ll change the date range to customize it ending at oh two to eight to one and run closing up the hamburger.
02:32
And of course the detail is going to be down here, down here. And this is our adjusting entry that we did with a journal entry. If we leave it the way it is, now, it’s gonna cause it’s for sure gonna cause a problem to the accounting department, meaning we did what we needed to do to make it correct for a financial statement purposes, or you can think of for tax purposes, whatever you’re doing it for whatever basis it is, whether it be at the month end or year end.
02:57
But if we don’t adjust it, then we’re left with this journal entry in the Detail Report. And that’s not good, because there’s a reason that that that the accounting department does it this way, the accounting department does it this way. Because this negative payment can be linked to the invoice that’s going to happen in the future. That’s why it’s being done this way. So the fact that we now have this, this unearned revenue on the on the balance sheet over here is correct for reporting purposes. But it’s not what they’re currently using logistically. And so we don’t want to just leave them hanging with that in their customer detail.
03:34
So we got to reverse it, that means we got to do a reversing entry. So we made our financial statements correct as of the cutoff date. And now we’re going to reverse it back. So logistically, it’ll be correct for the accounting department to continue doing what they’re doing. Because what they’re doing, it’s fine, it’s just that we need to adjust it periodically, if we need to, due to the fact that it’s a negative receivable at the timeframe when we’re going to be reporting the reports to somebody else. So now we’re going to do a reversing entry. So we’re going to call it a reversing journal entry. Number nine.
04:05
And I’m just going to reverse exactly what happened above it. So we’re going to reverse it exactly just noting, however, that we will be reversing it as of the first day of the next time period. So March 1, we’re going to reverse it as of March 1, you might also be thinking, Well, why don’t I reverse it, like, at the point in time that that the payment is received, you know, because, because then we would be on the proper basis for a longer period of time.
04:32
But we’re always going to reverse everything as of the first day of the next period, because that’s the easiest thing to do logistically so I know where all my reversing entries are. We don’t want to stagger the dates of our reversing entries. We just would rather have them in one spot. And I’m also going to reverse it by not trying to change the debits and credits to put the debits on top. I’m just going to reverse it top to bottom same accounts and just reverse the debits and credits here. I think that’s the easiest way to do it. At least for me. Then I’m going to go back up top.
05:01
And then we’re going to do this not in the adjusting entries columns, but in the reversing entries columns, which are currently hidden between this column I am. So I’m going to highlight and select from column ID m, so that I’m column, I’m column, we’re going to select that, and then right click and unhide unhide.
05:20
So now we’re going to be working in this reversing entry column. If you don’t, if you do not want to get mixed up and accidentally recorded to this other blue item, because these blue lines kind of draw our eyes, that’s why we made a blue tick, because that’s what we’re going to record them to, then I’ll hide these two just for now while I record, so that I don’t mess up and actually accidentally record there.
05:40
So I’m going to right click and hide these ones. So we’ll hide that. So now we’re just going to do our reversing entries, and the accounts receivable up top, we’re going to reverse that one, I’m going to double click on this, go to the end of it, I’m going to say plus, because there’s something in it, and then we’ll scroll back down to the transaction we’re on which is way down here, we want the credit for that 300. So the credit for the 300. The two things in here now being d 16, and D 35.
06:10
And it’s really nice up here, you can also use these little tools to kind of see what’s in there. And that’ll point to him right here. That’s why Excel is really transparent. That way, I highly recommend using these tools that they’re in the formatting, tab and they’re in formatting and auditing these two tools and not put them up in the toolbar up top because they’re super good tools, in my opinion. So I’m going to then close that back out and then go down to the unearned revenue, reverse that.
06:38
So we are in j 25, sale, J 25. Scrolling down, and then we’re going to reverse the unearned revenue, and it goes back down to zero. So there we have it. So let’s go ahead and unhide the cells now and see what we have in total. So I’m going to go from F column F to column I. So fi, we’re going to go five columns, and then right click, fee fi fo from in unhide, hit hide instead of unhide. Let me highlight D to J now. And unhide unhide, not hide. Okay, so then in the accounts receivable, we’ve got you know this, there’s two journal entries in here.
07:19
So once again, now it gets kind of confusing, because there’s two things happening. And then we reversed those two things. That’s why these little blue lines are super neat. So there we got that, and then it reversed out. And then here’s the one we just did. And then it reversed back out. So we’re back to where we started at except, of course, the second one we’re talking about as of March the first day of March. And there’s no change between these two columns.
07:43
Because we’re talking the balance sheet accounts up top, and they’re going to be the same. Whereas the post closing trial balance on the income statement is where the difference will be. Now also note down here that I have this staggering problem. And I kind of messed up the worksheet How did I mess up the worksheet, we have these items hid. And then when I added cells, for example, we added some cells for these two, I wasn’t careful to include the hidden columns.
08:10
When we added those I should have included one more column over in order to include those cells. So now I’m going to have to adjust this. So I’m going to give you a quick adjustment thing to do this will give you the worksheet as well, so that if you if you run into this problem, you don’t want to try to fix it this way, then you can pick up the worksheet we provide you but let’s try to just fix this, I’m just going to reconstruct the second half of the worksheet here, the one piece that I really need is this item, I can see where that’s going right there, that’s going to this journal entry.
08:38
So I need that journal entry to line up and I know where it goes, it’s going to go right against this one. So So what I’m going to do is grab this bottom piece, I know that needs to line up, the green item has to line up here. So I’m going to pull that down. And that means this this journal entry, at least lines up to the correct row in that way. So and now I’m just going to reconstruct what these two columns are. So see this column is summing up the adjusted balance and the reversing activity. And I should be able to copy that all the way down.
09:10
So I’m just going to recopy this all the way down is gonna say let’s just take that all the way down. And there it is, that puts me back in balance here. So that looks right. I don’t think I have any other transactions in this column. I’m just going to rebloomer fi these items. It looks like this is coming from the right spot, I believe. So the service item actually this service item here, if I if I see where that is coming from, I could see that should be going to the cost of goods sold. So it looks like we have a staggering problem here this needs to go to cost of goods sold.
09:46
So let me I’m going to bring this down to cost of goods sold and then this one is going to the sales of product So we’re currently in the rental. So I’m going to bring this down to the sales of product line. So that’s right. And then this one of obviously is unearned revenue. That one’s right, I’m going to rebloomer fi, this cell, and then this cell. So I think my adjustments are in the right column. Now, I hope, and we’ll double check them by checking them against QuickBooks as well.
10:23
And now I’m going to I’m going to redo this column. Again, I’m going to copy this all the way down. So we’ll copy that all the way down. And I think that’s right. Now, let’s see, see if anything is off on the fourth column. So I’m looking at this column here, if I double click this, I’m adding these two columns, that’s correct. I’m looking here, I’m adding those two columns, I’m looking here, something is wrong here. Because we are adding here, the net income. So there’s a staggering issue there, I’m going to get rid of this NPS.
11:01
So there there should be nothing in opening balance. And then here, this is where the the post closing trial balance, I want to take this number and then this is where I should be adding plus the net income, because the net income, then down here is closing into it. And then and then this one is going to be the adjustment, this one should be the beginning balance and the adjustment. So this should be equals the sum of these two. And now I’m on the income statement.
11:31
So the income statement down here, which should just be our adjustment, because we’re in the post closing trial balance, it should only be the blue item. So I’m going to pick that up and pull this down. And write to there. And that should put us back in balance. So hopefully I didn’t mess anything up terribly. We can we can double check it. One way to double check it is we can go into QuickBooks here and say, All right, let’s see if we run some reports and see if we still line up, I’m going to go to the income statement.
12:02
And let’s say we run the income statement for Oh, let’s make it Oh 1012120 220, let’s say, Oh 30121, and then run that report, I’m going to double check the net income down below, which is the 694 110. So that should be the 694 110. So that looks good. And so and then if I go to the next date range, let’s say we make this let’s just Is that right? 69412. Yeah, and then I’m going to say let’s make this as of just oh 30121. And run that report. Now we’re at the 1975, we’re at the night, we’re at the 519 75 here, and that’s because this sum, the sum total is adding this 500 that’s in the owner’s personal expenses. So it should be stopping.
13:06
Actually right there, it should be one lower. So I’m gonna pull this down right there. Now check this out for this one. This one’s This one’s picking that up, too. So I’m going to copy this all the way across. Because the income line item should be stopping at at the income line. But right below the blue line. So now we’re at 1975. And there’s the 1975. So that looks good, let’s just check each account, because there’s not many of them on the income statement. So we have the 380.
13:41
And that’s in sales of income. So we got the 380 sales of product income, right sales of product income, and then we got the 304 that should go back to the cost of goods sold. So 304 of the cost of goods sold. And then we’ve got the 5625 two interest, the 5625 two interest, okay, so hopefully I didn’t mess anything up terribly there. But you can see how you can kind of reconstruct your, your worksheet without too much trouble. I mean, I know I’ve done this a lot. So I probably did that way too quickly. But you know, you can reconstruct it without too much trouble if you if you kind of mull it over. It’s a good exercise to, to do that.
14:23
And also just realize that if you’re hiding cells here, if you’re hiding cells like this when you add another column, so next time when I when I hide, let’s just practice this, if I right click and hide these cells, which is not let me do right click and hide. Then if I was to add an account, let’s say right here, instead of stopping here, I should go one cell over so that I make sure to pick up these hidden cells, and then right click and insert, insert, and then I could shift cells down.
14:57
That’s what caused the problem. I didn’t go one cell If I wasn’t careful, and then if I unhide the cells, then it should pull all these items down with it. Right? If I didn’t go one more cell over, that’s what I that’s what the problem I did was. So I’m going to undo that. I’m going to say Undo, undo, undo the hide. And so there’s where we are at this point in time, we will provide the worksheet so you can take a look at it on your side as well.
15:25
And then next time, we’re going to go ahead and enter this adjusting entry or this reversing entry into the QuickBooks system. Noting that we got that issue with accounts receivable that we got to make sure that we tie it out to a customer or use some other kind of accounts receivable account. That’s not an accounts receivable type account, like an other current asset account.