Using Computer Software
1. E-mail address book
- Enter the names of all persons in the e-mail address book in the following format:
- ABC - Name
- ABC = short name of company; and
- Name = name of person.
2. BQ production software
- With the introduction of computers, people wrote programmes and software to help taking-off, billing and printing BQ.
- Standard phraseology complete with codes was devised to standardize the descriptions. Standard phraseology is presented in small tokens which are used to build up full descriptions. Here came the style of descriptions described in the early part of this Manual. The codes are used for selection from menu on screen and for automatic sequencing.
- Some BQ production software allows free style of descriptions, but this would require manual sequencing.
- Early BQ production software could not help much about taking-off, dimensions were still required to be entered into the computer screen one by one. Some early users, afraid of computers and making mistakes, took-off manually first and then entered the dimensions into the computer screen one by one, some others simply entered the grand total for billing purposes only.
- BQ production software does help squaring, transferring and billing, no more abstracting, transferring, working up, cut and shuffle, though some sequencing is required.
- The taking-off screens given generally follow the dimension sheets. Some resemble the traditional dimension sheets with vertically stacked dimensions faithfully, even with the same old timesing and dotting styles. Some resemble the tabular dimension sheets with horizontally laid –out dimension columns, plus additional columns for timesing using the normal computer symbols "*" for multiplication, "+" for addition, and "/" for division instead of timesing.
- Before the popularity of Windows, the widths of computer screens were generally limited to 80 characters, and the screens were monochrome. Taking-off schedules which require a lot of columns or colours were not practicable.
- Early BQ production software required mini-computers.
3. Microsoft Windows and Excel
- With the popularity of personal computers and Windows and the increased capability of computer hardware or software at decreased or steady prices in spite of inflation, BQ production software has been more powerful, flexible and user-friendly. Computerised taking-off schedules are feasible.
- On the other hand, the capability of Excel spreadsheet software has increased so much that it is now easily feasible to use Excel to take-off quantities and produce BQ without the need for BQ production software.
- Excel in tabular form lends it to a good taking-off tool. An example of measurement schedule is as follows:
- The above form seems to resemble the dimension sheets more than taking-off schedules. The manual type of taking-off schedule described earlier, though can be resembled by Excel, is still not practicable because there would still be too many columns which would require too frequent horizontal scrolling of the Excel spreadsheet in order to see them. Furthermore, colouring in a box requires too many keys and adding up boxes of the same colour requires some advanced skill in using Excel macros or Visual Basic for Applications.
- The tricks to solve the above problem are as follows:
- Instead of using a lot of columns or colours, a "Code" column in conjunction with the "vlookup" function of Excel are used.
- Another Excel worksheet is used to build up a definition table of Code, Description (Definitions) and Unit.
- By means of the vlookup function in the Description and Unit columns in the measurement schedule, the description and unit can be retrieved from the definition table using the Code entered in the measurement schedule to search the same Code in the definition table.
- The Quantity column in the measurement schedule is calculated as the product of dim1 to dim3 and times1 to times2.
- Using a combination of the Excel functions of sum() and if(), quantities of the same code can be summed up and brought forward to the Quantity column in the definition table. The quantity column in the Definition table is then the total quantity.
- By using codes defined by the Taker-off, the Taker-off can take-off several items in an inter-mixed manner but still can present them in an inter-related logical sequence. Information, dimensions and quantities can be set out in a systematic manner. The benefits of taking-off schedules are retained. Furthermore, the capability of Excel to do cut, paste, copy and delete can help duplicate similar dimensions for different items of work.
- The definition table actually resembles a BQ. In the simplest form, a measurement table and a definition table can also produce a BQ.
- A lot of measurement share the same set of dimensions. For example, quantities of tile equal to the quantities of screed. Area of slab formwork (before deduction of beams and others) equals area of concrete slab. Therefore, instead of duplicating the same set of dimensions, the same basic total can be transferred once to the tile item and once to the screed item. Similarly the same area can be transferred once to the slab formwork item and once to the concrete slab item subject to multiplication by the slab thickness. Further adjustments to the slab formwork can be done by another set of dimensions. This concept can be achieved by having a secondary table to transform the total quantities in the definition table to another set of quantities which are then transferred to the BQ. The principle is in fact the same as that for the measurement schedule and definition table, but a different set of codes may be required.
4. Intelligent use of Excel
- Excel should not be used just to resemble the manual method. Excel should be used intelligently. Using Excel effectively to produce BQ does not require advanced programming skill but only requires some knowledge of some useful functions provided by Excel. Advanced users knowledgeable in Visual Basic for Applications can of course explore greater capability.
5. Drawbacks of Excel
- A drawback of using Excel is lack of side-casts. Side-casts can be embedded as a build-up of a dimension in a cell of Excel but cannot be readily seen unless the cursor is on the cell and the embedded build-up does not have annotation. A method to deal with this is to enter more lines of dimensions instead of building up into one total dimension, and give the annotation in the location column.
- Another drawback of using Excel is that for long string of text within a cell, the last line may get truncated. This is not apparent on screen but will appear on printed copy. Care should therefore be exercised to check that there are no truncated words, otherwise an additional line break should be inserted at the end within the cell to increase the height of the row of cells to show the missing words.
- Another important point of using Excel is about the range of the Sum() function. The function is very useful for summing up total, but the effect of insertion before the summation cell should be carefully noted.
- For example, in Column A below, the cells shaded represent the cells to be summed up. If a row is inserted between the shaded cells, the range summed up will be automatically adjusted to include the cell as shown in Column B below.
- However, if a line is inserted immediately before the top of the range or immediately before the summation cell, the range will not be automatically adjusted to include the inserted cell. The inserted cell is outside the range summed up as shaded in Column C below, and the value will not be added to the sum. The formula in the summation cell has not been adjusted to include the insertion.
- To avoid people tampering with the formula of the summation cell, a better idea is to introduce two narrow rows at the beginning and at the end of the range of cells to be summed up as Column D above. They are narrow so that no data is supposed to be entered into these narrow rows. Any intended insertion of row will naturally be done between the two narrow rows and will be included in the range to be summed up. No adjustment of formula will be necessary.
7. Counter-checking totals and transfers
- Formulae entered in Excel may be carelessly invalidated by the users. Therefore, mechanism such as alternatively calculated totals should be used to counter-check totals and transfers to ensure formulae are properly set and remain intact.
- Counter-check mechanism should also be devised for BQ production software.
- Complete trust on calculations by computer can end up with disastrous errors.
8. Word processing software
- Because of their lack of calculation capability, word processing software should not be used for typing BQ, though it is feature rich in formatting.
9. Computer aided taking-off
- Some BQ production software adopts digitisers to help the taking-off side of BQ production.
- Development has also been in progress to integrate with CAD software to generate quantities directly from CAD Drawings.
- Generating quantities from CAD Drawings are also an established technology. However, how the quantities can be usefully classified and itemised for pricing would still require input beyond the normal knowledge of the average draftsmen, and experienced Takers-off would still be required.