Microsoft Excel is a fine piece of software, and most people underestimate the complexity and sophistication in the types of calculations that it can handle. One fine example of Excel's power is its quality to "predict the future" (sort of!) with what-if scenarios.
What is a What-If Scenario?
Mortgage
One of the very beneficial things you can do in Excel that you cannot as a matter of fact do with a piece of paper and a pencil (no matter how large the paper is or how big your eraser is) is to generate what-if scenarios. These are numerical models of "things in the world" (e.g., investment models, scientific experiments, personal financial models, etc.) that are used to find how things change under distinct circumstances (given distinct inputs).
Every what-if scenario is comprised of three types of elements:
a. Independent variable(s) (the "input" of your model).
b. Functions and formulas that act upon your independent variables.
c. Dependent variables that change depending upon "a" and "b" (the "output" of your model).
It is these dependent variables that you are trying to predict; they're what you want to know more about and are the guess for your building the model in the first place. With what-if scenarios, you start by creating a basic model and then tweaking it (configuring it in distinct ways) by forming distinct combinations of your independent variables (your input) and/or your functions and formulas. The goal, of course, is to "see what will happen" to your dependent variable(s).
Ms Excel: Examples of What-If Scenarios
Here are some examples of base what-if scenario applications:
1. Allocating your monthly discretionary income with your discrete types of debt (various credit cards, your mortgage, auto loans, etc.) to resolve which combination of payments to each will help you sell out your debt the most quickly.
2. Comparing many auto lease offers that have distinct combinations of variables such as: amount of money due at signing, monthly payment amounts, and lease terms, with the goal of determining which one will corollary in the lowest total expenditure over the lifetime of the lease.
3. Collate the total value of many job offers, including independent variables such as signing bonus, salary, value of benefits holder and each year bonus.
Using Goal Seek
Ms Excel has a very beneficial built-in feature called Goal Seek. This is a what-if scenario of sorts, but it does not want you to generate many versions of a model to see which version results in the desired production value. Rather, with Goal Seek, you just set up the model once and then tell Excel which variable you are trying to attain and what its value should be. You also need to specify for Excel which of the independent variables you would like automatically altered to predict that value. Here's how to accomplish a Goal Seek what-if analysis:
1. Start by creating a model. This could be as straightforward as inputting two variables into two cells, then creating a recipe in a third cell that refers to the first two and multiples the values together. Or, it could be much more complex.
2. Click on the Data tab. In the Data Tools section, go for the What-If pathology icon, then Goal Seek from the drop-down menu.
3. In the Goal Seek dialog box's "Set cell" field., enter the cell coordinates (e.g., A4) of the cell you for which you are trying to accomplish a sure value (e.g., 14).
4. Then, enter the value you are trying to accomplish in the "To value" field.
5. Finally, enter the cell coordinates in the "By changing cell" field of the cell whose value you are telling Excel to change in order to accomplish the desired value in the first cell. Click Ok. That's it!
You will see that Excel has automatically changed the designated value so that the desired value in the other cell is achieved. Goal Seek is an under-utilize function, but this is undeservedly so. Once you learn how to use it, it can be a great addition to your analytical toolbox.
Ms Excel - Examples of What If Scenarios
