 |
|
|
|
|
Download the associated
Workbook
for this lesson
Goal Seek is the second "What-If" analysis tool that we will discuss in this lesson. There are many occasions in today's business world when we know the result of a formula, but we do not know the input value the formula needs to determine the result that we want to see. This is where Goal Seek comes in handy. Goal seek asks the question "What will the value need to be in a particular cell to arrive at a specific total? It then determines which part of your formula needs to be changed in order to achieve the desired result. As with Scenarios, we need to firstly set up a base model with which to perform the Goal Seek. This base model needs to be set up in a Worksheet with the data and formulas already in place and working. For Goal seek to work correctly, you must identify on your base model:
The cell that contains the formula that you want to settle. This is called the Set cell
The value you want the formula to change to. This is called To value
The part of the formula that you wish to change. This is called By Changing Cell
It is important to remember that the Set cell must always contain a formula or a function, whereas the Changing Cell must contain a value only. Not a formula or function.
Let's say that we wish to borrow money from the bank to modify and improve the existing canteen at our place of work. We will use Goal Seek to calculate how the term of the loan will be affected if we increase the Amount of our monthly loan repayments to $6,000.
Open the attached workbook.
There is a formula in B7, which you will need to type to get the result ($4,791.09). It is =PMT(B5/12,B4,B3). This is calculating the monthly repayment of the loan.
It is always prudent to activate Goal Seek with your mouse pointer clicked on the Set cell this is because our Set cell will always contain the formula that we wish to settle. In our example, cell B7 is our Set cell, because it contains the formula calculating the monthly repayments of our loan.
With your mouse clicked in B7, select Tools>Goal Seek to display the Goal Seek dialog box. Notice that as soon as the Goal Seek dialog box appears on your screen, you will see a marquee around the Set cell B7.
As we have already selected our Set cell, we need make no changes in the first box. Click or tab to the To value: box and type in -6,000. Click or tab to the final box By changing cell: and select the collapse dialog button to the right of the box to collapse the dialog box. Select cell B4 which is the term of the loan in months. Note here that as soon as you click on B4, it becomes absolute. Collapse back through to your dialog box and click OK.
As soon as you select OK you will see that Goal Seek calculates a new term of the loan in the Worksheet. We have two options now, OK or Cancel. If we select OK the new term will be inserted into our Worksheet, of you select Cancel, the Goal Seek box will disappear, and your Worksheet will be in its original state. Let's click Cancel and perform a Goal Seek on our Worksheet to see what will happen to the Interest Rate if we wished to repay $6,000.
Select Tools>Goal Seek to display the Goal Seek dialog box again, ensuring that B7 is still your active cell. Click or tab to the To value: box and type in -6000. Now click in or tab to the By changing cell: box. This time we will select cell B5, as it is the interest rate that we wish to change. Click on OK to see the new interest rate that would be used if we repaid $6000 per month. This time, let's select OK to accept the Goal Seek solution into our Worksheet.
So as you can seek Goal Seek is another of the "What-If Analysis Tools" that can be performed on a Worksheet where you can actually specify the end result that you want, then using this very handy tool you can find out what the determining values need to be.