Home > Excel
for Business >
Strategies for an Excel System
For Invoicing or Estimating
"I'm trying to make an estimating spreadsheet
to work out prices for materials and labour charges. I've got the latest
UK pricing book. But when trying to put it all into Excel, I'm not sure
where to start. For example, do the material prices and labour prices
need to go into a database first?" -- Barry V.
A price estimating system is very similar to an invoicing system
when you are selling specific products or services. Here's how I would
approach the design:
You probably will use three or four worksheets for your estimate. I would
create them first, then connect the plumbing later.
1. The database. In general, I would structure it much like the first
illustration shown in Use a Combo Box with Many Criteria in an Excel Database.
You could enter a few rows of data to get started. Then, after
everything is working, you could complete the database. Just make sure
that the rows you enter represent your most complex data. You don't want
to design your system so that it works only with the easy data.
2. The Print sheet. In another worksheet or workbook, I would create the
estimate that you'll print, using your previous estimates as a guide for
the format. Initially, I'd enter numbers and text for the parts that
change, rather than formulas. At this point, don't worry about final
formatting. You just want to make sure that you have all the information
you will need, and the approximate spacing.
Should you use one workbook for your data and another for your
estimates? Or should everything be in one workbook?
The advantage to
using two workbooks is that you can have a lot of small estimate
workbooks all linked to the same data. You won't have many versions of
the data and you won't have large files saved for each estimate. The
disadvantage is that when you modify your estimate database, your past
estimates will be different the next time you open them.
Here's one possibility: If you can limit yourself to periodic changes in
your database you could name each new version with its date, like this:
EstData_2006_06_23.xls. (By naming the files in year, month, and day
sequence, the file names will sort correctly.) Then, when you create a
new estimate, just make sure it's linked to the current data file and
that you never change the data in that file and save it under the same name.
By the way, it probably won't matter if you add to a data
file. Just make sure that you don't change any data that could
change past estimates.
3. The Control sheet. This is a simple spreadsheet
form where you can fill in the header information in your printed
estimate. The estimate in your Print sheet would have formulas linked to the Control sheet
There are at least two advantages to using the Control sheet.
First, you can enter the estimate information row-by-row, which is
easier and faster. Second, you can include notes to yourself, if
necessary, about any field. These might begin, "Remember to include..."
4. The Estimate sheet. You'll probably enter the line items for your
estimate in the Print sheet form, with formulas that return data from
your database. However, if you need side calculations, notes, and so on,
you might want to use a worksheet to contain all this work, then merely
reference the correct cells in your Print sheet.
If you take this approach, your formulas in the Print sheet might look
something like this:
That is, your Print sheet displays only non-error, non-zero cells
your Estimate sheet. With this approach, the Print sheet with a short
estimate or invoice displays many null strings, which aren't visible and
Finally, after you have all these elements in place, you connect up the
plumbing among them. For each row of your estimate, you'll probably use
INDEX-MATCH formulas a lot. The article
How to Fight Spreadsheet Hell
With Three Excel Functions discusses
INDEX and MATCH. You might also
get some ideas from Use a Combo Box with Many Criteria in an Excel Database.
Hope this helps,