Push-Button Spreadsheets

our client owns an auto dealership—we’ll call it Cars R Us—and wants to automate some routine business tasks the sales staff performs countless times a day. While the staff has access to computers, no one is very computer-savvy, so you suggest a customized spreadsheet design that lets salespeople click on buttons to perform specialized calculations and access data.

In this article we will design such a spreadsheet using macros in Excel, and if you follow along, you should be able to apply the basic principles to any number of other specialized tasks for your firm, your clients or your employer. As you’ll see, you do not need experience with spreadsheets to make full use of one.

CREATE THE MENU
Before we start building the spreadsheet, we need to determine what we want it to do. The sales staff has three main tasks that are naturals for automating: It inputs and prints customer inquiry forms, calculates the commission on a given sale and locates the lender with the lowest interest rate for a given customer’s credit score.

With those goals we can assign spreadsheet buttons to perform each task. Once finished, the main menu will resemble exhibit 1, below, although later we’ll eliminate all the unnecessary spreadsheet visuals such as the toolbars.

Exhibit 1

To create the menu, open a new workbook and choose File, Save As and name the workbook Cars R Us. Double-click on the first sheet tab and rename it Main Menu . Go to View in the toolbar and click on Toolbar , opening Formatting, Drawing and Forms . Place the toolbars anywhere you want by dragging them.

Next, use the Borders icon in the Formatting toolbar to place an outline around the form. Then go into the Fill Color icon on the Formatting toolbar to color the form. Finally, use the Insert WordArt icon on the Drawing toolbar to create the title— Cars R Us Main Menu . Click on the title to drag it to where you want it on the form. We’ll show you later how to hide the grid lines.

Go back to View, Toolbars and open the Forms toolbar to create the buttons that will be attached to macros which we’ll create later. Click on Button in the Forms toolbar as shown in exhibit 2, below.

Exhibit 2

In pre-2000 versions of Excel, the cursor turns into a crosshair, which you need to drag to the location of your choice. In 2000 and later, just click on the button, and after right-clicking, paste (Ctl+V) it to a location on the screen.

When you release the mouse, the Assign Macro dialog box will pop up because Excel wants to know what macro should run when the user presses the button. Since we haven’t created any macros yet, hit Cancel .

To size the button, use the tiny round fill handles that appear around the button, as shown at right.

If you don’t see the fill handles, left-click on the button to trigger their appearance. While the fill handles are visible, type the button’s label, Complete Customer Inquiry Form . To size the button, place the cursor directly on any fill handle and drag. To move the button, place the cursor anywhere on the button’s border except on a fill handle. A spin arrow (four arrows in four different directions) appears (as shown in exhibit 3, below) and then you can drag the button anywhere on the worksheet.

Exhibit 3

We could draw the other two buttons in the same way, but since it would be difficult to match the size, just copy the first one by right-clicking on it and choose Copy from the shortcut menu. Then place the cursor where you want the new button to appear on the form, right-click again and choose Paste . Since the button is still on the Clipboard, you don’t need to copy it again. Simply right-click and choose Paste a second time. If necessary, reposition the buttons. Left-click on the buttons to label them as shown in exhibit 1.

To create the customer inquiry form, double-click on the second sheet tab in the workbook and rename it Cust. Inquiry Form. Create and format this form as shown in exhibit 4, below.

Exhibit 4

A salesperson completes this form and gives it to a customer as confirmation after he or she identifies the car of choice and financial terms have been quoted. Now, type in the formulas below and format the cells appropriately as Currency (with two decimal places) or Percentage (with one decimal place):

cell H21: =H19+H20
cell H22: =H21*0.06
cell H23: =H21+H22
cell H25: =SUM(H23:H24)
cell H28: =(PMT(H27/12,H26*12,H25))

Cell H28 will show #Div/0! since there are no numbers in the spreadsheet yet.
To test the accuracy of your formulas, type in the following test data:

In cell H19: 18000
In cell H20: 2000
In cell H24: -8000
In cell H26: 5
In cell H27: .06

If you correctly entered everything, H28 should show $255.19 (exhibit 5, below). Excel returns this value in red because a payment is a cash outflow. Using the same steps outlined above, add three more buttons to this worksheet: Print, Clear All and Return to Main Menu. Remember, we still have to attach macros to the buttons.

Exhibit 5

THE COMMISSIONS LOOKUP TABLE
The next two forms in the workbook use formulas that will rely on two lookup tables. If necessary, add three new worksheets to the workbook by right-clicking on a worksheet and choose Insert three times. Double-click on tab 5 and rename it Lookup Rates ; rename tab 6 Lookup Lenders . Return to the Lookup Rates worksheet. A salesperson’s commission is dependent on the car’s sales price, with the following commission structure:

