Excel Project 8

1
Excel Project 8

• Formula Auditing, Data
• Validation, and Complex
• Problem Solving

2
Objectives

• Use the Formula Auditing toolbar to analyze a
  worksheet
• Trace precedents and dependents
• Use the Watch Window to monitor cell values
• Add data validation rules to cells
• Circle invalid data on a worksheet

3
Objectives

• Use trial and error to solve a problem on a
  worksheet
• Use goal seeking to solve a problem
• Use Excels Solver to solve a complex problem
• Password-protect a workbook file
• Use Excels Scenario Manager to record and save
  different sets of what-if assumptions and the
  corresponding results

4
Objectives

• Create a Scenario Summary of scenarios
• Create a Scenario PivotTable
• Set and change the properties of a workbook
• Attach a custom toolbar to a workbook

5
Showing the Formula Auditing Toolbar

• Start Excel, open, and make the necessary
  adjustments to the workbook as described on page
  EX 575
• Click Tools on the menu bar and then point to
  Formula Auditing
• Click Show Formula Auditing Toolbar

6
Showing the Formula Auditing Toolbar
7
Tracing Precedents

• Click cell E14 and then click the Trace
  Precedents button on the Formula Auditing toolbar
• Click the Trace Precedents button two more times

8
Removing the Precedent Arrows

• Click the Remove Precedent Arrows button on the
  Formula Auditing toolbar twice
• Click the Remove Precedent Arrows button again

9
Tracing Dependents

• Click cell B8 and then click the Trace Dependents
  button on the Formula Auditing toolbar
• Click the Trace Dependents button two more times

10
Removing the Dependent Arrows

• Click the Remove All Arrows button on the Formula
  Auditing toolbar

11
Adding Data Validation to Cells

• Select the range B8B11
• While holding down the CTRL key, select the range
  C8C10
• While holding down the CTRL key, select the range
  D8D9
• Click Data on the menu bar
• Click Validation

12
Adding Data Validation to Cells

• When Excel displays the Data Validation dialog
  box, click the Allow box arrow and then click
  Whole number in the Allow list
• Click the Data box arrow and then click greater
  than or equal to in the Data list
• Type 0 in the Minimum box
• Click the Input Message tab and then type Discs
  to Replicate in the Title text box
• Type Enter the number of discs to replicate on
  the machine. The number must be a whole number
  that is greater than or equal to zero

13
Adding Data Validation to Cells

• Click the Error Alert tab and then type Input
  Error in the Title text box
• Type You must enter a whole number that is
  greater than or equal to zero. In the Error
  message text box
• Click the OK button
• Click cell E21

14
Adding Data Validation to Cells
15
Opening the Watch Window and Adding Cell Watches

• Click the Show Watch Window button on the Formula
  Auditing toolbar
• Click the Add Watch button on the Watch Window
  toolbar
• If necessary, move the Add Watch dialog box so
  that column E is not hidden
• Select the range E8E11

16
Opening the Watch Window and Adding Cell Watches

• Hold down the CTRL key and then click cell E14
• Click the Add button
• If necessary, expand the Name column to view the
  cell names in the third column

17
Using Trial and Error to Attempt to Solve a
Complex Problem

• If necessary, resize the Watch Window so that
  only the cell watches appear as shown in Figure
  8-19 on page EX 588
• Click cell B8 and type 2.5 as the number of CDs
  to replicate on Machine 1 and then press the
  ENTER key
• Click the Retry button
• Type 200 as the number of CDs to replicate on
  Machine 1 in cell B8
• Click cell B10 and type 300 as the number of DVDs
  to replicate on Machine 1

18
Using Trial and Error to Attempt to Solve a
Complex Problem

• Click cell B11 and type 285 as the number of
  SACDs to replicate on Machine 1
• Click cell C9 and type 255 as the number of VCDs
  to replicate on Machine 2 and then press the
  ENTER key
