Transcript Spreadsheet Modeling & Decision Analysis:

Multicriteria Decision Making

Decision problem often involve two or more
conflicting criterion or objectives:
– Investing: risk vs. return
– Choosing Among Job Offers: salary, location, career
potential, etc.
– Selecting a Camcorder: price, warranty, zoom, weight,
lighting, etc.
– Choosing Among Job Applicants: education,
experience, personality, etc.

We’ll consider two techniques for these types of
problems:
–The Multicriteria Scoring Model
–The Analytic Hierarchy Process (AHP)
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-1
The Multicriteria Scoring Model



Score (or rate) each alternative on each criterion.
Assign weights the criterion reflecting their relative
importance.
For each alternative j, compute a weighted average
score as:
ws
i ij
i
wi = weight for criterion i
sij = score for alternative i on criterion j

See file Fig15-41.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-2
The Analytic Hierarchy Process (AHP)
Provides a structured approach for determining the
scores and weights in a multicriteria scoring model.
 We’ll illustrate AHP using the following example:

– A company wants to purchase a new payroll and
personnel records information system.
– Three systems are being considered (X, Y and Z).
– Three criteria are relevant:
 Price
 User support
 Ease of use
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-3
Pairwise Comparisons

The first step in AHP is to create a pairwise comparison matrix for
each alternative on each criterion using the following values:
Value
Preference
1
Equally Preferred
2
Equally to Moderately Preferred
3
Moderately Preferred
4
Moderately to Strongly Preferred
5
Strongly Preferred
6
Strongly to Very Strongly Preferred
7
Very Strongly Preferred
8
Very Strongly to Extremely Preferred
9
Extremely Preferred



Pij = extent to which we prefer alternative i to j on a given criterion.
We assume Pji = 1/Pij
See price comparisons in file Fig15-43.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-4
Price
X
is Strongly Preferred to Y
 X is Very Strongly Preferred to Z
 Y is Moderately Preferred to Z
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-5
Support
X
is Equally to Moderately Preferred to Z
 Y is Moderately Preferred to X
 Y is Strongly Preferred to Z
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-6
Ease of Use
Y
is Equally to Moderately Preferred to X
 Z is Moderately Preferred to X
 Z is Equally to Moderately Preferred to Y
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-7
Criteria
 Support
is Moderately Preferred to Price
 Ease of Use is Moderately to Strongly
Preferred to Price
 Ease of Use is Equally to Moderately
Preferred to Support
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-8
Normalization & Scoring
 To
normalize a pairwise comparison matrix,
1) Compute the sum of each column,
2) Divide each entry in the matrix by its column sum.
 The
score (sj) for each alternative is given by the
average of each row in the normalized comparison
matrix.
 See
file Fig15-43.xls
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-9
Consistency

We can check to make sure the decision maker was
consistent in making the comparisons.

The consistency measure for alternative i is:
P s
ij j
Ci 
where
j
si
Pij = pairwise comparison of alternative i to j
sj = score for alternative j

If the decision maker was perfectly consistent, each
Ci should equal to the number of alternatives in the
problem.
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-10
Consistency (cont’d)

Typically, some inconsistency exists.

The inconsistency is not deemed a problem provided the
Consistency Ratio (CR) is no more than 10%
CI
CR 
 0.10
RI
where,


Ci
CI =   n  n / (n  1)
 i

n  the number of alternatives
RI =
for n =
0.00
0.58
0.90
1.12
1.24
1.32
1.41
2
3
4
5
6
7
8
Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-11
Obtaining Remaining Scores & Weights
This process is repeated to obtain scores for the
other criterion as well as the criterion weights.
 The scores and weights are then used as inputs
to a multicriteria scoring model in the usual way.
 See file Fig15-43.xls

Spreadsheet Modeling and Decision Analysis, 3e, by Cliff Ragsdale. © 2001 South-Western/Thomson Learning.
15-12