I would like some help from some of the pros in this forum on some detailed info about how to keep track of the assignments (shops) that I have accepted and then completed and then awaiting payment for.

I have been doing all this by hand by setting up weekly log sheets (but the days are not consecutive which sucks) and then transferring the info to individual sheets that I have set up for each MSC and then checking them off when I get paid but it has become rather daunting.

I do about 200-300 shops per month.

Day-by-day? Or Week-by-week Or set up a spreadsheet for each MSC (whew) or ????

Thanks everyone for continuing to provide lots of useful info.

Brian

What I have found useful for the past decade or so is a spreadsheet kept by month. I create a 'workbook' with a tab for each month and just copy the headers onto each page. Since I schedule work on that page, update mileage and actual reimbursement expected, report dates and any confirmation or invoice information when I do the shop, it is my 'record of first entry' for all work. When I am paid, the sheet gets marked with date and amount paid. When everything is paid for the month I am 'done' with that page until tax time. As I got more sophisticated with Excel I added formulae to total sheet categories and then created a recap page that pulls all of those totals automatically into an overall snapshot of where my business is thus far in the tax year.

Member Flash made this spreadsheet:

[www.mysteryshopforum.com]

I'm glad I started entering data in it from shop #1.

I keep it right on my desktop and update it every shop, before I add the report and recipt to the stack.

These are the headings on my spreadsheet:

Payor, Shop, Date, fee, Gas bonus, reimburse, Total $, mileage

Example:

MSCname, McDonalds , 06/15/14, $5, $15, $8, $28 (this figure will change when I enter the actual reimbursement)


When I apply for the job I fill in those columns except mileage. The Total $ is a computed column, the sum of Fee, Gas bonus, and reimbursement and is the amount I expect on the check.

I list them in order of shop date. I subtotal all columns by month. i have the entire year on one worksheet.

When I apply, I put the word "applied" in the mileage column.

when I get the job, I delete the word applied.

I group everything by date, so all shops for a day's route are consecutive.

I leave a blank line between jobs done and jobs to do. After each day's jobs are done, I delete the blank line that separated that group from all the "done" jobs above it. Result is that all finished jobs for a month are in one block with no blank lines, and all upcoming jobs are in daily blocks with a blank line separating them.

At that time I will also enter the actual reimbursements so the Total field recalculates, and enter the mileage on the last line for that day if I ran a route. (I don't enter mileage by shop, just the total for the day, and I keep the mapping printouts showing where I went and the miles in case I have to prove where I went for a tax audit.)

When jobs are paid, I bold face the line. You can add columns for date paid, amount, and method (PP, DD, check, PQ). It makes it easy to scan for unpaid jobs from two months back so you can inquire or check their payment policy to see if they're actually overdue or not.

By totaling the Total Due and the Total Paid columns, you can easily subtract one from the other to see if you're still owed any money for that month's shops.


You can add columns for shop numbers or other information. Since I keep all my shop docs, I just keep the spreadsheet with enough information about the shop to be able to find the right docs if I need to. (McDonalds Prescott or McD Phoenix 19th Ave, or Verizon Payson North, or Verizon Payson Bullfrog) (In this small town we have at least four Verizon sellers)

My method enables me to see most of a month without scrolling, the entire year with some scrolling. At the end of the year, I copy the entire page to a new tab, then sort the new page on MSC name so I can easily compute by MSC to see who owes me a 1099 (if I want to know). I can do other computations such as earnings by MSC, number of shops by MSC, number of shops by Client (McDonalds, Five Guys), I can sort of the total column to find my highest-paying shops, whatever I want to know. I do not sort the original worksheet -- I keep that in date order with the monthly subtotals.

And my total fees received (the sum of the Paid column) and my total reimbursements and total mileage can be computed with a few clicks for the tax return.



Edited 1 time(s). Last edit at 08/23/2014 05:16PM by dspeakes.

These are the headings on my spreadsheet

JOB ID SITE ID DEBRIEF CODE TYPE DUE DATE DATE DONE
TOTAL COST TOTAL PAID REIMB AMT TAXABLE AMT
INVOICE DATE INV NBR /PAYPAL DATE PAID
NAME STREET ADDRESS CITY ST ZIP STATION PHONE
MILEAGE EXPENSES MYSTERY SHOP COMPANY

These are all across the top from column A thru V

Of course not all shops use all of the fields especially A,B,C,K and L. T and U are used to track the mileage on my routes and the incidental expenses like food, motels and computer supplies.

My spreadsheet is very simple. I also do a tab for each month and I have 'Date' 'Company' 'Assignment' 'Fee' 'Reimbursement' and 'Total' Then next to that I have 'opening mileage' 'closing mileage' 'total mileage'

At the bottom of each page I have assignments totaled by MSC so I know exactly how much each MSC owes me. When they pay, I take the amount off the spreadsheet. I also have a taxation page at the end of it which refers back to each month 'fee' total for tax purposes.

keep it simple is the best way.

I have a monthly sheet that has tabs for each
msc that I do 10 or more shops for in a month.
I also have an 'other' tab for msc's I do less
than 10 since they don't deserve their own page.

All numbers in these pages link to a final tab that
has the overall monthly totals from each msc.

I use the MS Bible, an Excel spreadsheet/program that was created by a MS'er for MS'ers. It was the perfect solution for me when I first started. I was using this before Flash created and posted her spreadsheet.

Wow...Lots of info here.

Is anyone besides Shop2LiveinFL using MS Bible?

It looks interesting and kinda/sorta ready to go and only $10.

Thanks to all for the excellent suggestions.

Brian

Is anyone using a more full-blown small business book-keeping system like QuickBooks, FreshBooks, etc?

One thing I do that I haven't seen mentioned is that I'm a little fanatical about cash flow. I want to know if I'm on track for my monthly revenue targets, and I want to know if any of my clients are failing to pay me in a timely way. I have a promised pay date field for each shop, and I use it to sum up my anticipated revenues/reimbursals.

I color-code my spreadsheet to show the status of the shop [applied, assigned, completed, paid]. If I have to chase down the payment, then it gets highlighted in bright red, and the MSC is also highlighted in bright red on my MSC list.