This example shows how to set up QuickBooks and FormCalc for the task described above. If you need more basic information about the setup steps, please see the FormCalc Tutorial.

Overview

Rembrandt Landscaping specializes in decorative landscaping projects involving high levels of detail within a small area--water gardens, Japanese gardens, floral borders for statuary and other art work, etc. The bulk of their business consists of doing a large number of very small landscaping projects--most are under $3,000 total.

Because they provide estimates and bids on a large number of projects, they wanted to streamline their estimating process. Using the QuickBooks Estimate form was a big first step. It helped them quickly estimate material costs--trees, shrubs, sod, supplies, and so on. But it did nothing to help them estimate installation labor.

Installation labor is highly related to the items being installed. For instance, they know each tree or shrub of a certain size, of perennial flowers of a certain type, or a square foot of sod, takes an average of "X" minutes to install.

Rembrandt's owner, Joan Wales, found herself spending a lot of time duplicating each Estimate's Item descriptions and quantities in an Excel spreadsheet, just so she could estimate labor. And the usual errors caused by having data in two places cropped up: typing mistakes, changes in the Estimate that didn't get updated in the spreadsheet, and so on.

FormCalc provided an easy solution to the problem by automatically estimating installation labor in one step, right there on the Estimate form, without any need to copy or duplicate of Items or quantities in a spreadsheet. (Now Joan rarely uses Excel at all in preparing estimates.)

QuickBooks Setup

Getting her QuickBooks procedure set up to use FormCalc was easy. Joan first defined a Custom Field called Install Labor in the QuickBooks Items list. Then she entered an installation labor amount for each Item, in hours.

Here's the Custom Field window for Royal Star Magnolia, a plant Rembrandt uses in many of their designs. The installation labor requirement of 0.25 means it takes an average of one-quarter hour--15 minutes--to install one of these plants.

Next, Joan modified the template she uses on the Estimates form to include the Install Labor column:

Using this template on the Estimates form, here's how the form's column headings appear:

Next, Joan added an Item to the QuickBooks Items list to serve as a FormCalc trigger Item:

When FormCalc "sees" this Item in the Estimate form, it will display the estimated labor requirement on that line of the form.

FormCalc Setup

In FormCalc, Joan entered her new QuickBooks "trigger" Item on the Trigger Items tab. Notice that she left the Replace Description box un-checked, so that when FormCalc encounters this Item on a QuickBooks form it will simply add its results to the Description field text, not replace it.

Joan chose a Layout tab to use for processing the Estimates form, gave it a meaningful name, and selected Item and Description column numbers which match her Estimates form layout (for reference, see the Estimates form column headings illustration above). She also set the Results decimal places value to 2 so that labor estimates will be displayed with two decimal places:

Joan added a single Action to this Layout tab by double-clicking in the Action Details column of the first row. This displayed the Edit Action window, where Joan selected the "Extended total or subtotal" Action type, then selected column numbers matching the Quantity (column 3) and Install Labor (column 4) columns on her Estimate form, and chose to multiply the two columns (notice the '*' in the field between the two column number fields):

Finally, Joan clicked in the Trigger Items for this Action column of the Layout and selected the "Labor Estim." Item--the Item she had entered in FormCalc earlier--as the trigger Item for this Action tab.

Here's how the Layout tab looked when she was done:

Preparing QuickBooks Estimates with FormCalc

To prepare an estimate, Joan brings up the QuickBooks Estimate form, selects Items for the job, then drops down a couple lines and selects the Labor Estim. trigger Item (just to separate labor from the merchandise and materials lines).

Then she invokes FormCalc to process the Estimate form using the Layout described above. The result looks something like this (red has been added to show the information provided by FormCalc):

Because the Labor Estim. line is just informational--it doesn't add a labor charge in the Total column--Joan then drops down one more line, selects the Labor Item, and enters the actual labor charge she wants to use on the estimate. The result looks something like this:

Fine-Tuning the Labor Estimate

Notice that the labor hours Joan added on the Labor line are different from the estimated hours provided by FormCalc on the Labor Estim. line. This is where Joan's specific knowledge of the job comes into play. She uses the estimated labor as a guideline, but may increase or decrease the hours actually included in the estimated job cost due to a range of factors, such as:

• Knowledge of job specifics. Some jobs require much more site preparation, for example.
• Distance to the job. Distant jobs incur higher costs for travel time.
• Size of the job. Large jobs tend to have lower actual labor requirements per unit of plants and landscaping materials installed.
• Competitive factors. Sometimes Joan wants to "lowball" a bid in an effort to get a specific job.
• How "busy" Rembrandt is. When Rembrandt's employees have a significant backlog of work to do and they don't "need" any new jobs for a while, Joan sometimes adds to the labor estimate. She knows that if Rembrandt happens to land several such jobs she'll have to hire additional employees, and that means additional labor and training costs.

Another fine-tuning adjustment is to change some of the per-unit labor requirements in the Install Labor column. Suppose for example, this job will require more prep time before laying sod. In that case Joan might increase the labor requirement on the sod line. After invoking FormCalc, the Estimate might look like the following. (The estimated labor requirement is higher than it was before.)

What the Customer Sees

Joan doesn't include the Install Labor column on the printed copies of estimates mailed or FAXed to Rembrandt's customers. She doesn't want the estimated labor line to show, either. But she likes to keep it on the saved copy of the form, for reference purposes, in case she wants to review or change the estimate.

To solve this dilemma, Joan deletes the Labor Estim. line just prior to printing the form. When printing is done, she uses QuickBooks' Edit|Revert menu command to restore that line to the form. But it's not a big problem if she forgets to restore the line. If she ever needs it again, she can easily add a Labor Estim. Item line to the form and have FormCalc recalculate.

A Different Approach

Joan is happy with the method she uses, but some people want to see estimated installation labor for each line item on the estimate. Here's how the Estimate form might look if set up this way:

A slightly different setup is required in QuickBooks and in FormCalc to accommodate this approach.

Having estimated labor for each line requires adding another column to the Estimate form in QuickBooks. A good approach would be to include either the Other 1 or Other 2 column in the form's template, changing the column title to something like Extd. Labor (extended labor).

FormCalc then needs two Actions instead of one. The first would calculate values in the Extd. Labor column (by multiplying the Quantity column by the Install Labor column on each line). The second would total the Extd. Labor column and display the total on the Labor Estim. line--the same place Joan sees the estimated labor total.

For details on how to set up FormCalc and QuickBooks in this manner--with a calculated column Action and a column total Action--see the Shipping Weight Calculations Problem Solved! example.

Buy Now