Instructions for Operation of Excel Sensitivity and Scenario Analysis Used in the Course

1. Instructions for Operation of Excel Sensitivity and Scenario Analysis Used in the Course

2. Introduction • This file provides instructions on various excel techniques and macros. The excel functions and macros described are related to financial modeling applications. Practical step by step instructions are provided and helpful hints from actual applications are emphasized. • There are a number of excel files that go with the discussion of each subject. These exercises are in the “excel exercises” folder of your CD. • As explained on the next slide, if you would like to find a particular subject, use the CNTL, F key. Financial Modeling Workbook

3. Finding things in the Manual • To Find Item us CNTL F • For example, if you would like to find discussion of how to add sheets in macros, look for the word “sheets”: • Use CNTL F • Type sheets Financial Modeling Workbook

4. Creating a Scenario Analysis • The data tables described above does not allow you to adjust a series of variables and present a series of outputs. You can create an effective scenario analysis using the following excel tools: • The index command • Data tables • Combo Box Financial Modeling Workbook

5. Step by Step Process for A Scenario Analysis • The following step by step process can be used to develop a scenario analysis: • Type in input data for multiple cases • Select output variables and place a link to the variables to the right and above the input data • Type row numbers for each case and then type a row number for case selection • Use the index command to select the appropriate scenario • Link INPUT variables in the model sheet to the result of the INDEX command above • Create a DATA TABLE using the scenario row as the COLUMN INDEX • Present the scenario analysis with a COMBO box and a graph Financial Modeling Workbook

6. Step 1: Inputting Data • The first step is inputting data in a set of rows with each row corresponding to a scenario. First enter the case number and then input variables corresponding to each case. Other than the scenario name each item is simply input Financial Modeling Workbook

7. Step 2: Select Output Variables • In second step select a series of output variables. • The output variables must be one row above and one column to the right of the data inputs. • The output variables can be a series of income or cash flow variables for each year of the model. Here, each output variable comes from the financial model Financial Modeling Workbook

8. Step 3: Input a Scenario Number • The next step is to enter a row number that will be used to select the scenario. To begin simply type in a row number somewhere in the sheet. Financial Modeling Workbook

