Intro
to Ratings.xls
Given a small number of
alternatives, the spreadsheet Ratings.xls helps decision makers select the best
alternative. For example,
· Which job candidate to hire?
· Which software package to buy?
· Which location to select for a new store or factory?
· Which job to take?
In a nutshell, here is how
Ratings.xls works. To begin, you enter
the alternatives you’re considering.
(E.g., job candidates 1,2,3,4.)
Next, you enter the criteria that distinguish the alternatives and the
relative importance of these criteria.
(E.g., years experience (40%), quality of education (40%), interview
performance (20%).) Finally, you enter
measures of each alternative on each criterion within a specified range of
values. The spreadsheet then computes a
rating for each alternative; the one with the highest rating is your best
choice. The model is simple in
principle, but there are some subtleties.
(See some examples below followed by instructions for using the spreadsheet. See the spreadsheet for the formulas used.)
You can download Ratings.xls here.
Author:
Mendoza College of Business
University of Notre Dame
Copyright © 2010 David Hartvigsen
Examples:
How
to use Ratings.xls
Look at the Input sheet:
1. Enter the names of the
alternatives you’re considering.
2. Enter the names of your
criteria for rating the alternatives.
This list should address all your concerns (completeness), but be as
short as possible (nonredundancy).
3. In Step 5 you are going to
rate, for each criterion, all the alternatives (on quantitative scales). Before doing this, enter the possible range
of ratings for each criterion. Choose
the tightest range that would include ratings of all alternatives you might
reasonably consider (including, of course, the ones you are considering at
present). Specify if the high end of
this range corresponds to a good rating.
If a criterion is subjective, then you may specify any range you like (0
to 100 and 1 to 9 are commonly used).
4. For each criterion enter a
percentage representing the importance of the criterion (considering the range
specified) to your decision; the higher the percentage, the more important the
criterion. Do this so that the sum of
the percentages is 100%. A criterion at
60% should be interpreted as being three times as important as a criterion at
20%.
5. For each criterion, enter a
rating for each alternative. Make sure
that your ratings fall within the ranges you have set. Note: Suppose Criterion 1 is subjective on a
1 to 9 scale (where high is better than low).
Then a rating of 9 should correspond to the best possible rating of an
alternative you might reasonably consider and a rating of 1 should correspond to
the worst possible rating of an alternative you might reasonably consider
(although such alternatives need not be among those presently being
considered). Also, giving Alternative 1
a weight of 6 and Alternative 2 a weight of 2 means you consider Alternative 1
to be three times as good as Alternative 2 with respect to this criterion.
Look at the Ratings sheet:
The
highest rating indicates your preferred choice.
Note that all the input numbers have been converted, for consistency, to
numbers between 0 and 1; also note that 1 is now the best possible rating for
each criterion.
Editing:
Move and delete entries on
the Input sheet using the
Copy/Paste Special/Values and
Clear Contents commands (otherwise the cell references or formatting will get
messed up).
Insert additional rows and
columns using the Insert/Rows and Insert/Columns commands. You have to do this on all worksheets. When not on the Inputs worksheet, you have to
use Copy/Paste Special/Formulas to get the formulas into the new cells.
Delete rows and columns using
the Delete command, as usual.