Chapter 8

Doing What-If Analysis


Performing what-if analysis with one- and two-variable data tables

Performing what-if analysis with goal seeking

Looking at different cases with the Scenario Manager

It would be a big mistake to regard Excel 2016 as merely a fancy calculator that shines at performing static computations, for the program really does excel (if you don’t mind the pun) at performing various types of more dynamic what-if analysis as well. What-if analysis enables you to explore the possibilities in a worksheet by inputting a variety of promising or probable values into the same equation and letting you see the possible outcomes in black and white (or, at least, in the cells of the spreadsheet).

In Excel 2016, what-if analysis comes in a wide variety of flavors (some of which are more involved than others). In this chapter, I introduce you to these three simple and straightforward methods:

  • Data tables enable you to see how changing one or two variables affect the bottom line (for example, you may want to know what happens to the net profit if you fall into a 25 percent tax bracket, a 35 percent tax bracket, and so on).
  • Goal seeking enables you to find out what it takes to reach a predetermined objective, such as how much you have to sell to make a $15 million profit this year.
  • Scenarios let you set up and test a wide variety of cases, all the way from the best-case scenario (profits grow by 8.5 percent) to the worst-case scenario (you don’t make any ...

Get Excel 2016 For Dummies now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.