Any good financial model will usually contain scenario and sensitivity analysis functionality, at least to some degree. Scenarios are an important part of financial modelling, and the reason we have left this to near the end of the book is that it is usually a task performed at the end of the model-building process. If the model has been properly designed using best practice, it is not difficult to add, edit, and change scenarios in the model.

Scenarios and sensitivity analysis are great ways to insulate your model from risk. What would be the absolute worst that could happen? If everything that can go wrong does go wrong, will my business/project/venture still be okay? There are usually effects and interactions between multiple variables that may change in the model.

Scenarios can assist with decision analysis. They are laid out in advance so that decision-makers can see the expected impact of each course of action. How close to reality these scenarios are really depends on the accuracy of the assumptions implicit in the model—but that's another story! (See “Document Your Assumptions” in Chapter 3.)

Scenario analysis is a very important part of financial modelling—in fact, in some cases, being able to perform a scenario analysis is almost the whole point of building a financial model in the first place! Many of the principles of best practice in financial modelling discussed in earlier sections were to ensure that the model is set up in such a way that scenarios can easily be included in our model.

There are several different technical methods of creating scenarios and sensitivities in financial models, which we will discuss, but all scenarios involve changing input variables to see the impact of the change on the model outputs. By following good practice when building the model in the first place—particularly when it comes to linking to the source, and only entering data once—creating scenarios and sensitivities in the model is quite simple. (See Chapter 3, “Best Practice Principles of Modelling”.) With a well-built model that has all inputs linked to outputs, it is relatively easy to change inputs and watch the outputs change.

Building scenarios is a task normally best left to the end of the model-building process, because we need to get the model design and layout finalised and make sure the model is working correctly before we can change the input assumptions by adding scenarios. As with many complex model functions, perform a simple task first, then test it, check it, and make sure it is correct, before adding more complexity, such as scenarios.

WHAT ARE THE DIFFERENCES BETWEEN SCENARIO, SENSITIVITY, AND WHAT-IF ANALYSES?

Scenario analysis, sensitivity analysis, and what-if analysis are really only slight variations of the same thing.

A sensitivity analysis, otherwise known as a what-if analysis, in financial modelling refers to the process of tweaking one key input or driver in a financial model and seeing how sensitive the model is to the change in that variable. Scenarios, on the other hand, involve listing a whole series of inputs and changing the value of each input for each scenario. For example, a worst-case scenario could include interest rates increasing, the number of new customers being less than expected, or unfavourable exchange rates.

Scenarios should really focus on the area or assumption around which we have the least certainty. For example, let's say we are analysing the costs of reducing carbon emissions:

In this very simple example, you could model the prices that you think are most likely and then tweak each of these up or down.

It is possible to create many more than three scenarios in a financial model. I'm often asked how many scenarios should be in a model, and it really depends on the amount of time and the degree of detail and certainty that are required for the model. Three scenarios, as shown in this example, should be a minimum. It's possible to have 20, 30, or even 50 scenarios modelled, but it does become rather unwieldy and confusing so unless you really need them, I'd probably recommend sticking to around a dozen scenarios.

OVERVIEW OF SCENARIO ANALYSIS TOOLS AND METHODS

Many people will create a financial model and save it as a base case. Then they change all the numbers and save it as a worst case. Then they change all the numbers again and save it as a best case. Whilst this method will work, it's not a very efficient way of performing scenario analysis. If a subsequent change needs to be made to the model, it will need to be made several times, and there is a high possibility that an error will be made between the different versions.

Let's take a look at the technical methods available in Excel for creating scenarios and sensitivities, which will provide a much better way of building models. Essentially, there are three ways:

1. Manual drop-downs
   1. In-cell drop-downs (using data validation)
   2. Object drop-downs (using combo boxes)
2. Scenario Manager
3. Data Tables

Manual Drop-Downs

Creating scenario analysis using manual drop-down boxes means that you can only view one scenario at a time.

In-Cell Drop-Downs (Using Data Validation)    In an in-cell drop-down, the value sits within the cell of data validation drop-down box. See Figure 11.1.

In this case, the modeller has limited the valid entries to this cell to one of three options: Best Case, Base Case, or Worst Case. This is the easiest and most commonly used type of drop-down box.

See “Bulletproofing Your Model” in Chapter 7 for how to create a data validation drop-down box, and “Comparing Scenario Methods” later in this chapter for a practical exercise on how to use them in scenario analysis.

