20.2. Using Goal SeekThere are some problems that formulas just can't crack. One of these is solving questions that need trial-and-error guesswork. In most Excel spreadsheets, you begin with a set of data and use formulas to analyze that data and calculate some conclusions. When you create plans and projections, however, you sometimes invert this process and start with the conclusions. You may begin with a profit projection and calculate "backward" to find out the sales you need to make. Or, you may calculate the rate an investment needs to increase in value in order to meet a set target. Goal seeking is a fairly simple tool in Excel that can help you answer these sorts of questions, provided your problem meets the following guidelines:
To better understand goal seeking, it helps to think about a simple example. Consider the worksheet shown in Figure 20-7, which shows a list of student grades. Grace Dewitt is given the chance to resubmit the assignment, and she wants to determine what grade she needs on the assignment to increase her final grade from 72 to 80 out of a possible 100.
To use goal seeking in this scenario, just follow these steps:
20.2.1. Goal Seeking with Complex EquationsIn the student grade example, you don't necessarily need to use goal seeking to get the answer you're looking for. You could get the same information by rewriting the equation. To understand how this approach works, it helps to take a closer look at the formula that calculates the final grade: =(B3/B12)*25% + (C3/C12)*25% + (D3/D12)*50%
In this formula, the two tests are each worth 25 percent of the final grade, and the assignment is worth the remaining 50 percent. However, using a dash of high school math, you can rearrange this formula to find the grade you need on the assignment to get an 80 percent overall grade ( assuming you already know your grades on the two tests). Here's the answer: =(80% - (B3/B12)*25% - (C3/C12)*25%)/50%*D12 This formula looks a little intimidating at first glance, but it's really not too difficult to understand. First of all, you start with the 80 percent final grade you're trying to achieve. You subtract the two test scores to find the value you need from the assignment. Finally, you divide that value by 50 percent (the weighting of the assignment) and multiply it by D12 (the total score available in the assignment). The end result is 91.25. Based on this example, you may assume that goal seeking is just a tool for quickly calculating a number that you could obtain on your own by rewriting one or more formulas. Although that's often the case, there are some types of formulas that you can't reorganize to provide an answer. These formulas often have exponents and use the value in the variable cell more than once. You can find many examples of this sort of equation in the scientific world. One common example is the quadratic function , which shows up in lots of different scientific modeling and engineering formulas. A typical quadratic formula looks like this: y = x2 + 5x + 10 In this example, it's easy to calculate the result (y) provided you know the input value (x). However, it's much more difficult to perform the reverse taskdetermining x if all you know is y. Try as you might, there's no way to rearrange the formula to solve the problem. In this case, goal seeking becomes very useful. It uses a trial-and-error approach that can usually get the answer you need. Excel tries different values of x in succession trying to get closer and closer to the target result. To put the problem into Excel's formula format, assume that cell B1 contains the x value. Here's what the formula would look like: =B1^2 + 5*B1 + 10 Figure 20-10 shows an example worksheet that puts this function to the test. It's easy to see that an input value of 20 produces a result of 510. But what input value do you need to get a result of 1000? In this example, goal seeking is the only easy way to find the answer (which, incidentally, is 29.06343).
Note: It's often difficult to reverse engineer a formula in financial calculations, like those that calculate how an investment appreciates in value. However, as you saw in Chapter 10, Excel includes financial functions that can calculate in both directions: the final value of an investment based on a given interest rate, or the required interest rate or investment length based on your desired goal. Of course, you can still use goal seeking with financial functions to find an answer quickly without writing a new formula.
|