Tag: AHP

Welcome to BPMSG – Dec 2012

Dear Friends, dear Visitors,

yesterday I realized 10000 visits on my website since April 2012, when I implemented the Piwik web statistics. Over the last couple of months the daily visitor frequency was actually increasing, doubling within the last 3 months. On my youtube channel http://www.youtube.com/bpmsg I am now slowly reaching 100,000 video views.

So first to all of you a big thank you, showing interest in the topics of bpmsg.com, and especially to those of you,  giving me feedback, as I can learn and progress from there. For me it also means to stay committed and keep the content interesting and updated.

The topic with the highest interest is AHP – the analytic hierarchy process, and many of you downloaded my AHP excel template. Actually, here I would really like even more feedback about your applications, just to get an idea, in what other areas my template is used. Some of them, as I received, are:

  • Asset management prioritisation
  • BPMSG AHP template as a teaching tool
  • Weights of textual elements that affect difficulty of a given text
  • Environmental quality
  • Threads to biodiversity
  • Green supply chain

In my last update of the template  I improved the accuracy of calculation  significantly, so please always use the latest version, and revisit the site from time to time, to get the latest update. Alternatively you might subscribe to the bpmsg newsfeed; the link is given in the footer of the page.

My latest topic “Diversity index as business KPI – the concept of diversity” seems also to gain some interest. My video on youtube  got in a short time more viewers than the previous video about operational and strategic business performance. For me it was intersting to apply the diversity concept in business performance, as I haven’t seen this before, and the mathematical concept, to measure diversity of species in a habitat (biodiversity), is quite well established . I am thinking to publish a second video, showing more practical applications of the diversity concept in a business context.

After starting my youtube channel in 2009, I gained more and more experience in making videos. You can  clearly see the difference, comparing one of my older videos with the latest ones. Now my camcorder – a Canon XA10 – is with me most of the time on my business trips or vacations. Therefore you also find some video travel impressions on this web site under the topic “others”. My last trip was to the Philippines showing the nice island of Bohol, as well as one of the world’s largest crater lakes on a lake on an island – Lake Taal.

Klaus Goepel,
Singapore, Dec 2012

BPMSG stands for Business Performance Management Singapore. As of now, it is a non-commercial website, and information is shared for educational purposes. Please see licensing conditions and terms of use. Please give credit or a link to my site, if you use parts in your website or blog.

About the author

Updated AHP Excel Template Version 11.12.12

AHP IconDue to feedback from several users, I revised the implementation of the power method for the calculation of the Eigenvector and Eigenvalue to improve the accuracy of my AHP excel template. The calculation sheet ‘8×8 in the workbook was completely reworked. My tests show a significant increase in accuracy. As an example see my updated post AHP template – numerical accuracy.

By default the number of iterations is now set to 12.  The check value in sheet ‘8×8 cell B33 shows the sum of all matrix elements solving the Eigenvalue equation (A–I*λ) x = 0 with A the Decision matrix, λ = estimated principal Eigenvalue and x = estimated Eigenvector. The ideal check value is zero. With the example numbers given in the template the result is 5E-08.

Please let me know, if  you find any problems in the new version.

For the download of the latest version please go to the AHP template download page .

AHP template – numerical accuracy

Thanks to feedback from Mihail, here a few words about the numerical accuracy when using the AHP excel template.

AHP requires the calculation of the principal Eigenvalue, the weights are derived from the Eigenvector.  In my calculations I use the power method.  It is an iterative method, and  only one of several techniques that can be used to approximate the eigenvalues of a matrix.

Update 11.12.12

The whole calculation is shown in work sheet ’10×10′. I use 12 iterations; at the end of the sheet I do a check (the reverse calculation), using the Eigenvalue equation: (A–λ I) X = 0,  with A the AHP matrix; λ the principal Eigenvalue, and X the estimated Eigenvector. The resulting check value in cell B33 shows the sum of all matrix element of the Eigenvalue equation using the iterated Eigenvector and Eigenvalue. Ideally it should be zero.

