Question

(Scenario Manager and Goal Seek)

1) explain what these tools do.

2) Give an example of how this might be used in your profession, future profession, or a job you want. The example must be different from the example used in the text.

Answer 1

answer:

(1). The Goal Seek scenario tool is complementary to the What If scenario tool. The What-If tool tells the impact of making a change, whereas the Goal Seek tool tells the underlying factors that must change to achieve a desired result.By using what-if analysis tools in Microsoft Excel, you can experiment with several different sets of values in one or more formulas to explore all the various results.

For example, let's say goal is to increase customer satisfaction.Use Goal Seek analysis to determine which factors would be most likely to increase customer satisfaction, and decide whether those changes are cost-effective.

Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform what-if analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel. For more advanced models, you can use the Analysis ToolPak add-in.

answer:

(2). Using the Goal Seek Scenario Tool:

1. Open an Excel table.

2. Click Scenarios, and select Goal Seek.

3. In the Scenario Analysis: Goal Seek dialog box, select the column that contains the target value from the list.

4. Specify the value that want to achieve.

   If the column goal contains continuous numeric values, it can also specify a desired increase or decrease in the value. For example, choose Sales as the column and specify that the target is an increase of 120%.

   Or, specify the goal as a range of values, by typing a lower and upper limit.

5. Specify the column that contains the values you will change. In other words, pick the column that will be manipulated to produce the desired result.

6. Optionally, click Choose columns to be used for analysis, and select columns that contain useful information. Deselect columns that will not contribute to the analysis.

7. Specify whether you want to make predictions for the entire table, or for only the selected row.

8. If selected the Entire table option, the tool adds the predictions to the source table in two new columns.

9. If selected the option On this row, the results of analysis are output to the dialog box for review. The dialog box stays open so that you can continue trying out different values and goals.

For example:

Suppose a family wanted to take out the biggest loan that they could afford to pay for. If they set aside $500 a month, the goal-seeking program would try to work out how big a loan the family could afford to take out. Even using simple trial and improvement, a computer could quickly determine that they could not afford a $50,000 loan, but could afford a $48,000 loan. It would then repeat the process until it had reached a figure such as $48,476.34, which would give them a monthly repayment as close to $500 as possible, without exceeding it.

A more efficient method, especially on more complicated calculations, would be for the program to logically work through the argument. By drawing up a simple equation, the program could come to the conclusion that the output equalled one ninety-sixth of the input, and could then multiply the output (or goal) by ninety-six to find the necessary input.