Excel Annoyances

WHAT-IF ANALYSIS ANNOYANCES

CREATE A SCENARIO

The Annoyance:

I have two bosses, which means I’m asked a lot of “What if” questions. For example, when I put together a project cost summary worksheet, each boss asked about different sets of variables: what would be the effect if one component or another cost more or less? Could I drive down labor costs through productivity gains? All good questions, but when I try to include them in one worksheet my head starts spinning. What I really need is some way to display alternative values in certain selected cells, such as labor costs and component prices, and a way to switch back and forth between them. Is that possible?

The Fix:

You have only two bosses? Luxury. When I worked in Washington, DC, I had two official bosses and three “dotted-line” bosses who could reach across the corporate organization chart to order me around. Needless to say, I developed a few survival strategies, and one of them is the scenario.

A scenario is an alternative data set you can store as part of a worksheet. When you want to display the alternative values, just call up the alternate scenario and keep right on going with your presentation. Here’s how you create a scenario:

1. Choose Tools → Scenarios to display the Scenario Manager dialog box.

2. Click the Add button, and give your new scenario a name.

3. Click the Collapse Dialog button in the “Changing cells” field, select the cells you want to change, and press Enter. Remember that you can select noncontiguous groups of cells ...