BILLING WITH MS EXCEL (V-1)

For Construction billing of buildings

Many ERP Solutions Require.

1.      Tower wise data (Required).
2.     Unit wise data (Required but may be relaxed )

Additionally the best billing practices (Procedure) require the process to be:

1.      Simple & easy to work with and understand.
2. Transparent and Electronically Audit-able.
3.The important data such as BOQ item rates and material coefficients etc. should be tamper-proof.

HOW EXCEL CAN HELP?

A few of Excel tricks can make you achieve all the above and more. This tutorial shall help you to achieve the best construction billing practices which are essential for effective SAP implementation and utilization of the data for project related time and cost analysis for Project Monitoring & Control.
The good news is that you need not change your computer hardware or billing engineer though faster computer would help. It is assumed here that your billing engineer is already preparing the bills using Excel 2007 or 2010.

SKILL-1: NESTED IFS 

To be sure that the calculations are absolutely correct, it is essential that the computer operator is not required or authorized to fill calculation formulas. The practice of filling formulas for working out the quantities is tedious, unnecessary and makes the billing error prone and devious.
Nested ifs function enables you to do all calculations using only one formula.
In the illustration below, the quantities are worked out based on the unit of the item.


The formula in the formula bar below calculates the appropriate quantity based on the item’s unit.
“=ROUND
(IF([@Unit]="Cum",[@[No.]]*[@[L_Meters]]*[@[B_Meters]]*[@[H_Meters]],
IF([@Unit]="Sqm",[@[No.]]*[@[L_Meters]]*[@[B_Meters]],
IF([@Unit]="Rmt",[@[No.]]*[@[L_Meters]],
IF([@Unit]="Each",[@[No.]],
IF([@Unit]="KG",[@[No.]],
IF([@Unit]="MT",[@[No.]],0))))))),3)”

The formula further rounds up (shown in red colour above) the calculation to only the three decimal places (A SAP requirement).

For your bills, you need not reinvent the wheel and the above formula may be copied and pasted. But don’t forget to copy the column labels also. In fact it may be easier to copy the whole sheet from the Excel training file and then delete the measurement portion.  

 SKILL-2: DATA VALIDATION 

 


For above automation to succeed, it is imperative that the units are not typed and are looked up from a standardized list. For example if the units above for volume calculation is filled as Cum. instead of standardized Cum, an error message shall be displayed instead.

Please refer to the Validation sheet of the bill file provided for your training exercises.

For example, a name “Towers” is created from the selected cells below (i.e. CT_1,CT_2,CT_3,CT_4,CT_5,). Similarly the name for “Floor” & “Unit” are created by following the simple Skills below.


  1. Select the cells for which you want to create name with the wished-for one at the top. (In the example the Towers are at the top of the cell selection.)



2   2.Create Names from Selection dialogue box is available in the formula tab of the Ribbon.
3.   Further you need to define the name BOQ_Code for your project.
 4.You can view and edit the names in the Name manager tab as may be required      when new items are approved by the competent authority.

In the BOQ the units of an item and rates are defined and it would be desirable that the computer picks up these values from the authorized BOQ. 

  SKILL-3: PICKING UP THE ITEM SHORTTEXT AND THE UNITS WITH VLOOKUP.
   The VLOOKUP Function looks for a value in the left most column of a table and then returns a value in the same row from a column you specify. In the example we have named $A$2: $H$635 as of the SAPCODE sheet as BOQCODE.


The formula =VLOOKUP(D3,BOQCODE,3,0) in the E3 cell above works as below.
    1.VLOOKUP(D3,BOQCODE,3,0) : Lookup the value in the cell D3 (Refer the screenshot above)
    2.VLOOKUP(D3,BOQCODE,3,0) : Lookup in the range named as BOQCODE
3  3.VLOOKUP(D3,BOQCODE,3,0) : Lookup in the 3rd column of  BOQCODE range against the value in    the cell D3. 
4 4.VLOOKUP(D3,BOQCODE,3,0) : Lookup for the value only if the value exactly matches the one for which lookup has been asked for.
     The VLOOKUP function shall be required again when you need to look up the REFNOs if your billing requires the abstract to be sorted as per REFNOs. It has been done in the bill training workbook. 



SKILL-3: Hiding Sensitive data and workbook protection.



The sheets with item rates & cement and other material consumption coefficients etc. should not be accessible to the contractor. This is done as below.


Step-1 Hiding the sheet: Right Click on the sheet tab to be hidden and click on the Hide option (Refer screenshot below).






Step-2 Protect the work book: After you have hidden all the sensitive worksheets, protect the workbook structure. (Refer screenshot on the right). Password should be provided by the authorized representative of the company. The step -2 may be taken just before providing the worksheet to the agency by the authorized official. 



SKILL-4: Protecting the sensitive information on the measurement sheet: The contractor is further restricted to the entry of only the relevant measurements.


Step-1: Select the cells to be locked i.e. the Cells with quantity calculation formulas and data in the short text and unit fields where the entry is being looked up from hidden and protected sheets. Click on the protection tab of the format cells dialogue box 1. Check the lock option box 2. However to make this lock operational, you need to protect the worksheet.





Comments

Popular posts from this blog

Using Excel Solver Feature to analyze and optimize the Steel Truss Design.

Using Excel conditional formatting for comparative statements

Simple Visualization of Sun,facade, Window and Shade