• Click cell D8 and type 200 as the number of CDs
  to replicate on Machine 3
• Click cell B8, type 0 as the number of CDs to
  replicate on Machine 1, and then press the ENTER
  key

19
Using Trial and Error to Attempt to Solve a
Complex Problem
20
Using the Goal Seek Command to Attempt to Solve a
Complex Problem

• Click cell B13, the cell that contains the total
  hours for the order
• Click Tools on the menu bar
• Click Goal Seek
• Click the To value text box
• Type 50 and then click the By changing cell box

21
Using the Goal Seek Command to Attempt to Solve a
Complex Problem

• Click cell B9 on the worksheet
• If necessary, move the Goal Seek dialog box
• Click the OK button
• Click the OK button
• Click cell E21 to deselect cell B13

22
Using the Goal Seek Command to Attempt to Solve a
Complex Problem
23
Circling Invalid Data and Clearing Validation
Circles

• Click the Circle Invalid Data button on the
  Formula Auditing toolbar
• Click the Clear Validation Circles button on the
  Formula Auditing toolbar
• If necessary, select cell E21
• Click the Close button on the Watch Window
• Click the Close button on the Formula Auditing
  toolbar

24
Circling Invalid Data and Clearing Validation
Circles
25
Using Solver to Find the Optimal Solution to a
Complex Problem

• Click Tools on the menu bar
• Click Solver
• When Excel displays the Solver Parameters dialog
  box, click cell E14 to set the target cell
• Click Min in the Equal To area
• Click the Collapse Dialog button in the By
  Changing Cells area

26
Using Solver to Find the Optimal Solution to a
Complex Problem

• Click the By Changing Cells box and then select
  the range B8B11
• Hold down the CTRL key and then select the ranges
  C8C10 and D8D9
• Click the Expand Dialog button
• Click the Add button
• If necessary, move the Add Constraint dialog box
  so that the range B8B11 is visible

27
Using Solver to Find the Optimal Solution to a
Complex Problem

• Select the range B8B11 to set the value of the
  Cell Reference box
• Click the middle box arrow and then select gt in
  the list
• Type 0 in the Constraint box
• Click the Add button
• Select the range B8B11 to set the value of the
  Cell Reference box

28
Using Solver to Find the Optimal Solution to a
Complex Problem

• Click the middle box arrow and then select int in
  the list
• Click the Add button
• Click cell E8 to set the value of the Cell
  Reference box
• Click the middle box arrow and then select gt in
  the list
• Click the Constraint box and then click cell B18

29
Using Solver to Find the Optimal Solution to a
Complex Problem

• Click the Add button
• Enter the remaining constraints shown in Table
  8-2 on page EX 595, beginning with the
  constraints for the range C8C10
• When finished with the final constraint, click
  the OK button in the Add Constraint dialog box
• Click the Options button
• When Excel displays the Solver Options dialog
  box, click Assume Linear Model

30
Using Solver to Find the Optimal Solution to a
Complex Problem

• Click the OK button
• Click the Solve button in the Solver Parameters
  dialog box
• Click Answer in the Reports list
• Click the OK button

31
Using Solver to Find the Optimal Solution to a
Complex Problem
32
Viewing the Solver Answer Report for Order 1

• Click the Answer Report 1 tab at the bottom of
  the Excel window
• Drag the Answer Report 1 tab to the right of the
  Order Scheduling tab
• Double-click the Answer Report 1 tab and type
  Optimal Schedule Answer Report1 as the worksheet
  name

33
Viewing the Solver Answer Report for Order 1

• Click cell A1. Right-click the Optimal Schedule
  Answer Report1 tab and then click Tab Color on
  the shortcut menu
• Click yellow (column 3, row 4) and then click the
  OK button
• Scroll down to view the remaining cells of the
  Answer Report

34
Viewing the Solver Answer Report for Order 1
35
Saving the Workbook with Passwords

• Click the Order Scheduling tab at the bottom of
  the window
