Monday, May 12, 2014

Introduction

Excel is powerful!
Excel is fun!
I have good news for those who work in a construction business and like to work with Excel. You can for free use my Excel workbooks with VBA subroutines for your needs. These workbooks are the software for:
1.      Estimating;
2.      Resource planning;
3.      Purchase orders;
4.      Expense tracking;
5.      Clients invoicing;
6.      Projects financial analysis;
7.      Company budget.
The workbooks with VBA subroutines and auxiliary files (templates, descriptions and etc.) you can download from my public OneDrive folder Excel For Construction  ( http://1drv.ms/1iJADg7 ). Videos in my YouTube channel (https://www.youtube.com/channel/UCPSjuRxv2fsk4WHAd4bn7Ww/videos ) will help you to understand the ideas of the software and, in the case when you will decide to use this software, these videos set will be the valuable assistant in your daily work.  You can post your comments below the videos. When you will need an additional information from me or will have proposals, write e-mail, my address is excelforconstruction@gmail.com . I constantly work on improving this software and your notes will help me to make it better.  Thank you in advance.
I am grateful to my leaders, co-workers and other people which whom I collaborated, for their ideas and notes according which this software is developed. I am also grateful to those people who uploaded a lot of material into internet about MS Excel and VBA. I am able to use it for free and it helps me to create my workbooks and subroutines.  I hope that my material will helps you to solve daily problems and to raise the construction business in which you work into the higher level, and also will help to improve your personal skills.


More details about the ideas of this software are in the post below – Details.

Details

During my career in the construction companies I faced with these problems:
1.      It is very difficult and take a lot of time to compare included in an estimate resources with real expenses in accounting;
2.      It is impossible from an estimate directly calculate resource plan and prepare request of quotation for suppliers;
3.      Cost of many items in available external cost databases have such a big difference from really these items cost for company, that it is impossible to use these external cost databases for estimation and it is necessary to create own cost data base;
4.      It is impossible effectively create an own cost data base without tools for data accumulation and calculation;
5.      In most items quantity calculation formulas one of the parameters is area or length of element (example a wall) in which item is included  and becomes obviously that the best solution is to arrange data so that when area or length of element is entered all items, which are included in this element, quantities will be recalculated automatically.  
6.      It becomes obviously that it is impossible to work further without solutions and tools which accelerate and automate a work, because when own cost data base constantly grows, it takes more and more time to find a required item, include it into an estimate and to update cost of items according new data.
7.      It is impossible to automate resource planning and price update without items names in estimate and in accounting or suppliers proposals relation;
8.      It impossible to calculate item rates from accounting and suppliers data with using only one measurement unit for item, because some items are rationed in one unit and are purchased in another unit and even purchase units  can be different for the same item – for example insulation are rationing in area units (quantity of insulation in element which is calculated automatically from element area parameter will be in area units) but in suppliers invoices insulation quantity can be in volume units or sometimes even can be in packs;
9.      Loss or risk (contingency) must be evaluated for estimate accuracy and becomes obviously that the best solution is to have possibility evaluate different contingency percentage for every estimate row and to have possibility quickly change these percentage;
10.  During an estimating and a project execution becomes very hard to communicate with other team members and a project owner, if every estimate row haven’t relation with some document which is available and easy understandable for everyone  of mentioned persons;
11.  During a project execution some items a project owner can buy directly from other suppliers and must be created a tool which allows to track these items monetary value and then correctly to deduct it from an agreed in contract amount;
12.  During a project execution a project owner may want to change something and must be created a tool which allows to track these changes;
13.  During a project execution, when constructional decisions are reconciled with a project owner, can be created a new more detailed estimate or even several  more detailed estimates and must be created a tool which allows to work with an actual estimate and also allows to compare these estimates versions with each other and with real expenses;
14.  In some cases items are purchased for one project and used in another and for this reason must be created a tool which allows for every item in estimate evaluate is it already purchased and also is it already used;
15.  During a project execution arises different problems if every estimate row haven’t relation with  a supplier document, an issued for client invoice or some other objects which are very specific for company;
16.  It is impossible with company accounting system according company needs manage purchase orders;
17.  It is impossible with company accounting system according company needs to issue invoices for clients and  to manage information about these invoices and payments for them;
18.  It is impossible with company accounting system according company needs to create reports about projects financial status and to form company budget for the future period.
Do you also face with these or part of these problems?
If yes, my software can help you to solve them.
The solution which allows accelerating and automating a work and also creates relation between data is this – most of data are not in separate sheets but they are in one sheet with many columns. This sheet name is “Master data” and here data are arranged like in a database. In this sheet also are many columns in which are formulas for calculations. The columns with data and formulas are arranged and grouped in some logical sequence which helps for work. When main data are in one sheet and arranged like a database, it opens the possibility to use power of Excel filters, pivot tables and other Excel tools, it also opens the possibility easier to create automation tools with Excel VBA and do these tools more effective.
Don’t be afraid of quantity of columns in “Master data” sheet. In Excel is possible to hide unnecessary for the moment columns and also in Excel View tab are other excellent tools: Split, Freeze Panes, Custom views and Full Screen. These Excel tools and my macros, which I created for this purpose, will help you to have on your screen view what you will need for the moment. Of course, work with these workbooks is easier with large screen and additional monitor which allow working in the extended mode – computer monitor plus projector (projector in this case is additional big screen monitor). Finally,  if you need an additional rearrangement of columns or other software adaptation to your needs or market, if you haven't enough experience or time to enter data and generate reports and need this service, write to me email (address: excelforconstruction@gmail.com ).
Capacity of a memory and a speed of modern computers allow working with huge Excel workbooks. If during your work process in some workbooks you will accumulate such a huge amount of data that will be impossible to work effectively and you will need solution of this problem, write to me email.
Some of my workbooks have relation with each other or can exchange data with each other. In my job I also upload data into some workbooks from company accounting system. If you also need to upload data from your accounting system, write to me email.
The workbooks which you can download from my public OneDrive folder Excel For Construction are grouped in sets according initial purpose of workbooks and every set has separate folder – as example folder Estimate Module.
These workbooks are for those who like work with Excel and have background of Excel knowledge. If something related with Excel will be unclear, you can find a lot of material in internet about it. I suppose that with the help of this material about Excel, my workbooks, your creativity and, if you will need, my help, you will create a powerful tool which will do your work more efficiently.

Good luck!