what does your excel spreadsheet look like for shops? what do you title each of your columns?

what things do you list? like date? client name? address? payment? reimbursement? mileage? etc?

i want to get everything perfect on paper for each day/week/month/year. what's the best format?

I have a page for each month and on it the headings are, company shopping for, date to shop, mileage, company to shop, reimbursement due. I use this as my daily to do list. I also fit in personal appointments so if a shop comes in and I will be in that area I can take it. When month ends I have just one tab titled payments. I copy and paste the months activities. To the right of amount to be paid I enter date paid, method paid and amount. I then have one more tab and this is called payments not received. Once a shop reaches 2 months it goes on this list. I now send e mails or call to find out where my payment is.
Hope this helps. Kind of anal but accountant for 40 years, can't help it. So at the end of each month I know how much I earned and the mileage for year end.
I have a few other pages that I use for apartments shopped so I don't duplicate and bank shops so I know when I was at which branch when.

Vince: There is no perfect way. Do you like red tie or blue tie? There are certain basic information you need to have, and you have pretty good idea of what they are.

Here is what I have done.

I have a page called summary (more on that later). Then I have a pages where I put 3 or 4 MS companies together (my summary page tells me the titles. You could assign a page for each one of them, but then it becomes harder to control number of pages. I find 13-14 pages is the optimum for me). Then for each MS company I have columns that have shops and addresses, date, mileage, fee, UN-reimbursed, reimbursed, expenses, date pmt received, a column for notes, and a column for color coding. Once I do a shop I color code that cell blue, when I receive payment that cell becomes green. Also, For each MSC based on my experience with them I assign a star rating and also write their payment schedule. Then at the top of the page I sum all the entries. I automatically enter total $, total miles, total reimbursed, and total UN-reimbursed values on my summary page. On my summary page I also have place where I enter how much money I made in a month (I do that manually). In addition, when a shop is assigned I consider that as income - not when I receive payment. Since the payment schedule varies from mSC to MSC, I find this bit easier. On the summary page I also make a note NOT to shop for certain MSCs.

My situation would be, if felt I had time to do it, is exactly how much time it would take each day. lol

What you need to consider is how much time it will cost you if you don't have a decent system to keep records. I created my own spreadsheet years ago and it include a monthly calendar which tracks my daily income and mileage along with another page listing my income by company for each month and links to a separate spreadsheet with my budget. Everything is color coordinated for pending shops, completed shops, pending payments, etc. My reimbursements are tracked through my Quicken program along with all payments and business expenses. Shop details are copied into my Outlook calendar where they sync with my phone. So, basically I enter a variety of information into 3 different locations. It sounds unwieldy, but has actually become quite easy and probably takes me 10 minutes a day on average.

Lisa That is very true. It may take a while to design the excel spreadsheet the way you like, but after that it is just few minutes of entry time. You also will find on your own what works for you. In my case, I was used to computer based calendars for a very long time - because I was on computers all the time. However, after retirement I found that the old fashioned day planner works best for me now.

Yeah, when it comes to organization you have to look past the time it takes to get there and see how much more time will be saved later

Mine is actually an MS Access table, but I have my own internal job number, shop date, shopping company, agency job ID, status, client name, client address, contractor payment, reimbursed expenses, unreimbursed expenses, payment status and miscellaneous comments.

wow that would make a very searchable data base !

anakin Wrote:
-------------------------------------------------------
> wow that would make a very searchable data base !


That's exactly why I do it that way. With a really simple query, I can get contractor pay by company, unpaid jobs, etc. And, it's no harder to manipulate than a spreadsheet.

Quick question for you guys: why record the mileage?

brokestudentguide Wrote:
-------------------------------------------------------
> Quick question for you guys: why record the
> mileage?

because you can deduct mileage from your taxes. a lawyer tells me that 50 cents a mile can be deducted for taxes, if i interpret him correctly. not sure if it's like that in all states. i will bring my records to him to see if they are done correctly, before submitting them. don't quote me on this. other would know better than me.

Hmmm that's very interesting. I didn't know that. I'll have to check and see how that would work ...

brokestudentguide Wrote:
-------------------------------------------------------
> Hmmm that's very interesting. I didn't know that.
> I'll have to check and see how that would work ...

you just can't deduct your gas receipts if you do it that way, but it may work out to more money at that rate. again, don't quote me, because i still have to verify my papers with my lawyer friend who suggested it to me. he is a tax lawyer.

The mileage deduction comes from the IRS and changes twice a year to reflect operating costs. If you aren't taking it, you are paying way more in taxes on your income than you need to.

