Microsoft® Excel and Access as Estimating Tools for SMEs
How to use Microsoft® Excel and Access to produce quickly estimates that are carefully and fully considered and to which, if accepted, lead to work you’ll bring in on target.
Last updated on 2021-05-29 by David Wallis.
Off-the-Shelf Estimating Software Applications for the SME
Estimating is a tough activity to computerise. That’s because there are so many variables to be taken into account and no fixed pattern against which to match them.
Hence commercially available estimating packages tend to be expensive and targeted at large companies, often on big-budget projects in specific sectors, like construction and civil engineering.
For the SME, finding an off-the-shelf estimating package that relates to their activity, with a sufficiently close fit and to a budget, can be difficult. Learning that software and adapting it to make a truly close fit, can be time-consuming and expensive.
So what are the alternatives to off-the-shelf packages?
Microsoft® Excel as a Starting Point
Most clients I have worked with started using Microsoft® Excel as their estimating tool, for the simple reasons that included:
- No initial additional software costs
- Training costs are virtually non-existent
- Nearly everyone knows how to use Excel
- Putting together an estimate is an open and transparent process to which anyone in the business can make a contribution
- Changes can be incorporated with relative ease.
Estimate: a rough guess at what a job may cost.
Quotation: an exact price you give for the job on offer. Your quotation could be the basis for any dispute over pricing.
In my experience, a majority of clients are quick to ask for guide price. So, in response, I’m careful to make it clear whether I’m supplying an estimate or a quotation.
Usually, the point at which a client seeks my involvement is when they’ve concluded that spreadsheets perhaps aren’t the best way of producing estimates and quotations.
Their reasons for their conclusion include:
- It’s difficult to keep a track of all the spreadsheet files relating to estimates
- Too much time is taken up hunting for a previous estimate to use as a template for a new one
- No overview of all estimates is available. So, there’s no analysis of, for example, why one form of estimate leads to success in response to an ITT, and yet to failure in another
- It‘s difficult to decide if such-and-such an ITT is one to take up, especially at busy times
- Attempts at upscaling estimating spreadsheet to produce quotations has proved unsuccessful
- Quotations and responding to invitations to tender being so intertwined, laborious ways of merging Excel data into a Word document to complete the ITT response document has become a bottle neck, particularly as business expands
- There’s no post-project review. So comparisons between “successes” in terms of, say, profitability are not being made with any certainty.
With a lengthening list of requirements for an estimating system and with a low cost benefit to commissioning the programming of spreadsheets, a fresh approach is needed.
The move estimating to a Microsoft Access database becomes a serious consideration.
Access Database for Estimates and Quotations
A relational database allows you to have all your data in one place, accessible to everyone needing to use it.
The history of estimates stored in the database is a source for analysis and reflection. Hence, a previous estimate can easily be called up as a template for a new one.
Lists of products and services are easy to maintain and to call upon to populate a new estimate.
Direct and indirect costs attaching to those products and services are instantly available to the estimate make-up process and, because they are centrally stored, are ease to monitor and maintain by those responsible for the figures.
A correctly designed database will allow for expansion. It may start modestly, preparing the figures for the estimate and preparing good-looking documents for presentation.
Later, it might be enhanced to track communications relating to each estimate. And taken even further to a full-blown project management system — from ITT to final delivery of your product/service.
Preparing for the Jump
Moving estimating from spreadsheets to database takes an understanding of what is involved in creating a relational database in its many facets. Unless you have an Access expert in your team, I recommend you engage a professional developer, like me.
I would say that, wouldn’t I? Yes, of course I would. (Yours is fair conclusion that this whole website is a pitch to get you to engage my services.)
However, if you accept my view that creating a database is for the professional, then there’s one very important thing you can do before picking up the phone. Something that may save you time, money and upsets.
That imperative is to pinpoint the information, i.e. data, relating to all aspects of your estimating process. And then to render this information in terms that potential suppliers will understand. If you don’t, then they will be scratching their heads about how much they must include in their quote for unforeseen contingencies.
How to pinpoint the information is through “data modelling”. This, you think, sounds very IT-speak: how can I possibly make use of it without having to become a nerd?
Data Modelling in Practice, steps you through the data-modelling process, pointing out that your part requires of you no nerdy knowledge or understanding of IT systems.
Here to Assist
It would be great if you email me about helping with anything from data modelling through to a completed database .