What if...

1. What if...

Welcome back to Data Analysis in Excel. In this chapter we will explore the possibilities of asking, "What if...".

2. What is scenario analysis?

Scenario analysis is a type of what-if analysis that evaluates the performance of a dependent variable given certain input variables. This is called "What-if analysis" because it asks the questions "What would this be if that?" For example, if a company wants to know what their taxes would be if their income, deductions and tax rate are a certain amount, they could create a scenario analysis to answer this question.

3. Dependent versus independent

When creating scenario models, it's important to understand the difference between a dependent and an independent variable. Independent variables derive their value from outside of the model. They are the inputs of the analysis. Dependent variables derive their value from the model and therefore rely on independent variables. They are often the observed output of the analysis. For example, look at this equation. Which variables are dependent and which are independent? Taxes owed is a dependent variable in this example because it relies on three other independent variables; total income, deductions, and tax rate.

4. Sensitivity analysis

Sensitivity analysis is a type of what-if analysis that evaluates the performance of a dependent variable given a range of inputs. This is similar to scenario analysis in that they both analyze how a dependent variable will react to certain inputs. However, scenario analysis is specific to a certain "scenario", while sensitivity analysis is more open-ended because it gives a range of inputs and values. The goal of sensitivity analysis is to understand how the dependent variable reacts to a range of input values, known as independent variables.

5. Sensitivity table

Often, sensitivity analysis is presented in a table with conditional formatting that highlights the values from highest to lowest. Here is an example of the price sensitivity of supply and demand. Supply and demand are the independent variables labeled on the axes, and the price is the dependent variable, which holds the values in the table. It may look overwhelming at first, but it's simple once your eye is adjusted. Imagine that the units are for packs of bubble gum, so for example, supply can be between one-thousand and five-thousand packs of bubble gum, and the same goes for demand.

6. Sensitivity table

To read the table, first look at the column. Here, for example, the demand is 3,000. So the analyst can see all the possible prices when demand equals 3,000 packs of bubble gum.

7. Sensitivity table

Likewise, the highlighted row shows all listed prices when the supply is 3,000.

8. Sensitivity table

So now the analyst can pick a certain row and column to understand how the value changes. For example, when supply and demand equal each other, the price is 5 dollars.

9. Sensitivity table

However, when there is a shortage, and demand is 5,000 while supply is only 1,000, the price skyrockets to 25 dollars! This is why sensitivity tables are so useful; they display a wide range of outcomes that allow the analyst to understand how the inputs impact the dependent variable.

10. Growth rate

It's also important that you understand how to calculate growth rates, since we'll be using them in this chapter. A growth rate is the same as finding the percent change. Find the difference between the end and start values and divide it by the start value. For example, to find the growth rate between 50 million in 2019 and 70 million in 2020, first find the difference between 70 million and 50 million; 20 million. Then divide that by the start period; 50 million. That returns zero point four, or a 40-percent growth rate.

11. Growth rate

Using growth rates in calculations is also quite simple. Just add one to the growth rate before multiplying. For example, to find the sales of $90 million with a 20% growth rate, we would first add one to the rate of 20%, to get 1.2. Then we would multiple 1.2 to 90 million to get 108 million. Pretty simple, right?

12. Let's practice!

Awesome! You now know a lot about what-if analysis and growth rates. Now it's your turn to practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.