7. Sensitivity Analysis

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Sensitivity tables help you understand the impact that changing various input values can have on the final output - in our case additional profit per plane.

Summary

  1. Creating a Sensitivity Analysis (00:04)

    A sensitivity analysis reruns your model multiple times using different input values, and displays the corresponding output values in a table. It helps you better understand the relationship between the inputs and outputs of your model. You can create a sensitivity analysis for two inputs in your model using an Excel data table. If your model has more than two inputs, then you can conduct multiple sensitivity analyses.

    To create a sensitivity analysis, we start by creating a table where the row labels are possible values for one of our model inputs, and the column labels are possible values for another input. These possible values are derived through business intuition and analysis of the problem. Each value in the cell will contain the output value of our model using the relevant values of the two inputs. We also link the top left cell in this table to the desired output value.

    With the table set up, we create a data table using the shortcut Alt A, W, T. We specify the row input cell and column input cell using the cells from the model that correspond to the two inputs we are considering. Our table is then populated with the output values for each combination of the two inputs.

  2. Adding Conditional Formatting (02:14)

    Conditional formatting can be used to make the table easier to understand. To do this, we select the cells in the data table and press Alt, H, L. Selecting an option, such as a color scale, makes the table easier to read and generate insights from. 

    When we conduct a sensitivity analysis, we analyze the impacts of a change in two model inputs. However, all the other inputs still have an impact on the model. If we adjust one of the other model inputs, it will affect every cell in the data table. This is important to bear in mind when conducting sensitivity analysis.

Transcript

Once you have a sample model built, it's worth conducting some WHATIF analysis to better understand the relationship between your decision variables, projections, and your outcomes, in this case, additional profit. A common modeling tool for insight is sensitivity analysis. A sensitivity analysis reruns your model multiple times for different input values, which are then displayed on a table. To conduct a sensitivity analysis, we'll use the Excel data table, which allows us to conduct a sensitivity on two inputs simultaneously. My two table inputs will be the booking limit, which I control and the number of no-shows, which I don't control. However, I could have chosen any of the projection values from this list, and typically, I would run multiple sensitivities for these values as well. Let's start off by creating a space for our sensitivity analysis. I'll remove the grid lines in this space, which Alt + H, H and I'll also add a thick border with Alt + H, B, T. I'll now add in my values for the booking limit and the no-shows and to save some time I'll do this off-camera. Next, we have to link our table to the desired output value which is additional profit. So in the top left-hand corner cell of our table, I'll write equals and link this to additional profit. We're now ready to create our Excel table. So I'll select all our cells in the table and then I'll press Alt + A to enter the data tab, then W for WHATIF analysis and T for data table. This opens the data table dialogue box and asks for a row input cell. And a row input cell is simply the booking limit. So I'll select the booking limit and move on to column input cell, which is No Shows per Flight. When we press OK, our table is now populated with the additional profit values. To make our table values easier to analyze, let's apply some conditional formatting. So I'll select the cells, and I'll press Alt + H to enter the Home tab and then L for conditional formatting and I'll select the color scale from green to red. And now you can see that our table is much easier to read. Let's see what insights we can now draw from this table. From the first column, we can see that a booking limit of 180 seats makes no difference because no extra bookings would be created. So there will be no additional revenue or additional costs. A lower booking limit, such as 185 or 190 is a lower risk option and in fact, for this set of inputs, we can only gain money by implementing a booking limit of 185. Higher booking limits results in much higher risk for the company with a greater potential for profits if the no-shows are higher, but also a greater potential for losses if there are very few no-shows. It's important to remember that when you're conducting sensitivity analysis, other projections are also feeding into the model as well. For example, let's say we increase the price per ticket to 175. This significantly changes the values in our sensitivity table and makes the higher booking limit a much more attractive option. So by conducting sensitivity analysis always be aware of the other inputs that are not included in the table, but do affect your final outcome. As an exercise, try to create two more sensitivity tables replacing no-shows with price per ticket and demand. With practice, you will soon become very adept at creating sensitivity tables and gaining insights from them.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model