I'm trying to set my spreadsheets for individual companies so I can copy and paste into the main spreadsheet or vice versa.

I would like date, location, scheduler, fee, reimbursement, expenses, how I'm paid (DD, PP, check), mileage and date I'm supposed to be paid. I'm not sure if this would be the correct order or if I need to add other items.

I would like mileage to be a running total, so I think that should be rearranged. Expenses need to be categorized, because some of it's food, office, etc.

Thanks for any help you can give me.

I don't understand why you are doing separate spreadsheets for each company unless you shop for very few companies.

My spreadsheets I keep monthly and I can certainly copy and past them into a spreadsheet for the year and then sort by company for any totals by company I might want.

My current spreadsheet has: mileage start, mileage end and then a column that subtracts end from start to give miles; start date, due date (because many shops have a window and I can play within that window for routes); client name; client address; msp name; 'notes' (which may be scheduler name, time frame or things to remember such as photos); job number (I usually ignore this column or use it for additional notes); date completed, date reported (these sometimes are different); Fee, bonus, reimbursement, unreimbursed expense; Date paid, amount paid, pending payment (pending payment is the fee + bonus + reimbursement - amount paid); Invoice required?, Invoice date (as many companies have omitted the need for a separate invoice, I copy and paste the shop submission number in this column when that information shows up at the end of a Prophet shop, for example.)

Edited to add: In Excel I 'freeze panel' at the top of the sheet to have an accumulator for mileage, fee + bonus, reimbursements, unreimbursed expenses and pending payment. These are summations of about 100 cells of the column, which will pretty readily handle a month.

Edited 1 time(s). Last edit at 12/31/2016 07:53PM by Flash.

@Flash wrote:

I don't understand why you are doing separate spreadsheets for each company unless you shop for very few companies.

My spreadsheets I keep monthly and I can certainly copy and past them into a spreadsheet for the year and then sort by company for any totals by company I might want.

My current spreadsheet has: mileage start, mileage end and then a column that subtracts end from start to give miles; start date, due date (because many shops have a window and I can play within that window for routes); client name; client address; msp name; 'notes' (which may be scheduler name, time frame or things to remember such as photos); job number (I usually ignore this column or use it for additional notes); date completed, date reported (these sometimes are different); Fee, bonus, reimbursement, unreimbursed expense; Date paid, amount paid, pending payment (pending payment is the fee + bonus + reimbursement - amount paid); Invoice required?, Invoice date (as many companies have omitted the need for a separate invoice, I copy and paste the shop submission number in this column when that information shows up at the end of a Prophet shop, for example.)

Edited to add: In Excel I 'freeze panel' at the top of the sheet to have an accumulator for mileage, fee + bonus, reimbursements, unreimbursed expenses and pending payment. These are summations of about 100 cells of the column, which will pretty readily handle a month.

I'm doing it because I can no longer run rings around Excel. While I teach myself how to get the most of it, I want to record everything.. Hopefully, I'll be able to tweak the spreadsheets at the end of each IRS fiscal quarter.

I want to track time/mileage vs. fees for certain shops. There's a home improvement shop that normally gets a healthy bonus, but the report is a pain. I finally decided only to do it if it pays for a night in a hotel.

Thank you for how you set up your spreadsheets, it helps.

Now I'm trying to figure out if MileIQ is worth it. I swore I had an email from Microsoft that it could be imported to Excel. Downloading it to the phone isn't a big deal, but I don't want to pay for it.

MA, you just described my workbook!

The first spreadsheet is a summary of the MSCs, mileage, total shop payment and total amount paid. At the very bottom are the totals.

Each spreadsheet after is for the individual MSCs with full details of each shop. At the bottom of the sheets are the totals, which are linked to the Summary page.

I prefer the links over cutting and pasting because a) it saves a few steps, b) the updates are automatic, and c) if I change the order of the individual MSC worksheets, the summary sheet is not affected.

Excel is a powerful tool, but you have to know how to use it.

Flash, MA can have many spreadsheets in one workbook. That's how mine is set up.

The workbook is then saved as one file.

@Flash wrote:

I don't understand why you are doing separate spreadsheets for each company unless you shop for very few companies.

@HonnyBrown wrote:

MA, you just described my workbook!

The first spreadsheet is a summary of the MSCs, mileage, total shop payment and total amount paid. At the very bottom are the totals.

Each spreadsheet after is for the individual MSCs with full details of each shop. At the bottom of the sheets are the totals, which are linked to the Summary page.

I prefer the links over cutting and pasting because a) it saves a few steps, b) the updates are automatic, and c) if I change the order of the individual MSC worksheets, the summary sheet is not affected.

