One of the most appealing aspects of Excel is its ability to create dynamic models. A dynamic model uses formulas that instantly recalculate when you change values in cells that are used by the formulas. When you change values in cells in a systematic manner and observe the effects on specific formula cells, you’re performing a type of what-if analysis.

Excel includes many powerful tools to perform complex mathematical calculations, including** what-if analysis**. This feature can help you **experiment** and **answer questions** with your data, even when the data is incomplete.

Excel’s what-if analysis tools:

**Goal Seek****Scenario Manager****Data Table****Solver**

**PART 1: Goal Seek and Scenario Manager**

**What is Goal Seek?**

When you create a formula or function in Excel, you put various parts together to calculate a **result**. **Goal Seek** works in the opposite way: It lets you start with the **desired result**, and it calculates the **input value** that will give you that result. We’ll use a few examples to show how to use Goal Seek.

**How to Use Excel Goal Seek?**

**Example 1:**

Let’s say you’re enrolled in a class. You currently have a percentage of 15, and you need at least 60% to pass the class. Luckily, you have final exam that might be able to raise your average. You can use Goal Seek to find out how much you have score in final exam to get first class?

In the image below, you can see that the marks obtained in internal and mid-term exam are **12 and 30**. Even though we don’t know what the final exam marks will be, we can go ahead and write a formula or function that calculates the final percentage. In this case, percentage is calculated by applying this formula in cell **C8**, **=SUM(C5:C7)/D8**. Once we use Goal Seek, cell **C7** will show us the minimum marks we’ll need to make in final exam.

- Select the cell containing the value you want to change. When you use Goal Seek, you’ll need to select a cell that already contains a
**formula**or**function**. In our example, we’ll select cell**C8**because it contains the formula**=SUM(C5:C7)/D8.** - Begin by clicking on the
**Data**tab on the Ribbon and locating the**What-If Analysis**button in the**Data Tools**Click on the**What-If Analysis**button and choose**Goal Seek**from the menu. - A dialog box will appear with three fields:
**Set cell:**This is the cell that will contain the desired result. In our example, cell**C8**is already selected.**To value:**This is the desired result. In our example, we’ll enter**60%**because we need to earn at least that to pass the class.**By changing cell:**This is the cell where Goal Seek will place its answer. In our example, we’ll select cell**C7**because we want to determine the grade we need to earn on the final assignment.

- When you’re done, click
**OK**. - The dialog box will tell you if Goal Seek was able to find a solution. Click
**OK**. - The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least
**63**marks in final exam to get first class.

**Example 2:**

Let’s say you need a **loan** to buy a new car. You already know you want a **loan amount** of $20,000, a 60-month **term**—the length of time it takes to pay off the loan—and a** payment** which is not more than $400 per month. However, you’re not sure yet what the **interest rate **will be.

In the image below, you can see that **Interest Rate** is left blank and **Payment** is $333.33. This is because the payment is being calculated by a specialized function called the **PMT (Payment) function**, and $333.33 is what the monthly payment would be if there was **no interest** ($20,000 divided by 60 monthly payments).

Function calculating the monthly paymentIf we typed different values into the empty **Interest Rate** cell, we could eventually find the value that causes **Payment** to be $400, and that would be the highest interest rate that we could afford. However, **Goal Seek** can do this automatically by starting with the **result** and **working backward**.

We can use Goal Seek to find the interest rate we’ll need.

- From the
**Data**tab, click the**What-If Analysis** - Select
**Goal Seek**. - A dialog box will appear containing three fields:
**Set cell:**This is the cell that will contain the**desired result**(in this case, the monthly payment). In this example, we will set it to**B8**(it doesn’t matter whether it’s an**absolute**or**relative**reference).**To value:**This is the desired result. We’ll set it to**-400**. Because we’re making a payment that will be**subtracted**from our loan amount, we have to enter the payment as a**negative number**.**By changing cell:**This is the cell where Goal Seek will place its answer (in this case, the interest rate). We’ll set it to**B7**.

- When you’re done, click
**OK**. The dialog box will tell you whether Goal Seek was able to find a solution. In this example, the solution is**7%**, and it has been placed in cell**B4**. This tells us that a 7% interest rate will give us a $400-per-month payment on a $20,000 loan that is paid off over five years, or 60 months.

**What is Scenario Manager?**

A **Scenario** is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new **scenarios** to view different results.