Update 9.5.14

From version 2014-05-09 onward the template shows the convergence of the power method, when calculating the eigenvalue. In the summary sheet a threshold (squared Euclidean distance d2) can be set, to show how many iterations it takes, until the change of the approximated eigenvector is below the given threshold. By default the value is set to Thresh: 1E-07. As the number of iterations in the template is fixed to 12, care should be taken if the value reaches 12.

Examples

Here a practical example comparing the results from the power method, as now implemented in my template, with  an example (7 criteria) given by Saaty in Int. J. Services Sciences, Vol. 1, No. 1, 2008 (p 86, table 2). The AHP matrix is:

1 9 5 2 1 1  1/2
 1/9 1  1/3  1/9  1/9  1/9  1/9
 1/5 3 1  1/3  1/4  1/3  1/9
 1/2 9 3 1  1/2 1  1/3
1 9 4 2 1 2  1/2
1 9 3 1  1/2 1  1/3
2 9 9 3 2 3 1

The result according Saaty is
(0.177,  0.019, 0.042, 0.116, 0.190, 0.129, 0.327) with consistency ratio of 0,022

The result from my AHP Excel template is
(0.1775, 0.0191, 0.0418, 0.1164, 0.1896, 0.1288, 0.3268) with CR 0f 0.022
exactly the same. The check value in sheet ‘8×8 is 4E-12.

More examples

Latest Excel template download

 

 

 

AHP Consistency Ratio CR

Q: I read in some texts that a consistency ratio (actually inconsistency ratio) of less than 0.1 (10%) is good. I am not sure if your consistency ratio is a consistency ratio (i.e. the higher the percentage of the CR, the better and the more consistent the results are) vs inconsistency ratio (i.e. the consistency ratio percentage in your spreadsheet should be less in order to be more consistent).

Can you please let me know if a lower of higher percentage of the consistency ratio reflects a better more consistent response? Also, how important is the CR in the interpretation of results? If two consecutive rounds of solicited info yields very similar results, would that be acceptable even if the consistency ratio may not be good?

A: The CR in my spreadsheet is exactly the same you can find in the literature. A value less than 0.1 (10%) is good, but the threshold of 0.1 is a rule of thumb . Lower values are better than higher values, but values above 0.1 can be acceptable. It depends on the nature of your project. When you process the inputs from a group (several participants), it happens that individual CRs are above 10%, but the consolidated matrix CR is ok. Please read also my comment here.

How to use the AHP excel template in a project?

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.

New AHP Excel template with multiple inputs

The AHP Excel template works under Office Libre 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: 2022-07-08.

Alternative for complex AHP projects: AHP free online software.

Excel Template

  • 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.

Limitations

  • 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.

Reference

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

Download

Download AHP Excel Template Version 2022.07.08 (zip)

Please consider a donation, it will help me to maintain the website and program. An explanation of AHP (Analytic Hierarchy Process) is given in my video here. For terms of use please see our user agreement and privacy policy.

Your comments are always welcome!

BPMSG – One Year On-line

After being on-line with the website for 1 year, it’s time to change the entry page!

Here, on BPMSG.com you will find a couple of interesting articles related to business performance management, from a more practical than theoretical point of view.

Topics covered range from

A new topic was introduced just recently: “Value-driven leadership”. I started with a general article, and added a personality profiling tool (NEO-select). If interested, you might participate and send back the completed questionnaire to get a full report.

In the section other stuff  I post other interesting information, not fitting to the main topic of the site. For example a nice video of the olive-backed sunbird, a review of the Sennheiser Bluetooth Headset etc.

BPMSG stands for Business Performance Management Singapore. As of now, it is a non-commercial website, and information is shared for educational purposes. Please see licensing conditions and terms of use.

Please also be so kind, if you use parts in your web-site or blog, to give credit and a link to my site. I hope you will find some useful information, let me know your feedback or contact me directly for any suggestions.

Klaus Goepel, Mar 2012

about the author

;