 QuickBooks Online 2023 mileage tracking reports by month using Excel and Adjusting Entry. Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023. Support Accounting Instruction by clicking the link below giving you a free month membership to all of the content on our website broken out by category further broken out by course. Each course then organized in a logical reasonable fashion making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems PDF files and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. Here we are in our QuickBooks Online test company file using the account view as opposed to the business view you can toggle between the two views by going to the cog up top and switching the view down below. Duplicating some tabs to put reports in like we do every time right clicking the tab up top to duplicate it right clicking the duplicated tab to duplicate it again back to the tab to the middle as the one to the right is thinking reports on the left hand side we want to open up one of the favorites that being the balance sheet report of course let's tab to the right and then go to the reports again on the left and close and let's open the profit and loss the P and the L closing the hamburger changing the range from I'm going to start off from 010123 to 123123 run it and then I'm going to tab to the left and close up the hamburger and change the range to the same 010123 to 123123 and run that now in prior presentations I'm going to go to the tab to the left we've been working on the mileage tracking feature within QuickBooks and we've been entering data we entered data for one month of basically miles and we talked about how we might use this data to do an adjusting entry so we can track our information for tax preparation using a mileage method and even enter an adjusting entry possibly into our QuickBooks system so we have a nice little reconciliation right there so now I'm going to do the same kind of thing for the second month and see how we can basically sort our report that is here when we download the trips if we wanted to sort the reports in a bit more complicated situation where we had possibly multiple months and we want to break it out by month and then we have personal data as well as business data possibly tracking information not just for the miles here but possibly for a charitable deduction that might be helpful for taxes or for medical type of expenses that might be possible for taxes as well so the main reports that we have is they give you this little summary page up top but the other reports isn't in the reports area it's just going to be this export to a CSV file which you can then convert to an Excel file if you have a more complex type of report that you're going to be dealing with so we'll play with that for a while now if I go to the tab to the to the right last time we imagine that we stopped and we entered an adjusting entry at the end of May so let's make this the cutoff then as of 05-31-2-3 and let's adjust it here we turned on the classes to do that and run this and then we had this nice little breakout using our classes so that we can have our totals not really messed up but then have an adjusting entry to adjust for the fact that we're not using the actual method but the mileage method for our column here that's basically on a tax method and this is kind of like our adjusting entry column over here all right now let's imagine we do some data input for the next month in June so we have some data that we can export so let's say we're adding our trips which we can do with the mobile app so as you have the mobile app if it's just tracking your miles as it's pulling into the information down here it might be unreviewed or you might have automatically categorized it between the business personal and so on that pulls in or you can use the manual tracking up top will enter in manual here so I'm going to go up top and I'm going to say add a trip and then I'm going to say this is happening in June now so we're in June and let's enter a couple of these some of the standard ones we have the office to our client so we go from office to client number one and so there we have it and we have our round trip we'll keep it at the normal Toyota default and we're going to say that it's going to be a meeting that we're going to once again so we'll say save that and there's the calculation notice the calculations here are summing up for the entire year which is perfect for you know taxes but you might want more detail month by month if you're doing you know your information on a month by month breakout and you might want more detail on your personal side of things which could include the the medical and uh and uh say charitable I'm going to add this to the business side of things I'm going to add a meeting let's do another one let's say we have another one that happened once again in the following month let's say the second and let's say this one is going to be from our office once again this time to to client uh to I'm going to put on the round trip and we're going to say it's for a meeting once again boom so we've got that one I'm going to pull this one over to the to the business side of things and so there it is and so well I need a reason a meeting so now we've got this populating uh kind of automatically so then up top let's imagine we have some other kinds of trips so I'm going to say now that I want to put in my favorite locations and let's do another one another favorite location here it happens to be the 944 let's do so I'm putting in the 1013 North Roxbury Drive and I'm going to say this is going to be for charity let's say this is some kind of charity that I'm going to so I'm going to say that's going to be a charitable kind of item let's save that it wouldn't let me save it because it wasn't coming from google maps so I basically refreshed the screen so it was giving me this nice drop down and then I'm going to say it's charity one let's say so I'm going to say all right let's do that and then maybe if I was using my mileage tracking app I might make a rule for it so I might manage rules down here and say we're going to say this is a mileage rule and the starting point is going to be my office and then we're going to go to charity one and this is going to be on the personal side and we'll say Toyota that's could apply to past trips no I'm just going to say boom and so hopefully that will pull over automatically when I make trips say with my mobile app to the personal side of things so I'm going to keep that and then if I add a trip and let's say this happened sometime in June let's say 5th and I'm going to say that this is going to be so notice when I manually input it here I still have to put in the points so I'm going to say it's from my office to the charity one but if I was using my app and driving around that's when it might automatically pull into this information here and possibly automatically assign it to the purpose which I said was personal now I'd like to put it over to personal and then give it another designation of charity or medical type of of transactions right so I could say it's a charity transaction round trip and then save it and so once again it didn't put the other side of the round trip in here but I'm going to save that on over to personal as well so on the personal side we have those in here so we've got these items on the personal side okay and then let's do another one I'm going to add a trip and we're going to say this one is going to be sometime in June and we'll say the 13th and we went from our office to the end point of the charity one again and let's say that this was personal and I'm going to say it was charity round trip boom and then let's add a medical one as well so let's say I'm going to add another one let's make a rule a mileage rule and I'm going to say another rule this one is going to be the starting point in my office and we're going to go this time to see if this drop down thing works this time this is 2002 Loma Vista Loma Vista 29 20 30 million dollar place here personal Toyota round trip it so if I was using the app it should do that automatically I'm going to save it and there's our another our another item and then I'm going to but I'm going to add the trip manually here say this happened on the 15th of June we're going to say starting point office again ending point is going to be uh hold on I didn't the ending point is going to be let's just type it in again I'm going to say it's going to say 2002 Loma Vista boom okay I should have saved the location but as a as a favorite but in any case I'm going to say that this is going to be personal and I'm going to say this is medical and round trip so now I have that and let's add one more let's add a rule this time I'm going to say not a rule let's add a a favorite location and let's say that Loma one's a favorite location so I'm going to say this one is a favorite 2002 Loma not Lima Loma Loma Vista and I'm going to say this is medical one it's not really my favorite because I'm going to the doctor and they do horrible things to me over there and tell me that it's for my own good which I have doubts about but I don't know I don't know what's going on so I go anyways any case so now we're going to say add a trip and we're going to go 19 and we're going to go from our office to medical one personal but this time I'm going to make it a medical trip which could possibly be deductible as well so we've got those different designations round trip it and boom so there we have that now I'm going to enter a little bit some data here for the following month which is going to be May for actual data that would come through the bank feeds right it would come through the bank feeds and so let's add like a bank feed type of transaction with just an expense form the actual expenses we're going to pay for gas and whatnot well they'll say this is the gas station that is quite high they keep on blaming the gas station but I know it's inflation it's not really the gas I'm not going to get mad at the gas station guy it's the inflation's doing it I feel like but whatever so and let's just pretend gas went up I'll put it in there for 200 this time or let's just say yeah let's just say 200 and then I'm not going to put it to any class and then I'm going to save and new and let's just put one more in there and let's do this one as the this was like the parking stuff because that's the one that could be included even if we do the mileage method so parking and fees populating automatically let's awfully high let's say it's like 150 all right so there are those let's save and close it this time and if I go on over to my my profit and loss I could run it by class but now I want to see May so I'm going to go from 05 let's say 050123 to 0630 that's not a zero 0632 3 and then I want to see it not by class but months month by month so there's our June May and June actually I entered the other stuff before May apparently let's go 010123 so I entered this stuff in January and February and there's my June stuff okay so now we're focused in on June so this is my actual expenses so now we have a bit more complex situation with our data over here this is my actual year to date information but I kind of want to break it out and include my personal which is the medical and the other and the and the business so what I can do is hit my report up top and just download the trips just like we did before it opens in a CSV file which we can open in excel but when you want to adjust it in excel you got to save it as an excel file or it's not going to save all your stuff that you do to it so I'm going to save it I'm going to put it into this random picture file it's going to be an excel workbook that's the point that's what you have to do so then we'll save it there and then we can pretty pretty simply put this into a table even though it's a lot of data so let's just see if we can put this in the table and manage it fairly easily I'm going to make the top row I'm going to make bold and then I'm going to convert this data into a table so then I'm just going to put my cursor in it and say insert and make it into a table please boom and that in and of itself makes it fairly manageable so now I can if I want to see the the I'm going to make this a little wider and these are the places we went so if I wanted and then if I want the totals down here I can add a total table and then the totals down here this is probably going to want to be a sum function and this is going to be a sum now if I wanted to see just the business stuff I can filter this down I can say let's see just the business like that and so now we've got the business stuff and if I wanted to see the business stuff just for the current month then I can say show me the business stuff just for June get rid of May and so I can filter it down like that which is quite nice and of course I can put all the dates back and I can also filter it if I wanted to see my my information uh personal versus non-personals so I can hit the drop down and I have my personal and then I have some unreviewed stuff apparently that I didn't put in place that's not good but let me just check that out I had some unreviewed items so I'm not going to bother with that you'd want to review them all before pulling them over but uh then if I wanted to check this out notice here it has the personal so I can say let's check out the personal stuff this way and then I can further break it out by medical versus charity which also could have some tax implications so I can hit the drop down and say I just want to see my charity miles and not the blanks and so there's the charity so that's pretty that's pretty neat we can do that with a table and we can sort this information possibly tracking our charity versus our medical and if we had you know so we could sort our data in a in a couple different ways and you can use these filtering options for charity or medical for whatever you want as another filtering field if you want to track whatever is important to you to track under that category so that you can export the reports and see how much you drove to that category and have a log of it so I'm going to unfilter this and then the other way you can sort this data is with a pivot table let's say I'm going to add all this back in so you could convert this into a pivot table once you have this data might as well do because it's a nicely formatted table for a pivot table we could insert a pivot table over here and I'm going to the whole thing I'm going to put in the existing worksheet I'm just going to put it on the side I got to make sure it's on the top so I'm just going to put it over here boom and now we can we can sort our information in a pivot table format for example we can summarize the data possibly by type I can pick up my type over here and then I want to say distance and so then it distances it and it puts them automatically in the sum function so now it's summing up you know everything all the distances all the miles so you get a nice tight little uh little summary data you might want to break it out by vehicle so I can break it up by vehicle and then distance so again it kind of sums it up gives you a little recap in a pivot table format so I clean those off another way you might do it as you might say that you want to break it out by date and so now I've got the dates on the left hand side and maybe I want then the distance which is now summing up in the totals and I can see kind of the breakout uh of the months here so now I've got the months and the totals and then you might want to also include a filtering option possibly by uh what what did we call it the type the type right so now the type got pulled into rows but maybe I want to use it as like a filtering option so now I've got the types up top here so I can say okay here's may june and then I want to filter this thing by just the business stuff boom so now that's another you know common kind of pivot cool pivot table thing you could do with data like this another common thing you might do is like type and then you might want the distance which is going to pull over here and then maybe you want to filter by this other further filtering down which was the personal that that that broke out into the trip purpose right so within the trip purpose I can put that maybe into the filter boom it pulls it down here automatically so it kind of has this added drop down but you could put it into the filters and so now you've got uh the business personal and the unreviewed but I can hit the drop down and say I just want to see it like the charity and then and then filter now obviously the business and personal go I mean the business goes away and now you just have the personal breaking out by charity and then you can do the same for medical right if we wanted to do something uh like that all right so just some good data to mess with some pivot tables with so if I go back on over here and I wanted this if I was entering this month by month you might use this data to enter into your software like once a year or help give it to your tax professional you can organize it this way and just give it to your tax professional and whatever makes sense or you might do adjusting entries at the end of the year to kind of get an idea or do an adjusting entry kind of monthly so let's take this data and do our month our monthly kind of adjusting entry so if I look at this let's filter it this way by just looking at at may and then I'm going to say that I want to look at the meeting the meeting with let's say we just want to look at the business stuff so I'm going to say just the business stuff and so that comes out to our 35.76 and if I multiply that times the rate that's how they're getting this 23.42 for the rate now hold on I wanted this for June the second month let's do this for June so there we have it so now we've got uh and not may what may is still there just June okay so there we have it so so it's just a a whopping $11 that we're going to say on the mileage method so let's do our our journal entry thing we did before but now for the second month so if I go to the income statement and I look at this just for for 060123 and I run it here so there's June all we have are these items in that month I'm going to say let's see this on a classes breakout now we don't have any classes yet and I'm going to do this breakout basically removing these two amounts or just this one because that's not deducted under the mileage method and then I'll add the deduction that we calculated over here for that month which is just the $11 I know they don't tie out because we just randomly put in dollar amounts here but they would be closer in practice you would think because the mileage method the iris is trying to attempt it to be somewhat approximating the actual so if I go back on over I'm going to say there's $200 that I'm going to make a journal entry for removing it for taxes and then I'll put in the amount we we can deduct for this month so I'm going to go to the tab the left plus button and I'm going to make our journal entry again like we did in the prior presentation but this time for the end of June this is going to be a monthly adjusting entry which you might do in practice on a yearly basis if you just want to do it once a year but just to get the idea we're going to say that this is going to be going to auto expense and this is going to be for 200 and then the other side is also going to go to auto uh wait not just auto expense there's an auto tax adjustment the auto tax adjustment that we set up last time which is a new account that's just going to have our adjustment sub-account of the auto expense so what I would like it to do then is reduce the the the un the one with no class and then I'm going to make the this one then be our tax adjustment so there's our tax adjustment so this will just be a column by column adjustment won't have any net impact on this account because the two will total out and back to zero so let's save and close it if I go back on over here just to check it out boom and so now we have our this it doesn't have any net effect on the total if we took the classes off and now this not specified area has been adjusted to account for decreasing the amount that we're not going to be able to deduct on an actual method keeping the 150 we can deduct and this is going to be kind of a jewellery so we can see the detail of what we're doing and then I'm going to put on the books the amount that we can deduct just for the second month which we're saying are this 11 11 01 that's what we're calculating noting that I'm I had to kind of export it out here to see it because if I look at this report it's only kind of giving us the summary data for uh the entire year here right we could have a custom date let's do the custom date and this is for June uh 1st to June 30th and so there's yeah it gives us a custom date so we can do it that way as well to kind of double check our numbers we could sort of that way but it still doesn't give us all the personal stuff but there it is there's the 11 01 and so we're gonna say all right let's enter journal entry for that one that's what we get to deduct and so we're gonna say all right this is going to be a auto tax auto tax adjustment for 11 01 and that's going to go into the unclassified and the other is going to be auto tax adjustment and this is going to be 11 01 I should put a description adj adjusting entry tax or auto let's say for second month and there we have it and then this is going to increase the this is going to increase the unclassified so this needs the class this is the tax adjustment so there it is once again having no impact on the net income so it shouldn't be too intrusive to the bookkeeping side of things so if I go back on over run the report again so so now we've removed the 200 and we and we put on the books uh the 11 so the net adjustment then was was the one eight eight so if I go into this account then for example we have our adjusting entries are two adjusting entries that net out back down to zero and what is included and this month is then the parking 150 plus this 11 01 plus 11 01 which is the 161 01 so pretty neat because then again it kind of gives us this this adjusting this tax adjusting entry worksheet just within uh quickbooks which is and it's not very intrusive because if I add this other account that just goes in and back out again now we could do the same thing if we had use this mileage stuff to figure our if we had some mileage deductions for uh the the personal for for say the medical and charity right I can sort by the by the by the charity and maybe I get a deduction for that but charitable deductions are much more complicated to kind of think about because they're on the schedule a you might not be using the schedule a the rate is different and then there's a floor that you have to do so so oftentimes it's more complicated to calculate the uh that well there might not be a floor with charity but there is with a medical expenses so these two are more different they're usually not as as big a benefit unless you had a lot of charity or medical expenses and they're not as straightforward because they're itemized deductions to calculate kind of as you're going because you kind of need tax software to actually figure what that's going to actually do but if you were doing your taxes and and you were wanted to have a nice worksheet to track what what you came up with for the tax adjustment for like charitable miles and or or or the medical miles that you calculated in the tax software you can use this similar method for entering into QuickBooks these kind of tax adjustments so that you can see the ending result for your net income on you know like a book basis and then the tax adjustments and then the tax basis giving you kind of a reconciliation so that if you have there's an audit or something you can tie out to what you did on the tax return so there's that and then of course we did this on a on a two-month basis so if I run this from let's go from 010123 to 123123 then now we have a a similar thing for the for the whole year the year to date we made two adjustments on a monthly basis which still kind of tie out here so they still net out to zero so if we did this for multiple months on a monthly basis or in multiple years if we did this in the following year this whole thing's just going to roll into equity so it doesn't really bother the bookkeeping much here because then it's going to wash out to equity and and if they're not using classes for anything else it might not be an issue for anything and then and you can see here that if I looked at the totals we can say this is going to be four taxes for the two months it this ties out to the parking which is deductible even if we do the plus we had then if I do this for the whole year plus the 34.43 which is at the yeah the 55443 and this 3443 should tie out over here if I was to say I want to just see all of this and then I want to see all of this and then what's where's my filters get out of here filters and then I want to see this just for the business boom business 34.43 and that ties out to the 34.43 so just a couple some different ways that we could possibly utilize this mileage tracking tool