Lookup tables can be aligned so that the search procedure occurs vertically, as in this case, or horizontally. Vertically aligned lookup tables require use of the VLOOKUP formula, while horizontally aligned lookup tables need the HLOOKUP formula. In our example, the sales price is listed in one column, which must be searched from top to bottom—that is, vertically—to determine the right category for a given sale, so a VLOOKUP formula is needed.

In either case, Excel requires the table to follow certain conventions. First, the numbers that determine which rate is appropriate must appear in the left-most column (or first row if an HLOOKUP table). Second, the numbers in the left-most column must be sorted in ascending order from lowest to highest. Finally, if the left-most column contains a range of values, as in our example, the first column of the table must show only the lowest number of the range. Based on this information, create and format the Lookup Rates worksheet as shown in exhibit 6, below.

Exhibit 6

Let’s name the lookup table COMM_Rates so it will be easier to refer to later. To do that, highlight D15 through E19. Note that you should not include the column headings or the blank line in between the headings and the first row of the table. Click on the Name Box and type COMM_ Rates as shown in exhibit 7, below, and hit Enter .

Exhibit 7

Next, we’ll create the Lender Rates lookup table with the following information:

Note that this table also is aligned vertically and thus also will require the use of the VLOOKUP function. Create the Lender Rates form as shown in exhibit 8, below.

Exhibit 8

Highlight B14 through J17, click on the Name Box and type LENDER_ Rates and hit Enter . When we use this lookup table in future formulas, we will refer to it by its name, LENDER_Rates . Now that we’ve created and named the two lookup tables, let’s go back and create the forms that will use these tables.

THE COMMISSIONS WORKSHEET
Double-click on tab 3 and rename it Commissions . Create and format the worksheet as shown in exhibit 9, below, including the buttons.

Exhibit 9

The VLOOKUP formula that will be entered in F15 has three required components. The first tells Excel what value a user is searching for in the lookup table. Here, it’s the sales price of the car that is entered in F13 to determine the commission rate. The second tells Excel the location of the lookup table. Because we named the table, we can simply use that name, COMM_RATES. The third component tells Excel which column of the lookup table should be returned. Because the commission rates are in the second column of the lookup table, we will use 2. So enter the following formulas:

In F15: =VLOOKUP(F13, COMM_RATES,2)
In F17: =F13*F15

As a test, enter 2100 as the price in F13. If you entered everything correctly, the commission rate should show 6% and the commission should be $1,272 as shown in exhibit 10, below.

Exhibit 10

THE LENDERS WORKSHEET
Now we will add the final form to the customized application, the Lenders form. Double-click on tab 4 and rename it LENDERS . Create and format the form as shown in exhibit 11, below.

Exhibit 11

To use the LENDERS form, a salesperson enters the customer’s credit score (as obtained from the customer’s credit report) into E12. The form automatically supplies the names of all lenders and interest rates that will lend at that score. Start by naming E12 Score . To do that, place the cursor in E12 and type Score into the Name Box and hit Enter . Input the following formulas in the appropriate cell (it’s easiest to type the first one and then cut and paste the rest):

C17: =VLOOKUP(score,Lender_Rates,2)
D17: =VLOOKUP(score,Lender_Rates,3)
C18: =VLOOKUP(score,Lender_Rates,4)
D18: =VLOOKUP(score,Lender_Rates,5)
C19: =VLOOKUP(score,Lender_Rates,6)
D19: =VLOOKUP(score,Lender_Rates,7)
C20: =VLOOKUP(score,Lender_Rates,8)
D20: =VLOOKUP(score,Lender_Rates,9)

Format the Interest Rates column entries for percent with one decimal place. Since all four lenders will lend at such a high credit score, those lenders and the corresponding rates should be returned to the form as shown in exhibit 12, below.

Exhibit 12

CREATE AND ATTACH MACROS
Now we will create and attach the macros to the appropriate buttons. The macros for this application can be created easily using Excel’s macro recorder. Let’s start with the three macros on the Cust. Inquiry Form worksheet. Select Tools, Macro, Record New Macro . Under Macro name: , type print_cust_form and then hit OK . The Macro Recording Toolbar will appear, and now every keystroke will be recorded into the macro until the Stop Recording button is pressed. Another way to turn off the macro recorder is by choosing Tools, Macro, Stop Recording. This comes in very handy when the macro toolbar sometimes arbitrarily disappears.

Click on the sheet tab named Cust. Inquiry Form and then press the Print icon on the standard toolbar. The worksheet will print (without the buttons). Press the Stop Recording button on the Macro toolbar.

