Ratings.xls – David Hartvigsen

Given a small number of alternatives, the spreadsheet Ratings.xls can help a decision maker 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. Below you can download a few examples and find instructions for using the spreadsheet.

Examples:

Hiring example

Software example

Location example

How to use Ratings.xls

Look at the Input sheet:

Enter the names of the alternatives you’re considering.

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).

Below, 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).

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%.

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.