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:
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 🙂
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?
Please, check your email.
Regards
Very many thanks Mr. Kawser. All your posts have been very helpful. Thanks a million!
Nice to hear that it helped you. Thanks and regards.