Object Drop-Downs (Using Combo Boxes)    There is very little difference from the user perspective with a combo box drop-down, but it's built very differently. See Figure 11.2.

This type of box is an object that sits on top of the sheet, rather than within the cell itself. If you look very closely, you can see that the box is sitting across several cells instead of within a single cell. This type of box is a little more difficult to build, but is easier for the user, as the drop-down arrow appears all the time, rather than only when the cell is selected.

See “Form Controls” in Chapter 7 for how to create a combo box drop-down, and “Comparing Scenario Methods” later in this chapter for a practical exercise on how to use them in scenario analysis.

Scenario Manager

Scenario Manager is an Excel tool in which you can create multiple scenarios. Once it is set up, the user can select a scenario, and input cells will change automatically. Scenario Manager is fairly limited and is not particularly helpful in large and complex models. It is therefore not a very widely used tool for scenarios in financial modelling. See Figure 11.3.

Scenario Manager is a tool that has been grouped together with Goal Seek and Data Tables in the What-if Analysis section of the Data tab. Being grouped with other tools that are so useful would lead the aspiring modeller to believe that Scenario Manager is also a critical tool to know. However, despite its useful-sounding name and the good company it keeps, Scenario Manager is quite limited in its functionality and is not particularly helpful in large and complex financial models. It is therefore not commonly used by expert financial modellers; however, for the sake of completeness, we will cover it very briefly in this chapter.

Let's take a very simple example of a person creating her personal budget for next year. Let's assume that she does not know what her mortgage payments will be, due to the changing rate of interest, or how much her travel railcard will cost.

You can define different scenarios and then switch between them to do what-if analysis to see if she will end up in debt or be able to afford a holiday. Scenarios work best on complex spreadsheets where there is a large knock-on effect from changes in the variable data.

Scenarios are created and managed using the Scenario Manager in the following 13 steps:

1. Create and format the spreadsheet as shown in Figure 11.4. You can leave the input variable cells in B6 and B8 empty.

   

2. On the Data tab, in the Data Tools section, click on the What-if analysis icon, and select Scenario Manager from the drop-down list.
3. This will bring up the Scenario Manager dialog box.
4. Click on the Add button to create a new scenario.
5. This will bring up the Add Scenario dialog box.
6. Enter a name for the first scenario into the Scenario Name box (i.e., Base Case).
7. Enter the cell references for the variable cells into the Changing Cells box. Use absolute references and separate each reference with a comma (if there is more than one), but don't use spaces. You can actually hold down the Control key and click on each cell in the spreadsheet to insert the references into the box. Click OK.
8. This will bring up the Scenario Values dialog box.
9. Enter the variables' values for this scenario (i.e., Base Case). For example, $15,000 for mortgage and $2,000 for travel. Click OK.
10. This will take you back to the Scenario Manager dialog box.
11. Follow the previous steps again to create each scenario (i.e., Worst Case: $20,000 for mortgage and $3,000 for travel; Best Case: $10,000 for mortgage and $1,500 for travel).
12. When you have created all the scenarios, you can use the Scenario Manager to view each scenario.
13. Scenarios are sheet-specific, meaning they only exist in the sheet where you created them.

As we can see, Scenario Manager is a rudimentary tool, which simply changes hardcoded numbers. It's not very easy to see, display, or print the different options unless we go into the Scenario Manager tool. Using the summary tool creates a summary report of the scenarios created, but they are not dynamic or interactive and are therefore of little use as a modelling tool. The biggest downfall of Scenario Manager is that the user cannot see the results on the sheet unless she actually goes in to view the scenarios.

Using Data Tables for Sensitivity Analysis

Data tables are one of the more advanced and complex financial modelling tools. They can be used for scenarios and sensitivity analysis, but they are not as commonly used as drop-down scenarios, mostly because users don't know much about them. Because data tables use array formulas, they are unlike most other formulas in that you cannot trace dependents, and they are very difficult to follow unless you are familiar with them.

Note that a modeller who is not familiar with data tables will be unable to edit the table, or make any changes.

Let's create an interest rate calculator upon which we can test the sensitivity of monthly repayments to changes in interest rates and loan terms.

1. First, set up the model with the hardcoded input assumptions, as shown in Figure 11.5.

   

2. In cell B12, use a PMT formula to calculate the monthly repayments. See “Loan Calculations” in Chapter 6 for more information on how to use this function.
3. Your formula should be . The function returns a negative value because this is an expense. For our purposes, change it to a positive value by preceding the function with the minus symbol.

One-Variable Data Table    The data table presents a body of data derived from a function. The rows and columns of the table are drawn from one or two of the inputs or variables fed to the function. In this case, the data comes from the results of the PMT function, using the interest rate variable (cell B8).

1. We will decide what the column elements will be by entering them in cells E7 to E12. For this exercise, use 6.00 to 8.50 percent in increments of half a percent, as shown in Figure 11.6.

   

2. Merge cells D7 to D12 and change the orientation under the Alignment tab under Format Cells if you wish to have the interest rate title oriented vertically, as shown in cell D6 of Figure 11.6.
3. Enter in cell F6 the formula , which is the cell containing the PMT function. The table, when created, will use the PMT function to populate the table according to the values in the input column (this will become clearer once the table is populated).
4. Highlight cells E6:F12 as shown in Figure 11.6. You must highlight all the cells for it to work. Select What-if Analysis from the Data tab. Choose Data Table from the options that appear.
5. The Data Table dialog box will appear. Because we are only doing a one-variable data table, we only need to enter data for one of the interest rate or term variables, but which one depends on whether our input variable is arranged in a row or a column. Because it is in a column, we should use the Column input cell field. Link this field to the input field for the interest rate (cell B8).
6. Your dialog box should look like Figure 11.6.
7. Click OK, and your data table will populate. Note that the formula in the cells will have curly brackets around it, denoting that it is an array formula: . You will not be able to edit the cells.
8. Your sheet should look something like Figure 11.7.

Two-Variable Data Table

1. Now, let's change this to a two-variable data table. Clear cells F7:F12. You will need to highlight and clear them all at once, as you cannot change or delete part of a data table.
2. With a two-variable table, the output cell needs to be at the top left of the table, at the intersection of the row and column variables. Therefore, cut and paste cell F6 to cell E6.
3. In cells F6:I6, enter the number of years you wish to test in your data table. For this exercise, enter the values 20, 25, 30, and 35 across the table. Change the formatting as necessary. We now have the makings of a table with the term across the top row and the interest rate down the left-hand column, as shown in Figure 11.8.

   

4. Now highlight the table area cells E6:I12 as shown. You must highlight all the cells for it to work.
5. Select Data Table from the drop-down list under the What-if Analysis icon in the Data Tools section on the Data tab.
6. Your row input cell will be the entry field for the values shown in the row (the term in years), and the column input cell will be the entry field for the values shown in the column (the interest rate). Your dialog box should look like Figure 11.8.
7. Your table should now look like Figure 11.9.

   

8. You might wish to change the font in cell E6 to white; while it is required for the data table to work, it does not add any value visually (and is simply confusing).

Limitations of Data Tables    We can see from the example above that data tables are a great way to look at multiple scenarios or sensitivity analyses one at a time. Instead of manually changing the interest rate or the term of the loan, we can display at a glance the impact of these changes.

However, data tables have a few limitations that make them inappropriate for some scenarios or sensitivity analysis situations. These three limitations are:

1. The inputs and outputs need to be on the same page.
2. You have a limitation of showing only two inputs and one output at a time. This is not a restriction with other forms of scenario analysis.
3. Formula auditing (trace precedents and trace dependents) doesn't work very well in data tables.

Data tables are extremely useful when, as in the example shown above, you want to see the incremental change of one or two inputs on a single output. For example, how much does my profit margin change if I change my price from $450 to $460, $470, $480, $490, or $500? A data table would not be an appropriate solution if the output of your financial model were a full set of financial statements, for example. In this situation, a drop-down scenario would be most appropriate.

ADVANCED CONDITIONAL FORMATTING

A data table is a good opportunity to use conditional formatting with a formula. We can use advanced conditional formatting to enhance a two-variable data table, making it more visually interesting and interactive for the user.

Our final result will look similar to Figure 11.11, using the term of the loan and the interest rate as the two variables to drive the conditional formatting. You can find the completed version as shown in Figure 11.11 at www.plumsolutions.com.au/book. If you change the interest rate in cell B8 from 7.5 to 7 percent, the row currently being highlighted will change from row 10 to row 9. Similarly, if the term of the loan is changed in cell B10 from 25 to 30, the column currently being highlighted will change from column G to column H. In this way, we can see at a glance which inputs have been selected currently in the model.

