Ahp excel _ due in 5 hours

Sharfuk9t

2875_416592_IT515OnlineModule7AHP.pptx

Home >Information Systems homework help >Ahp excel _ due in 5 hours

IT 515 Decision Making for IT

Module 7: Analytic Hierarchy Process (AHP)

March 5, 19 & 26, 2018

December 2017

IT 515 AHP

The purpose of this module is to show you a quantitative method to support decision making when there are competing priorities. We will start with a simple example of buying hobby equipment, and the go into other examples such as choosing a graduate school. Expect to devote the equivalent of about 2.5 to 3 class periods for this module in addition to doing the homework.

We will solve these problems “long hand” using Excel so that you will understand how the basic algorithm works. You will need to defend the algorithm in plain English should you use it. That being said, there are a wide variety of commercial AHP applications which are much easier and efficient to use and can easily handle big problems.

This module is intended as an introduction to AHP. It is certainly possible to take other approaches and use other theoretical foundations. For this class, you are free to modify the approach taken here, as long as the modification is logically identical to the approach shown.

IT 515 AHP

THE BASIC PARADIGM

Suppose that I want to go to the grocery store to buy some fruit. I want to buy only one type, but I like apples, bananas, and grapes. I want to evaluate based on price and product quality. Which should I buy?

To me, in terms of price, apples are preferred to bananas, and bananas are preferred to grapes. In terms of product quality, grapes are preferred over bananas, and bananas are preferred over apples. Based on this information, which should I buy? According to one way of looking at this, apples and grapes have an overall tie.

But there is a more sophisticated way of supporting this decision. Suppose we add weighting factors. To me, quality might be three times more important than price, so I could incorporate a weighting factor of .75 to quality, and .25 to price. Weighting factors could also be attached to the comparisons within price and quality. For example, instead of saying that apples are preferred over bananas in terms of price, I might say that apples are “very strongly preferred” over bananas and that bananas are “weakly more preferred” over grapes.

IT 515 AHP

“Very strongly preferred” might get a weighting factor of 7, and “weakly more preferred” might get a weighting factor of 3.

Using weighting factors, we can sum up all of the computations involving weights for apples, bananas, and grapes pairwise comparisons and which ever fruit has the highest score would be the one to choose.

This methodology is called the Analytic Hierarchy Process, and with a spreadsheet like Excel to keep track of the accounting and other details it is a straightforward process to arrive at a recommended decision. The market also has many AHP software packages available.

IT 515 AHP

ANALYTIC HIERARACHY PROCESS

First Exercise

A neighbor asked me to help him buy a starter telescope. He has little knowledge of amateur astronomy, but he wants to get started at a reasonable price. I suggested three telescopes for him: a very portable 3” reflector, a 5” refractor, and an 8” reflector. The next two slides show what each instrument looks like. We need to have a discussion of which telescope to buy.

IT 515 AHP

3” reflector

5” refractor

IT 515 AHP

8” Reflector

IT 515 AHP

1. The goal is to decide which telescope to buy. We will build an AHP model to support the decision. The model will not be the sole factor in the decision, but it will hopefully provide significant insight.

2. The buyer decided that “cost” is 40% of the reason for buying a telescope, and “quality” factors represent 60% of the reason for buying a telescope.

IT 515 AHP

3. Here are the costs of each instrument.

3” reflector -- $100

5” refractor -- $650

8” reflector -- $400

This is sufficient information to start to build an AHP pairwise comparison table to determine the relative benefit in terms of cost for each telescope option. A table is started below. Row values are compared with column values.

IT 515 AHP

	3" Reflector	5" Refractor	8" Reflector
3" Reflector	1.000	6.50
5" Refractor		1.00
8" Dobsonian			1.00

Here’s how to read the table.

Compare the rows to the columns. The cell in the intersection will quantify how much more favorable the row value is compared to the column value. This represents performing all possible pairwise comparisons by the time we are done.

Examples. When comparing a 3” reflector to a 3” reflector, we are indifferent – it is a “wash.” So the favorability is 1.000.

A 3” reflector costs $100, and a 5” refractor costs $650. Therefore, a 3” reflector is 6.50 times as favorable as a 5” refractor in terms of cost.

IT 515 AHP

Go into Excel and finish building this table. Here is the first step.

Notice the 6.500 factor in cell D2. This means that a 3” reflector is 6.500 times as favorable as a 5” refractor in terms of cost; put the formula for $650 / $100 into D2. If the 3” is 6.500 times more favorable than the 5”, then the 5” is .154 (the inverse of 6.5) times as favorable as the 3” (cell C3).

There are two ways of calculating this.

Calculate $100 / $650, or .154. Place that formula into cell C3.

