Construction Billing with Excel Update

MS Excel has been used for construction billing from the very beginning of its launch.

Excel 2007 with the introduction of Pivot tables made calculations like abstracts appear like magic. Other calculations like theoretical cement consumption etc. were also possible and yet total automation was not possible. Power Query and Power Pivot an addon to Excel 2010 were initially inaccessible have come of age now.

The users of this blog would like to visit "Billing with MS Excel (V-1)" to see the possibilities of using pre-2013 Excel versions.

The new data capabilities make Excel a gamechanger for billing and similar applications. Please look at the possibilities of this tutorial.





All calculations that were semiautomated till now, are now done just by adding a file for the newer bill into the folder and updating the calculations. Yes, that is all for your billing team to do. The outcomes shown above is now as simple as updating the tables and refreshing the Pivot tables.

Why not consider exploiting this technology for project management related decisions as well?

Very good material on learning Excel skills is available on blogs and on Youtube. This blog addresses your billing and Project management issues for the Construction Industry perspective.

Those who want to download and try on their own which is recommended for the full understanding can download the folder named as BILLING-II.

1.       Download the BILLING-II folder

2.      Open a new excel file and name it AshutoshpBilling-II (Or you can have your own name)


3.      Go to Data tab,

                                      i.      Get data

                                    ii.      From File

                                   iii.     From Folder


4.      Give the folder path of Bills folder

5.      The following screen would open


 6.      On the bottom side of the same screen, the following options are seen.


7.       Open the combine tab and choose Combine & Transform Data



8.     
Excel automatically detects the first Bill as a sample file.


9.      Choose data table and tick mark the “Skip files with errors” checkbox.




10.  On the right side “Query Settings” records each step that has been taken in the process.


11.    Filter all files starting with Bill. Other files if kept in the folder would be deselected.



12.   Now the .xlsx suffix is unnecessary repeat in the source names and thus substituted.

a.       Select Source.Name Column and right click.


b.      Find .xlsx and replace it with a blank 



The query steps are shown below.


 

13.   In the File Menue choose Close and Load To...



In the Import Data window choose “Only Create Connection” and check on the “Add this data into Data Model”





14.   Now Get the data from the “Theoretical Workbook”



Choose the Theoretical.xlsx work book



Load the DAR worksheet


Choose “Only Create connection” and Add this data to the “Data Model”


The records from the Bills and DAR queries are loaded.


Go to the Power Pivot tab.



15.   Create connections between the two by clicking and dragging on the item tab from Bills to DAR


Now


 

 

 

 


Now


 

Now Create reports like you would create from a Pivot table.


 

Comments

  1. Looks good! I have tow things to add. First there are large number of off the shelf software already available which are integrated in the ERP solutions being provided by various firms. The advantage of these are that they have already been de-bugged and can be integrated with progress reports-DLR, DPR etc. and can flow seamlessly to the higher management, accounts, finance etc. Second point is that in smaller firms billing is done by people who are not very highly qualified so they need simple excel files which can be handled by them.
    having worked on billing from recording measurements, writing MBs, to doing test checks, to doing actual billing etc. I know the importance of this activity and how it can lead to better management of projects.
    There are large number of 'Cost Management Consultants' around who can examine this and/or adopt it Try them out.
    Let me also say that with BIM and software like Revit billing can be, in my opinion, really simplified and automated and integrated with PM software. Try and see how this works.
    Good luck. Stay safe.

    ReplyDelete

Post a Comment

Popular posts from this blog

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

Using Excel Power query for Construction Billing & Data Mining

BILLING WITH MS EXCEL (V-1)