How are your spreadsheets developed or organized?

My spreadsheet is for the current year. The first tab is a summary with a list of the MSCs I contract with and links for mileage, shopper fees, and the amount I was paid. Following spreadsheets are categorized by MSC and have all of the shop specifics. The totals are linked to the summary.

I'm looking for your input because I can always learn something new.

Mine is done by month. I don't care about keeping up with shops by individual companies. I want to know what my profit is each month, so that's how my spreadsheet is setup. I have 18 tabs:

Summary - Basically just that. My summary page has a list of months and columns for Total Fees, Reimbursements, Total Income, Required Purchases, Mileage Cost, Travel Costs, and Profit then a set of cash flow columns that is Payments, Costs, Total Cash Flow. Those are all just the totals from each monthly tab. At the bottom I have quarterly totals of taxable profit only so I'll know how much quarterly tax estimates I need to send.

Monthly - there is a tab for each month. At the top is a list of shops, one shop per line, with date of shop, company, client, fee, reimbursement, total income, required purchase, mileage, mileage deduction, actual mileage cost, value of goods, taxable profit, actual profit, expected pay date, and payment type.

Below the list of shops is a section for travel costs that aren't associated with an individual shop (hotels, etc). I subtract the total of those from the taxable and actual profits above to give my total profits for the month. All of this is automatically populated into the summary tab.

Trip mileage - this tab is merely a record for IRS purposes. On multiple day trips I record my start and ending mileage for my car. For local routes I just put the actual mileage on each shop.

Payments - This is where I keep up with payments. I have a list at the top of unpaid invoices sorted by expected date with a column for MSC, Client, Total Payment, Expected Date, and Payment Type. Below this I have a section for each month. As I receive a payment I cut the line from the unpaid section and paste it into the month I receive payment. The total for each month is what gets populated to the summary page in the cash flow section.

Meals - I have a tab for meals when out of town on overnight trips. This is another tab for IRS purposes only. I don't account for it personally because I have to eat anyway. The IRS lets you take a partial deduction for those so I drop the total from that tab into my tax program when doing my taxes.

