Preparing your Bill of Quantities for E-Tendering

Details

Last Updated on Monday, 14 May 2012 07:13

Written by Suranga Jayasena

It made me to write this bit of information due to question raised at the Public Seminar on 5^th January by Dr. Bingunath Ingirige; how to protect a BoQ from bidders changing descriptions.

I would take the above question as “how to protect the contents while allowing the bidders to change only the permitted cells”, assuming that we are talking about using MS Excel (2007 version) to prepare BoQs. Here’s how to do that.

Prepare you BoQ in MS Excel as you would normally do. Make sure all the calculations are done by using excel formulae (equations). For example, get the Amount figures by multiplication of Qty and Rate, Total by SUM, Summary sheet figures by reference; so that when you enter the rates and quantities (and provisional sums where applicable), you get the final figure (contract price) automatically.
 

Important: Save your workbook with a new name. For example “filename – protected. xlsx".
 

Now, we are going to unlock the allowable cells. What we want to do is allow bidders only to select and edit (i.e. enter figures) to rate cells in each work item. We don’t want them to edit the Qty or Amount cells (or any other cells). It is better you start doing this with your own rates included to see that all calculations are correct.
 

Whenever you open a new workbook in Excel, all the cells are ‘locked’ by default. However, this status is not activated unless and until you enable (switch on) protection. While it may not necessary, just to make sure, let’s lock all the cells first. Select all the cells in your BoQ worksheet (Hint: you can do this by clicking on small arrowhead at the top left corner of your worksheet where column and row headings meet). Now right-click on the worksheet and select “Format Cells...” from the popup menu. A new dialog box opens. Select the “Protection” tab. Tick in front of “Locked”. Note that, it would be already ticked if you had not changed this for any cell before. Click “OK”. Repeat this for all worksheets.
 

Now, select only the cells you want your bidders to change. These will be the rates for work items, and if you want to allow inserting sums (such as in preliminaries) directly in Amount columns, those cells too. To select multiple cells, you can click on the cells while ‘Ctrl’ key is pressed. To avoid mistakes, do this for manageable number of cells at a time. Right-click on one of the cells selected and go to “Format Cells...” and “Protection” tab as mentioned above. Remove the tick in front of “Locked”. Click “OK”. Repeat this for all relevant cells, including the cells insert daywork rates, profit and discount rates. See Figure 1 for an illustration.
 

Figure 1
 

In the excel main menu, go to “Review” tab and the Review Ribbon will open. Click on “Protect Sheet”.  A dialog box will open. Make sure “Protect worksheet and contents of locked cells” is ticked. From list of “Allow all users of this worksheet to:” tick only “Select unlocked cells” and keep all other cleared. See Figure 2. In the “Password to unprotect sheet:” enter a password. A good password would be a combination of letters (small and caps), numbers and symbols. Do not use any standard words or name. Here is an example of a good password: ”PexLo26$9”. Click “OK” when you are done.  Re-enter the password. Repeat this for all the worksheets you want to protect, including the summary page.
 

Figure 2
 

Now, clear all your own rates and sums. You will notice that you are not allowed to click on other cells, and so won't your bidders.
 

Save the file.
 

Congratulations...!       You are ready for e-tendering.
 

Note: You can unprotect the sheet by using the password from the "Review" tab whenever you want to make changes to other content.

This email address is being protected from spambots. You need JavaScript enabled to view it. so that we can improve to make this more useful for everybody.

Comments on Feedback

My thanks to Buddhika who pointed out that there are many tools available on the Internet to retrieve (hack) the password and some bidders have then played around the quantities. This is clearly cheating, and the client can seek contractual remedy for that. However, this would not occur if we test for this at the tender evaluation. What you can do is, copy-paste only the "Rate" column from the bidders submission to your own excel BoQ. Then, you can compare your "Amount" column with that of the bidder's submission. For easy comparison, you can "copy > paste special > values "the bidder's Amount column next to your Amount column. You need not to do this for all the bids, but only for the selected bid. If found cheating, you can either go for the next lowest or take any corrective action.