Excel 4 Ur Business

Overview

• 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?

The Scenario Manager lets you change a group of input cells and see how various spreadsheet outputs change. Unfortunately, the Scenario Manager requires you to enter each scenario individually, which makes it difficult to create more than a few scenarios. For example, suppose you believe that four key inputs to our car net present value (NPV) model are year 1 sales, sales growth, year 1 price, and year 1 cost. (See the file NPVAudit.xls.) We’d like to see how NPV changes as these 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 spinners, however, a user can quickly generate a host of scenarios that vary each input between its low and high value. A spinner is a button that is linked to a given cell. As you click on the spinner button, the value of the linked cell changes and 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 spinners that allow us to vary year 1 sales, sales growth, year 1 price, and year 1 cost within the ranges we want. Our original spreadsheet (see the file NPVSpinners.xls) is shown in the following figure.

To create the spinners, I select the rows (I used 2-5 in this example) in which I want to insert buttons and then increase the height of the rows by selecting Format, Row and then Height. A row height of 25 is usually big enough.

Next I display the Forms toolbar, shown in the following figure, by choosing View, Toolbar, Forms.

I click on the spinner button and then draw a box for a spinner beginning at the top of cell D2 and ending at the bottom of cell D2. A spinner now appears in cell D2. We’ll use this spinner to change the value of year 1 sales. Right-click on the spinner, and choose Copy from the shortcut menu.

Rightclick in cell D3, and then choose Paste. Also paste the spinner in cells D4 and D5. You should now see four spinners as displayed in the following figure.

Now we need to link each spinner button to an input cell. To link the spinner in D2 to cell C2, right-click on the spinner in cell D2 and then choose Format Control from the shortcut menu. Fill in the Format Control dialog box as shown in the following figure.

The current value is not important. The rest of the settings tell Excel that this spinner is linked to the values in cell C2 (year 1 sales) and that each click on the upper spinner will increase the value in C2 by 1000, while each click on the lower spinner will decrease the value in C2 by 1,000. Once the value in C2 reaches 30,000, clicking the upper button will not increase it; once the value in C2 reaches 5,000, clicking the button will not decrease the value in cell C2.

Next we use the Format Control dialog box to link the spinner in D4 to year 1 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 spinner in cell D4 will vary year 1 price between $6 and $20 in $1 increments.

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

Linking the spinner in cell D3 to sales growth is trickier. We would like the spinner to change sales growth to 0 percent, 1 percent, … 50 percent. The problem is that the minimum increment allowed for a spinner is one. Therefore, we link our spinner 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. The following figure and 18-6 show how I linked our spinner to cell E3. Remember that the sales growth in cell C3 is simply the number in cell E3 divided by 100.

Now by clicking a spinner button, we can easily see how changing a single-input cell-given the values for the other inputs listed in the spreadsheet- will change the car’s NPV. To see the effect of the changes, you can select cell F9 and then choose Window, Freeze Panes. This command freezes the data above row 9 and to left of column F. You can now use the scroll bars to arrange the window as you see it in the following figure.

Given the values of our other inputs, clicking the spinner for sales growth shows us that a 1 percent increase in sales growth is worth about $2,000! (Choosing Window, Unfreeze Panes will return the spreadsheet to its normal state.)

Explore posts in the same categories: MS Excel Tune Up