My accountant has always had me deduct mileage rather than gas. It actually works out to more, but it is an either or kind of thing. Mileage is designed to include wear and tear. My spreadsheet includes monthly calendars and I track the mileage each day. The spreadsheet then calculates it for the month. It's always best to check with whoever does your taxes, but it certainly doesn't hurt to keep track of everything, mileage, gas and service.

vince Wrote:
-------------------------------------------------------
> brokestudentguide Wrote:
> --------------------------------------------------
> -----
> > Hmmm that's very interesting. I didn't know
> that.
> > I'll have to check and see how that would work
> ...
>
> you just can't deduct your gas receipts if you do
> it that way, but it may work out to more money at
> that rate. again, don't quote me, because i still
> have to verify my papers with my lawyer friend who
> suggested it to me. he is a tax lawyer.

I was taught by some very wise women on here that I should have that on my spreadsheet. All those miles add up quickly. Sometimes I do routes and drive 150+ miles and those are LOCAL routes. Can't imagine those people that do state to state routes.

Vince, I use the MS Bible and LOVE it. It's an Excel sheet that has all that set up for you and I believe it's still only $10 and Sherry will email it to you. You can go to www.MysteryShoppingBible.com to see screen shots of what it looks like, the tabs, and such. She uses formulas so some of the information transfers over to the other tabs.

Definitely track your mileage! And anything you buy for MSing. A portion of your computer is deductible and your cell phone, etc. Which reminds me I need to let my tax lady know that. I bought this laptop last year and forgot to add it to my expenses.

Someone on the forum recommended keeping a little date book in your car. Before he/she gets out of the car they write down the # of miles for the day; re-set the tripmeter back to zero and then are ready for their next shopping day.

It has enough space so I write down the streets I'll be visiting & I circle the # of shops. Then when I get home - FINALLY it's become a "habit" - I'll grab the book from the passenger side visor where it's stored, and write down the # of miles.

At the end of the month, I'll bring in the date book and then enter the miles for each day. I have a separate worksheet for mileage (in the same spreadsheet).

I do something similar but I print off cheat sheets for the shops and write the mileage for each shop on the corresponding cheat sheet. Then plug in the numbers in my MSB.

I've got a lazy way of tracking mileage. The MapQuest app for my phone has voice nav which saved me having to get a GPS. I know some people hate MapQuest, but I've been traveling all over the place with it for the last year and a half and it's only been wrong a few times Anyway, I map my route for each day's shops and that is the number that gets entered into my spreadsheet. During the day I may decide to go off route for personal business or for just something silly and fun on a road trip. Since I've already documented my actual work miles there is no need for further tracking. If my route gets changed drastically I just go back in to the saved map and make the adjustments which can them be used to update the spreadsheet.

Another nice option the original Mapquest has is the ability to calculate fuel costs. I have my car's city and highway mileage saved and with one click I have a good estimate.

I used to love MapQuest until it sent me to Main Street of a different town. I Google Maps everything now or if I forget to track my mileage, I pluck in the addresses there and use that to figure it out. I have GPS so if I need to know how to get there, I use that after I've checked out Google Maps.

I will double check the results some times and I do take a really good look at the route before heading out, but since they've added the app it seems to be way more up-to-date than before. I'm also blessed with a really good sense of direction Plus, I'm just a real cheapskate and it is all free With all the traveling I"ve done in the last couple of years it really has been invaluable.

okay, thanks everyone.

these are my columns. there are 9 columns. how are these? am i missing anything?

VISIT DATE
JOB ID #
COMPANY/CLIENT NAME
ADDRESS/PHONE
MILEAGE
FEE
EXPENSES
DATE PAID
NOTES

vince Wrote:
-------------------------------------------------------
> what does your excel spreadsheet look like for
> shops? what do you title each of your columns?
>
> what things do you list? like date? client name?
> address? payment? reimbursement? mileage?
> etc?
>
> i want to get everything perfect on paper for each
> day/week/month/year. what's the best format?


You covered it very logicly. It is important to know the date the shop was performed, the date you were paid, the amount you were paid. The name and address of the business evaluated, the MSC name and shop number. The agreement you understood as to fee and reimbursement in seperate colums as the reimbursments are NOT taxable. When you are eating at a restaurant,for example, your focus is not on having entertainment value. You are gathering and recording data. The items you were reimbursed for was a tool necessary to evaluate the food, service and environment of the restaurant. Often you are buying items the client wants you to buy when you would rather have something else. "Other" is for tolls and other expenses and remarks that would make a IRS auditor back off because you documented the event well.

Piled Hip Deep, PHD Wrote:
-------------------------------------------------------
> You covered it very logicly. It is important to
> know the date the shop was performed, the date you
> were paid, the amount you were paid. The name and
> address of the business evaluated, the MSC name
> and shop number. The agreement you understood as
> to fee and reimbursement in seperate colums as the
> reimbursments are NOT taxable. When you are eating
> at a restaurant,for example, your focus is not on
> having entertainment value. You are gathering and
> recording data. The items you were reimbursed for
> was a tool necessary to evaluate the food, service
> and environment of the restaurant. Often you are
> buying items the client wants you to buy when you
> would rather have something else. "Other" is for
> tolls and other expenses and remarks that would
> make a IRS auditor back off because you documented
> the event well.

okay, here are my final column categories:

VISIT DATE
JOB ID #
COMPANY/CLIENT NAME
ADDRESS/PHONE
MILEAGE
FEE
EXPENSES
DATE PAID
NOTES

Well Vince you have all the basic information that is needed. Consider having a summary spreadsheet that pulls the totals from individual sheets, and tabs with individual or a group of MSCs. Otherwise your spread sheet will become long and long and long.

I have individual tabs for the companies I work the most and lump 2-5 MSCs in one tab.

anakin Wrote:
-------------------------------------------------------
> Well Vince you have all the basic information that
> is needed. Consider having a summary spreadsheet
> that pulls the totals from individual sheets, and
> tabs with individual or a group of MSCs.
> Otherwise your spread sheet will become long and
> long and long.
>

i will have weekly, monthly and yearly summary pages.

> I have individual tabs for the companies I work
> the most and lump 2-5 MSCs in one tab.

that's a good idea. thanks.

Interesting question. My columns read Month, under which I put the date; time; company; assignment (shop number); location; summary; fee; notes; status; amount; OOP (out of pocket); total; paid; made; expenses; mileage; and for the first two or three months of each year a column titled prior year.
Prior year gets dollar amounts for shops done in the prior year. That's income for the current year. The total column is sum of fee and OOP. The made is the difference of total and paid. For status I use S(scheduled), C(completed), P(paid), X(failed, no pay). I color code shop lines for status S, C, and P/X so I notice what is coming up or is done. I keep rows in order by date (month column).
I can find just about anything searching this data (by MSP, date range, fee amount, job number, etc.
I have a sheet for totals of various columns that gives me the information I need for taxes each year. I get the total fees, OOP, paid, expenses, mileage, previous year, and amount made. It calculates the rate for mileage and I end up with 10 lines of summary data that I put into my tax forms.
After tax time I print out the whole workbook and file it away. For the little bit of time it takes me to enter jobs and then update them when shopped, it gives me peace of mind.

Glabow Wrote:
-------------------------------------------------------
> Interesting question. My columns read Month, under
> which I put the date; time; company; assignment
> (shop number); location; summary; fee; notes;
> status; amount; OOP (out of pocket); total; paid;
> made; expenses; mileage; and for the first two or
> three months of each year a column titled prior
> year.
> Prior year gets dollar amounts for shops done in
> the prior year. That's income for the current
> year. The total column is sum of fee and OOP. The
> made is the difference of total and paid. For
> status I use S(scheduled), C(completed), P(paid),
> X(failed, no pay). I color code shop lines for
> status S, C, and P/X so I notice what is coming up
> or is done. I keep rows in order by date (month
> column).
> I can find just about anything searching this data
> (by MSP, date range, fee amount, job number, etc.
> I have a sheet for totals of various columns that
> gives me the information I need for taxes each
> year. I get the total fees, OOP, paid, expenses,
> mileage, previous year, and amount made. It
> calculates the rate for mileage and I end up with
> 10 lines of summary data that I put into my tax
> forms.
> After tax time I print out the whole workbook and
> file it away. For the little bit of time it takes
> me to enter jobs and then update them when
> shopped, it gives me peace of mind.

okay, let's try this:

VISIT DATE: 120331 (two digit year/month/day) (TIME optional)
JOB ID #: 123456
COMPANY/CLIENT NAME: Franz Mystery Shopping/McDonalds
ADDRESS/PHONE: 123 Main Street, Chicago, Illinois 60606 (773-645-2200)
MILEAGE: 20
FEE: $10.00
OOP EXPENSES: $1.00
TOTAL FEE/OOP: $11.00
DATE PAID: 120502 (two digit year/month/day)
GUIDELINES/NOTES: Camera required/Manager looked suspicious

will this work? each year/month/week/day page will have a subtotal.

Edited 1 time(s). Last edit at 03/31/2012 12:31PM by vince.