Use Data Table for Scenario Analysis

 

Use Data Table for Scenario Analysis

data table logo
msofficeshare logo
pic5 goal seek

Scenario Analysis is a very useful financial decision tool. It is a vital part for strategic planning. It projects outcomes of different scenarios so users can hope for the best and prepare for the worst. It also provides information as a stress test that indicates what level of outcomes that a user or investor can bear. For example, the scenario analysis can tell at what level of income when the project starts incurring loss so the managers can decide if such project should be carried out. Another example is how much loss can be borne with the provision of certain reserved amount. If there is not sufficient reserve to cover the forecasted loss amount, the managers should consider putting in more reserve.

The Data Table function in Excel is not intuitive to use. However, if you know how to use it, it can become a very powerful tool. The Data Table can be built with one variable and you get a list of the results corresponding to such variable in different scenarios. Or you can build the Data Table as a matrix with two variables and the results will show up like a table format.

I am re-using the example that I did for the Goal Seek. Let’s start with just one variable. The below example shows a tiered Commission Rate structure. The higher the Sales Amount, the larger the percentage of Commission Rate is. In addition, the sales person entitles an annual Base Salary plus a Target Bonus if the sales person achieves a pre-defined Sales Amount ($1,000,000 in our example).

If you toggle Excel to view the formula (CTRL+`), you can see the below formula.

pic6 goal seek

Now I want to create different scenario with the change of Sales Amount to calculate the Total Compensation Amount. Let’s start with $500,000 with $250,000 increment, up to $2,750,000 (cell A19 to A28). You need to tell Excel what amount to be calculated and put such amount on the very top of the list (cell B18). In this case, we want to calculate the Total Compensation Amount which is in cell E11. Therefore, I made cell B18 to equal to E11.

pic1 data table

Now highlight the list.

pic3 data table
pic2 data table

Click the “Data” tab on the ribbon, then click the “What-if Analysis” and choose the “Data Table…” command.

pic4 data table
pic5 data table

This is the result (cell B19 to B28) after you click “OK” button. The Total Compensation Amounts are calculated corresponding to the Sales Amounts in the list. For example, if the sales person can make $2 million sales, he will be compensated with $112,500.

pic6 data table

You can also create a chart by using the calculated results to make the report more informative to the users or the management.

Two Dimensional Data Table

You can also create a two dimensional data table which shows the matrix results of two variables. Let’s see the below example. The Monthly Principal and Interest (P&I) Payment calculation is determined by four variables: property value, down payment %, interest rate, and payment terms. In Excel, you can analyze a maximum of two variables by Data Table at a time.

pic7 data table

Let’s assume that we are now only interested in seeing a scenario analysis of down payment % and interest rate. So I created a table with the down payment % on top row and interest rate as the left column as headers. Then I put in “=B7” in the intersection of the two headers (cell D2) to tell Excel that it is the calculation result to be populated in the Data Table.

pic8 data table

Please follow the below steps:

Step 1: Highlight the area that the calculation results to be populated (including the headers).

Step 2: Click the “Data” tab on the ribbon, then click the “What-if Analysis” and choose the “Data Table…” command.

Step 3: Put the B2 in the “Row input cell:” to tell Excel to use the Down Payment % as row variable. Put B4 in the “Column input cell:” to tell Excel to use the Interest Rate as column variable.

pic9 data table

Step 4: Click “OK” button to see the result.

pic10 data table

Note:

A data table can not obtain data from another data table. For example, you can not look up a value from a data table and use it as an input for another data table.

More Posts

Excel Vlookup Function

The VLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a table and return a corresponding