Using Goal Seek - Exercise

Contact Us or call 1-877-932-8228
Using Goal Seek - Exercise

Using Goal Seek

Duration: 15 to 25 minutes.

In this exercise, you will practice using Goal Seek to adjust the number of periods it will take to pay off a loan based on varied payments and to figure out what grade a student needs on their final exam to achieve a target grade for a course.

  1. Open Goal Seek.xlsx from your Excel2013.3/Exercises folder.
  2. The worksheet named "Loans" contains information on four loans. The Payment is calculated via the PMT formula. Use Goal Seek to determine:
    1. How many months (periods) it will take to pay off your boat if you can only pay $100 per month.
    2. How many months (periods) it will take to pay off your first car if you can only pay $250 per month.
    3. How many months (periods) it will take to pay off your second car if you can pay $200 per month.
    4. How many months (periods) it will take to pay off your house if you can pay $1,000 per month.
  3. The worksheet named "Grades" contains scores for a number of students on four exams, an empty column where the score for the fifth exam will go, and a column showing the average score on all exams. Use Goal Seek to figure out:
    1. What grade Babe Ruth needs to get on the last exam to raise his final grade to 90.
    2. What grade Barry Bonds needs to get on the last exam to achieve a passing grade (65).
    3. What the lowest grade Cal Ripken can get on the last exam to finish with a final grade of 90.

Solution:

  1. To determine how many months it will take to pay off your boat if you can only pay $100 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek Command
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B2.
      2. In the To value box, enter -100.
      3. In the By changing cell box, enter D2:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept Change
    4. The correct result is 63 months.
  2. To determine how many months it will take to pay off your first car if you can only pay $250 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B3.
      2. In the To value box, enter -250.
      3. In the By changing cell box, enter D3:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. The correct result is 148 months.
  3. To determine how many months it will take to pay off your second car if you can pay $200 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B4.
      2. In the To value box, enter -200.
      3. In the By changing cell box, enter D4:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept Change
    4. The correct result is 42 months.
  4. To determine how many months it will take to pay off your house if you can pay $1,000 per month:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell B5.
      2. In the To value box, enter -1000.
      3. In the By changing cell box, enter D5:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. The correct result is 252 months.
  5. To figure out what grade Babe Ruth needs to get to raise his final grade to 90, go to the sheet named "Grades" and then:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G2.
      2. In the To value box, enter 90.
      3. In the By changing cell box, enter F2:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Babe Ruth needs a 100 on the final exam to raise his average to 90.
  6. To figure out what grade Barry Bonds needs to get on the last exam to achieve a passing grade (65):
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G8.
      2. In the To value box, enter 65.
      3. In the By changing cell box, enter F8:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Barry Bonds needs a 133 on the final exam to achieve a passing grade. Poor Barry Bonds.
  7. To figure out the lowest grade Cal Ripken can get on the last exam to finish with a final grade of 90:
    1. On the DATA tab, in the Data Tools group, click the What-If-Analysis command and then click Goal Seek:Goal Seek
    2. In the Goal Seek dialog box:
      1. In the Set cell box, enter cell G9.
      2. In the To value box, enter 90.
      3. In the By changing cell box, enter F9:
      4. Click OK:Click OK
    3. In the Goal Seek Status dialog box, after the calculation has completed, click OK to accept the change:Accept the Change
    4. Cal Ripken needs at least a 79 on the final exam to finish with a final grade of 90.
Next