Use Data Table for Scenario Analysis
File to download: Goal Seek and Data Table Example.xlsx
Related Article: Use Goal Seek command to reverse engineer desired results
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.
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.
Now highlight the list.
Click the “Data” tab on the ribbon, then click the “What-if Analysis” and choose the “Data Table…” command.
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.
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.
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.
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.
Step 4: Click “OK” button to see the result.
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.
