Overview
This lecture explains how to perform sensitivity analysis in Excel using data tables, including one-variable, two-variable, and multiple-formula setups.
Introduction to Sensitivity Analysis
- Sensitivity analysis in Excel tests how outputs change when you adjust one or more input values.
- Excel uses a feature called "Data Table" for sensitivity analysis.
One-Variable Data Table
- Requires inputs (e.g., Price, Quantity) and a formula cell using these inputs.
- Set up two columns: one for the changing input values, one for the resulting output.
- The first row under headers is for the original value; link the result cell to your main formula.
- Input different values to test in the input column.
- Select input and output cells, then use Data > What-If Analysis > Data Table.
- For one-variable, use the column input cell if your values go down a column.
- Always link the result cell to the main formula to simplify updates.
- Can also arrange data horizontally using the row input cell.
Two-Variable Data Table
- Allows testing combinations of two inputs (e.g., Price and Quantity) and their effect on the result.
- Arrange one input vertically and the other horizontally; put the formula in the top-left cell.
- Select the entire grid including formula and input values.
- Use Data Table with both row input (for top row variable) and column input (for left column variable).
Multiple Results with Data Table
- Data tables can show the effect of changing one input on several output formulas (e.g., payment, total paid, interest paid).
- Set up multiple columns, each linking to different result cells.
- Select all relevant cells, then use a data table as usual.
Tips and Caveats
- Data tables are special ranges and must be deleted or changed as a block.
- For performance, if spreadsheets slow down, set calculation options to "Automatic Except for Data Tables."
Key Terms & Definitions
- Sensitivity Analysis — Examining how output values change when input values are adjusted.
- Data Table — Excel feature allowing testing of multiple input scenarios and seeing results automatically.
- One-Variable Data Table — Data table where one input changes across scenarios.
- Two-Variable Data Table — Data table testing scenarios for two changing inputs.
- Row Input Cell/Column Input Cell — The input cells in a data table setup corresponding to row or column of changing values.
Action Items / Next Steps
- Practice setting up one-variable and two-variable data tables in Excel.
- Experiment with showing multiple outputs in a single data table.
- Adjust calculation options if you notice slow performance with large data tables.