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.
3. Now that the template has been customized,
specifics can be added in individually to each change
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
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.
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
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.
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.
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
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
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.
4. This sample shows how a change order might
look once completed and ready to take to the field for
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!
Joe Stoddardis the technology editor for The Journal
of Light Construction.