The What-if or sensitivity analysis tools in Excel are often overlooked by financial modelling professionals. The tools represent immense analytical value during a financial modelling exercise, no matter if it is for corporate valuation, strategic planning or management accounting/reporting purposes.

Excel offers an array of fantastic what-if analysis tools; Data Table, Goal Seek and Solver.

Data Table

This tool demonstrates how changing one or two variables, in a range of cells, will modify the computed outputs of those formulas. Data tables enable modellers a fast and effective method for calculating numerous results in one procedure; as opposed to creating numerous financial models or worksheets for each iteration or scenario. In the case of strategic planning, this tool is an efficient means to analyse and compare the financial results of all the numerous planning scenarios in your financial model.

The below video presentation illustrates how the Data Table can calculate a company’s net present value (NPV) under varying weighted average cost of capital (WACC), and then with the added variable of varying corporate tax rate.

Goal Seek

The Goal Seek tool extracts an unknown input value, when the preferred result from a single formula is already known. Goal Seek iterates the value in the one specific cell (changing cell) until the formula, which is dependent on the changing cell returns the desired result.

In strategic planning, conceptualising how WACC and/or corporate tax rates impact a desired NPV is fundamental, as it will help the financial modeller to present a more marketable or realistic plan to corporate executives or clients.

Goal Seek enables the modeller to accept the new iterated value or cancel on the Goal Seek and return to the original values.

Solver

Solver allows a financial modeller to attain one cell’s optimal value for a formula in a worksheet (known as the target cell). A user must specify the cell values to change (adjustable cells), in order to derive the specified result based on the target cell formula. Constraints can be applied to limit the values that Solver can apply in the financial model; these constraints can denote other cells that impact the target cell formula.

In the above video, Solver seeks a $17.5 m NPV value based on constraining the corporate tax rate to greater than or equal to 28%, and the WACC to less than or equal to 15%.

Like Goal Seek, Solver provides the option to accept or cancel on the solved values.

Conclusion

Excel provides financial modellers some value-adding what-if or sensitivity analysis tools, which are especially pertinent to a thorough and all-encompassing strategic planning exercise. A Data Table can change calculated outputs based on one or two dependent variables in the output’s formula. Goal Seek can derive an unknown input value given the desired result from one formula is already known. While Solver can extract a cell’s optimal value based on dependent cell constraints based on a specified result.