9. Step 4: Use the Index Function to Select the Scenario • Use the index function and select only one column. Use column series and fix the row number as follows when entering the index command. • INDEX(Column series,Fixed Row #) The index command selects the entire row number for the row number selected Financial Modeling Workbook

10. Step 5: Link the Input Variables to the Index Row • This may be the most confusing part of the process. You should go to the model and then put an = sign in the input variables that links to the index row of the scenario table Link the input variable to the scenario sheet Financial Modeling Workbook

11. Check of Analysis After Step 5 • To make sure the analysis is working, change the row number that you input and the variables should change. This is illustrated below When you change the row number, all of the output variables should change after you linked the variables Financial Modeling Workbook

12. Step 6a: Type in the Row Numbers to Set-up the Data Table • In the next step, you should manually type in the row numbers in the column to the right of the input data. This will allow you to make a data table that uses the row number as the column input. Type in the row number of the scenario between the input and the output parts Financial Modeling Workbook

13. Step 6: Create a Data Table • In this step, shade the area that includes the row numbers and all of the output variables. Then use the data, table menu and enter the row number as the column input as shown below. The column number for the data table is the row number input Financial Modeling Workbook

14. Completed Scenario Analysis • The table below illustrates the scenario analysis where the inputs is shown next to the output Financial Modeling Workbook

15. Making a Graph with Scenario Analysis • To make a graph with scenario analysis, follow the following steps: • 1 Shade the first two rows and make a graph • In the series part of the graph, link the series names to the scenarios • Make a title on the graph from the top of the data table • Make a combo box to show alternative scenarios Financial Modeling Workbook

16. Graph Step 1 • Shade the first two rows. This will compare the fixed base case on the second row with other rows. Shade and then press F11 Financial Modeling Workbook

17. Graph Step 2: Series Names from Scenario Inputs • Right click on the graph and then link the first name to the index value and link the second name to the base case. Financial Modeling Workbook

18. Graph Step 3: Enter the Chart Title • Enter the chart title using the same process as described in the break-even section Financial Modeling Workbook

19. Graph Step 4: Create a Combo Box • Create a combo box with range names as and copy the combo box to the graph. The cell link is the row number and the input range is the list of scenarios Financial Modeling Workbook

20. Creating a Tornado Diagram This shows a step by step process to create a tornado diagram Financial Modeling Workbook

21. Use data from the scenario analysis and Re-create the INDEX Function • Set up a scenario analysis using the INDEX function together with the data table as described above, where the column is used in the INDEX together with the scenario number. This has two objectives. First, it allows you to maintain the scenario analysis. Second it provides a basis for creating data for the tornado diagram. Similar to the Index function in the scenario analysis Financial Modeling Workbook

22. Create an Option Button • 2. Add a form control that allows one to select between a scenario analysis and a tornado diagram (You can use option buttons where you insert a button and then select a cell link. The option buttons work well with the CHOOSE function in excel). Financial Modeling Workbook

23. Enter the Variable Number • 3. Add an input for a variable number and enter numbers across each of the variables. The variable number can be entered near the scenario number that was used in the scenario analysis and the number for each variable can be entered above or below the variable title. Variable Number Code Number for each variable Financial Modeling Workbook

24. Add TRUE/FALSE Switch Variable • Below the INDEX computation, add a variable that includes a true/false test for whether the variable in the table is the same as the variable number input. For example, say there are six variables as illustrated in the diagram above. There should be a list of numbers from one to six above the variables. The test evaluates whether the input number for the variable number equals the number associated with the variable. There is only one value that is TRUE for the list of variables. The formula for the test should use the F4 key for the variable number and compare this to each formula as illustrated below: • Variable Number Input (Fixed with F4) = Number Associated with Each Variable Financial Modeling Workbook

25. Add IF test that uses either BASE CASE or Selected Scenario with INDEX • 5. Create an IF function using the test variable. If the variable is true, then use the value from the INDEX function discussed in step 3 above. If the variable is false, then use the base case value. Using this approach, every variable remains at the base case value except the variable which equals the variable number input. The IF test has the form illustrated below: • IF(TRUE/FALSE Test, INDEX from Step 3, Base Case from Scenario Table) Financial Modeling Workbook

26. Use the Choose Variable to Link Variables to Model as with Scenario • 6. Use the CHOOSE variable together with the cell link described in step 2 to select either the INDEX value – for the scenario option, or the IF function – for the tornado option. The values in this part of the analysis should drive the financial model. An illustration of this part of setting up a tornado diagram is illustrated in the excerpt below. The last line in the table is used in the input section of the financial model. Financial Modeling Workbook

27. Create a Two Way Data Table • 7. After setting up the variable using both IF test that runs one variable at a time, set up a two way data table where one of the variables represents the variable number and a second variable represents the scenario number for the base case, low case and high case. The form of this two way data table is illustrated below: Financial Modeling Workbook

28. Compute Low vs. Base and High vs. Base • 9. For the first row, the base case output is simply repeated for in each case. Once the table is computed, the base case versus the low case and the base case versus the high case can be computed by simply subtracting the low case row from the base case row and by subtracting the high case row from the base case row. This presents the impacts of different variables which is the basis for the tornado diagram. All that is left to do is to present the data. An illustration of this step is shown below. You can see that the variable with the largest effect if variable number 5 and the second largest is variable number 2 with variable number 3 coming in last place. Financial Modeling Workbook

29. Sort Using the SMALL Function • 12. To sort a variable one can use the SMALL function or the LARGE function. In this case use the SMALL function with the absolute value shown in the table above. The mechanics of the small function is illustrated below: • SMALL(Fixed Row to Sort (Absolute Value from Table Above), Variable Number) Financial Modeling Workbook

30. Create a Sort Key with the MATCH Function • 13. Match the sorted variable against the original absolute value line so as to establish a sort key that can be used in with the INDEX function to extract sorted variables. When computing the sort key, use the MATCH function so as to create an exact match. To do this enter a zero as the match type as illustrated below: • MATCH(Sorted Single Value, Unsorted Values -- Fixed, 0) Unsorted Values Single variable Financial Modeling Workbook

31. Use Sort Key and INDEX Function to Structure Variables for Graphing • 14. To create a graph, you will need the title of each variable as the x-axis and the low versus the base and the high versus the base. To set-up the data for making a graph, use the INDEX function along with the sort key. For example, to find the title, enter the original titles (fixed with the F4 key) in the index command and use the sort key as the column number (you do not need a row number) as illustrated below: • INDEX(Original Series of Titles – Fixed, Sort Key) Financial Modeling Workbook

32. Create Graph with F11 Key • 15. Graph the sorted variables using the F11 function and change to a stacked bar chart (make sure that there is no caption next to the titles so the excel will know that this is the x-axis. Financial Modeling Workbook

33. Combo Box for Scenario Analysis • The combo box is an effective way to manage scenario analysis – it is better than the data validation. • Examples • You want a series of oil prices (base case, downside case, upside case) and the price vary year by year. • You want to establish a series of scenarios – downside, upside, etc. • You want to create sensitivity analysis on variables – e.g. change the interest rate from 5% to 10% by 1%. Financial Modeling Workbook

34. Use of Combo Box • The combo box can be used effectively with range names if you want a fancier list box. Steps: • 1. Find combo box from the view toolbars, forms menu. • 2. Use an range name and the format control to assign the list (you could do this without a range name, but a range name works well). • 3. Assign a cell to link the number of the selection to. This puts the number of the selection in a cell • 4. Use the index, offset or choose command and the range name to find the name and use it elsewhere Financial Modeling Workbook

35. Starting the Combo Box • As with the other commands, it all starts with the View, Toolbars, Forms: • Find the Combo Box • Click the Combo Box Selection (a plus sign appears) • Drag the plus sign to make the combo box appear Financial Modeling Workbook

36. Combo Box and Choose or Index • In these examples, the combo box is used with the choose command or the index command to run scenarios. • Note that when the cell link is used, the cell link can be changed independently (without the combo box) and the selection of the combo box will change. • Multiple combo boxes can refer to the same cell link – so you can change the scenario from multiple locations in the spreadsheet Financial Modeling Workbook

37. Index • The index command is related to the cells command in the macro discussion below. It can be used to find the value of a cell in a table. The syntax is cell(table,row,col) • Index(range_name,1,1) • Index(matrix,5,5) Use of Index with range name Financial Modeling Workbook

38. Choose • The choose function is helpful for scenario analysis. • Input a number that defines a scenario • The choose command will choose the row out of a table depending on the scenario choosen Financial Modeling Workbook

39. Choose • Choose(index,number …) Use of Choose Command with range names to Select the Scenario Financial Modeling Workbook

40. Combo Box and Offset Example • Use the Combo Box to Find the Row Number of the Option • Use the Offset Command with anchor and the number for the row offset. The column offset is zero and the size is 1,1. • Offset(Anchor Cell, Rows Down, 0,1,1) Financial Modeling Workbook

41. Getting Back the Combo Box Information with the Index Command • When you use a combo box, you often want the result of the combo box in a cell for presentation, sensitivity analysis, graphs etc. • To do this, use the index command, where the range is the range that you used for setting-up the combo box and the row or column number in the index function is the cell reference • Example: index(range,row number) Financial Modeling Workbook

42. Combo Box Notes • Works well with the offset command – the cell link can tell you how many rows to go down or how many columns to go across • The range name in the combo box must be vertical. You can use the transpose function explained below to convert from horizontal to vertical. • Caution: You need to have the input range for display in columns rather than rows. Financial Modeling Workbook

43. Combo Box Hints and Warnings • If you use range names in a combo box, excel does not automatically pick-up the combo box – you must type the range name directly. • Range names are effective in combo boxes because the range names allow you to copy the combo boxes to other sheets (otherwise you cannot copy the boxes) • You can use the Shift, Cntl, Spacebar to copy multiple boxes to another sheet Financial Modeling Workbook

44. Combo Box Warnings • Sometimes, you want the cell link of the combo box to be a formula rather than a fixed number. • Be very careful with this, because when you run the combo box, the formula is lost. • To solve this problem, make another cell and use an if test or a choose command to solve the problem. • Another solution to the problem is to attach a macro to the combo box (this also applies to the spinner box and the scroll bars). Financial Modeling Workbook

45. Option Buttons • Option Buttons can be useful if there are a set of different options (more than only a true and false). When you put multiple option buttons into your sheet, only one is selected. Financial Modeling Workbook

46. Option and Check-Box Example • T Option buttons attached to a simple macro that moves to a different part of the sheet Financial Modeling Workbook

47. Attaching a Macro to a Scroll Bar and a Spinner Box • Quite often, the scroll bar or the spinner box is used along with other elements of a spreadsheet. For example, you may require some formulas to be over-ridden with the scroll bar. • In these cases, it is a good idea to attach a macro to the spinner box and be sure to also use range names to make the macro flexible. Financial Modeling Workbook

48. Data Tables • Data tables can be effective in presentation of results when you want to test a number of scenarios. • Data tables are effective in financial models for break-even analysis, for sensitivity analysis and for credit analysis. • For example, you can see what happens to the year by year debt to capital ratio when the price is lower. Financial Modeling Workbook

49. The General Concept of Data Tables -1 • Before using data tables, you may have done the following when computing scenarios: • Run the model with a certain variable • Copy and then paste special the an important variable to another part of the spread sheet • Run the model with another value for the input variable • Copy and paste special the output variable with the second value of the input variable to section of the spreadsheet variable with the scenarios Financial Modeling Workbook

50. The General Concept of Data Tables -2 • The data tables do this for you. They work as if you keep re-running the excel model with different input variables and then copying and pasting special the output variables to another area of the sheet. • Therefore, to operate a data table, you need to know the output variable that you want to report and the input variable (or variables) that you want to adjust. Financial Modeling Workbook