Q: I’m very new to AHP and I want to use it to identify which country is the best location to offshore a certain function of a company for my MBA project. I need to find relative importance of different factors for such decision and the relative ranking of different ountries from those factors.
How do I use this excel for such purpose? Do I run it multiple times; first for finding the priority of the factors, and then for the comparison of the countries one by one for each of the factors? And lastly multiply the priorities of the factors by each country’s priority? Is there an easier way via your template to do it?
A: There is no easier way. My template only calculates the priorities of factors in one single category of a hierarchy. If you have different categories, you have to run it multiple times (once in each category/ sub-category); then calculate the final weighting factors and make the evaluation of alternatives in your own sheet. NEW since Dec 2013: You might also use my online tool BMPSG AHP hierarchy.
I cannot generalize my template, as the hierarchy could be very different from one to another project.
Version 12-08-13 of the template allows for decimal input figures in the pairwise comparison sheets. It is now possible to enter a decimal value between 1 and 9 instead of integers 1 to 9. This simplifies the combination of templates, in case you need to extend to more than 20 participants.
This latest version also handles the different judgment scales for non-integer inputs.
Kindly let me know in case you find any problem with this new version. Feedback is appreciated always! You can download this latestes version from my AHP template download page.
The AHP Excel template works under Windows OS and Excel version MS Excel 2013. The workbook consists of 20 input worksheets for pair-wise comparisons, a sheet for the consolidation of all judgments, a summary sheet to display the result, a sheet with reference tables (random index, limits for geometric consistency index GCI, judgment scales) and a sheet for solving the eigenvalue problem when using the eigenvector method (EVM). Latest version: 2018-09-15.
Within the input worksheets (questionnaires) priorities are calculated using the row geometric mean method (RGMM).
Three consistency indices (the consistency ratio CR, the geometric consistency index GCI and overall dissonance Psi) are calculated. The level of consistency needed (α) is implemented as a variable input field in the summary sheet, and can be set between zero and one.
If CR exceeds α, the top 3 inconsistent pair-wise comparisons on the input sheets are highlighted, to allow the participants an adjustment of their judgments. The judgment resulting in lower inconsistency is proposed.
Final priorities are shown in a summary sheet; their calculation is based on the eigen vector method (EVM). For the solution of the eigenvalue problem the power method algorithm is applied with a fixed number of 20 iterations.
Different judgment scales are implemented.
Errors of the EVM and RGMM are show beside the calculated priorities.
Either individual participants, or an aggregation of individual judgments (AIJ) based on the weighted geometric mean of all participants’ judgments can be selected.
The template does not include the hierarchy of the decision problem and the final aggregation of weights, i.e. it is only suitable for finding the weights in each category or sub-category. For the definition of a hierarchy and evaluation of alternatives see here.
Sensitivity analysis of the final result is not included.
How to use the template?
A detailed description (pdf) is attached in the download file.
When you use the template for your research, please make a reference to the author’s paper.
Please cite: Klaus D. Goepel, (2013). Implementing the Analytic Hierarchy
Process as a Standard Method for Multi-Criteria Decision Making In
Corporate Enterprises – A New AHP Excel Template with Multiple Inputs, Proceedings of the International Symposium on the Analytic Hierarchy Process, Kuala Lumpur 2013. DOI: https://doi.org/10.13033/isahp.y2013.047
AHP stands for Analytic Hierarchy Process. It is a multi-criteria decision making method, originally developed by Prof. Thomas L. Saaty. AHP derives ratio scales from paired comparisons of criteria, and allows for some small inconsistencies in judgments.
Inputs can be actual measurements, but also subjective opinions. As a result, ratio scales (weightings) and a consistency index will be calculated. For decision making with multiple inputs from different stakeholders, the geometric mean of individual inputs is used.
Mathematically the method is based on the solution of an Eigen value problem. The results of the pair-wise comparisons are arranged in a matrix. The first normalized Eigenvector of the matrix gives the ratio scale (weighting), the largest Eigenvalue determines the consistency ratio.
Deriving weights for a combined performance index,
Deriving a consolidated scale of importance from different inputs.
We used AHP for the evaluation of weights for the combined service index and the hotline performance index. We also applied the method to evaluate the importance of different strategies, using multiple inputs from different colleagues, and to find the ranking of financial key performance indicators for the operational performance assessment of our companies.