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.