MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

As discussed in Chapter 17, “Using the Scenario Manager for Sensitivity Analysis,” the Scenario Manager lets you change a group of input cells to see how various outputs change. Unfortunately, the Scenario Manager requires you to enter each scenario individually, which makes it difficult to create more than a few. For example, suppose you believe that four key inputs to our car net present value (NPV) model are Year1 sales, Sales growth, Year1 price, and Year1 cost. (See the file NPVspinners.xlsx.) We’d like to see how NPV changes as the inputs change in the following ranges:

Using the Scenario Manager to generate the scenarios in which the input cells vary within the given ranges would be very time consuming. By using spin buttons, however, a user can quickly generate a host of scenarios that vary each input between its low and high value. A spin button is a button control that is linked to a specific cell. As you click the up or down arrow on the spin button, the value of the linked cell changes. You can see how formulas of interest (such as a car’s NPV) change in response to changes in the inputs.

• I need to run a sensitivity analysis that has many key inputs, such as year 1 sales, annual sales growth, year 1 price, and year 1 unit cost. Is there a way I can quickly vary these inputs and see the effect of the variation on the calculation of net present value, for example?

• Here’s how to create spin buttons that allow us to vary Year1 sales, Sales growth, Year1 price, and Year1 cost within the ranges we want. Our original worksheet (see the file NPVspinners.xlsx) is shown in Figure 25-8.

  Figure 25-8: The automobile sales data worksheet without any spin buttons

• To create the spin buttons, select the rows (I used rows 2–5 in this example) in which you want to insert spin buttons, and then increase the height of the rows by right-clicking and selecting Format, Row, and then Height. A row height of 25 is usually enough to accommodate the spin button arrows. Alternatively, holding down the Alt key while you draw the control in the cell fits it to the cell.

• Display the User Forms toolbar by clicking Insert in the Controls group on the Developer tab. Click the spin button form control (shown in Figure 25-1), and drag it to where you want it to appear in your worksheet (cell D2). You will see a plus sign (+). Clicking the mouse button will anchor the spin button where you want it, and allows you to draw the desired shape for the spin button. To change the shape of a user form control or to move the control, place the cursor on the control and hold down the Ctrl key. When the four-headed arrow appears, drag it to move the form. When the two-headed arrow appears, drag it to resize the form.

• A spin button now appears in cell D2. We’ll use this spin button to change the value of Year1 sales. Right-click the spin button, and on the shortcut menu, click Copy. Right-click cell D3, and then click Paste. Also paste the spin button into cells D4 and D5. You should now see four spin buttons as shown in Figure 25-9.

  Figure 25-9: Spin buttons placed in worksheet cells

• Now we need to link each spin button to an input cell. To link the spin button in D2 to cell C2, right-click the spin button in cell D2 and then click Format Control. Fill in the Format Control dialog box as shown in Figure 25-10.

  Figure 25-10: Use the Format Control dialog box to link Year1 sales to a spin button.

• The current value is not important. The rest of the settings tell Excel that this spin button is linked to the values in cell C2 (Year1 sales), that each click on the up arrow will increase the value in C2 by 1000, and that each click on the down arrow will decrease the value in C2 by 1000. When the value in C2 reaches 30,000, clicking the up arrow will not increase it; when the value in C2 reaches 5,000, clicking the down arrow will not decrease the value in cell C2.

• Next, we use the Format Control dialog box to link the spin button in D4 to Year1 price (cell C4). For current value, I used 9. The minimum value is 6, the maximum value is 20, and the incremental change is 1. Clicking the spin button arrows in cell D4 will vary the Year1 price between $6 and $20, in $1 increments.

• To link the spin button in cell D5 to Year1 cost (cell D5), I used 6 for the current value, 2 for the minimum value, 15 for the maximum value, and 1 as the incremental change. Clicking the spin button arrows in cell D5 will change Year1 cost from $2 to $15, in $1 increments.

• Linking the spin button in cell D3 to Sales growth is trickier. We would like the spin button control to change Sales growth to 0 percent, 1 percent, … 50 percent. The problem is that the minimum increment a spin button value can change is 1. Therefore, we link our spin button to a dummy value in cell E3 and place the formula E3/100 in cell C3. Now, as cell E3 varies from 1 to 50, our Sales growth varies between 1 percent and 50 percent. Figure 25-11 shows how to link our spin button to cell E3. Remember that Sales growth in cell C3 is simply the number in cell E3 divided by 100.

  Figure 25-11: The Format Control dialog box settings that link the spin button in cell D3 to cell E3.

• By clicking a spin button arrow, we can easily see how changing a single input cell-given the values for the other inputs listed in the worksheet-will change the car’s NPV. To see the effect of the changes, you can select cell F9, and on the View menu click Freeze Panes, and then click Freeze Panes again. This command freezes the data above row 9 and to left of column F. You can now use the scroll bars on the right of your screen to arrange the window as you see it in Figure 25-12.

  Figure 25-12: You can freeze panes to see the results of calculations in other parts of a worksheet.

• Given the values of our other inputs, clicking the Sales growth spin button arrows shows us that a 1 percent increase in Sales growth is worth about $2,000! (To return the worksheet to its normal state, click Freeze Panes on the View menu, followed by Unfreeze Panes.)

