Keeping Track of Tips
Originally published, July 2017 - Updated August 5, 2019 and January 4, 2022
For a time, I was a 40 hour a week pizza driver. Now I am more like a 20 hour a week driver. When you rely on tips for boring things like food and rent, you need to keep track of them, or at least I do.
I developed this spreadsheet to keep track of my money. Along the way, it also became a tool to track good nights from bad nights and further analyze my income. It has also evovled as the industry has evolved.
I am providing this to help my fellow drivers.
Sample spreadsheets are available for download
On the spreadsheet, the shaded columns are for you to enter information, the white columns are calculations. You do not need to change the formulas, although you may need to exstend the colunms as needed. I have only included a single month. I would suggest making a page for each month and using the summary to calculate your end of year totals forclaiming your car on your taxes. (You can no longer claim a mileage exemption on your taxes.
Setting up the Spreadsheet
Open the spreadsheet and go to line 2. If your store uses "in the store" and "on the road" pay rates, enter them here. This version of the spreadsheet assumes you are paid cents per mile" mileage reimbursment based on your actual miles.
For each shift, enter the date in mm/dd/yy format. For the curent year, MS Excel defaults to the current year if you just use mm/dd. Either will work. Notice that the day of the week should make the correct day. This column was added so I could quickly look at "all Mondays" and decide if Mondays or Tuesdays would be better days off.
The next three columns should be fairly obvious. This will calculate your actual miles for the shift. Also, the monthly totals are going on the summary page. If you name the pages with the months written out and capitalized (February) you will end up with a the end of the year information for your taxes. While you are at it, add your opening mileage for January 1 to the summary page. You will be looking for it in December.
Mapinfo Miles and number of runs are from the store's computer. Our system has a driver's print out. I get a print out every shift. They serve valuble services. They provide the information for this page. These calculations tell me if I have had a good day or a bad day. When I say last Friday was the worst day I have had in five years, I mean $10.73 an hour is the lowest total hourly wage I have had in five years, and that sucks. The printouts are also your verification of your time. The timeclock was invented to keep employers honest. Computerized time clocks have made shaving hours way easier than ever before. Your store management may not know this but the computers keep exelent records of time changes. Your print outs are the proof that someone has been playing with the time records. Finally, the delivery records are proof of where you went if you are audited.
Mapinfo miles are the stores calculations. Runs are the number runs. Hours in and out are the newest changes to this workbook. These are the total hours inside the store at the inside rate and the hours on a run at the outside rate. It is a pain being paid two different rates, but the spreadsheet converts this down to a useful number. Sales are added to convert tips into a percentage of sales. IRS assumes a server in a sitdown restaruant will make 8% in tips, that is what they assume that server will pay taxes on. I average around 15%. Our store pays mileage on the actual miles driven. I calculate what the mileage paid is. There is some roundoff error in this number. There is also the lazyiness that comes with doing a calculation that few people check. Our store has a written policy on mileage rates, down to what gas station will be used to get the benchmark price. If my boss is reading this, we check the numbers. We also add miles to bring the rate paid back in line with written policy. If your store pays based on the calculated mileage, you will need to change the formula to reflect this.
Paycheck income is the two pay rates, times their respective hours, added together. Cash for the day is mileage plus tips.
These are calculations to give me a better handle on my day. They should be pretty straight forward. Runs per hour; tips per address (in dollars); miles per address; dollars per hour and dollars per hour minus gas; and finally tips as a percenage of sales. I have discovered something after looking at these numbers. The important one is runs per hour. 2.25 and up, good day. 2.0 and down, bad day. I am at a disadvantage. As a late/closing driver, I am doing dishes and mopping for part of my time. This hurts the runs per hour. For my store and its long runs, 3.5 an hour is pushing it. When I worked in a store with a higher address density, 3 runs an hour was easy, five was possible.
The last section is all on gasoline. I buy two or three tanks (13 gal. tank) a week for two or three nights a week delivering. I calculate MPG for every tankful. One thing I noticed. In 2011-12, I lost 80 lbs. My miles per gallon went up 2 mpg hauling less of me around each day. The "mileage covers miles?" column takes what I was paid in mileage and subtracts what it cost to work that night (miles/mpg*price per gallon). Before "cents per mile", about 1/3 of the time mileage covered the gas used that shift. Now it does 100% of the time. The diff. column represents this number.
In 2017 I drove a Saturn VUE. 22 MPG was a very good day. Today I drive a Prius. 45 MPG is really bad. The only time I have had under 40 MPG for a night was in 8" of snow with the heat and defrost cranked and 5 mph traffic. In December 2021 I had a few 100 mile nights. Gasoline was around $2.80 a gallon. I used $6.50 ish in gas that night. My mileage was $42.00.
There you have it. Want to use my form? Go ahead.