Describe how to use Excel data tables Scenario Manager and g

Describe how to use Excel data tables, Scenario Manager, and goal seek tools to analyze decision models.(I need this is straight paragraph form and 500 words or less. Actual written statements and not just a photo copy of a book.)

Solution

You created your own decision-making model and now you want Excel to help you find the best solution for your situation. The software possesses several analysis tools that can help you. This page explains three of these tools: target value, the scenario manager and the solver. Each answers your needs in different situations.

Goal Seek

This option will help you find the missing value in a cell to attain your goal. In the following example, what should be the value in the A1 cell so that the A3 cell shows 500? You already know what the result should be but not what the value in a cell should be. Instead of trying to find the answer by trial and error, Excel can find you find the answer by using the Goal Seek.

Enter the following values and formula in the appropriate cells.

From the Tools menu, select the Goal Seek option.

Excel needs three things to find the targeted value.

Which cell has the result that you want? That cell should always have a formula.

Write in the Set cell box A3.
OR
Select the A3 cell.

What is the value you wish to reach for this cell?

Write in the To value box 500.

Which cell can Excel change the value in order to get the desired result? That cell should only contain a number or a value; no formulas. Excel will refuse to proceed otherwise.

Write in the third box A1.
OR
Select the A1 cell.

Press the OK button.

Excel will attempt to reach the goal value that you put in. It may not reach it. It depends on your model and the conditions that makes it work. For this example, Excel found the following result.

Goal seek was able to change the content of the A1 cell so that the A3 cell was able to reach the goal value of 500. The A1cell must have a value of 300. There are also boxes where Excel will not find the results you requested. That\'s because there is no direct or indirect link between the goal cell and the cell you determined Excel could change.

There are limits to Goal seek. Only a single cell, can be changed and the goal value must be known. It\'s impossible to minimize, maximize or to optimize the result in your model with this option. You\'ll need to look at the Solver tool to optimize your model.

The data table ( 1 or 2 variables)

Excel offers you several tools to analyze the results of your model. The Data table tool allows you to create a table that shows the result of certain cells if you change the contents of one or two cells. Instead of trying many \"what if\" situations with several values, the results will appear quickly in the form of a table. The next exercises consist in creating data tables with one or two variables with a small mortgage payment model. Before creating a data table, you must first have a complete model. For this case, the model calculates what your mortgage payments will be.

The formula in the B6 cell calculates the payments according to the interest rate, the number of payments per year and the total value to be paid. The formula in the B7 cell indicates the total sum, the capital and the interest, that you should pay before being the \"true\" owner of the house. It\'s probably a little more that you imagine.

Enter the text, the following numbers and the formula in the appropriate cells.
For the B6 cell, you can enter the function by using the button .
OR
From the Insert menu, select the option Function.

From the Finances section, select the PMT function.

Enter the data in the appropriate boxes.

Data table with a single variable

Before being able to use a data table, you should have a model that works correctly. Otherwise, the results of the table are worthless.

Enter the following numbers and formulas in the appropriate cells.

To create a data table, two data are always required. There are the cells that will show the results and the cell that contains the values that you want to change with regard to the current values. These data are always placed on the first row and the first column of the table. It\'s also important not to mix the values and the cells. The cells you want to see the results are placed horizontally and the values you wish to change are placed vertically.

According to the data of the example you just entered, the values you want to change are on the first column and the cells you wish to see the result on are on the first row. The data table will show the effect on payments and total in the cells that are on the first row and according to the changes in the interest rate from the first column. Because you want to know what happens to your payment and the grand total when the interest rate changes. You also need to know in what cell the interest rate are stored. In this exercise, it\'s the B2 cell.

You can now ask Excel to fill-in the data table and to replace the contents of the first row, the first column or both. Because it\'s about a data table with a single variable, only the following two boxes will have a cell address.

Make a block with the cells A9 to C15.
From the menu Data, select the option Table.

Click in the box Column input cell.
Click on the cell where the value will be replaced by each of the values of the first column of the data table. For this exercise, select the B2 cell.
Press the OK button.

Excel calculated what would be the payment and the grand total if the interest rate changed from 6.5 % to 9 %.

From the B2 cell, change the interest rate from 7 % to 10 %.

The model changes immediately to show the new result. However you will have to redo the data table if you want to add other interest rates or the other cells to be compared.

Data table with two variables

The last exercise created a data table with one variable based on the interest rate. You were able to see what payment will be required when the interest rate is changed. The next exercise consists in developing a new data table with two variables. The next table will show what happens on the grand total when you increase the number of payments per year according to the interest rate.

Enter the numbers and formula the appropriate cells.

The first row represents the number of payments per year. You find this value in the B4 cell of the previous model. As for the table in a variable, the first column demonstrates the various interest rates. The value is in the B2 cell.

At the intersection of the first row and the first column, you will put the address of the cell that will contain the results. For this exercise, it is the total of the payments that will be seen in the cell B6. The structure of a table with two variables allows you to look at what happens in a cell at the same moment.

Select the block of cell from A19 to D25.
From the Data menu, select the Table option.

Enter the addresses of cells the appropriate boxes.
Press the OK button.

As the table indicates, there is a small decline if you pay more often during the year. The capital is paid off faster and therefore you pay less interest and a smaller total value. What is more important is the change in the total as the interest rate rises. These are important sums of money that can be saved over time.

