@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.
@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.
@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 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."
@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."
@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.
@clinen11 wrote:
There's something more satisfying to me about having a piece of paper to travel with.
@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).
@HonnyBrown wrote:
MA have you played around with your spreadsheet yet? Do you have any questions?