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

1 https://www.investopedia.com/terms/s/scenario_analysis.asp
Data Analysis in Excel

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

Data Analysis in Excel

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.
1 https://corporatefinanceinstitute.com/resources/knowledge/modeling/what-is-sensitivity-analysis/
Data Analysis in Excel

Sensitivity table

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

Data Analysis in Excel

Sensitivity table

Sensitivity table with one column highlighted

Data Analysis in Excel

Sensitivity table

Sensitivity table with one row highlighted

Data Analysis in Excel

Sensitivity table

Sensitivity table where demand is equal to supply

Data Analysis in Excel

Sensitivity table

Sensitivity table with both a column and row highlighted

Data Analysis in Excel

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.

  1. g = ($70M-$50M)/$50M
  2. g = ($20M)/$50M
  3. g = 0.4 or 40%
Data Analysis in Excel

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.

  1. x = $90M * (1 + 20%)
  2. x = $90M * (1.2)
  3. x = $108M
Data Analysis in Excel

Let's practice!

Data Analysis in Excel

Preparing Video For Download...