Sensitivity analysis determines how a spreadsheet’s outputs will vary in response to changes made to the spreadsheet’s inputs. Excel gives us a number of tools for sensitivity analysis. One of these is the spinner. A spinner is linked to a cell containing one of the variable inputs. As the user clicks on the spinner, the value of the linked cell changes.
For example, the following worksheet can be used to examine how changes in three variable inputs (price, unit cost, and fixed cost) affect profit. To set up the formulas for the worksheet, follow these steps:
- Select C1:D7, as shown in Figure A.
- Go to Tools | Options, click the Calculation tab, and then select the Accept Labels In Formulas check box. (In Excel 2007, click the Formulas tab, click Create From Selection in the Defined Names group, and then click OK.)
- Enter 2 in cell D1, which is the current price.
- Enter the rest of the formulas as shown in Figure B.
- Type Enter Unit Cost (in hundredths) in cell H1.
- Type Enter Fixed Cost (in thousands) in cell H2.
- Enter 44 in cell I1 (the current Unit Cost).
- Enter 20 in cell I1 (the current Fixed Cost), as shown in Figure C.
- Go to View | Toolbars | Forms to display the Forms toolbar. (In Excel 2007, click the Developer tab.)
- Click the Spinner button on the Forms toolbar. (In Excel 2007, click Insert in the Forms Group and then click the Spin button under Form Controls.)
- Click and drag in cell F1 to create the Price spinner.
- Repeat step 9 and then click and drag in cell F3 to create the Unit Cost spinner.
- Repeat step 9 and then click and drag in cell F4 to create the Fixed Cost spinner.
- Right-click the spinner in F1, click Format Control, and enter the values shown in Figure D.
- Right-click the spinner in F3, click Format Control, and enter the values shown in Figure E.
- Right-click the spinner in F4, click Format Control, and enter the values shown in Figure F.
Your manager just learned that increases in material costs have raised the unit cost to .60. Using the spinner to adjust for this higher cost, the profit decreases to $45,800. Clicking the Price spinner up arrow once to raise the price to $3 immediately shows a drop in demand (assuming the demand formula is correct). However, even with this drop in demand, profit goes up significantly, which may warrant the price increase.
Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.