What-If Analysis in Excel: Hello Everyone, Today We’ll discuss What-If Analysis in Excel. Excel provides the most powerful tool for making the calculation quickly and easily. It is used to explore different scenarios on the same data in the excel spreadsheet. You can use several sets of values for getting the various results. Some other tools also work with this tool like Scenario Manager, Goal Seek, and Data table.
What-If Analysis is a process of changing a set of values in cells that affects the results of the formulas on the excel sheet. This tool is very useful for economics and management purpose because you can see how different value affect outcomes.
Assume you have a small business store and you have some items in the inventory. You want to sell the item to make the profit.
Here you have 375 items in the inventory and you get a profit Rs.200 per item, calculate total profit 375*200=Rs.75000.
You can use Scenario Manager to apply different scenario on the data for getting the outcomes as you want. What-If analysis excel tools used to allow you to easily compare the results of the different scenario.
For that, you can easily change in cell A2 and see the changes in the C2 cells. For proper knowledge of the scenario manager , you can follow some steps which are discussed below:
- Click on Data tab.
- Click on What-If Analysis from the Data tab.
- Then click on Scenario Manager.
- Scenario manager window opens then click on Add Button.
- Add the scenario-Fill scenario name and changing cell and click OK.
- Add other Scenario.
- To check the summary of the different scenario, click on the Summary button.
- Choose the resulting cell and click OK.
- Get the result.
If you sell 375 items then you obtain Rs.75000 profit, sell 600 item then obtain Rs.120000 and sell 800 item then you can obtain Rs.160000 so you can easily find the outcomes if you change the scenario of the data.
Goal Seek feature is used to obtain a certain goal means if you want to get a particular outcome then this features calculate the input values.
Example: If you want to get Rs.82000 as a profit in your business then this features calculate how many items should be sold to get that outcome. To apply these features follow some steps as :
- Click on the Data tab, then click on What-If analysis then Goal Seek.
- Then fill the value in Set cell, To value and By changing cell field.
- Click Ok.
Here, we select a C2 cell for the result and enter value Rs.82000 and Change the cell A2 means to get the outcome Rs.82000, we have to find the number of items sold.
Results: The result of above analysis is-
Thus, we need to sell 410 item to obtain a total profit of exactly Rs.82000.
Thus, in this article, we have focused on What-If analysis in excel where Excel Scenario Manager and Excel data table works with this tool. These features play an important role if you know the desired results but you need to find the input value. If you like this post then you can share this post with your friends, colleagues, and relatives. We’ll update this information on the regular basis. You can also share this post on facebook, twitter, Instagram, Google+ or other social media website. If you have any question regarding this post then you can write in the comment section. We’ll revert back to you as soon as possible.