Although it\'s very interesting to know what happens at certain cells thanks to the data tables, this technique has also some disadvantages. By using a data table with two variables, you can only see the result of a single cell at a given time. In this last box, you can always change the cell address and print or copy the results. For example, change the contents of the A19 cell to =B5. You can see what happens to payments but you lost the previous results. To resolve this problem, you must create another table.

Another more important limitation is that you can only change a maximum of two variables. The scenario manager opens the other possibilities to compare situations.

Scenarios

What can you do if you want to compare several possibilities? You created a model of a company or a system. You are offered several possibilities to make it work even better. But which one is best? You can enter these various possibilities, or \"scenarios\", in Excel to find that answer. Once the scenarios are entered, you can view the result of important cells such as the raw profit, income, costs, ratios... In brief, any cell that you consider important.

But, before you start using scenarios, you should have a working model of what you want to analyze or compare. Make sure to validate your model with several tests. Change the values of the variable cells and verify the results. Very often, you forgot something in a model; whether it\'s a number or a formula. It\'s for that reason that you must check the model completely. If the model gives bad results, the scenarios will too.

One of the limitations of the scenarios is that all the variable or input cells and all the result cells must be on the same worksheet. Some of your calculations can be on other worksheets, but not the variable cells or the cells you wish to see the results.

The objective of this exercise is to know the result of the A3 cell according to an optimistic or pessimistic scenario. So, two scenarios must be created that will be named \"optimist\" and \"pessimist\". Later, you can create your own scenarios to answer your needs. You \'re not limited to theses names or to only two scenarios. You can have several scenarios to compare the Peter\'s theories to those of Paul\'s and Mary\'s. You can also merge these scenarios if you wish.
You can write the addresses or by using the left mouse button and pressing theCTRL key, click on the cells you require. The variable cells are those that you want to change the value. They will not change the values of the worksheet. These cells will be only used for the scenario.
In your choice, you can also write a comment. This is to give you more details of the scenario.
Excel will ask what are values for the cells for this scenario.
Excel offers you two kinds of reports: Scenario summary and Scenario Pivot table report.
The summary of scenarios generates a new worksheet with the list of the variable cells and the cells that contain the results you want to see. Once the summary as been generated, it will be impossible to change the model or the scenarios. You will have to regenerate a new summary with new results. You will then be able to compare the various analysis according to your changes.
The pivot table allows you to compare the result of several variables. It also allows you to change the presentation and to carry out more detailed analyses.
Excel will ask you in what cells the result are to be in. You can choose several cells. To select them, you can write the addresses of cells or, by keeping a finger on the keyCTRL, Click on the cells of your choice.Excel will take a moment to create a new worksheet with the results of all your scenarios.
The table is in two parts. The part of the height includes the address of cells as well as its value for each of the results. The bottom part will show the results according to the scenarios. Each scenario that you have added will be presented in a different column. The grey cells at he top represent the values that you have changed in regards to the values of the worksheet.
From the Edit menu, select the Delete sheet option.
Be careful! Make sure that the sheet you want to erase is the one that is shown on the screen. In this case, you want to erase the worksheet with the results of the scenarios and not the one that contains the model! The sheet of the file will Scenarios are very interesting when you need to compare several situations. But pay attention to three things: the Show button, the period for decimals and the replacement of a formula by a number.
In the upper right corner of the window of the scenarios, the Show button allows you to show the results of a scenario on the file. It\'s interesting to see the effect of a scenario on the entire model. But you have to be very careful with this option. If you continue to work, it will be with the values of the scenario and not the original values.
To return to the initial values: Close the window of the scenarios.
Pay attention not to put in the Changing cell area the cells that contains a formula. Although the value is the same, there is no more formula. The administrator of scenarios replaced the formula by the value of moment. So, to avoid this situation, never select a cell containing a formula in the list of the variable cells. Excel warns you before making the conversion.
Fusion of scenarios
A limitation of the scenarios is that they are only available on the worksheet that they were created on. This is not advantageous if you want to use them somewhere else. The fusion button of scenarios allows you to copy all the scenarios of a worksheet in the other or even to another document of Excel. It can be practical when several person are working on the same model but develop different scenarios. This command allows you to group together several scenarios and compare them. In fact, one should better call them group scenarios.
If you want to copy scenarios from one Excel document to another, it\'s necessary first to open both documents. To pass from a document to another, use the Window command follow-up of the name of the document. Select the document that will be receiving the scenarios. Select the worksheet that is to receive the scenarios. From the Tools menu, select the Scenarios option.

For this exercise, there are already the Optimistic and pessimistic scenarios. We will suppose that there is another scenario in another document of Excel.

Press the Merge button.

The box file will show the name of all the documents Excel, or file according to the term of Excel, opened at this moment. Because there are only two opened documents, Excel presumes that the popular scenarios are in the other document. You can change it to the document of your choice.

The box sheet shows the name of the worksheets that exists in the file. In that box, there is only one worksheet: Evolution. At the foot of the window, it\'s registered to the number of scenarios included in the sheet.

The scenario that was on the Evolution worksheet was copied or \"imported\". If this last sheet would have had several scenarios, all would have been copied. If two scenarios would have had the same name Excel will automatically change the name of the imported scenario