veejay-kathuria
Sensitivity Analysis in
Excel – Practical Tips
Streamline your financial models and
make smarter decisions.
What is Sensitivity
Analysis?
Sensitivity analysis evaluates how changes in
key variables affect outcomes in a financial
model.
Purpose: Helps identify critical drivers of
results and assess risk.
Common Use Cases: Valuation models,
forecasting, and investment decisions.
Tip #1
Set Up Tables Properly
Place input variables and output data on
the same sheet.
Input values should be hard-coded or
adjusted by percentages/numbers.
This structure ensures accuracy and allows
easy manipulation.
Tip #2
Calculate Variable Effects
Adjust one variable at a time while
keeping others constant.
Track percentage changes in both the
input and output.
Example: If input increases by 10%,
measure the corresponding effect on your
output.
Tip #3
Measure Sensitivity
Use this formula:
Sensitivity = (% change in output) /
(% change in input)
A higher sensitivity indicates that variable
significantly impacts results.
Tip #4
Use Excel Data Tables
Automate the process using Excel’s Data
Table tool:
Navigate to Data → What-If Analysis
→ Data Table
Set your row and column inputs to see
results dynamically.
Saves time and reduces manual errors.
Tip #5
Optimize Calculation Options
Switch Workbook Calculation to:
Automatic Except for Data Tables.
Press F9 to refresh results as needed, ensuring
smoother workflows.
Tip #6
Summarize Scenarios
Create a summary on a separate
worksheet for better presentation.
Include key metrics and insights to
compare scenarios effectively.
Follow Veejay Kathuria for more
related to Finance!
veejay-kathuria
