Explain Goal Seek feature with the help of an example.
Answers
Answer:
Explanation:
Technically, Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values. For our purposes, Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, put another way, Goal Seek determines input values needed to achieve a specific goal. Use Goal Seek when you don't have an exact value to use.
Calculating the terms of a loan is a good example of a what-if situation. What if the payment your lender offers is too high for your budget? Using Goal Seek can help by evaluating the payment formula and returning new values for the rate, term, and even the principal to fit your desired payment. Figure A shows a simple mortgage calculator where all variables are known: to repay a 6% loan on $200,000 over 15 years, your monthly payment must be $1,687.71. (Deposits matter, but we won't complicate this example with more variables than we need.)
ou want the house, but your ideal payment is $1,200. You could spend a lot of time plugging in new rate and term values until you get lucky. A better solution is to use Goal Seek to determine the rate and term necessary to reduce your payment. First, let's calculate the necessary rate, as follows:
1)Select the cell that contains the formula. In this case, that's E4.
2)Click the Data tab. In the Data Tools group, click What-If Analysis and choose Goal Seek (Figure B). In Excel 2003, Goal Seek is on the Tools menu.
3)In the resulting dialog, you'll find three input values. The Set cell value defaults to the selected cell, E4. (That's the cell that contains the PMT() function.) The To value should equal your goal. In this case, enter the payment you can afford, -1200 (the value is negative because it's a payment). The By changing cell value points to the input value you want to adjust. Enter B4 (the interest rate) in that control
4)Click OK, and Excel will iterate (using built-in settings) until it finds the best result (Figure D). You need a 1% interest rate to reduce your payment to $1,200.
If Goal Seek doesn't work, revisit the To value in step 3. Specifically, you must enter the desired payment as a negative value. To return to the original values, click Cancel in step 4 instead of clicking OK after viewing the result in the dialog. Or, you can click Undo on the Quick Access Toolbar (QAT) after clicking OK. If Goal Seek can't find a solution for you, Excel enables the Step and Pause buttons, which allows you to force further iterations to reduce the gap between the current values and your goal. (
Answer:
Technically, Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values. For our purposes, Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. ... Calculating the terms of a loan is a good example of a what-if situation.