The Mote Method

The Engineer’s Database

January 22, 2010

I have decided I will roll out a fourth book. This will expand on The Engineer’s Basic. I separated the subject because it is a whole other world and level of thinking. The Engineer’s Basic is concerned with the contribution to the calculations whereas The Engineer’s Database is concerned with the engineer’s role on the project.

The potential for Excel VBA in the engineer’s project role is discussed here. Beyond excellent calculations, the opportunity to reassert the engineer’s responsibility for the material, budgets, manpower, schedules and project deliverables is really exciting. I could only achieve this through the rare opportunities and physically volunteering for the roles.

Level IV databases usually have most of their structure in place within two to three months and last the duration of the project with valuable insights for the future. I have performed  the following databases using Excel:

• Energy auditing of 200 schools
• Material Take Off and tracking for $6 bn project
• Material Estimate figures for a $1bn project
• Preservation management tool for over 2000 pieces of equipment
• Manpower and budget schedules for 20,000 hrs lump sum with workshare
• Drawing and calculations register for $3 bn project and workshare
• Volumetric studies for early purchase based on historical data
• Calculations for concrete building to blast
• Connections check for piperack design

 

For the purpose of this blog, I will look at the material estimate database concept.

Conventionally, keeping the material estimate in paperwork format is a major headache at the best of times. When I first did estimate work in the pre-windows days, there would be scribbles on the drawings and pages of tables of quantities, without explanations. Some of it would be relevant, or information only. These scribbles were often poorly written in fading pencil with or without a date or signature. To revisit these after time required the patience of a watchmaker and careful considerations. It is slow work and often impossible to be confident in the results. Eventually, you have an endless quantity of paperwork with numbers collated into a non-visual Excel format which is very difficult to read and impossible to review or visualize if you have broken the chains to the thinking that went on before. Additionally, there is nothing worse than having to take over someone else’s work when there is no narrative. For most small and medium-sized projects this is not really a significant problem but imagine handling a multi-billion dollars project with multiple areas and multiple teams. By nature, we know different teams will do it differently. The challenge is, amongst all the different workbooks in all the different folders for all the different areas, all quantities have to follow certain assumptions and rules and somehow be collectable into a high level summary. Never happens, despite best intentions as you race to the deadline and field endless meetings with impossible questions and comments to resolve with your work.

What is today’s conventional approach in Excel? The estimate list would be compiled into a spreadsheet of everything, spreading itself out to many columns and infinite number of lines. You have to use frozen views to scroll down and across to find a specific value. An MTO like this could have 50 columns or more with cladding and anchor bolts types mixed in with dates of planned delivery, painting requirements needing to be identified as site supplied etc. Numbers get mixed up and moved to the wrong column and the list in hard copy form is enough to give you a mild headache from looking at a million borders, tiny numbers and no logic.

It seems one is forever checking the sums equations and improperly nested if statements. For example, say there are three contractors to assign anchor bolts for the concrete work. In one column is listed the contractor who is responsible for the work. In a list of over 500 foundation items you ask how many anchor bolts need to go to contractors A, B and C, how many are on site and who gets what first? And how many are vendor supplied? At the bottom of  the list there is a formula for counting that is getting harder and harder to write. This is the domain of the Excel reference books we never get round to reading. It is hard work but not smart. The world of accountant defaulting habits cannot apply to database models without becoming a tough job.

My approach is radically different. It may not appear intuitive or logical at first but once you see it in action, you will realize there is no other way to manage it. I develop a series of workbooks that can break the project by Area, (or Turnover systems, disciplines  or contracts etc) and each workbook is identical in design. When one Area workbook is designed, approved and tested; it can be rolled out rapidly for all other Areas. In doing this, more than 80% of my budget time of 3 months will be soaked up in this singular process, verifying, documenting, defining and so on

We do not want a single database of everything. We want a collection of hyperlinked lists of tagged items that hyperlinks to a Tagged Worksheet built from a template. As each hundreds of worksheets in the Area Workbooks are identical in structure we can roll up quantities to an Area Summary Worksheet. A Master Workbook will collect all Area Summaries and roll these up to a Project Summary.

