Share Gallery 1

home *** CD-ROM | disk | FTP | other *** search

/ Share Gallery 1 / share_gal_1.zip / share_gal_1 / UT / UT007.ZIP / MANUAL.GS < prev next >

Wrap

Text File | 1988-02-18 | 55KB | 1,431 lines

G O A L S E E K E R U S E R S' M A N U A L Copyright (C) Brown Bag Software 1987 2155 South Bascom Ave Campbell Ca 95008 U. S. A. Tel: (408) 559 4545 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software THE SHAREWARE CONCEPT Copyright 1987 Telemarketing Resources DBA Brown Bag Software. All rights reserved world wide. Both the GoalSeeker program and documentation is copyrighted. It can not be disassembled or modified without our express written consent. GoalSeeker is distributed on a ShareWare basis. You may use GoalSeeker without charge for the purposes of evaluating it's suitability for your use. We suggest that this evaluation period be limited to 30 days of actual use. If you find that GoalSeeker is worth continuing to use, then you are required to pay for it. We call the payment a "Registration Fee". Simply complete the registration form and mail it in to use with your payment. If your company requires an invoice, we suggest that you use a copy of this registration as the pro forma invoice. When you register your copy of GoalSeeker, you'll receive the latest version of the program, along with a comprehensive printed user manual and free unlimited technical support for one year. We aggressively develop our products, and as a registered user you'll be entitled to upgrades to the newest versions as they are released, at a greatly reduced price. Most importantly though, by registering your copy of GoalSeeker (or any ShareWare product), you'll ensure the survival of the convenient and risk free distribution method that brought you GoalSeeker in the first place. If you have received this copy from a Users' Group, "Software Library" or "SIG", and have paid a small fee (usually $3 to $10), you have NOT acquired the registered rights or continued right to use GoalSeeker. This fee was for the convenience of obtaining the diskette with GoalSeeker on it. The fee does NOT apply to the registration fee. If you have obtained a "ShareWare" copy of GoalSeeker from Brown Bag Software, and have paid a small fee, this fee IS applicable towards registration if you register your copy within 90 days. If you don't know whether or not your copy of GoalSeeker is registered or not, then look for an original diskette with a printed Brown Bag Software diskette label. If in doubt, just call us or write us. As of October 1987, Brown Bag Software has offices in London, Copenhagen, Amsterdam, Cologne and Zurich. Our products are available locally in these cities with local technical support, payable in local currency and shipped from within the EEC without duty. If this version of GoalSeeker does NOT support the European keyboard, then you will receive a version that DOES support the keyboard WHEN YOU REGISTER. You may freely copy GoalSeeker for distribution under the ShareWare concept, without charge. You may NOT charge any fee for the GoalSeeker program or documentation without our WRITTEN APPROVAL (usually granted). You may NOT distribute GoalSeeker or it's documentation in connection with ANY commercial venture, product, publication or service without our written approval. 1 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software GETTING STARTED WITH GOALSEEKER DISCRIPTION GoalSeeker is used in conjunction with your spreadsheet (Lotus 1-2-3, etc.) to automatically find solutions with a "reverse" or "backward" search method, also known as "goal seeking". Goal seeking is the ability to achieve certain targeted values in a spreadsheet by varying the associated data. HARDWARE/SOFTWARE REQUIREMENTS GoalSeeker requires an IBM PC, XT, AT or 100% compatible using DOS 2.0 or above. GoalSeeker requires approximately 40k of memory overhead. GoalSeeker must be run in 80 column mode with either a monochrome or color graphics monitor in conjunction with your spreadsheet software. Although not required, GoalSeeker will utilize an 8087 or 80287 math co-processor chip if installed. INSTALLING GOALSEEKER GoalSeeker supports SuperCalc4, Multiplan and VP Planner, as well as Lotus 1-2-3. Use the GSTYPE program to adapt GS.COM to the spreadsheet your are using. IF YOU USE A FLOPPY DISK SYSTEM Insert the GoalSeeker disk in drive A:. Insert your spreadsheet work disk in drive B:. At an "A:>" prompt type: GSTYPE Then Choose the SpreatSheet your are using: 123 if you use Lotus 1-2-3. SC4 if you use SuperCalc4. MP if you use Multiplan. VP if you use VP Planner. The type COPY A:GS.COM B: IF YOU USE A HARD DISK SYSTEM Insert the GoalSeeker disk in drive A:. 2 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software Change into your spreadsheet sub-directory on your hard disk. At a "A:>" prompt type: GSTYPE Then Choose the SpreatSheet your are using: 123 if you use Lotus 1-2-3. SC4 if you use SuperCalc4. MP if you use Multiplan. VP if you use VP Planner. then type: COPY GS.COM C: TO START GOALSEEKER To start GoalSeeker, change to the disk drive and sub-directory where your spreadsheet is stored. If GoalSeeker has been properly installed (as per instructions above), you can type "GS" at the DOS prompt and then press the <Enter> key to run the program. This will load GoalSeeker into the memory of your computer. You can activate GoalSeeker while you are in your spreadsheet with the push of a key. You must be within your spreadsheet to use the GoalSeeker system. NOTE: If you use ANY other memory resident software such as HomeBase you should load GoalSeeker FIRST. Once GoalSeeker is loaded, start your spreadsheet as normal. When the spreadsheet is displayed on the screen, you may activate GoalSeeker by pressing the <ALT><SPACE> key combination. To do this, hold down the key marked "Alt" with your left hand and press the space bar with you right. The GoalSeeker menu will appear on the top line of the screen (over the top of your spreadsheet). The menu looks like this: Help Goal Seek Parameters Multi-goal Review Quit You may use the arrow keys to move to the desired option and press the return key or simply enter the first letter of the option you wish. SET THE GOAL VALUE FIRST Move the spreadsheet cursor to the desired GOAL cell. This cell must be a "formula (calculated) cell". Press the <ALT><SPACE> key combination, then press the "G" key for "Goal". A rectangular box will be displayed over the original position of 3 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software the spreadsheet cursor. Type in the desired goal and press <Enter>. Press the <Esc> key to cancel. Example: If cell B3 has a formula of "+A1*2", set a goal on this cell of 128.00. A B C D 1 12.00 2 GOAL 3 128.00 4 5 NEXT MOVE CURSOR TO SOLUTION CELL Move the spreadsheet cursor to the cell you wish to vary to achieve your desired goal. In our example above, move to cell A1. This cell must be an "input (data) cell". Press the <ALT><SPACE> key combination, and then the "S" key for "Seek". This will begin the goal seeking operation. You will notice the spreadsheet screen flashing as GoalSeeker begins its calculations. Calculations will continue until the solution is found or the maximum number of iterations is reached. Example: (where B3 is +A1*2) A B C D 1 64.00 2 3 128.00 4 5 When the solution is found, usually after 3 tries (iterations), the message "SOLUTION FOUND" will be displayed at the top of the screen. Press the "R" key to "Restore" the original value or any other key to continue. When the "R" key is pressed, the starting value in the solution cell is replaced. A SIMPLE DEMO 4 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software There is a demo worksheet named "DEMO" on your GoalSeeker diskette. After you have typed "GS" and pressed the <Enter> key at the DOS prompt, load the worksheet DEMO into your spreadsheet. Follow the steps outlined on your screen to see how easy GoalSeeker is to use. In this demo the following information is true: "Sales" and "Expenses" are input numbers. "Profit" is calculated as "Sales" minus "Expenses". "TOTAL" is the sum of "Jan", "Feb", and "Mar". "TOTAL Profit" is at cell G20 and we set the goal to $7,000. We want to find what "Sales" are required in "Feb" (cell E17) to reach our goal of $7,000 Profit for the 1st quarter. GoalSeeker MENU When you are in your spreadsheet and press the <ALT><SPACE> key combination, a "1-2-3 like" menu will appear on the second line of your screen such as the one below. Help Goal Seek Parameters Multi-goal Review Quit In the previous example, the word "Help" is highlighted. You may use the right or left arrow keys on the numeric keypad to move to the correct option, or you may enter the first letter of the option desired (i.e., Pressing the "R" key will display the "Review" window). If you press the <Enter> key, the option currently highlighted will be activated. Under the menu is displayed one line explanation of the currently highlighted option. When you change the highlighted option, the one line explanation will change accordingly. Pressing the <Esc> key will cancel the menu and return you to the spreadsheet. HELP OPTION Help can be obtained from the GoalSeeker menu by pressing the "H" key or positioning the cursor on the "Help" option and pressing the return key. A help screen will be displayed. 5 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software GOAL OPTION Two terms are defined here: GOAL CELL is the cell where we want our target number. GOAL VALUE is the desired goal. The GOAL cell must contain a formula, because we will modify another cell to get the calculated value of this cell to equal the goal value you desire. When you press the "G" key on the GoalSeeker menu, the following message will be displayed at the top of your screen: Enter the desired goal in the cell below & press return, (Esc=cancel). The current goal (xx) = yy.yy Where xx is the goal cell and yy is the goal value. A rectangular box with a blinking cursor will be displayed over the GOAL cell (where the spreadsheet cursor was). The user may enter the desired goal followed by pressing the <Enter> key or press the <Esc> key to return to the GoalSeeker menu. To see the original value in the cell, press the <Esc> key. IMPORTANT The GOAL cell will remain in the same location until you enter another location. The GOAL cell value will not change even if you insert or delete rows/columns, even if you change worksheets, or even if you quit your spreadsheet and restart it later. Example: If you enter a goal value in cell C23, quit out of your spreadsheet, restart your spreadsheet and load a different worksheet, the goal cell is still defined as cell C23 with the same goal value. GOAL VALUES The goal value must fit the following rules: Be a numeric value. Begin with one of the characters: 0 1 2 3 4 5 6 7 8 9 . - Must not be entered with commas, dollar signs, or percent signs. 6 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software Can contain up to 15 characters. Must be between -1.e99 and 1.e99, although GoalSeeker stores numbers ranging from -1.e308 to 1.e308. Can only have one decimal point. Can be entered in scientific format. For example, "1.123E-12" or " .344-e44" would be acceptable, where the "E" can be in upper or lower case. You can use the following special keys: You must press the <Enter> key when you have completed the number. You can press the key combination <Ctrl><H>, a backspace key, or the left arrow key on the keypad to correct typing mistakes. The <Esc> key will cancel the number and return you to the GoalSeeker menu. No goal value will be entered. LARGE NUMBERS The rectangular box is the same size as the size of the spreadsheet column width; however, if you wish to enter a number larger than the width of the box, continue to type the goal value up to 15 digits. GoalSeeker will allow you type past the edge of the box. SCIENTIFIC FORMAT A number may be entered with a power of 10 scaling factor. The number may be up to plus or minus 10 to the 99th power. The following chart describes the scientific notation standard. upper or lower case E 1 or 2 digit number xxxxxxE nn either a plus or minus sign any number, positive or negative An example of scientific notation would be "1.234E-74". 7 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software SEEK OPTION The Seek option is used to start the goal seeking process. You should set the goal value first. For more information on setting that value, see "Goal Option". Move the spreadsheet cursor to the SOLUTION cell (the cell we are going to vary) and press the "S" key (for "Seek") to begin "goal seeking". You will see the screen begin to flash as GoalSeeker tries new values in this SOLUTION cell. SOLUTION FOUND Once the solution is found, the correct value will be displayed in a box in the SOLUTION cell, and the following message will be printed on the top line of the screen: SOLUTION FOUND Press the R key to Restore the original value - any key to continue G H I J 23 24 SOLUTION 1.23 3.23 2.37 25 44.84 55.55 63.44 77.21 26 27 In the previous example, if you press any key except "R", the solution will be accepted. When the "R" key is pressed, the starting value (the value before GoalSeeker began processing) is placed back into your spreadsheet. If the recalculation mode is "Automatic", then the spreadsheet will be returned to its original condition. If the recalculation mode is "Manual", the starting value will be replaced, but a recalculation will not be performed. SOLUTION NOT FOUND If the solution has not be found, the following message will be displayed at the top of your screen: Maximum number of iterations reached Press the R key to Restore the original value - any key to continue GoalSeeker will choose the solution which came closest to the desired goal as a final answer. Press any key to accept this 8 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software "best" answer or press the "R" key to "Restore" the starting value in the SOLUTION cell. If the solution was not found, you might try the goal seek again with this new starting value. Usually, the closer the solution value is to the actual value, the more quickly GoalSeeker will be able to find the correct answer. You may wish to look at the Review Option to determine how close GoalSeeker came to the actual goal. See the SPECIAL SITUATIONS section for more information on the "solution not found" message. PARAMETERS OPTION Parameters are used to set limits on the goal seeking process. There are 7 parameters which are used in GoalSeeker. When you press the "P" key on the GoalSeeker menu, the "Parameters" window will appear over the top of your spreadsheet. To change one of the parameters, enter a number from 1 to 7. When a number is entered, you may type in the parameter number, followed by the <Enter> key. The <Esc> key will cancel the current option and/or the parameters menu. Option 7 toggles between "YES" to "NO" when the "7" key is pressed. NOTE: The parameters stay in effect if you load a different worksheet, or even if you quit your spreadsheet and restart it later. The parameters will not be reset until you remove GoalSeeker from memory (with the "REMOVE" command) or turn your computer off. MAXIMUM NUMBER OF ITERATIONS The default value is 10. Typically, GoalSeeker will find the solution in three iterations (number of solutions tried). However, some of the more complex spreadsheets may require more than 10 iterations to find a solution. NOTE: The starting value of the solution is important. If the goal has not been found in the maximum number of iterations, the best value is chosen. You might try the goal seek again with the new value. 9 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software Typically, fewer iterations are required if the starting number is close to the solution. The number of iterations may be set from 1 to 25. PERCENT ERROR ALLOWED IN GOAL The default value is .5%. When the Goal value falls within this percent tolerance (plus or minus), the goal is determined to be found. The default percent error is one-half of 1%. GoalSeeker will usually find the answer exactly; however, this percent error will allow for some flexibility. Example: If .5% is the percent error in the goal and the goal value we are looking for is 100, then a goal of 99.5 to 100.5 is valid. 99.5 100 100.5 If the goal value we are looking for is zero (0), the percent error is multiplied times 1 to determine the variance allowed (1 is used because zero times percent error is zero). Example: If .1% is the percent error allowed in the Goal and the goal we are looking for is 0.0, then a goal of -.001 to +.001 is valid. -.001 0.0 .001 The percent error value may be entered up to 9 decimal places. NOTE: The user may enter any percent error including zero percent (0%) error, however, if a zero percent error is used, the goal value must match exactly to 15 decimal places. PERCENT TO VARY INITIAL VALUE The default value is 10%. The starting value of the SOLUTION cell is multiplied times this 10 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software percentage to obtain the second value. This percentage is used to make the first "guess" to solve the goal seeking problem. The percent value can be within any range, including a minus percentage. In most cases, this percentage will have no effect on the outcome of the solution. However, if the solution is bounded by a small amount, the user may wish to restrict the second guess to say 1%. Example: If the starting value is 10 and the percentage is set to 10%, the second value of the solution will be 11. LOWER/UPPER BOUNDARY OF THE SOLUTION The default value is -1.e99 to 1.e99 (or no bounds). If you know the range of the solution, you may wish to limit the bounds of the solution with this parameter. Example: If you know the solution is positive, set the lower bound to 0. Generally you will not need to set this parameter. RECALC "N" TIMES BEFORE SOLUTION IS PICKED The default value is 1. This parameter is only useful on spreadsheets with a circular reference. Some spreadsheets are required to be executed "N" number of times before the answers in the spreadsheet converge. You should know the number required to make the goal value converge before you use this option. The Recalc "N" times may be set from 1 to 50. MULTI-GOAL: STOP ON ERROR The default value is "YES" This parameter is only used for the "Multi-Goal" option. When "Stop on error" = YES and the maximum number of iterations is reached without finding a goal, the "Multi-Goal" option will stop and issue its normal error message. "Stop on error" = NO is useful if the user wants to control the multi-goal with the spreadsheets macro capability. If the maximum 11 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software number of iterations is reached or any error occurs, the multi-goal macro will be executed. If no macro exists, the next multi-goal value will be executed. If "Stop on error" = NO, no indication that an error has occurred will be given. MULTI-GOAL OPTION The "Multi-Goal" option allows you to run a series of goal seeking jobs at a time. When you press the "M" key (for "Multi-Goal") on the GoalSeeker menu, the "Multiple Goals" window will be displayed over your spreadsheet. GoalSeeker MULTIPLE GOALS Esc=cancel, F10=start multiple goal seek To use multiple goals, a portion of your spreadsheet should look similar to below - each cell is adjacent Enter the cell location within your spreadsheet where the cell C2 below would be located: ______________ (then press return; F10 to execute) SOLUTION GOAL GOAL Results MACRO Cell Cell Value Key (optional) your H I J K spread C2 D3 1234.23 sheet E5 E15 2672.89 MACRO R F5 F15 0.0 MACRO T MULTI-GOAL AND YOUR SPREADSHEET Within your spreadsheet, you should set aside an area which will describe the values required for GoalSeeker to operate. The area should contain 4 columns (cells) of information. These 4 values are on a single row and contain enough information for GoalSeeker to perform one goal seek. This area will describe the SOLUTION cell, the GOAL cell, the desired goal value for the GOAL cell, and optionally, the name of a user defined macro to execute after the goal has been found. NOTE: The word "MACRO" is a keyword to GoalSeeker. RULES FOR MULTI-GOAL SPREADSHEET VALUES The cell values may be upper or lower case (i.e., a23). Cell values must be left justified(i.e., 'C9). 12 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software Only one value per cell. The goal value must be a numeric value. The goal value may be in any numeric format (i.e., $1,234). The keyword MACRO must be left justified in the cell. A single space should separate the keyword MACRO and the single Letter defining the macro (i.e., MACRO G). TO OPERATE To enter the Multi-Goal Option, press the "M" key from the GoalSeeker menu. Next enter the cell location (which is the upper left hand corner of your spreadsheet area), press <Enter> then press the "F10" key to start the Multi-Goal goal seeking process. There is no GoalSeeker limit as to the number of rows used in a Multi-Goal operation. Example: The following example of Multi-Goal and macros demonstrates how you can goal seek on "Sales" for each of 12 months to find a goal of $17,000 in "TOTAL PROFIT" in cell L95. A B C D E 13 14 Jan Feb Mar Apr 15 Sales 1,234 3,434 4,234 6,347 . . . . . . . . . . . . . . . . . . 299 300 B15 L95 17000 MACRO B 301 C15 L95 17000 MACRO C 302 D15 L95 17000 MACRO D 303 304 \B /CB15~B297~ 305 \C /CC15~C297~ To start the above example, enter cell "B300" in Multi-Goal Option. Multi-Goal and Macros The fourth parameter in your spreadsheet is an optional macro name. When the keyword "MACRO" followed by a letter "A" thru "Z" is in the fourth cell, after the goal is found, it will be executed. This macro must be predefined in your worksheet. Example: 13 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software R S T U V 21 22 23 A7 G19 123.33 MACRO I 24 A7 G19 34.51 MACRO K 25 27 \I /FStest1~ 28 \K /PPG To start the above example, enter "S23" in the Multi-Goal Option. In the above example, the cell A7 is the SOLUTION cell, G19 is the GOAL cell, the goal value is 123.33, the predefined MACRO I will be executed after the goal 123.33 has been achieved, then the next row (beginning with S24) will be executed. The goal seeking process will stop on cell S25. MACRO I is a 1-2-3 macro to do a "File Save test1". MACRO K is a 1-2-3 macro to do a "Print Printer Go" (range preset). The macro executed by GoalSeeker can perform almost any function, except as described in the next section. EXAMPLES OF MULTI-GOAL MACROS Save the spreadsheet Print the solution and goal to a printer Load a different worksheet Change the goal value in multi-goal Save the solution in another cell Restore the original value Change the worksheet equations Change a worksheet value NOT ALL MACROS ARE ALLOWED! Macros which request input from the keyboard are NOT allowed to be used with Multi-Goal. Macros such as "{?}", "\XL", and "\XN" can not be used. DISALLOWED 1-2-3 MACROS {?} prompt for input \XL prompt for input \XN prompt for input 14 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software NOTE: No checking for the macro type is done in GoalSeeker. If any of the above macros are used, the goal may not be found. STOPPING THE MULTI-GOAL PROCESS GoalSeeker will process each goal seeking request from top to bottom, one row at time. When a blank, null, or invalid solution cell is reached, the goal seeking process will stop. STOP ON ERROR PARAMETER The default parameter is to stop the goal seeking process if an error occurs or if the maximum number of iterations has been reached. You may set this error option within the Parameters Option. If the Parameter for Multi-Goal is "Stop on Error = NO", then GoalSeeker will not stop on an error or if the maximum number of iterations is reached. You can control the goal seeking process and error trapping with your own user defined macros. See Parameters Option. REVIEW OPTION The Review option is used to review the results of the last goal seek operation. Each solution value, goal value, target goal value, and the differences are displayed for each solution tried. The Review option may be useful if GoalSeeker could not to find the correct solution, or if you wish to more closely examine the goal seeking process. The Review window is overlayed on top of your spreadsheet. An example of the Review window is shown below. The following example is a squared equation (A1^2). GoalSeeker REVIEW Esc=cancel # Solution Goal Actual Diff --- ------------ ----------- ------------ ----------- 1 12.000000000 144.0000000 4.0000000000 140.0000000 2 13.200000000 174.2400000 4.0000000000 170.2400000 3 6.4444444444 41.53086419 4.0000000000 37.53086419 4 2.0000000003 4.000000001 4.0000000000 .0000000012 The starting value of the solution cell was 12 and the starting value of the goal cell was 144. The second solution value is 15 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software 13.2, which is 10% of the starting value (12.00). The 10% value is the "Initial starting percentage" as described in the "Parameters" section. The previous example proceeds through 4 iterations until our goal of 4.0 was reached. In this example, you will notice that the answer is not exact, but it is within our percent error allowed in goal which is described in the "Parameters" section. The Review window will hold approximately 13 numbers. If more than 13 iterations are required to find the goal, the following message will be displayed: Press any key=next page, Esc=cancel Press any key to see the results of the remaining iterations up to 25. GOAL ERRORS If the result of the calculated GOAL cell was in error or had a value such as "ERR", "*******", or "NA", a message will be displayed in the REVIEW window which states "ERROR in Calculation". The solution value which caused the error will also be displayed. This example uses the @SQRT(A1) function in Lotus 1-2-3. GoalSeeker REVIEW Esc=cancel # Solution Goal Actual Diff --- ------------ ----------- ------------ ----------- 1 4.0000000000 2.000000000 1.0000000000 1.000000000 2 4.4000000000 2.097617696 1.0000000000 1.097617696 3 -.0976176980 ERROR in Calculation 4 0.0 0.0 1.0000000000 -1.00000000 5 1.5574153941 1.247964500 1.0000000000 .2479645002 6 1.0000000001 1.000000000 1.0000000000 .0000000001 As you can see, GoalSeeker used a negative number for the third try, which caused an error in the GOAL cell. (Square Root of any negative number is an error). When an "ERROR in Calculation" occurs, GoalSeeker must "guess" at a value to clear the error. In the example above, the first "guess" was zero. 16 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software If the goal had not been found, this review may have given you information to set parameters, such that the solution is always positive. PROBLEMS AND SOLUTIONS The following is a list of situations which can arise while using GoalSeeker and possible corrections to problems. VARYING MORE THAN ONE ITEM GoalSeeker will vary only one data item at a time to find a single goal. However, you may want to see the result goal seeking would have on many input variables at the same time. One way is to average or "weigh" the input numbers. For example, assume you have 12 monthly sales numbers and you want to set a goal on the yearly profit number. To find the solution for all twelve months, we could average. Example: If the 12 sales numbers are input data values, change each to a formula cell. Set each input monthly sales number equal to a formula (1: +G4/12, month 2: +G4/12, etc.) and vary G4 (the SOLUTION cell). Now we can see the average for each month. MULTIPLE SOLUTIONS Under some conditions, there may be more than one solution to achieve the desired goal value. The best example is a squared equation (i.e. A1^2). If you are looking for a goal of 1, then there are two answers: 1 and -1, because a minus one times a minus one is still a plus one. To solve this problem, you can: 1. Use a starting solution value close to what you believe is going to be the final solution. Example, a negative starting value in the above example would produce a -1 answer. 2. Limit the bounds of the solution using the Parameters option. In the above example, set the lower bounds of the solution to 0 to obtain a positive solution of 1. It is possible to have three or more solutions to a desired goal. If the solution GoalSeeker found is not the solution you desire, set both the lower and upper bounds of the solution to the range you desire. 17 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software SOLUTION NOT POSSIBLE It is possible to request a goal value which simply cannot be reached. The best example is again the squared equation A1^2. If a goal value of -1 is requested, no solution will be found. DISCONTINUOUS FUNCTIONS A discontinuous function is one where an IF function (@IF in Lotus) has changed the normal result of an equation. GoalSeeker can solve this type of problem, however, many iterations may be required. Example: EXPENSES = SALES * 9% IF EXPENSES < 500 THEN EXPENSES = SALES * 3% When EXPENSES changes from 499 to 500, a different equation is used. Since the equation changed at 500, this would be a discontinuous function. SOLUTION CELL PROTECTED If the solution cell is protected, a "Cell Protected" message will be printed. You must unprotect the solution cell for GoalSeeker to operate. CANNOT LOAD WORKSHEET GoalSeeker requires approximately 40k bytes of memory, which is taken before you load your worksheet. If the memory size of your worksheet is close to the memory size of your computer, you may see a "memory full" error message produced by your spreadsheet when you load your worksheet. For GoalSeeker to operate on the worksheet, you will either have to reduce the size of the spreadsheet or add more memory to your computer. Memory can be added with an EMS board, for example. 18 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software SOLUTION NOT BOUNDED GoalSeeker will have a difficult time finding the correct answer to an unbound solution. This is a solution where the answer does not converge or the goal seems random. REMOVE PROGRAM The REMOVE program is used to remove GoalSeeker from memory. GoalSeeker is a memory resident program which takes about 40k bytes of overhead to operate. You may need to free that amount of memory for other applications. IMPORTANT GoalSeeker must be the last memory resident program loaded to remove it from memory. At the DOS prompt, type "REMOVE" and then press the <Enter> key. You should see the message "Unloading the product: GoalSeeker". This indicates that GoalSeeker has been removed from memory and the memory that it previously occupied has been freed. If you run REMOVE and the message "Nothing found to unload" appears, then GoalSeeker has already been removed from memory. If you see the message "Another program intervenes", this indicates that GoalSeeker was not the last memory resident program loaded. Another memory resident program has been loaded after GoalSeeker. To remove GoalSeeker from memory, you must first remove the intervening program with that program's own remove procedure. NOTE: Some memory resident utilities cannot be removed from memory, such as the DOS commands MODE and PRINT. If you have loaded either of these memory resident program after GoalSeeker, the only way to remove GoalSeeker from memory is to re-boot your system. GOALSEEKER MESSAGES The following GoalSeeker messages are in alphabetical order. An explanation of each message follows. "A GOAL VALUE MUST FIRST BE DEFINED" You must define a goal value before you begin the goal seeking operation. Move to the GOAL cell and press <Alt><Space> and then press "G" key. "ALREADY LOADED" 19 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software This message will be displayed if you attempt to load GoalSeeker when it is already in memory. All of your parameters remain in effect. "ERROR - THE SOLUTION CELL IS NOT A FUNCTION OF THE GOAL CELL (XX)" OR "ERROR - THE GOAL VALUE DID NOT CHANGE WHEN THE SOLUTION WAS MODIFIED" The goal value did not change when the starting solution value was multiplied by the starting percentage. See the Parameters Option. Check your equations to be certain that the SOLUTION cell is used in calculating the GOAL cell. If it is, then be certain that changing the solution value by the starting percentage will modify the goal value. The change in this goal value is required for GoalSeeker to operate. "ITERATIONS MUST BE BETWEEN 1 AND 25" Within the Parameters Option, the maximum number of iterations is 25. "MAXIMUM NUMBER OF ITERATIONS REACHED" GoalSeeker has reached the number of tries specified in the Parameters Option and has stopped processing without finding the desired goal. Some of your options are: 1. Try GoalSeeker again with the new starting value. 2. Increase the percent error in goal parameter. 3. Limit the bounds of the solution. "NO MEMORY AVAILABLE FOR GOALSEEKER - NOT INSTALLED" GoalSeeker was not installed on your computer when you typed GS at the DOS prompt. GoalSeeker requires only 40k bytes of memory, but not enough memory was available to install it. Check your memory chips for errors or remove other memory resident programs from memory. "PRESS R KEY TO RESTORE ORIGINAL VALUE" If the R key is pressed when this message appears, GoalSeeker will automatically restore the beginning solution value to the spreadsheet. This would be the value before the goal seeking operation started. "THE GOAL CELL MUST BE A CALCULATED (FORMULA) CELL" When you are defining a target goal, the GOAL cell must contain a formula. Refer to your spreadsheet users manual for the definition of a formula. Example: +A1 is a calculated cell. 20 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software "USE DOS 2.0 OR ABOVE" GoalSeeker will only work with DOS version 2.0 or higher. You can use the DOS command "VER" to find out what version of DOS you are currently running. For more information on "VER", refer to your DOS manual. "YOU MUST BE WITHIN YOUR SPREADSHEET TO ACTIVATE GOALSEEKER" If you press the <ALT><Space> to start GoalSeeker you, are required to be in your spreadsheet. If you are in your spreadsheet program and get this message, check the section of this manual labeled "INSTALLING GOALSEEKER" to be certain that the correct version of GoalSeeker is installed to match your spreadsheet. 21 GoalSeeker Users' Manual Copyright 1987 Brown Bag Software SITE LICENSES AND BUNDLING ARRANGEMENTS --------------------------------------- Corporate site licenses and bundling arrangements are available direct from Brown Bag Software. Site licenses can be used to distribute large quantities of GoalSeeker and its documentation within a specific organization at very reasonable prices (hundreds of dollars less per copy than competing products). All users under a site license can become registered users at no extra fee and are automatically notified of updates. Bundling arrangements can be used to distribute copies of GoalSeeker in order to promote some other product or service. Note that neither of these activities is permitted under the limited license granted to all users of GoalSeeker without the expressed written consent of Brown Bag Software. 22 REGISTRATION FORM ----------------- If you purchased GoalSeeker directly from Brown Bag Software in your own name, then your copy is already registered and you will recieve all the benefits of registration. You do not need to send in a registration form. If you received GoalSeeker some other way, you may register your copy by filling out the following form and mailing it to the listed address along with your check. When you register for a fee of $20 + $5 shipping, you will receive: The latest version of GoalSeeker. The latest Users' Manual. Free Technical Support for 1 year. 10% Commission on registrations from your copy. You CANNOT give copies away or sell GoalSeeker in connection with any commercial product, service or venture without our written permission. Mail checks to: Brown Bag Software, File #41719, P.O. Box 60000, San Francisco, Ca 94160-1719. California residents: Please add 7% tax. Or call 408-559-4545, 800-523-0764 (800-323-5335 in CA). MC/Visa/Amex Accepted In addition, you will be supporting software distributed under the shareware concept and will be contributing to the further development of GoalSeeker and other shareware products. Mail To: Brown Bag Software File #41719, P.O. Box 60000 San Francisco, CA 94160-1719. NAME _________________________________________ COMPANY ______________________________________ ADDRESS ______________________________________ CITY/STATE ___________________________________ ZIP __________________ Serial # on the Opening Screen_________________ How did you first learn about GoalSeeker or where did you first obtain a copy of GoalSeeker? _________________________________________________ _________________________________________________ _________________________________________________ 23