Smart Goal Seek Excel Add-In
Let us present the Smart Goal Seek add-in that extends the capabilities of Excel when you work with the Goal Seek feature. It also offers the powerful Sensitivity Analysis tool.
Now you do not have to use the Goal Seek feature several times in order to determine the value of a cell for several values of its parameter. Just use the Smart Goal Seek feature instead.
Installation
Close all open Excel projects before the installation. Then you should run the installation file. The add-in will be installed after you complete all steps of the Wizard.
You can remove the add-in either via the Start menu or using the Control Panel (Start menu :: Settings :: Control Panel :: Add or Remove Programs).
Smart Goal Seek Command
For example, let us test it using the loan calculator. Suppose we have a loan for 20 years with monthly repayments and the annual interest rate of 6%. The monthly repayments will be $286.58 if the loan is $40,000. Let us try to find the loan amount when the monthly payments will be $300, $400, $500, ..., $1000. Start the Smart Goal Seek feature and fill the fields in the dialog box:
- Set Cell - the address of the cell whose values are known
- Cell Name - cell name (an optional parameter)
- Range / Values - it is possible to specify values for the cell either as a range or with the help of the minimum and maximum values
- Change Cell - the address of the cell whose values should be calculated
In this case, we know the repayment values (cell D15) with the minimum of $300 and the maximum of $1000, as well as the repayment increase step of $100. After that set the address of the cell whose values you want to calculate - loan amount (cell D8). After you click the "Run" button, a new sheet will be created with loan amounts whose repayments will be $300, $400, $500, ..., $1000. You can see from the table that the repayment will be $700 if the loan is $93887.48.
Smart Sensitivity Analysis Tool
With the Sensitivity Analysis tool, you can see how the calculation results will change if you change any of possible parameters. For example, you can take a look at how the monthly repayment will increase in case you change the loan period from 5 to 40 years. You can have several changing parameters (up to 100 parameters). This way you can get a whole array of results for their possible values.
Here is an example. After opening the Tool dialog box, you should specify the list of changing parameters and the range of input cells. To specify a new parameter, click the Edit button.
You should specify the address of the cell, the name of the parameter and the array of possible values (a range or its minimum and maximum values) in the Input Parameter dialog box.
After that specify the parameter you want to see - repayment (cell D15). After you click the Run button, a new sheet with the array of results will be created. You can study several cells at once. In this case, you can specify not only cell D15, but the whole range D15:D21, and also the corresponding cell names - C15:C21.
Let's make using Excel more comfortable!
Download (180 Kb)