Companies - my companies tab is simply a list of companies. Each name is hyperlinked to the login page. I have my user id and pw listed (kind of, not really, it's in a shorthand I understand though) along with their payment type and pay schedule. It is sorted by both reporting platform and how much I work for the company.

Expenses - This is another tab that I use for IRS purposes only but don't actually use anywhere else. I don't spend a ton on "other" stuff but sometimes I do, it goes here.

Schedule - This is not at all a schedule. But it kind of is. It's just a list of the 52 weeks of the year and blocked out by what I expect to be doing in that week. I do a lot of recurring work so I fill those in for the year so I can see where I can add work.

It's a pretty complex spreadsheet honestly, but it works for me.

I keep three sheet with detailed data. One for shops that I have yet to complete, one for shops that I have completed but which I haven't yet been paid, and one tab for shops which I have received payment.

When I complete a shop, I simply cut and past the line to the not-paid sheet; likewise, when I receive payment I cut and past to the paid sheet and enter the date paid and payment means. This allows me to most easily see what work I need to complete and which MSCs deserve to be put on credit hold.

The columns are identical on each sheet. Each sheet has the same columns with totals way down at the bottom which in turn link to a summary sheet. I have separate records for expenses, contacts, & etc.

bgriff, my spreadsheet is complex also. It took me most of last year to build, and I started using it this year. It works!

Your Schedule tab, is that like a calendar? Color coded weeks? I haven't figured out how to add a calendar to Excel without using macros, so I left it alone.

Thanks Rousseau!

My main sheet is a list of companies, payment terms, monthly columns for the total due from each with the totals at the bottom and expenses deducted. My monthly tabs are set up like a calendar with shop name, location, MSC and fee. Two lines at the bottom of each week are for daily income and mileage. Income and mileage are also automatically totaled for each week and then for the month. I also have a section for my income goal each week. The actual income feeds into that to give me my over/under for the week and month.

Instead of moving things, I color code everything. On the company sheet it is either red for booked income or pink for pending, black after they pay. On the calendars I use the same colors and add green for shops completed but not paid yet. Why I did that eight years ago is beyond me, but now it is too ingrained to change



Edited 1 time(s). Last edit at 05/01/2016 12:18AM by LisaSTL.

No it's just a simple list of weeks and a blank cell for notes. So it might say:

April 10-16 New Home Video Route Los Angeles
April 17-23 Rhode Island Audit Route
April 24-30 Florida Retail Video Route

I don't use it for route planning at all (I have a separate template for that). I mostly use it so I don't get screwed up and take too much work (which happens). I do a lot of recurring work each quarter. I have about 6 weeks of each quarter I need to block out. So I block my expected weeks out for the whole year. Then when someone calls me up and asks me to do that two week video route in May I can very quickly look and see that I've done 4 of my 6 weeks already and while I have 1 of the other weeks in the slot the 2 week route needs to go in I can easily cut and paste it into one of the other empty weeks. If I don't have any empty weeks then I know I can't take it.

My spreadsheet has evolved every single year that I've been doing this. I'm sure there are things I will add next year. Perhaps booked but not yet done work. That would really boggle my mind though.

Thanks Lisa!

I like the idea of having weekly goals on my spreadsheet. Right now, they are on my calendar, which is a separate software.

You just gave me an idea for a new tab!

bgriff, how far in advance do you plan your shops?

I do my planning for next week this week. I had a shop on a job board that I accepted at the beginning of the month, and I nearly had a meltdown when I got the reminder email on Thursday..

I am a novice at Excel and created mine without ever getting to see any of the great ones others have provided over the years. While mine is not ideal, it became such a habit it works for me. I like the "calendar" view because it gives me an overall on how my month's shaping up. So same result as bgriffin, just a different method. I do have some duplicate entry because each shop is also entered on my Outlook calendar. I can add notes or even paste whole sections of guidelines then it syncs across all my devices. I can open the calendar on my phone to copy and paste the address to one of my GPS apps.

Hi HonnyBrown,

I switched over to Peachtree Professional this year. In 2014 and 2015 I used Excel 2013. I had 4 core worksheets. The first one was a log of all jobs I did. Each tab was for each month. I listed date, MSC, location/phone, amount paid, mileage/purchase reimbursement, type of shop, requirement of shop (what I did). The second one was of c ash receipts. This is all the money I was paid, I listed date, amount, type of payment and where I put the payment. The last one was an individual log of each job with the payment applied. I could easily see who owed me money from when. The last one was a log of expenses. You need to know what you spend to know what you get paid and your profit. This even includes non tax deductible items. Just remember not to include it on your taxes.

In a small sole proprietorship you generally want to record your work to know what you do and how much you get paid. It also justifies your existence to the IRS and others. You are obligated to record all payments you receive. It would be smart to know what you spend to make the money or do the jobs. Knowing who owes you what lets you know who is paying you. At a minimum you need a job log, cash receipts log, expenses log and MSC job & payment log.

I keep my schedule in a different format to cut down on recordkeeping for me. I only made a list of who I am registered with. I have bookmarks for all the companies. I do not bother with payment times as I have them memorized. For the companies I do little work for I just go on their website. I only keep accounting records for the core I told you above. The other administrative items are in my email or bookmarks. I also do not print records unless they are yearly summaries. I just keep 3 copies. One copy is on my computer, one in the "cloud" and one on a USB drive updated monthly or so.

My spreadsheets are very basic. I intermittently analyze information in different formats using customized worksheets. Your records are for financial accounting purposes for reporting purposes. Customizing the information is for managerial accounting. This helps you determine information which makes you better than our competitors. An example may be that you want to see if you cut out low paying jobs if you can be profitable or if you are more well paid with certain types of shops.

Good Luck!

@LisaSTL wrote:

I do have some duplicate entry because each shop is also entered on my Outlook calendar. I can add notes or even paste whole sections of guidelines then it syncs across all my devices. I can open the calendar on my phone to copy and paste the address to one of my GPS apps.

I don't use my Outlook calendar but I do have duplicate entry as well. I have over 200 shops assigned to me right now, there is no way for me to get a visual representation of that in a spreadsheet. I use Streets and Trips and use a new map each quarter. My recurring work is automatically populated and I manually add any additional shops. As a shop gets done it gets deleted from the map.

I've never been able to use Streets and Trips because they never had an app and I never had a dedicated GPS. I'm cheap so a multi-tasking phone is preferred over a uni-tasker GPS

@HonnyBrown wrote:

bgriff, how far in advance do you plan your shops?

I do my planning for next week this week. I had a shop on a job board that I accepted at the beginning of the month, and I nearly had a meltdown when I got the reminder email on Thursday..

My version of planning and your version of planning are probably different things, lol. I start before the beginning of each quarter by getting a rough estimate of when I want to do my recurring work. So I might say hrm, I'm gonna do all the Lisa's Widgets shops in STL the first week of April. And I'm going to do all the Griffin's Gags shops in Virginia the second week. On down the line. Then I might start looking for filler weeks and I might start fleshing out the recurring routes. I usually do those in about the same order but I might start looking for other shops when stuff for that month starts posting. I'm pretty stuck on repetitiveness. For instance there is a restaurant that has 8 or so locations in the geographical area of one of my routes. The shop times are very generous so I know I can always work one or two in and make it work. I know when those start posting so I grab them when it's time. As shops get added to the route they get dropped on my map. So when I leave for the trip I have a visual idea of what goes where, but the actual listing of addresses for a day usually gets done the night before.

@LisaSTL wrote:

I've never been able to use Streets and Trips because they never had an app and I never had a dedicated GPS. I'm cheap so a multi-tasking phone is preferred over a uni-tasker GPS

I don't use Streets and Trips for directions. I only use it to get a visual on where everything is. I do use it to route but I use my stand alone GPS (I used to use my phone, a GPS is so much better) for actual directions when I'm in the car.

You better stay away from Lisa's Widgets

My version is more like bgriffin's. For one of my recurring projects I'm given a quota and list of locations to choose from at the beginning of the year. I then plug them in with other projects as the year progresses and add filler shops last. While I will occasionally look for filler shops, most of the time they come to me.

@LisaSTL wrote:

You better stay away from Lisa's Widgets

Gladly. Those Widgets are a pain in the ass!

I thought Streets and Trips was discontinued. I switched over to Google (cloud, notes, maps, calendar, etc) from Microsoft because of that. The only things Microsoft are Excel and Word.

Just because they don't make it anymore doesn't mean it doesn't work. Honda doesn't make my S2000 anymore but I still drive mine on sunny days.

Oh, yeah, our planning is night and popsicles. I wouldn't even know how to plan shops for next quarter.

Thank you Sandra. I also keep multiple copies of my spreadsheet. I use mine for more than taxes though.

Sure you can. If you find yourself doing things repetitively start planning for them. If you don't find yourself doing things repetitively you need to start. It's the only way to make money in this IMO.

I do have some repetitiveness in my shops. And a good relationship with some of the schedulers.

But how do you plan for the following quarter when the schedulers release the shops by week or month?

I have a tab for login's, contact info, data retention requirement, pay schedule, etc. I tie this information to automatically generate the pay dates, data retention dates, etc.

I have a tab detailing fee, expense, pay, etc.

I have a tab to track last time I shopped a location. This helps quickly remind me when I'm eligible to shop a location again (or an idea of when to inquire again).

I have a tab to track bonus amounts for specific projects to help me negotiate.

I have a tab to track specific narrative, which is useful for signing up for new companies.

I have a summary tab spitting out data needed for applications as well.

Depending on a project, I track narratives if it helps me save time to just word smith work I've already put in to certain areas of a report.

I have an “Earned” tab with each MSC in a column along with how they pay: direct deposit, Paypal, etc. There is a row for each week of the year. There are totals for each week, month, and MSC so I know what my earnings are in each of those categories.

My “Income” tab is the same layout as the “Earned” tab, but it is all formulas based on the MSC’s pay schedule.This tab doesn’t require any data entry.

For instance, if I do a shop for Sentry the first week in May, I enter $17 on the “Earned” tab and it automatically appears as $17 income for the last week in July on the “Income” tab since Sentry pays 2 months later. If I enter a shop for GfK the first week of May, it automatically appears on the “Income” tab for the third week of May since GfK pays every 2 weeks.. This way I can tell at a glance how each month is shaping up.

The “Income” tab serves as my summary because it shows weekly and monthly income. Plus it has running totals for each MSC and I can confirm my 1099 at the end of the year.

When I complete a shop, I highlight that cell green on the “Earned” tab; when I get paid, I highlight that cell green on the “Income” tab.

The monthly totals from the “Income” tab then feed into my monthly budgets in the same spreadsheet.

It’s the Ron Popeil of spreadsheets Set it, and forget it. The only thing I need to do is enter the amount of the shop, and everything else takes care of itself.

Tarantado, how do you track narratives?

Thank you Chris. How do you account for bonuses?

Mine is not complicated. I have a tab for each year. Each tab (or spreadsheet) is a list by date of all the shops/audits I have completed for the year so far, what the pay was and any reimbursement. Then, there are more columns where I put in the pay when it comes, what method (check, direct deposit, PayPal), and mileage. At the top of the page I have totals such as how much I earned, how much I've been paid so far, how much mileage for the year so far, etc. I can select another tab and see what my income was from a previous year's spreadsheet. I can search for a company to see if they have paid me yet, etc.

So, my Excel file is more of an accounting of income. The scheduling I do by Google calendar and it syncs to my phone and my tablet. All shops are coded a different color in there than personal appointments.

I also have paper files, and a paper calendar. These may seem redundant, but it helps me to see things on paper without having to turn on an electronic device.

@HonnyBrown wrote:

Thank you Chris. How do you account for bonuses?

I add the Bonus to the shop payment. So if it's a $20 shop with $5 bonus, I enter $25 into the "Earned" tab.