This article will go over the function of helping you create estimates from your jobs straight into Microsoft Excel and then to Microsoft Word. This function gives you lots of flexibility with estimating and creating your own proposals.
Contents
Create Estimate Template
Making An Estimate
Create Estimate Template
Go to the following link to watch the video below: https://fast.wistia.net/embed/iframe/whyfameg5a
Like in the example video above -
First, create a template from Microsoft Word that you will use for your estimates. You will create a Microsoft Word document that contains estimate information and wording. Instead of inputting the prices or descriptions, add a merge field from CLIP later on. This document can be blank for now but eventually you will want to fill it with useful proposal information.
From the jobs tab, click Create Estimate towards the bottom.
From the Estimate Mail Merge screen, click the Add/Edit templates button.
If you are setting up your first template from the Estimate Templates screen, click Add. Fill in a Code field (this should be a short description of the new estimate form). In the Description, enter a longer description.
In the File field, enter the name of the Microsoft Word document you want to create or click the three dot button in order to select a Microsoft Word document from your computer.
Once you click Save, click the Edit button in order to open your Microsoft Word document to Add/Edit the template to add merge fields. If you are creating a new template, you will see Microsoft Excel open up. Microsoft Word will open and afterwards you will have access to the mail merge tool bar.
From here, type out all the wording of your estimate. When you get to a spot where you will want information from CLIP, select the Insert Merge Field button. You will now see a list of fields you can use.
When Print More Information is selected, you will have 32 job fields you can select from the merge database. When Print Less Information is selected, you will have 20 job fields you can select from in the merge database. You will have additional job fields for Bill_Type, Minimum and the Maximum Charge Fields.
See below for a listing of the Merge Fields:
Print More Information:
NUM= Customer Number
NAME= Customer Property Name
PADDRESS= Customer Property Address
PCITY= Customer Property City
PSTATE= Customer Property State
PZIP= Customer Property Zip
BWTEL= Work Telephone
BHTEL= Home Telephone
EMAIL= Customer Email Address
BILL_NAME= Customer Billing Name
GREETING= Customer Greeting
BILL_C_O= Customer Care Of
BILL_ADDR= Customer Billing Address
BILLCITYST= Customer Property City & State
BILL_ZIP= Customer Property Zip
ESTIMATENOTES=
TOTALCONTRACT = The total price for the contract, including the discounts.
CONTRACTTAX = Sales Tax on the contract, if any.
TOTALCONTRACTWITHTAX = Total with the tax
TOTALCONTRACTWITHOUTDISCOUNT= Total price without a discount
CONTRACTLENGTH = Number of months that this contract will go.
MONTHLYPAYMENT = Monthly Payment for the customer.
MONTHLYDISCOUNTPCT = Percentage of discount if the customer chooses the MONTHLY= (2 = 2%)
MONTHLYDISCOUNTSAVINGS = Dollar amount of the savings.
TOTALNONCONTRACT = the total for the non-contract jobs
NONCONTRACTTAX = Tax on the non-contract jobs
TOTALNONCONTRACTWITHTAX = Total with tax
SALESMAN = The Salesman who sold the job
EARLYPAYDISC = the discount if the customer pays early. (In percent – 5 = 5%)
EARLYPAYDEADLINE = The date that the early pay is due.
EARLYPAYDISCOUNTAMOUNT = The dollar amount of savings.
EARLYPAYDISCOUNT = Amount of Discount
TOTALPRICE = Totals for all of the jobs.
TOTALTAX = The tax for the whole contract
TOTALWITHTAX = Total for contract with Tax
EP_TOTALPRICE = Early Pay totals for all of the jobs.
EP_TOTALTAX = The early pay tax for the whole contract
EP_TOTALWITHTAX = Early Pay total for contract with Tax
A_JOBNUMBER = Job number for the first job
A_JOBNAME = Job Name of the first job
A_AMOUNT = Charge per job for the first job
A_ADJUST = Charge over max for the first job
A_AREA = Area for the first job
B_JOBNUMBER = Job number for the second job.
The jobs go up to FF_JOBNUMBER.
Print Less Information:
NUM= Customer Number
NAME= Customer Property Name
PADDRESS= Customer Property Address
PCITY= Customer Property City
PSTATE= Customer Property State
PZIP= Customer Property Zip
BWTEL= Work Telephone
BHTEL= Home Telephone
EMAIL= Customer Email Address
BILL_NAME= Customer Billing Name
GREETING= Customer Greeting
BILL_C_O= Customer Care Of
BILL_ADDR= Customer Billing Address
BILLCITYST= Customer Property City & State
BILL_ZIP= Customer Property Zip
ESTIMATENOTES=
TOTALCONTRACT = the total price for the contract, including the discounts.
CONTRACTTAX = Sales Tax on the contract, if any.
TOTALCONTRACTWITHTAX = Total with the tax
TOTALCONTRACTWITHOUTDISCOUNT= Total price without a discount
CONTRACTLENGTH = Number of months that this contract will go.
MONTHLYPAYMENT = Monthly Payment for the customer.
MONTHLYDISCOUNTPCT = Percentage of discount if the customer chooses the MONTHLY= (2 = 2%)
MONTHLYDISCOUNTSAVINGS = Dollar amount of the savings.
TOTALNONCONTRACT = the total for the non-contract jobs
NONCONTRACTTAX = Tax on the non-contract jobs
TOTALNONCONTRACTWITHTAX = Total with tax
SALESMAN = The Salesman who sold the job
EARLYPAYDISC = the discount if the customer pays early. (In percent – 5 = 5%)
EARLYPAYDEADLINE = The date that the early pay is due.
EARLYPAYDISCOUNTAMOUNT = The dollar amount of savings.
EARLYPAYDISCOUNT = Amount of Discount
TOTALPRICE = Totals for all of the jobs.
TOTALTAX = The tax for the whole contract
TOTALWITHTAX = Total for contract with Tax
EP_TOTALPRICE = Early Pay totals for all of the jobs.
EP_TOTALTAX = The early pay tax for the whole contract
EP_TOTALWITHTAX = Early Pay total for contract with Tax
A_JOBNUMBER = Job number for the first job
A_JOBNAME = Job Name of the first job
A_BIL_TYPE = Include in Contract, Per service, Time and Materials, & Pre-Pay
A_MINIMUM= Minimum for the first job
A_MAXIMUM = Max for the first job
A_AMOUNT = Charge per job for the first job
A_ADJUST = Charge over max for the first job
A_AREA = Area for the first job
A_TOTAL = This field takes A_MAXIMUM times A_AMOUNT
B_JOBNUMBER = Job number for the second job.
The jobs go up to T_JOBNUMBER.
Making An Estimate
In the job detail screen, you have an option to include or not include the job in the estimating function. Make sure that at least one job is marked as Include In Estimate. As long as one job is marked, you will still get the merge field to show up for each job when creating a mass estimate later on.
From the screen above, make sure that the Estimate Code matches the template that you previously created. In order to create an estimate out of this job, click Create Estimate. To do a mass estimate later on in the year, the job's that are selected as Include In Estimate will show up on that screen as customers included in the mass estimate process.
Click Create Estimate and select the template at the top or click Add/Edit Templates to edit or create a new template. Give it a Description in step 2, and then click the Run Merge button to the right of Description.
Microsoft Excel will open briefly, DO NOT MANUALLY CLOSE EXCEL. Allow CLIP to close Microsoft Excel. A new line will appear under step 3 (see and print Estimate). At this point, click the View button in order to view the estimate before finalizing the data and printing it off.
In newer versions of Microsoft Word, there will be a Mailings tab. This tab will allow us to insert merge fields. Preview results of the merge, align them properly, and finalize the document for printing.
Let us know if you need any further assistance!