Create Sensitivity Table in Excel

Introduction to Sensitivity Analysis

Sensitivity analysis is a powerful tool used in various fields, including business, finance, and engineering, to understand how the output of a model or system changes when one or more input variables are changed. It helps in identifying which variables have the most significant impact on the outcome, thereby allowing for better decision-making. One of the most effective ways to perform sensitivity analysis is by creating a sensitivity table in Excel.

What is a Sensitivity Table?

A sensitivity table, also known as a what-if table, is a tool in Excel that allows users to see how different input values affect the outcome of a formula. It is particularly useful when you want to analyze the impact of two or more variables on a result. For instance, in a business scenario, you might want to analyze how changes in the price of a product and the cost per unit affect the profit margin.

Creating a Sensitivity Table in Excel

To create a sensitivity table in Excel, follow these steps:

• Open your Excel spreadsheet and select the cell where you want the output to appear.
• Ensure that the formula you want to analyze is already entered into this cell.
• Go to the “Data” tab in the ribbon.
• Click on “What-If Analysis” and then select “Data Table” from the drop-down menu.
• In the “Data Table” dialog box, you will be asked to input the row input cell and the column input cell. These are the cells that contain the variables you want to change.
• For a one-variable data table, select the cell that contains the variable you want to change as either the row or column input cell, depending on how you want to set up your table.
• For a two-variable data table, select one variable as the row input cell and the other as the column input cell.
• Click “OK” to create the data table.

Example of Creating a Sensitivity Table

Let’s consider a simple example to illustrate how to create a sensitivity table. Suppose we have a formula for calculating profit based on the selling price and the cost per unit: [ \text{Profit} = (\text{Selling Price} - \text{Cost per Unit}) \times \text{Number of Units Sold} ] We want to analyze how changes in the selling price and the cost per unit affect the profit, assuming the number of units sold remains constant at 100.

📝 Note: Before proceeding, ensure your Excel sheet is set up with the necessary formulas and input values for the scenario you are analyzing.

To set up the sensitivity table: - Place the selling prices you want to test in a column (e.g., A2:A5). - Place the costs per unit you want to test in a row (e.g., B1:E1). - In the cell where you want the first result to appear (e.g., B2), enter the formula that calculates the profit based on the selling price and cost per unit. For this example, the formula might look something like this: =($A2-B$1)*100. - Select the range that includes the headers and the formula (e.g., B1:E5). - Go to the “Data” tab, click on “What-If Analysis,” and select “Data Table.” - In the “Data Table” dialog box, for the row input cell, select the cell that contains the cost per unit variable, and for the column input cell, select the cell that contains the selling price variable. - Click “OK” to generate the sensitivity table.

Interpreting the Sensitivity Table

The resulting table will show the profit for each combination of selling price and cost per unit. By examining the table, you can see how sensitive the profit is to changes in these two variables. This can help you make informed decisions about pricing strategies and cost management.

	Cost per Unit 1	Cost per Unit 2	Cost per Unit 3
Selling Price 1	Profit 1	Profit 2	Profit 3
Selling Price 2	Profit 4	Profit 5	Profit 6
Selling Price 3	Profit 7	Profit 8	Profit 9

Advantages of Using Sensitivity Tables

Sensitivity tables offer several advantages, including:

• They allow for the quick analysis of multiple scenarios without having to manually recalculate each one.
• They provide a clear visual representation of how changes in input variables affect the outcome.
• They can be used to analyze the sensitivity of a model to changes in any number of variables, making them a versatile tool for decision-making.

In summary, creating a sensitivity table in Excel is a straightforward process that can greatly enhance your ability to analyze and understand complex systems. By following the steps outlined above and applying the concept of sensitivity analysis to your own scenarios, you can make more informed decisions and navigate the uncertainties of your business or project with greater confidence.