Prioritization Matrix with Microsoft Excel: Objectivity in Projects Selection

Prioritization Matrix- one of the seven Management and Planning tools- is a decision-making tool to select the best option based on weighted criteria. It is usually used in projects selection when resources are limited or disagreement amongst stakeholders may arise for which project should take precedence.

What a Prioritization Matrix Consists of

In the Matrix, Options (for instance, Projects) are listed in one column while Criteria are listed horizontally. Criteria elements are given weights relevant to each other. The easiest way to weigh criteria is by starting with 1 as the lowest score, then score other elements as multiples of it according to importance. For example, if “Cost-Saving” is given 3 points while “Competitive Edge” is given 1, then Cost-Saving is three times as important as Competitive Edge in the selection process.

The same goes with weighing Projects. Each project is weighed in terms of the degree it satisfies each criteria element. If project A scores 5 points for the “Cost-Saving” criterion while it scores 1 in “Linked to Strategy”, then project A results in high cost-saving but is not deemed linked to strategy much.

After that, project scores are calculated across all criteria, then summed up to produce the overall project score. The total score for each project determines its ranking relative to other projects. And the best project to initiate is the one with the highest total score.

Steps to Construct a Prioritization Matrix

Creating a Prioritization Matrix is a team collaborative effort, but it is easy to construct with Microsoft Excel. Following are the main steps to build a PM that selects the most feasible project from a list of A, B, C, and D projects taking into consideration each one’s effect on four criteria elements, viz., Linked to Strategy, Cost-Saving, Increased Customer Satisfaction, and Higher Competitive Edge.

1. Set up two matrices as shown in Figure.2. Each matrix consists of the list of projects as one column with the criteria listed row-wise.
2. In Matrix 1, below each criterion, populate the cells with the relative weight (importance). This is usually performed by a consensus method amongst the stakeholders. A common strategy to populate the weights is by assigning 1 to the least important criterion, then assigning multiples of 1 to others according to their relative importance.
3. Similarly, populate weights for each project according to its relative effect on each criterion.
4. To have similar effects of weights in both criteria and projects, ‘Normalize’ criteria weights and projects’ weights by converting the values to a range between 0 and 1. One method to do so is by dividing each weight by the sum of weights. Normalization is performed for the criteria and for each project weights.
5. In Matrix 2, calculate each project-criterion pair score at the intersecting cell by multiplying the project weight by the criterion weight.
6. Finally, sum all scores of each project to produce the total project score.
7. The project with the highest total score is the most feasible to implement.

Prioritization Matrix is an efficient and effective tool in any endeavor to select an option over many others. It helps reduce bias, achieve consensus, and resolve conflict amongst projects’ team members. With Microsoft Excel, you can build a PM with a few simple steps.

PS. Watch the video of detailed steps to build a Prioritization Matrix with MS Excel