Notice that .154 is the reciprocal of 6.500. So put the formula for “1 / D2” into cell C3.

IT 515 AHP

For cell E2, put in the formula $400 / $100 to signify that the 3” is 4 times as favorable in terms of cost than the 8”.

For cell C4, put in the formula $100 / $400 to signify that the 8” is .250 as favorable as the 3” in terms of cost.

For cell E3, place in the formula $400 / $650 to signify that the 5” is .615 as favorable as the 8” in terms of cost.

For cell D4, place in the formula $650 / $400 to signify that the 8” is 1.625 as favorable as the 5” in terms of cost.

IT 515 AHP

Add rows 7 through 9 to the spreadsheet as below. C2 will be the same as C7, C3 the same as C8, C4 the same as C9, etc.

After rows 7 through 9 are finished, then add row 10. This row is the sum of the weighting factors for each telescope. For example, C10 = C7 + C8 + C9 = 1.404.

Ensure your table looks like this one. With the exception of the 1.000 values in the top half, all cells should be populated with formulas.

IT 515 AHP

Complete this table by adding rows 12 through 15. The “bottom line” is generating cells F12 through F15. This gives the average weighting factor for each row (telescope).

Cell C13 = C7 / C10. Of the 1.404 weight for the 3” column, 1 / 1.404, or .712 is the weight associated with the 3” column. Continue to calculate C13 though E15 in this way. Conclude that the average weight for the cost factor of a 3” is .712, of a 5” is .110, and .178 for the 8” (column F).

IT 515 AHP

4. There are trade-offs in terms of four characteristics of quality. We need to develop pair-wise comparisons in much the same way as with the cost.

Bigger telescopes can show finer detail.

Bigger telescope can show fainter objects.

Smaller telescopes are more portable (easier to carry).

Reflectors have a hard time staying in focus, refractors almost always stay in focus.

IT 515 AHP

From the perspective of the buyer, below is the start of the quality pairwise comparison table. Other buyers could have different weights but we will build this model for this buyer.

Read this table as you read the previous table. For example, “Sees Fine Detail” has twice the benefit to this buyer as “Sees Faint Objects,” and “Sees Fine Detail” is 3 times as important to the buyer as “Portable” (the portability of the telescope).

Enter this table onto you Excel spreadsheet and complete it. The completed section of the Quality table is on the next slide.

IT 515 AHP

Now finish the table in the same way as the Cost table. The completed table is on the next slide.

IT 515 AHP

Please go into Canvas and download the spreadsheet “AHP Telescope Example.” This spreadsheet has the final layout and suggested formulas in each cell (you may use different formulas as long as the answer and logic is the same as here). It will show you better where we are going with this and give you at least one idea of how to lay out your spreadsheet.

IT 515 AHP

This is a very small copy of the “AHP Telescope Example” Excel file. The two gray areas on the left are the Quality and Cost tables. We now need to attend to the blue sections in the center.

IT 515 AHP

There are four Quality factors (“See Fine Detail,” etc.). We need to build pairwise comparison tables for each. Each is built in the same way aa the Cost and Quality tables. On the right is the completed “See Fine Detail” table. From the previous slide , this is the first table in the blue area. The next three are presented starting on the next slide. The values in the top table are provided by the buyer (i.e., are given).

IT 515 AHP

Complete each of these tables for the blue section of the spreadsheet. If you need help with the formulas or something else, consult the downloaded spreadsheet.

IT 515 AHP

We now need to recap the scoring for each telescope. The telescope having the largest score is the best choice according to the model. Please refer to the area around cell AK5 in the AHP Telescope Example spreadsheet.

Consider the cost row. The cost weighting factor for the 3” was .712, and the buyer said that cost was 40% of the reason for buying the telescope (quality was 60% of the reason). Multiple .712 by .400 to get .285. This is the total weighting score for 3” cost. Do not enter these factors “by hand;” make sure that they are formulas. For example, the AHP Telescope Example spreadsheet has = +H45 as the formula in the .712 cell. The .712 is a rounded number, so do not enter it to just 3 significant figures. Use all of the Excel capability and put the formula in to capture all of Excel’s significant figures.

IT 515 AHP

For “See Fine Detail” enter the cell address for .125 below the .712 (P18). This is from the blue table “See Fine Detail” and is the “Avg. of Row” for the 3”. You may want to check the “AHP Telescope Example” for the formula in that spreadsheet. The .125 for “Sees Faint Objects” comes from the second blue area in the corresponding cell. The .745 comes from the third blue section, same corresponding cell. And the .159 is from the fourth blue section, same corresponding cell.

IT 515 AHP