On a large project, the issues for the material estimate database are:

• Consistency – looks planned
• Visibility – diagrams are included
• Traceability – being able find the design basis
• Checkability – Easier to verify numbers
• Defining controls- agreeing what is measured
• Designing for the future – planning for new information or the potential for it
• Building the team around the estimates – training the team.

 

The material estimate database is a process that uses MS Excel workbooks, plot plans, datasheets and flowcharts. We devised a plot-plan coordinated database that kept track of quantities. Basically, the front sheet of an excel workbook is a plot plan view of the area. One could click on an area and it would go to a more detailed equipment level plot plan. Clicking on specific items would navigate to a proforma worksheet. This worksheet would show:

• Summary of quantity
• what was assumed,
• the concept layout
• who created the quantities,
• the work package number,
• who checked it,
• when it was done, and
• dated revisions notices.

 

All Area Workbooks had designated directories, designated worksheets and fixed proforma datasheets, for completion by other engineers or populating from external sources. We assumed major equipment tags of all items to be installed, had associated materials and costs for planned(and actual) piling, concrete, steel and their schedule dates, where known.

 Let’s say there is a list of equipment tags in an Area Worksheet and it may have an associated CAD dump file. You can imagine a procedure that will go something like this,:

• Read the first filename in a list
• Check if a worksheet with that filename exist
• Copy template worksheet and rename to filename
• Create hyperlinks between list and filename worksheet
• Open CAD Workbook of the same filename
• Read column x for values of braces and add to braces total
• Read column x for beams and read column y for quantities, add to beam total
• Read column x for columns and read column y for quantities, add to columns total
• Close workbook
• Return to database worksheet
• Write totals into specific cells

Nearly all of this  can be achieved using a macro recorder but you will  need to add loops, If statements and rename your macros appropriately. This is shown in The Engineer’s Basic. 

I try to verbalize the process, and then draw the flowchart. This is building the story and it becomes one I can repeat on project after project with improvements. In discussing this procedure with your ‘buddy’ or boss, you will start to see how the work process can be built, refined and designed.

For the Material estimate database  the starting point is the worksheet list and the design of the template worksheet that will multiply from the worksheet list. For the worksheet list, you need a list of the filenames in electronic format and the association tag to the database.

For example, a list of 300 piperacks has associated material quantities dumped into individual workbooks, for each piperack. A piperack module has module steel, stick-built steel, concrete, piles, excavations and backfills. While piles, excavations and backfills are estimated. Each CAD Dump workbook for each of the principal material components can be opened by macro, retrieved and summarized for beams, columns, braces, handrails and posted into the correct cells on your waiting worksheet. If there are 900 files for the piperacks comprising the concrete, steel and foundations, the final numbers are placed onto the 300 worksheets corresponding to the piperacks.

The template worksheet will collect into fixed cells specific values for concrete, steel and foundation quantities. Thus rows 20 to 40 collects all the concrete quantities, rows 50 to 100 collects all the steel quantities and rows 110 to 130 for the foundation quantities.

Rows 200 onwards, collects the assumptions, sketches and basis for the estimated quantities.

Row 300 onward adds the History for the worksheet.

What a lot of rows! Remember, we have at our disposal 64,000 rows per worksheet. A macro can be run to hide all rows with blank values and then you will have a condensed worksheet that can be printed without difficulty.

Fixing cells to certain account codes and property will make data collection so much easier in the programming. I know in a post like this, it is the tip of the iceberg, too much to digest and there is still so much to show and to say. I have a lot of enthusiasm for this work and I know every engineer that had the chance to try their hand at this database development loved it also.

Remember, the size of the job is the incentive not the disincentive. This will ignite your passion in a way that the conventional  2D listing will not!

All this and more will follow in The Engineer’s Database.

Filed under: Database

Tags: civil engineering database, Database, engineering database, Excel, material estimates, VBA