|
Goal
Seek is part of a suite of commands in Excel that are used to conduct
'what-if' analysis. This type of analysis consists of a process where
the user changes values in a single selected cell to determine the
impact of other formulas in a worksheet. This approach is particularly
useful when the user knows the expected result of a formula, but does
not know the initial input starting value to obtain that result. One
example involves loans and interest rates in an amortization table,
whereby the user can determine the amount of payments. The Excel Goal
Seek algorithm can vary the result in only one specific cell, until a
formula that depends on that's dependent on that cell returns the
result desired by the user.
<< Top >>
Excel's GoalSeek limitations
GoalSeek is capable of solving a target cell value by changing only one
cell. It is also susceptible to the limitations of finite precision
computer arithmetic.
Competitive products
Lotus 123's BackSolver has a largely superior algorithm. It can solve
for required target cell values by changing values in a range of
contiguous cells. The drawbacks of their algorithm are that changing
cells are either all incremented or all decremented irrespective of the
natural relationship of the individual cells w.r.t. the target cell.
Further, the changing cells should be contiguous and therefore,
naturally on one
sheet.
<< Top >>
SNGSP Capability Improvements
- SNGSP can work with multiple variable cells, in multiples of sheets
in multiples of open workbooks. Up to 64000 cells in multiple ranges
may be incorporated into the problem - but that figure can be increased
or lowered to suit business strategy. Those cells, if selected, but do
not relate to the target cell by formulae or functions, will be
automatically ignored. Upper and lower bounds may be set individually
for each range. Solver cannot work with multiple worksheets/workbooks.
- SNGSP can work with protected workbooks, Solver cannot.
- SNGSP can/will alter its internal precision differently for each
variable cell in an effort to find a solution (in case the user has
incorporated the Round, etc. functions in his/her workbook).
- SNGSP does not get bogged down if the starting values are such that
the "set up" sub-routine does not detect a change in target cell value.
For example: See the image in http://cargocal.com/interface.html
and look at the Measured Ull column. This column represents the ullage
of the respective tank. (Ullage is a measure of the empty space above
the liquid cargo in a tank - Total tank height less the height
(sounding) of the cargo). The ullage for empty tank is usually an odd
figure like 22.068 or 21.567, etc. The users of the program will not
write this cumbersome figure to indicate empty tank, they will adopt a
rule such as to enter 23 in tanks that are empty. With 23 in the
measured ullage column, the volume, etc. will be zero. SNGSP will not
be bogged down by this, as it will progressively increase its internal
precision in an effort to detect and measure a change. Solver cannot
work if start values do not change with changes as per its setup
computing sub-routines.
- SNGSP can use the value of a referenced cell for the set-cell value.
As the value in the referenced cell changes with change of the variable
cells, so will the problem target value change, until an equilibrium is
reached. Solver cannot.
-
SNGSP uses extensive branching routines to overcome non-linear hitches
and limitations of finite precision computer arithmetic. Solver has
more limitations in this
respect.
-
As computing progresses, should certain cells reach their threshold for
upper or lower bounds or if they tend to cause the introduction of an
error situation (#div0, #num, etc), those cells are blocked at their
last acceptable value and computing continues with the remaining cells.
Solver stops at the first encountered error.
-
SNGSP is not an optimizing tool and so cannot compute for optimizing
scenario where minimum, maximum and a set of constraints are set.
Conversely, the optimizing tool, Solver as shipped free with Excel has
a host of limitations and cannot backsolve/goalseek as effectively as
SNGSP.
- SNGSP may be called programmatically from
the user's VBE but limited to 10 ranges. (This feature is under further
development).
Please
check out this article on Excel GoalSeek and Solver limitations. SNGSP
overcomes any of these with start values up to 120,000.
http://www.bus.ualberta.ca/ite/vol2no2/troxell/
Current and potential future uses of Excel with
SNGSP
SNGSP's algorithms are called programmatically in cargo related
spreadsheet based software written by its author for some tank vessels
in various shipping companies. All Excel users are potential
customers for the spreadsheet based solutions that has as one of it
features, backsolving (premium goalseeking).
All those who have changed over from Lotus-123 for various reasons but
sorely miss the BackSolve feature but cannot do much about it will
welcome this add-in.
Conclusions
SNGSP is a tool that should interest any user who knows the result
he/she is looking for but does not know the values in the changing
cells required to achieve this. The more complicated the project,
including a tendency for the project to go into a 'circular'
computation, enhances to evidence of the need of such a tool.
SNGSP
can be improved upon in collaboration with Redmond.
|
SN's Goalseek Premium V1.08