Sensitivity Analysis in Excel
Originally published: 29/05/2020 08:10
Publication number: ELQ-32687-1
View all versions & Certificate

Sensitivity Analysis in Excel

A tutorial on using Excel's Data Tables to conduct Sensitivity Analysis on a P&L

Sensitivity Analysis is the testing of the inputs in a profitability function to determine the influence each has on the output (NPV). Once all inputs have been quantified and ranked, they can be applied to any business model so the user has a much clearer idea of the "levers" available, and exactly how sensitive they are to adjustment.

In Excel, Data Tables are a convenient way to see the outputs generated from any number of possible inputs (i.e. variables). By viewing the inputs vs. outputs on a graph, we can determine the degree of sensitivity of the output (i.e. the NPV) on changes to the input (i.e. variables such as Price or Variable Cost per Unit).

This tutorial presents the user with both single-variable Data Tables and dual-variable Data Tables, and walks them through how to use both types to derive a sensitivity analysis on any input variable in the NPV function, including for example:

● Unit Price
● Units Sold
● Sales Growth
● Var. Costs % of Sales
● Fixed Costs
● Beginning Land Value
● Ending Land Value
● Cost of Capital

Included in the tutorial are step-by-step instructions on how to:

1. Set up an NPV function
2. Compose a data table
3. Execute a data table
4. Graph the results
5. Interpret the results

This Best Practice includes
1 Excel file

ExcelModels offers you this Best Practice for free!

download for free

Add to bookmarks


4.7 / 5 (6 votes)

please wait...