Apr 11, 2016
I created three Scenarios using the Scenario Manager feature in my previous blog. I used the following spreadsheet data:
The Scenarios I created are:
- Original Projections
- Advertising Push
- Reduced Expenses
These scenarios will show management two options for increasing the company’s Profit.
While you can display each scenario’s result separately by selecting the scenario name and clicking the Show button, you most likely will not take the laptop or PC with you to show these scenarios to the management.
You will want to create a professional Scenario Summary report and print it out. To create a Scenario Summary Report, perform the following steps:
- Click the Data tab on the Ribbon
- Click the What-If Analysis button
- Click the Scenario Manager… button. The following dialog box will display:
- Click the Summary… button
- Click OK
You will see the following report:
This report looks OK, but we do not know what the Changing Cells references represent. If you print out this report and presented it to your manager, probably he or she will say that this is rubbish. So let’s make this report better.
We will go back to the spreadsheet we used to create these scenarios. We will name the ranges first. To name the ranges in this spreadsheet:
- Select the ranges you want to name, in our example select all numbers and their related labels (see below):
- Click the Formulas tab on the Ribbon
- Go to Defined Names group
- Click the Create from selection button. You will see this dialog box.
- Click OK. Click the arrow next to the Name Box
Excel will use the rows labels to create the names.
- Click the Data tab on the Ribbon
- Click the What-If Analysis button
- Click the Scenario Manager… button. The following dialog box will display:
- Click the Summary… button
- Click OK
You will see the following report:
Now the report looks great!
The left hand side of the report show a plus sign (+). Click the plus sign (+). The comments you added to scenarios will display so everyone looking at this report will know what values you did change.
Watch out for other great Microsoft Excel blogs soon.
How do your Excel skills stack up?
Test NowNext up:
- Creating a Point-to-Site VPN to your Azure Virtual Machines
- The future is here
- Tune Your SSD for Windows 10
- Screen Clippings with Hyperlinks in OneNote
- The confident negotiator consistently gets to ‘yes’
- Ten Tips for Using Microsoft Project
- A guide to dealing with a complainer
- Tune Your SSD for Windows 10 - Part 2
- Rounding in Excel
- Feeling Connected at Work
Previously
- Protecting Confidential Data with Symmetric Encryption - Part 2
- Creating Custom Shows in PowerPoint
- Handling non-performing employees fairly and with common-sense
- How SharePoint works with Active Directory
- Master Managers are Master Planners
- Protecting Confidential Data with Symmetric Encryption
- How to create Scenarios with Microsoft Excel
- Who is a bad boss then?
- Fundamentals of Microsoft Azure
- Why is it so hard to apologise?