offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

Performing a What If Analysis in Excel

By making the use of what-if analysis tool in excel, one can easily begin to use different sets of values in one or more formulas, exploring different results. For example, the What-If analysis helps you in building two budgets that assume a certain level of revenue. Or you can specify a result that you wish to produce, and then begin determining the sets of values that will help you to produce the results.

The What-If analysis in excel is generally considered to be a procedure of changing the values in cells, and know how these changes will then affect the outcome of formulas on the worksheet. There are three different kinds of what if analysis tools which comes in excel, and they are: Scenarios, The Goal Seek and the last but not the least, Data Tables.

Scenarios

Scenarios and the data tables generally take sets of input values and determine the possible results. But a data table will work only with one or two variables and will accept many different values that comes out from those values. A scenario can turn out to have multiple variables but can accommodate only 32 values. Goal seek is something that works completely in a different manner when compared to Scenarios and Data Tables, as it takes the results first and then determines the possible input values that help in producing those results.

In addition to these three tools, you can also install the add-ins that will help you perform what-if analysis in a better manner, say, for example, the Solver add-in. The Solver add-in is very much similar to the Goal Seek but can accommodate many variables. In fact, you can also have forecasts being created using the fill handle and various commands that are existing in excel.

Using Scenarios in order to consider different variables, Scenarios are generally considered to be a set of values that an excel saves and substitutes automatically in the cells of the worksheet. You can, in fact, create and save different groups of values on a worksheet and then switch to any of these scenarios viewing different results.

Suppose, you are dealing with two budget scenarios- a worst case and the best case. You then use the scenarios manager to create both scenarios on the worksheet and then switch between them. For each and every scenario you specify the cells that change and the value you could use for that scenario. When you make a switch between them, the resulting cell changes reflecting the different cell values.

Goal Seek

Using Goal Seek to get the desired result: If you know what result you will be getting from the desired formula, and not sure about the input value, then the formula requires you to get that result is the goal seek.

For example, suppose you need to borrow some money from someone, and you do know how much of money you want, and how much time do you require to pay off that loan, and how much can you afford to pay each and every month. You can then use the goal seek to determine what interest rate you need to secure in order to meet up your desired goal.

Data Tables

Using Data Tables to see the effects of one or two variables: If you have a formula that uses for one or two variables, or multiple formulas that use one common variable, you can then try and use the data table to see all the outcomes in one place. Using data tables makes it really easy enough to examine a range of all the possibilities at a glance.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!