Use quiz scores to help Goal Seek training hit home

Learning Excel's Goal Seek feature is difficult for students perplexed by accounting terms and methods. Cut through the confusion by teaching demo problems almost all students understand. Show them how Goal Seek can manipulate their quiz scores.

Most courses introduce Excel’s business analysis tools with a demonstration of Goal Seek. Because Goal Seek’s analytical capabilities are limited to equations with only one variable, students need focus on only one changing condition to solve a problem, making it an excellent introduction to what-if analysis. Why, then, did I find that even after they successfully completed the introductory exercises, most students had difficulty later applying Goal Seek to an original problem?

Trying to model their solutions against the demonstration exercise in their texts did not seem to help. When they came to the Goal Seek dialog box, they did not understand how the demo exercise came up with a value for the ”set” cell, or how the “changing” cell was related to solving the problem. It became clear that most of the students needed a lesson on the business principles involved with the demo problem before they could learn how to apply Goal Seek.

Teach accounting first?
Most texts use a corporate budgeting worksheet as a basis for teaching Goal Seek. A demo problem typically involves determining how much a company needs to reduce their existing salary budget to gain a 2 percent increase in profits. Since most students in Excel classes do not have a background in financial management, they find it hard to understand what is being asked by the problem, let alone learn how to solve it with Goal Seek. Without an understanding of such terms as revenue, expenses, net income, overhead, percent of revenue, etc., these students were unable to see how the demo got the numbers it did with Goal Seek.

Would prefacing the Goal Seek demos with a lesson on the business principles involved in corporate budgeting help? It did, to some extent, but by the time we got to the actual demo, many students began suffering from information overload. Students unfamiliar with accounting just couldn’t relate the business concepts to their own everyday decision-making. Some even found the concept of decreasing a salary budget to raise profits disturbing.

Since explaining the financial concepts at this stage proved much too intense for non-business students, I decided to substitute an example closer to the students’ experience—one that did not require an additional lesson in accounting. In this way, I was able to shift the focus of the beginning lesson from teaching that particular problem to teaching the larger skill of problem analysis.

A beginning lesson on Goal Seek
Students constantly ask me whether or not they will pass the course (or get an A) if they do not do well on the final quiz. (There are usually four quizzes in the course, the average of which is a major portion of their grade.) I decided to have my Excel students use Goal Seek to answer such questions for themselves.

I began by presenting this question to the students: What must a student score on Quiz 4 to get an A in the course, if the scores on the first three quizzes were 90, 75, and 90?

Then, I set up a worksheet that looked something like this:

Why use the Average function?
Before I went any further, I took some time to explain the importance of using the Average function. Do not assume your students know how to use the Average function. Some will try to bypass the function and create their own formulas that are likely to produce incorrect results when Goal Seek is applied.

To show the importance of using the Average function, point out that when the cell containing the score for Quiz 4 is left blank, the Average function ignores it in its calculation. Thus, in the above worksheet, the Average function will divide the total score by 3, not 4, to get “85” even though C5 is referenced in the function.

Once a value is entered in C5, Average will include that cell in its calculations. This is important. Most errors occur when the students try this example on their own and neglect to reference the empty “changing” cell in their average formulas.

The next step is to have the students go through the thinking process to arrive at an answer themselves. If they have to do the calculation “manually,” they will get a better idea of how Excel automates the process for them.

I had them perform their own “what-if” analysis by plugging in different test scores for Quiz 4 to see which one would yield the desired result. In this case, the desired result was an A average and to get that, C6 had to be equal to 90 or above. The students found that none of their guesses produced an A average—not even 100.

Let Excel do it
Having tried to solve the problem manually, the students were now ready to have Excel work on the problem for them. Unlike hit-and-miss guessing, Goal Seek lets us tell Excel what we want the final result to be, and then has it come up with the correct answer to get the desired result.

To begin, I showed them how to get to Goal Seek from the Tools menu. Once the dialog box appeared, I showed them how to identify the ”set” cell and the ”changing” cell Goal Seek needed to solve to the problem.

The “set” cell is the cell with the formula in it. In this case, it was C6, the cell with the Average function. Goal Seek needed to know what the desired result of this cell should be. Since the quiz average had to be at least 90 to get an A, we entered 90 in the To Value text box. Finally, we had to specify the “changing” cell or C5, the cell that will contain the correct value for Quiz 4.

Once we clicked OK, Excel input “105” as the correct score for Quiz 4. Each time I perform this demo, the students’ reaction is typically one of surprise (sometimes laughter) as they realize my mythical student has no chance of getting an A!

Learning in less time
By using a problem that they could immediately understand, the students learned Goal Seek in a fraction of the time it took to learn it using the corporate budget demo. In that short time, they learned how to use a spreadsheet to ask what-if questions; how to use a reference cell in a formula to recalculate various options; and, more importantly, how to set up a problem where you already know the desired result—you just want to know how to get there.

They were not only prepared for the next lesson on Solver, but almost all students left the class knowing how to use Goal Seek. They were even able to apply it later to a budgeting problem, once the basic business principles were explained.

And there was one other advantage to using quiz averages to introduce Goal Seek: I got fewer questions regarding final grades.
Do you teach accounting first? What about basic algebra? What are the keys to explaining math to non-numerically minded students? Send us your approach to a basic Excel class so we can share your success with other TechRepublic readers.

Editor's Picks

Free Newsletters, In your Inbox