The .417 comes from the Quality table (top of gray section) and is the average of row for “See Fine Detail.” The .331 is also from the same Quality table, the average of rows for “See Faint Objects.” The .147 is from the same table, the average of rows for “Portable,” and the .105 is from the same table, average of rows “Stay in Focus.”

Multiplying .125 * .417 * .6 yields .031, which is the total weighted score for “See Fine Detail.” The total weighted average score for “Sees Faint Objects” is .125 * .331 * .6, or .025. The .066 and .010 are calculated in the same way. Add all of these up, and the total score for the 3” is .417.

IT 515 AHP

The scoring for the 5” and 8” are similar. Both use the .4 factor for cost and the .6 factor for quality. The .417, .331, .147, and .105 also stay the same. The multiplications and total cell calculations stay the same. What does change are the values in the first column, but they come from the same average of row area of the corresponding blue sections. Refer to the AHP Telescope Example spreadsheet for details as needed.

IT 515 AHP

The total score for the 3” is .417; the total for the 5” is .291 and the total for the 8” is .366. The 3” scores the highest of the three, so the model supports a decision to buy the 3”.

The model does not make the decision – it makes a recommendation. In practice, the decision maker may have confidential information that the modeler is not aware of, or the decision maker might choose a different solution for some other reason. The model supports the decision, but does not make it.

Another feature of the model is the “repeatable process.” Given the weighting factors provided by the buyer of the telescope, all analysts will arrive at the same numbers and conclusion. This may eliminate the need for forming a committee to propose the best answer, or using the Delphi technique; anyone seeing the original weighting factors and other original data will agree on the same conclusion.

IT 515 AHP

AHP Example 2

I am considering four Universities for grad school. There are four criteria I am looking at, as I can get into each based on grades, etc. The criteria are cost, driving time, whether the school has an advanced accreditation, and intangibles which I will call “campus environment.”

The goal is to decide which University to attend.

I believe that “cost” in itself has weight of is 30% and “quality” has a weight of 70%.

IT 515 AHP

Here are the cost comparisons for the Universities and the associated partial table.

A = $3000 per semester

B = $8000 per semester

C = $5000 per semester

D = $2000 per semester

IT 515 AHP

Here are the quality comparisons for the Universities. This is my perception, and is therefore “given” for this exercise.

IT 515 AHP

Here are the further comparisons for the Universities. This is my perception, and is therefore “given” for this exercise.

For Advanced Accreditation, only A has it. Compare “A to the others” as 1, 0 “others to A.”

IT 515 AHP

Please download from Canvas the Excel file “AHP Excel Files Second In-class Exercise.” Please use this as a reference/guide as you complete the AHP tables and exercise.

IT 515 AHP

Please complete the Quality Comparison table (as below) on your spreadsheet. Again, your formulas and mine may differ but they should be logically equivalent and provide the same answers. Again, with the exception of the top part of the table which is given, all other cells must have formulas or cell references in them.

IT 515 AHP

Please complete the blue section comparison tables on your spreadsheet. Again, your formulas and mine may differ but they should be logically equivalent and provide the same answers. Again, with the exception of the top part of the table which is given, all other cells must have formulas or cell references in them. The answers are below.

IT 515 AHP

Finally, complete the recap area. The University having greatest number, .685 for University A, will be the recommendation of the model. The answers are below.

IT 515 AHP

In the real world, if you use AHP you will want to buy an AHP software package. These are much easier to use than Excel, can handle much larger problems, and have numerous “canned” reports.

But when using such software, you will need to defend the basic algorithm to your “customer” – who might by your boss, a colleague, a Director, or a client. Do not portray the AHP software as a “black box.” Being able to defend the basic algorithm is one of the goals of this module.

It is likely that vendors will approach you to sell you AHP software or other kinds of quantitative method software from time to time in your career. Be sure that the vendor explains the underlying algorithms in the software to you. If the vendor can not do this, then you are not ready to buy it. I remember a vendor approaching me to sell me project management software, which assumed that the project team members waited until the “last minute” to start every task in the project. This is not reality and I did not buy the software, but I had to ask about the underlying algorithm to avoid the mistake of buying this.

EDITORIAL

IT 515 AHP

There are many different styles for building the Excel file. Here is one slightly different style, that you may use.

https://www.youtube.com/watch?v=syqZm1fAq8k

You should be ready to do the homework. Please refer to Chapter 4, Exercise 3, a and b only. Refer to the table in the center of page 77 for some of the weighting factors. 8 pts.

For exam purposes, you will not have to build an AHP model “from scratch.” You will be given a partly completed model and will have to fill in what is blanked.

After successful completion of the homework, please start the next Module, Module 6.