Using a Data Table to Carry Out Sensitivity Analysis

  • Post author:
  • Post category:Uncategorized

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