Excel is a powerful tool, but you have to know how to use it.

I used to know how to use it. But as the saying goes if you don't use it you lose it. I haven't really needed it until this past year and not enough patience to relearn the skills.

I loved Excel in school. I wasn't the perfect A student, but I knew how to manipulate Office to submit reports that professor's couldn't fathom with the professional aspect. It helped I had a printer that was awesome. I could incorporate Word, Excel and PowerPoint for a well presented printed report and an oral presentation. Never could figure out Access and not sure I ever want to conquer that program.

I'll set up things the way I need to evaluate every month and at the end of the quarter.

HoneyBrown, could you tell me how to set up links. You have my permission to PM.

Thank you

I fully understand making workbooks and indeed my 2017 'spreadsheet' has separate sheets for each month, a page to keep track of who shops whom, a sheet of MSC with whom I am registered including url to their login page and my login and password in case they don't automatically fill in, an expense sheet (other than specific shop expenses) that codes through to the recap sheet by category, as do totals from monthly sheets. The recap sheet can tell me at any moment what the current 'net income' of my business is, though I definitely want to check Schedule C with TurboTax each year before year end to make sure there are no surprises.

I've started an Excel spreadsheet of info on MSC's. I've started with the 100 + that I am registered with. Trying to identify also WHEN and HOW they pay and if I have to check their sites for jobs or if they post an email. I know that I am missing alot of info so I won't be ready for the 1st of the year, lol. Only some have a website link as I started that late in the game. There is a notes field for who they shop or for things I learn about them from other MS's. This is information that I want to be able to share with other MS's or newbies. (hiding some of the fields of course.)

I haven't gone to having all my reporting on separate spreadsheets, that I still do by hand. I still file by date of shop and list the MSC & shop name. I did start using these Shopping Assignment Logs, that I don't remember where I got and would like to manipulate in Excel...for monthly quick looks at what assignments I have. What I have scheduled and so I don't double book myself. I am still on an old school paper calendar for that!

I admire those of you that have gone so automated for your tracking! I feel like I am doubling my work but still getting into doing this as a regular thing! I need to add some of the fields that you all have mentioned that I had not even thought of so THANK YOU for sharing guys!

Edited 1 time(s). Last edit at 01/01/2017 01:28AM by LeslieKay111.

Which version are you using? I am using 2016. There was a major overhaul in 2010.

@MA Smith wrote:

@HonnyBrown wrote:

MA, you just described my workbook!

The first spreadsheet is a summary of the MSCs, mileage, total shop payment and total amount paid. At the very bottom are the totals.

Each spreadsheet after is for the individual MSCs with full details of each shop. At the bottom of the sheets are the totals, which are linked to the Summary page.

I prefer the links over cutting and pasting because a) it saves a few steps, b) the updates are automatic, and c) if I change the order of the individual MSC worksheets, the summary sheet is not affected.

Excel is a powerful tool, but you have to know how to use it.

I used to know how to use it. But as the saying goes if you don't use it you lose it. I haven't really needed it until this past year and not enough patience to relearn the skills.

I loved Excel in school. I wasn't the perfect A student, but I knew how to manipulate Office to submit reports that professor's couldn't fathom with the professional aspect. It helped I had a printer that was awesome. I could incorporate Word, Excel and PowerPoint for a well presented printed report and an oral presentation. Never could figure out Access and not sure I ever want to conquer that program.

I'll set up things the way I need to evaluate every month and at the end of the quarter.

HoneyBrown, could you tell me how to set up links. You have my permission to PM.

Thank you

Flash, don't get defensive. You're the one who assumed MA was using "separate spreadsheets for each company."

@Flash wrote:

I fully understand making workbooks and indeed my 2017 'spreadsheet' has separate sheets for each month, a page to keep track of who shops whom, a sheet of MSC with whom I am registered including url to their login page and my login and password in case they don't automatically fill in, an expense sheet (other than specific shop expenses) that codes through to the recap sheet by category, as do totals from monthly sheets. The recap sheet can tell me at any moment what the current 'net income' of my business is, though I definitely want to check Schedule C with TurboTax each year before year end to make sure there are no surprises.

@HonnyBrown wrote:

Flash, don't get defensive. You're the one who assumed MA was using "separate spreadsheets for each company."

I assumed nothing. Her statement was "I'm trying to set my spreadsheets for individual companies so I can copy and paste into the main spreadsheet or vice versa."

2016, but I quit paying attention in the late 90''s because I didn't need to use it anymore.

@Flash wrote:

