Convenience of multiple scenarios
Excel is a powerful tool that is often under-utilized as people are not aware of the numerous functions that the package has to offer. Many companies use Excel spreadsheets for financial and auditing purposes and with those kind of spreadsheets often the user is required to make many tables with only one variable that has changed. This results in multiple scenarios that are often hand-compiled which is very time-consuming but with Excel’s Scenario Manager this no longer has to take hours.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Uses of the multiple scenarios manager:
The scenario manager is the perfect tool to enter variable figures in your what-if model and watch the effect on dependent values. It is also use for more complicated models than what data tables can manage, often having up to 32 variables.
- create multiple scenarios for a what-if model, each scenario can have its own set of variables
- what-if model can be distributed to teams so that members can add their own scenarios, theses multiple scenarios can then be merged onto a single worksheet
- examine the relationship between multiple scenarios using the Scenario Summary
Applying the multiple scenarios manager
The Scenarios Manager for multiple scenarios from Excel has a certain process to be followed, here is a simple outline:
Defining scenarios:
- from the tools menu, click Scenarios
- a Scenario Manager dialogue box will appear; click Add
- type a name for your scenario
- in the changing cells field, enter in the cell references for the cells that are going to be varied
- click OK, one of multiple scenarios has now been created
- a Scenario value dialogue box will appear and here you will enter in the values that you would like to change
- to compile a new scenario, click Add
- to save your scenario, click save at the top of the workbook
Viewing your scenarios:
- select the Scenario Manager from the tools menu again
- select the one of the multiple scenarios you have compiled
- click show, Excel will replace the values in your current worksheet with those as specified in the scenario
- the dialogue box will still be visible, click close or press ESC to close the scenario and return your worksheet to its original statements
- Modifying and deleting scenarios:
- any of the multiple scenarios can be edited using the Scenario Manager, simply select edit from the dialogue box and change the necessary criteria
- to delete one of the scenarios simply go into the Scenario Manager select the scenario and click delete
Creating summaries of the multiple scenarios
- this enables you to keep track of the multiple scenarios that you have made
- in the scenario management dialogue box select the scenario summary options and then make sure scenario summary is selected
- in the result cells field enter the cells that you would like to have in your report, separate the cells with commas
- click OK
- Excel will now generate your scenario summary
For optimum results from this tool a training course in the usage of Excel will be beneficial to any company. Such a course can be completed at Miricle Solutions, a company that is an expert in the field of Excel, Accounting and Auditing. The staff are trained to train you to use the numerous functions that Excel has, including the multiple scenario manager to optimize your Excel usage and allow you to get the most out of the package.