Utilizing Word and Excel For Estimating

New Contributor

I am a general contractor and we currently create all of our proposals in Excel.  The process is to first write a detailed Scope of Work (10-15 pages) in the workbook, input the unit costs of everything, then do the take-off and add the quantities.  Since starting this process a year ago it has helped tremendously for we do our estimating next to each line item which ensures that everything was accounted for.

 

I am having a few issues with utilizing Excel to write the Scope of Work.  In order to make the final proposal professional, there are colors, logos, grouping, etc. and other users keep "breaking" these items.  I'm trying to find a way that I could create the Scopes in Word and then create the estimates in Excel, but tie them together so that if one is updated, the other is and so we can also ensure every line item is covered.

 

Any suggestions?

3 Replies

@AZSkoz 

 

My first response is to go to Word's Mail Merge function for, at the very least, a cover page..because that's where you could have such things as logo, colors, key groupings and high level "bottom line" figures for major categories in your estimates. The backup and detailed sheets could be the Excel documents.

 

The Excel workbook with all its details can be used as the source for the Mail Merge document--so there'd be no problem with inconsistency, so long as you print them at the same time and make no changes in the backup details after printing the cover sheet (or vice versa). This would presume, ideally, that your Excel workbook is consistent enough from job to job that a master Mail Merge document could be created without a need to make changes.

 

For what it's worth, mail merge can be made to be very smart, with conditional sections--appearing some times, but not always, so that could handle the difference between types of projects, or sub-types.

@AZSkoz 

Thanks Mathetes; however, I'm not quite sure I'm understanding. I attached a screenshot of what one of them looks like.  You'll see that on the left is the written Scope of Work, then on the right, after the black row, is the actual estimating.  You can see in this workbook, that someone already messed up the conditional formatting running down the center.

 

All of the Scope of Work is printed and presented to the client and the numbers are summaries and included in the report (they don't see the detailed number.)

 

Is there something I don't know about Mail Merge that would help me somehow?  The primary focus is not necessarily the cosmetics of it all.  It's the ability to input numbers directly next to the line the Scope was written.

 

Thanks again.

@AZSkoz 

 

This would be a LOT easier face-to-face than via postings on a message board. You could show me more than a single screen shot, I could demonstrate what I mean by referring to Mail Merge and its capabilities.

 

A screen shot, for example, is somewhat informative, but it (for example) doesn't enable me to see what those "conditional formatting" rules are that you tell me have been broken. Conditional formatting is typically used to highlight some cells that meet (or fail to meet) certain conditions. I see cells that are highlighted, but whether that means a rule has been met, not met, ignored....I can't actually tell (despite how clear it it to you). And it's not clear, although in person it could immediately be clarified what the customer actually ends up seeing of this, which numbers are hidden and which are presented.

 

My point on Mail Merge--you had been asking of a way to integrate Word and Excel--and I simply said it made me think that Mail Merge might help. You may well be thinking of Mail Merge as nothing more than a way to get "personalized" versions of what are in fact impersonal mass mailings. The address and salutation change, but everything else is the same throughout hundreds or thousands of letters. And in fact, that probably is how it's used in 97.3% of the times it's employed.

 

BUT, in fact, and I've done this, you can integrate an Excel database with complex data of various sorts with Word, through the merge capabilities, to produce highly individualized documents, each with quite distinctive sections. Still, yes, a basic consistent structure. During my working career (I retired nearly 20 years ago) I used mail merge, much more primitive I suspect than it is today, to produce individualized letters to thousands of employees concerning their benefits; different lines of detailed and differing costs, different paragraphs altogether, were automatically placed on the page for John Appleseed, who got a different letter entirely from Mary Marple, etc., etc. Yet all were also similar in their purpose while varied in their content.

 

You put these together using a similar structure for each client, but they also vary. As I said, I was just proposing a highly individualized cover page, with the details that would follow from Excel, the latter being the data source for all of it.

 

Now, that still may not be what you want--which is fine--but we need to get a lot more info flowing in both directions in order to progress. Is it at all possible for you to post an actual spreadsheet, not just an image? Does the idea of a cover letter make any sense at all (a personalized cover letter with the high level bottom line numbers)? Any sense at all?