Excel template ahp analytic hierarchy process
Excel template ahp analytic hierarchy process
This article provides details of Excel template ahp analytic hierarchy process that you can download now.
Microsoft Excel software under a Windows environment is required to use these templates.
These Excel template ahp analytic hierarchy process work on all Excel versions since Excel 2013.
Example of a ready-to-use spreadsheet: Download this table in Excel format (.xls), and fill it in with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
The file to download contains Excel template ahp analytic hierarchy process entirely to your needs.
The AHP template works under 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).
The file to download presents four Excel spreadsheet templates for small business expenses :
- Tow Simple Excel template ahp analytic hierarchy process
- Advanced Excel template ahp analytic hierarchy process
Instructions:
Analytic Hierarchy Process, AHP
- On the "AHP Template" worksheet, select the number of criteria that you would like to rank (3 to 15)
- Enter the names of the criteria/requirements and a title for the analysis
- Work through the matrix comparing each of the criteria to each other (pairwise comparisons)
- For each comparison, decide which is the more important and select the appropriate weighting:
Note: You only need to complete half the matrix, the second half (reciprocal) of the matrix is automatically completed
- Once completed the the AHP column contains the priorities for the given criteria
- Check the consistency index which should be 10% or less
- Ahp matrix template
Plug the data of the converted score (Geometric Mean) into the AHP Matrix Template. An automatic data will be generated into the columns of the Normalized Score Table. You have to just look at the last column of the Normalized Score table called Percent(%) Ratio Scale Of Priority of the Customer or the Customer Preference. A Histogram can be then made starting with the Least % ratio scale of priority to the maximum % ratio scale of priority. The Least score value will indicate that which problem is to be attacked first (as ranked lowest by the customer). Alternately we can draw a Pareto chart by first subtracting each % Ratio Scale Of Priority Score from 100% and after subtracting the greatest value of % Ratio Scale Of Priority will become the least value. To Draw a Pareto Chart we can arrange the subtracted values from greatest to the least and plot the pareto Histogram. The Maximum value as per the pareto chart is the CTQ to be most attended first and ultimately improve our internal and external processes to increase the Customer's Value entitlement
The Analytic Hierarchy Process (AHP), introduced by Thomas Saaty (1980), is an effective tool for dealing with complex decision making, and may aid the decision maker to set priorities and make the best decision. By reducing complex decisions to a series of pairwise comparisons, and then synthesizing the results, the AHP helps to capture both subjective and objective aspects of a decision. In addition, the AHP incorporates a useful technique for checking the consistency of the decision maker’s evaluations, thus reducing the bias in the decision making process.
How the AHP works
The AHP considers a set of evaluation criteria, and a set of alternative options among which the best decision is to be made. It is important to note that, since some of the criteria could be contrasting, it is not true in general that the best option is the one which optimizes each single criterion, rather the one which achieves the most suitable trade-off among the different criteria. The AHP generates a weight for each evaluation criterion according to the decision maker’s pairwise comparisons of the criteria. The higher the weight, the more important the corresponding criterion. Next, for a fixed criterion, the AHP assigns a score to each option according to the decision maker’s pairwise comparisons of the options based on that criterion. The higher the score, the better the performance of the option with respect to the considered criterion. Finally, the AHP combines the criteria weights and the options scores, thus determining a global score for each option, and a consequent ranking. The global score for a given option is a weighted sum of the scores it obtained with respect to all the criteria.
Features of the AHP
The AHP is a very flexible and powerful tool because the scores, and therefore the final ranking, are obtained on the basis of the pairwise relative evaluations of both the criteria and the options provided by the user. The computations made by the AHP are always guided by the decision maker’s experience, and the AHP can thus be considered as a tool that is able to translate the evaluations (both qualitative and quantitative) made by the decision maker into a multicriteria ranking. In addition, the AHP is simple because there is no need of building a complex expert system with the decision maker’s knowledge embedded in it. On the other hand, the AHP may require a large number of evaluations by the user, especially for problems with many criteria and options. Although every single evaluation is very simple, since it only requires the decision maker to express how two options or criteria compare to each other, the load of the evaluation task may become unreasonable. In fact the number of pairwise comparisons grows quadratically with the number of criteria and options. For instance, when comparing 10 alternatives on 4 criteria, 4·3/2=6 comparisons are requested to build the weight vector, and 4·(10·9/2)=180 pairwise comparisons are needed to build the score matrix. However, in order to reduce the decision maker’s workload the AHP can be completely or partially automated by specifying suitable thresholds for automatically deciding some pairwise comparisons.