Using Excel to Build a Quoting System, Part I

Excel has all the right tools you need to build a solid, stable quoting system that saves time and is easy to use for Excel-challenged employees. In this multi-part article, I'll show you how to create your own quoting system.

As the director of a division of a green energy company that offers renewable energy systems and efficiency products to homeowners and businesses, we get called upon to issue quotes quite frequently. For a time, I was responsible for issuing every quote, and it became quite time-consuming. The reason was simple: nobody really knew Excel well enough to generate new quotes. Option changes and pricing updates were cumbersome and technical, pushing issuing a simple quote past the realm of the salespersons’ knowledge. Finally, the process was insecure insofar as there was no protection for the company against a salesperson changing the pricing of the components or offering options that were not approved.

Fortunately, Excel has the tools to build a great quoting system that is easy to use and secure. In this series, you will learn how to use Excel’s Control Toolbox, logical functions, style features, and worksheet protection features to build a functional quoting system. The series will consist of the following modules:

1. Building the Backbone (Creating and Protecting Standardized Data)
2. Creating a Data Input Sheet: Using Drop-Down Lists
3. Creating a Data Input Sheet: Using Check Boxes
4. Creating a Dazzling Estimate Page

Using these powerful features requires no VBA or even macro skills, and once you learn them, will take your knowledge of Excel to an entirely new level.

We’ll build a quote system for a hypothetical company, AxeBuilders, that offers standardized guitars with options available for each instrument.

Building the Backbone (Creating and Protecting Standardized Data)

The first step in building our quote system is to define and protect all of our standardized data. Let’s say that AxeBuilders makes four instruments:

• A six-string guitar
• A twelve-string guitar
• A four-string bass, and
• A five-string bass

Each instrument has standard features and options. The first step is to create packages that define the basic features of each instrument that you will want to dynamically change on the quote sheet. Here’s an example, in table format:

The next step is to define the available options for each instrument, which for AxeBuilders are:

• Active Electronics
• Gloss Finish
• Fretboard Inlays

Finally, we have five available colors:

1. Red
2. Green
3. Black
4. Blue
5. Natural

Now that we have determined what our standard data is, it’s time to set up an Excel workbook and get started. Create a new workbook by clicking File > New. Rename the first sheet to Standard Features, by double-clicking on the current sheet name tab, typing in the new name and hitting Enter. Do the same for the next two sheets; name one Options, the other Colors.

Next, retype the table above into the Standard Features sheet. Be sure to keep the table format the same, as shown in the image below:

Now that the text is entered, let’s protect it so that a salesperson can’t mistakenly change any of the information. (Let’s give them the benefit of the doubt that any adjustments would be unintentional!):

1. With the Standard Features sheet selected, go to Tools > Protection > Protect sheet.
2. Leave the boxes checked, and type in a password (I used “axes.” Use whatever you want, just make sure you write it down!) and hit Enter.
3. Retype the password a second time for confirmation and hit Enter, again.

The sheet now cannot be changed without entering a password that only approved personnel should have.

Now you'll need to enter all the standard data for the options and colors features into their, respective, Options and Colors worksheets, just as you did with the Standard Features sheet.

Next month, we’ll start to create a data input sheet and build the smart logic that will really bring our quote model to life.