Ramik, Jaroslav – Decision Analysis Module for Excel (DAME) – 2014

01 – DAME Tool – Group Decision Making and Evaluation Made Simple

Group evaluation techniques are many and group decision-making is always challenging. There are whole software packages today specializing only in just that like Expert Choice, for example. But what if you don’t want to shell out thousands of dollars and achieve a comparable result? Believe it or not, there is a way and it is rather simple and virtually free. DAME stands for Decision Analysis Module for Excel and it is quite a useful solution.

Group evaluation techniques are many and group decision-making is always challanging. There are whole software packages today specializing only in just that like Expert Choice, for example. But what if you don’t want to shell out thousands of dollars and achieve a comparable result? Believe it or not, there is a way and it is rather simple and virtually free. DAME stands for Decision Analysis Module for Excel and it is quite cool.

Take an example of Expert Choice. Within a space of two decades it grew from a niche decision making software application to a big group decision making tool. It is based on AHP (Analytical Hierarchy Process) originally developed in Pittsburgh by Prof. Thomas Saaty. It is good and you can certainly try it for free. However for “production” purposes it will cost you (or your company) thousands of dollars a year.

Of course, the calculations can all be done “by hand” but, truly, who would have time for that? You’re looking for a tool after all to save time.

This small university developed a simple but very useful Excel add-in that can do just that. Courtesy of Prof. Jaroslav Ramik who was behind the project it can be downloaded here. The Tool is called DAME (Decision Analysis Module for Excel) and this article is aiming at showing some of its very useful features. In other words by using it, you save not only time but money too.

So how does it work? The best start would be to consider a simple comparison of 4 products that was part of one of my previous articles. The products have the following prices:

A = 190B=230
C = 320D=290

It is the simplest evaluation task possible but even in this case hand calculations would still be quite time-consuming. You’re going to get exactly the same results (a final rank of evaluated alternatives) with DAME without using any calculations whatsoever in a matter of minutes.

All in all, DAME is an extremely neat solution.

It can be used for much more advanced tasks like requirements prioritization.

02 – Theory behind the AHP method (Analytic Hierarchy Process)

