Natural Scores
Price
Prexel
Criston
Thrush
Fuel Effic. (MPG)
$22,000
$25,000
$27,000
Utilities and Weights
10%
Price
Prexel
Criston
Thrush
1
0.4
0
Safety Rating
32
38
35
Comfort/Ride
8.5
8.2
9.6
Color
6.7 Red
7.9 Black
9.2 Blue
Original Weights
30%
30%
20%
10%
Fuel Effic. (MPG) Safety Rating
Comfort/Ride
Color
0
0.21
0
1
1
0
0.48
0
0.5
1
1
0.7
20%
Price
Prexel
Criston
Thrush
20%
20%
20%
20%
Fuel Effic. (MPG) Safety Rating
Comfort/Ride
Color
1
0
0.21
0
1
0.4
1
0
0.48
0
0
0.5
1
1
0.7
40%
Price
Prexel
Criston
Thrush
20%
20%
10%
10%
Fuel Effic. (MPG) Safety Rating
Comfort/Ride
Color
1
0
0.21
0
1
0.4
1
0
0.48
0
0
0.5
1
1
0.7
100%
Total Score
0.263
0.436
0.72
100%
Total Score
0.442
0.376
0.64
100%
Total Score
0.542
0.408
0.47
Original
Solution
Sensitivity Analysis:
Equal Weights.
High Weight for Price.
Module 3 - Background
Multi-Attribute Decision Making (MADM)
This decision method assumes certainty. In other words,
there are no probabilities of future states to determine. And
the data and costs are assumed to be known and accurate.
The most common type of decision is a preference decision.
The decision maker wants to determine which of several
options is the best to achieve some set of goals or fulfill a
set of criteria or attributes. Common examples include
deciding which car to buy, which house to buy, which
apartment to rent, where to go on vacation, which machine
to buy for production, which supplier to use, and many more.
The decision process consists of the Decision Maker (DM)
identifying the need for some object (or person) or concept
that he/she currently does not have. Or it could be to replace
some object that has outlived its usefulness, such as
replacing a copying machine. The decision consists of
determining a set of criteria that the object must have or
meet with some level of satisfaction. For example, when
buying a car, the DM might consider its price, color, fuel
efficiency, safety rating, warranty, comfort/ride, among other
factors. This process is important because it provides and
defines the performance and outputs that the user will
expect.
The step for this decision is to search for and find the
choices (alternatives or options) to be considered. There
may be one criteria that is used as a filter, such as price. In
the car buying example, the DM may have a price range that
fits into his/her budget. They may also have a preference of
Make, such as Chevrolet or Ford. But this second
preference may actually be a bias and could limit the
choices and exclude some viable choices. The search for
alternatives usually generates choices in a serial manner.
Specific alternatives are identified one at a time. It is
possible to find several choices at nearly the same time, for
example, being shown several different makes and models
of cars at one dealership during a single trip.
The DM now has identified the choice options as well as the
criteria to be fulfilled. Each alternative will fulfill each criterion
at some level of value. The DM must collect this data and
put it into a table for easy analysis. Here is an example of a
decision table for purchasing a car.
Price
Fuel Effic.
(MPG)
Safety
Rating
Comfort/Ride
Color
Prexel
$22,000
32
8.5
6.7
Red
Criston
$25,000
38
8.2
7.9
Black
Thrush
$27,000
35
9.6
9.2
Blue
Note that the names are fictitious. The safety ratings and
comfort/ride ratings could easily be obtained from a car
buyer magazine. Price and MPG are from the dealerships.
We are using only 3 options and 4 criteria for example
purposes. The colors are those of cars that are in stock. You
could order a car of your preferred color, but you do not
want to wait 6 – 8 weeks for delivery.
As you look at this table, you will see that each criterion is
measured differently than the others. How do you compare
price with MPG, with Safety rating, and with color? To do
this, you need a common metric and one that each
criterion’s value can be converted easily. This metric is
Utility, which is scaled between 0 and 1. Utility of 0 has no or
minimal value and utility of 1 is the maximum.
Let’s take a minute to get rigorous in our model and use
some shorthand notation.
Let Ci be the ith criterion. We have five criteria and we
number them from 1 to 5. So Price is C1 and Color is
C5. Criterion Ci, i = 1 to n, and n =5.
Let Aj be the jth alternative. We have three alternative and
we number them from 1 to 3. A1 is the Prexel, A2 is the
Criston, and A3 is the Thrush. Alternative Aj, j = 1 to m, and
m = 3.
In the table we have the values of each alternative j for each
criteria i and we term this vij, the value of the ith criteria for
the jth alternative. Value vij, i = 1 to n, and j = 1 to m.
But we need to convert the values, vij to utilities, uij, so that
they are all measured on the same metric. This will allow us
to compare alternatives.
To convert from raw values to utilities can be done in several
different ways. The easiest way is to use a linear
transformation. Take fuel efficiency. There is 38, 35, and 32.
The maximum value is assigned a Utility value of 1 and the
minimum value is assigned a Utility value of 0. The
intermediate values are assigned a proportionate level using
a linear translation. So Utility of 38, or U(38) = 1 and U(32) =
0. Or in general, U(Max value) = 1, and U(Min value) = 0.
But what about U(35) = ??
To find U(35), use a translation formula: U(X) = (X – Min
value) / (Max value – Min value)
In our example, U(35) = (35 – 32) / (38 – 32) = 3 / 6 =
0.5
This formula for U(X) is for a criterion when More is Better.
You can use this to convert the Safety Rating and the
Comfort/Ride rating, because More is Better.
But for a criterion where Less is Better, like Price, you need
to use this formula:
U(Max value) = 0, and U(Min value) = 1. (remember, Less is
Better).
U(X) = (Max value – X) / (Max value – Min Value).
In our example, on the Price criterion: U(27000) = 0,
U(22000) = 1,
and U(25000) = (27 – 25) / (27 – 22) = 0.4
But what about criterion that are subjective or do not have
any numeric raw values, like color? The utility scores are
determined strictly by personal preference. Which of the
three colors is most preferred and which is least preferred,
and which are in the middle?
In our example, let’s say that Red is most preferred and
Black is least preferred, making Blue with a medium level
preference. U(Red) = 1, U(Black) = 0, and U(Blue) = ??
Where does Blue fit on a scale of 0 to 1? This is a subjective
rating. You can choose any score. In our example the DM
prefers Blue to be 0.7, closer to Red than to Black.
Now we have our utilities, uij. Here is the decision table with
utility scores.
Price
Fuel Effic.
(MPG)
Safety
Rating
Comfort/Ride
Color
Prexel
1
0
0.21
0
1
Criston
0.40
1
0
0.48
0
Thrush
0
0.50
1
1
0.70
There is one more step. Each criterion must be weighted
according to its relative importance to the decision or the
overall performance or results. These weights are decimals
or percent and must total to 1.0. We will let wi denote the
numerical weight for each of the i criterion. And we use this
formula to insure the correct amount of total
weight. Sum(wi) = 1.0.
How does the DM determine these weights? This is
subjective as well. The first step is to rank order the criteria
with 1 most important and N as the least important (here N =
5). In our example, the DM thinks that Fuel Efficiency and
Safety Rating are the most important, but not sure which is
first. Then Comfort/Ride. Finally, the DM thinks that maybe
price and color are last. The rank ordering is:
[C2 and C3], C4, [C1 and C5].
The DM decides to use the following weights, at least as a
starting point. Then he/she can do some sensitivity analysis
and adjust them a bit.
C1 = 0.1
C2 = 0.3
C3 = 0.3
C4 = 0.2
C5 = 0.1
Note that these weights total to 1.0.
The final step is to multiply the weights times each utility
score for each alternative and sum these to get a total score
for each alternative. Using our notation, the formula for Total
Score, Tj , for the jth alternative is:
Tj = Sum[(wi)(uij)], i = 1 to n (n = 5)
For example, the total score for the Prexel is calculated as:
(0.1)(1) + (0.3)(0) + (0.3)(0.21) + (0.2)(0) + (0.1)(1) = 0.263.
This is the table with all alternatives and their Total Scores
using the weights above.
Weight
10%
30%
30%
20%
10%
100%
Criteri
on
Price
Fuel Effic.
(MPG)
Safety
Rating
Comfort/Ri
de
Color
Total
Score
Prexel
1
0
0.21
0
1
0.263
Criston
0.4
1
0
0.48
0
0.436
Thrush
0
0.5
1
1
0.7
0.720
We should not be surprised that that Thrush is the preferred
choice. It is scored the best on two of the criteria and
2nd best on two others. And is only scored the worst on one
criteria which was given the lowest weight of 0.1
The Prexel scored best on two criteria, but these were the
least important in weight and it also scored the worst on two
criteria.
The question now to consider, “do these weights reflect the
true preference and importance to the DM? If they were
adjusted up or down by some degree, would it change the
overall total score and hence the decision?” This process is
Sensitivity Analysis.
For a general understanding of decision making and the
process, watch the following videos:
http://permalink.fliqz.com/aspx/permalink.aspx?at=d55a346d20aa46
6d84ffd99b15f7d128&a=5fae3cf0f1624f39b0341263a6541ea0
http://permalink.fliqz.com/aspx/permalink.aspx?at=2cc5262c22b543
ec928bec51be6c23f6&a=5fae3cf0f1624f39b0341263a6541ea0
Download this Excel file with the Car Decision
Example: BUS520-Module 3 SLP Car Example.xlsx
PRACTICE EXERCISE: Now that you have seen how to
develop a Decision table, try this Practice Exercise.
Practice Exercise Scenario: Hiring a key person.
You are hiring a person for a top position in your company.
You have narrowed the field down to the top four
candidates. You want to use multi-attribute decision
analysis. You have determined that there are four decision
criteria that are most important.
Four Attributes (criteria): Salary, experience, education,
leadership personality.
The salary number is the amount that the candidate said
he/she needed to accept the job.
Experience is based on number of years of direct
experience, plus an add-on for other related experience that
is equal to about half of the years.
Education is the level of degree plus any other training or
certifications. You have decided to use a scale of 1 to 5 to
evaluate. 1=bachelor, 3=master, 4=PhD or other doctoral
degree. Add-ons for certifications, i.e. CPA, Certified Coach,
etc. and for second degrees can be applied from .5 points,
or 1 point. The max score cannot exceed 5 points.
Leadership Personality is based on your subjective
evaluation including the opinion of your Supervisor who will
be working with this person. This score is also a rating scale
of 1 to 5.
1 = probably needs a lot of effort to be a leader, and 5 =
probably will perform at top leadership capability.
Here is the information on the four top candidates:
Bob:
Salary: $75,000, Experience: 22 years direct, 8 other
related, Education: Bachelors plus certified coach and
certified leadership graduate; Leadership personality: 4
Sam:
Salary: $68,000, Experience: 18 years direct, 10 other
related, Education: MBA; Leadership personality: 3.5
Mary:
Salary: $69,000, Experience: 15 years direct, 4 other
related, Education: Masters, plus certified HR Professional;
Leadership personality: 4.3
Lisa:
Salary: $62,000 Experience: 5 years direct, 6 other related,
Education: Doctor of Business Administration (DBA);
Leadership personality: 3.7
Create a multi-attribute decision analysis using Excel. What
are the weights that you would assign to the four criteria?
How do you convert the raw data into utility values? Which
criteria are “Less is better” and which are “more is better”?
When you have worked through this example, download this
Excel file and check your work: BUS520-Module 3 SLP
Practice.xlsx
Optional Reading
Check the following video on pivot table:
http://permalink.fliqz.com/aspx/permalink.aspx?at=d4fd059eda8a46
a2b90041deffd6c061&a=5fae3cf0f1624f39b0341263a6541ea0
Check the Multi-Attribute Decision Making_BUS520 Module 3
Background PowerPoint presentation
Module 3 - SLP
PIVOT TABLE AND MULTI-ATTRIBUTE DECISION
ANALYSIS
Assumed Certainty: Multi-Attribute Decision Making
(MADM)
Scenario: You are the Vice President of Franchise Services
for the Lucky restaurant chain. You have been assigned the
task of evaluating the best location for a new Lucky
restaurant. The CFO has provided you with a template that
includes 6 criteria (attributes) that you are required to use in
your evaluation of 5 recommended locations. Following are
the 6 criteria that you will use to evaluate this decision:
Traffic counts (avg. thousands/day)—the more traffic, the
more customers, and the greater the potential sales.
Building lease and taxes (thousands $ per year)—the
lower the building lease and taxes, the better.
Size of building (square feet in thousands)—a larger
building is more preferable.
Parking spaces (max number of customers parking)—more
customer parking is preferable.
Insurance costs (thousands $ per year)—lower insurance
costs are preferable.
Ease of access (subjective evaluation from observation)—
you will need to “code” the subjective data. Use Excellent =
4, Good = 3, Fair = 2, and Poor = 1.
Now that you have collected the data from various sources
(your CFO and COO, local real estate listings, personal
observation, etc.), you have all the data you need to
complete an analysis for choosing the best location.
Download the raw data for the 5 locations in this Word
document: BUS520 Module 3 SLP.docx
Assignment
Review the information and data regarding the different
alternatives for a new restaurant location. Then do the
following in Excel:
Table 1: Develop an MADM table with the raw data.
Table 2: Convert the raw data to utilities (scaled on 0 to 1).
Show the utility weights in a second table.
Table 3: Develop a third table with even weights (16.7%) for
each variable.
Evaluate Table 3 for the best alternative.
Table 4: Complete a sensitivity analysis by assigning
weights to each variable.
In a Word document, do the following:
•
•
•
Discuss the process used to put together Tables 1–4 above.
Provide the rationale you used for choosing for each of the
weights you used in Table 4.
Give your recommendation of which location the company
should choose (based on results of Table 4).
SLP Assignment Expectations
Excel Analysis
Complete Excel analysis using MADM (all four tables noted
above must be included).
Accurate Excel analysis (Excel file includes working
formulas showing your calculations; all calculations and
results must be accurate).
Written Report
•
•
•
•
•
•
•
•
•
•
Length requirements: 2–3 pages minimum (not including Cover
and Reference pages). NOTE: You must submit 2–3 pages
of written discussion and analysis. This means that you should
avoid use of tables and charts as “space fillers.”
Provide a brief introduction to/background of the problem.
Discuss the steps you used to compile the Excel analysis (i.e.,
the four tables).
Discuss the assumptions used to assign weights to each
variable of your sensitivity analysis (Table 4). That is, provide
the rationale for your choice of weights for each variable.
Provide a complete and meaningful recommendation related to
the location that should be chosen as a new site.
Write clearly, simply, and logically. Use double-spaced, black
Verdana or Times Roman font in 12 pt. type size.
Have an introduction at the beginning to introduce the topics
and use keywords as headings to organize the report.
Avoid redundancy and general statements such as "All
organizations exist to make a profit." Make every sentence
count.
Paraphrase the facts using your own words and ideas,
employing quotes sparingly. Quotes, if absolutely necessary,
should rarely exceed five words.
Upload both your Excel file and written Word report to the SLP
3 Dropbox by the assignment due date.
Purchase answer to see full
attachment