I am including an Excel Workbook with 9 worksheets. These show step-by-step procedures on how to build a 1 and a 2 input “What If” analysis.

The main example is based on a very brief Income Statement. The requirement is to project the budgetary figures for 2007 using actual values from 2006 multiplied by one or more growth factors. Our objective is to see how the Net Profit Margin depends on the Sales Growth used. The formulation shows it for 4%. The What If analysis allows you to vary the Sales Growth from 2% to 12% in 0.5% increments and study the resulting Net Profit Margin.

The last worksheet shows how sensitivity analysis is applied to discounted cash flow whereby the NPV (Net Present Value) is varied against different discounting rates.

Sadly, Excel only allows us these limits: 1) One input variable but one or more output variables and 2) Two input variables but only one output variable. To define more variables, Excel provides us with the Scenario Manager. This is a powerful facility but needs a bit more work to complete. I will show you its intricacies in the next entry.

Download the What If Excel Workbook.