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.
Mendoza College of Business
University of Notre Dame
Copyright © 2010 David Hartvigsen
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.
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.