Adjusting Entry Prepaid Insurance Excel 10.40

QuickBooks Online 2021 adjusting entry related to prepaid insurance with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are in our get great guitars practice file, we’re going to be opening up our financial statement reports balance sheet income statement by going to the tab up top right clicking on it and duplicate the tab doing it once again, right clicking on the tab up top so that we can then duplicate it, we’re going to start off with our profit and loss report selecting the good old P and L Profit and Loss income statement range, changing it up top to the endpoint of


Oh 228 to one, that being our cutoff date running that report close in the hamburger holding down control scrolling up just a bit to that one to 5%, then we’re going to go over to the tab to the left, go down to the reports once again, select then this time the balance sheet report balance sheet report range, changing it up top in point oh 228 to one, run that report, close the hamburger, we’re now talking about insurance. Insurance is one of those items.



That’s a classical example of a prepaid type of thing because insurance by definition means that you have to pay for it before you get any coverage. Therefore, when you pay for it, technically, you haven’t gotten anything, therefore it’s pre paid at that point in time. So normally, under an accrual system, then you will put the prepaid insurance on the books as an asset. So when we bought the prepaid insurance, we’re always going to put it on the books basically as an asset. And then normally, under an accrual type of system periodically, most likely monthly, we would then want to decrease that asset record in the amount of coverage that has been used up periodically as we go.



Now note that’s can be a little bit confusing, because we haven’t really you might think we didn’t really get anything unless we actually used the insurance and you know, got paid out some coverage for it. But we are getting something we’re getting the coverage, the coverage period, we’re getting the protection over a certain period of time, whether we use the insurance or not. And we should then be allocating the expense to the period that has been covered by the plan, not when we pay for the plan.



So the easiest way to do the adjusting entries is just to record the insurance every time you pay for it in the asset account, let it accumulate upwards and then periodically, either monthly or possibly at the end of the year. Adjust that note, however, that you might want to if you’re just recording the bookkeeping process and you’re recording the insurance and you’re relying on the accountant, then or the tax preparer at the end of the year to do whatever they need to do to insurance to make it appropriate for either taxes or financial reporting purposes, you might just record it as an expense, you can do the same kind of adjusting entry in the expense format.



Meaning if instead of when we purchased this, we just recorded it to insurance expense, go into the income statement, it would be down here in insurance expense, then the adjusting entry that would need to be done as of the end of the month or year would be to take it out of the expense and records part of it as prepaid insurance. The reason we don’t one reason we don’t typically do that is because the expense side of things is decide that closes out it has those temporary accounts that close out at least annually at the end of the year where they can monthly here as well.



So it’s a little bit more confusing. If you do it. If you try to do the adjusting entries that way if you try to record it as an expense when you pay for it, and then reverse out the amount that is not has not been consumed yet if you need to at the end of the period. That’s why traditionally, if you’re planning on doing adjusting entries, it’s easier to do it by putting everything when you pay for it into prepaid insurance and then expensing it as you go. That’s kind of the proper way to do it.



But you can kind of do it the other way. And again, if you if you want to make sure that you’re recording the expense, and that you’re working with an accountant or a tax preparer that makes sure that they pick up the expense and make any adjustments they need to do to it. You might want to put it on the income statement to say, Hey, here’s the full amount that we paid, here’s the range. If you need to make an adjustment to it, then then make the adjustment to it. But make sure you deduct whatever needs to be deducted.



Because obviously, if it’s a sole proprietorship, then they’re focusing in on the income statement to do the accounting or the taxes. Okay, so then that’s going to be it. So we’re going to do this first in Excel, we’re going to jump on over to Excel first to do this. Let’s go on over to Excel, I’m going to ungreen a five what we did last time, let’s um greenify these items, because we’re on a new journal entry now. So I’m going to greenify these. I’m going to go back up top we’re going to I’m going to hide the reversing entry columns because we’re focusing in on the adjusting entries.



Also note, however, that as we’re talking about the reversing entries, the insurance adjusting entry will not be reversed. This is what’s called kind of a permanent adjustment. This isn’t a timing issue. It’s a permanent issue of recording the amount of the insurance That has then been consumed. So let’s go back up top, I’m going to go from j to L on the columns, right click on them and hide them, then let’s highlight the accounts that will be affected in our adjusting entry, there’s at least two typically, classical or most adjusting entries will have a balance sheet account and an income statement account one above the blue items, which are the equity one below.



So up top, we’re going to be picking then the account that’s affected, which is insurance. So we had prepaid insurance up top, where is the prepaid insurance? There it is, I’m staring right at it, but I couldn’t see it. So I’m going to say there’s the prepaid insurance. There it is. And then down below, we’re gonna have insurance expense, this being the first month, we don’t have insurance expense on the books yet, so I need to add the account. Once again, we might add it into QuickBooks just to see where it lines up.



So let’s just go ahead and try that I’m going to go back over to QuickBooks, if I have access to QuickBooks, I would then say, Okay, let’s go to the chart of accounts, and then go down to the accounting. And the reason I say if we have access, if you’re doing this in like an accounting department, then you might be like kind of doing this separate from the QuickBooks file in trying to figure out the financial statements during the adjusting entries, and then working with the bookkeeper to enter it into QuickBooks. But if I have access to QuickBooks, and I’m doing this and I’m as part of the adjusting entry, then I might go into the accounting and actually add this account, I’m not going to add anything to it yet.



