Creating A Bill Of Quantities On Excel – How Easy Is It?

What Is A Bill Of Quantities In Construction? 

A bill of quantities (BOQ) is a detailed pricing document that provides an itemised list of materials and services needed to complete a project.  

Quantities listed in a BOQ are measured terms of number, length, area, volume, weight or time.  

The document is usually prepared by a quantity surveyor or cost consultant based on quantities identified in completed project drawings and specifications.  

A bill of quantities defines the quality and quantity of work to be carried out by the main contractor. It provides valuable information on material scheduling and ordering, cost planning and analysis, and many other important pieces of information necessary to complete a construction project. There are two types of BOQs: 

• Approximate bill of quantities - Also known as a notional or provisional bill of quantities, is produced when the measurements or costs are incomplete. Most details are approximated and are remeasured during the construction process. 

• Firm bill of quantities - A firm bill of quantities contains accurately measured and priced quantities. It provides detailed information on a project needed to complete a construction project. 

 

The primary purpose of a BOQ is for a client to have a simple and fair way of comparing contractor proposals.  

On large complex projects it can be difficult to compare proposals without providing contractors with set quantities of work to price. Tenders are submitted in a standardised format, with work and quantities needed clearly stated. Contractors calculate the cost of each item listed in the BOQ so that competing quotations can be easily compared by the client on a rate-for-rate basis. 

An accurate tender and cost estimate will improve the overall construction process. The bill of quantities defines the scope of work on a project, meaning less chance of misinterpretation or ambiguity through omissions. Work can be carried out without confrontation, resulting in a happy client.  

An accurate estimate also prevents waste, in time, money, or materials. The bill of quantities is an important connecting document between separate stages - from design through to project completion. 

Is It Easy To Create A Bill of Quantities On Excel?

Many construction companies use Excel when preparing their bill of quantities. Most businesses have Excel installed on their systems and have employees that are familiar with using the tool. 85% of the construction industry still use Excel for their finances and estimates.  

On the surface, a bill of quantities looks like a simple table format and so Excel seems like an obvious choice for creation. However, on large and complex projects with many subcontractors and stages, Excel spreadsheets may be pushed beyond their means.  

Let's look at some reasons why Excel might be used to create BOQs: 

• Free templates - Because of the seemingly simple table layout and standardised methods, there are many free templates available to download online. 
• Accessibility - Excel is a widely used tool throughout the construction industry. Most professionals have a basic knowledge of how to use Excel and is a less intimidating tool to use. There are also many free tutorials available online to be able to learn how to use Excel and how to produce BOQs. 
• Automation - Excel has many built-in formulas to assist people with calculations. Spreadsheets can be set up so that costs are automatically calculated. 
• Customisation - Excel is essentially a blank canvas. You can customise it to suit specific project requirements. BOQs can be formatted with any colour or font that you desire. 

Despite these benefits, there is no denying that creating a bill of quantities in an Excel spreadsheet is a time-consuming task.  

Users need a more advanced practical knowledge of the tool to get the most out of it, for example, keyboard shortcuts, lookup and reference functions, pivot tables and more. The output is only as good as the data input and depends heavily on having the correct formulas and avoiding input errors. 

How To Create A Bill of Quantities on Excel

A bill of quantities is usually prepared in the pre-construction phase of a project and includes several steps. They are created with a 'takeoff' process where construction work that can be measured and priced is identified. Bills of quantities follow a standard set of measurement rules outlined by the Royal Institution of Chartered Surveyors', called the New Rules of Measurement (NRM).   

Following a standard methodology allows for consistency and mitigates risk of disputes. The process for producing a bill of quantities on Excel includes: 

