A Brief Glimpse At Excel's What If Analyses Tools

Mon 24th January 2011

Find out how where to find and how to use theses "What If" Excel business tools Scenario, Goal Seek and Solver for any Excel version

If you're interested in using Excel to help make business decisions then you may find the built in "What If" analyses tools offer you some interesting possibilities. There are either two or three tools available by default depending on which Excel version you are using and you can add more via Excel's Add Ins feature. This article summarises how to use two of the basic tools; Scenario Manager and Goal Seek and one of the additional tools, Solver.

Where to find the What If analyses tools

In Excel 2003 just choose the Tools menu, expand to a full menu, and you'll see Goal Seek and Scenarios already available. To add further tools, choose Tools, Add Ins and tick one or more selection. To add Solver tick its checkbox and click OK to finish. Now if you choose the Tools menu you'll see Solver is also available.

In Excel 2007/2010 choose the Data tab and in the Data Tools group click the What-If Analysis button. You'll see the three tools Scenario Manager, Goal Seek and Data Table available. To add more tools, in Excel 2007 choose the Office button or in Excel 2010 select the File tab. Then choose Options, Add-Ins and click on the Go button at the bottom of the screen. In the Add-Ins panel you can see another four tools available. Tick the Solver Add-in checkbox and click OK to finish. Now you'll find Solver is available in the Data tab, in a new group.

Scenario Manager

Scenario Manager lets you save one or more groups of cell values under a particular name, within the Excel file. So, for example, if you are preparing some forecasts you could create three different sets of figures, with the first set your optimistic plan, the second your normal plan and the third your pessimistic plan. You could certainly keep these different sets of figures in different parts of your Excel file anyway, but Excel lets you easily swap and change the sets of values back into the original cells. This is why Scenario is popular because of the ease in data swapping.

You could create lots of different scenarios for different aspects of a spreadsheet and then permutate applying them, or create more complex scenarios built from your initial ones. Scenarios can be imported and exported, so users of similar layout spreadsheets can apply scenarios someone else has created.

Goal Seek

Goal Seek is a deceptively simple tool used in determining how to achieve a certain value in a cell containing a formula, by varying just one data cell used in the formula. You are in effect specifying the result you want for the formula and then working backwards to vary the values in one of the underlying data cells to achieve this result.

To use Goal Seek you first select a cell containing a formula. Then choose Goal Seek. In the Goal Seek panel the selected cell is shown as the "Set Cell". Then in the "To Value" box type in the required value you want for the selected cell. Then in the "By Changing Cell" box choose the cell you to change the value in, until the value in the formula reaches your chosen value. It's easy to use and you can set lots of different target values for a formula and work backwards to determine how to achieve each.

Solver

Solver is a more specialized tools and hence not available until you use the Add-In. It's a utility which lets you optimize numerical formula subject to constraints. So for example if you build several products in a small workshop you might use Solver to determine the optimum number of PCs to build in a week to give you maximum profit. You know your product prices, the costs of materials and overheads, and you can hire extra skilled staff by the hour. You also know how many hours it takes to build and test each product.

However your operation is constrained by several factors. Examples might be that your workshop can only hold a certain number of particular products, you can only buy limited parts to build one of the product, and you can only hire extra skilled staff for four hours per day.

To use Solver you would first create your spreadsheet to calculate income, expenditure and profit based on the number of different products to be produced, but you leave the numbers to be produced as zero. Next select the cell with the profit formula and launch Solver. In the Solver panel you would choose to maximise the profit. You then tell Solver which cells contain the product numbers which currently show zero.

Solver will apply different values to these cells to maximise the profit value. Then lastly you can add one or more constraints, such as the maximum number of PCs your workshop can hold, and the maximum hired staff hours available. Once added, click the Solve button, and Solver will go to work. Either Solver will find a solution and offer the values to go in the cells to be changed, or Solver will advise a solution cannot be found. You can choose to accept or reject the solution, and you can also save the solution as a scenario. So if you are working with a formula with several variables, subject to one or more constraints, then Solver could be a useful way forward.

In this article we have looked at three "What-If" analyses tools available in all versions of Excel which let you change values of cells in different ways. Scenarios let you keep different sets of data all used in particular cell locations, Goal Seek lets you work backwards from a formula to find a cell value to produce a desired value in the formula, and Solver lets you optimize a formula with data subject to several one or more constraints.

You might like to learn more about Excel's many features by attending an instructor led training course, and really boost your Excel skills.

Author is a freelance copywriter. For more information on excel course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1412-a-brief-glimpse-at-excels-what-if-analyses-tools.html