The Excel Data Table “What-if Analysis” tool is one of the most powerful, yet underutilized functions in Excel. Undoubtedly it is a great way to deliver a high-level, informative what-if analysis of a data set.
Although it might appear confusing and difficult to use. However, once you know how to use data table, it is quite straight forward to use. You simply need to know the input drivers of the dependent metric. Which you are wanting to perform for the what-if calculation.
Especially in a business context. A data table is a great way to better understand a company’s financial position. For example, a what-if analysis of the company’s Net Present Value (NPV) from a Discounted Cash Flow (DCF) schedule.
Practical benefits of a DCF Sensitivity Analysis
The following video illustrates the benefits of the Excel Data Table. It presents the inverse correlation between the Weighted Average Cost of Capital (WACC) and a company’s NPV.
In the first example, the “Row Input Cell – Data Table” references the dependent WACC amount of 8.61% (cell “G36”) to analyse its effect on NPV. This discount amount is used to calculate the original NPV of $628,045.5 in the dependent DCF.
The second example analyses the relationship between the WACC and the Corporate Tax Rate. As the above screenshot illustrates, a company’s NPV is impacted by both a changing WACC and Corporate Tax Rate.
Automatic Updating properties
Assuming the user enables Excel’s Auto Calculation properties. Any changes to the source row or column input cells, will result in the Excel Data Table updating automatically as well.
Conversely it is not the case with all functions of Microsoft Excel. For instance with the Pivot Table tool or array formula (aka Control+Shift+Enter or CSE formulas).
Clearly a failure by Excel users to update certain formulas will result in data control issues or erroneous outputs in a financial model.
Important things to remember
In the above example, the cells sourced are input cells for both the “Row Input Cell” or “Column Input Cell”. Indeed both are dependent formula cells in the calculation of the output. In this example, we want to calculate the NPV.
If the user doesn’t structure the table like it is in the video. The execution of the Excel Data Table will not work.
If users need to delete the Data Table. Importantly, the entire data array will have to be deleted. Excel will not permit a partial deletion of a data table for obvious reasons.
Sample workbook
The following sample spreadsheet contains the what-if analysis using the Excel Data Table. It is demonstrated in the above YouTube video.
Strategize Financial Modelling – DCF Sensitivity Analysis – Excel Data Table.xls
