Navigation:  »No topics above this level« Bill of Quantities (optional)

Use Case

The client may impose the estimate's layout and breakdown by supplying a Bill of Quantity. It is a document used for tendering within the construction industry, in which workforce, parts and materials (and their prices) are itemized. This enables a contractor to price the work for which the contractor is bidding.

There are different styles of BoQs, mainly the Elemental BoQ and the Trade Bill.

Typically, BoQs are prepared by quantity surveyors or building estimators. The cost of a building, structure or other project is estimated based on measurements. These measurements are used to create an estimate, with regard to, for example, the square footage of walls and roofs, the count of doors and windows, and systems such as heating, plumbing and electricity. Similar items (Tasks) are then grouped under one item (Branch).

 

To keep the customer standpoint (BoQ) updated when the estimate is edited, you must establish links between the BoQ in the one hand, and WBS on the other hand.

In some trades, the BoQ substitutes for the WBS; then you must establish ALL THE LINKS between the BoQ in the one hand, and the Minutes and Overhead on the other hand. That is why these minute rows or overhead cells are referred to as "minutes of the BoQ".

 

There are two ways to address the general case:

▪The one is in column mode and depicted in blue: only the empty columns are filled (thru links); refer to Conversion of a BoQ into an Estimate; supposedly, the others will not change

▪The other is in cell mode and depicted in red: it is described hereunder:

 

 

SP = Selling Price

NOTE In both ways, QDV7 can open the workbooks protected by a password. When the data of the estimate are fed back, QDV7 restores the password of the file.

 

Creation of the Frame

To create the workbook, refer to Calculation Options. This opens the tabs BoQ and Sheet3.

To create the frame, choose either method:

▪Right-click any cell, select Import B.o.Q and select an appropriate Excel™ file

▪Refer to How to Import Data; this pastes frame of another estimate; only the Description column is filled.

 

Description

A BoQ is a workbook similar to the Expanded WBS with chapters, branches, tasks, comments, options. They may differ in the column layout. The list of tasks can span across several sheets.

The blue cells originates in the Expanded WBS.

Check the box on the right to split the BoQ window into two vertical panes; the Expanded WBS is the default right pane. If the need arises, adjust the vertical border position.

NOTES

- The zoom slider in the right bottom corner affects the right pane

- The latest viewing mode (2-pane or 1-pane) is saved when you save the estimate.

 

Edition

To edit the workbook in Excel™, click Data>Excel>Edit under Microsoft Excel; this opens a file XLBOQ.xls. In Excel™, name the cells if needed.

 

To establish dynamic links between the BoQ cells and the cells of the WBS or the Overhead workbook, or the Minutes view:

1.Check the box in the lower right corner (spotted by the red arrow in the illustration below; alternative: View>Show/Hide flags>Show Bottom View; the circle around the pictogram shows the mode); this shows side-by-side the BoQ and a workbook containing the other sheets

2.To select the source sheet, click the Expanded WBS, Minutes or Overhead workbook button

3.Switch to Edit mode (Refer to Actions)

4.Select a source cell (the pointer turns to a hand)

5.Drag from the sheet to the BoQ target cell (empty or not, but in a regular row); the pointer turns to a sign +

6.Click Refresh Screen; the current value shows

7.If you tweak the formula, click "Compute all" to refresh

8.To delete the cell link and the value, right-click and select Reset cell(s).

 

NOTE Ranges can be dragged too.

The links between the BoQ cells and the cells of the WBS or the Overhead workbook, or the Minutes are based on Excel™ formulas.

The formulas MULTILANG() and NUM2WORDS() are allowed. Refer respectively to Multilingualism and Consumption of Variables. They may come from a conversion when the column is "Linked to BOQ".

 

Shown syntax in :

Example from the WBS: =GetDataFromWBSExtended("7FBC783FC2C83F3B9AE23E4DE4A2535C";"7")

Example from the Minutes view: =GetDataFromMinute("Description";"7FBC783FC2C83F3B9AE23E4DE4A2535C";"LineSellingTotalPrice";FALSE;FALSE;FALSE)+(D19*0.8/5)

i..e. GetDataFromMinute(FieldToCheck, ValueInFieldToCheck, FieldToSum, UseWBSQuantities, UseOptions, ShowGrouped)