Analytic Hierarchy Process can be useful as a decision-support method in project management in instances where a few options (be it requirements, risks or other “alternatives” need to be prioritized or selected. Let’s take a look at some of the key theory concepts behind it.

There are two fundamental principles used in the general decision-making theory: that of deduction and that of induction (sometimes referred to as a system approach).  Deduction arrives at particulars from the general by applying logic. In other words it goes top-down, narrowing down more general truths into detailed conclusions.

The system approach on the other hand, is based on the premise that particulars are not as important. You are going bottom-up, as if trying to seize general truth.

The essence of the Analytic Hierarchy Process is based on employing both approaches. It is a method that first decomposes a complex problem in single components, and the components (sometimes referred to as variables), are put in a form of a hierarchy. They are then given numerical values. Each variable gets a value according to its importance in relation to other variables. (That is depends on whether it is quantitative or qualitative.) What follows is a synthesis of the values which will determine what weight each variable has in influencing the overall evaluation of the problem. Eventually all evaluated outcomes (alternatives/variants) will receive its total numerical value to form a ranking.

Many decision-making situations entail both physical as well as psychological aspect. Physical aspect can be regarded as objective, as it is from “tangible” realm, something that can be taken hold of or at least measured. Price as a criterion, for example can be quantified by money units, size or distance is quantifiable by units of measurement etc. On the other hand, the psychological aspect of the decision-making problem is more tricky. It is “intengible” in essence and there is no scale or range that might sufficiently, universally and unambiguously express it. They are often a product of subjective ideas, gut-feeling  or assumptions of an individual, a group or the whole society. Let’s take design qualities of a product as an example. The AHP deals with both of those aspects and is able to incorporate them as equal inputs of a unique decision-making support system.

Breaking down a seemingly complex problem into a clear hierarchy and only then focusing on different aspects of the decision, substantially expands possibilities of those who make the decision.

Analytic Hierarchy Process has been developed in Pittsburgh, USA in 1984, originally by Dr. Thomas L. Saaty – internationally recognized scholar and innovator of the decision-making theory.  It has since become one of the most successful and widely used decision support systems of today. It has grown into a comprehensive software tool used in collaborative, teamwork corporate decision-making.

Stages of AHP Using Expert Choice Software

As mentioned above, the largest contribution of AHP is its support in decision-making process by employing both subjective and objective factors when it comes to evaluating different alternatives of outcomes. Unlike in other methods, both quantitative data (that are clearly represented by numbers) and qualitative data (that often regarded as subjective) can be fed into the process. They are then assessed depending on importance that the decision maker has given to them but also in what layer of hierarchy they were placed. In a few steps, apparently advanced and complex decision-making problems can be solved in a relatively simple way.

The whole process can be divided into 5 stages:

1. Breaking down the problem into a hierarchy (analysis)
2. Evaluating criteria and decision alternatives on different levels of the hierarchy (setting priorities)
3. Measuring consistency of evaluation (finding consistency ratio)
4. Synthesis – generating overall weight for each evaluated decision alternative and their ranking
5. Sensitivity analysis

Breaking Down the Problem Into a Hierarchy (Analysis)

Decomposing the problem into a hierarchy is the first basic step of the Analytic Hierarchy Process. A hierarchy means a system of several levels, each including a finite number of elements. There is a mutual relationship between each two vertically-neighbouring levels. The higher the level is, the more general role it plays. Elements placed higher in the hierarchy controlled and managed by elements immediately underneath them. The element at the very top of the hierarchy is always the Goal of the decision-making process. The Goal has a weight that equals 1. 1 is than divided among the elements of the second level of the hierarchy, evaluation of elements in the second level of the hierarchy are then “dissolved” into the third level etc.

Hierarchy chosen depends on the character of the decision-making problem. There are a few types of the hierarchy:

  • Goal – Criteria – Alternatives
  • Goal – Criteria – Subcriteria – Alternatives
  • Goal – Experts – Criteria – Alternatives
  • Goal – Criteria – Intensity Levels – Multiple Alternatives

In most decision-making problems we will make do with the first mentioned type of hierarchy, that is Goal – Criteria – Alternatives but it can always be extended.

Evaluating Criteria and Decision Alternatives on Hierarchy Levels (Setting Priorities)

To set priorities for individual elements of the hierarchy, one must first know whether the data has a quantitative or qualitative nature. If quantitative, they may be ruled either by maximizing or minimizing. The maximizing rule will regard the highest value to be the best, while minimizing rule will regard the lowest value to be the best.

Evaluating by Quantitative Criteria

Price is a kind of a quantitative criterion that typically has a minimizing ruling when considered from the consumer’s point of view. The less it costs, the better. Let’s evaluate 4 products by price: A, B, C, and D, with the goal of assigning each one a numeric weight. The prices are:

Product            Price

A                190B 230
C                320D 290

Because the criterion is minimizing (lower values are considered better), the first step to calculate the weights is converting the values by using the following formula to get a coefficient kj:

kj=1Price100

This coefficient actually converts a minimizing criterion into a maximizing one (the higher the better because with price it’s the other way round):

Product kj
A 0.526
B 0.436
C 0.313
D 0.349

The resulting quantitative pj weights are calculated by a normalization formula:

\begin{equation}p_{j}=\frac{k_{j}}{\sum_k_{j}}\end{equation}

Product kj pj
A 0.526 0.324
B 0.436 0.268
C 0.313 0.193
D 0.349 0.215



Evaluating by Qualitative Criteria – Pairwise Comparisons

Pairwise comparisons belong to one of the most basic concepts of Analytical Hierarchy  Process. It is for evaluation of the criteria that are not clearly quantifiable but in the overall decision-making process play a crucial part. It is very difficult to assign weights to qualitative assessments by guessing and intuition, the AHP derives the information from comparing all the alternatives among themselves on every level of the hierarchy. In other words it slices the overall information into pairs of information. It is then used as a base for calculating numerical weights of each alternative.
Each pair of criteria being compared is assessed by 9-degree numerical scale that was developed specifically for this purpose:

Numeric Scale Description Explanation
1 Equal Both elements having same importance
3 Moderate Moderate importance of one over another
5 Strong Strong/essential importance of one over another
7 Very strong Very strong or demonstrated importance
9 Extreme Extreme importance of one over another

Besides the ones mentioned, there are also half-grades of 2, 4, 6, 8.

The relationship between elements in pairwise comparisons is called ‘importance’ of one over another but it can just as well be referred to as ‘preference’ or ‘likelihood’ of their occurrence. It always depends on the type of the problem being solved.

After pairwise comparisons of k-number of elements, pairwise comparison matrix (also known as Saaty’s matrix) is constructed. It is basically a reciprocal matrix consisting of k2 elements with 1’s on its diagonal and inverted values on each side. Typical pairwise comparison of 3 evaluated “Options” by a qualitative criterion can look as follows:

Option A Option B Option C
Option A 1 2 8
Option B ½ 1 6
Option C 1

For better clarity usually only the values in bold are shown. The number of those values can be calculated by the following formula:

n(n1)2

While AHP appears to be rather straightforward at first sight, the background mathwork needed for calculating numerical weights out of pairwise comparisons is not as straightforward. Eigenvalues and eigenvectors are involved and computer software such as Expert Choice does the hard work.

There is however an approximation method – an algorithm that can calculate rough weights in three steps without using a computer.

Algorithm for Calculating Approximate Weights

Step 1: Add up values in each column of pairwise comparison matrix

Option A Option B Option C
Option A 1 2 8
Option B ½ 1 6
Option C  ⅙ 1
Total 13/8 19/6 15


Step 2: Each item is divided by the total of its column thus getting a normalized matrix

Option A Option B Option C
Option A 8/13 12/19 8/15
Option B 4/13 6/19 6/15
Option C 1/13 1/19 1/15
Total 1 1 1



Step 3: Total of each row will be divided by the number of items in the row

Option A Option B Option C
Option A (8/13 + 12/19 + 8/15) / 3
Option B (4/13 + 6/19 + 6/15) / 3
Option C (1/13 + 1/19 + (1/15) / 3
Total 13/8 19/6 15

The calculated means will then serve as the approximate weights of each alternative (called Options here). From the numbers below we can see that Option A “won”.

Approximate weight

Option A        0.593

Option B        0.341

Option C        0.066

Total            1

Measuring Consistency of the Evaluation

Consistency of the measurements is way of expressing a certain ‘compactness’ of the preferences created during pairwise comparisons. It shows to what extent the data fed into the computer is logically cohesive. If for example, Alternative 1 is twice as important as Alternative 2 and Alternative 2 is three times as important as Alternative 3, then Alternative 1 must be 6 times (2 x3) as important as Alternative 3. This would be a case of a perfect consistency, in other words, the consistency coefficient would equal 0.

Inconsistency of judgements is at the background of human thinking. Humans don’t just use logic when drawing conclusions but rather stick to intuition, emotions, experience that all influence their attitudes and swing their decisions. If someone prefers apples to oranges and at the same time the person likes oranges better than bananas, shouldn’t it automatically be assumed that apples will be preferred over bananas? And yet the same people will still go for bananas rather than apples because there are other things to consider, like say, time of the day, season, etc. all eventually causing that they illogically, or ‘inconsistently’ choose this alternative and not the other.

In practical applications perfect consistency is rare because new and new information is constantly added in evaluation and it changes the previous relationships. Therefore pairwise comparisons permit a certain amount of inconsistency of preferences. The AHP works with the so called Consistency Ratio, with the rule of thumb that, if the inconsistency be more than 10 per cent, the evaluation should be revisited.

High inconsistency (bad consistency ratio) implies one of the following problems:

  • Ill logic in pairwise comparisons
  • Badly structured hierarchy
  • Errors/typos during data input

Expert Choice calculates consistency ratios automatically with each pairwise comparison. In case that the inconsistency is too high, it even has a feature that discloses the elements where inconsistency is highest. It could be repeated until inconsistency goes back to an acceptable level.

For illustration let’s look at how approximate inconsistency can be calculated without using a computer. The previous example is used:

Option A Option B Option C
Option A 1 2 8
Option B ½ 1 6
Option C 1

If Option A is preferred twice over Option B and Option B is preferred 6 times over Option C, then Option A should be preferred 12 times over Option C. That would be an ideal situation or perfectly consistent evaluation. However, because Option A is only 8 times more preferred than Option C, consistency ratio needs to be found.
Working out the approximate consistency ratio is dealt with in the next chapter.

Algorithm of Calculating Approximate Consistency Ratio

Step 1: Each column element of the original pairwise comparison matrix is multiplied by the resulting weight of their alternative and then rows are summarised:

Option A Option B Option C
(0.593) (0.341) (0.066)
Option A 1 2 8
Option B 0.5 1 6
Option C 0.125 0.167 1

will become

Option A Option B Option C
Option A 0.593 0.682 0.528
Option B 0.297 0.341 0.396
Option C 0.074 0.057 0.066

Resulting totals then are:

Option A        1.803

Option B        1.034

Option C         0.197

Step 2: Each resulting total is divided by its weight:

Option A        1.803 / 0.593 = 3.04

Option B        1.034 / 0.341 = 3.032

Option C         0.197 / 0.066 = 2.985

Step 3: Mean is calculated:
Lmax=3.04+3.032+2.9853=3.019

Step 4: Consistency Index CI=Lmaxnn1 is then calculated, where is number of elements being compared:

CI=3.01932=0.0095

Step 5: Consistency ratio is calculated using the so called Random Index – which is an average consistency index of a randomly generated n x n – size matrix:

CR=CIRI

Random Index (RI) doesn’t need to be calculated as it is already provided in the following chart:

n        RI
2        0
3        0.58
4        0.9
5        1.12
6        1.24
7        1.32
8        1.41

For n = 3 RI is 0.58 and the Consistency Ratio is:

CR=0.00950.58=0.016

The approximate Consistency Ratio of the three evaluated Options equals 0.016 and meets the previously stated expectation of CR < 0.1.

The evaluations are therefore considered to be sufficiently consistent.

Practical Application of Expert Choice

Analytical Hierarchy Process and Expert Choice has a wide application in real-life business-related situations. Generally, it has large usage in marketing in product comparisons but it can just as well be used to support decision-making or in planning, investing, conflict resolution, forecasting or risk management to name a few.
IBM used Expert Choice when applying for Malcolm Baldridge National Quality Award. General Motors used it in its design projects when evaluating prototypes of its new products. Xerox used it for the portfolio management, evaluating new technologies and as a support tool in marketing decisions. It has been used by government in rating of buildings by historic significance, or in assessing the condition of highways so the engineers could determine the optimum scope of the project and justify the budget to lawmakers.

In project management it can  be used in the scope management knowledge area, in estimating cost of work packages through control account level, then aggregating them into the overall project cost estimates.
It has a large usage in Human Resources in Acquire Team process to evaluate employees or potential team members from large number of applicants against the set of defined criteria. They are quickly rated and scored to select the ones that best suit the criteria.

Portfolio management is another ara of application where it helps decision-makers rate the business value of their potential projects. AOL project portfolio management can be an example.
AHP can also be used in risk management, identification and prioritization where both subjective inputs (qualitative risk analysis) and quantitative data (quantitative risk analysis) need to be assessed.

03 – Selecting Seller by Source Selection Criteria using DAME

In procurement management it is often necessary to evaluate sellers based on proposals they have sent. Evaluation is crucial for the award decision.

Proposal evaluation is an assessment of the proposal and the offeror’s ability to perform the prospective contract successfully. Here is a simplified example how this process can be done with a little known tool called DAME.

Let’s say you’re evaluating 4 Sellers according to 4 evaluation criteria, which are:

Bid ($) – money asked to carry out the job -> “MIN” (more about “minimizing” and “maximazing” here)

Past Performance – Excellent (2), Satisfactory (1.5), N/A (1), Bad (0.5) -> “MAX”

Know how possessed by sellersPairwise comparison (we’re comparing sellers against one another) -> PAIRWISE COMPARISON

Own resources available – Pairwise comparison (we’re comparing sellers against one another) -> PAIRWISE COMPARISON

There are 2 key authorities submitting their judgements. That means 2 “scenarios”. One of them will be the CEO and the other will be the PM (Project Manager).

Download the whole example from the link below.
Download
Selecting Seller by Source Selection Criteria using DAME

04 – Requirement Prioritisation using DAME

Sometimes little means more. This little-known and free Excel add-in can do exactly what many powerful propriatary software packages. DAME stands for Decision Analysis Module for Excel.

If you haven’t worked with DAME before, here is a short instruction manual.
Why don’t you make your life easier. Using this little-known free Excel add-in you can save thousands. DAME stands for Decision Analysis Module for Excel.

Let’s say you lead a development team and you prioritize the development features. Based on the Pareto principle you know that 80% of of effort will be spent on only 20% of features. In other words you need to spend the bulk of your money on one fifth of the most important features. You narrow the feature list down to only say 10 most important ones – those that are an absolute Must Have for the customer.  However you need a better picture when it comes to prioritizing those 10 key requirements.  Evaluations is being carried out by 5 experts including a customer representative. Their estimates must be synthesized into a final outcome to have a better idea how the budget should be shaped for the next ‘iteration’, sprint, run, or whatever agile terminology is used at your organisation.

One way of doing this could be by using DAME.

Let’s say, with a certain amount of simplification, that you already have a complete and up-to-date requirements breakdown list with say 12 categories of user requirements. The requirements are coded by: a number –  representing the category and a letter – standing for the actual requirement.  E.g. 1a, 4c, etc.

You know that the 10 most important requirements for the moment are:
1=> 1a – users able to record, view, edit info of all clients who have ever entered service
2=> 1b – records will be modeled on a used government reporting tool
3=> 3a – users can record clinic activity (prerequisite in applying for gov grants)
4=> 3b – clinic info will be categorized
5=> 5a – information is confidential (storing on secure server, only for the righ eyes)
6=> 6a – communications book ready with no editing allowed (24/7 shifts need to pass critical information)
7=> 8a – client exclusion list – a list of clients currently banned from service available from anywhere to everybody
8=> 10b – assigned staff will be able to edit and delete client information
9=> 11d – Client will have a special ID that matches the currently used government reporting tool
10=> 11f – One database will be shared by two physical facilities (on different physical addresses). Each organisation will only see data related to it.

Now let’s assume you think that those requirements should be seen as follows (1 = most important; 10 = least important):
Order; Requirement Code; Rank

1. 11d
2. 5a
3. 8a
4. 3a
5. 6a
6. 11f
7. 10b
8. 1a
9. 3b
10. 1b

That’s your evaluation. However there are 4 more stakeholders involved and they might see the whole thing differently.

This problem (when being resolved in DAME) calls for the following input:
10 variants (10 requirements being evaluated)
5 scenarios (5 decision-makers or evaluators)
1 criterion (highest likelyhood that the customer will be happy if a particular requirment is met by the end of the next development cycle; it is a kind of an expert judgement) The important thing here is that is is a minimizing criterion because (you rank the requirements by your judgement from 1 to 10 where 1 is most important and 10 is least important – that means – the less the better)

You decide that the weights (decision-making power) of the stakeholders (based on their position) is as follows:

Customer Rep = 50%
Project Mngr = 20%
Developer = 20%
Tester = 5%
Coordinator 5%

The results from the 5 decision-making parties are then synthesized into one final outcome for each requirement. This way you receive the final ranks for your ten most important requirements:

1. 11f = 0.206557377
2. 11d = 0.133911394
3. 10b = 0.133518493
4. 3b = 0.105744479
5. 5a = 0.093889717
6. 1a = 0.059748002
7. 8a = 0.056868988
8. 1b = 0.05641512
9. 6a = 0.053082238
10. 3a = 0.050264192

If your budget is 100,000yousplititagainsttheweightstoseehowmuchmoneyshouldgotowardsmeetingeachrequirment:1.11f=0.206557377>20,656
2. 11d = 0.133911394 -> 13,3913.10b=0.133518493>13,352
4. 3b = 0.105744479 -> 10,5745.5a=0.093889717>9,389
6. 1a = 0.059748002 -> 5,9757.8a=0.056868988>5,687
8. 1b = 0.05641512 -> 5,6429.6a=0.053082238>5,308
10. 3a = 0.050264192 -> $5,026

The above example is simplified of course for demonstration purposes only it is only to show that using tools such as DAME is elegant, quick and neat, and most importantly, it doesn’t cost anything.