• The scroll bar control is very similar to the spin button. The main difference is that by moving the cursor over the gray area in the middle of the scroll bar, you can cause the linked cell to continuously change in value. By selecting Format Control on the shortcut menu and changing the value under Page Change in the Format Control dialog box, you can control the speed with which the linked cell changes.

• How do I set up a simple check box that toggles conditional formatting on and off?

• A check box is a form control that enables us to check a box. When the box is checked, a TRUE is placed in a cell. When the box is unchecked, a FALSE is entered into the cell. Check boxes can be used to enable “toggle switches” which turn a particular feature on or off. As an example of the use of a check box, we show how a check box can be used to turn the conditional formatting feature on or off.

• Suppose our worksheet contains monthly sales and we want to color the five largest sales in green and the five smallest sales in red (see the file Checkbox.xlsx). We enter in cell G4 the formula =LARGE(Sales,5), which computes the fifth-largest sales value. Then, in cell H4, we compute the fifth-smallest sales value with the formula =SMALL(Sales,5). (See Figure 25-13.)

  Figure 25-13: Use of a check box to turn conditional formatting on and off

• Next, we will create a check box and cause it to enter a TRUE or FALSE in cell F1. On the Developer tab, click Insert and select the check box (see Figure 25-3) from the Forms palette. Drag the check box to cell G9, and change its text to read TURN FORMATTING ON OR OFF. Then right-click the check box, click Format Control, and fill in the dialog box as shown in Figure 25-14.

  Figure 25-14: Format Control for a check box

• Now, whenever we place a check in the check box, a TRUE is placed in F1, and whenever we do not have a check in the check box, a FALSE is placed in cell F1.

• After selecting the cell range D4:D29, click Conditional Formatting in the Styles group on the Home tab, and then click New Rule. Enter the formulas shown in Figures 25-15 and 25-16 to color the top five sales green and the bottom five sales red. Note that the AND($F$1) portion of the formula ensures that our format can be applied if cell F1 is TRUE. Of course, our check box determines whether cell F1 is TRUE or FALSE, so if the check box is not checked, then our cells will not turn green or red.

  Figure 25-15: Format to turn five largest cells green

  Figure 25-16: Format to turn five smallest cells red

• If desired, we can select cell F1 and change the Font Color to white so that TRUE or FALSE is hidden.

• How can I set up my worksheet so that my supply chain personel can check a button to choose whether we charge a high, low, or medium price for a product?

• Let’s suppose that there are three prices we can charge for a product: High, Medium, or Low. These prices are listed in cells B7:B9 of the file Optionbuttons.xlsx. We could easily use a lookup table to print out the price if the user typed in High, Medium, or Low. It’s a better design, however, if the user can select an option button that says High Price, Medium Price, or Low Price, and then have a formula automatically compute the price (see Figure 25-17). To use option buttons, you first draw a Group Box (see Figure 25-5) from the User Forms toolbar. Then you drag an option button for each choice into the group box. Because we have three price levels, we drag three option buttons into the group box. Right-click any option button. Then using Format Control, we can link any one of the option buttons to a cell. We chose to link the first option button to cell E4. All of the option buttons in the group box are now linked to the same cell. Selecting the first option button will enter a 1 in cell E4, selecting the second option button will enter a 2 in cell E4, and selecting the third option button will enter a 3 in cell E4.

  Figure 25-17: Using option buttons to select product price

• Entering the formula =INDEX(A7:A9,E4,1) in cell E7 will return the price description corresponding to the selected option button. In cell F7, the formula =VLOOKUP(E7, A7:B9,2,FALSE) computes the price corresponding to the selected option button.

• How can I create an easy way for a user of my spreadsheet to select a day of the week without having to type any text?

• The file Combobox.xlsx shows how to use a combo box or a list box to allow the user to easily select an item from a list (data validation also makes it easy to create drop-down boxes; see Chapter 35, “Validating Data”). (See Figure 25-18 on the next page.) Our goal is to compute the number of hours an employee worked on a given day. The hours worked each day are listed in cells G9:G15. A combo or list box allows you select an entry from a list. If the combo or list box is linked to a cell (via Format Controls), then if the first entry in the box is selected, a 1 is placed in the linked cell; if the second entry in the list is selected, a 2 is entered in the linked cell, and so on. After selecting the Developer and Forms toolbar, and selecting Insert, we drag a combo box from the Form Controls to C5 and a list box to B14. After right-clicking the combo box and clicking Format Control, we selected the input range F9:F15 (this contains days of the week) and cell link A8. After right-clicking the list box and selecting format control, we selected input range F9:F15 and cell link A13. If, for example, we select Tuesday from the combo box and Friday from the list box, we see a 2 in A8 and 5 in A13.

  Figure 25-18: Combo and list boxes

• In cell F3, the formula =INDEX(F9:F15,A8,1) lists the day of the week corresponding to our combo box selection. In cell G3, the formula =VLOOKUP(F3,$F$9:$G$15,2,False) locates the number of hours worked for the day selected in the combo box. In a similar fashion, the formulas in F4 and G4 list the day of the week and hours worked for the day selected in the list box.