Example from an Overhead sheet: =GetDataFromOverhead("WBS_12345678"); QDV7 generates the 8-digit number from any sheet of the Overhead workbook..

The Option #2 is activated if the Option #1 is TRUE.

NOTES

- If GetDataFromMinute results in #N/A in all cells, close the estimate, and reopen it; this may solve the issue.

- It is NOT recommend to use GetDataFromMinute() in an in-row workbook.

 

To spot the Tasks/Minutes whose at least one column is used as a source, create the special boolean field "Has reference in BOQ" and insert it in the WBS/Minutes view; these Tasks/Minutes feature a value of 1. If you try to delete (or overwrite whether by pasting, by glossary or by article/set with BOQ-referenced article) the source row, a warning message pops up. If you try to cut the source row from the WBS, another warning message pops up (pasting can restore the row).

As described in How to Create Free Special Columns, select Special columns>Has reference in BOQ; this creates a text field whose entry is not allowed.

QDV7 calculates the values when you click Refresh Screen.

NOTE Of course, all the duplicates of such Minutes if any (see Links Between Tasks) feature also a value of 1.

 

ALTERNATIVE FOR MINUTES and WBS

A value in the BoQ can be a value in the Minutes view, or the sum of values of identical Minutes.

Row(s) in the Minutes view can be defined by the value taken by a field that does not change as the estimate is updated. Costs and prices are not eligible; non numerical free fields are.

1.Select a BoQ cell, right-click and select "Formula Editor from Minutes"; the Field tab lists out these fields

2.Double click a field in the list (alternative: click + Insert); the formula included the column name =GetDataFromMinute("<Picked field ID>"; "";"";True;False;False)

3.Select the Value tab

4.To define the row(s) in the Minutes view: if the value is exactly a BoQ cell content, check the 'Reference is in Cell' box and enter the BoQ cell reference ($ is not required) in the combo

5.Otherwise, enter in the combo the desired value of the field selected in step #2  

6.Check the desired options (the 'Include the Overhead minute' option is TRUE by default, and shows in the formula only when FALSE)

7.Select the Sum tab; this includes the value in the formula

8.Double click in the list the field that defines the column in the Minutes view; this includes the field in the formula

9.Click OK and Refresh button; this fills the cell.  

 

If there are several matching rows and the Operand #2 is numerical, the formula sums their values. If the option "Use quantities from WBS" is checked in the Value tab, the sum is the overall sum.

To change an existing formula (green cell),  select Reset cell(s), invoke the editor, erase the formula, click several times in the formula bar until the list of fields get accessible.

Summary:

 

This is exemplified in the sample estimate named Car, where the "What in BOQ" free field is used, in place of Guid, to determine the row(s). In addition, the studies price is distributed across the BoQ items using percentages; this part is added at the end of the formula.

NOTE When you use the formula editor, the "Has reference in BOQ" column (see above) is NOT filled.

 

For the WBS, the procedure is similar thru Formula Editor from WBS.

SUMMARY

 

MINUTES OF THE BoQ

Viewing

To retrieve the source cell of a BoQ colored cell, right-click a BoQ cell and select Show source of cell; this opens the second pane on the Expanded WBS/Minutes/Overhead workbook, and shifts the focus to the source cell. Alternative if the source cell is in the WBS: display the second vertical pane and click the BoQ cell.

To access the Minutes view, select a colored cell, then select Show minute (Alternatives: press F4 or "Minutes" special button, double-click the cell):

▪If the cell source is in the WBS, this opens the related section of minute in the Minutes tab

▪If the cell source is in the Minutes view, this opens the source minute apart in the Minutes tab

▪If the cell source is in the Overhead workbook, this opens the corresponding sheet and select the source cell.

 

To revert to the BoQ, press F4 or the button BoQ.

 

Exchanges with Excel™

▪Thru Data>Microsoft Excel>Excel>Export without formula, QDV7 creates a copy for the customer

▪Thru Data>Microsoft Excel>Import from Excel (alternative: right-click and select Import BoQ), QDV7 imports the original file.

 

Summary

 

 

To append a unit to numerical column values (e.g., lb), you can, in Excel, give the cells a custom format; the dialog box is the same as in Cell Format.  

 

Methods