Use Excel spin boxes to help with financial modeling

Businesses use financial models to understand the potential impact of a variety of initiatives, ranging from the launch of new products or services to equipment investments or mergers and acquisitions. Financial models are used in business plans, pro forma financial reporting, and even applications for bank loans by businesses.

Developing financial models can be complicated, particularly if they contain many variables and operating scenarios. The spin box feature in Microsoft Office Excel 2003 can help you build a more robust financial model that can easily test operating scenarios.

What is a spin box?

A spin box is a text box attached to a button (called a spin button) consisting of an up arrow and down arrow that you click to incrementally change the value in the text box. By using spin boxes, you can see how input changes to your financial model will alter the model outputs. You can attach a spin button to a specific input cell. While you click the up arrow or down arrow on the spin button, the integer value in the targeted input cell increases or decreases.

You can set spin button properties associated with the targeted input cell, including the increment of change that is used for each time the spin button is clicked and the maximum and minimum values that can be displayed. When you use a variety of spin buttons, you can alter your financial model quickly without revising the model's hard-coded formulas.

The spin button is a component of the Forms toolbar in Excel. To display the Forms toolbar on an Excel worksheet, click Toolbars on the View menu, and then click Forms. The Spinner button on the Forms toolbar appears as a button with an up arrow and a down arrow.

Spinner button on the Forms toolbar

How can you use spin boxes in a financial model?

In your financial model, identify the key input variables that you would like to change in order to evaluate different operating scenarios. In the financial model used in the following example, Year 1 unit price is used as the key variable.

Excel worksheet financial model highlighting Year 1 unit price

To create a spin box on an Excel worksheet, follow these steps:

Step 1: Prepare a worksheet for spin boxes

When you prepare your financial model worksheet for the insertion of spin buttons, make sure to allow for sufficient row and column space.

  1. On your worksheet, select the row that contains the cell with the key variable, right-click the row, click Row Height, and then enter 25.
  2. If you need space so that you can add a spin button next to the input cell that you want to use, insert a new column. Select the cell to the right of the input cell. On the Insert menu, click Columns.
  3. Select the new column, right-click it, click Column Width, and then enter 5.

 Note   The values used here for the row height and column width are arbitrary. You can modify the row height and column width in the way you want.

Step 2: Add spin buttons to a worksheet

  1. On the Forms toolbar, click the Spinner button.
  2. Place your pointer over the cell to the right of the cell with your key variable, and then press and drag the pointer to "draw" a spin button.
  3. Adjust the width and height of the spin button to fit the cell by dragging the sizing handles that are at the corners and along the edges of the spin button.

Spinner button inserted onto Excel spreadsheet

Step 3: Set spin button properties

Follow these steps to change the properties of the spin button or spin buttons that you added to the worksheet.

  1. Right-click the spin button, and click Format Control.
  2. In the Format Control dialog box, click the Control tab.
  3. In the Current value box, enter the value of your key variable (400 in this example). This is the default value that appears every time you select the worksheet.
  4. In the Minimum value box, enter the lowest value that you want the spin button to display. For this example, 250 was entered.
  5. In the Maximum value box, enter the highest value that you want the spin button to display. For this example, 600 was entered.
  6. In the Incremental change box, enter the increment of change that you want each click of the spin button to make. For this example, 25 was entered.
  7. Place your insertion point in the Cell link box.
  8. On the worksheet, click the cell where the key variable number currently appears and where spin box results will appear (cell B10 in this example).
  9. In the Format Control dialog box, click OK.
  10. Repeat steps 1 through 9 for the other spin buttons that you added to the worksheet.

Format Control dialog box

Spin boxes can display only positive integers (from 0 to 30,000). But suppose that the input cell to which you want to attach a spin button contains a fraction, like the value of 5% (0.05) in cell B11 in this example. Follow these steps to use an input cell that has a number less than one:

  1. Select an empty cell next to the spin button for your key variable, and type 5. For this example, cell D11 was used.
  2. In cell B11, create a formula that references cell D11 and also calculates 5%. For this example, the formula =D11/100 was used.

Formula entered into cell B11 to reference cell D11

  1. Right-click the spin button for cell B11, and click Format Control.
  2. In the Format Control dialog box, click the Control tab.
  3. Enter values in the Current value, Minimum value, and Maximum value boxes for your spin box.
  4. In the Cell link box, type D11 so that the spin button references the positive integer that's in cell D11.

 Note   The spin button properties that were set in the Format Control dialog box apply to the positive integer that's in cell D11, not the fraction that's in cell B11.

Step 4: Use your spin box

Before testing your spin box, make sure that the model inputs and model outputs are visible on your computer screen. By doing so, when you click a spin button, the input and output changes are immediately displayed.

If your financial model has model inputs and model outputs on separate worksheets in a workbook, follow these steps to view both worksheets on your screen simultaneously:

  1. Open the workbook, and select the input worksheet.
  2. On the Window menu, click New Window. An identical version of your workbook appears in the new window.
  3. In the workbook in the new window, select the output worksheet.
  4. Press CTRL+F6 to switch to the first workbook window.
  5. In the first workbook window, click Arrange on the Window menu.

 Note   Click Horizontal if you want your input worksheet to appear above your output worksheet. Click Vertical if you want your input worksheet to appear to the left of your output worksheet.

  1. On the input worksheet containing the spin box or spin boxes, click the up arrow or down arrow on the spin button. While you click the spin button, review the output cells to see how they change.

Input worksheet appears at top of screen and output worksheet appears at bottom of screen

Callout 1 Input worksheet
Callout 2 Output worksheet

Spin boxes are useful tools to help you develop more effective and user-friendly financial models. By using spin boxes, you can control key variable inputs more easily and see the impact of variable changes on the model outputs.

 
 
Applies to:
Excel 2003