Friday, November 29, 2013

Evaluating, ranking and deciding - The Analytic Hierarchy Process way

One thing that I keep on doing over and over again is evaluating something in order to reach a decision, so my guess is that it happens to you too. It may be that you opted for buying something instead of building it and so you need to select a supplier; or it might be that you need to choose one of several options: Or it might be that you can select 2 new team members out of 10 possible people. Or maybe you should build that new warehouse in concrete. Or is it wood better? Or maybe it should be prefabricated?
So what did I do? Well, I decided to set up a spreadsheet with an "evaluation form" that you can adapt and use as you need it! Interested? Please keep on reading then!

The Analytic Hierarchy Process

One way to go about it is to make a 2 level decision tree. For instance, you can have Technical, Commercial and Maintenance criteria that you find important and/or necessary in order to choose a supplier. For the Commercial criteria, the delivery date could be the single most important parameter for this particular project, but there could be a few other important things that you should consider, like the price and the supplier history with your company. Whatever the case, this kind of a 2 level decision tree is a particular case of an Analytic Hierarchy Process (you can explore it a bit more about it on Wikipedia, I find it always a good place to start).
Now, to set up a 2 level decision process based on the Analytic Hierarchy Process you can do like this:
  • Define your first level of general criteria (such as technical, commercial and maintenance) that we shall call criteria group.
  • For each identified important criteria group (such as technical, commercial and maintenance) you then detail it a bit more into parameters in such a way that you can evaluate it - the more objectively the better. As part of the Technical criteria, these could be things like "Material durability" or "Technology".
  • For each criteria group (like technical, commercial and maintenance), you then figure out the relative importance of each group. Is technical criteria worth 60% of the overall decision? 40% maybe?
  • For each single parameter (such as the delivery date, price or supplier history in the case of the "Commercial criteria"), figure out their relative importance too: how important is the delivery date? Is it about 60%?
  • The last thing to do is, for each option available, to compute how well they do for each parameter. For instance, supplier A can deliver by February 10. Is that OK for you? Maybe you can evaluate "Delivery date" for 50% then. Or is it about perfect? 100% seems fair in this case.
Ideally you should come up with evaluations parameters suchs as "Number of days to deliver" or "Delivery date" so you can objectively compare all options. It's transparent to compare "March 2, 2014" to "February 10 2014", all the meaning is there even after a couple of years after you're done with this project. But if you evaluate the parameter as "70%" and "85%" it might feel like it's transparent at the time, but after a couple of years anyone would be intrigued about the meaning of "70%" or "85%".
The reason for taking this approach that is clearly not perfect is just the simplicity of construction of the supporting spreadsheet. In real project decisions please use meaningful parameters and not percentages of some previous evaluation that is not on the spreadsheet.

How to work with this spreadsheet

When you're giving importance to the criteria groups you've selected, they must add up to 100%. When they don't, the "Overall score" line for the "Partial weight" column gets your attention by changing its background color to dark red.
See the image on the left? 50% + 40% + 20% adds up to 110%, so there must be something wrong there. Please check what's wrong and correct it.
The same applies to the partial weights within each criteria group. On the example on your right, if "Material durability" is worth 60% and "Technology" is worth 50% then they add up to 110% - so something must be wrong also there.
One other thing that you can adapt to your particular project is  how many point is a perfect score worth. For some situations, a possible score of 5 maybe perfect, while for others a perfect score of 10, 20 or 100 maybe be better. It's your choice, all you have to do is set the value you want for a perfect score on the highlighted cell on your left and let the spreadsheet compute the scores.
And finally, you must evaluate how a "compliance percentage" for each option and each parameter: for instance, Supplier A offers you perfect conditions regarding your "Material durability" criteria, so they got 100% there.
Finally you can see each option overall score and their respective rank so Supplier A, that has the highest score, gets ranked "1" and Supplier B "2". Pretty neat, hein?

And how to adapt it

If you need to add options all you need to do id to right click column F and choose "Insert 1 left" and then copy the entire column E to the new and empty column F. If everything works fine, all the formulas are correct and you can proceed to change things as need, like the option name from "Supplier A" to whatever suits you best. And the same goes for the criteria groups (such as "Technical criteria") and the criteria parameters (such as "Material durability"). If you make add criteria groups in the middle of the existing ones just like previously shown with the options, everything should work out just fine.

How to fine tune

In case you opt to go for explicit evaluation values instead of percentages, which is by far a better option than the one presented, the simplest way to tackle the problem is to set a linear scale defined by the worst and the best candidates. An example may make this a bit more clear. Suppose you're evaluating the delivery date and date the earliest date you got was "February 10 2014" and the latest "March 2, 2014". If you attribute 100% to the earliest date and 0% to the latest, then you can attribute a percentage between 0% and 100% to any date you get in between, right? The actual computation is a bit out of context for this blog as it involves the formula for a line defined by 2 points:

where your x-axis is time (where your dates lie) and the y-axis where your percentages lie (and y1=100% and y2=0%). Let me know if you're interested about it, I can send you a working example.
One other good option might be to set a possible minimum and maximum values for a parameter (let's say that the earlier date you'll consider is February 1 and the latest is February 28 2014. Then you can go about it as in the previous example.
Yet another option is to set mandatory parameters. For instance, you may not accept a supplier that doesn't supply your order within February 2014.
And your needs rule, each case is a case and you'll probably keep on fine tuning this spreadsheet forever. The point is that now you have something to support some of your project decisions n a more objective fashion.


And finally, you can view, use and download this and other documents from the download section of this blog or directly from this link. In this particular case, the document is on Google Spreadsheet but you can download it as an Excel spreadsheet if that works better for you. I hope this proves useful to you and please get back to my with any suggestions you may have.

Images from

No comments: