Accounts Receivable Spreadsheet

Okie · 2024-06-12T05:11:59+00:00

I forgot where I read it and tried searching for it, but I remember someone mentioning how they go through an Accounts Receivable Spreadsheet. Does anyone go through all of their shops and reconcile back to their bank account / PayPal? That seems excessive to me, but just curious how others do it. From my experience, MFI and Ipsos no issues. Some of the new companies where I picked up 1-2 jobs recently, my mistake for not actively monitoring the first couple of shops. Those are the ones I should be tracking. Anyways, been doing more shopping the past couple of months and trying out shops with a bunch of new companies. Found some good and reliable ones I like working with. The ones where each job payment is higher but less frequent, I usually get a detailed description in PayPal detailing the job. Then, there have been the few where it's just a mess from an operations and administrative standpoint.

Accounts Receivable Spreadsheet

June 12, 2024 11:53AM

I use a spreadsheet, after the payment comes in, I just highlight the row in green. I don't necessarily record the date of payment, I figure I can pull that back up if I need to. Most of the shop I do are with companies I regularly shop for, so I don't run into too many issues. There is a shop I did for a company in April I'm awaiting payment on, not a company I shop for regularly.

Create an Account or Log In

Membership is free. Simply choose your username, type in your email address, and choose a password. You immediately get full access to the forum.

More About Excel & Spreadsheets

June 13, 2024 08:29AM

Many of us keep a spreadsheet of pertinent information of every job we do...date, place, type of shop, payment promised , payment received with date etc. You can make your own columns to suit what you think is important. But I am not sure what you mean by reconciling it to your bank acct. Different msc pay differently. Some of mine go directly into my checking acct, some go to paypal, some send me a paper check. Personally I do not have a separate bank acct just for shops.

June 13, 2024 02:03PM

I have an excel file like the others. I have a sheet for each month and i use a pivot table to summarize each msc by month. I can't figure out (really i haven’t has the time to figure out), how to use that pivot table to calculate my yearly but one day i will.

June 13, 2024 03:10PM

I reconciled payments when I started out. It was interesting to see how much I had received versus how much I was owed. Back then Maritz required invoicing, so by keeping track I did catch a few shops I had failed to invoice. But eventually it got to be too much, and I found the payments reliable, so I stopped keeping track of who has paid. I still track it in my mind and have a pretty good idea of who should pay me what, but I don't track individual shops.

June 14, 2024 12:16AM

Date , Target, Company, Reimbursement, Fee, Paid, Pay Date/Method, Outstanding, Note

Those are my columns. Target is just the store/restaurant/etc, and if there's a specific person, I'll put that in. I use the formulas to add up the columns for reimbursement, fee, paid, and outstanding so I can easily see what I've made, and what is still to come in. Reimbursement/Fee is what I should get back, and then paid is what I do get paid. Note is for if there's an issue with payment and I have to contact, I note the date/method of contact (ie Email sent on 2/8).

If I need to, I can sort the columns. So, for example, I can sort all the MSC companies alphabetically or see the last time I shopped a particular location or whatever. I also have an unnamed "blank" column next to date, where I put in if there's a specific time I need to shop during/after/etc, or if I'm doing a lot of shops in a day, so I know what time I need to do them (usually that doesn't matter too much since I don't do it full time).

June 14, 2024 12:36AM

I just have a notebook binder that i write all my jobs down on and then I start transferring the data to an excel spreadsheet sometime in summer. I just bring my notebook pages along if I am going on "vacation" and if I have downtime sometime during the day when I dont have any busy work at home to do I start to fill it in.
My columns are...Miles, Name of MSC, brief name of vendor, location, date of job, scheduled pay and max reimb and bonus if any , actual amt spent, date $$ rec'd and amt rec'.
These columns are switched in order on my actual excel spreadsheet and can be easily sorted by msc name if need be. On my excel sheet I have a column for Reimbursed amt, another column for fee, a column for flat fee and a misc column.
on my paper sheet I can easily see if there are any holes in the data. My last column is payment so if that column is blank for too long I look into payment. I use a lot of shortened words so my paper version for each job fits on one line of a lined notebook page.
I keep a running tab but after filling it all in I can sort and subtotal by msc or by month if I need that or lots of other columns as needed. I dont want to bother to turn on my laptop and find my spreadsheet to consult so i just keep my paper version handy on my desk and jot jobs down as i get them.

June 14, 2024 01:16PM

I keep a spreadsheet for each MSC includes date, client, location, fee, expense, reimbursement, payment, milage and comment. By the end of the year, I add a "Sum" on the bottom, print out the pages and give it to my CPA for tax filing purposes. Make both of our life easy that way.

June 14, 2024 01:22PM

@Okie wrote:

That's how I operate too with a paper notebook. But more as a calendar to keep track of all of my shops. For me, it's easier to jot down and revise. Before I'm ready to head out for the day, I'll enter the order of the jobs I need to do on my phone based on shop requirements like time period and putting a route together by location.

Paper notebook is good when you are scheduling. You can see what you have on hand already, and potentially your route. and try to sqeeze in more projects or different dates etc.

keeping a spreadsheet is a completely different platform in my opinion. One is pre-operation, one is post-operation.

June 14, 2024 04:22PM

@kisekinecro wrote:

Paper notebook is good when you are scheduling. You can see what you have on hand already, and potentially your route. and try to sqeeze in more projects or different dates etc.

keeping a spreadsheet is a completely different platform in my opinion. One is pre-operation, one is post-operation.

I can do this in excel. I just "add a row" above or below to slot it in where I need. And I highlight each date with a different color so I can very quickly see what's already being done on a particular day (I unhighlight once I've completed a shop). When I have more than one or two on a day, I actually move them around to the order I intend to do them in, and I can pull them right up on my phone.

Sorry, only registered users may post in this forum.

Click here to login

Accounts Receivable Spreadsheet

Create an Account or Log In

Official List of Mystery Shopping Companies

Mystery Shopper Magazine