Home > Excel for Business > 

Strategies for an Excel System
For Invoicing or Estimating

Barry,

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 cells.

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..." or whatever.

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:

=IF(ISERROR(Estimate!C10),"",IF(OR(Estimate!C10=0,"",C10))

That is, your Print sheet displays only non-error, non-zero cells from your Estimate sheet. With this approach, the Print sheet with a short estimate or invoice displays many null strings, which aren't visible and don't print.


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,

Charley Kyd
June, 2006

(Email Comments)