Advanced Training for Microsoft Office Excel 2003

Analyzing Data

Defining and Editing Alternative Data Sets

1To create a scenario, click the Tools menu, and then click Scenarios.
2To add a scenario, in the Scenario Manager dialog box, click Add.
3To define a name for the scenario, for the purposes of this exercise, press SPACEBAR to have the text typed for you in the Scenario name box.
4To collapse the Add Scenario dialog box, at the right edge of the Changing cells box, click the Collapse Dialog button, as indicated.
5Notice that the name in the title bar of the dialog box changes to Add Scenario - Changing cells. To delete the contents of the Changing cells box, press DELETE.
6To define the cells that will hold alternative values, hold down the CTRL key, and then click cells F8, F9, F12, and F13.
7To expand the Add Scenario - Changing cells dialog box, at the right edge of the Changing cells box, click the Expand Dialog button, as indicated.
8To begin specifying the new values for the selected cells, click OK.
9To specify a new value for the first selected cell, press SPACEBAR to have the number typed for you.
10To return to the Scenario Manager dialog box, click OK.
11To replace the original values with the alternative values you defined in the scenario, click Show.
12To close the Scenario Manager dialog box, click Close.
13To revert to the original numbers in the worksheet, on the Standard toolbar, click the Undo button.
14To save the scenario that you have created, on the Standard toolbar, click the Save button.

Defining Multiple Alternative Data Sets

1To create a scenario, click the Tools menu, and then click Scenarios.
2Notice that the Furniture Sale scenario has already been created. To add another scenario for tools, click Add.
3To define a name for the scenario, for the purposes of this exercise, press SPACEBAR to have the text typed for you in the Scenario name box.
4To collapse the Add Scenario dialog box, click the Collapse Dialog button at the right edge of the Changing cells box, as indicated.
5Notice that the name in the title bar changes to Add Scenario - Changing cells. To delete the contents of the Changing cells box, press DELETE.
6To define the cells that will contain alternative values, click cell D7, hold down the SHIFT key, and then click cell D11.
7To expand the Add Scenario - Changing cells dialog box, click the Expand Dialog button at the right edge of the Changing cells box, as indicated.
8To begin specifying the new values for the selected cells, click OK.
9To specify a new value for the first selected cell, press SPACEBAR to have the number typed for you.
10To return to the Scenario Manager dialog box, click OK.
11To summarize the scenario results, click Summary.
12To collapse the Scenario Summary dialog box, click the Collapse Dialog button at the right edge of the Result cells box.
13To delete the contents of the Result cells box, press DELETE.
14To define the cells that will hold the summary of the two scenarios, click cell C14, hold down the SHIFT key, and then click cell H15.
15To expand the Scenario Summary dialog box, click the Expand Dialog button at the right edge of the Result cells box.
16To view the new worksheet named Scenario Summary containing the projected sales for furniture and tools, click OK.
17To view the summary of row 15, click once below the vertical scroll box.
18To save your changes, on the Standard toolbar, click the Save button.

Working with Goal Seek and Solver

1To select the target value that you need to achieve, click cell C4.
2To begin using Goal Seek to determine the necessary input values, click the Tools menu and then click Goal Seek.
3To set the target value, in the Goal Seek dialog box, click in the To value box, and for the purposes of this exercise, press SPACEBAR to have the number typed for you.
4To enter the cell reference for the value you need to adjust, click in the By changing cell box, press SPACEBAR to have the cell reference typed for you, and then click OK.
5Notice that the values in the worksheet have been recalculated, and the Goal Seek Status dialog box displays the closest solution that Goal Seek has found. To accept the solution, click OK.
6To revert to the original cell values, on the Standard toolbar, click the Undo button.
7To save your changes, on the Standard toolbar, click the Save button.
8To open AdDetails.xls, on the Standard toolbar, click the Open button, double-click the Sales folder, click AdDetails, and then click Open.
9To begin using Solver, click the Tools menu and then click Solver.
10To begin adding a constraint, click Add.
11To specify the cell for which you need to constrain the value, click cell F9.
12To enter a cell name in the Constraint box, click in the Constraint box and then click cell G11.
13To accept the constraint and add another, click Add.
14To specify the cell for which you need to constrain the value, click cell G9.
15To specify the relationship between the referenced cell and the constraint, click the list arrow in the middle box, as indicated, and then click >=.
16To enter a cell name in the Constraint box, click cell G12, and then to accept the constraint and add another, click Add.
17To specify the cells for which you need to constrain the value, click in cell E5 and drag to cell E7.
18To specify the relationship between the referenced cell and the constraint, click the list arrow in the middle box, and then click >=.
19To enter a cell name in the Constraint box, click cell G13, and then to accept the constraint and add another, click Add.
20To specify the cell for which you need to constrain the value, click cell E8.
21To specify the relationship between the referenced cell and the constraint, click the list arrow in the middle box, and then click >=.
22To enter a cell name in the Constraint box, click cell G14, and then to accept the constraint and add another, click Add.
23To specify the cells for which you need to constrain the value, click in cell E5 and drag to cell E8.
24To specify the relationship between the referenced cell and the constraint, click the list arrow in the middle box, and then click int.
25To accept the constraint, click OK.
26To display the result in the worksheet, in the Solver Parameters dialog box, click Solve.
27To accept the solution found by Solver, in the Solver Results dialog box, click OK.

Analyzing Data with Descriptive Statistics

1To begin calculating the descriptive statistics, click the Tools menu, and then click Data Analysis.
2To choose the descriptive statistics tool from the Analysis Tools list, click Descriptive Statistics and then click OK.
3To collapse the Descriptive Statistics dialog box, click the Collapse Dialog button at the right edge of the Input Range box, as indicated.
4To specify the cell values that need to be analyzed, click cell C30, hold down the SHIFT key, and then click cell L30.
5To expand the Descriptive Statistics dialog box, click the Expand Dialog button at the right edge of the Input Range box, as indicated.
6To specify the data that needs to be analyzed, in the Descriptive Statistics dialog box, in the Input area, click Rows.
7To define and create the new statistics worksheet, in the Output options area, verify that New Worksheet Ply is selected by default, select the Summary statistics check box, and then click OK.
8To increase the width of column A, double-click the indicated area between column A and B.
9To increase the width of column B, double-click the indicated area between column B and C.
10To save your changes, on the Standard toolbar, click the Save button.