Creating a Change Order Template, continued

Formatting the Main Page

Once you're done with the "Customize" page, click the "Invoice" tab at the bottom of the sheet and begin formatting the final change-order template. When completed and filled in, your file should look like the one in Figure 3.

Figure 3. Now that the template has been customized, specifics can be added in individually to each change order.

Here's a summary of the changes: Header. First, change "Invoice #" to read "CO #." Then change the INVOICE text box to read CHANGE-ORDER. I also changed the font to Copperplate Gothic Bold so it would match the company information, and resized the text box containing the company info by right-clicking and dragging the handles to stretch the box. Customer info. Change the fonts to match the font used for the company information. Next, instead of "Order #" use "CO Desc." -- a brief description of the change order. Then, change "FOB" to "Job #" -- this is your internal job number. Description area. The original automatic math functions are not exactly what you need for a change order, and since the template file is hard-coded using the Visual Basic for Applications scripting language (VBA) and is password-protected by some zealous programmer at Microsoft, it's a big hassle to change any of it. Luckily, with a little creativity, I was able to make it work without getting too far under the hood. The only new formula I added was to cell K32 [=SUM(L18:L31)], which calculates the cost of the particular change order before it's fed into the cells at the bottom of the column. I also added some text to permanently indicate both the original contract price and any payments to date.

Payment Details

Change "CC#" to "Ck or CC#" and adjust the blue boundary by right-clicking and dragging. Totals area. Add in the Overhead and Builder's Fee to indicate the markups, and "Revised Contract Price" under the TOTAL. Footer. The template is set up with a cell for "fine print." I made use of it by adding text that could help eliminate misunderstandings about what happens to a job when changes are added: "Your signature appearing above signifies acceptance of this change order, and authorizes us to begin the work outlined herein. You understand this change order may increase your total financial obligation under the terms of the original contract, extend the completion date into the future, or both." Of course, have your lawyer take a look to make sure your notice is binding in the state you work in.

Color Coding

I take a lot of ribbing for color coding my spreadsheets, but experience has taught me that anything you can do to eliminate data entry mistakes or omissions is well worth the hassle. For our change order template I decided on a simple color code scheme whereby yellow cells are those which the template automatically calculates, blue are the Original Contract Price and Payments to Date, and green are the data you or your lead man in the field would need to add to the spreadsheet.

Finishing Up

When you're satisfied with the way the new change order template looks and works, go back to the Customize Your Invoice tab and click the Lock/Save Sheet button at the top of the form. When saving this time, and the message box pops up asking you what to do with the database record, select "update the existing record," which will save the changes to your original file name and location.

Using the Change Order Template

To use the change order template, click FILE, then NEW. You'll find the new change order template ready to use in the "General" tab. Just double-click to open, and then "save as" an Excel file (.xls) with the appropriate job name and number. Figure 4 shows a filled-in version of the template.

Figure 4. This sample shows how a change order might look once completed and ready to take to the field for a signature.

Minor quirks. There are a couple of minor quirks due to the change order template starting life as an invoice -- first, any allowance credit or payment has to be entered with a negative number in the quantity column, and you'll get an alert message complaining about it. Just click YES to continue. Second, the Builder's Fee and Overhead will be a running total for the job, not just the individual change order. You can eliminate them by setting the percentage to "0" on the CUSTOMIZE page, and adding individual markup line-items in the user-entry part of the form. Minor quirks aside, this change order template should take you a long way to getting paid for all those little "extras" that always end up being an argument at the end of a job. Don't feel like setting up your change order template from scratch? Log on to the JLC Computer Solutions Forum and download a free copy!