DCF Sensitivity Analysis using Excel Data Table

Excel Data TableThe Excel Data Table “What-if Analysis” tool is one of the most powerful, yet underutilised functions in Excel. It is a great way to deliver a high-level, informative what-if analysis of a company’s financial position such as its Net Present Value (NPV) from a Discounted Cash Flow (DCF) schedule.

 

Practical benefits of a DCF Sensitivity Analysis with Excel Data Table

The following video will illustrate the benefits of the Excel Data Table. It will present 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% (G36) to analyse its effect on NPV. This discount amount was used to calculate the original NPV of $628,045.5 in the preceding DCF.

The following spreadsheet performs Sensitivity Analysis with Excel Data Table in the YouTube video.

Excel Data Table

Strategize Financial Modelling – DCF Sensitivity Analysis – Excel Data Table.xls

 

The second example performs a “Row & Column Input Cell – Data Table”, which analyses the relationship between the WACC and the Corporate Tax Rate. As the following screenshot illustrate, a company’s NPV is negatively impacted by both a rising WACC and Corporate Tax Rate.

Excel Data Table

 

Automatic Updating of an Excel Data Table

Assuming the Auto Calculation properties of Excel are enabled, any changes to the source row or column input cells, will result in the Excel Data Table updating automatically. This is not the case with all functions of Microsoft Excel, such the Pivot Table or array formula (aka Control+Shift+Enter or CSE formulas).

 

Important things to remember with Excel Data Table

The cells sourced as input cells for either the “Row Input Cell” or “Column Input Cell”, must be dependent formula cells in the calculation of the output; which in this example is the NPV.

Otherwise the execution of the Excel Data Table will not work.

If users need to delete the Data Table, the entire data array will have to be deleted – Excel will not permit a partial deletion of a data table for obvious reasons.

Excel Data Table