What-if analysis in Microsoft Excel 2019

We will now look at a set of analysis tools called what-if analysisin Excel 2019. This category consists of three tools—namely, Goal Seek, Scenario, and Data Table. In this book, we will only focuson Goal Seek and Scenario. Now, let’s look at how to use theGoal Seek tool:

1. Open the SSGSchoolA.xlsx workbook and select theBRITTON-FINAL sheet.

   We will show you a really simple example of how to use Goal Seek. GoalSeek allows you to find out missing information based on the datayou already have. For instance, take a situation where a student getsfour results back from the exams they have already sat but still has onefinal exam to sit in order to pass the course. We can work out theaverage result of the five exams (the four received results and themissing result) and then use this average to work out the mark that thestudent needs to get in the final exam to achieve an overall grade of60 to pass the course.

2. Click on cell H7 and work out the average of all fiveresults—this includes the empty Final Exam cell. You shouldreceive a result of 57.2.
   This is not enough to pass the course as the student needs to achieve atleast 60 to pass. With this information, the student nowknows how hard they need to work in the final exam to achieve theoverall pass grade of 60.
3. As we can see the average in cell H7, we can use thatcell in Goal Seek. Go to Data | What-IfAnalysis | Goal Seek…:

4. In the Goal Seek dialog box, enter60 into the To value:placeholder, then click on cell H6 to place the referenceinto the By changing cell:placeholder. Click on the OK button towatch the magic happen:

So, in order to achieve a final grade mark of 60, thestudent needs to get 89 in the final exam.

Let’s now look at the financial modeling tool called Scenario. This tool is great to use toexamine and evaluate future projections or estimations of possibleeffects on the value of a business in either a positive or negative way.It is used for business decision-making in order to find the bestpossible outcome (for example, profits) or worst-case scenarios, such aslosses. When using this tool, you create the original scenario, theworse-case scenario, and the best-case scenario:

1. Open the ScenarioSolver.xlsx workbook. In cellA19, you will find the budget information that works out theoperating income for a business. The values in the GrossRevenue, Cost of Goods Sold, Income,Rent, Utilities, and GeneralAdministrative cost cells are values that could change. We willuse these cells to work out different scenarios for our business.
2. Go to Data | What-If Analysis | Scenario….

3. Type Actual into the Scenarioname: placeholder, then select the actual cell values in theworksheet, as in the following screenshot, by pressing Ctrl andclicking your mouse:

4. Type Expenses into the Scenario name: placeholder, edit the valuesas in the following screenshot, then click on Add:

5. Type Revenue into the next Scenario name: placeholder, then click onOK. Edit the scenario values to alterthe Revenue values, as shown, then click on Add:

6. To see the different scenarios created, click on Expenses in the Scenario dialog box, then select Show at the bottom of the dialog box. Noticehow the values update in the worksheet to project the future operatingincome. Click on the Revenue scenarioto see those changes, too:

7. You can produce a summary from the different scenarios you createdto visually see the changes next to each other. Click on Summary… in the Scenario Manager dialog box, then click onthe result cells you wish to include in the summary:

8. Click on OK to view the scenariosummary; this is created on a separate sheet in the workbook:

Notice that Changing Cells and Result Cells incolumn B and C are cell references. It would beeasier to read if these were named range cells that reflect actual cellnames, rather than references—consider this skill (shown in a previoustopic) when you use data for analysis and reporting. 

You have now learned about some of the analysis tools in Excel 2019and know how to install the add-in Analysis ToolPak. In the nextsection, we will discuss data models and learn how to buildrelationships between datasets for easy reporting.