Using Spreadsheets to Solve Problems
1.6 What-If Analyses and Goal Seek
You now have the ability to create fairly extensive worksheets using relative and absolute cell referencing and a host of arithmetic functions. As previously discussed, the greatest benefit of using a spreadsheet tool and cell referencing is the ability to easily incorporate changes and instantly know the affects of those changes. This section will look at techniques that lets the user not only know how changes will affect the final values, but allow the user to determine what input will be need to reach a specified final value.
The What-if anlyalsis
Consider the worksheet in Figure 1. This worksheet includes a list of data inputs (cells A3:B12), calculation section A14:A18 and a final value in cell B19. The worksheet calculated the Total Wages per year from a specific job based on a list of expected hours worked, including hours at regular pay and hours at 1.5x and 2x regular pay.
Each of the formulas in cells B15 through B19 contain cell references to the data inputs above. By constructing the worksheet in this format, the user can easily performing What-If analyses by simply substituting one or more of the given input values.
For example, if the dollar per hour pay rate were to increase to $9.50 per hour, substituting this new value in cell B5 would be all that is necessary to arrive at a new Total Wages per year. All of the intermediate calculations in cells B15:B18 would be automatically updated as needed. The user may substitute one or more data inputs as desired to perform a variety of What-If analyses. This is especially important in business when trying to analyze the affects of changes on the viability of a project or the prosperity of a business.
The Goal Seek Tool
In addition to being able to perform “what-if” scenarios on spreadsheets, many spreadsheet applications provide tools that allow you to work backwards to determine what input value is required for a specific output value. In Microsoft Excel this tool is called Goal Seek and can be accessed by clicking the What-If button in the Data Tools group on the Data Ribbon.
Using the worksheet in Figure 1 consider the question, “To earn $25,000 in wages from the same number/type of hours, what base payrate in $/hour would be required?” This question can be answered using the Goal Seek tool.
Before going through the detailed steps of using the Goal Seek tool, it is important to know its limitations:
· It is only possible to vary values in cells that do not already contain formulas. So cell B16, OT wages per week, cannot be varied since it already contains the formula =B$4*B6*B11.
· Only one value can be varied per analysis. For example, the number of hours and the payrate cannot be varied simultaneously to arrive at a specific outcome for total wages per year.
The mechanical steps to execute this Goal Seek are as follows:
1. On the Data Ribbon in the Data Tools group, click on the What-If button and select Goal Seek. This will display the Goal Seek dialog box as seen in Figure 2. Each box must now be filled in with the proper data.
In the question, “To earn $25,000 in wages from the same number/type of hours, what base payrate in $/hour would be required?”, the total wages is the value being set to $25,ooo. This calculation resides in cell B19 which currently contains $22,275. Cell B4 contains the $/hour, the value being varied to arrive at this $25,00 total wage.
2. In the Set cell box place the cell reference of the value being set. In this case, the cell B19. This can be done by clicking directly on the cell or typing the reference into the text box. Note that by default this reference is made absolute.
3. In the To value box type the new value being set in the set cell box. In this case, the value $25,000 (no comma’s please!).
4. In the By changing cell box place the cell reference of the value being varied: the $/hour in cell B4 in this case. Again this reference can be selected by clicking on the cell or typing and defaults to an absolute reference.
5. Once all of the inputs are specified, click OK. This will bring up the Goal Seek Status dialog box as seen in Figure 3. Excel uses trial and error to find the solution, varing the cell to be changed in increments. If the value has been found then the Target Value and Current Value of the set cell will be the same. If not, you can actually modify the starting “change” value used and the increments (other options will be displayed). In this example an answer has been found. Note that the “changed” value is not displayed in the dialog box, but can be viewed in the worksheet (Hint: at this point you can’t change worksheets so start the goal seek on the worksheet page containing the “change” value and it will be easy to view at this point).
6. To accept the changes, click OK. To return to the original values, click Cancel.
Once the changes are accepted the old values are deleted. One trick to preserve these old values is to copy the worksheet and run the goal seek on the new worksheet. When working with a complex workbook with calculations/variables on multiple worksheets, copy the workbook and perform any what-if analyses and/or goal seeks in the copied workbook. In this manner the original values will remain intact.
Excel does provide some more advanced features for these types of analyses – Scenario Manager for doing What-If analysises and Solver for performing multivariable “goal-seeks”. These tools are not within the scope of this course.
Exercise 1.6-1 Chapter Review – What-If/Goal seek
- In the spreadsheet above, you would like to calculate a new total profit based on updated stock quotes for ATT and Merck. The new prices are $43 and $92 respectively. What do you need to do to execute this modification?
- In the spreadsheet above, how would you answer the following question: What would the current price of the Microsoft stock have to be for your entire portfolio to be worth $50,000?
Goal Seek 1.6 CSE1111 Page 79