@HonnyBrown wrote:

Flash, don't get defensive. You're the one who assumed MA was using "separate spreadsheets for each company."

I assumed nothing. Her statement was "I'm trying to set my spreadsheets for individual companies so I can copy and paste into the main spreadsheet or vice versa."

Flash assumed nothing. I said I wanted to set up for individual MSC's

It's going to be a brand new year in a few hours. Let's not start the petty fighting. It demeans this site.

I posted a request so I could get ideas from people I respect, even though I've never met them.

I've learned more from many members in a few months that I would never have learned in a few years hitting my head against the wall. This is the point of the site.

Happy New Year! And I apologize if I started a firestorm for 2017. It was not my intention.

There's never a firestorm on my end. My life is too valuable!

I'll post here in case others need to use the info.

To create multiple worksheets in a workbook:
Open Excel
Next to the tab at the bottom, click the PLUS sign
Double click on the tabs to give them unique names.

To create a running total:
Click FORMULAS
Place your cursor on the first cell of the column you want to tally
Drag down to a few cells below that last entry (this will allow you to add rows)
Click AUTO SUM at the very top

To create links:
Copy a cell
Go to the new location
Right click the new cell
Under PASTE OPTIONS, you will see a paper with a chain (on mine, it's to the far right)
Click that

Creating COMMENTS (just something I find useful)
Click the cell where you want to make a note
Click REVIEW at the very top
Click NEW COMMENT

The first worksheet in my workbook is a summary of mileage and money. Everything is linked from the MSC worksheets. When I input new information on the MSC sheets, the information is automatically updated via the links.

@HonnyBrown wrote:

There's never a firestorm on my end. My life is too valuable!

I'll post here in case others need to use the info.

To create multiple worksheets in a workbook:
Open Excel
Next to the tab at the bottom, click the PLUS sign
Double click on the tabs to give them unique names.

To create a running total:
Click FORMULAS
Place your cursor on the first cell of the column you want to tally
Drag down to a few cells below that last entry (this will allow you to add rows)
Click AUTO SUM at the very top

To create links:
Copy a cell
Go to the new location
Right click the new cell
Under PASTE OPTIONS, you will see a paper with a chain (on mine, it's to the far right)
Click that

Creating COMMENTS (just something I find useful)
Click the cell where you want to make a note
Click REVIEW at the very top
Click NEW COMMENT

The first worksheet in my workbook is a summary of mileage and money. Everything is linked from the MSC worksheets. When I input new information on the MSC sheets, the information is automatically updated via the links.

Thank you, I know I will find this information useful.

Happy 2017 everyone. Looking forward to a brand new year getting to "work with" and know you all. I use MileIQ last year but for something else but I think im going to keep it and use it to help with shops. Forgive me if this has been asked but do you have to report income on each individual MSC or just mystery shopping as a whole for this companies you don't earn a 1099 with? and yes I will head back to the newbie page this afternoon and catch up on my reading

There is no place on a Schedule C to report income MSC by MSC. Rather there is a section to report 1099s where you received more than $600 in income from individual MSCs and a line to report non-1099 income.

I started spreadsheets last year after some shoppers advice and it helped tremendously. I also kept a hard copy log book too. I only noticed one flup up last year in my handwriting and I know it was because it was a phone call while I was on the road offering a gig. There's something more satisfying to me about having a piece of paper to travel with.

Anytime I'v done a schedule c I have always had a 1099 but i have some income that i won't get a 1099 for so i wasn't sure. Thanks much!!

@clinen11 wrote:

There's something more satisfying to me about having a piece of paper to travel with.

There is absolutely nothing wrong with paper records as long as there is one central location for them that can be preserved for many years. IRS wants businesses to preserve their 'record of initial entry' forever. Stuff that is collateral to the Schedule C needs to be held about 5 years after filing, but your accounting or 'record of initial entry' needs to be kept forever. That is where well backed up spreadsheets can be a boon. Specific accounting software such as QuickBooks or Quicken needs to be repurchased over time as you purchase replacement computers with updated operating systems so that you still have access to your records. A spreadsheet kept in .xls format can be opened by OpenOffice (free) or Excel or most any other spreadsheet software as .xls format is an international standard (as is .doc for word processing).

MA have you played around with your spreadsheet yet? Do you have any questions?

I hand write my "spreadsheets" and then at the end of the year I put them all in excel format online. Since I do not shop full time it is not too time consuming. I am wondering, based on what you said below about saving the original forever, if it would work to scan my original paper copy into the computer to save it or would they want the paper version? I cannot imagine they will ask me 10 years from now to produce my original from 2015 but if they do I would then have the scan which would not have fallen apart from age.

@Flash wrote:

@clinen11 wrote:

There's something more satisfying to me about having a piece of paper to travel with.

There is absolutely nothing wrong with paper records as long as there is one central location for them that can be preserved for many years. IRS wants businesses to preserve their 'record of initial entry' forever. Stuff that is collateral to the Schedule C needs to be held about 5 years after filing, but your accounting or 'record of initial entry' needs to be kept forever. That is where well backed up spreadsheets can be a boon. Specific accounting software such as QuickBooks or Quicken needs to be repurchased over time as you purchase replacement computers with updated operating systems so that you still have access to your records. A spreadsheet kept in .xls format can be opened by OpenOffice (free) or Excel or most any other spreadsheet software as .xls format is an international standard (as is .doc for word processing).

I save and backup my excel spreadsheet each year including once in my computer file of all documents supporting my tax return. I also print out a paper copy to file with my paper copy of my return in my files. Should I ever be audited, I can put all documents that supported my return on a fresh thumb drive in minutes. Provided I am being audited for a year within the past 5 I can provide the original paper copies of all of those documents. With just a little more digging I can provide the vast majority of receipts in pdf format for the past decade and since I try to pay shops by credit card, I can provide credit card statements that would support the vast majority of expenses.

@HonnyBrown wrote:

MA have you played around with your spreadsheet yet? Do you have any questions?

I've set up what I thought would work on one screen, without needing to move to the other columns. I was filling it in tonight and thought I should add a column for total day mileage. My mileage was almost 400 miles today (no I didn't make big bucks, buy produce or seafood)

I think I'm going to transfer money from my Amazon account to my brother so he can order a couple of dummy books for me. I'll admit I'd be better off auditing courses than reading a book, but this will be the most affordable option.

i know we will have a meeting with the family CPA around the middle of February. She may be able to tell me how she'd like to see the spreadsheets from her clients.

I'm OK with what I've set up so far. The basics are listed and I can always edit down the line.

Thank you for asking. I really appreciate it.

MaryAnn

Even easier is the free tutorials on the internet such as [www.youtube.com]

@Flash wrote:

Even easier is the free tutorials on the internet such as [www.youtube.com]

I'm better banging my head against a wall than dealing with videos. I'm the student, who said yes I have a question and explain it to me like I'm a 3 yr old. I was a pain. Great professors loved it, because I was a good student. They realized if I had a question, the majority of the room didn't have a clue. Other professors hated it, because it meant they needed to prepare for class.

My Dad, who taught; always said this...any student who has the guts to ask a question; means at least another 10 don't get it. And it was his job to change up how he taught so students would understand.

So, yes; I'm the chick who asks questions and everyone groans because they ain't getting out of class early.

I prepare my workbooks differently. There are separate tabs/sheets for each month, rather than MSC. I'm a calendar person for organization, and this works for me. At the end of the month I sort by MSC to keep track of payments better. Annually, all data gets copied onto another sheet and sorted and totalled by MSC. My columns are a mix of text, numbers and calculations.

My thought on spreadsheets is that you need to know what you want and what works for you, then set them up that way. They can be easy to use for basic mathematical tasks. Databases allow for more flexible text searches and fast reporting. Back in the day, I could integrate spreadsheets and databases easily. I've been thinking about doing that again, but I don't think my shop volume would justify the time investment. But it sure would be fun! #pcsoftwarenerd

Sounds as though your spreadsheet workbook is set up very similarly to mine, @LGRM. And yes they evolve over time as there are things you decide you want to keep track of and things that become less important. As the workbook is put together for the upcoming year I make my changes so that for the year the data captured and manipulated will remain the same for 12 months. So back in the day when you mailed in receipts and were not reimbursed for the mailing, my sheets had a postage column to keep track of that expense.

IRS wants businesses to preserve their 'record of initial entry' forever. @Flash - what does this mean? Does it mean the IRS wants to be able to see records of our very first shop, forever? Or something entirely different?

It is standard for any business to keep their records since inception. I have had several different Schedule C businesses in my life. Early ones (pre-PC) had paper records in the form of an accounting columnar pad which showed dates, client, receipts and expenses. When I closed down those businesses I ditched the records five years later since there had been no IRS inquiry, but the records went back to day 1 of that business.

If you treated your shopping as a hobby, claiming income as 'other income' on your 1040 without the deductions you get by being a Schedule C business, you will only need documentation of income as an individual. But once you decide to take the deductions, you need to be a business 'with the intent of making a profit' and therefore need records.