Another helpful Excel guide from Acuity Training’s Nick Williams.
What-if analysis is a useful way of being able to test out various scenarios in Excel. You can look at these things two different ways.
The first way is to change the input variables and see what impact that has on the output. The scenario manager and data tables work in this way and can be used to answer questions like, what would happen to our profits if the number of units we sell doubled, or what would happen to our profits if the cost price of each of our units sold increased by 10%.
The second way is to say what outcome you would like to have and ask Excel to calculate what change in the inputs would be required to achieve this. The goal seek feature works this way and can answer questions such as how many units of a product need to be sold in order to reach a desired profit level.
Scenario Manager
The following example looks at scenarios showing the profit from selling 100 apples, with differing levels of mark-up.
The first step is to create some scenarios. The scenario manager can be found on the Data ribbon, under What-If Analysis. Click the Add button to start creating new scenarios. The next dialog box will ask for a name for the scenario and which cells are to be changed. A scenario can contain up to 32 changing cells, although in reality, most scenarios will use far fewer than this. Once the scenario name and cells to be changed have been selected, click the OK button and fill in the values for each cell to be used in the scenario. From here, click Add to continue adding more scenarios, or OK when finished.
For this example, four scenarios have been created – for a 50%, 100%, 150% and 200% markup. There are two ways that a scenario can be applied – it can either be shown on the worksheet itself, or a summary can be created. The below table shows the results when a 50% markup is applied. This was achieved simply by selecting the 50% Markup scenario from the Scenario Manager and clicking the Show button. 50% has been entered into the Markup column, and the figure in the Profit column, which is calculated using a formula referring to this has updated accordingly.
To compare results from several scenarios simultaneously, the Summary option can be used. As with the Show option, the Summary report is accessed from the Scenario Manager dialog box. There are two options here; Scenario summary and Scenario PivotTable Report. Although the option is there to display the results as a PivotTable report, in most cases, the summary will display the results in a more user-friendly way.
The below shows the results from the scenario summary, demonstrating that the profit changes depending on the markup scenario used. Note that the Changing Cells and Result Cells are shown using their cell references. In this example, they have been left so that they can be referred against the table above, however in practice, it will make the report easier to read if the cells have been named.
Goal Seek
The scenario manager is good for known variables, however, sometimes it is desirable to work backwards. Using the same example of selling 100 apples, we might want to know what markup we would need to use in order to achieve a profit of £25. Goal Seek can be used to answer exactly these sorts of questions.
Goal Seek can be found on the Data ribbon, under What-If Analysis. It simply asks for three parameters. Set cell refers to the cell we want to contain the goal value. In this case it is E2, the cell showing the profit on apples. The value in the To value box should represent the goal, in this case 25, representing the desired £25 profit. Finally, the By changing cell box should show the cell reference for the cell to be manipulated in order to achieve the goal, in this case the markup.
Upon pressing OK, Excel will look for a solution, displaying a dialog box like the one below when it has finished. Note that in the example below, cell C2 has been updated to show a 250% markup and cell E2 showing the profit has updated accordingly. Pressing OK will confirm these changes and commit them to the worksheet, while pressing cancel will see the cells revert back to their previous values.
Data Table
Scenario summaries give a table showing data from various scenarios, however, they do not update if the data they are based upon changes. In the apple sales example, we might not need scenarios based on the cost price, as this is a non-controllable factor, yet it could still change in the future. The scenario summaries based upon it would not change if this was updated, whereas a data table would.
Data tables can be based on either one or two variables. For a single variable, a two column data table is required. The first column should contain the variable, whilst the second should be left blank for Excel to populate. The exception to this is the very first row, where the second column should show the formula on which the calculation is based.
To populate the data table, select the entire table (in the case of this example this would be cells H2 to I6) and navigate to What-If Analysis on the Data ribbon, and choose Data Table from the drop down menu. On the dialog box that appears, select the cell containing the variable as the column input cell. This tells Excel to use the value in the input column instead of this cell in the formula performing the calculation. Press OK, and Excel will populate the rest of the data table.
A two variable data table works in much the same way, however the layout is slightly different. Instead of consisting of two columns, there should be one column containing the values for the first variable, with a row containing the values for the second variable. The formula should go in the top left corner, where the two meet.
As with a single variable data table, highlight the entire table, so B6 to F10 in the example, and select Data Table from the What-If Analysis Dropdown on the Data ribbon. This time, enter the cells containing both the row and column variables in the formula. Press OK, and Excel will populate the table.
