How should I design my Excel 2013 Invoicing for better efficiency? Suggestions or advice?
My little contracting business is finally getting busy enough, that I should do something about my invoicing. I'm spending too much time with invoicing and I was hoping an excel expert can advise me on how to use Excel more efficiently, possibly in conjunction with my cell phone, and save me a lot of learning curve and experimentation. I think the only real difference in my Excel 2013 RT versus regular Excel, is that I cannot use any VBA for Macro, which is pretty huge. If the only good way is with Macros, then I may upgrade, but the budget is tight. I have included an example invoice file attached. Customers like it for detail and record keeping of their home repairs. I don't yet use the invoice number field but stuck it in there for the future. I have about 13 regular customers with ongoing work, plus occasional odd jobs.
Current slow process.
While working, I log in and out with paper and pen, in my "Daytimer" calendar appointment book which is on site with me. Some jobs I take the time to log in and out even for the partial jobs. This is very useful data for future price estimates, i.e., if I paint one large room, sub-headings would be prep, first coat hand brush edges of wall or trim (cut-in), second coat cut-in, first coat roller, etc. This data sort of goes into the invoice adjusted by my analysis of the info in my Daytimer. "Sort of" because the invoice has my real log in data per day so the customer can reconcile my hours with what they know. Obviously, the per day hours include mixed partial jobs, as you can see in the sample. Then during billing, I either open an old invoice that already has the customer's name and address, then alter the other info, or if the other info to alter is too much, I open the template version and put in all fresh data for the customer. Then I sit with my Daytimer and manually enter the dates, times, and descriptions of the labor, into the invoice. Then I switch from my Daytimer and get my pile of receipts to review all my expenses and calculate what to invoice, and how much tax, under miscellaneous, which also gets a manually typed description. Then the spreadsheet gives me the totals. I make a PDF and email it. Some still get a paper print.
Goal process.
Will keep Daytimer ink records for safety and ease of use. I could duplicate records in a phone app or google sheet, but again, I would have to design the right google sheet. I could even see moving everything to the phone and eliminating the Surface RT, but prefer doing real computer work on the Surface. Probably will not use phone, but don't know what advice will come back about this. Would like to launch an icon that either lets me select a customer to invoice from a list, or choose "new", then lets me choose "new invoice" with date, invoice number, name and address filled in, or search for old invoice by various fields. I don't think I can automate this without VBA but someone out there knows how close I can get. I also wonder if I will choose a good path of setting up the database. Currently it is not a database. Currently each invoice is one xlsx file, with one invoice, in one worksheet. As I change over should I try to keep all customers in one xlsx file? Keep one customer in one spreadsheet? Keep one customer in an xlsx file, with one worksheet per invoice, to allow easier creation of new invoices? Would like to be able to analyze my data to improve cost estimating, and income and expenses, and be able to search and retotal by any field. I think what I want is out of my price range but I just thought I'd ask you folks what to do.
Thanks for listening.
Chris
InvoiceSample1ForEE.xlsx
InvoiceSample2ForEE.xlsx
Current slow process.
While working, I log in and out with paper and pen, in my "Daytimer" calendar appointment book which is on site with me. Some jobs I take the time to log in and out even for the partial jobs. This is very useful data for future price estimates, i.e., if I paint one large room, sub-headings would be prep, first coat hand brush edges of wall or trim (cut-in), second coat cut-in, first coat roller, etc. This data sort of goes into the invoice adjusted by my analysis of the info in my Daytimer. "Sort of" because the invoice has my real log in data per day so the customer can reconcile my hours with what they know. Obviously, the per day hours include mixed partial jobs, as you can see in the sample. Then during billing, I either open an old invoice that already has the customer's name and address, then alter the other info, or if the other info to alter is too much, I open the template version and put in all fresh data for the customer. Then I sit with my Daytimer and manually enter the dates, times, and descriptions of the labor, into the invoice. Then I switch from my Daytimer and get my pile of receipts to review all my expenses and calculate what to invoice, and how much tax, under miscellaneous, which also gets a manually typed description. Then the spreadsheet gives me the totals. I make a PDF and email it. Some still get a paper print.
Goal process.
Will keep Daytimer ink records for safety and ease of use. I could duplicate records in a phone app or google sheet, but again, I would have to design the right google sheet. I could even see moving everything to the phone and eliminating the Surface RT, but prefer doing real computer work on the Surface. Probably will not use phone, but don't know what advice will come back about this. Would like to launch an icon that either lets me select a customer to invoice from a list, or choose "new", then lets me choose "new invoice" with date, invoice number, name and address filled in, or search for old invoice by various fields. I don't think I can automate this without VBA but someone out there knows how close I can get. I also wonder if I will choose a good path of setting up the database. Currently it is not a database. Currently each invoice is one xlsx file, with one invoice, in one worksheet. As I change over should I try to keep all customers in one xlsx file? Keep one customer in one spreadsheet? Keep one customer in an xlsx file, with one worksheet per invoice, to allow easier creation of new invoices? Would like to be able to analyze my data to improve cost estimating, and income and expenses, and be able to search and retotal by any field. I think what I want is out of my price range but I just thought I'd ask you folks what to do.
Thanks for listening.
Chris
InvoiceSample1ForEE.xlsx
InvoiceSample2ForEE.xlsx
I agree with John. Don't use Excel. I started my business using Excel, too. It looks tempting because Excel can do some automated math, but Excel is NOT a good accounting tool.
The problem is that it's not just about recording the data. Your business will have a workflow to it that you must keep track of.
Your business workflow will probably go something like this (Sorry John I think i'm basically saying the same thing as you here)
1) Write up an estimate for your customer based on notes you took while talking / surveying / selling them on your product
2) Keep track of the work you've done, and eventually draw up an invoice that lines up with the estimate
3) Receive payment from the customer and mark the invoice paid
4) Deposit the payment into your bank account
5) Reconcile your bank account statements with your books so that you know that your books are accurate and money is not disappearing anywhere
6) Record your expenses, and keep track of how much your spending in what areas, differentiate between "cost of goods sold" (e.g. material costs that you bill for such as paint), and actual Business expenses.
7) Enter bills and keep track of what bills you have outstanding, how much money you owe to whom, and pay them on time
Now, the part that Excel really cannot do:
8) Run reports to get an idea of how much money you are making and spending: Balance sheets, profit/loss reports, budgets and expense categories, and comparisons from the previous years, quarters, and months. Only through these reports can you actually know how well your company is doing and whether you have money to afford things. The balance in your bank account quickly becomes irrelevant since it doesn't take into account the receivables you have yet to collect from your customers, nor does it take into account the liabilities and payables you are responsible for (taxes, bills, loans etc.).
You'd have to make all of these reports MANUALLY in Excel. What a pain.
9) You have to keep track of how many customers owe you money, how much, and when it was due. You have to chase customers, produce statements for them, assess interest charges, and not let anything "slip through the cracks".
Again, difficult to do in Excel without a lot of manual work.
10) At the end of the year you have to break all of this down into something that your accountant can use to file your taxes, and you have to make sure you have enough money to PAY your taxes after you've filed them.
Excel is BAD at all of this. But something like Quickbooks is VERY GOOD at this.
IMHO, you should buy Quickbooks Pro.
Also, forget about doing bookkeeping on your smartphone. It's ugly and painful, and really just doesn't work. Take a laptop or a Windows tablet like a Surface Pro with you if you need to do invoicing on-the-go.
The problem is that it's not just about recording the data. Your business will have a workflow to it that you must keep track of.
Your business workflow will probably go something like this (Sorry John I think i'm basically saying the same thing as you here)
1) Write up an estimate for your customer based on notes you took while talking / surveying / selling them on your product
2) Keep track of the work you've done, and eventually draw up an invoice that lines up with the estimate
3) Receive payment from the customer and mark the invoice paid
4) Deposit the payment into your bank account
5) Reconcile your bank account statements with your books so that you know that your books are accurate and money is not disappearing anywhere
6) Record your expenses, and keep track of how much your spending in what areas, differentiate between "cost of goods sold" (e.g. material costs that you bill for such as paint), and actual Business expenses.
7) Enter bills and keep track of what bills you have outstanding, how much money you owe to whom, and pay them on time
Now, the part that Excel really cannot do:
8) Run reports to get an idea of how much money you are making and spending: Balance sheets, profit/loss reports, budgets and expense categories, and comparisons from the previous years, quarters, and months. Only through these reports can you actually know how well your company is doing and whether you have money to afford things. The balance in your bank account quickly becomes irrelevant since it doesn't take into account the receivables you have yet to collect from your customers, nor does it take into account the liabilities and payables you are responsible for (taxes, bills, loans etc.).
You'd have to make all of these reports MANUALLY in Excel. What a pain.
9) You have to keep track of how many customers owe you money, how much, and when it was due. You have to chase customers, produce statements for them, assess interest charges, and not let anything "slip through the cracks".
Again, difficult to do in Excel without a lot of manual work.
10) At the end of the year you have to break all of this down into something that your accountant can use to file your taxes, and you have to make sure you have enough money to PAY your taxes after you've filed them.
Excel is BAD at all of this. But something like Quickbooks is VERY GOOD at this.
IMHO, you should buy Quickbooks Pro.
Also, forget about doing bookkeeping on your smartphone. It's ugly and painful, and really just doesn't work. Take a laptop or a Windows tablet like a Surface Pro with you if you need to do invoicing on-the-go.
Thank you both for the comments.
I appreciate how QuickBooks is designed to be my turnkey solution. I'm also sure it has improved since I last got a look at it back in the 90s I think. While playing with it, my thought was what if I want to do this or that? Custom ideas I had I was not able to implement. Thought it was sort of for people who don't want to deal with computer stuff, and just need to get their numbers done "quick." I also thought, if I have Excel why would I want to switch to this? I admit it is also my upbringing. The company where I was first exposed to Excel used it across the board in accounting. We had all our balance sheets, profit and loss, expense reports, cost accounting, you name it, in Excel.
And I apologize for not stating this earlier, but I am actually trying to get the brief run down on best practices for implementing those reports manually in Excel. I'm pretty familiar with accounting procedures and what has to be done. I want to get the technical advice on whether for instance, to keep all customers in one xlsx as I was asking above. Also will be looking to use Powershell to help me automate some of the Excel use. Sticking with Excel allows me compatibility with accountants, suppliers, and customers, and furthers my education in the use of the industry standard tool. Plus I still have very few customers, so I was hoping to get Excel implemented in an improved way before I get even busier.
Hope that clarifies my Excel questions some. But all very good advice.
I appreciate how QuickBooks is designed to be my turnkey solution. I'm also sure it has improved since I last got a look at it back in the 90s I think. While playing with it, my thought was what if I want to do this or that? Custom ideas I had I was not able to implement. Thought it was sort of for people who don't want to deal with computer stuff, and just need to get their numbers done "quick." I also thought, if I have Excel why would I want to switch to this? I admit it is also my upbringing. The company where I was first exposed to Excel used it across the board in accounting. We had all our balance sheets, profit and loss, expense reports, cost accounting, you name it, in Excel.
And I apologize for not stating this earlier, but I am actually trying to get the brief run down on best practices for implementing those reports manually in Excel. I'm pretty familiar with accounting procedures and what has to be done. I want to get the technical advice on whether for instance, to keep all customers in one xlsx as I was asking above. Also will be looking to use Powershell to help me automate some of the Excel use. Sticking with Excel allows me compatibility with accountants, suppliers, and customers, and furthers my education in the use of the industry standard tool. Plus I still have very few customers, so I was hoping to get Excel implemented in an improved way before I get even busier.
Hope that clarifies my Excel questions some. But all very good advice.
Excel is an excellent reporting tool. I use it for extensive management reports at clients. I also use it to get information to Accountants. It is the easiest way to do this.
But the underlying transaction tool is QuickBooks.
I also do not make pretty invoices. You asked initially for greater invoice efficiency. I achieve this through QuickBooks. I can create them, email them, print them and whatever.
Will keep Daytimer ink records for safety and ease of use <-- I keep all appointments and billing details in Outlook. All this is on my iPhone as well. It is easy to copy and paste the details into the QuickBooks invoice.
But the underlying transaction tool is QuickBooks.
I also do not make pretty invoices. You asked initially for greater invoice efficiency. I achieve this through QuickBooks. I can create them, email them, print them and whatever.
Will keep Daytimer ink records for safety and ease of use <-- I keep all appointments and billing details in Outlook. All this is on my iPhone as well. It is easy to copy and paste the details into the QuickBooks invoice.
By design, excel is great at analysing data but is not good at keeping and filtering groups of records
which is what you need for an accounts package.
If you don't want to be constrained by a package such as Quickbooks and you want a bespoke package,
you would be better far off using Access for most of your accounting and then linking it to excel for analysis.
Compare these searches .... Accounting In Excel 2010 & Accounting in Access 2010
You can get off to a quick start by buying a customisable Access accounts package.
If you do want to stick with excel, consider using tables within excel ....
http://www.jkp-ads.com/articles/Excel2007tables.asp
Have you come accross this ....
http://www.excel-easy.com/examples/automated-invoice.html
I do have basic accounts working with excel at the moment.
It's designed for use by a small one man business.
> It works on the basis of mirroring a bank account.
> Using a dropdown, it filters transactions and hides/shows columns based on transaction type.
> It analyses expenses by type.
> It does a basic P&L account.
> It projects profit at the end of the year based on transactions to date.
It was not easy to implement .... I was fighting and workingaround the excel structure all the time.
I can't imagine how one can use excel across the board in accounting.
You are probably doing most of this already, but, if you think it might help you, let me know and I'll post it.
which is what you need for an accounts package.
If you don't want to be constrained by a package such as Quickbooks and you want a bespoke package,
you would be better far off using Access for most of your accounting and then linking it to excel for analysis.
Compare these searches .... Accounting In Excel 2010 & Accounting in Access 2010
You can get off to a quick start by buying a customisable Access accounts package.
If you do want to stick with excel, consider using tables within excel ....
http://www.jkp-ads.com/articles/Excel2007tables.asp
Have you come accross this ....
http://www.excel-easy.com/examples/automated-invoice.html
I do have basic accounts working with excel at the moment.
It's designed for use by a small one man business.
> It works on the basis of mirroring a bank account.
> Using a dropdown, it filters transactions and hides/shows columns based on transaction type.
> It analyses expenses by type.
> It does a basic P&L account.
> It projects profit at the end of the year based on transactions to date.
It was not easy to implement .... I was fighting and workingaround the excel structure all the time.
I can't imagine how one can use excel across the board in accounting.
You are probably doing most of this already, but, if you think it might help you, let me know and I'll post it.
I just read your question in detail. As you don't have many customers, as a stopgap .....
You could have one workbook per customer with one sheet per invoice, and a summary sheet showing all invoices & payments to that customer.
You would also have a template/empty sheet for easy copying for new invoices for that customer.
You could create a separate workbook for the odd-jobs.
You would then need a summary workbook which lists and adds up all your invoices and expenses by looking up customer's individual worksheets.
No VBA is needed for the above.
Note: The example I mentioned earlier would be of no use to you as it's very reliant on VBA.
You could have one workbook per customer with one sheet per invoice, and a summary sheet showing all invoices & payments to that customer.
You would also have a template/empty sheet for easy copying for new invoices for that customer.
You could create a separate workbook for the odd-jobs.
You would then need a summary workbook which lists and adds up all your invoices and expenses by looking up customer's individual worksheets.
No VBA is needed for the above.
Note: The example I mentioned earlier would be of no use to you as it's very reliant on VBA.
That's exactly it Eirman!!
The summary workbook you mentioned is where I want to go. Can I get Excel to do this lookup? Must I look up and then enter the customer's individual summary data into the summary workbook? Or does Excel have an ability to access data in a workbook without fully opening it in Excel? Would it not be easier to use an implementation with all customers in a workbook to allow Excel to analyze it in one file? I guess not, since that is not what you said. :-)
It was across the board in the 80's. I guess there wasn't much else available that was reliable. It was before Access. I was in sales and when they gave us numbers I would ask how they did it and they said it was an Excel spreadsheet.
And the post about Access and Tables with links is also helpful since I probably want to go there too. The reasoning is to hone my skills. For me, it is not only about getting the job done, it is about increasing my knowledge of Excel's capabilities and limitations.
On the phone apps. I've been using a bar code scanner app called CD Shelf, to put together a database of 1000 or so CDs with my brother. About half done. Really seems to work great, CD cover artwork thumbnails included. Export to Excel and publish as local html for end user. So I wonder what is available that works for receipts such as NeatSheets which can be seen at neat.com...
http://www.neat.com/products/neatreceipts/
and has a PDF (attached to this post) where I was reading the bottom half of page three...
If Neat messes up or isn't as customizable as it sounds I will avoid it. Anyone here ever use it or a similar product?
Thank you all for contributing.
scantooffice-help.pdf
The summary workbook you mentioned is where I want to go. Can I get Excel to do this lookup? Must I look up and then enter the customer's individual summary data into the summary workbook? Or does Excel have an ability to access data in a workbook without fully opening it in Excel? Would it not be easier to use an implementation with all customers in a workbook to allow Excel to analyze it in one file? I guess not, since that is not what you said. :-)
It was across the board in the 80's. I guess there wasn't much else available that was reliable. It was before Access. I was in sales and when they gave us numbers I would ask how they did it and they said it was an Excel spreadsheet.
And the post about Access and Tables with links is also helpful since I probably want to go there too. The reasoning is to hone my skills. For me, it is not only about getting the job done, it is about increasing my knowledge of Excel's capabilities and limitations.
On the phone apps. I've been using a bar code scanner app called CD Shelf, to put together a database of 1000 or so CDs with my brother. About half done. Really seems to work great, CD cover artwork thumbnails included. Export to Excel and publish as local html for end user. So I wonder what is available that works for receipts such as NeatSheets which can be seen at neat.com...
http://www.neat.com/products/neatreceipts/
and has a PDF (attached to this post) where I was reading the bottom half of page three...
If Neat messes up or isn't as customizable as it sounds I will avoid it. Anyone here ever use it or a similar product?
Thank you all for contributing.
scantooffice-help.pdf
Can I get Excel to do this lookup? Must I look up ...... ?
All that you ask is a bit much from a transaction point of view for Excel especially if it is not open.
As noted, I use Excel for reporting.
I do not use Neat. It doesn't fit with my business (consulting) that I can see.
I have been invoicing customers and collecting receivables for over 10 years now.
All that you ask is a bit much from a transaction point of view for Excel especially if it is not open.
As noted, I use Excel for reporting.
I do not use Neat. It doesn't fit with my business (consulting) that I can see.
I have been invoicing customers and collecting receivables for over 10 years now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's just what I wanted Eirman. Thank you all for posting.
1. Invoice.
2. Collect receivables.
3. Enter bills.
4. Pay bills.
5. Keep a bank account and reconcile the same.
6. Keep track of Sales Taxes (if any) and build into invoice. I need to do this.
Consider asking your Accountant for advice on this matter.