What-if analysis in Microsoft Excel?

You can explore the thoughts inside your mind through this tool in Excel. It’s easy to predict and decide by looking at the results of all the modes. Suppose you have a factory, you want to compare how much your profit will be in different scenarios. Scenarios that depend on your production, production costs, etc., and we want to see and compare each change. Generally when you have a function or you created a function yourself and you want to analyze the output of the function for different values, you can use What-If analysis.

Three tools are for you:

Scenarios: You set different scenarios that differ in your computing inputs, and for each scenario, you can see the output as a table and so on.

Goal Seek: You get help from Excel to figure out how much it’s given to an arbitrary number.

Data Tables: In this section you draw a table and like the scenario mode (but without the need for a scenario definition) you will see the output of the function for different values.

Examples are simpler, such as having a bookstore and having 100 books for sale. A certain percentage of the books are expensive books with a profit of $ 50 and a certain percentage with a profit of $ 20.

To begin with, we assume that 60% of books are only $ 50 in profits and $ 40 in cheap books.

What-if analysis in microsoft excel

The Number of Book column is automatically given in percentage terms, and the total number of books is 100. Profit column entered manually. The total profit column identifies the total profit formulation that is equal to:

60*50$+40*20$=3800$

Total of 100 books, 60 of them were expensive, and 40 of them were cheap. Now, according to what was said, we would like to see this profitable for a variety of expensive and inexpensive books.

Create a scenario in Excel

In the Data tab, click on What-If Analysis and select Scenario Manager.

What-if analysis in microsoft excel

In the popup window, you must add your scenarios, click Add.

What-if analysis in microsoft excel

Choose an arbitrary name for your scenario. enter the second part of the house that is supposed to change its value in this scenario to see the outputs based on its change.

What-if analysis in microsoft excel

Click ok to see another window.

What-if analysis in microsoft excel

Because you have entered a house, he wants an entry. What is the price of the house entrance for this scenario? We want 60% of our books in this scenario to be expensive. Put 0.6at C4 until this scenario is active. Define for other scenarios, when 70% of the books are expensive, when 80 and so on. Even when 100% of our books are expensive.

What-if analysis in microsoft excel

If you select the desired scenario and click Show, Excel will only render this scenario. Our scenario is not complicated and only changes the C4 home for us. But that’s what we could do manually. It’s true, however, that when the scenario has several variable parameters, changing it with a little bit of time is done. Here we do it with one click.  Also, we can see the total output of different scenarios in a table. How?

Scenario Summary

In the previous window, click Summary to get the following.

What-if analysis in microsoft excel

Whether you want to show the output as a pivot table or a simple table, and in the Result cells section, you’ll also need to choose a house that you want to see for the different scenarios. We want to see the amount of orange house D10 for different scenarios.

What-if analysis in microsoft excel

You will see how you can display the output of your operation or formula for different inputs (different scenarios) in a table.

Goal seek tool

Sometimes you want to follow a goal. In fact, you want to know how much books you need to get your profit to 4700$. Or any other formula, for example, how much should you cut the bank’s installments to a profit of 1 million.

Click the What-If Analysis tab on the same tab and select Goal Seek. Below window will open:

What-if analysis in microsoft excel

In the first part you will set a house for it. In the second part, you order Excel how much you want it to be. For example, we would say our home profit (D10) would reach 4700. Now, in Excel, you must order what parameter to change to reach the number. We say that the house C4 will change so that the house D10 reaches 4700$.

What-if analysis in microsoft excel

What-if analysis in microsoft excel

The result: Excel placed 90 at home at C4 so that we get to our profit. You learned what-if analysis.

What-if analysis in Microsoft Excel?
4.5 (89.09%) 11 votes

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *