We carry out sensitivity analysis (also called **what-if analysis or data table**) in Excel when we wish to observe what a specific result would look like under different conditions. We can easily select one or two variables to see the desired output. In this article, we will learn how to carry out sensitivity analysis using a Two-variable data table.

*Figure 1 – Example of a sensitivity table*

**Two-Variable Data Table Sensitivity Analysis**

We can use the **data table** as a shortcut to perform multiple calculations of different versions of a single scenario. It also provides a way to compare and view the results of the different variations in one place.

In this tutorial, we will create a summary of expenses and income in Columns A and B. Next, we will prepare a **Profit and Loss Table,** and finally, we will find the **Operating Profit** over different sales volumes and price of goods sold using a Sensitivity analysis table.

- First, we will create a
**worksheet**as shown below.

* Figure 2 – Setting the items for sensitivity analysis*

- Next, we will create a
**Profit and Loss Statement Table,**where we will make our entries after the Sensitivity analysis. Where:- In Cell B12, we will enter the formula =B4*B3;
- In Cell B13, we will enter the formula =B5*B3;
- In Cell B14, we will enter the formula =B12-B13;
- In Cell B15, we will enter the formula =B14-B6-B7.

* Figure 3 – How to run a sensitivity analysis*

- We will have the result as
**shown below**

* Figure 4 – Profit and loss statement for price sensitivity analysis*

- Now, we will prepare our sensitivity analysis table. In the figure below, we have
**Price**as**our vertical data range**while**Items Sold**will be our**Horizontal data range**.- In Range
**H4:N4,**we will type the Items sold (sales volumes)**from 500 to 3,000** - In Range
**G5:G13,**we will type prices from**80 to 300** - In the Cell
**G4**, we will type the formula**=B15**

- In Range

* Figure 5 – Sensitivity analysis in the tutorial*

- We will select Range
**G4:N13**

* Figure 6 – Data table for sensitivity analysis*

- We will go to the
**Data Tab**, select**What-If Analysis**and then click on**Data table**

* Figure 7 – How to do an excel sensitivity analysis*

- In the
**Data table dialog box**- We will specify the cell for
**Items sold**in**the Row Input Cell box**. In this case, we will enter**$B$4** - We will specify the cell for
**Price**in**the Column Input Cell box.**In this case, we will enter**$B$5** - Lastly, we click
**OK.**

- We will specify the cell for

* Figure 8 – Sensitivity analysis*

- Once we have click
**OK**, excel will automatically find the operating profit for each scenario.

* Figure 9 – Sensitivity analysis*

**Instant Connection to an Excel Expert**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment