How to use Scenarios in Excel

Scenarios are similar to Tables, in that you are asking "What if"? "What if I change this value, or that amount? What will the spreadsheet look like then?" The difference is that you can create a number of different scenarios and save them.

An example of a scenario you might want to create is this:

You've worked out the family budget, and find you have barely enough left for a night out. The question is, what can you cut back on to give yourself more spending money?

We'll create a spreadsheet to tackle that problem, and you'll see how scenarios work. To get started, there's a new spreadsheet for you to download:

Download the Spreadsheet for this section

When you open up the spreadsheet, you'll see there is only 46 pounds left at the end of every month. And the cost of the car hasn't even been included yet! Clearly, some cutbacks have to be made.

With a scenario, we can switch between our different budgets and see which one we like best. The best way to see how a scenario works is to construct one yourself.

 

Scenarios

To create your scenario, do the following:

• 
  
  From the Excel menu bar, click on Tools


• 
  
  From the drop down menu, click on Scenarios


• 
  
  The Scenario Manager dialogue box pops up

• 
  
  We want to create a new Scenario, so click the Add button


• 
  
  The Add Scenario dialogue box pops up

• 
  
  Click inside the Scenario Name text box and type Original Budget

You now need to tell Excel which cells will be changing. Although nothing will be changing in this scenario (because it's our original), we still need to specify which cells will be changing. We want to reduce the Food bill, the Clothes Bill, and the Phone bill.

• 
  
  So click inside the Changing Cells text box


• 
  
  Click back on your spreadsheet and highlight cells B7 to B9


• 
  
  The "marching ants" will appear around your selected area, and your dialogue box will look like this one:

• 
  
  Click the OK button at the bottom


• 
  
  Excel will prompt you for the Scenario Values


• 
  
  We don't want the values on the original to change, so just click OK


• 
  
  You are taken back to the Scenario Manager dialogue box, and you'll see the scenario you have just created displayed.

We now need to create another Scenario, so that we can switch between the original budget and the new one. So click the Add button to add a new scenario.

• 
  
  When you get the Add Scenario dialogue box back up again, click inside the Scenario Name text box and type Budget Two.


• 
  
  The Changing cells text box should read B7:B9. These are the cells we want to change, so leave them alone


• 
  
  Click the OK button at the bottom of the Add Scenario dialogue box


• 
  
  You will be taken to the Scenario Values dialogue box. It looks like the one below:

The values in the Text Boxes are the ones from cells B7, B8, and B9. Click inside each text box and type in a different value. The 280 spent on food can be changed to something like 180. Take 50 off the clothes bill. And take 20 off the phone bill. Your dialogue box will then look like this one:

Click the OK button when you're done. You'll be taken back to the Scenario Manager. And this is where the fun starts.

To view a scenario, click on one from the list. Then click the Show button. In the image below, Budget Two has been selected:

After you click the Show button, have a look at your spreadsheet. The figures should have changed. Select Original Budget from the list, then click the Show button. Your spreadsheet should show the original figures.

Click the Close button on the dialogue box when you're done. To view your two scenarios again, just click on Tools > Scenarios. This will bring up the Scenario Manager again.

So a Scenario offers you different ways to view a set of figures, and allows you to switch between them quite easily.

Scenario Reports

Another thing you can do with a scenario is produce a report. This is quite easy. To produce a report of your scenarios, do the following:

• 
  
  Click on Tools from the menu bar


• 
  
  From the drop-down menu, click on Scenarios


• 
  
  The Scenario Manager dialogue box appears


• 
  
  Click on the Summary button


• 
  
  The following dialogue box appears

• 
  
  To change the result cells, click on your spreadsheet


• 
  
  Click individual cells by holding down the Ctrl key on your keyboard, and then clicking inside a cell with your left mouse button


• 
  
  So hold down the Ctrl key and click on cell D3 (income)


• 
  
  Hold down the Ctrl key and click on cell B12


• 
  
  Hold down the Ctrl key and click on cell D13


• 
  
  If you make a mistake and want to get rid of a highlighted cell, just click inside it again with the Ctrl key held down


• 
  
  Click OK when you're done


• 
  
  Excel will produce the Summary for you. It will look something like the one below:

All right, it's not terribly easy to read, but it looks pretty! Perhaps it will be enough to convince our family to change their ways. Unlikely, but a nice diagram never hurts!