Transcription
How to do AHP analysis in ExcelKhwanruthai BUNRUAMKAEW (D3)Division of Spatial Information ScienceGraduate School of Life and Environmental SciencesUniversity of Tsukuba( March 1st, 2012)
The Analytical Hierarchy Process - AHP AHP is one of the multiple criteria decision-makingmethod that was originally developed by Prof. Thomas L. Saaty(1977). provides measures of judgement consistency derives priorities among criteria and alternatives simplifies preference ratings among decision criteriausing pair wise comparisons2
Using AHP1. Decompose the decision-making problem into ahierarchy2. Make pair wise comparisons and establishpriorities among the elements in the hierarchy3. Synthesise judgments (to obtain the set of overallor weights for achieving your goal)4. Evaluate and check the consistency of judgements3
The basic procedure is as follows:1. Develop the ratings for each decision alternativefor each criterion by developing a pair wise comparison matrix foreach criterion normalizing the resulting matrix averaging the values in each row to get thecorresponding rating calculating and checking the consistency ratio4
2. Develop the weights for the criteria by developing a pairwise comparison matrix foreach criterion normalizing the resulting matrix averaging the values in each row to get thecorresponding rating calculating and checking the consistency ratio3. Calculate the weighted average rating for eachdecision alternative. Choose the one with thehighest score.5
Structure the HierarchyDecompose the decision-making problem into a hierarchy of criteriaand alternatives.GOALGoalCriteriaFactor1Subfactor 11Criteria 11Subfactor 12Criteria 121CriteriaFactor 2 2Subfactor 13Criteria 13Alt 1Subfactor 21Criteria 21Subfactor 22Criteria 22Alt 2CriteriaFactor 3 3Subfactor 31Criteria 31Subfactor 32Criteria 32Subfactor 33Criteria 33Alt 3Level 1 is the goal of the analysis. Level 2 is multi-criteria that consist of severalcriterions, You can also add several other levels of sub-criteria. The last level is thealternative choices6
The first step in the AHP procedure is to make pair wise comparisonsbetween each criterion.The example scale for comparison (Saaty & Vargas, 1991).ScaleDegree of preference1Equal importance3Moderate importance of one factor over another5Strong or essential importance7Very strong importance9Extreme importance2,4,6,8Values for inverse comparisonResults of the comparison (for each factors pair) were described in term of integervalues from 1 (equal value) to 9 (extreme different) where higher number meansthe chosen factor is considered more important in greater degree than other factorbeing compared with.7
ExampleTable: Primary questionnaire design: effective criteria and pair wise comparisonFactor weighting scoreFactorMore importance thanEqualFactorLess importance 765432123456789C1Table: Pair wise comparison matrix which holds the preference valuesCriteriaC1C2C3C1145C20.2510.5C30.221 1/2If the criteria in the columnis preferred to the criteria inthe row, then the inverse ofthe rating is given.This table shows a simple comparison matrix of order 3 where 3 criteria C1, C2 andC3 are compared against each other.8
Consider the following example:Factor weighting scoreFactorEqualMore importance thanFactorLess importance 765432123456789C4C498765432123456789C5C5 Start with the total cost criterion and generate the following data in a 001.001.00How to fill up the upper triangular matrix is using the following rules:1.If the judgment value is on the left side of 1, we put the actual judgment value.2.If the judgment value is on the right side of 1, we put the reciprocal value.9
Making Comparison Matrix (How to make reciprocal matrix?)To fill the lower triangular matrix, we use the reciprocal values of the upperdiagonal. If aij is the element of row i column j of the matrix, then the lowerdiagonal is filled using this formula ABCDEF1FactorC1C2C3C4C5 1/D/D222C1 1/E/E223C2 1/74C3 1/3 1/0.14C4 1/1 1/0.20 1/1C5 1/1 1/0.20 1/1 1/C/C22 1/F/F22561.00 1/E/E33 01.001.00 1/11.00This slide shows how to analyze this pairedcomparisons10
Step 1: Pair wise comparisonThe criteria in the row is being compared to the criteria in the column.Pair wise 4825.006.144.204.2067 Sum (B2(B2:B6:B6) Thus now we have complete comparison matrix The next step is to normalize the matrix. This is done by totaling the numbersin each column.11
Step 2: NormalizationThis step is to normalize the matrix by totaling the numbers in each column.Each entry in the column is then divided by the column sum to yield itsnormalized score. The sum of each column is 4.204.20GH Sum 30.23121 (B66/B (B/B77) (C66/C (C/C77)Highest averagescoreNormalized inputs(priority vector) AVERAGE(G10/5)8 112
Step 3: Consistency analysisNow, calculate the consistency ratio and check its value. The purpose for doing this is to make sure that the original preferenceratings were consistent.There are 3 steps to arrive at the consistency ratio:1.Calculate the consistency measure.2.Calculate the consistency index (CI).CI lmax - n .n-13.Calculate the consistency ratio (CI/RI where RI is a random index).CR CI / RITo calculate the consistency measure, we can take advantage of Excel’smatrix multiplication function MMULT().13
Consistency Ratio 01.001.001.001.00CI 0.04RI 1.1216 MMULT(B2:F2,H10:H14)/H10RI is provided byAHP (see slide 16) MMULT(B3:F3,H10:H14)/H11 (AVERAGE(H10 (AVERAGE(H10:H:H1414))-5)/)/44C.Ratio0.04 I15 I15/I/I1616))CR CI / RI14
Approximation of the Consistency Index1. Multiply each column of the pair wise comparisonmatrix by the corresponding weight.2. Divide of sum of the row entries by the correspondingweight.3. Compute the average of the values from step 2, denoteit by lmax .4. The approximate CI lmax - n .n - 1 (AVERAGE(H10:H14)-5)/415
Consistency Ratio (CR)CR CI / RI In practice, a CR of 0.1 or below is considered acceptable. Any higher value at any level indicate that the judgements warrantre-examination.Consistency Index (CI) reflects the consistency of one’s judgementlmax - n .n - 1CI Random Index (RI) the CI of a randomly-generated pair wise comparison matrixnRI1230.00 0.00 0.5840.956789101.12 1.24 1.32 1.41 1.46 1.49Notes: n order of matrixRandom inconsistency indices for n 10 (Saaty, 1980)16
Summary With AHP, we can measure the degree of consistency; and ifunacceptable, we can revise pair wise comparisons. If we are perfectly consistent, then the consistency measureswill equal n and therefore, the CIs will be equal to zero and sowill the consistency ratio. If this ratio is very large (Saaty suggests 0.10), then we arenot consistent enough and the best thing to do is go back andrevise the comparisons. All of this work concludes the first step in the procedure. Thenext step is to use similar pair wise comparisons to determine theappropriate weights for each of the criteria. Now, continue for the other sub-criteria. You can easily do thisby copying this sheet into other sheets and then simply changingthe pair wise comparisons.17
RemarkBy now you have learned several introductory methods on Multi-CriteriaDecision Making (MCDM) from the advantage of Excel’s simple crosstabulation, using rank, and weighted score until AHP.Widely Used AHP Cost/Benefit Analysis Strategic planning R&D priority setting and selection Technology choice Investment priority Priority for developing tourism Evaluation of for new telecommunications services Other evaluation of alternatives18
The mathematics of AHP(1) Normalization: “Behind the scene”19
(2) Consistency analysis : “Behind the scene”Source: Haas, R. and Meixner, N.20
References KnowledgesSaaty, T.L. (1980). The analytic hierarchy process. McGraw-Hill, New York.Saaty, T.L.,Vargas, L.G. (1991). Prediction, Projection and Forecasting. Kluwer AcademicPublishers, Dordrecht, 251 pp.Haas, R. and Meixner, N. (n.d.). An Illustrated Guide to the Anlytic Hierarchy Process. Instituteof Marketing & Innovation, University of Natural Resources and Applied Life Sciences,Vienna [Available online] http://www.boku.ac.at/mi/ Multi-Attribute Decision Analysis Approach: Qualitative Approach Analytic Hierarchy Process(AHP) – Expert Choice Exercise www.satecs.com – Some words on the Analytic Hierarchy Process (AHP) and the providedArcGIS extension ‘ext ahp’ DECISION MODELING WITH MICROSOFT EXCEL: Multi-Objective Decision Making21
Table: Pair wise comparison matrix which holds the preference values Criteria C1 C2 C3 C1 1 4 5 If the criteria in the column is preferred to the criteria in 8 C2 0.25 1 0.5 C3 0.2 2 1 This table shows a simple comparison matrix of order 3 where 3 criteria C1, C2 and C3 are compared against each other. 1/2 the row, then the inverse of
