If you use Excel to analyze what-if situations putting different assumptions to a test then this article has something that will make your Excel-jockeying a charm.

Main theme – making assumptions graphical

Key factor in designing Excel based financial models is keeping them user friendly regardless of user's expertise with spreadsheets and professional background (specially for non-accountants).

To understand, we will use a simple profitability model linked to product quantity, selling price and expenses to demonstrate concept which may later be used for real world complex situations.

Note: Excel 2010 is used, below images may vary slightly if you are using a different version.

Basics

Our first step will be to activate hidden menu called “Developer”.

1.    Go to File Menu and click on Options (Menu > Options)

2.    Click on “Customize Ribbon” and from right side of window, check “Developer” tab.

3.    After checking Developer tab, click OK to close options dialog box. Now you should see a new tab called “Developer” after “View” in ribbon.

 

Setup base model

After adding Developer tab, we are ready to start developing base financial model.

1.    Use below figure to develop a simple model.
 


2.    Yellow cells are to be used for input of assumptions by user while grey ones are reserved for formulas. Please use formulas shown in column D as a guide to create formulas in column C.

3.    After setting up base model, go to Developers tab and click on “Insert” from “Controls” and select “Scroll Bar Form Control” as shown in below figure:

 

4.    Once you have selected scroll bar form control, cursor will change to + sign. Move it near cell D13 and hold down left click and drag to draw a rectangular shape as shown in below figure:

 

5.    Now right click once on scroll bar object, copy and paste object to cells D14 and D15 as shown below:

 

6.    After adding scroll bars, we want to use them as input handle therefore each must be linked to relevant cell to feed our model. To do this, left click on first scroll bar (cell D13) and then select “Format Control” as shown below:

 

7.    Properties box is quite detailed and self-explanatory but for the moment, focus on control tab in the dialog box.

8.    Put minimum value to zero, maximum value to 1,000 and in “Cell Link” put $C$13 (so it gets linked to input value as shown in C13). This will link our object to cell C13 with specified min and max values.

9.    Press OK to close format control box.

10.    Try dragging scroll bar left and right; you will observe C13 increasing or decreasing thus changing sales value in our model.
 


11.    If you understand the concept of using scroll bars to change inputs (better referred to as ‘what-if’ analysis) then repeat steps 5 to 9 for rest of scroll bars in cell D14 and D15. Feel free to change Max and Min values to your preference.

12.    Once you are done, your final product should look like this:

 

13.    Save your workbook and it is ready for use. Best part about this is we have not used any macro which makes workbook suitable for all security settings.

Using the same concepts we just learned, you can go on to develop complex models with graphs linked to model such as: