What If Analysis Using Scrollbar in Excel

How to perform a what-if analysis using a scroll-bar control
You can perform a what-if analysis in an elegant manner by inserting a scroll bar control on your worksheet and changing a value in small increments that is linked to another calculation.
Description of the process:
1. Click on the Developer Tab
2. Click on Inset in the Controls command group
3. We select the Scroll Bar Form Control
4. We click and drag on the Excel worksheet to place a Scroll Bar Control of appropriate length and width
5. With the Scroll Bar control selected we click on the properties icon in the controls group next to the Design Mode icon
6. A Format Control window pops up
7. We change the Minimum value to 900, the Maximum value to 1500, Incremental Change to 10 and the Page change to 100. Also we place ‘d5’ in the text box next to cell link
8. The cell D5 displays the value 900
9. Now we link the cell’s D5 value to the value in cell B5 where the interest rate per year appears
10. To get a value in percent in cell B5 we use the formula ‘=D5/10000’ in cell B5
11. Now the minimum value is set to 900 and the maximum value is set to 1500 in the scroll bar. So we can display the minimum interest rate per year as 9% and maximum rate of interest per year as 15% in cell B5
12. With every click on the scroll bar’s left and right arrows we decrease the interest rate per year by 0.1% or increase the interest rate per year by 0.1% respectively
13. If we click inside the scroll bar we increase or decrease the interest rate per year by 1% because the click causes a change of 100 units
14. Based on the change in interest rate per year the Equal Monthly Payment or EMI increases or decreases accordingly
15. In this manner we perform a sensitivity analysis of the EMI with respect to change in interest rate per year.

Watch the video below to see how this is achieved:

Further reading:
Form Controls – Adding Interactivity to Your Worksheets

Leave a Reply

Your email address will not be published. Required fields are marked *