Analyzing Alternative Sets of Data

Advanced Training for Microsoft Excel 2002

Defining and Editing Alternative Data Sets

1	To begin defining an alternative data set, click the Tools menu, then click Scenarios.
2	To add a scenario to the file, in the Scenario Manager dialog box, click Add.
3	To name the scenario, in the Add Scenario dialog box, in the Scenario name box, type Furniture Sale.
4	To begin defining the cells for the Furniture Sale scenario, at the right edge of the Changing cells box, click the Collapse Dialog button.
5	To clear the contents of the Add Scenario - Changing Cells dialog box, press the DELETE key.
6	To select the cells for the Furniture Sale scenario, press and hold down the CTRL key, click cells F8, F9, F12, and F13, and release the CTRL key.
7	To use the selected cells in the new scenario, at the right edge of the Changing cells box, click the Expand Dialog button.
8	In the Edit Scenario dialog box, click OK.
9	To change the value in the 1: $F$8 box, type 5000, then press the TAB key.
10	To accept the values shown, click OK.
11	To view the changed contents of your worksheet, reflecting the values in your scenario, in the Scenario Manager dialog box, click Show, and then click Close.
12	To revert the changed cells to their original values before you save the file, on the Standard toolbar, click the Undo button.
13	To save the scenario you've created, on the Standard toolbar, click the Save button.

Defining Multiple Alternative Data Sets

1	To begin defining alternative data sets, click the Tools menu, and click Scenarios.
2	Notice that Furniture Sale is already a defined scenario. To add a second scenario to the file, in the Scenario Manager dialog box, click Add.
3	To name the second scenario, in the Add Scenario dialog box, in the Scenario name box, type Tool Sale.
4	To begin defining the cells used in this scenario, at the right edge of the Changing cells box, click the Collapse Dialog button.
5	To clear the contents of the Add Scenario - Changing Cells box, press the DELETE key.
6	To select cells representing sales of tools in June through October, click cell D7, press and hold down the SHIFT key, click cell D11, and release the SHIFT key.
7	At the right edge of the Changing cells box, click the Expand Dialog button.
8	To begin defining values for the scenario, in the Edit Scenario dialog box, click OK.
9	To change the value in the 1: $D$7 box, type 8500, then press the TAB key.
10	To accept the values shown, click OK.
11	To create an additional scenario that allows you to easily switch back to the original values of this worksheet, in the Scenario Manager, click Add.
12	In the Add Scenario dialog box, in the Scenario name box, type Normal.
13	At the right edge of the Changing cells box, click the Collapse Dialog button.
14	Note that cells D7:D11, used in the Tools Sale scenario, are already selected. To add to these the cells used in the Furniture Sale scenario, press and hold down the CTRL key, click cells F8, F9, F12, and F13, and release the CTRL key.
15	On the right edge of the Changing cells box, click the Expand Dialog button.
16	In the Edit Scenario dialog box, click OK.
17	To accept current worksheet values for use in the Normal scenario, in the Scenario Values dialog box, click OK.
18	To move the Scenario Manager out of the way of cells you need to view, click the blue Title Bar of the Scenario Manager, press and hold down the left mouse button, drag the Scenario Manager to the right upper corner, and release the mouse button.
19	To view the Tool Sale scenario reflected in the worksheet, in the Scenarios list, click Tool Sale, then click Show.
20	Note the changes to values and total in the Tools column. To view the Furniture Sale scenario reflected in the worksheet, in the Scenarios list, click Furniture Sale, then click Show.
21	Note the changes in the Furniture column. To return the worksheet to its original values, in the Scenarios list, click Normal, then click Show.
22	To create a new worksheet that reflects a summary of all scenarios, in the Scenario Manager, click Summary.
23	To define the cells for which you'd like to see summarized results, click cell C14, press and hold down the SHIFT key, click cell H15, and release the SHIFT key.
24	To display the newly created Scenario Summary worksheet, in the Scenario Summary dialog box, click OK.

Varying Your Data to Get a Desired Result

1	To use Goal Seek to determine the data needed to achieve a specified result, click the Tools menu, and then click Goal Seek.
2	You would like the Tools category to account for 25% of sales, and need to know what amount of tool sales will accomplish this goal. To set the target cell, click cell C4 on the worksheet.
3	To set the goal value, in the Goal Seek dialog box, click in the To value box, and then type 0.25.
4	To specify which cell should be changed to achieve your goal, click in the By changing cell box, and then click cell C3.
5	To have Excel search for a solution with the given variables, in the Goal Seek dialog box, click OK.
6	Notice that the closest solution Excel could find was 24.97%, rather than 25%. To accept the proposed solution, click OK.
7	To revert the changed cells to their original values, on the Standard toolbar, click the Undo button.

Finding Trends in Data

1	To find the trend for September sales revenue based on the six years of data shown, in the chart, click the purple September 1997 column (first from the left).
2	Click the Chart menu, and click Add Trendline.
3	In the Add Trendline dialog box, notice that it is set for a Linear trend type based on the September series, which is fine. To continue, click the Options tab.
4	To forecast September sales for the next two years, in the Forecast section, double-click in the Forward box , type 2, and then click OK.
5	To change the new trendline to forecast three years ahead, double-click the trendline.
6	On the Options tab of the Format Trendline dialog box, in the Forward box, click the up arrow two times.
7	To change the appearance of the trendline, click the Patterns tab.
8	In the Line section, click the Color arrow, then click the blue color (row 2, column 6).
9	To apply your changes, click OK.