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.

1. 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.
2. 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.
3. 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.
4. When you’re done, click OK.
5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
6. 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.

1. From the Data tab, click the What-If Analysis
2. Select Goal Seek.
3. 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.
4. 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.

1. Go to Data tab and click on What-If Analysis in Data Tools In the drop-down, choose Scenario Manager.
2. In the Scenario Manager dialogue box, click on Add.
3. 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.
4. 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.
5. Use the same approach that we applied when creating Worst Case scenario to build Best Case scenario. The details are shown in following Figure.
6. 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.
7. 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:

1. 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.
2. 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.
3. 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.
4. Otherwise, a Scenario Pivot Table report is created with a new tab if you choose Scenario Pivot Table report.

Notes:

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

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