For each scenario, you need to give it a unique name. The scenarios can be saved as part of the workbook. You can switch between scenarios to see how the inputs can affect the final output by double clicking on any of the scenarios in the Scenario Manager Dialogue box. Moreover, with the saved Scenarios, Excel can also create a beautiful summary report containing the various sets of inputs and corresponding final outputs to facilitate review.

**How to Use Scenario Manager?**

Suppose that we are going to sell a book and would like to know how the Sale Units, Price per Unit and Variable Cost per Unit can affect the final profits. Figure below shows how the dataset is constructed. The profit is dependent on Sale Units in Cell C2, Price per Unit in Cell C3 and the Variable Cost per Unit in Cell C5. Therefore, we typed formula “=C2*C3-C4-C2*C5” in Cell C6.

- Go to
**Data**tab and click on**What-If Analysis**in**Data Tools**In the drop-down, choose**Scenario Manager**. - In the
**Scenario Manager**dialogue box, click on**Add.** - In the prompted
**Add Scenario**dialogue box, fill in the required details. Enter a name**Worst Case**for the**Scenario name**Add any comment that you wish to into the**Comment**box. Or you can also leave it blank. As for the**Changing cells**, fill in the all the reference cells C2, C3, C5 in this case that contain the input values.Please note that the references must be separated by commas. Or, just press CTRL key on your keyboard and select all the cells, one by one, that contain the input values. - Click
**OK**to open the**Scenario Values**dialogue box. Fill in the Scenario Values dialog box with the input values that define the worst case, as shown in Figure. Click on**OK**, the Worst Case scenario will be successfully created. Since we’d like to create another scenario, we click on**Add**button. After clicking on**Add**, another**Add Scenario**dialogue box will appear. - Use the same approach that we applied when creating Worst Case scenario to build Best Case scenario. The details are shown in following Figure.
- With the same approach, create Most Likely Case scenario. Here Figure represents the details. You can also use the same above approach to creating other scenarios if you have other combination of input values. In this example, we assume that there are only 3 scenarios available.
- Now we click on the
**OK**button in**Scenario Values**dialogue box. From the Figure, you can see that three scenarios have been successfully created and they are listed in sequence.

**How to view different scenarios:**

So far, you have saved all of those 3 scenarios in your workbook. Now you can view the result from each of the scenarios by simply double clicking on any of the scenarios. For example, if we double click on Worst Case, the input values in Excel worksheet will change into what have filled for Worst Case and the output value will be calculated automatically based on the formula in cell C6. To view a specific scenario and its corresponding outputs, you can also click on that scenario and then click on the **Show** button at the bottom. The right part in Figure shows how the Excel worksheet looks like if we click on Best Case scenario and then click on **Show**.

**How to create a summary report:**

- I have already told you in the introduction part that Excel can create a summary report based on the saved scenarios. Now let’s see how to make a summary report. Go to
**Data**tab and click on What-If Analysis, then choose**Scenario Manager**in the drop-down to open**Scenario Manager**Dialogue box. In the prompted Scenario Manager dialogue box, click on**Summary Button.** - After clicking on
**Summary**, a**Scenario Summary**dialog box appears for you to put**Result cells**C6 in this case and choose between**Scenario summary**and**Scenario Pivot Table report**. - Figure shows that a Scenario summary report with all of the three scenarios is created with a new tab if you choose Scenario summary in Figure.
- Otherwise, a Scenario Pivot Table report is created with a new tab if you choose Scenario Pivot Table report.

**Notes:**

- It’s hard to create lots of scenarios with the Scenario Manager because you need to input each individual scenario’s input values. The work will be time-consuming and also expose you to a high risk of making a mistake.

- Suppose that you send a file to several people and ask them to add own scenarios. After you receive all workbooks, you can merge all the scenarios into one workbook. Open each person’s workbook and click
**Merge**button in the**Scenario Manager**Dialog box in the original workbook. In the**Merge Scenarios**dialog box, select the workbook containing the scenarios that you want to merge. Do the same things with all of the workbooks. Figure shows you how to choose the workbook in**Merge Scenarios**dialog box.**Click OK.**You will return to the previous dialog box, which now displays the scenario names that you merged from the other workbook.

**Reference:**

- https://www.gcflearnfree.org/excel2010/using-whatif-analysis/1/
- http://stephenlnelson.com/articles/what-if-analysis-scenario-manager-excel/

**About M.Hemalatha:**

M.Hemalatha is a B.Tech (Telecommunication Engineering). Currently she is working as Analyst Intern with NikhilGuru Consulting Analytics Service LLP (Nikhil Analytics), Bangalore.

## Leave a comment