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:

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.

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.

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:

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!

In the next part, we'll take a look at Goal Seek, what it is and how to use it.

Move on to Goal Seek in Excel -->

<-- Back to the Excel Contents Page