ADVANCED MICROSOFT EXCEL Lesson 12 Working with Analysis Tools

1
ADVANCED MICROSOFT EXCELLesson 12 Working with
Analysis Tools
2
Objectives

• Use Excels auditing feature.
• Perform data validation.
• Circle invalid data.
• Create and Merge scenarios.

3
Objectives (cont.)

• Perform what-if analysis using Goal Seek.
• Use Solver.
• Project values with correlation and regression.

4
Terms Used in This Lesson

• Auditing
• Correlation
• Data Validation
• Dependent

• Goal Seek
• Precedent
• Regression
• Scenario

5
Auditing
Trace Precedents
6
Auditing (cont.)
Trace Dependents
7
Performing Data Validation and Circling Invalid
Data

• The Data Validation feature of Excel allows data
  to be entered according to certain rules that you
  specify.
• If data exceeds a certain amount, you can have
  the data flagged.

8
Performing Data Validation and Circling Invalid
Data (cont.)

• You can also have an instructive message appear
  as data is entered into cells and another message
  appear if the data does not meet the rules that
  you specify.
• Excel can circle any data that violates the rules
  specified.

9
Data Validation
1
3
4
2
10
Circling Invalid Data
Circled invalid data
11
Scenarios
Scenario Manager dialog box
Scenarios are created using the Scenario Manager
12
Scenarios
Scenarios created using the Scenario Manager
13
Merging Scenarios
Merge Scenarios dialog box
14
Goal Seek (What-if Analysis)
Finds one unknown value
15
Solver
Solver finds two or more unknown values.
Solver Parameter dialog box
16
Projecting Values Using Correlation and Regression
Regression dialog box
Correlation dialog box
17
Projecting Values Using Correlation and
Regression (cont.)
Regression Results
18
Summary

• Scenarios let you enter possible variations to
  values in a worksheet.
• The auditing feature assists you in locating
  precedents, dependents, and formula errors.
• Invalid data can be circled so that you can
  review data that violates the validation rules.
• Goal Seek and Solver assist you in finding
  unknown values.

19
Summary (cont.)

• You can add data validation to cell data to
  assist with the entry of data and alert you when
  data is not entered according to the rules that
  you specify.
• Correlation allows you to determine whether a
  strong relationship exists between variables.
• You can perform a regression if a strong
  relationship exists. Use a regression equation
  to predict values.