Excel Functions

What-If Analysis in Excel

Pinterest LinkedIn Tumblr

Intro to What-If Analysis

From individuals to big conglomerates everyone wonders on a question with What if? Big giants deal with what if I had produced more, Individuals deal with What if I had done better. Excel provides assistance with a pretty cool tool, namely What if Analysis to help analyze the so-called What-If situation. It helps in building scenarios and then compare them or set a goal and ascertain the desired inputs to achieve the goal.

What-If Analysis is the process of changing multiple sets of data values to how those changes affect the outcome. There are three kinds of What-If Analysis tools in Excel: Scenario Manager, Goal Seek, and Data Tables. Scenarios and Data tables take the input values to determine possible results.  On the other hand, Goal Seek is a reverse process. It takes in the result i.e. output value and determines the possible input values. It saves time and makes your work easier.

Let’s say you and your partner are in an argument whether to increase the advertising expense which could boost the sales. You do not like the idea as you believe it might affect the profit margins. To resolve this issue, you can use Scenarios. In case you wish you achieve a certain level of turnover. To achieve the desired result (turnover) you need to know the input factors, like how much to produce, how much to spend on advertising and many more. Goal Seek can address this issue aptly.

Moreover, you can use What-If Analysis to build multiple budgets that make an assumption of different revenue total. You can specify the desired result to be produced, and then ascertain what sets of values will produce that result.

What-If Analysis in Excel

To use what-if analysis click on the Data tab from the ribbon and then select What-If Analysis? under the Forecast section.

What if Analysis
What-If Analysis

Further, you have three options:
Scenario Manager
Goal Seek
Data Table

What-If Analysis in Excel 1

Alternatively, you can use the Excel Keyboard shortcut Alt + A+ W. Then click S/G/T for Scenario, Goal seek, Data table respectively.

Scenario Manager

In general Scenario Manager is used for comparing scenarios. Say for example you are in the Sales department and you have to plan the sales for the next quarter. In this case, you can build multiple scenarios and then compare all the scenarios. You can make a worst-case scenario, best case and so on and then make a comparison.

Goal Seek

As mentioned earlier goal seek is a reverse process, it retrieves what the inputs must be for the desired result/output. For example, you have a bill amount of Rs. 10,000, on which there is a 5% tax which totals up to Rs. 10,500. Now the customer asks for a discount of Rs. 500. Therefore, the final amount must be 10,000. To find out how much the value must in and a 5% tax to a total of Rs. 10,000 you can use Goal Seek.

Data Table

A Data Table works with only one or two variables, but it can accept many different values for those variables. A Scenario can have multiple variables, but it can only accommodate up to 32 values.

For example, you own a store and have 100 items each of two sizes stocked in your store of. Items are priced based on size Rs. 15 and Rs. 20 respectively. Your sales consist of items of both the sizes. Now If you sell 60% of the higher price, items and rest of the lower-Price; your sales would be (60*20 )+ (40*15). Similarly, you can create multiple cases and sum them in a table for easier comparison.

What-if analysis can be used in Financial modeling as a type of sensitivity analysis performed in Excel. It is used to test how changes in assumptions impact certain outputs in a model. Goal Seek and Data Tables are the common types of sensitivity analysis used in financial modeling and valuation. This means when 1 or 2 of the inputs in your model change you can ascertain the change in output based on input. In contrast, Goal seek works opposite, i.e. it ascertains what inputs would lead to the desired output, potential in planning.

These type of analysis can be of great help in decision making. To sum up, you can use Scenarios to consider many different cases, Goal Seek to find out how to ascertain the desired result and use Data tables to notice the effect of one or two variable on a formula.

For more updates on excel visit our Youtube channel https://www.youtube.com/channel/UCD0p7s8LCEUl5c_bP2ErFnA

Related Posts

Write A Comment