But that will help me to verify where it lands. Because once again, in the category of in this case, expenses, which is the largest category, it will then be an alphabetical order, I don’t have control with an account number to put it exactly where I want to. Now I can of course see where it should go kind of alphabetically, but there might be more accounts that are not affected, which are expense accounts, that will throw off where it should go to.



So I want to make sure I, I put it in the right place here. So I’ll test it out, I’ll add the account here, I want to say a new account. And I’m going to say it’s an expense type of account. So it’s going to be an expense account. And then we’re looking at insurance expense. So insurance, and then I’m just going to make it a generic insurance expense, we might have multiple types of insurance, we might want to have like a parent account of insurance, and then break out the different insurance, liability insurance and so on in that format, but I’m just gonna put it into the insurance here.



And it’s saying another account already using that name. So they already have an insurance expense, which is another good reason to be checking this. Let’s scroll back down and see where that live. So it is right here. So they already have it. And notice I don’t see it, I didn’t see it on my end, because of course, there’s nothing in it yet. So we don’t have anything on our trial balance, even though we export this from QuickBooks, and we can see where this is located, then it is right above the interests, which was what you would expect it to be in alphabetical order.



So we’ve got the insurance right above the interest. So I’m going to add it right above the interest. So I’m going to select these cells, just these cells, right click on them, and insert. And then I want to push these cells down, push them down. And notice that doesn’t have any impact in anything to the right or to the on to the left or to the right of it. And then I’m going to call this insurance. And notice how I’m going to pick up the same name that’s in QuickBooks as well, if I name it something different than the accounts could be thrown off, because it’ll be in alphabetical order, we’re gonna put the zero there,



I’m going to copy the formula down, or I could just redo the formula, the sum of these two items. So there’s the insurance when I unhide. These other two cells, I’m going to want to make sure that the formulas carry forward in in my other worksheets as well for the reversing entries. So we’ll have to check that out. And then if I go to the right, then we’re going to make our adjusting entry. Let’s make this green. What we had here is we have this 11,000.



We made this in February. So we’re gonna imagine we made it in February and that this is going to cover a 12 month period from the point of the payment, which was February if you want if you needed to check when that payment was made, you might then have to go back to the system here if you have access to QuickBooks, and then go into that, that account. So we could go into the balance sheet, double click on the insurance, there’s the 11,000 or click one time on the insurance.



There is on that 128 so we had it, we paid for it on 128. And we’ll say that it covers February, and then 12 months from that point in time starting February on. So if that were the case, then one month has been accounted for. Note that if we had just expensed it now,



and we were comparing like January to February, February to March, it would look January would look like it had this big expense which isn’t really fair from an accrual standpoint because it should be broken out over the time that it was used. Let’s pull up the trustee count. Later here, so I’m going to say calculator, trusty calculator, and just do the calculation then. So that means that one month has then been used. So we’ll say, all right, 11,000 for the year, and divided by 12, that would mean that we used about 916, point six, seven about.



So we’re gonna say right, this needs to go down then by that amount, leaving us 11 months that will then be covered for the next 11 months. And we should be recording the expense on an accrual basis. And I made the wrong one green here. And interest and insurance, this one should be the green one. This one will be debited recording the expense for this time period this month. Okay, so let’s record it, this is going to be a j, e five. And I’m going to say this is going to be equal to the expense, which will be the debit.



And then the credits will then be this item up top. Just obviously, to note this, what you would need to do that calculation is you need this item here, we would need the insurance policy, or we would need to know what the insurance policy is to know the dates that were covered. So we basically said it was a year starting February in a year from February. So that we would need to know that range so that we can do that that calculation that estimate of how much how much had been used, which would be one month in this case. So we’re gonna say then do that same calculation, I’m going to say this is equal to the 11,000 divided by 12.



Notice it’s rounded now because it rounded 2.67, meaning if I added decimals, it’s really point 666 on forever. But point six, seven, if I round it, that’s going to be the debit and the credit. So if we record this, then let’s record the expense first down below, it’s going to go right there in H 35. This is going to be equal to the 916 67, which increases the expense. And then it decreases the net income throws us out of balance with the red number up here until we record the other side here in H six, which will be equal to then the 916 67. And that will be bringing the prepaid insurance down to that 10,083.



Note that if I was to take this 916 point six, six, which is one month worth of insurance times 11 months that are left, then we have the 10,008 10,008 three, so you can calculate either the amount that should still be left in prepaid insurance or the amount that has been expended. If you work with book problems related to these adjusting entries. They may ask it either way, they might say what should still be in prepaid insurance.



And you could say, well, it was 11,000 and then divided by 12, because it was 12 months. So that would be per month. And then we know that there are 11 months left times 11. That’s what should still be left. How much should we expense. If they asked that? Well, it was 11,000 divided by 12, breaking it down to a monthly basis. And there’s only one month that passed. So times one would be that 916 which is of course what we recorded down here in the expense item down there.



So that’s going to be our adjusting entry. Once again, there’s no reversing entry related to it, it does have impact on the net income decreasing the net income, here’s our total change thus far to the net income. Next time we’ll enter it into the QuickBooks system nothing too special as a problem to enter into QuickBooks because we don’t have any sub Ledger’s that we have to deal with. So we’ll we could probably use them the journal entry, the journal process, to actually use the journal to enter the journal entry and we’ll do that next time.

Leave a Reply

Your email address will not be published. Required fields are marked *