Analysis Functions in Excel 2010

Enable Analysis Tools

The analysis tools in Excel need to be switched on before use. To enable the Data Analysis tools:

  1. Select Options from the File tab
  2. Choose Add-ins
  3. At the bottom of the box, select Excel Add-ins and click Go
  4. Ensure both the Analysis ToolPak and Solver Add-in are ticked and click OK

Performing “What If” Analysis Using Goal Seek

Goal Seek is a function that determines the variable needed to derive an answer. It is used in conjunction with any Excel function that uses a number from another cell on the sheet.

To use Goal Seek:

  1. Create the spreadsheet and formulas required e.g. as below where B6 contains the formula =B3*B4
  2. Select the cell with the formula in e.g.B6
  3. From the Data Tools group on the Data tab select What-If Analysis and then Goal Seek

  4. Enter the target figure in the To value box and then enter the reference of the cell that needs to change in order to achieve this result in By changing cell box. Click OK
  5. The suggested results will be displayed. Click OK to accept or Cancel to revert to original values

Working With Scenarios

Scenarios allow you to save different versions of a spreadsheet to compare variations of the information.

To use scenarios:

  1. Set up your spreadsheet with one scenario, include all formulas
  2. This set of data needs setting it up as the first scenario. On the Data tab, in the Analysis group, select What If Analysis and then Scenario Manager
  3. The scenario manager dialog box opens. Click Add button
  4. In the Scenario Name box enter a name for the scenario – e.g. Current
  5. Click in the Changing Cells box and then select the cells which control the changing scenario. Click OK
  6. The current values are shown in the box. As you are saving the current scenario, you do not need to change these. Click OK
  7. You are now returned to the scenario manager dialog box and can now add the alternative scenarios. Click Add button
  8. In the Scenario Name box enter a name for the scenario – e.g. Optimistic
  9. Click in the Changing Cells box and then select the cells which control the changing scenario. Click OK
  10. The current values are shown in the box. Enter the values you want to use for this scenario. Click OK
  11. You are now returned to the scenario manager dialog box. Repeat steps 7 to 10 until you have recorded all desired scenarios.

To display a scenario:

  1. On the Data tab, in the Analysis group, select What If Analysis and then Scenario Manager
  2. The scenario manager dialog box opens. Select the scenario to show and click Show button

To compare the scenarios:

  1. On the Data tab, in the Analysis group, select What If Analysis and then Scenario Manager
  2. The scenario manager dialog box opens. Click Summary button and choose Scenario Summary
  3. Choose the cells you want to see the values of on the summary sheet in the Results Cells box
  4. A new sheet is produced displaying the changing values and results cells

NOTE: If you name the changing and results cells, the names will be used in the summary making it much more user friendly. More details on named cells and ranges can be found in the Advanced Functions reference guide.

Using Solver

With Solver you can find the maximum or minimum value for a formula in one cell, called the objective cell, subject to constraints or restrictions on the values of other formula cells on a sheet. Solver works with a group of cells, called decision variables or variable cells, which are used by the formulas in the objective and constraint cells. Solver adjusts the values in the variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

For example, when deciding where to invest a lump sum, there are likely to be limits on some options in terms of the amount you can pay in whilst others may have high fees. Solver can help you work out what is the best combination of accounts to use based on the constraints.

To use Solver:

  1. Set up the spreadsheet with all the formulas and some values. The values do not have to meet the required constraints.
  2. On the Data tab, in the Analysis group, select Solver. The Solver dialog box appears.
  3. Set the objective cell in the Set Objective box and choose if you wish that cell to be set to the highest possible value (Max), lowest possible value (Min) or a fixed value using Value of.
  4. Enter the cells that can be changed in the By changing variable cells box. These can be a single range (e.g. B2:C4) or a series of ranges and single cells all separated by a comma (e.g. C2,D3,F4:G5).
  5. Enter the constraints, by clicking the Add button and then setting a mathematical constraint on a variable or calculated cell (e.g. C2 <= 2000 indicates that cell C2 cannot exceed 2000). Click Add to add in another constraint or click Done if you do not need to add any more.
  6. Click Solve and a solution will be sought. When Excel has found a possible solution it will show the Solver Results dialog box and display the results on the spreadsheet. The Solver Results dialog box also details if any of the constraints have not been fully met. Where a result cannot be obtained no results will be presented, just an error dialog informing you that results are not possible.
  1. You can now choose to keep the solution or restore the original values. In addition you can also create a scenario for the solution should you wish to explore other scenarios.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s