Using Excel to Build a Quoting System, Part IV

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. This multipart series will show you how.

This is the fourth and final installment of our four-part series about using Excel to build a quoting system. So far, we’ve set up the basic worksheet for our hypothetical company, AxeBuilders, typed in and protected our standard data, and covered creating drop-down list boxes to dynamically select list data, and covered creating check boxes to dynamically select instrument options. In this installment, we will actually create our estimate page.

Creating A Dazzling Estimate Page

Fortunately, the hardest part is over! Now comes the creative work of building an estimate page that can be sent to customers. First, let’s do a little more work on the estimate page so that we can personalize each proposal. Add a block on the Data Input sheet to include the customer’s information, as shown below:

Now, let’s create our Estimate page by right-clicking on the Standard Features worksheet, selecting Insert, then double-clicking on the Worksheet icon. Then, double-click on the Sheet1 tab and retype the name as “Estimate”. We will then have a blank Estimate worksheet positioned between the Data Input sheet and the Standard Features sheet, as shown below:

Now, let’s create a simple Estimate page. First, type the information in the Estimate sheet as shown below:

Then, we will copy over the Customer Information block in such a way so that when it is changed on the Data Input sheet, it will change in the Estimate. To do that, click on cell A5 in the Estimate sheet, type “=” and hit Enter, then click on cell A10 on the Data Input sheet and hit Enter. “Name” will appear in cell A5 of the Estimate sheet. Then, click and drag the small black box in the lower right corner of the box to include cells A5-A10 of the Estimate sheet. The results will appear as below:

Then, click and drag the small black box over one column to include the range A5-B10. The results will appear as below:

Now, if we change the customer’s information on the Data Input sheet it will automatically be updated on the Estimate sheet.

Next, let’s create a block for the product information and pricing, as shown below:

Note that I have the values for the Instrument and Color fields included in the Estimate. To do this on your sheet, set the contents of cells B4 and B17 on the Estimate sheet to cells C1 and C2 on the Data Input sheet.

Now, go to the Data Input sheet and select the Instrument Package “G12” and the Color “Black” from their respective Combo Boxes. The Estimate page will be updated as shown below:

Now, let’s introduce the LOOKUP feature of Excel in order to fill out the rest of the product information and calculate the pricing.

Using Excel’s LOOKUP Function

Excel’s LOOKUP function enables the user to look up information in a multi-column, multi-record list based upon the value of one of the fields. For instance, since we’ve selected the G12 model, we can use the LOOKUP function to pull in to the estimate the value of the other standard features of the G12, including its type, number of strings, neck length, and price.

However, before we use the LOOKUP feature on a list, we have to make sure that the list is sorted. LOOKUP will not work properly on an unsorted list (just as an unsorted dictionary would be useless to us). Let’s start by sorting the Standard Features list. Select cells A1-F5 on the Standard Features sheet, as shown below:

Then, select Sort from the Data menu; the following window will appear:

We can use this window to set the sorting methods on our list. It turns out that the default sort value of this list is just what we want, but note that you can change the column, change the sort order, and do up to three levels of sorting. Also note that Excel intelligently noticed that the first row was a header row that contained column headings. Useful feature!

Now, click OK to sort the list. It will appear as below:

Now that the list is sorted, let’s use the LOOKUP feature. Click on cell B14 of the Estimate sheet, then select Function from the Insert menu. A window will pop up that will allow you to select from Excel’s many built-in functions. Click on the Lookup & Reference function category, and select Lookup, as shown below:

Click OK, and the following window will appear:

Click OK again, and the following box will appear:

Note that next to the input area for Lookup_value is an icon that looks like a miniature spreadsheet. Click on that icon, then select the Data Input sheet, then click on cell C1, then hit Enter. The LOOKUP function box will be updated as shown below:

Note that the Lookup_value has been set to cell C1 on the Data Input sheet, and the value “B5” confirms that. Next, type “Model” into the Lookup_vector input area and hit Enter. The function box will be updated as below:

Note that the values for the Model list (B4, B5, G12 and G6) have been set to the Lookup_vector. Since we created a Named Range (List) called Model, we can use the name reference rather than selecting the cells. This is much handier than it even appears in this example, as you’ll find out as you explore Excel more deeply. For now, it saves us a little time and helps us to work more efficiently and accurately.

Finally, we want to set the Result_vector to the range that we want to look up based upon which model instrument we select. In this case, we want to specify the type of instrument based upon our model selection, so type “InstType” into the Result_vector input area, and hit Enter. Excel then performs the lookup, and the result is placed into the cell we selected, as shown below:

Let’s use the Named Range feature to make the rest of our work easier as well. Click on cell C1 in the Data Input sheet, highlight “C1” in the Name Box, and type “MyModel”. Now, we can use the reference MyModel instead of ‘Data Input’!C1 to refer to the model that we have selected. Repeat the process as follows:

Now, our Estimate sheet will appear as follows:

Now, if you experiment with changing the instrument model, you will note how all the other related information changes as well. Use the Instrument Package Combo Box to select the G6 model, and have another look at the Estimate sheet:

Now, it’s time to tie in the Options functionality. Set cells A19-A21 of the Estimate sheet to refer to cells A4-A6 of the Data Input sheet. Then click on cell B19 of the Estimate sheet and select Function from the Insert menu. Then select the Logical function category and click on IF, then hit Enter. The following box will appear:

Click on the small spreadsheet icon to the right of Logical_test, then click on cell C4 of the Data Input sheet. Next, type “Yes” in the Value_if_true field, and “No” in the Value_if_false field, as shown below:

Then hit Enter. The result will appear as follows:

Repeat this for the Gloss Finish and Fretboard Inlays options, then experiment with changing the option values via the Data Input sheet. You now have the basic features in place for your estimate page! What remains is to set up the pricing features and jazz up the formatting.

First, let’s go to the Options sheet and set the prices for the instrument options as follows:

Type the price information into column B next to each option. Next, highlight the range A1-C3 and sort the range using column A1 in ascending order.

Now, notice I’ve introduced a problem that will need to be fixed: since we changed the sort order of the Options list, the check boxes are now out of order on the Estimate sheet (as you’ll notice if you compare the options listed in cells A4-A6 on the Data Input sheet with their respective Check Boxes). To fix this problem, we need to use Named Range references rather than relative references for selecting our options. In fact, the more you use the Named Range features, the more flexible and efficient your workbooks will be.

Go to cells C4-C6 in the Data Input sheet and use the Name Box to name each cell “Opt_Active”, “Opt_Gloss”, and “Opt_Inlays”, respectively. Then, go to cells A4-A6 in the Data Input sheet and change the option names to match the names in the descriptions of the Check Box fields to restore the original option order.

This is a real-world type of challenge that you will encounter. Forethought can eliminate many of these goofs, but proper design techniques will protect you from most of them regardless of how well you plan ahead.

Finally, after cleaning up the styling of the sheet and adding the pricing and totals, we have a working, presentable estimate sheet, as shown below:

In the sample workbook that comes with this lesson, I’ve protected all the sheets but the data input sheet, so the only information that the salespeople can change is on the data input sheet.

Note, with the protection that we’ve added, only those with password access can alter the critical product information and pricing data, and users cannot alter the estimate in any way beyond generating and modifying proposals that you can be confident are accurate and look great.

With the building blocks we’ve covered in these lessons, you can build a quoting system much more complex and powerful than Axebuilders’ model. I encourage you to explore other tools in the Control Toolbox; when used in Excel, these tools can be used to build complete applications that would normally take a full-time software development team months or years if attempted from scratch.