A What-If example in Excel

This article is part of my series: What-If Analysis in Excel – A Step by Step Complete Guide.

You can create dynamic models in Excel. This is one of the most appealing aspects of Excel. A dynamic model uses formulas that instantly recalculate when you change values in cells that are used by the formulas. When we change values in cells in a systematic way and observe the effects on specific formula cells, we’re actually performing a type of what-if analysis. For example, we are asking “What if the interest rate on the loan changes to 8.5% rather than 8.0%?” or “What if the inflation rate rises up by 10%?”. If we set up our worksheet properly, answering such questions is really simple. Just like plugging in new values and observing the results of the recalculation. Excel provides useful techniques to help you in your what-if analysis.

A What-If Example

The following figure shows a simple worksheet model. This model calculates information relating to a mortgage loan. We have divided the worksheet into two sections: the input cells and the result cells. Result cells contain formulas.

This simple worksheet model uses four input cells to produce the results.

With this worksheet model, we can easily answer the following what-if questions:

• What if I can negotiate a lower purchase price on the property?
• What if the lender requires a 30% down payment?
• What if I can get a 25-year mortgage?
• What if the interest rate increases to 15.50%?

We can answer these questions by simply changing the values in the cell range C4: C7 and observing the effects in the dependent cells (C10: C13). You can, of course, vary the values of any number of input cells simultaneously.

Read More

Performing manual what-if analysis in Excel

Download Working File

Download the working file from the link below: