You created your own decision-making model and now want Excel help to
you find the best solution for your situation. The software possesses
several analysis tools to 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 this next 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 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 it's value 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 make 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 cases where
Excel will not find a result. 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 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 the appropriate cells.
To create a data table, two data are always required. There
are the cells of that you want to see the results and the values that you
want to change with regard to the current values. These data are always placed
on the first line 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 that you entered of the previous picture, the values to
you wish to change are on the first column and cells you wish to see the result is on the first
line. The data table will show the effect on payments and total, cells
of the first line, according to the changes in the interest rate from the first
column. Because you want to know what happens to your
payment and on the grand total when the interest rate changes, you must also know in that cell the interest rate is stored. In this exercise, it's
the B2 cell.
You can now ask Excel to fill-in the data table, to replace
the contents of the first line, 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 on 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 data with two variables. The next table
will show what happens on the grand total when you increase the number
of payments a year according to the interest rate.
Enter
the numbers and formula the appropriate cells.
The first line represents the number of payments a 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 line and the first column is where you put the cell
address of the result of that you want to see. For this exercise,
it's about the total of the payments that's normally in the cell B6. The
structure of a table in two variables allows to look what happens at 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 to notice is the difference of the total according as the interest
rate rises. These are important sums of money that can be saved over time. It's important to fight to always get the lowest rate possible.
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 case, 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 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 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 that's the result in the A3 cell. If you made an error
in the one or other one of the scenarios, it's now the time to change it. Click
the name of the scenario and press the button change. Later, continue
with the rest of the procedure.
Press the Summary button.
Excel offers you two kinds of reports: Scenario summary and Scenario Pivot table report.
The synthesis of scenarios generates a new worksheet with the list
of the variable cells and the cells the result of that you want to see. When
the synthesis is generated, it will not change if you bring changes to
the model or to the scenarios. It will be necessary to regenerate a new synthesis
with the new results. It will be possible to compare the various syntheses according
to your changes.
The dynamic crossed table allows you also to compare the result of several
variables. But the dynamic table 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 Synthesis of scenarios.
Excel will ask you that are the cells the result of that you
want to see. 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 parts in two parts. The part of the height includes the
address of cells as well as its value for each of the results. The part of bottom
shows the results according to the scenarios. Every scenario that you added
meets itself in a column. The cells of grey color of the part of the height
mean that you changed the value of the cell with regard to the value in the
course 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! Be sure that the sheet that you want to erase is the one that's
shown in the screen. In that case, you want to erase the worksheet
with the results of the scenarios and not the one that has
your model!
The sheet of the file will fade.
Give names to cells
You noticed maybe that the name of cells appears to the left of the synthesis
(A$1 , A$2 , A$3). But what represent exactly the contents of these cells? Would
not it be more interesting to have a representative name for the cell? 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's also 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 you desire.
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 sheet of synthesis with the names of cells.
From
the menu Tools, select the option Administrator of scenarios.
Press the button Synthesis.
Make
sure to choose a report of type Synthesis of scenario and however the resultant
cell is the A3 cell. Press the OK button.
It's much easier to understand the results when you have representative names.
Scenarios are very interesting 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 box 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 the variable cell of
that's 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 selected, the cell
A 3 for the exercise, contains a formula. It you warned that it's going
to convert the formula in a value at the time of showing the scenario
or also during the creation of the synthesis of the scenario. It's still possible
to you to avoid the conversion by changeing 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 Summary button.
Select
for the resultant cell the A3 cell and select the report Scenario
summary. Press the OK button.
Excel shows you the sheet of synthesis 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 even to you before making the conversion.
There is another exercise on the scenarios on the
page for creating models of this site. You can be practised once again
if you want it.
Fusion of scenarios
A limitation of the scenarios is that they are only available on the worksheet on that they were created. This is not advantageous if you want
to use them somewhere else. The button of fusion 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 persons work on the same
model but develop different scenarios. This command allows so to group
together several scenarios to be able to use them and compare them. In fact,
one should rather have to call it group scenarios.
If
you want to copy scenarios of a document Excel 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 in that where you want to receive the scenarios. Select
the worksheet where you want 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 document in your choice.
The box sheet shows the name of the worksheets that exists
in the file. In that case, there is only a worksheet: Evolution.
At the foot of the window, it's registered 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. In case two scenarios would have
had the same name. Excel is automatically going to change the name of the imported
scenario
The solver
The solver serves for finding the optimal solution of your model according
to the conditions that you determine it. He can so much find how to maximize
your profits that to minimize your losses or determine that's the best way
to achieve the certain number.
The model is very simple. It's necessary to discover that are numbers 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 optimize. For the example, it's about the A3 cell. Then the solver wants
to know if he should maximize, minimize or optimize in a certain value the cell
to be optimized. Later, the solver needs to know what are the cells that he
can change to try to optimize the target cell.
So that the solver works correctly, it's necessary to determine it constraints
that it's going to respect. You can add until 100 constraints of your choice.
For this example, it's necessary to add 4 constraints that determine the maximums
and the minimums 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 simply , don't enter these values directly 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 then. It's so preferable to put the values
of the constraints in cells that in the constraints themselves.
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 in 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 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 was entered. There
is
The address
of the cell to be defined The addresses
of the variable cells and 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
to try 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 with the assigned rooms for manoeuvre that there are several
possible optimal solutions. The solver is only going to show you the first
solution that it found.
Enter
the following values and formula in the appropriate cells. 
.
t
.
The address
of the cell to be defined
