I need an excel spreadsheet for the scenario using madm model 2-3

Scenario: You are the VP of Franchise services for the Happy Buns Restaurant. You have been assigned the task of evaluation the best location for the next HB that a prospective franchisee has suggested in the Columbus, Ohio, area. You are using the standard template that provides for which criteria (attributes) you should evaluate. But the specific weights for these are open to adjustment depending on the specific area. These are the six criteria that you will use to evaluate this decision.

·        Close to drive through traffic – traffic counts (avg. thousands/day)

·        Property cost/investment and taxes = NPV of investment ($$)

·        Size of building (square feet in thousands)

·        Size of parking (max number of customers parking)

·        Insurance costs (thousands $ per year)

·        Ease of access from streets (subjective evaluation from observation)

There are five possible locations. You have collected the data from various sources including your VP Finance, Real estate agents, etc. This document summarizes the raw data for each of the five locations: Abberton, Bellview, Casstown, Denton, and Eddington, all suburbs of Columbus. See Data Below.

Assignment

Review the information and data regarding the different alternatives for restaurant location. Develop a MADM table with the raw data. Convert the raw data to utilities (scaled on 0 to 1). Determine the relative weights of each criteria. Evaluate the Decision Table for the best alternative. Do a sensitivity analysis.

Write a report to your boss, Executive VP. Explain your analysis and your recommendation. Provide a rationale for your decision including the logic you used to determine your weights.

Data

Download this Word doc with the data: Happy Buns Raw Data.docx

Summary of Raw Data

for location of Happy Buns

in the Columbus, Ohio, area.

 

 

     Criteria

 

 

Location

Traffic count (avg. thousands/day)

NPV of investment

($000,000)

Bldg. size (sq ft. 000)

Lot size

(Max customer parking)

Insurance

($000 / yr)

Access

(subjective)

Abberton

17

1.3

3.0

44

5.2

Good

Bellview

10

2.1

3.8

54

5.6

Excellent

Casstown

11

1.5

2.6

65

5.0

Fair

Denton

20

3.0

3.6

52

6.4

Poor

Eddington

15

2.8

4.2

50

6.3

Good


written report and Excel file 

SLP Assignment Expectations

Analysis

·        Accurate, complete analysis (in Excel and Word) using the MADM model and theory.

Written Report

·        Length requirements = 2–3 pages minimum (not including Cover and Reference pages)

·        Provide a brief introduction/ background of the problem.

·        Complete and accurate Excel analysis.

·        Written analysis that supports Excel analysis, and provides thorough discussion of assumptions, rationale, and logic used.

 

·        Complete, meaningful, and accurate recommendation(s).

 

MADM Model and Theory

Multi-Attribute Decision Making (MADM)

This decision method assumes certainty. In other words, there are no probabilities of future states to determine. And the data and costs are assumed to be known and accurate. The most common type of decision is a preference decision. The decision maker wants to determine which of several options is the best to achieve some set of goals or fulfill a set of criteria or attributes. Common examples are: deciding which car to buy, which house to buy, which apartment to rent, where to go on vacation, which machine to buy for production, which supplier to use, and many more.

The decision process consists of the Decision Maker (DM) identifying the need for some object (or person) or concept that he/she currently does not have. Or it could be to replace some object that has outlived its usefulness, such as replacing a copying machine.

The decision consists of determining a set of criteria that the object must have or meet with some level of satisfaction. For example, when buying a car, the DM might consider its price, color, fuel efficiency, safety rating, warranty, comfort/ride, among other factors. This process is important because it provides and defines the performance and outputs that the user will expect.

The step for this decision is to search for and find the choices (alternatives or options) to be considered. There may be one criteria that is used as a filter, such as price. In the car buying example, the DM may have a price range that fits into his/her budget. They may also have a preference of Make, such as Chevrolet or Ford. But this second preference may actually be a bias and could limit the choices and exclude some viable choices. The search for alternatives usually generates choices in a serial manner. Specific alternatives are identified one at a time. Although it is possible to find several choices at nearly the same time, for example, being shown several different makes and models of cars at one dealership during a single trip.

The DM now has identified the choice options as well as the criteria to be fulfilled. Each alternative will fulfill each criterion at some level of value. The DM must collect this data and put it into a table for easy analysis. Here is an example of a decision table for purchasing a car.

 

Price

Fuel Effic. (MPG)

Safety Rating

Comfort/Ride

Color

Prexel

$22,000

32

8.5

6.7

Red

Criston

$25,000

38

8.2

7.9

Black

Thrush

$27,000

35

9.6

9.2

Blue

Note that the names are fictitious. The safety ratings and comfort/ride ratings could easily be obtained from a car buyer magazine. Price and MPG are from the dealerships. We are using only 3 options and 4 criteria for example purposes. The colors are those of cars that are in stock. You could order a car of your preferred color, but you do not want to wait 6 – 8 weeks for delivery.

As you look at this table, you will see that each criterion is measured differently than the others. How do you compare price with MPG, with Safety rating, and with color? To do this, you need a common metric and one that each criterion’s value can be converted easily. This metric is Utility, which is scaled between 0 and 1. Utility of 0 has no or minimal value and utility of 1 is the maximum.

