Updated AHP Excel Template Version 12.8.2013

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.

New AHP Excel template with multiple inputs

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.

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

Download

Download AHP Excel Template Version 2018.09.15 (zip)

Please consider a donation, it will help me to maintain the website and program. At least rate the template from 1 star (poor) to 5 stars (excellent) below. 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!

Incoming search terms:

  • Ahp excel
  • ahp calculations in excel
  • bpmsg com
  • content
  • ahp excel template
  • AHP tool template

My AHP Video got more than 10000 viewers

Thanks to all visitors and viewers of my AHP video. Views exceeded 10000, quite a lot for such a special topic. I hope, you all enjoyed and could take something usefull out of it.

Here the Youtube link to this AHP video

Another practical application for ANP is in preparation: The Marketing Mix. Will still take some more time, as ANP is more complex than AHP.

More interesting things to come!

Currently I am working on the leadership topic, still trying to get more people to try out the personal inventory.

You will receive a full report free of charge when sending the file back to me. (bpmsg at bpmsg dot com)

For those, interested in nature: have a look at my sunbird video, its in HD and some of the shots are really nice in quality.

Analytic Hierarchy Process (AHP)

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.

An explanation of the method can be found in my video on Youtube.

An Excel sheet, doing the calculation for up to 10 criteria, can be found in the download area. Download AHP slides.

AHP can be applied in many fields:

  • Evaluation of product features,
  • Cost-Benefit Analysis,
  • Strategy development,
  • Selection of Key Performance Indicators,
  • 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.

Comments and feedback welcome!

AHP-ANP practical Application with Pros and Cons

The analytic hierarchy (AHP) and analytic network process (ANP) are two multi-criteria decision methods (MCDM), originally developed by Prof. Thomas L. Saaty.

ANP is a more general approach, based on the description of the problem by means of a network instead of a hierarchy as in AHP. On the other hand, ANP is also more complex in its application.

In my latest video presentation, pros and cons for both methods are shown, and a few tips for the practical application of AHP,  and setting up a network for ANP are listed.

Download the slides

Link to video in Youtube

Feel free to comment and feedback here!

Incoming search terms:

  • anp template network
  • pros and cons of ahp
  • pros and cons of ahp method
  • pros of the AHP approach
  • when to use anp instead of ahp

AHP Excel Template

NEW! BPMSG’s free AHP online software: For a quick evaluation of priorities use my AHP online calculator. If you need to handle a complete AHP hierarchy, try out my new AHP-OS System.

Download page of the AHP Excel Template:

Latest Version
Please download the latest version from here

The work is licensed under the Creative Commons Attribution-Noncommercial 3.0 Singapore License. For terms of use please see our user agreement and privacy policy.

Older Versions for tracking purpose: please contact the author.

Incoming search terms:

  • ahp software
  • analytic hierarchy process template
  • ahp template excel
  • ahp spreadsheet template
  • ahp software free download
  • Analytic Hierarchy process software

Slides of my Video Presentations

Here you can download the slides of my video presentations.

Video: #001 Business Performance Management Introduction

Video: #002 The basic equation: Cost per Order (CPO)
Slides: CPOPPC_part1a.pdf

Video: #003 The basic equation: Profit per Cost (PPC)
Slides: CPOPPC_part1b.pdf

Video: #004 Profit per Cost and Order Income
Slides: PPC_OI.pdf

Video: #005 Order Income Variations
Slides: BPMSG-RSK.pdf

Video: #006 Setting up a small Business
Slides: BPMSG_example.pdf

Video: #007 Order Income per Headcount Sales
Slides: BPMSG-OIHCS.pdf

Video: #008 Value Add and Local Content
Slides: BPMSG-VA.pdf

Video: #009 Fixed and Variable Operating Expenses

Video: #010 The Dashboard

Video: #011 Combined Performance Indicators
Slides: BPMSG-COMB.pdf

Video: #012 Analytic Hierachy Process
Slides: BPMSG-AHP.pdf

Video: #014 Diversity Index as Business KPI
Slides: BPMSG-Div.pdf