What if...

Data Analysis in Excel

Nick Edwards

Analyst at Mynd

What is scenario analysis?

Scenario analysis is a type of what-if analysis that evaluates the impact of a dependent variable given one or more inputs.
- Asks "WHAT is this IF that?"

A diagram of inputs into a model creating outputs

¹ https://www.investopedia.com/terms/s/scenario_analysis.asp

Dependent versus independent

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 rely on inputs
- The observed output of the analysis

Example: Taxes Owed = (Total Income - Deductions) * Tax Rate

A diagram of inputs into a model creating outputs

Sensitivity analysis

Sensitivity analysis is a type of what-if analysis that evaluates the impact of a dependent variable given a range of inputs.

Is more open-ended than scenario analysis
The goal is to understand how the dependent reacts to a range of input values.

¹ https://corporatefinanceinstitute.com/resources/knowledge/modeling/what-is-sensitivity-analysis/

Sensitivity table

An example of a sensitivity table with supply on rows and demand on the columns

Sensitivity table

Sensitivity table with one column highlighted

Sensitivity table

Sensitivity table with one row highlighted

Sensitivity table

Sensitivity table where demand is equal to supply

Sensitivity table

Sensitivity table with both a column and row highlighted

Growth rate

Finds the change in x between two periods

Growth rate formula

Example: Find the growth rate if total sales in year 1 is $50M and in year 2 is $70M.

g = ($70M-$50M)/$50M
g = ($20M)/$50M
g = 0.4 or 40%

Growth rate

Using growth rates in calculations

Add 1 to g before multiplying!

Example: If total sales is $90M today, find what it would be at a 20% growth rate.

x = $90M * (1 + 20%)
x = $90M * (1.2)
x = $108M

Let's practice!

Data Analysis in Excel

Preparing Video For Download...