Let’s take a minute to get rigorous in our model and use some shorthand notation.

Let Ci be the ith criterion. We have five criteria and we number them from 1 to 5. So Price is C1 and Color is C5.  Criterion Ci, i = 1 to n, and n =5.

Let Aj be the jth alternative. We have three alternative and we number them from 1 to 3. A1 is the Prexel, A2 is the Criston, and A3 is the Thrush.  Alternative Aj, j = 1 to m, and m = 3.

In the table we have the values of each alternative j for each criteria i and we term this vij, the value of the ith criteria for the jth alternative. Value vij, i = 1 to n, and j = 1 to m.

But we need to convert the values, vij to utilities, uij, so that they are all measured on the same metric. This will allow us to compare alternatives.

To convert from raw values to utilities can be done in several different ways. The easiest way is to use a linear transformation. Take fuel efficiency. There is 38, 35, and 32. The maximum value is assigned a Utility value of 1 and the minimum value is assigned a Utility value of 0. The intermediate values are assigned a proportionate level using a linear translation. So Utility of 38, or U(38) = 1 and U(32) = 0. Or in general, U(Max value) = 1, and U(Min value) = 0. But what about U(35) = ??

To find U(35), use a translation formula:  U(X) = (X – Min value) / (Max value – Min value)

In our example,  U(35) = (35 – 32) / (38 – 32)   =   3 / 6  = 0.5

This formula for U(X) is for a criterion when More is Better. You can use this to convert the Safety Rating and the Comfort/Ride rating, because More is Better.

But for a criterion where Less is Better, like Price, you need to use this formula:

U(Max value) = 0, and U(Min value) = 1. (remember, Less is Better).

U(X) = (Max value – X) / (Max value – Min Value).

In our example, on the Price criterion: U(27000) = 0, U(22000) = 1,

and U(25000) = (27 – 25) / (27 – 22) = 0.4

But what about criterion that are subjective or do not have any numeric raw values, like color? The utility scores are determined strictly by personal preference. Which of the three colors is most preferred and which is least preferred, and which are in the middle?

In our example, let’s say that Red is most preferred and Black is least preferred, making Blue with a medium level preference.  U(Red) = 1, U(Black) = 0, and U(Blue) = ?? Where does Blue fit on a scale of 0 to 1? This is a subjective rating. You can choose any score. In our example the DM prefers Blue to be 0.7, closer to Red than to Black.

Now we have our utilities, uij. Here is the decision table with utility scores.

 

Price

Fuel Effic. (MPG)

Safety Rating

Comfort/Ride

Color

Prexel

1

0

0.21

0

1

Criston

0.40

1

0

0.48

0

Thrush

0

0.50

1

1

0.70

 

There is one more step. Each criterion must be weighted according to its relative importance to the decision or the overall performance or results. These weights are decimals or percent and must total to 1.0. We will let wi denote the numerical weight for each of the i criterion. And we use this formula to insure the correct amount of total weight.  Sum(wi) = 1.0.

How does the DM determine these weights? This is subjective as well. The first step is to rank order the criteria with 1 most important and N as the least important (here N = 5). In our example, the DM thinks that Fuel Efficiency and Safety Rating are the most important, but not sure which is first. Then Comfort/Ride. Finally, the DM thinks that maybe price and color are last. The rank ordering is:

[C2 and C3], C4, [C1 and C5].

The DM decides to use the following weights, at least as a starting point. Then he/she can do some sensitivity analysis and adjust them a bit.

C1 = 0.1

C2 = 0.3

C3 = 0.3

C4 = 0.2

C5 = 0.1

Note that these weights total to 1.0.

The final step is to multiply the weights times each utility score for each alternative and sum these to get a total score for each alternative. Using our notation, the formula for Total Score, Tj , for the jth alternative is:

Tj  = Sum[(wi)(uij)], i = 1 to n (n = 5)

For example, the total score for the Prexel is calculated as: (0.1)(1) + (0.3)(0) + (0.3)(0.21) + (0.2)(0) + (0.1)(1) = 0.263.

This is the table with all alternatives and their Total Scores using the weights above.

Weight

10%

30%

30%

20%

10%

100%

 Criterion

Price

Fuel Effic. (MPG)

Safety Rating

Comfort/Ride

Color

Total Score

Prexel

1

0

0.21

0

1

0.263

Criston

0.4

1

0

0.48

0

0.436

Thrush

0

0.5

1

1

0.7

0.720

 

We should not be surprised that that Thrush is the preferred choice. It is scored the best on two of the criteria and 2nd best on two others. And is only scored the worst on one criteria which was given the lowest weight of 0.1

The Prexel scored best on two criteria, but these were the least important in weight and it also scored the worst on two criteria.

The question now to consider, “do these weights reflect the true preference and importance to the DM? If they were adjusted up or down by some degree, would it change the overall total score and hence the decision?”

This process is Sensitivity Analysis.

 

Watch the video that reviews this resource and discusses Sensitivity Analysis.

 

Academic Brigade
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.