3.2.8 What if analysis; Goal seek and solver

It is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

Goal seek

The simplest sensitivity analysis tool in Excel is goal seek. Assuming that you know the single outcome you would like to achieve, the goal seek feature in excel allows you to arrive at that goal by mathematically adjusting a single variable within the equation.

To illustrate how it works, imagine that the bank is offering an interest rate of 9% per annum on personal loans with 24 months to repay, and that you would like to borrow $40,000.

Using the above information, the bank calculates that the amount borrowed plus interest over the loan period will be $47,200, as shown in cell B5. The amount to be paid each month is also calculated and shown in cell B6.

By using the Goal Seek command, we can indicate a desired outcome and Excel will determine the adjustment we need to make to a single variable.

In the example above, cell B5 is dependent on the variables in cells B1, B2, and B3. Cell B6 is dependent on cells B3 and B5. Therefore, if we determine that the monthly repayment amount quoted is higher than desired, we can use Goal Seek to set the monthly amount to $1750. Excel can work backwards to change either cell B1, B2, or B3 to reach that goal.

Practically speaking, we may not have much control over the interest rate, so it is more likely that we have the option of adjusting the amount we borrow, or the repayment period.

Our first inclination may be to find out how much we will be able to borrow if we pay $1750 per month and all other variables remain the same. Excel will change the principal (B1) based on the number we enter as the new value for cell B6.

Assuming that the interest amount (9%) and loan period (24 months) remain the same, the new principal amount is calculated and displayed in cell B1 if a valid solution exists.

Points to note:

1. The cell chosen in the “Set cell” field must be a cell containing a formula.
2. The cell chosen in the “By changing cell” field must be a cell containing a constant.
3. Once “OK” is selected from the Goal Seek Status window, the values on the worksheet are adjusted and are only retrievable by selecting the ‘Undo’ command (Ctrl+Z Windows shortcut/Cmd+Z Mac shortcut).