Page 663 - Excel 2013 All-in-One For Dummies

Chapter 1: Performing What-If Scenarios
In This Chapter
✓ Doing what-if analysis in one- and two-variable data tables ✓ Creating and playing with different scenarios
✓ Performing goal seeking
✓ Using the Solver utility
Because electronic spreadsheet formulas are so good at automatically updating their results based on new input, they have long been used (and sometimes, misused) to create financial projections based on all sorts of assumptions. Under the guise of what-if analysis, you will often find the number crunchers of the company using Excel as their crystal ball for pro- jecting the results of all sorts of harebrained schemes designed to make the company a fast million bucks.
As you start dabbling in this form of electronic fortune-telling, keep in mind that the projections you get back from this type of analysis are only as good as your assumptions. So when the results of what-if analysis tell you that you’re going to be richer than King Midas after undertaking this new business venture, you still need to ask yourself whether the original assumptions on which these glowing projections are based fit in with real-world marketing conditions. In other words, when the worksheet tells you that you can make a million bucks of pure profit by selling lead-lined boxer shorts, you still have to question how many men really need that kind of protection and are willing to pay for it.
In Excel, what-if analysis comes in a fairly wide variety of flavors (some of which are more complicated than others). In this chapter, I introduce you to three simple and straightforward methods:
✦ Data tables enable you to see how changing one or two variables affects the bottom line. (For example, you may want to know what happens to the net profit if you fall into a 45 percent tax bracket, a 60 percent tax bracket, and so on.)
✦ Goal seeking enables you to find out what it takes to reach a predeter- mined objective, such as how much you have to sell to make a $20 million profit this year.
✦ Scenariosletyousetupandtestawidevarietyofcases,allthewayfrom the best-case scenario (profits grow by 20 percent) to the worst-case scenario (in which you don’t make any profit).