Now, to attach the macro to the Print button, right-click on the button and choose Assign macro from the shortcut menu that pops up. From the Assign macro dialog box, select the print_cust_form macro and choose OK . Test the button to see if it works properly.

The next macro is the one that will clear the data previously entered into the form to ready it for the next user. To record this macro, select Tools, Macro, Record New Macro . Under Macro name: , type clear_data and hit OK . Place the cursor in the following cells and hit the Delete key for each: H19, H20, H24, H26 and H27. Press Stop Recording on the macro toolbar to complete the macro. Attach the Clear All button to the clear_data macro as described above.

The last button should return the user to the Main Menu . Select Tools, Macro, Record New Macro . Under macro name, type main_menu and hit OK . Click on the Main Menu tab. Press Stop Recording on the macro toolbar to complete the macro. Attach the Return to Main Menu button to the main_menu macro in the way previously described.

The Commissions worksheet requires two macros. To record the macro that will print the worksheet, select Tools, Macro, Record New Macro. Under macro name, type print_comm and hit OK . Click on the Commissions tab and then the Print icon. Press Stop Recording to complete the macro. Attach the print_comm macro to the Print button. The Return to Main Menu button does not need a new macro recorded, since there already is a macro that does this. Attach the Return to Main Menu button to the main_men u macro in the way previously described.

The two macros required in the Lenders worksheet work the same way as the two macros in the Commissions worksheet. Use the macro recorder to record the macro that prints the Lenders worksheet. Name this macro print_lenders . Attach it to the Print button on the Lenders worksheet and attach the Return to Main Menu button to the main_menu macro that already exists.

Finally, to create the macros for the three buttons on the Main Menu , select the Main menu tab and turn on the macro recorder. Under Macro name: , type goto_ cust_form and hit OK . Click on the Cust. Inquiry Form tab and stop recording and then attach the goto_cust_form macro to the Complete Customer Inquiry Form button.

To record the macro for the Calculate Commission button, turn on the macro recorder and under Macro name: , type goto_comm and hit OK . Click on the Commissions tab and stop recording. Then attach the goto_ comm macro to the Commissions .

Record the macro for the Locate Lenders button, and under Macro name:, type goto_lenders. Click on the Lenders sheet tab, stop recording and attach the goto_lenders macro to the Locate Lenders button.

CELL PROTECTION
If the application will reside on a server where multiple users will access it and enter data, all formula cells should be protected against inadvertent or unauthorized changes. To do that, start at the Cust. Inquiry Form . Highlight B13 to I20, select Format, Cells, Protection and click on the Locked box to unlock those cells as shown in exhibit 13, below. Click on OK .

Exhibit 13

This will allow changes to be made to these cells when protection is turned on. Follow the same procedures to unlock H24, H26 and H27. The remaining cells in the workbook contain formulas and thus should not be unlocked. Now protect the worksheet by selecting Tools, Protection, Protect Sheet and click on OK . You can use a password to keep users from turning off protection at will.

FINAL TOUCHES
The last thing to do to complete the customized application is to turn off grid lines and hide unnecessary toolbars and sheet tabs. To do that, select the Main Menu sheet and Tools, Options, View and deselect the following: Formula bar, Status bar, Gridlines, Row & column headers and Sheet tabs.

Then go to View, Toolbars and deselect any toolbars you wish to hide. Although hiding the toolbars with the View tab will accomplish this on the entire workbook, you will need to hide the grid lines and row and column headers on each worksheet. Don’t hide the sheet tabs because that will make the sheets with the lookup tables inaccessible. To view those sheets, it would be necessary to choose Tools, Options, View and check the sheet tabs box again to show sheet tabs.

Also, don’t turn off the scroll bars; that sometimes creates problems viewing an entire screen. Exhibit 14, below, shows the Main Menu with all controls hidden.

Exhibit 14

Finally, we’ll save the completed customized application as a template so it can be used repeatedly. The advantage of saving our final workbook as a template, rather than just as a regular file, is that if users want to save their work, they will be prompted to save it under a new file name since templates preserve the original form. Excel stores templates with an .xlt file extension. To save the file as a template, select File, Save As . Under Save As select Template and type in the workbook name, Cars R Us , and Save .

Now when salespeople want to use the template, they will choose File, New and, from the General tab, double-click on Cars R Us.

Once you know the steps to creating a customized application, it’s easy to spot opportunities for your client or your own firm to more efficiently handle any routine task that uses a spreadsheet.

STEPHANIE M. BRYANT, CPA, PhD, is Advisory Council Professor of Accounting at the University of South Florida School of Accounting, Tampa. Her e-mail address is sbryant@coba.usf.edu . MICHELE MARTINEZ is an accounting doctoral candidate at the University of South Florida.