Payment tracking and excel sheets
Warning: For Full Access Please Log In or Create an Account (Free)
Your portable hard drive is probably a USB connection. It may or may not have a CD with it for installation. For the most part all these require with Windows XP and above is to just plug the thing into a USB drive on your computer and give the machine a moment to "find" the extra drive. Then from the file tree you reach by right clicking on the "Start" button you can scoot down the list to find the drive. It is likely it will be G: or F: or something like that.
What I like to do is left click on the drive letter to 'open' it and go to "File" (in the top menu bar), "New" and "Folder" to create a new folder on the drive. Then I go to that new file and either while it is highlighted give it a name or right click on it and go to "Rename" and name it something esoteric like "Backup 2008 August" [This keeps all my backups together by alphabet in future file trees and all the 2008 backups will be kept together so I will never have to hunt through more than 12 files to find August.]
Once that is done, I go to "My Documents" (because I store all data and photos etc. in there) and right click on it. I select "Copy" and give the machine a moment to collect itself. I then go down to that "Backup 2008 August" file (I may have to open the drive letter to get to that folder again), right click and select "Paste". This will create a "My Documents" file in the "Backup 2008 August" file and paste into it all files and folders that were in the "My Documents" on my hard drive.
If, when you initially plug in the external drive you get a warning message that "New Hardware" was found and you can't find the drive in your file tree, you may need to unplug the drive, plug it in again and install the drive with the provided software. I have found that I have not needed to do this with USB 1 or USB 2 drives since I got away from Windows 98.
What I like to do is left click on the drive letter to 'open' it and go to "File" (in the top menu bar), "New" and "Folder" to create a new folder on the drive. Then I go to that new file and either while it is highlighted give it a name or right click on it and go to "Rename" and name it something esoteric like "Backup 2008 August" [This keeps all my backups together by alphabet in future file trees and all the 2008 backups will be kept together so I will never have to hunt through more than 12 files to find August.]
Once that is done, I go to "My Documents" (because I store all data and photos etc. in there) and right click on it. I select "Copy" and give the machine a moment to collect itself. I then go down to that "Backup 2008 August" file (I may have to open the drive letter to get to that folder again), right click and select "Paste". This will create a "My Documents" file in the "Backup 2008 August" file and paste into it all files and folders that were in the "My Documents" on my hard drive.
If, when you initially plug in the external drive you get a warning message that "New Hardware" was found and you can't find the drive in your file tree, you may need to unplug the drive, plug it in again and install the drive with the provided software. I have found that I have not needed to do this with USB 1 or USB 2 drives since I got away from Windows 98.
Looking for a good payment tracking system for excel.
I really would suggest incorporating the information directly into your shop sheet. When you list a job you have accepted, you probably want columns for:
1) Odometer reading out
2) Odometer reading on return
3) Mileage for this job (this is the second number above minus the first and can be made to be an automatic calculator)
4) Earliest performance date
5) Latest performance date
6) Store or client name
location address
7) MSP you are doing the shop for
8) A column for notes, such as hours for shop, phone# if call ahead, etc.
9) MSP's job number
10) Date you performed the job
11) Date you reported the job
12) Stated fee
13) Bonus promised
14) Reimbursement promised (change to actual reimbursement if you spend less than the maximum amount)
15) Unreimbursed expenses
16) Date paid
17) Amount paid
18) Payment pending
These are the basic pieces of information you need. You may find it useful to add additional columns for scheduler names or email addresses, whether an invoice is required and if/when you submitted it, etc. The core of your information, however, is above.
When you accept a job, immediately you can enter 4-9 above as well as 12-14. I usually copy and paste this information from the website as much as possible so that I KNOW I didn't get it wrong by making a typo.
When I do the job, part of my reporting is my interaction with my spreadsheet. I now can fill in 1 & 2 and if I have put in a formula for 3, that will be calculated for me. I fill in 10 and 11 (sometimes a report is submitted after midnight so the date will be different and some companies will allow reporting the following day anyway). I make any changes to 14 (maximum reimbursement for a dinner shop of $50 was on the sheet but I only spent $48.53 so I will change the $50 to $48.53). 15 is trickier. There are times when you have a flat fee shop but are required to make a purchase. That expense goes in 15. Sometimes you had a reimbursement of $20 but were unable to meet the purchase requirements for less than $23.48--$20 stays in reimbursed, $3.48 goes in 'unreimbursed expense'. Be careful with this column because it is only required expenses where the reimbursement did not cover the costs entirely.
Now my sheet will calculate 18 for me by a formula that adds together 12, 13 and 14 and subtracts out 17. I can make any other notes I need to make in appropriate extra columns to the right. With a new-to-me company I am likely to put in a date by which I expect to see payment. I may note whether payment will be by check, direct deposit or paypay so I don't have to go look it up again. I may note the scheduler or a phone number or a reminder to mail receipts etc.
With my own sheet I assign a job number of my own to every job. Mine run across years so at any time I know how many jobs I have performed. I simply have added a column before 1) to do this and it means I can sort my spreadsheet for any of a number of purposes and still return it to the sequence in which my jobs were performed.
There is not room here to teach you how to use Excel, but suffice it to say that I create a workbook where every month has its own page of the above information. Because my printer is not exclusively for my shopping activities, I charge per page for printing and claim no deduction for paper, ink or the printer itself. I keep track of postage to mail stuff in that is not directly reimbursed (if reimbursed, that amount gets added to the 'reimbursement' column). If I have to fax information in, that is annotated in at an average cost per page for the number of pages. I "freeze" the top panel of my sheet so my column headings show up no matter how far down the list I am for the month. I have running totals for the month in that frozen top panel for mileage, fees, bonuses, reimbursements etc and of course the outstanding payments. When I get paid, I highlight the line that is now paid so when my whole sheet has "turned blue" I know I am fully paid for the month and until that time I can easily spot shops that haven't been paid. When I request a shop I am likely to put the basic information for that shop (4-8 and 12-14) into my sheet and highlight it in yellow so that I know what I have requested and can remember to delete the request if accepted jobs make it no longer interesting (not going to be in that area during the time frame, etc.). If I get the job the yellow highlighting comes off and I finish filling in the data specifics in 8 and 9 as needed. If I cancel my request I just delete the line.
With my sheets I take the totals from the heading areas at the top of the page and feed them through to a recap sheet which shows me each month's activities, the outstanding amounts total and sums everything. I also have an expenses sheet for those things like equipment, cell phone bill and other deductible items that feeds over onto the recap sheet.
My sheets also become a whole tax information and life information thing as I keep track of non shopping appointments, birthdays, etc. because this IS my calendar as well. Medical copays and mileage for doctors' visits and pharmacy runs have a page of their own that breaks them down into the categories I will find on my tax return. A monthly running sheet of all bills to be paid is part of the overall packet and they get highlighted as I set them up to be paid and the highlight removed as they are paid from my checking account. If it is tax deductible, it is documented in my sheets as I go through the year because several times a month I am looking at my bank account on line so can see the inflows and outflows. I go over my credit cards carefully before setting them up to be paid. If it is a mystery shopping expense I don't worry about it because it is on my shop sheet. If it is a farm expense (feed, meds etc.) it gets copied to that page of my workbook while I am reviewing the cards. I keep an auto sheet more for curiosity than use as I take mileage, but the insurance, gas, repairs and maintenance are all copied to there. In other words, my sheets go well beyond the basics because it is a convenient way to keep track of everything. It is the first file opened every morning and the last one closed every night.
1) Odometer reading out
2) Odometer reading on return
3) Mileage for this job (this is the second number above minus the first and can be made to be an automatic calculator)
4) Earliest performance date
5) Latest performance date
6) Store or client name
location address
7) MSP you are doing the shop for
8) A column for notes, such as hours for shop, phone# if call ahead, etc.
9) MSP's job number
10) Date you performed the job
11) Date you reported the job
12) Stated fee
13) Bonus promised
14) Reimbursement promised (change to actual reimbursement if you spend less than the maximum amount)
15) Unreimbursed expenses
16) Date paid
17) Amount paid
18) Payment pending
These are the basic pieces of information you need. You may find it useful to add additional columns for scheduler names or email addresses, whether an invoice is required and if/when you submitted it, etc. The core of your information, however, is above.
When you accept a job, immediately you can enter 4-9 above as well as 12-14. I usually copy and paste this information from the website as much as possible so that I KNOW I didn't get it wrong by making a typo.
When I do the job, part of my reporting is my interaction with my spreadsheet. I now can fill in 1 & 2 and if I have put in a formula for 3, that will be calculated for me. I fill in 10 and 11 (sometimes a report is submitted after midnight so the date will be different and some companies will allow reporting the following day anyway). I make any changes to 14 (maximum reimbursement for a dinner shop of $50 was on the sheet but I only spent $48.53 so I will change the $50 to $48.53). 15 is trickier. There are times when you have a flat fee shop but are required to make a purchase. That expense goes in 15. Sometimes you had a reimbursement of $20 but were unable to meet the purchase requirements for less than $23.48--$20 stays in reimbursed, $3.48 goes in 'unreimbursed expense'. Be careful with this column because it is only required expenses where the reimbursement did not cover the costs entirely.
Now my sheet will calculate 18 for me by a formula that adds together 12, 13 and 14 and subtracts out 17. I can make any other notes I need to make in appropriate extra columns to the right. With a new-to-me company I am likely to put in a date by which I expect to see payment. I may note whether payment will be by check, direct deposit or paypay so I don't have to go look it up again. I may note the scheduler or a phone number or a reminder to mail receipts etc.
With my own sheet I assign a job number of my own to every job. Mine run across years so at any time I know how many jobs I have performed. I simply have added a column before 1) to do this and it means I can sort my spreadsheet for any of a number of purposes and still return it to the sequence in which my jobs were performed.
There is not room here to teach you how to use Excel, but suffice it to say that I create a workbook where every month has its own page of the above information. Because my printer is not exclusively for my shopping activities, I charge per page for printing and claim no deduction for paper, ink or the printer itself. I keep track of postage to mail stuff in that is not directly reimbursed (if reimbursed, that amount gets added to the 'reimbursement' column). If I have to fax information in, that is annotated in at an average cost per page for the number of pages. I "freeze" the top panel of my sheet so my column headings show up no matter how far down the list I am for the month. I have running totals for the month in that frozen top panel for mileage, fees, bonuses, reimbursements etc and of course the outstanding payments. When I get paid, I highlight the line that is now paid so when my whole sheet has "turned blue" I know I am fully paid for the month and until that time I can easily spot shops that haven't been paid. When I request a shop I am likely to put the basic information for that shop (4-8 and 12-14) into my sheet and highlight it in yellow so that I know what I have requested and can remember to delete the request if accepted jobs make it no longer interesting (not going to be in that area during the time frame, etc.). If I get the job the yellow highlighting comes off and I finish filling in the data specifics in 8 and 9 as needed. If I cancel my request I just delete the line.
With my sheets I take the totals from the heading areas at the top of the page and feed them through to a recap sheet which shows me each month's activities, the outstanding amounts total and sums everything. I also have an expenses sheet for those things like equipment, cell phone bill and other deductible items that feeds over onto the recap sheet.
My sheets also become a whole tax information and life information thing as I keep track of non shopping appointments, birthdays, etc. because this IS my calendar as well. Medical copays and mileage for doctors' visits and pharmacy runs have a page of their own that breaks them down into the categories I will find on my tax return. A monthly running sheet of all bills to be paid is part of the overall packet and they get highlighted as I set them up to be paid and the highlight removed as they are paid from my checking account. If it is tax deductible, it is documented in my sheets as I go through the year because several times a month I am looking at my bank account on line so can see the inflows and outflows. I go over my credit cards carefully before setting them up to be paid. If it is a mystery shopping expense I don't worry about it because it is on my shop sheet. If it is a farm expense (feed, meds etc.) it gets copied to that page of my workbook while I am reviewing the cards. I keep an auto sheet more for curiosity than use as I take mileage, but the insurance, gas, repairs and maintenance are all copied to there. In other words, my sheets go well beyond the basics because it is a convenient way to keep track of everything. It is the first file opened every morning and the last one closed every night.
Thanks Flash for the Payment sheet information.
Wow when you explain it it sounds so perfect and easy to do, but I dont have alot of Excel knowledge so I dont know how well I would do with this. If I have a spread sheet can I hand write it in every job and update it as payments come in, and after jobs what the mileage was...alot of that you can mapquest too...right. I need to get a better system, so I can keep track. You know alot about computers dont you. I took a class, but unless you use it, and know alot more than me about computers its so much harder. Thanks for all the advise though, even if I get alittle from what you explain,its more than I knew before.
I do a much simpler process. I keep all my shop printout and on each print out I put the milage. If I have more than one shop that day, I take my total milage and divid by the number of shops and that becomes my miles for each. I Keep 5 files. Four of the files are for the companies I do a lot of shops for and the fifth is for all the other shop companies I work with. I do about 40 shops a months with more than 1/2 from my 4 primary companies. I will make more than $600 from each of these, so I will get a 1099 from them, the rest I just add up at the end of the year.
I keep track of mine like Flash describes. I'm afraid I'm an Excel junkie, if there is such a thing and if I can get my text documents to save into a spreadsheet form, I would. :-) It really is so much easier to keep track of everything in one single file. Come tax time, everything's in one place.
Like everything that's new, it's intimidating in the beginning but it's very user-friendly once you get the hang of it.
Like everything that's new, it's intimidating in the beginning but it's very user-friendly once you get the hang of it.
I like "Excel junkie" 
I will fess up to being the same. It is a rush to being able to have the spreadsheets re-sort for anything you want to be at your fingertips. The closest Word can do is a "find". And at tax time it is so much easier to simply sum the columns or portions of a column rather than key the information again into an adding machine or computer. While I can use a ten-key with the best of them, there is no way I can beat a simple Excel summation for speed or accuracy.
I will fess up to being the same. It is a rush to being able to have the spreadsheets re-sort for anything you want to be at your fingertips. The closest Word can do is a "find". And at tax time it is so much easier to simply sum the columns or portions of a column rather than key the information again into an adding machine or computer. While I can use a ten-key with the best of them, there is no way I can beat a simple Excel summation for speed or accuracy.
Sorry, only registered users may post in this forum.