Md. Araf Bin Jayed
Forecasting is an essential part of doing business. One cannot forecast an absolutely correct result but can predict the nearest result. In Microsoft Excel, there is an amazing feature called What-If Analysis which can help anyone in this regard. This feature of Excel helps anyone to calculate the predicted outcome of a scenario based on one or more changeable inputs. In this article, we will show you three examples of what if analysis in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
What If Analysis in Excel
What-if analysis in Excel is essential for statistical analysis. If anyone wants to see the possible outcomes of a project based on different variables related to that project, then a what if analysis will be the best choice for doing this. By changing one or more variables, users can see the possible outcome. This outcome will help them in taking decisions regarding that project. Under What-If Analysis in Excel, there are three different features. They are as below-
- Scenario Manager,
- Goal Seek and
- Data Table.
Each of these features has its own unique usefulness in analysis. We will explain each of these features in the following description.
3 Suitable Examples of What If Analysis in Excel
In this article, we will show you three suitable examples of what if analysis in Excel. We will cover every feature of this analysis and its usefulness in making decisions. To explain our article, we will use the following sample data set.
1. Using Scenario Manager to Show Different Scenarios
By using the Scenario Manager feature, one can compare different scenarios or results of one’s project. It is the first example of the three examples of what if analysis in Excel. To do that, he or she has to input different variables related to that project and see the outcome based on those changed variables. This helps in the decision-making process. What the possible outcome would be or what amount of profit one wants to earn can be shown through this scenario manager.
1.1 Completing Precalculations
Before starting our main procedure, we have some precalculations to be done. Because without those results we can not proceed further. The steps for our precalculations are as follows.
Step 1:
- First of all, type the following formula to get the Total Sells in cell C6.
=C4*C5
Step 2:
- Then, press Enter to see the desired result.
Step 3:
- Thirdly, to determine Total Cost, write the following formula in cell C9.
=C7+C8
Step 4:
- Fourthly, hit the Enter button and see the outcome of the formula.
Step 5:
- Fifthly, we will calculate the Profit.
- To do that, use the following formula in cell C10.
=C6-C9
Step 6:
- Sixthly, to see the final result, press Enter.
1.2 Using Scenario Manager
In our previous discussion, we have mentioned the usefulness and effectiveness of Scenario Manager. As per one’s demand, one can change one or more than one variable to see the desired outcome. Now, in our example, we will change the variables Product Quantity and Price Per Item. By changing these two variables, we will create three different scenarios and analyze them. The steps for this procedure are as follows.
Step 1:
- First of all, go to the Data tab of the ribbon.
- Then, under the Forecast group, choose What-If Analysis.
Step 2:
- Secondly, select the Scenario Manager… command from the dropdown.
Step 3:
- Thirdly, you will see the Scenario Manager dialogue box.
- Then, click on Add in that box to add a scenario.
Step 4:
- Fourthly, we will create the first scenario in this step.
- After selecting the command from the previous step, you will see the Add Scenario dialogue box.
- Firstly, in the Scenario name type box, type First Scenario.
- Then, in the Changing cells type box, input the cell address that you want to change.
- In our example, the changing cells are C4 and C5.
- Thirdly, in the Comment box, we can insert comments based on what variables you are changing.
- Lastly, press Ok.
Step 5:
- Fifthly, a new dialogue box will appear after the last step.
- Here, the box will ask you to input the new value of the variables.
- Then, according to your calculation, input those values.
- Lastly, press OK.
Step 6:
- In this step, you will see the First Scenario under the Scenarios label.
- Then, click on Add to attach more scenarios for comparison.
Step 7:
- Then, create a second scenario for the analysis by following the previous steps.
Step 8:
- Again, input the values of those variables which you want to change in the Scenario Values dialogue box.
- Then, press OK to save this scenario.
Step 9:
- Again, click on the Add button to add the third scenario to our analysis.
Step 10:
- After completing all the steps shown for the first scenario, you will see all the scenarios in Scenario Manager.
- To see the scenario summary, click on the Summary option from the box.
Step 11:
- Then, a new dialogue box will pop up.
- This box will ask for the result cells.
- In the Result cells dialogue box, put cell range C4:C10.
- Consequently, our main goal is to analyze profit for different scenarios, but we also want to see the changes for other variables as well.
- Lastly, press OK.
Step 12:
- Finally, we will see the Scenario Summary in our workbook.
- In this summary, we can see three types of profit in Result Cells $C$10.
- These profits have resulted from changing the variables of cells C4 and C5.
- From the summary, we can say that by adjusting the product quantity and price per item we can get our estimated amount of profit.
- Also, in the summary, the cells with gray color are the changing variables.
- In the above discussion, you will see the use of two variables in making the scenario summary. In your procedure, you can either use more than two variables or less than two to achieve your desired outcome.
- The result in the scenario summary is fixed. It won’t change despite changing any variable in your data set.
Read More: How to Do What-If Analysis Using Scenario Manager in Excel
2. Utilizing Goal Seek Feature to Achieve Goals
Goal Seek is the second example among the three examples of what if analysis in Excel. This feature is slightly different than Scenario Manager. In the previous feature, we gave variables as inputs and saw the final outcome in the form of profit. But in Goal Seek, we will set the final outcome as input and this feature will change the variables depending on this outcome. Here, we will fix the result in the start of the procedure.
2.1 Utilizing Goal Seek Feature by Changing Product Quantity
In our previous example, we gave two variables as inputs. But for the Goal Seek feature, it will change one variable at a time for the desired output. So, first, we will set our goal of earning a certain amount of profit by changing the product quantity. For a better understanding, go through the following steps.
Step 1:
- First of all, choose What-If Analysis from the Data tab of the ribbon.
Step 2:
- Secondly, from the dropdown, select Goal Seek.
Step 3:
- Thirdly, you will see the Goal Seek dialogue box.
- Then, in the Set cell type box, input the cell address that will be the outcome of this goal.
- In our example, it is C10.
- Secondly, in the To value dialogue box, set your desired outcome, which is 4500 in our example.
- Thirdly, input the cell address by altering which you want to get the outcome.
- In our case, we want to alter the product quantity.
- Lastly, press OK.
Step 4:
- Finally, you will see the Goal Seek Status.
- Furthermore, after some calculation, you will get the required amount of product quantity in cell C4 to get the profit of $4500.
- Consequently, it will change from 80 to 122.308 to get a profit of $4500.
2.2 Utilizing Goal Seek Feature by Changing the Price Per Unit
In this section, we will get our outcome by changing another variable other than the product quantity. Now, we want to change the price of the product. You can see what the output will be in the following steps.
Step 1:
- Firstly, select the Goal Seek feature from What-If Analysis like in the previous section.
- Then, in the Goal Seek dialogue box, fill in the type box as per the previous section.
- Here, our goal will be the same as to make a profit of $4500.
- But, here we will change the cell value of cell C5 which contains the Price Per Item.
- After filling in all the boxes, press Ok.
Step 2:
- Finally, after some calculation, we will see the new price of the item to achieve our goal.
- Consequently, the price will change from $65 to $99.38.
- While setting goal, you must input a cell containing a formula in the Set cell type box.
- In case of, giving input in By changing cell type box, the input cell must be a constant or without any formula.
Read More: How to Do What-If Analysis Using Goal Seek in Excel
Similar Readings
3. Generating a Data Table for Side-by-Side Comparisons
Among three examples of what if analysis in Excel, the last example is the Data Table. By using this feature, we can use one or more variables to create a data table and see the outcome for each change. This feature helps users perform side-by-side comparisons after changing variables.
3.1 Generating Data Table from Row Input
Now, we can either generate a data table either from column input or from row input. Both procedures are almost the same apart from some issues. First, we will generate a data table by giving input from a row.
Step 1:
- First of all, under the main data set, create a horizontal data table.
- Here, we will determine the profit for different product quantities.
- To start our procedure, give the same input in cell C13 as in cell C10 like in the following pictures.
Step 2:
- Secondly, in the product quantity row, give input as per your choice.
- Then, select the cell range from C12:H13.
- Furthermore, choose the Data Table command from the What-If Analysis of the Data tab.
Step 3:
- Thirdly, you will see the Data Table dialogue box.
- In the Row input cell type box, select cell C4.
- Then, press Ok.
Step 4:
- Fourthly, we will see the different amounts of profit for different product quantities.
- Consequently, you can select from various inputs to get your desired output.
3.2 Generating Data Table from Column Input
Now, we will generate the table from a column input. It is like the previous section but our input values will be in a column instead of a row. Also, we will change our input variable from product quantity to price per item. The steps are as follows.
Step 1:
- Firstly, create a vertical data table under the main data set to get the output.
- Furthermore, in cell C13, insert the same cell value as in cell C10.
Step 2:
- Secondly, input various prices under the column header Price Per Item.
- Then, select the cell range from B13:C18.
- After that, choose the Data Table command from What-If Analysis like in the previous discussion.
- Furthermore, in the Data Table dialogue box, select cell C5 as the Column input cell.
- Lastly, press OK.
Step 3:
- Thirdly, we will see profits for different prices per item.
- From there, you can choose your desired output.
3.3 Generating Data Table from Two Variables
In our previous two discussions, we created a data table from one variable. But, we can also create a data table by inserting more than one variable. Here, we will use both the product quantity and the price per item at the same time to get the desired profit. The detailed steps of this procedure are as follows.
Step 1:
- Firstly, we need to create a data table like the following for inserting values for two variables.
Step 2:
- Secondly, in cell C13 insert the same cell value as in cell C10.
Step 3:
- Thirdly, after filling in different values under the variable headers, select cell range C13:H18.
Step 4:
- Fourthly, choose the Data Table command from What If Analysis.
- Then in the Data Table dialogue box, you have to give some input.
- Here, in the Row input cell box select cell C5.
- Then, in the Column input cell box select cell C4.
- Lastly, press OK.
Step 5:
- Finally, you will see the data table filled with various values of the output.
- This indicates how much profit you will earn in terms of that respective product quantity and price per unit.
- For example, if other costs are fixed and we sell 80 products at $65 per product then we will make a profit of $1750.
- While giving input from the main data set in the data table, the data table cell must contain the exact same formula just like the data set.
- Otherwise, if you input the value only, it won’t show any actual result.
Read More: How to Create One Variable Data Table Using What If Analysis
Conclusion
That’s the end of this article. We hope you find this article helpful. After reading the above description, you will get three examples of what if analysis in Excel. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.