1. Obtain the drawings and specifications from the design team and take off the quantities from the drawings. 
2. Open a blank file and add in the project name, date and location. Write column headings including item numbers, description, unit of measurement, quantity, item rate, amount and any others you may need, such as notes. 
3. Split the work needed into appropriate sections, such as subcontractor packages, and have a separate tab for each section. This makes it easier to navigate the document as with large projects, there are usually many stages and separate contractors involved. For some projects, there will be a lot of tabs needed. A table of contents linking to the corresponding tabs may be necessary. 
4. Breakdown the work needed for each section into measurable items. Listing all the requirements from beginning to completion for each section is the most important and time-consuming part of the process. The descriptions of each item need to be concise but detailed enough for a contractor to understand exactly what is needed from them. The quantities and units need to be accurate and provide precise estimates of the contractors' responsibilities. It is easy at this stage to make mistakes and so a keen eye is needed to avoid any errors. 
5. Review all items and measurements. Excel will not highlight data input mistakes and so everything needs to be reviewed before sending to contractors to avoid disputes.  
6. Format your spreadsheets. Set formulas to automatically calculate costs. Edit the colours, borders, cell sizes, and page view so that the document is easy to understand. This stage of the process can take time if the person creating the bill of quantities is not familiar with Excel shortcuts, formulas and functions. 

 

Once these steps are completed the document can be saved and sent to contractors to fill out prices based on the quantities given.  

Many sources will be taken into consideration when deciding on rates and prices. Current rates, past projects, market rates, price libraries and many other criteria will be cross-referenced to achieve as accurate an estimate as possible.  

At this stage, if a contractor spots any missing items based on the drawings and specifications provided it is best practice to bring this to the client's attention. However, it may be seen as commercial advantage to withhold this information.  

Throughout the process there are many chances for human error and so each step needs to be carefully actioned and double-checked to avoid disputes, delays, or overruns.

What Are The Drawbacks of Using Excel to Create A Bill of Quantities? 

As mentioned previously, there are many chances for mistakes when creating a bill of quantities using Excel. Excel requires manual input, which often leads to human error. Over 88% of spreadsheets contain errors, and when dealing with cost-sensitive information this can have a huge impact on a project's financial performance. Errors can also lead to disputes where there are discrepancies between the BOQ and the drawings and specification. The client is held responsible for these mistakes and can result in claims for extensions or loss and expense. A few other major drawbacks with using Excel for BOQs are: 

• Lack of integration - Excel may not integrate with the various systems used throughout a project, causing data duplication or discrepancies. It becomes a difficult task to cross-reference data from multiple sources and makes it more likely details are missed. 
• Static data - The data in a spreadsheet is not updated in real-time making it out-of-date the moment it is complete.  
• Low visibility - A lack of visibility on past and present data limits the ability to forecast future opportunities. Not making use of the vast amounts of data produced each project hinders innovation and growth. 
• No specialisation - Excel is a general tool made for many purposes. It is not specific to the construction industry and doesn't take needs into account the needs of a construction project. 
• Time-consuming - Creating a BOQ in Excel is very time-consuming because of the manual data input necessary. There is not the same level of automation available as there is in specialised software. Wasting time putting together complicated spreadsheets could cause delays and missed opportunities. 
• Limited collaboration - Collaboration is limited when working on multiple files and disparate systems. Multiple versions of the same document are produced and changed without the core document being automatically updated. It can become difficult to track the various files and changes being made. 

 

These are just a few of the limitations when using Excel to prepare a bill of quantities. Excel may be a useful tool, but it is not the most efficient or effective option.  

It is always important to evaluate the specific needs and complexity of a project to determine whether a more specialised software would be better suited for the job. 

When Is Best To Use Excel To Create A Bill of Quantities?  

Excel can be the best tool to use for certain companies. For small to medium-sized projects where the quantity of items is limited then Excel is a good tool to create a top-level estimate.  

With a small quantity of items, the BOQ should be relatively quick and simple to put together on a spreadsheet. For larger and more complex projects, Excel does not have the automation or visibility to create accurate BOQs without taking a long amount of time. A few examples of when it is best to use Excel are: 

• Projects with a tight budget may want to use Excel, instead of more specialised higher investment software, to save on costs.  
• If you have a team of people that are highly proficient in Excel and know the necessary formulas and functions to create an accurate BOQ. Most people have a basic understanding of Excel and will feel more comfortable using a familiar product, but an advanced knowledge is needed for larger projects. 
• High levels of customisation are available with Excel, you can create templates and formulas specific to project requirements. This can be time-consuming to set up. It is important to note that software providers will listen to specific company needs and are often willing to cooperate.