QuickBooks Custom Transaction Detail Report Can Help Avoid Missed Mileage Reimbursement

QuickBooks can help jog your memory about business trips so you don't lose out. I'm not talking about the QuickBooks mileage feature. I'm talking about using QuickBooks to remember trips you may have neglected to record.
How many of your business trips involve a purchase? Did you drive to Staples to buy that paper? Or to the bank to make that deposit? Did you use your personal car to drive to that hotel for business or that auction where you bought that piece of equipment?
You can probably think of many more transactions that might relate to business mileage.

To create a Transaction Detail report that you can scan for possible missed mileage:

1. click Reports at the top of QuickBooks
2. select Custom Transaction Detail Report at the bottom
3. change the dates of the report in the date boxes
4. click the Filters tab at the top of the Custom Transaction Detail Report window
5. From the Filters box on the right, scroll down and select Transaction Type
6. choose Multiple Transaction Types from the Transaction Type drop-down box to the right

select the following from the list of transaction types in the Select Transaction Types window that
 
opens:

• check: garages or repair shops--did you pick up or drop off a vehicle or piece of equipment? accountant or attorney--did you have an appointment or drop off or pick up papers?
• deposit: did you make a trip to the bank?
• journal: accountant adjustments--did you pick up a financial statement?
• credit card: garage or repair shops--did you drop off and pick up a vehicle or piece of equipment? hotel rooms--did you take your personal car?
• credit card credit: did you drive somewhere to return an item?
• estimate: did you drive somewhere to look at a job and give an estimate?

1. close the Select Transaction Types window
2. from the Filters box, scroll down and select Detail Level
3. Select the radio button next to Summary Only on the right
4. close the window

Hint: Eliminate unwanted columns in the report by holding the right-click button down on your mouse while hovering over the diamond to the right of the column head and moving it left until the column disappears. I find this quicker and easier than scrolling the Column List in the Modify Report Window.
Sorting by name and then by transaction type works best for me.
To sort by name, select Name from the Sort By drop-down box at the top of the Report Menu on the right.
If you're a paper and pencil fan, you can print the report and write in the miles to the left or right of each transaction that relates to a business trip and use a calculator to total.
If you have Excel, totaling the mileage will be easier. If you aren't familiar with Excel these detailed instructions will give you a total in no time.

1. Click the Export button at the top of the Report Menu >
2. Export Report window opens
3. Select the radio button to the left of: a new Excel workbook
4. Click the Export button at the bottom of the Export Report Window
5. Save the spreadsheet
6. Select one of the blank columns on the left margin of the spreadsheet (or the right if you prefer) by clicking the column heading. The column should now be highlighted.
7. While the column is highlighted, right click anywhere in the column.
8. Choose Format Cells from the fly-out menu.
9. Choose Number from the Category box of the Format Cells window.
10. Change the number of decimals if desired and click OK.
11. Scroll down the spreadsheet looking for mileage reimbursable transactions, entering the number of miles for each in one of the blank columns on the left of the spreadsheet

There are several ways to total the column. Choose one that works best for you.

Method 1:

1. Click on the last cell at the bottom of the spreadsheet, under the last transaction
2. The cell might say something like "Total unclassified". Ignore.
3. While the cell is selected, select Insert from the top menu
4. Select Function from the drop-down menu
5. Select sum from the Function List box.
6. The Function Arguments window will open and you'll see something like B40:B80 in the Number1 Box.
7. Change the first number to 2 so that the box now reads B2:B80 where the last number is the last transaction row.

Method 2:

1. Click View from the top menu. Make sure Formula Bar is checked
2. The name of the selected cell (column and row) appears in the left (short) box of the Formula Bar.
3. The right (long) box should be blank. Type: =sum(B1:B80) Where:
4. B=column
5. 1 = first row
6. 80 = last row
7. Replace the formula above with your own numbers: =sum([column & first row]: [column & last row]) Last row is the row above the selected cell. If you enter the row of the selected cell as the last row, a circular reference error message appears.
8. Hit enter and if you followed instructions correctly, the column will be totaled.

Method 3:

1. Select View from the top menu
2. Highlight Toolbars and make sure Standard is checked.
3. Select the cell at the bottom of the column you want to total.
4. Left click the AutoSum symbol on the Standard toolbar (at the top Excel menu). Hint: the AutoSum symbol looks like the letter M resting on its left side.
5. Left click the cell directly above the selected cell and Excel will outline the cell in blue
6. Left click the top right corner of the blue border and drag the blue border all the way to the top of the column to the first cell you want to include in the total
7. Hit enter If you followed instructions, the cell will now contain the sum of the column

These steps might seem complicated if you've never used Excel but once you've used it, you'll probably find Method #3 the simplest and quickest.