Using Excel Power query for Construction Billing & Data Mining

 This blog published automating construction billing during 2013. It is tried and tested and customises, nested ifs, data validation, Vlookup and workbook protection skills to meet the accounting department's requirements. Excel since then has transformed into analyzing and visualizing the big data. Construction projects have also transformed. 2013 was the time when the dashboard became fashionable, it served the purpose. Now is the time for low code, no-code development  — dashboard remains in the front but the real value and not just automating a semi-manual billing that was inherited; what present-day site monitoring and management requirements are much beyond. Construction quality and safety as demanded today can be met by freeing the site engineers from all possible non-related activities. And meeting the requirements of construction data mining. 

Excel Basics for Construction Professionals

The following blog and attachments are on an Excel Workbook that I use for my introductory classroom sessions on Excel for Construction Professionals. In a time when social distancing is prescribed, it would be better if the tool makes one learn on its own. The present one still requires a class supplementation, and I need your perspective on making it more useful.

ANGAN- Till when we would remain obsessed with refrigerants, air conditioners and high rise?

Following professional conferences, I used to write my reflections to my subject matter expert friends. This time, I felt like writing it on my blog. Climate emergency is not limited to my professional friends; it affects everyone. ANGAN (Augmenting Nature by Green Affordable New-Habitat) an International Conference organised by Bureau of Energy and giz during 9-11th September 2019 was a such an event.

Project Scenarios-What if Analysis

Project conception and monitoring involve estimation risks. Estimates are uncertain. Expert judgement is used to quantify these.  However hard we plan, there would always be some random variations. Let us explore the “what if” in the forecast group at data tab.  Scenario manager here is helpful in creating various project (probable) outcomes.   This concept is best understood using an example. Click  here  to download the workbook. You need to solve it to get a grip over it. Do the followings. Use “Goal seek” to create the various break-even quantities Use “data table” to vary two variables at a time Create a “Scenario Summary” with most likely, Optimistic and Pessimistic quantities.    Run Monte Carlo simulation. These tricks allow great possibilities to a problem solver. Your comments to simplify and further explore it are welcome.

Using Excel's Forecast Sheet Feature for Construction & Life Cycle Parameters

Sustainability requires that we think and act in terms of a  circular economy . ...yes massive inertia and complexity of the construction industry means that the way is very long. One step at a time would however make it happen. We can begin with the Life Cycle Costing of Buildings. This requires us to update both our knowledge base and skill sets. Many iterative cycles would be required for maturity but this should not deter us in making a small beginning. Specially when we have such intuitive features like forecasting sheet readily available in the newer version of Excel that industry uses day in and day out. Construction industry generates lots of data most of which is not used. What is usually used is for litigation and contract administration actions. Much of it is picked and chosen to make or break a case. With new data analysis features, making good use of Construction and maintenance data is easy. Appropriate databases may automatically be made to provide useful insights in

Simple Visualization of Sun,facade, Window and Shade

The raison d'etre of using Excel for designing/ decision making is that the solution is not a black box and professionals besides relating to basic principles can play with it through and create scenarios. Transparency of formulas and correlations when supported with basic visualizations however can authenticate decision making. Seeing is believing. Optimal solar passive design requires varying requirements.

Revising Criteria weights using Excel Solver

Technological and other changes are forcing the organizations to quickly modify their project design requirements. For example, successful introduction of new technologies in the construction industry would require a fundamental change in which the preliminary cost and schedule estimates are made. Changes in the weights of various inputs that go in the preliminary estimate becomes an immediate requirement. Design cycle improvements require training teams interactively and     process inputs improvement. Project stakeholders demand improvements in the deliverables and justifications of enhancements in the first costs even if the life cycle costing of the habitat goes down. Similarly, the technique illustrated in the solution may be used in changing indicator weights for as needed. (The improvements and user demand would also push the green building norms and other sustainability indicators).