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.

What if Analysis in Excel

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:

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

6 Comments
  1. I am a working person. I work as business development executive for a Engineering Institute. I want to learn all about excel. your website is very helpful. thanks

    • Glad to hear that my website is helping you in a way.
      It’s my pleasure Mohammed Hussain. Keep in touch 🙂

  2. i am subscribed and logged in but when i go to download the example file to see what makes it tick, i am sent to the subscribe page. what am i doing wrong?

  3. Very many thanks Mr. Kawser. All your posts have been very helpful. Thanks a million!

Leave a reply

ExcelDemy
Logo