Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Using a Data Table to Carry Out Sensitivity Analysis – Excelchat

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

 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.

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc