Microsoft Office Tutorials and References
In Depth Information
Exhibit 9.20 Goal seek solution to PMT of $5000
complex calculation may make a very significant difference in the application of
Goal Seek.
We will repeat the Goal Seek for a new set of inputs, and now we will change
the Set cell entry to C20, the sum of two years of accumulated interest, and
the To value entry to $25,000. The Changing cell entry will remain C11. In
Exhibit 9.21 we see the new Goal Seek entry data, and in Exhibit 9.22 the results
of the Goal Seek analysis. The results are a bit troubling in that the dialogue box
indicates that the tool “ may not have found a solution .” How is this possible? The
algorithm used to find solutions is a search technique that does not guarantee a solu-
tion in all cases. Additionally, these types of algorithms are often very sensitive to
where the search starts, i.e. they use the value that is currently in the cell to begin
the search for the goal. In the case of Exhibit 9.21, the changing cell contained 48
periods, so this is where the search began. The search terminated at 24 periods and a
cumulative sum of $26,835.08, but the tool was unsure of the solution. The problem
we face is that it is impossible to achieve a $25,000 in a term of greater than or
equal to 24 months and the problem required that 24 months be used in the calcu-
lation period. But, some experimentation shows that the end period in cell H11 can
be changed to 18 and 19 months to achieve a value very near $25,000, $24,890.93
and $25,443.72 respectively. Obviously, this is a complex condition and may take
considerable experience before it is easily identified by an Excel analyst.
Search JabSto ::

Custom Search