Skip to content
Shop | Excel tracker

Utility Analysis

Weighted decision matrix for up to 6 alternatives: weights from a paired comparison, must criteria, scores, rank, sensitivity test and a one page decision record.

Screenshot of the Utility Analysis workbook with example data
Example data from the workbook.

How it works

  1. Open 02 Setup. Describe the decision, name up to 6 alternatives, up to 10 criteria and the must criteria. Write down for every criterion what a score of 10 means.
  2. Compare the criteria in pairs in 03 Paired Comparison: 2 when the criterion in the row is more important, 1 when both are equal, 0 when the criterion in the column is more important. The sheet proposes weights.
  3. Enter the weights in 04 Scoring. Take the proposal or adjust it with the team. The check turns OK when the weights add up to 100 percent.
  4. Score every alternative on every criterion from 0 to 10 and answer the must criteria with Yes or No. Score criterion by criterion, not alternative by alternative.
  5. Read 05 Result: utility value, rank and the contribution of each criterion. Alternatives that fail a must criterion are excluded from the ranking.
  6. Test the result in 06 Sensitivity before you decide. Record the decision with reasons in 07 Decision.

Definitions used in the workbook

  • Utility value: Sum of weight times score over all criteria. With scores from 0 to 10 the best possible value is 10.00.
  • Must criterion: A condition without trade-off. An alternative that fails one is out, whatever its utility value.
  • Paired comparison: Each criterion is compared with each other one. The points of a row plus 1 for the criterion itself, divided by all points, give the proposed weight. The 1 keeps the least important criterion above zero.
  • Sensitivity: The weight of one criterion is changed while the other weights keep their proportions. If the first place changes with a small shift, the decision is not robust and needs a second look.

Sheets in the workbook

  1. Start
  2. Setup
  3. Paired Comparison
  4. Scoring
  5. Result
  6. Sensitivity
  7. Decision

Who it is for

Project managers, engineers and buyers who prepare a concept, supplier or site decision.

Cheaper in the pack

This workbook is part of the Project and Cost Tool Pack (EUR 129), which holds all 20 Excel tools of this topic area, and of the Excel Tools Complete Library with all 100 tools.

Newsletter

One Lean idea.
Every two weeks.