For a review of the basics, see “Conditional Formatting” in Chapter 7. As you know, conditional formatting allows you to apply formats to a cell or range of cells and have that formatting change depend on the value of the cell (or the value of a formula).

In this particular example, we want the interest rate and term to intersect at the appropriate repayment instalment. As the variables to the PMT function are changed, the intersection point will move to spotlight the repayment amount.

One way we can accomplish this is to apply conditional formatting by means of a formula to each row within the table and, likewise, to each column. For instance, in Figure 11.12, we can stipulate that if cell E10 is equal to the interest rate (B8), then Excel must colour row 10 within the table.

This would require 10 formulas if we were to cover each of the six interest rates and each of the four terms. However, we can accomplish the exercise with two formulas only: the first locks either the rows or columns with absolute references; the second allows other elements of the cell address to float across the row or up and down the column by using relative references. This exercise illustrates how this is done.

1. Select the entire table (cells E6 to I12). Choose Conditional Formatting on the Styles section of the Home tab. Select New Rule from the drop-down list, and then “Use a formula to determine which cells to format”.
2. Note that E in Figure 11.13 is an absolute reference to hold us in column E, but that the row number (presently 6) is free to roam down within the column.

   

3. Format the cell as required using the Format button.
4. Repeat the exercise to format the terms. In this case, the formula will reverse the relative and absolute references like this: E$6, locking in row 6. The rules should look like Figure 11.14.

   

5. To modify the conditional formatting, highlight the relevant cells (in this case the entire data table). Choose Manage Cells … from the Conditional Formatting menu.
   • Alternatively, we can highlight just the single cell where the inputs for the two variables intersect using an ordinary Conditional Formatting Rule, as shown in Figure 11.15.

     

COMPARING SCENARIO METHODS

Let's create a very simple property development feasibility model to demonstrate the difference between the methods of scenario analysis. You are considering purchasing an empty block upon which you are considering developing a residential villa complex. You can find this template, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

The assumptions are:

• The land will cost $4.3 million to purchase.
• Council contribution fees will be $750,000.
• The properties that we develop will be 5,000 square metres in total.
• Building costs will be $1,500 per square metre.
• We will sell the properties for $3,000 per square metre.
• Sales commission to the estate agent will be 2.5 percent of the sale price.

To create the working model, follow these eight steps:

1. Lay out the model, and enter the assumptions as shown in Figure 11.16.

   

   • These assumptions are all input variables, so these should be formatted as inputs with blue font and beige background.
2. Calculate the income from selling the properties in cell C11 by multiplying the sale price per square metre by the total number of square metres. Your formula should be .
3. Similarly, calculate the building costs in cell C14 by multiplying the building cost per square metre by the total number of square metres. Your formula should be .
4. Calculate the sales commission by multiplying the income by the sales commission. Your formula should be .
5. Add the expenses in cell C17.
6. Calculate the net profit in cell C19 by deducting the expenses from the profit.
7. Calculate the profit margin in cell C20 by showing the profit as a percentage of the revenue. Your formula in cell C20 should be .
8. The calculations in your model should look something like those in Figure 11.16 so far.

We have now completed the workings of the model and are ready to add some scenarios. Note that this is a very simple one-page model, which we will use to demonstrate how to build scenarios. Your model can be a lot more complex than this, containing many more pages and calculations, but the methodology for creating a scenario will be exactly the same.

Manual Sensitivity Analysis

The two input variables that we think are the most likely to change are the building cost and the sale price per square metre. Note that because of the way we have built this model, with these assumptions hardcoded and all the calculations linking to these inputs, we can quite easily change these inputs manually and this will affect the output of the model. We can see that if the building costs were to decrease from $1,500 to $1,200 per square metre, this would increase the profit margin to 24 percent. Additionally, if the sale price were to increase from $3,000 to $3,300 per square metre, this would increase the profit margin further to 31 percent.

You can see that we can also manually change the sales commission and the total number of square metres in the development, although we have determined that these are unlikely to change.

Creating Built-In Scenarios Using a Data Validation Drop-Down    Tweaking these input variables manually is a form of sensitivity or what-if analysis, but it's difficult to control and not very auditable. It's much better practice to build some predefined scenarios into the model, and allow the user to switch between the scenarios using a drop-down box.

Let's build some scenarios using this 10-step method:

