Use the Scenario toolbar to quickly analyze Excel data

by Mary Ann Richardson | Sep 05, 2006 7:00:00 AM

Tags: PRODUCTIVITY, Microsoft Office, Mary Ann Richardson, tool, Microsoft Excel, toolbar, Microsoft Office Suite Excel Tips Newsletter

Takeaway: Mary Ann Richardson shows you how to set up a scenario using a lot fewer steps than navigating all the menus you might ordinarily use in Excel.

While Excel's Scenario toolbar can be a powerful analysis tool, clicking through all the menus needed to set up a scenario is often clumsy and time consuming. Excel provides a tool that allows you to create a scenario in two steps. First, you will need to place the tool on your toolbar. Follow these steps:

1. Go to Tools | Customize.
2. On the Commands tab, select Tools under Categories.
3. In the Commands box, navigate to the Scenario: tool which has a drop-down box.
4. Click and drag the Scenario tool to the Standard toolbar.

Suppose you are planning a seminar and would like to know how much tuition you will need to charge in order to make a profit. First, you should follow these steps to set up a worksheet:

1. In A1 enter Instructional Cost Per Hour.
2. In A2 enter Hours Per Seminar.
3. In A3 enter Tuition.
4. In A4 enter Number of Attendees.
5. In A5 enter Total Seminar Cost.
6. In A6 enter Tuition Charged Per Seminar.
7. In A7 enter Total Profit Per Seminar.
8. In B5 enter =B1*B2.
9. In B6 enter =B3*B4.
10. In B7 enter =B6-B5.

Then, follow these steps to create two scenarios, one showing 10 attendees and the second showing 20 attendees:

1. Enter 50 in B1.
2. Enter 3 in B2.
3. Enter 100 in B3.
4. Enter 10 in B4.
5. Select B1:B4.
6. Click in the Scenario: drop-down box, type Minimum Attendees, and press [Enter].
7. Enter 20 in B4.
8. Select B1:B4.
9. Click in the Scenario: drop-down box, select the current scenario, type Maximum Attendees, and press [Enter].

You can now view each scenario by clicking the drop-down arrow in the Scenario: tool and selecting a scenario from the list. If you need to delete a scenario, go to Tools | Scenario, select the scenario from the list, click the Delete button, and then click OK.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Tech Tipshadr_dinesh@...  | 01/23/07