Microsoft Excel: What-If-Analysis - Data Tables, One-Variable and Two-Variable Sensitivity Analysis

What This Is and Why It Matters

One-variable and two-variable sensitivity analysis in data tables is a powerful tool for evaluating the impact of changes in input variables on output variables. This technique is crucial in decision-making, where small changes can have significant effects. In MS-Excel, sensitivity analysis is often performed using data tables, which allow you to test how different input values affect the outcome. Failing to perform sensitivity analysis can lead to suboptimal decisions, while mastering this technique can help you make more informed choices.

Core Knowledge (What You Must Internalize)

• Sensitivity analysis: A method for evaluating how changes in input variables affect output variables. (Why this matters: It helps you understand the impact of small changes on the outcome.)
• Data table: A table that contains multiple scenarios of input values and corresponding output values. (Why this matters: It allows you to easily compare the effects of different input values.)
• One-variable sensitivity analysis: Evaluating the impact of changes in one input variable on the output variable. (Why this matters: It helps you understand the relationship between one input variable and the output variable.)
• Two-variable sensitivity analysis: Evaluating the impact of changes in two input variables on the output variable. (Why this matters: It helps you understand the relationships between multiple input variables and the output variable.)
• Input variable: A variable that is used as input in the analysis. (Why this matters: It determines the outcome of the analysis.)
• Output variable: The variable that is being analyzed. (Why this matters: It is the result of the analysis.)
• Scenario: A specific combination of input values and corresponding output values. (Why this matters: It allows you to compare the effects of different input values.)

Step-by-Step Deep Dive

One-Variable Sensitivity Analysis

1. Create a data table: Set up a table with input values and corresponding output values. (Underlying principle: A data table allows you to easily compare the effects of different input values.) Example: Suppose you want to analyze the impact of changes in the interest rate on the monthly payment for a loan. Create a table with different interest rates and corresponding monthly payments. Common pitfall: Failing to include a wide range of input values.
2. Identify the input variable: Determine which variable you want to analyze. (Underlying principle: The input variable determines the outcome of the analysis.) Example: In the previous example, the interest rate is the input variable.
3. Analyze the output variable: Evaluate the impact of changes in the input variable on the output variable. (Underlying principle: The output variable is the result of the analysis.) Example: Analyze how changes in the interest rate affect the monthly payment.
4. Draw conclusions: Based on the analysis, draw conclusions about the impact of changes in the input variable on the output variable. (Underlying principle: Sensitivity analysis helps you understand the relationships between input variables and output variables.)

Two-Variable Sensitivity Analysis

1. Create a data table: Set up a table with input values and corresponding output values. (Underlying principle: A data table allows you to easily compare the effects of different input values.) Example: Suppose you want to analyze the impact of changes in the interest rate and loan term on the monthly payment for a loan. Create a table with different interest rates and loan terms and corresponding monthly payments. Common pitfall: Failing to include a wide range of input values.
2. Identify the input variables: Determine which variables you want to analyze. (Underlying principle: The input variables determine the outcome of the analysis.) Example: In the previous example, the interest rate and loan term are the input variables.
3. Analyze the output variable: Evaluate the impact of changes in the input variables on the output variable. (Underlying principle: The output variable is the result of the analysis.) Example: Analyze how changes in the interest rate and loan term affect the monthly payment.
4. Draw conclusions: Based on the analysis, draw conclusions about the impact of changes in the input variables on the output variable. (Underlying principle: Sensitivity analysis helps you understand the relationships between input variables and output variables.)

How Experts Think About This Topic

Instead of memorizing formulas and techniques, think of sensitivity analysis as a way to understand the relationships between input variables and output variables. By analyzing how changes in input values affect the outcome, you can make more informed decisions and optimize your choices.

Common Mistakes (Even Smart People Make)

• Mistake: Failing to include a wide range of input values. Why it's wrong: You may miss critical relationships between input variables and output variables. How to avoid: Make sure to include a range of input values that covers the possible scenarios. Exam trap: Failing to include a wide range of input values can lead to incorrect conclusions.
• Mistake: Failing to analyze the output variable. Why it's wrong: You may not understand the impact of changes in input variables on the outcome. How to avoid: Make sure to analyze the output variable and draw conclusions based on the analysis. Exam trap: Failing to analyze the output variable can lead to incorrect conclusions.
• Mistake: Failing to consider multiple input variables. Why it's wrong: You may not understand the relationships between multiple input variables and the output variable. How to avoid: Make sure to consider multiple input variables and analyze their impact on the output variable. Exam trap: Failing to consider multiple input variables can lead to incorrect conclusions.
• Mistake: Failing to draw conclusions based on the analysis. Why it's wrong: You may not understand the implications of the analysis. How to avoid: Make sure to draw conclusions based on the analysis and consider the implications. Exam trap: Failing to draw conclusions based on the analysis can lead to incorrect conclusions.

Practice with Real Scenarios

Scenario 1: Analyzing the Impact of Changes in the Interest Rate on the Monthly Payment

Question: What is the impact of a 1% increase in the interest rate on the monthly payment for a loan?

Solution:

1. Create a data table with different interest rates and corresponding monthly payments.
2. Identify the input variable: interest rate.
3. Analyze the output variable: monthly payment.
4. Draw conclusions: A 1% increase in the interest rate will increase the monthly payment by $25.

Answer: $25

Why it works: By analyzing the impact of changes in the interest rate on the monthly payment, you can understand the relationship between the two variables.

Scenario 2: Analyzing the Impact of Changes in the Interest Rate and Loan Term on the Monthly Payment

Question: What is the impact of a 1% increase in the interest rate and a 1-year increase in the loan term on the monthly payment for a loan?

Solution:

1. Create a data table with different interest rates and loan terms and corresponding monthly payments.
2. Identify the input variables: interest rate and loan term.
3. Analyze the output variable: monthly payment.
4. Draw conclusions: A 1% increase in the interest rate and a 1-year increase in the loan term will increase the monthly payment by $30.

Answer: $30

Why it works: By analyzing the impact of changes in the interest rate and loan term on the monthly payment, you can understand the relationships between the three variables.

Quick Reference Card

• Sensitivity analysis is a method for evaluating how changes in input variables affect output variables.
• Data table is a table that contains multiple scenarios of input values and corresponding output values.
• One-variable sensitivity analysis evaluates the impact of changes in one input variable on the output variable.
• Two-variable sensitivity analysis evaluates the impact of changes in two input variables on the output variable.
• Input variable determines the outcome of the analysis.
• Output variable is the result of the analysis.
• Scenario is a specific combination of input values and corresponding output values.

If You're Stuck (Exam or Real Life)

• What to check first: Make sure to include a wide range of input values and analyze the output variable.
• How to reason from first principles: Start by understanding the relationships between input variables and output variables.
• When to use estimation: Use estimation when you need to make quick decisions or when you don't have enough data.
• Where to find the answer (without cheating): Use online resources, textbooks, or consult with experts.

Related Topics

• Decision trees are a visual representation of the relationships between input variables and output variables.
• Regression analysis is a statistical method for evaluating the relationships between input variables and output variables.
• Optimization techniques are methods for finding the optimal solution to a problem.