If you use QuickBooks for your accounting and you create your Estimates in Excel instead of within QuickBooks you loose out on job costing capabilities and so much more!
I often see and hear comments such as this as I browse the web or talk with potential customers for our software:
Right now we are using QuickBooks Contractor. It is ok for our accounting software but we are looking for something that works as an overall system for our office – CRM, estimating, job costing, proposals, work tickets and most importantly easy for my guys to use. Right now we are using Excel for Estimates and invoices.
When I see and hear comments like this I get so confused – because QuickBooks does allow you to create Estimates, Proposals, and do Job Costing - so I thought I’d attempt to write a short article to address this.
Creating Estimates in Excel vs. QuickBooks – Pros and Cons:
Excel is REALLY easy to use – anyone can quickly whip up an Estimate, Proposal or a Work Order., you can create some great looking documents that you are proud to hand to your prospective customer. But that is about as far as you can go.
QuickBooks, on the other hand, if set up properly will allow you to create an Estimate, Proposal, Work Order, or an Invoice and perform job costing just by entering vendor bills, writing checks, entering credit card charges, and employee payroll. You then can access all kinds of Job Costing reports and an Actual vs. Estimate Report – which allows you to see the difference between what you thought you’d make on the job vs. what you actually made.
But….QuickBooks requires more work on the front end and then proper training for your employees.
I think I just heard everyone groan! There is no way that I can write a “short” article on the benefits of using QuickBooks to prepare these documents for your business, but I can provide you with some suggestions to get started.
- Is the QuickBooks Estimate function turned on? If not go to the Edit menu -> Preferences -> Jobs & Estimates -> Company Preferences tab -> set “Do You Create Estimates” AND “Do You Do Progress Invoicing” to Yes
- Modify/Customize the built in forms to meet the needs of your company using the Layout Designer, by downloading a pre-built template or by using the Create Form Design option. All of these options are available from the Lists menu -> Templates -> and then clicking the Template button at the bottom of the window.
- Create a FULL list of the services, cost codes and/or products your company uses for billing
- Have a meeting – get EVERYONE involved – take that master list of products and/or services/cost codes and come up with descriptions that EVERYONE can agree on {letting them know that they can “tweak” it more to their liking later}
- Go to the Items List and set up items that correspond to that master list of of the things your company does, creating Group Items or Inventory Assemblies if appropriate
- Create an Estimate, a Proposal, or a Work Order using the entries in your Item List – print it out and see what everyone things of it. If they are happy teach your Estimators how to use the Estimating function
- When entering Vendor bills, writing checks, or entering Credit Card Charges use the Items tab and assign the cost to the job
- Turn on time tracking, enter employee hours by Job, Service Item, and Payroll Item
- Create a Progress Invoice from an Estimate for the current amount that you are billing the customer
- Look at the Estimate vs Actual Report for a specific job and look at the report results
These suggestions are just the tip of the iceburg for taking QuickBooks from plain-Jane accounting to job-costing accounting. Use the search box on our site and enter specific keywords for additional tips.
If you can’t get your Estimators to leave Excel, there is a 3rd party application Transaction Pro Importer – that will import your Excel based Estimate into QuickBooks for you and you can implement some of the other suggestions in this article.
I hope you’ve found this article to be helpful, if so please take a moment to leave a comment or ask a question – or share it on your favorite social networking site if you think others would find it to be useful.
Dealing with special customer invoicing requirements can be a royal pain! Sometimes it feel like each customer you deal with says “if you don’t invoice me like THIS you aren’t going to get paid”. While QuickBooks is a very flexible software program, sometimes it’s very difficult to meet the requirements of specific customers or clients. Below is a perfect example, submitted by a reader of just how difficult billing requirements can be.
I use QuickBooks Contractor version as a self-employed contractor with a hospital. The problem is I get the job on a P.O. from the hospital, say it’s for $80,000. As I do different stages of the job I bill the hospital for that stage and they send me a check. When I do the next stage the hospital wants the invoice to reflect the original P.O. and the money they have already paid. How do I do that on this software? Jean
____________________________________________________
Jean, I’m pretty certain that you can meet these requirements – it will cause some extra work on your part each time you are ready to bill the hospital for the next stage; but I don’t think it will be a lot of work.
Go ahead and create your invoice as usual – I’m making a BIG assumption here that you take the PO from the hospital and set that up as a QuickBooks Estimate and generate Progress Invoices from your Estimate {but even if you don’t that’s ok}.
Once you are viewing that Invoice in QuickBooks, click the Letters icon at the top and choose Prepare an Invoice Letter
Right click on the image to enlarge it
When the Choose a Letter Template window appears, select Invoice Letter with Details and then click the Next button
Right click on the image to enlarge it
This now sends your Invoice into Microsoft Word and by default provides information about the invoice that you can then modify to meet the invoicing requirements of the hospital.
Right click on the image to enlarge it
For example, I would change the heading called Invoice Amount $720.00 to read Original PO Amount $80,000.00, Invoice Balance $720.00 to read Previous Payments Received $XXX.XX, and add a line that reads Current Payment Due $720.00.
Right click on the image to enlarge it
NOTE: You can edit this letter template and include the changes to the wording – that way all you have to do is enter the amounts for Original PO and Previous Payments. To Edit a letter template go to the Company menu -> choose Prepare Letters with Envelopes -> Customize Letter Templates -> View or Edit Existing Letter Templates -> choose Invoice Templates -> Invoice Letter with Detail.
QuickBooks has a lot of built-in functionality – the problem is finding it! We hope you found this QuickBooks tip for dealing with special invoicing requirements to be helpful. If so please take a moment to leave a comment or share it on your favorite social network.
A QuickBooks tip for creating a pre-set or memorized Estimate with a standard set of items that will automatically display whenever you create an estimate.
Some contractors, and many other types of businesses as well, frequently prepare an Estimate, Schedule of Values, Proposal, or Quote for different customers that use the exact same items – or groups of items, just with different pricing. Manually creating this same bid over and over by pulling the items into the form is a repetitive and time-consuming task that can easily be automated in QuickBooks by memorizing it.
Follow these simple steps to create a pre-set estimate with standard items and memorize it in QuickBooks
- Pull up a blank estimate form
- Build your bid using Items (cost codes) from your Items List
- Do NOT add any dollar amounts
- Review the basic proposal carefully to make sure that you’ve included all the standard items
- Press the CTRL and “M” keys at the same time (this tells QuickBooks that you want to Memorize the transaction on your screen)
- QuickBooks may present you with a message window that states: When you memorize this estimate, QuickBooks removes the customer:job so that the estimate can be recalled for any customer. Click the OK button.
- Next, a Memorize Transaction window will appear. Use the Name field to hold a description for this quote (Site Work, etc.), select the radio button next to Do Not Remind Me and click the OK button. You can also close the Create Estimate window.
You’ve now created a pre-set Estimate containing standard items that your company uses for bidding.
Using or recalling the memorized Estimate
The next time that you need to create a bid, follow these simple steps:
- Click the Estimates icon from the Home Page OR from the Customers menu choose Estimates OR From the Lists menu -> choose Memorized Transactions OR press the CTRL and the “T” keys at the same time
- Click on the transaction name -> and click the Enter Transaction button
- Select the Customer:Job, review, add any additional/special items for this project, enter pricing, print, and save.
NOTE: Make sure that you always carefully review the information on the form before you print it to make sure that all of the required items, specific to this job have been included. You can add or remove items from the base group that you initially memorized.
I hope that you have found this article to be helpful, if so please take a moment to leave a comment.
This QuickBooks tip discusses adding project information – such as the project name and location – to Invoices and Estimates – by using the Ship To Address block in the job record and modify the template slightly.
There are many times that a contractor, or other business owner, needs to display project information – such as the project name and location – on invoices and estimates. Displaying project information is very useful, especially when a business is working on more than one job or project for the same customer and needs to make it clear which job or project they are talking about when it’s time to create an Estimate or an invoice.
Adding Project Information to Invoices & Estimates
While there are a couple of ways to add project information – such as the project name and it’s location – to an Estimate or Invoice that you generate; I’ve always used the “Ship To” block in the Job record to hold this information.
From the QuickBooks Customer Center, pick a current Job from the Customers & Job list. You can click on the job name to select it and then click the Edit Job button OR if you double-click on the job name you’ll be taken to the Edit window.
On the Address Info tab, look to see if there is any information in the “Ship To” block; if there isn’t, click the “Add New” button (located just below the Ship To block).
When you click on the “Add New” button, QuickBooks opens the Add Shipping Address Information window. Here you will enter details about the job that you want to display on your Estimates and Invoices – see sample screenshot below.
Right click on the image to enlarge it.
Be sure to check the option for default shipping address, and click the OK button to save the information. Your Job record now has both a billing and shipping address.
Right click on the image to enlarge it.
Next you’ll want to edit your Estimate and Invoice templates. From the Lists menu, choose Templates (if you aren’t sure which templates you use, click from the Home page click the Estimate or Invoice icons and the template name will be displayed in the upper right corner of the window), double-click on the Estimate template. This opens the Basic Customization window.
From the Basic Customization window, click on the Additional Customization button. On the Header tab – check the Screen and Print options for both the Name/Address and Ship To titles – you can even make changes to the information that displays on the final forms. For example, you can change Name/Address heading to read To Owner and Ship To to Project Information. See the before and after screenshot below.
Right click on the image below to enlarge it.
NOTE: You may need to change the positioning in the Layout Designer, depending on what other options you select on this window.
Click the OK button twice.
Switch to the Home Page, click the Estimate icon and choose the job that you originally edited. Your Estimate form now displays both billing and project information.
Right click on the image to enlarge it.
When you print your Estimate, this same information will display on your printed form.
Right click on the image to enlarge it.
We hope you’ve found this QuickBooks Tip for adding Project Information to your Estimates and Invoices to be valuable. If so please take a moment to leave a comment.
There are many times that a contractor will want to create a budget, to capture the costs and markup, for a job that they are working on. There are several methods to do so, but the one that I like best is to create a custom Estimate that will track estimated costs (the budget) a planned mark-up and a fixed sales price to the customer. This method is available to contractors using QuickBooks Pro, Premier (any flavor), or Enterprise (any flavor).
To accomplish this we’ll start at the Lists menu -> Templates ->
Right click on the image to enlarge it.
This will open a window displaying a list of all the available form templates within QuickBooks; scroll down until you find the Custom Estimate template – click on it to highlight it.
Right click on the image to enlarge it.
Click the Templates button at the bottom and select Duplicate -> Estimate -> and click OK. Your list now contains a template named Copy of: Custom Estimate. Double-click on this template. This opens the Basic Customization window.
Right click on the image to enlarge it.
Click the Manage Templates button, this opens the Manage Templates window. In the Template Name field, change the name FROM Copy of: Custom Estimate to something like Estimate with Budget and click the OK button.
Right click on the image to enlarge it.
Back at the Basic Customization window, you can:
- Insert/Use your company logo on the Estimate form by clicking into the box just in front of the words “Use logo”, this activates the Select Logo button and you can browse to the location of your logo file on your computer.
- Select a Color Scheme - click the drop down menu and choose from: Black, Gray, Maroon, Green, Blue or Beige.
- Change Font For – Title, Company Name, Company Address, Labels, Data, Subtotals Label, or Totals Label; you can change the Font Style, Color, and Size.
- Company & Transaction Information – select by checking whether or not you with to display Company Name, Company Address, Fax Number, Phone Number, Email Address, or Web Site Address. Click the Update Information button to make sure that the items you have selected are available in the Company Information window.
- Additional Customization button – this is the button that we want to click on.
- Right click on the image to enlarge it.
Clicking the Additional Customization button displays a window with several tabs – Header, Columns, Footer, and Print. These tabs allow you to design an Estimate that will meet the needs of your company. Starting with the Header tab, you can choose whether you want to print the following items on the actual Estimate form or to simply display them on your Estimate form, you can also change the titles of all the options available. Standard options include: Title (form Title), Date, Estimate #, Name/Address (billing), Ship To, Project/Job, P.O. Number, Terms, Due Date, Rep, Account Number, FOB, Other, and any Custom Fields that you have created in the Customer and/or Job Record.
Right click on the image to enlarge it.
When you have made your selections, click on the Columns tab. Here you want to make sure that the Item, Description, Quantity, Cost, Amount, Markup, and Total items are selected to display on your Screen; while the Description, Quantity, Amount and Total are selected to Print on the actual form. You can also change the title of these fields if you wish – for example, you can change the Markup title to Profit & Overhead.
Right click on the image to enlarge it.
Click on the Footer tab. Here, you can choose to Print a Message, Subtotal, Sales Tax, Total, and Signature on the final form. You will not be able to choose to display on your form the Subtotal, Signature, or Plain Text items. When you are ready click the Layout Designer button.
Right click on the image to enlarge it.
The Layout Designer allows you to arrange the elements that you have selected to appear on your printed Estimate form. In most instances clicking on an item will allow you to drag it to another location or resize it.
Right click on the image to enlarge it.
When you have arranged everything to your satisfaction click the OK button twice, and close the Templates List window.
Using the New Estimate:
When you use the new Estimate form, you need to use it in a specific order – enter information in the Item, Quantity, Cost and the Markup/Profit & Overhead columns. Overhead & Profit can be entered as a fixed dollar amount or a percentage – if you use a percentage you MUST enter the % sign.
Right click on the image to enlarge it.
When you enter the line items on your estimate in this way, QuickBooks automatically calculates the Total that you will be charging your customer using the following formula: Quantity (15) times Cost (125.00) = Est. Amount (1875.00) plus Profit & Overhead (33.3% or 624.375) equals Total (2499.38). You’ll also notice that QuickBooks will keep track of the totals of your Estimated Amount, your Profit & Overhead, and the Total that you will charge your customer.
Right click on the image to enlarge it.
When you’ve entered all of the vendor bills for this job and have billed it off to your customer, you should run an Actual vs. Estimate Report to see how you did. Compare the numbers from the report to the totals of your Estimate to see where you need to adjust your Profit & Overhead. Keep in mind that any difference in cost will effect your total Profit & Overhead.
Let’s assume that you had a small job, you estimated costs at $2375.00 added a total Profit & Overhead of $749.38 for a total contract of $3124.38.
When all bills were entered your actual cost was $2500.00 or $125.00 more than you Estimated; this means that your Profit on the Job is really $624.38 ($749.38 Estimated Profit & Overhead MINUS $125.00).
Right click on the image to enlarge it.
Having access to this information is vital to your bidding process.