• Click File on the menu bar and then click Save As
• Type Reasonable Replications2 in the File name
  text box and, if necessary, click 3½ Floppy (A)
  in the Save in list
• Click the Tools button in the Save As dialog box
• Click General Options

36
Saving the Workbook with Passwords

• Type mincost in the Password to open text box
• Type mincost in the Password to modify text box
• Click the OK button
• When Excel displays the Confirm Password dialog
  box, type mincost in the Reenter password to
  proceed text box

37
Saving the Workbook with Passwords

• Click the OK button
• When Excel displays the Confirm Password dialog
  box, type mincost in the Reenter password to
  modify text box
• Click the OK button
• Click the Save button

38
Saving the Workbook with Passwords
39
Saving the Current Data as a Scenario

• Click Tools on the menu bar and select Scenarios
• Click the Add button
• When Excel displays the Add Scenario dialog box,
  type Order 1 in the Scenario name text box
• Click the Collapse Dialog button
• When Excel displays the Add Scenario Changing
  cells dialog box, select the range B8B11, hold
  down the CTRL key, and then select the ranges
  C8C10, D8D9, and B17B21

40
Saving the Current Data as a Scenario

• Release the CTRL key
• Click the Expand Dialog button
• Click the OK button
• Click the OK button
• Click the Close button on the Scenario Manager
  dialog box

41
Saving the Current Data as a Scenario
42
Adding the Data for a New Scenario

• Click cell B17 and type 80 as the maximum hours
  for the order
• Click cell B18 and type 1000 as the number of CDs
• Click cell B19 and type 100 as the number of VCDs
• Click cell B20 and type 75 as the number of DVDs
• Click cell B21, type 480 as the number of SACDs,
  and then click cell E21

43
Adding the Data for a New Scenario
44
Using Solver to Find a New Solution

• Click Tools on the menu bar
• Click Solver
• Click the Solve button
• Click Answer in the Reports list
• Click the OK button

45
Viewing the Solver Answer Report for Order 2

• Click the Answer Report 1 tab at the bottom of
  the Excel window
• Drag the Answer Report 1 tab to the right of the
  Optimal Schedule Answer Report1 tab
• Double-click the Answer Report 1 tab and type
  Optimal Schedule Answer Report2 as the worksheet
  name
• Click cell A1

46
Viewing the Solver Answer Report for Order 2

• Right-click the Optimal Schedule Answer Report2
  tab and then click Tab Color on the shortcut menu
• Click maroon (column 2, row 1) and then click the
  OK button
• Scroll down to view the remaining cells of the
  Order 2 Answer Report

47
Viewing the Solver Answer Report for Order 2
48
Saving the Second Solver Solution as a Scenario

• Click the Order Scheduling tab at the bottom of
  the window
• Click Tools on the menu bar
• Click Scenarios
• Click the Add button

49
Saving the Second Solver Solution as a Scenario

• Type Order 2 in the Scenario name text box
• Click the OK button
• Click the OK button
• Click the Close button

50
Showing a Saved Scenario

• Click Tools on the menu bar
• Click Scenarios
• If necessary, in the Scenario Manager dialog box,
  select Order 1 in the Scenarios list
• Click the Show button and then click the Close
  button

51
Creating a Scenario Summary Worksheet

• Click Tools on the menu bar
• Click Scenarios
• If necessary, in the Scenario Manager dialog box,
  select Order 1
• Click the Summary button
• Click the OK button

52
Creating a Scenario Summary Worksheet

• When Excel displays the Scenario Summary,
  double-click the Scenario Summary tab and type
  Weekly Orders Scenario Summary as the worksheet
  name
• Right-click the Weekly Orders Scenario Summary
  tab and then click Tab Color on the shortcut menu
• Click blue (column 6, row 2) and then click the
  OK button
• Drag the Weekly Orders Scenario Summary tab to
  the right of the Optimal Schedule Answer Report2
  tab

