Microsoft Excel is a powerful 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 ability to “predict the future” (sort of!) with what-if scenarios.
What is a What-If Scenario?
One of the very useful things you can do in Excel that you cannot easily do with a piece of paper and a pencil (no matter how large the paper is or how big your eraser is) is to create 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 different circumstances (given different 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 reason 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 different ways) by forming different 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 common what-if scenario applications:
1. Allocating your monthly discretionary income with your various types of debt (various credit cards, your mortgage, auto loans, etc.) to determine which combination of payments to each will help you reduce your debt the most quickly.
2. Comparing multiple auto lease offers that have different combinations of variables such as the amount of money due at signing, monthly payment amounts, and lease terms, with the goal of determining which one will result in the lowest total expenditure over the lifetime of the lease.
3. Compare the total value of multiple job offers, including independent variables such as signing bonus, salary, value of benefits package and annual bonus.
Using Goal Seek
MS Excel has a very useful built-in feature called Goal Seek. This is a what-if scenario of sorts, but it does not require you to create multiple versions of a model to see which version results in the desired output 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 perform a Goal Seek what-if analysis:
1. Start by creating a model. This could be as simple as inputting two variables into two cells, then creating a formula 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, select the What-If Analysis icon, then Goal Seek from the drop-down menu.
3. In the Goal Seek dialogue box’s “Set cell” field., enter the cell coordinates (e.g., A4) of the cell you for which you are trying to achieve a certain value (e.g., 14).
4. Then, enter the value you are trying to achieve 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 achieve 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.
John is a technical expert, he has written technical blogs, manuals and reviews for many websites such as office.com/setup