Analysis tools

Introduction

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 A1 cell 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 analyse 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. To understand how scenarios work, let us take the following simple example. Enter the numbers and formula in the appropriate cells. From the Tools menu, select the Scenarios option. t The following screen will appear. It's in this window that you will create the two scenarios named "optimist" and the other "pessimist". Don't worry if you make a mistake. You can always press the Edit button later to change the scenarios. Press the Add button. In the Scenario name box, enter the name of the scenario: Optimist. In the Changing cells box, enter the cell addresses that you want to change in your model. For this example, select the A1 and A2 cells. You can write the addresses or by using the left mouse button and pressing the Ctrl 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. For example, you could write: "here is a very optimistic scenario for the company", or "Here is the Mario's evaluation" etc. When you entered the data, press the OK button. Excel will ask what are values for the cells for this scenario. For the A1 cell, enter 1000000. For the A2 cell, enter 2000000. Press the OK button. The data for the first scenario are entered. It's time to add the second scenario. Press the Add button. Once again, enter the name of the scenario, the cell addresses of the changing cells and, if need be, a comment. For A1 and A2 cells, enter the value 0. Because there are no more scenarios to be added, press the OK button. It remains only to see what will be the the result in the A3 cell. If you made an error in onr of the scenarios, now is the time to change it. Click the name of the scenario and press the button change. Press the Summary button. 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. For more information about the pivot table, click this link. Select the option Scenario summary. 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 key Ctrl, Click on the cells of your choice. For this example, select only the A3 cell and press the OK button. 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. Delete the summary sheet If you want to erase the sheet to re-try. 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 fade. Give names to cells The name of the cells will appears to the left of the synthesis (A$1 , A$2 , A$3). But what represent exactly the contents of these cells? Would it not be more interesting tthat the name of the cell represent it's contant. This is another advantage to give names to the important cells. Select the worksheet with numbers and formula. Place the cursor in the A1 cell. From the Insert menu, select the options Name and Define. In the first box, enter the following name: Product_1. To have an underline (_), press the Shift and minus (-) keys. Excel doesn't allow spaces for a cell name. A way of by-passing this limitation is to underline between the words. Ex: August_revenus, Annual_Profit. Press the OK button. For this exercise, it is necessary to give a name to the A2 and A3 cells. Place the cursor in the cell A2. From the Insert menu, select the options Name and Define. Enter the name Product_2 and press the OK button. There is another way to give a name to a cell. Place the cursor in the cell you wish to give a name. Go directly to the name box and enter the name of you're choice. Place the cursor in the A3 cell. From the Insert menu, select the options Name and Define. Enter the name Profits and press the OK button. It's now time to regenerate a new analysis sheet with cell names. From the menu Tools, select the option Administrator of scenarios. Press the button Scenario summary. Make sure to choose the scenario summary report type and indicate to Excel that the results is to be showed in the cell A3. Press the OK button. It's much easier to understand the results when you have a name that represents the subject. 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. Press the Cancel button. OR From the Edit menu, select the Cancel View option. Pay attention not to put in the Changing cell area the cells that contains a formula. Otherwise, the scenario manager will automatically replace the formula by the values in the scenario. Here is an example. If it's not already made, enter numbers and following formula. For the exercise, it's necessary to create a scenario with the variable cell that is contain in A3. From the Tools menu, select the Scenarios option. Press the Add button. For the scenario, enter for name "Test" and the A3 cell for the variable cells. Press the OK button. Excel will warn you that one of the cells that you have selected, the cell A3 for the exercise, contains a formula. It is warning you that it will convert the formula into a value. It will do this when showing the scenario and/or also during the analysis of the scenario. It is still possible for you to avoid the conversion by changing your scenario not to include the cell. Press the OK button. Change the value to 500. Press the OK button. The value has really no importance for the example. Press the Scenario summary button. Select for the resultant cell the A3 cell and select the report Scenario summary. Press the OK button. Excel shows you the analysis of the scenario. Return to the worksheet having numbers and formula. Place the cursor in the A3 cell. 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. There is another exercise on the scenarios on the page for creating models of this site. If you require more examples. 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. When you selected the good file and the good worksheet, press the OK button. 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 The solver The solver is used for finding the optimal solution of your model according to the conditions that you determine. It can also h find how to maximize your profits, to minimize your losses or determine the best way to achieve certain numbers. The model is very simple. It's necessary to discover what numbers are to put in cells A1 and A2 to maximize the result of the A3 cell. The first action is to activate the solver. From the Tools menu, select the Solver option. If you don't see the option in Excel, select Tools, Add-Ins. From the list, choose Solver Add-in. The option is now going to appear in the menu tools. If the option is not shown, install it from the Office CD. The installation of Excel is not complete. In the first box of the window, the solver asks you what is the cell to be optimize. For the example, it is the A3 cell. The solver will ask if he should minimize, maximize or optimize the value of the cell. Later, the solver will ask what are the cells that he can change in order to optimize the target cell. So that the solver works correctly, it's necessary to determine the constraints that it must respect. You can add up tol 100 constraints of your choice. For this example, it's necessary to add 4 constraints that determine the maximum and the minimum of cells A1 and A2. The minimum and maximum values that A1 cell and A2 should respect are registered in columns C and D. Why not simply enter these values directly in the constraint? It's much easier to change the contents of a cell than to try to find a constraint among a long list to change it to. It is preferable to put the values of the constraints in the cells that contain the constraint. Press the Add button. Click on the first box of the window Add a constraint. From the box, click on the A1 cell or write A1. Click on the small arrow pointing downward and select the option < =. Click on the third box of the window Add a constraint. From the box, click on the D1 cell or write D1. Press the OK button. You have just added your first constraint. It indicates to the solver that the A1 cell should be lower or equal to the contents of the cell D1. You have just determined the maximum that the A1 cell can reach. The next constraint serves for determining the minimum that the A1 cell can reach. Both others serve for the maximum and the minimum of the cell A2. Add the three other constraints shown below by pressing on the Add button at the bottom of the window or that of the window of the solver. Press the OK button Here is the list of the constraints that you have just added. All the data that the solver needs to resolve the problem have been entered. They are: The address of the cell to be defined The addresses of the variable cells The constraints to be respected. There is no last point to be verified. Press the Options button. Here are some possible options of the solver. 3000 increase the number of iterations in about. The iterations are the trial number that the solver will carry out in order to find the optimal solution. If there are not enough iteration, the solver will not find the optimal solution. It's going to show you the best solution with the number of available iterations. Press the OK button. It remains only to ask the solver to find the optimal solution. Press the Solve button. Here is the result. Excel determined that the possible maximum for the A1 cell is 1 million and that that of the cell A2 is 2 millions. So, for a total of 3 millions in the A3 cell. This was a very simple example. But you can now use the concepts that you have just seen to leave the solver to determine the optimal solution for much more complex models. Note: It happens that Exel will find e several possible optimal solutions. The solver is only going to show you the first solution that it as found.