Multiple-criteria decision making

Have you ever been unable to decide on a product or service because of many different criteria? For example, you need to choose between supplier A and supplier B, and you want to know which one is best for you, considering not only the price but also the payment terms, warranty, etc.

I want to share with you the spreadsheet template, which will help with that.

The file is easy to understand and consists of three steps:

  1. Define your criteria and the subject of the assessments

  2. Assess which criteria are most important to you.

  3. Assessment of subjects

Following all 3 steps will give you the final answer which product or service to choose.

Bear in mind that, for example, Step 2 may require evaluation by a broader committee.

Explanation of the file

Step 1 - Sheet "Define Criteria"

  1. Add your criteria from cell C5 to C19. You can add maximum 15 criteria.

  2. In column F (F5-F9) write down product name or vendor/service name.

Step 2 - Sheet "Rank Criteria"

All you have to do here is evaluate if criteria from the left-hand side are more, less, or equally important as criteria from the top.

2 - means criteria is more important

1 - is equally important

0 - is less important

In the above example:

  • Criteria PRICE is more important for me than PAYMENT TERM - so I put 2 in cell E5.

  • Criteria PAYMENT TERM is equally important to QUALITY - so I put 1 in cell F6.

  • Criteria INCOME TERMS is less important than WARRANTY - so I put 0 in cell H8.

Don't change anything in gray marked cells - there are formulas.

You only add/change in yellow cells.

Step 3 - Sheet "Assessment"

Provide a number between 1 and 6 for each criterion and each subject. Where 1 is the worst and 6 is the best score.

The price I received from the US supplier was the highest, so I put 1. The lowest cost was from China, so I put 6.

Sheet "Results"

Here we have Chart and 2 small tables. The table at the top shows the results. If you want to see only 2 or 3 suppliers on the chart, remove the x from the lower table.

As always, files don't use any macros/javascript or pivot tables.

There are 3 internal links to move between the tabs.

If you want to use it:

  • MS Excel downloads the files.

  • Google Sheets, click the link, and then in the top left corner, in the menu bar, click FILE -> MAKE A COPY.

Let me know what you think. Also, don't forget to contact me if you would like to adjust something in the templates.

Microsoft Excel