Using Excel conditional formatting for comparative statements

Project Control: Using Excel Conditional Formatting for guiding rate negotiations in Comparative Statement.

Project control requires that the various work packages are carried out as planned. Civil construction is typically based on approved building plan and specifications. In view of progressive elaboration of project, Item rate tenders are most prevalent and provide the transparency and flexibility to both the owner and contractor.

Projects are always mired with some uncertainty and items and their quantities often deviate. Various known and unknown factors affect the prices quoted by a contractor. Success of the project greatly depend on the Project’s ability to negotiate the contracts at a mutually win-win cost, performance and time frame even in the face of uncertainties.

Practice of defining a Project/ work package through a Priority matrix is a handy tool for exploring on the negotiation possibilities.

	Time	Performance	Cost
Constrain			X
Enhance	X	X
Accept

Figure-1 Project/ work Priority Matrix

Once the overall work requirements priorities are defined, the Project manager has to look into the organizational work culture. For example, in most public works where transparency is of prime importance negotiations are tricky and the PM is restricted to award only the lowest vendor. Even when restricted to negotiate, the rates are to be analysed for reasonability and marked for limiting the quantity deviations to a certain absurdly high and low rated items.

In informal sectors depending on the priority matrix the indicative requirements might be differently defined for indicative marking through conditional formatting.

While the contractor’s sole purpose may be to maximize profit, the project manager’s decision making may not be as simple. Once the work is awarded, the contractor becomes one of the key stakeholder and a nice balance of rates and managing quantities for absurdly high or low rated items becomes important.

  Whereas using excel for instantly creating comparative statement while opening tenders is easy and straight forward additional conditional formatting addressing the defied indicative requirements may also provide insights into reasonability and possible negotiations leading to a better project control.

Problem statement: To create comparative statement instantly along with indicative insights into rate negotiations in an item rate tender.

The first step is to set up the worksheet in advance using formula and conditional formatting by protecting the worksheet and leaving only the rate columns for various contractors and other predefined indicative parameters open. For marking the negotiation indicators, the Project team has to do their homework prior to tender opening i.e. defining the work priorities as per the priority matrix and rate justification analysis.   

For this you uncheck the Locked default option in the protection tab of the format cells dialogue box and then protect the sheet.  

 In the sample workbook which can be downloaded by clicking the link , the rates of the contractors, the absurd defining limits and the number of the highest value items that you want to highlight have been kept open, the rest of the worksheet have been locked to ward of any inadvertent changes.

            Step-1: Uncheck the locked cells                           

Step-2 : Protect Sheet

          

These steps come after you have set the required formula and the anticipated conditional formatting as once you have protected the sheet you would only be able to fill in the cells which have been designated to be filled.

With the above the worksheet would be ready for data entry during the tender opening process. The comparative along with the indicators for project control guidance would be ready as soon as the rates of all the contractors are filled.

The conditional format has been set in the sample worksheet for

1.   1  To define the absurdity limit which highlights the absurd high and low rated items. This is a routine which is required for ensuring that the contractor does not draw undue advantage by somehow deviating quantities in his favor. In public procurement one needs to ensure that the L-1 contractor remains the L-1 at the time of contract closure as well. The conditional formatting has accordingly been set to mark such rates.
2.      You can also see the top valued items by filling up the number of such items that you want to highlight. This might be important for project cost control.

 

 The above figure shows the conditional formatting that have been set for the table of the comparative statement. LARGE function has been used for marking the high valued items.

    The L-1 to L-5 have been marked using the SMALL function as shown in the figure below. 

Formula in the cell H4 which has been copied till P4 thus reads

=IF(H$5=SMALL($H$5:$SP$5,1),"L_1",IF(H$5=SMALL($H$5:$SP$5,2),"L_2",IF(H$5=SMALL($H$5:$SP$5,3),"L_3",IF(H$5=SMALL($H$5:$SP$5,4),"L_5","Check your data Entry"))))

L-1 and L-2 are also marked using the conditional formatting using the top and bottom values.

The final comparative would look as below.