Summing Up Scenarios With Excel

Mon 25th April 2011

For many, the idea of using data tables can sometimes prove too daunting to handle. But if you use the Scenario Manager in Microsoft Office Excel 2003 you can create easy to follow, what-if models in a few simple steps. Summing up need never phase you again.

I like to think of myself as a wordsmith more than a data analyst, so it's a given that when I compare notes with my buddies it will be about the merits of serif type as opposed to sans serif; the benefits of tracking changes in Word; and the amazing captioning abilities of PowerPoint.

However, from time to time, there are occasion when we need to cross over into the world of statistics, tracking progress reports and handling budgets. So it comes as no surprise that, nine times out of ten, when spreadsheets are mentioned I can see the eyes of my colleagues gloss over as they scuttle away to do something more pressing. And the most frequent objection to using the program? "Oh, I don't use Excel, it doesn't do Reports."

Well, this might be news to some, but Excel does "do Reports" - and it does them beautifully. If you are using Excel 2010, for example, it is really quite straightforward to create a Scenario Summary Report. A scenario can be described as a type of "what-if" situation. In Excel, this could be changing data to see what effect this would have on another set of data. For example, if you reduce the amount of money allocated to advertising, what will you have left to spend on research and development? In Excel 2010, scenarios can be saved, so that you can apply them again with a quick click of the mouse.

To model problems that are more complicated than data tables can handle, involving as many as 32 variables, you can use the Scenario Manager in Microsoft Office Excel 2003. Use the Scenario Manager to enter variable figures in your what-if model and watch the effect on dependent computed values. For example, you could use the Scenario Manager in Excel to create multiple scenarios for a single what-if model, each with its own sets of variables. You can create as many scenarios as your model necessitates. Or you could distribute a what-if model to members of your team so that they can add their own scenarios. Then you can collect the versions and merge all the scenarios onto a single worksheet. And you can even use Scenario Summary to examine relationships between scenarios created by multiple users.

It's easy to define a scenario, to add scenario and even edit a scenario with Scenario Manger. After you've created the scenarios that you'd like to test, you can view the changes that each scenario produces on your worksheet, and you can even modify and delete existing scenarios by using the Scenario Manager dialog box.

After using Scenario Manager to add scenarios to a table in an Excel 2010 worksheet, you can then instruct Excel to create a summary report. The report will display the changing and resulting values for all the scenarios you want to define and also the current values in the changing cells in that worksheet table.

To do this, open the workbook containing the scenarios you want to summarise. Now you can create the scenarios, by clicking the Add button. Then, on the Data tab, choose What-If Analysis/Scenario Manager in the Data Tools group . When the Scenario Manager dialog box appears, click the Summary button. The Scenario Summary dialog box gives you a choice between creating a (static) Scenario Summary (the default) and a (dynamic) Scenario PivotTable Report.

You can also modify the range of cells in the table that are included in the Results Cells section of the summary report by adjusting the cell range in the Result Cells text box. Now click OK to generate the report. Excel will now create a summary report for the changing values in all the scenarios (and the current worksheet) along with the calculated values in the Results Cells on a new worksheet. You can rename and reposition the Scenario Summary worksheet before you save it as part of the workbook.

And if you have two or more Microsoft Excel worksheets that are identical to each other (except that the values are different), you can use the Data Consolidate feature in Excel to consolidate the worksheets into a summary report. Many people do this to combine a workbook that consists of two worksheets. One worksheet could contain, for example, client names and their corresponding spend. The second worksheet lists the clients' names and their overall spend. It's easy, then, to consolidate the data and create a worksheet listing the clients' average spend. So easy, that there's really no excuse to scuttle away and find something much more uninteresting to do.

Author is a freelance copywriter. For more information on excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1635-summing-up-scenarios-with-excel.html