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 Analysi s** 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
in cell*Total Sells*.*C6*

`=C4*C5`

**Step 2:**

- Then, press
to see the desired result.*Enter*

**Step 3:**

- Thirdly, to determine
, write the following formula in cell*Total Cost*.*C9*

`=C7+C8`

**Step 4:**

- Fourthly, hit the
button and see the outcome of the formula.*Enter*

**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

**and**

*Product Quantity***. By changing these two variables, we will create three different scenarios and analyze them. The steps for this procedure are as follows.**

*Price Per Item***Step 1:**

- First of all, go to the
tab of the ribbon.*Data* - Then, under the
group, choose*Forecast*.*What-If Analysis*

**Step 2:**

- Secondly, select the
command from the dropdown.*Scenario Managerâ€¦*

**Step 3:**

- Thirdly, you will see the
dialogue box.*Scenario Manager* - Then, click on
in that box to add a scenario.*Add*

**Step 4:**

- Fourthly, we will create the first scenario in this step.
- After selecting the command from the previous step, you will see the
dialogue box.**Add Scenario** - Firstly, in the
type box, type*Scenario name*.*First Scenario* - Then, in the
type box, input the cell address that you want to change.*Changing cells* - In our example, the changing cells are
and*C4*.*C5* - Thirdly, in the
box, we can insert comments based on what variables you are changing.*Comment* - 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
under the*First Scenario*label.**Scenarios** - Then, click on
to attach more scenarios for comparison.**Add**

**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
dialogue box.**Scenario Values** - Then, press
to save this scenario.*OK*

**Step 9:**

- Again, click on the
button to add the third scenario to our analysis.*Add*

**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
option from the box.*Summary*

**Step 11:**

- Then, a new dialogue box will pop up.
- This box will ask for the result cells.
- In the
dialogue box, put cell range*Result cells*.*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
in our workbook.*Scenario Summary* - 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
and*C4*.*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.

**Notes:**

- 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

*. In the previous feature, we gave variables as inputs and saw the final outcome in the form of profit. But in*

**Scenario Manager****, 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.**

*Goal Seek*#### 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
from the*What-If Analysis*tab of the ribbon.*Data*

**Step 2:**

- Secondly, from the dropdown, select
.*Goal Seek*

**Step 3:**

- Thirdly, you will see the
dialogue box.*Goal Seek* - Then, in the
type box, input the cell address that will be the outcome of this goal.**Set cell** - In our example, it is
.*C10* - Secondly, in the
dialogue box, set your desired outcome, which is*To value*in our example.*4500* - 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
to get the profit of*C4*.*$4500* - Consequently, it will change from
to*80*.*122*to get a profit of*308*.*$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
feature from*Goal Seek*like in the previous section.*What-If Analysis* - Then, in the
dialogue box, fill in the type box as per the previous section.*Goal Seek* - Here, our goal will be the same as to make a profit of
.*$4500* - But, here we will change the cell value of cell
which contains the*C5*.*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
to*$65*.*$99.38*

**Notes:**

- While setting goal, you must input a cell containing a formula in the
type box.*Set cell* - In case of, giving input in
type box, the input cell must be a constant or without any formula.*By changing cell*

**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
as in cell*C13*like in the following pictures.*C10*

**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
command from the*Data Table*of the Data tab.*What-If Analysis*

**Step 3:**

- Thirdly, you will see the
dialogue box.*Data Table* - In the
type box, select cell*Row input 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
, insert the same cell value as in cell*C13*.*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
command from*Data Table*like in the previous discussion.*What-If Analysis* - Furthermore, in the
dialogue box, select cell*Data Table*as the*C5*.*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
insert the same cell value as in cell*C13*.*C10*

**Step 3:**

- Thirdly, after filling in different values under the variable headers, select cell range
.*C13:H18*

**Step 4:**

- Fourthly, choose the
command from*Data Table*.*What If Analysis* - Then in the Data Table dialogue box, you have to give some input.
- Here, in the
box select cell*Row input cell*.*C5* - Then, in the
box select cell*Column input 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
products at*80*per product then we will make a profit of*$65*.*$1750*

**Notes:**

- 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.