1. In cells A4 to A6, enter Best Case, Base Case, and Worst Case.
2. Enter in hardcoded assumptions, as shown in Figure 11.17.

   

3. At the very top of the model in cell B1, create a data validation drop-down box that the user can select from to choose the scenario they'd like to see. For instructions on how to do this, see “Using Validations to Create a Drop-Down List” in Chapter 7.
4. The data validation should be linked to the source cells, as shown in Figure 11.18. The drop-down box should then look like Figure 11.19.

   

   

5. Now we need to change the input assumptions in cells B8 and C8 from hardcoded inputs to formulas that will change depending on the scenario that has been selected from the drop-down box.
6. There are several different functions that we can use to achieve this; an IF statement, a VLOOKUP, or a SUMIF will all return a similar result. If you choose a SUMIF, the formula in cell B8 should look like this:
   • 
7. If you've got the cell referencing correct, you can simply copy the formula across to cell C8 without changing the formula.
8. Change the cell and font colour of cells B8 and C8 to denote that they now contain formulas instead of hardcoded values.
9. Your completed sheet should now look something like Figure 11.20.

   

10. Practice changing the scenario drop-down in cell B1, and watch the numbers change. Under the best case, you'll be making a 29 percent profit, and in the worst case, you'll have a loss of 5 percent.

Creating Scenarios Using a Combo Box Drop-Down    Now that we've got our scenarios working, let's take a look at what we could achieve using a combo box drop-down instead. To review how to create a combo box, refer to the “Form Controls” section in Chapter 7.

If you are using the template provided, choose the next tab across, called “Combo Drop-Down”. If you are creating this model from scratch, then leave the scenario model we've just created intact, and make a copy of the sheet by right-clicking on the sheet tab at the bottom. Select Move or Copy and then select Create a Copy and click OK.

1. If you have copied the sheet, then remove the data validation drop-down we created in cell B1. The easiest way to do this is to copy a blank cell and paste it over cell B1. This will mess up your calculations, but don't worry; we'll fix them later.
2. Now create a combo box drop-down in the same area. For instructions on how to do this, see the “Combo Boxes” section in Chapter 7.
3. Right-click on the combo box and select Format Control. On the Control tab, under Input Range, select the words Best Case, Base Case, and Worst Case in cells A4 to A6.
4. Also choose a cell link, which is where you want the output cell from the drop-down box to be. We often choose the cell behind the combo box, in this case, cell B1, as this will be hidden behind the drop-down, and unlikely to be accidentally deleted. You may need to move the combo box out of the way temporarily (select the combo box whilst holding down the Control key to do this).
5. Your Format Control dialog box should look like Figure 11.21.

   

6. Click OK. Click away from the combo box, and then select it again. Practice changing the options from the drop-down, and you'll notice the number in cell B1 changing. See Figure 11.22.

   

7. What we need to do now is to change the formula in cells B8 and C8, which are driving the calculations in the model. The SUMIF (or IF, VLOOKUP, or whatever you used) function we created earlier will not work because cell B1 no longer contains the name of the scenario. This now needs a formula that will select the first option if it contains a 1, the second option if it contains a 2, and so on.
8. Again, there are several functions we can use to achieve this, a CHOOSE or an INDEX to name two. If you use a CHOOSE function, your formula in cell B8 will be . If you use absolute referencing, you can copy this across to cell C8.
9. Practice changing the drop-down box and make sure it works properly. Your model should now work in exactly the same way as it did earlier, except that we are now using a combo box instead of a data validation drop-down.
10. Hold down the Control key, and move the combo box so that it covers the cell link output number in cell B1.
11. Your sheet should now look something like Figure 11.23.

    

What's the Difference Between a Data Validation and Combo Box Drop-Down?    From the user perspective, the data validation and the combo box drop-down should be almost indistinguishable from each other. They both work in the same way: the user selects from a drop-down list, and the model will change. However, there are a few key differences from the model developer's perspective:

• The combo box takes longer to build, as it requires inputs and outputs.
• As we can see in the previous example, the formulas we use to link to it are quite different.
• The combo box can easily be embedded in a chart.
• We can assign macros to a combo box so that unbeknownst to the user, a macro is launched as soon as an option is selected from the drop-down.
• The source data that appears in the combo box must be oriented vertically, not horizontally. This is an important point when designing the model. If you plan to use a combo box for scenario analysis, make sure that the input assumptions are listed vertically. Sources for data validation drop-down boxes can be oriented either horizontally or vertically.

As shown in Figure 11.24, combo boxes will not work if the source data is oriented horizontally instead of vertically.

Creating Scenarios Using a Two-Variable Data Table

Let's take a look to see if we could achieve the same result on our property development feasibility model using a data table method of scenario analysis instead of a drop-down box.

If you are using the template provided, choose the next tab across, called “Data Table”. If you are creating this model from scratch, then leave the scenario model we've just created intact, and make a copy of the sheet by right-clicking on the sheet tab at the bottom. Select Move or Copy, then select Create a Copy and click OK.

Instead of creating base, best, and worst-case scenarios from which users need to select the scenario that they want to display, we are instead going to create a single matrix in six steps, where users can see simultaneously the results of different inputs. If you've copied the sheet, then you'll need to change the design of the model to look like Figure 11.25 in order to create this scenario analysis using a data table instead.

1. To create the titles for the data table inputs, use Merge Cells, and then for the vertically oriented title, wrap text, and right-hand click. Select Format Cells and on the Alignment tab change the orientation. To show the title on the right-hand side of the cell instead of the centre, choose Right text alignment instead of Center under the Text Alignment area.
2. Once we have the layout right, then link cell B2 to profit margin in cell C20, as this is the output we want to show in the data table.
3. Now create a two-variable data table by selecting the input assumption for sale price for row and building cost for column. For details on how to create a data table, see “Using Data Tables for Sensitivity Analysis” earlier in this chapter.
4. Highlight the entire data table, and go to the Data Table tool under the Data tab, in the What-if Analysis section.
5. The Data Table dialog box will look like Figure 11.26.

   

6. When completed, the data table will look like Figure 11.27.

   

This gives us exactly the same results as the drop-down box options, but instead of having to flick through the drop-downs, the user can see at a glance all the results of the different scenarios simultaneously.

In this situation, the data table is an appropriate option for displaying the sensitivity of our profit margin to changes in inputs (because the inputs and outputs are all on one page, and we are only interested in a single output). We do lose some detail by using the data table method, as we cannot see, for example, the building cost amount under each different scenario, only the final outcome.

This feasibility model shows clearly the difference between three of the scenario methods. We have chosen not to use Scenario Manager because, even though it is perfectly possible for this model, it is not a particularly useful tool. In this example, the data validation drop-down, combo box drop-down, and data table scenario analysis methods are all suitable. However, it really depends on the size, layout, and required output of the model as to which method is best to use.

ADDING PROBABILITY TO A DATA TABLE

After completing the last exercise, we can see from the results of our sensitivity analysis—as shown in the completed data table in Figure 11.27—that the expected profit margin from our financial model will be anywhere between a profit of 29 percent and a loss of 5 percent. In order to actually help the decision-making process, a good financial modeller will try to narrow down the possible outcomes. One way of doing this is to apply some probability to each outcome and then calculate the probability-weighted predicted outcome.

Let's assume that the base case is the most likely, and assign a probability of 50 percent to that outcome under each variable. We then need to assign probabilities to the worst and best cases as well.

1. Complete the data table again, this time showing the net profit, instead of the profit margin.
2. In cells I2:K2 and H3:H5, enter the probabilities as shown in Figure 11.28, and make sure that the probabilities you enter add up to 100 percent.

   

3. In cell I3, we need to calculate the probability of those two combinations occurring. Enter the formula .
4. Copy cell I3 across the range I3:K5 and check that all nine cells add up to 100 percent, as shown in cell L6.
5. Next, we can calculate the exact dollar value of each combination. In cell O3, enter the formula and copy it across the range O3:Q5.
6. We can now calculate the probability-weighted predicted outcome in cell R6 with the formula .
7. The result should be $1,886,250, as shown in Figure 11.28.

SUMMARY

In this chapter, we have covered the different tools that are available in Excel for scenarios and sensitivity analysis. Depending on the inputs and outputs for the scenarios, different tools are most appropriate.

As discussed, the drop-down method is the most popular, as that allows for many different scenario inputs as well as outputs. Scenario Manager is not a particularly useful tool, and data tables are most useful for sensitivity analysis where there are a maximum of two input variables, and only one output that needs to be shown. Whilst there are several different scenario tools available, the methodology and logic behind creating the scenarios remain the same.