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!
