Discover the Ultimate Car Receipt Template for Enterprises

Car receipt template for enterprises

how to track your business car mileage using a simple spreadsheet do you need to keep on top of your business mileage can you claim your business mileage back from your employer if so then our Simple Car mileage tracker spreadsheet might be just the tool you need follow my step-by-step guide on how to create an easy to use business car mileage spreadsheet and if you want a free copy of this template then as usual we can provide you with this just follow the instructions as shown in the description section below the various spreadsheet functions and commands that I've used include drop down boxes to select data I employ a simple if statement and use conditional formatting the car mileage template is easy to use and can be adapted and expanded to suit your individual requirements now if you are new to our Channel then please do take the time to subscribe and to receive notifications of our upcoming videos and please feel free to ask questions or to leave a comment in the section below I do hope that you enjoy watching okay let's start the design of our car mileage log spreadsheet by opening up a new workbook and then copy in the headings as shown on screen starting in cell C4 and ending in cell j4 you can pause the video whilst you complete this now we'll populate the first row of the mileage data in cell C5 enter in the date the 1st of January 2023 our starting odometer reading is thirty six thousand five hundred and twenty one miles so enter this value into cell D5 in cell E5 we want to differentiate between business mileage and personal mileage so let's enter in a drop down box selector to do this with cell E5 active pick up the data validation tool from the data ribbon and in the allow field select list and in the source field type in business comma personal with no spaces and then click ok to save and close the drop down box should appear to the right of cell E5 please select business our reference in cell F5 is your narrative for this journey in cell G5 we need to record the mileage for this trip I have entered 256. now H5 is the new odometer reading which should be the starting odometer reading in cell D5 plus the value for this trip in cell G5 so the formula in cell H5 is simply equals plus D5 plus G5 if you are able to claim a mileage allowance for business trips then you can enter in your mileage allowance rate in cell i5 however as we are recording both business and personal mileage we only want the rate to appear if the value in cell E5 is equal to business so let's create a simple if statement to achieve this select cell i5 and enter in the formula equals if Open brackets E5 equals speech mark business speech mark comma 0.75 comma naught and close the brackets this formula says if the value in E5 is business then enter in our mileage rate of 0.75 otherwise simply enter in a zero as this particular line of data is for business then I5 populates correctly with 0.75 our final cell on this row is the value of our claim for this strip this is simply the miles in cell G5 multiplied by the rate in i5 so the formula in cell J5 becomes equals plus G5 multiplied by I5 that's great we have now completed our first line of data in the next section we'll replicate the formulas in row five to fully automate our car mileage log table enter in a new date for our second line of data so cell C6 becomes the 2nd of January 2023 the starting odometer reading for this line is the same as the closing odometer reading for the previous line so in cell D6 enter in the formula equals plus H5 the value in cell D6 now becomes thirty six thousand seven hundred and seventy seven navigate to cell E5 in the row above and copy and drag this formula all the way down to E18 this copies down the drop down box selector such that it now appears on each row highlight the range E6 to E18 and press the delete key the values disappear but the drop down box selector remains now with cell E6 active so business from the drop down box place your trip narrative in cell F6 and the mileage for this trip of 184 in cell G6 the values for cells H6 I6 and j6 use the same formulas as in the cells above we can easily copy these formulas down to the end of our mileage log table so high note the range H5 through 2 J5 and copy and drag the contents down to row 18. hopefully cells H6 through 2j6 populate correctly find in this section we need to copy down the formula in cell D6 to the end of the table so with cell D6 active copy and drag this formula down to row 18. in the next section we will format our table we'll add a total for our mileage claim entering a title and then apply some conditional formatting to our table in cell C2 enter in your title and format this as shown on screen from the page layout ribbon uncheck the viewgridlines Box now home at the range C4 through to j18 and from the borders tool on the home ribbon select all borders in the range G5 through 2G 18 enter in a background or fill color of light gray select the ranges D5 through 2d18 and whilst holding down the control key select also the ranges G5 through to G18 and H5 through to h18 now right click your mouse to format these cells to zero decimals and have the 1000 comma separator checked right the range I5 through to J 18. once again right click your mouse and set the decimals to two different places and the 1000 comma separator checked make bold the range J5 through to j18 and finally in cell j20 use the autosum command to calculate our total claim value enter the text claim in cell j20 and format both I20 and j20 as shown on screen now highlight the range I5 through 2J 18. let's get rid of the zeros to make our worksheet look a bit tidier so pick up the conditional formatting tool from the home ribbon select the Highlight cells rule and from there the equals to option entering a value of zero and from the width field select the last option in the drop down box which is custom format in the dialog box that opens select the font Tab and then the color option and set this to White click OK and OK again to save and close hopefully all of the zeros in this range merge into the white background and cannot be seen and that's it you now have completed your very own car mileage log copying the values that I've entered for rows 7 down to row 18. hopefully all the formulas work correctly you can easily expand this car mileage table to accommodate many more rows simply use the techniques as shown to achieve this I do hope that you found the contents of this video both useful and informative many thanks for watching we do hope that our spreadsheet presentation was of value to you and that there was lots of useful content if you would like a copy of this free template then all we ask is that you subscribe to our Channel and give this video a big thumbs up alternatively you can visit us on one of our channels at either Facebook Instagram or Twitter if you enjoyed watching this video then why not watch our how to create a bookkeeping spreadsheet in Excel or how to keep inventory using an Excel spreadsheet thank you once again for watching we do