53
Creating a Scenario Summary Worksheet
54
Creating a Scenario PivotTable Worksheet

• Scroll to the Order Scheduling tab and then click
  the Order Scheduling tab at the bottom of the
  window
• Click Tools on the menu bar and then click
  Scenarios
• If necessary, when Excel displays the Scenario
  Manager dialog box, select Order 1
• Click the Summary button

55
Creating a Scenario PivotTable Worksheet

• In the Scenario Summary dialog box, click
  Scenario PivotTable report in the Report type
  area
• Click the OK button
• Double-click the Scenario PivotTable tab and type
  Order Scenario PivotTable as the worksheet name
• Click cell A8

56
Creating a Scenario PivotTable Worksheet

• Right-click the Order Scenario PivotTable tab and
  then click Tab Color on the shortcut menu
• Click green (column 4, row 3) and then click the
  OK button
• Drag the tab to the right of the Weekly Orders
  Scenario Summary tab
• Click the Save button on the Standard toolbar to
  save the workbook using the file name, Reasonable
  Replications2

57
Creating a Scenario PivotTable Worksheet
58
Changing Workbook Properties

• Scroll to the Order Scheduling tab and then click
  the Order Scheduling tab at the bottom of the
  window
• Click File on the menu bar
• Click Properties
• If necessary, in the Reasonable Replications2
  Properties dialog box, click the Summary tab
• Type Order Scheduling in the Title text box

59
Changing Workbook Properties

• Type Optimal Disc Replication Calculation in the
  Subject text box
• Type Anita Peresh in the Manager text box
• Type Order planning for April 2005, week 4. Order
  constraints met for these orders. in the Comments
  text box
• Click the OK button
• Click the Save button on the Standard toolbar to
  save the workbook using the file name, Reasonable
  Replications2

60
Changing Workbook Properties
61
Creating a Toolbar and Attaching a Toolbar to a
Workbook

• Right-click anywhere on a toolbar
• When the shortcut menu is displayed, click the
  Customize command
• If necessary, when Excel displays the Customize
  dialog box, click Toolbars, and then click the
  New button
• When Excel displays the New Toolbar dialog box,
  type Order Scenarios in the Toolbar name text box

62
Creating a Toolbar and Attaching a Toolbar to a
Workbook

• Click the OK button
• Click the Commands tab and then select Tools in
  the Categories list
• Scroll down the Commands list until the Scenario
  command is displayed
• Drag the Scenario command to the Order Scenarios
  toolbar

63
Creating a Toolbar and Attaching a Toolbar to a
Workbook

• Click the Toolbars tab on the Customize dialog
  box
• Click the Attach command
• When Excel displays the Attach Toolbars dialog
  box, click the Order Scenarios toolbar in the
  Custom toolbars list
• Click the Copy button

64
Creating a Toolbar and Attaching a Toolbar to a
Workbook

• Click the OK button in the Attach Toolbars dialog
  box
• Click the Close button in the Customize dialog
  box
• Drag the Order Scenarios toolbar to the right of
  the Formatting toolbar
• Click the Save button on the Standard toolbar

65
Creating a Toolbar and Attaching a Toolbar to a
Workbook
66
Summary

• Use the Formula Auditing toolbar to analyze a
  worksheet
• Trace precedents and dependents
• Use the Watch Window to monitor cell values
• Add data validation rules to cells
• Circle invalid data on a worksheet

67
Summary

• Use trial and error to solve a problem on a
  worksheet
• Use goal seeking to solve a problem
• Use Excels Solver to solve a complex problem
• Password-protect a workbook file
• Use Excels Scenario Manager to record and save
  different sets of what-if assumptions and the
  corresponding results

68
Summary

• Create a Scenario Summary of scenarios
• Create a Scenario PivotTable
• Set and change the properties of a workbook
• Attach a custom toolbar to a workbook

69
Excel Project 8 Complete