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.
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.
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.
- 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 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.
So, don’t depend on tactic too much quantity of} since roulette’s a game of chance. En Prison– This rule’s much like la partage, besides half of the player’s stake stays on the 코인카지노 desk here. The tern can translate to ‘in prison’ in English for the reason that} player’s stake is not returned to them.
ReplyDeleteWe advise beginners learning to play video poker to kick off their on-line play betmove with this variant. That’s outcome of|as a outcome of} it has a extra straightforward paytable comparability with} the opposite variations. In addition, it’s a fan favourite, thus being played by most gamblers.
ReplyDelete