7.3: What-If Analysis- Goal Seek and Scenario Manager

Last updated
Save as PDF

Page ID: 151317

Gabrielle Brixey
Coalinga College

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$ \newcommand{\dsum}{\displaystyle\sum\limits} $

$ \newcommand{\dint}{\displaystyle\int\limits} $

$ \newcommand{\dlim}{\displaystyle\lim\limits} $

$ \newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$

( \newcommand{\kernel}{\mathrm{null}\,}\) $ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\id}{\mathrm{id}}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\kernel}{\mathrm{null}\,}$

$ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$

$ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$

$ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\AA}{\unicode[.8,0]{x212B}}$

$ \newcommand{\vectorA}[1]{\vec{#1}} % arrow$

$ \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$

$ \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vectorC}[1]{\textbf{#1}} $

$ \newcommand{\vectorD}[1]{\overrightarrow{#1}} $

$ \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} $

$ \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} $

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$\newcommand{\longvect}{\overrightarrow}$

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$\newcommand{\avec}{\mathbf a}$ $\newcommand{\bvec}{\mathbf b}$ $\newcommand{\cvec}{\mathbf c}$ $\newcommand{\dvec}{\mathbf d}$ $\newcommand{\dtil}{\widetilde{\mathbf d}}$ $\newcommand{\evec}{\mathbf e}$ $\newcommand{\fvec}{\mathbf f}$ $\newcommand{\nvec}{\mathbf n}$ $\newcommand{\pvec}{\mathbf p}$ $\newcommand{\qvec}{\mathbf q}$ $\newcommand{\svec}{\mathbf s}$ $\newcommand{\tvec}{\mathbf t}$ $\newcommand{\uvec}{\mathbf u}$ $\newcommand{\vvec}{\mathbf v}$ $\newcommand{\wvec}{\mathbf w}$ $\newcommand{\xvec}{\mathbf x}$ $\newcommand{\yvec}{\mathbf y}$ $\newcommand{\zvec}{\mathbf z}$ $\newcommand{\rvec}{\mathbf r}$ $\newcommand{\mvec}{\mathbf m}$ $\newcommand{\zerovec}{\mathbf 0}$ $\newcommand{\onevec}{\mathbf 1}$ $\newcommand{\real}{\mathbb R}$ $\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}$ $\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}$ $\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}$ $\newcommand{\laspan}[1]{\text{Span}\{#1\}}$ $\newcommand{\bcal}{\cal B}$ $\newcommand{\ccal}{\cal C}$ $\newcommand{\scal}{\cal S}$ $\newcommand{\wcal}{\cal W}$ $\newcommand{\ecal}{\cal E}$ $\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}$ $\newcommand{\gray}[1]{\color{gray}{#1}}$ $\newcommand{\lgray}[1]{\color{lightgray}{#1}}$ $\newcommand{\rank}{\operatorname{rank}}$ $\newcommand{\row}{\text{Row}}$ $\newcommand{\col}{\text{Col}}$ $\renewcommand{\row}{\text{Row}}$ $\newcommand{\nul}{\text{Nul}}$ $\newcommand{\var}{\text{Var}}$ $\newcommand{\corr}{\text{corr}}$ $\newcommand{\len}[1]{\left|#1\right|}$ $\newcommand{\bbar}{\overline{\bvec}}$ $\newcommand{\bhat}{\widehat{\bvec}}$ $\newcommand{\bperp}{\bvec^\perp}$ $\newcommand{\xhat}{\widehat{\xvec}}$ $\newcommand{\vhat}{\widehat{\vvec}}$ $\newcommand{\uhat}{\widehat{\uvec}}$ $\newcommand{\what}{\widehat{\wvec}}$ $\newcommand{\Sighat}{\widehat{\Sigma}}$ $\newcommand{\lt}{<}$ $\newcommand{\gt}{>}$ $\newcommand{\amp}{&}$ $\definecolor{fillinmathshade}{gray}{0.9}$

Excel provides tools that allow users to test different outcomes and reverse-engineer solutions without manually adjusting formulas. These what-if analysis features are powerful for exploring multiple possibilities, evaluating options, and making informed decisions.

Goal Seek

Goal Seek solves backward problems—when you know the desired result but not the input required to reach it. Instead of trial and error, Excel automates the process.

Steps to Use Goal Seek:

Go to Data > What-If Analysis > Goal Seek.
Set the target cell (the formula you want to reach a specific outcome).
Enter the goal value (desired result).
Identify the changing cell (the input Excel should adjust).
Run Goal Seek and review the result.

Example: Using the mortgage example, suppose you would like to determine what value of home you can afford. Most mortgages have a term of 30 years (360 months), and you are planning on a 5 percent interest rate. You would like a monthly payment not to exceed $850. The Goal Seek tool can determine the loan amount that will keep the mortgage payment within your budget. Go to the Data tab, click on the drop-down arrow next to What-If Analysis, and choose Goal Seek (Figure 11.70). Notice the input window has three arguments: Set cell, To value, and By changing cell.

Goal Seek pane displays options: Set cell ($D$3), To value (850), By changing cell: ($C$3). Cell A3 selected with 5% (Rate) inside. Cell C3 selected with $250,000.00 (Loan Amount) inside.

Figure 7.3.1: Goal Seek is similar to Solver, but you are restricted to a single input variable. (Used with permission from Microsoft)

In this example, type $D$3 in the Set cell field, 850 in the To value field, and $C$3 in the By changing cell field (loan amount). Click OK (Figure 11.71). The result will display in the window if Excel found a solution.

Goal Seek Status pane displays: Goal Seeking with cell D3 found a solution. Target value: 850. Current value: $850.00. Cell A3 is selected with 5% (Rate) inside. Cell D3 contains $850.00.

Figure 7.3.2: Goal Seek displays the result if a solution is found in the Goal Seek Status window. (Used with permission from Microsoft)

Your budgeted monthly payment of $850 will allow you to afford a home that is approximately $158,000. The result window also provides the target value (850) and current value ($850.00) for the solution. When you click OK, Excel retains the solution in the spreadsheet. If you want the values to return to their original numbers, click Cancel instead. You can use the tool to examine various options, such as the price you could afford if you increased your monthly payment to $900. This tool provides information for data analysis and contingency planning when you are focused on one input variable, but it is limited in its use for in-depth planning and analysis.[GB1]

Scenario Manager

While Goal Seek focuses on one target value at a time, Scenario Manager allows comparison of multiple sets of inputs to explore a range of possible outcomes.

Steps to Use Scenario Manager:

Go to Data > What-If Analysis > Scenario Manager.
Click Add and define a scenario by choosing input cells and values.
Repeat to add more scenarios (e.g., “Conservative,” “Expected,” “Aggressive”).
Generate a summary report or switch between scenarios to compare results.

Data tab selected. What-If Analysis button selected and opens to options: Scenario Manager, Goal Seek, and Data Table.

Figure 7.3.3: The Scenario Manager tool provides a way to look quantitatively at contingency planning. (Used with permission from Microsoft)

A scenario determines the values that Excel will use to change the input cells. You can save different scenarios with different values to view the impact on your target cell(s) at the same time. When you choose Scenario Manager, an input window will appear (Figure 11.64). From here, you will set the changing cells that define the scenario and the target cell for the results.

Scenario Manager pane displays Scenarios options: window reds: No Scenarios defined. Choose Add to add scenarios. Add button selected at right. Changing cells and Comment windows blank.

Figure 7.3.4: To use the Scenario Manager, first set the changing cells and target cell. (Used with permission from Microsoft)

Example: Let’s begin with a simple example of obtaining a home mortgage. The initial mortgage is for $150,000 for 30 years with an interest rate of 5 percent. You want to find a better interest rate and see the impact that rate will have on your payment and the total amount you will pay. Using the PMT (payment) and FV (future value) functions from the Function Library, you can quickly determine the status of the loan (Figure 11.65). With these values at the current rate of 5 percent, you will pay just over $800 per month and at the end of the life of the loan, you will have paid nearly $260,000. The functions by default return a negative number for the results, as indicated by the parentheses and the red font color. You will need to adjust the equation to convert the number to a positive number. You can either put a “-” in front of the function or use the ABS function at the beginning of the formula to use the absolute value of the number. You can use the Scenario Manager to investigate different interest rates and different payment terms if you decide you want to pay the loan over 15 years or a time frame other than the standard 30-year mortgage term. You will use the same mortgage example to investigate different levels for the interest rate and repayment terms.

In the spreadsheet, cell E3 is selected and =FV(A3,12,D3,C3) is in the formula bar. ($256,561.46) is inside cell E3 in red font.

Figure 7.3.5: The functions by default return a negative number for the results as indicated by the parentheses and the red font color. (Used with permission from Microsoft)

Adding and Deleting a Scenario

Using the mortgage example, you can create a scenario to look at the impact of lowering the interest rate on both the payment and the total amount paid for the loan. First, to add a scenario, go to the Data tab, choose What-If Analysis, and select Scenario Manager. Click Add and the Add Scenario dialog box will appear (Figure 11.66). Here, you will define the changing cells and name the scenario. You can name this scenario “rate changes.”

Add Scenario pane displays options: Scenario Name: (rate changes), Changing cells: (E3), Ctrl+click cells to select non-adjacent changing cells. reds beneath, Comment: (Created by AM on 3/25/2021), Protection (Prevent changes (selected), Hide).

Figure 7.3.6: To add a scenario, click Add in the Scenario Manager window and define your scenario in the dialog box. (Used with permission from Microsoft)

Next, select the cell you want to change. To change the rate, you would select cell A3. The Scenario Manager does allow you to add more than one cell in the scenario. In this example, you will also change the length of time for the loan. Select A3:B3 for Changing cells. You can add comments if desired to explain the scenario, such as details about the reason for the selected ranges and other relevant information. Then, click OK. Next, you will define the levels or range of values that Excel will use to change the value in cells A3 and B3. Assume you want to compare the impact of a rate higher than the original 5 percent and a loan term shorter than the original 360 months to the impact of a lower rate and longer loan term (Figure 11.67). Add two separate scenarios at the various levels for each cell, A3 and B3. Click OK. This will take you back to the Scenario Manager window. Add the other scenario in the same way.

Row 2 displays headers and Row 3 displays numerical values. Scenario Values pane reads across top: Enter values for each of the changing cells. Options: 1: ($A$3, 0.055) and 2: ($B$3, 180).

Figure 7.3.7: Define the ranges of values for Excel to manipulate in the scenario. (Used with permission from Microsoft)

Notice that you have two scenarios listed in the Scenario Manager (Figure 7.3.8).

Scenario Manager pane displays selected options: Scenarios (High rate, Low rate). Add, Delete, Edit, Merge, Summary buttons visible. Changing cells: ($A$3:$B$3), Comment: (Created by AM on 3/25/2021, Modified by AM on 3/25/2021).

Figure 7.3.8: The Scenario Manager lists all the active scenarios on a spreadsheet. (Used with permission from Microsoft)

There are a couple of options at this point. If you want to see the results from either scenario on the spreadsheet, click the appropriate scenario in the list and click Show. This will change the value in the spreadsheet, but only for one scenario at a time. If you choose Summary, the Scenario Manager will compile the information for all the scenarios in the list in a clear table on a separate worksheet so that you can compare the results (Figure 7.3.9). You can then format and adjust the table to fit your needs. One other functionality of the Scenario Manager is that it can merge scenarios from other worksheets into a single worksheet using the Merge button.

Scenario Summary spreadsheet header row includes Current Values, High rate, Low rate columns. Changing Cells and Result Cells sections follow with populated cells. Numbers with () indicated in red. Note under table.

Figure 7.3.9: The Scenario Manager tool provides a summary of the results on a separate worksheet. (Used with permission from Microsoft)

To delete a scenario, go to the Scenario Manager and select the scenario you want to delete from the list and choose Delete. If you have created a scenario summary table, the table will remain even if you delete the scenario. If you instead selected Show and the scenario is displayed on the spreadsheet, the numbers will not revert to the original values even if you delete the scenario. Rather, you will lose your original data. For this reason, it is generally better to use the Scenario Summary rather than the Show option.

This page was created by pulling information from Workplace Software and Skills by OpenStax, CC BY 4.0.

Search

Text Color

Text Size

Margin Size

Font Type