Module 3 – Case

PIVOT TABLE AND MULTI-ATTRIBUTE DECISION ANALYSIS

Assignment Overview

You are the lead consultant for the Diligent Consulting Group. It is mid-October. One of your top clients, Sunshine Floor Barn, has just closed the books for the first three quarters of the year (January through September). Sunshine Floor Barn requests that you analyze the sales performance of its 5 product lines over this 3-quarter period. From past consulting work you have done for the company, you know that Sunshine Floor Barn has 4 regions and 18 total store locations. Each Regional Manager at the company has compiled the data for his/her region. The raw data provided consists of the sales revenue for each of the 5 premium flooring lines for all 4 regions and 18 locations for the first three quarters of the current year.

Case Assignment

The data have been provided in list format. Generate a Pivot Table Report with Charts. Use the Pivot Table and Charts to analyze the data. Following your in-depth analysis of the data, write a report to Sunshine Floor Barn in which you discuss and analyze the data, and make appropriate recommendations relative to how Sunshine Floor Barn should improve its sales performance going forward.

Assignment Expectations

Data: To begin, download the list data here: Data chart for BUS520 Case 3

Excel Analysis:

Provide accurate and complete Excel analysis (Pivot Table with Charts).

Written report:

• Length requirement: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must have 4–5 pages of written discussion and analysis. This means you should avoid use of tables and charts as “space fillers.”
• Provide a brief introduction to/background of the problem.
• Using the Pivot Table and Pivot Charts, discuss and analyze the data, noting key highs and lows, trends, etc.
• Include charts from your Pivot Table to support your written analysis. (Please do not use charts as “space fillers.” Instead, use them strategically to support your written analysis.)
• In a “Recommendations” section, give clear, specific, and meaningful recommendations that Sunshine Floor Barn should use to improve overall company sales.
• Be sure to consider highs, lows, and trends in the data. Which cities are the highest performers? Lowest? Which regions and quarter had the highest sales? Lowest sales? Consider what may be driving the numbers: Poor marketing? Outstanding marketing strategies? Inventory management? Seasonal sales? Other? There are innumerable possibilities. Your role is to reflect on the data, and ultimately, to use the data to give useful recommendations.
• Write clearly, simply, and logically. Use double-spaced, black Verdana or Times Roman font in 12 pt. type size.
• Have an introduction at the beginning to introduce the topics and use keywords as headings to organize the report.
• Avoid redundancy and general statements such as “All organizations exist to make a profit.” Make every sentence count.
• Paraphrase the facts using your own words and ideas, employing quotes sparingly. Quotes, if absolutely necessary, should rarely exceed five words.
• Upload both your written report and Excel file to the Case 3 Dropbox.

Rubric Name: MBA/MSHRM/MSL Case Grading Rubric -Timeliness v1

Criteria Level 4 – Excellent Level 3 – Proficient Level 2 – Developing Level 1 – Emerging Assignment-Driven Criteria 23 points Demonstrates mastery covering all key elements of the assignment in a substantive way. 20 points Demonstrates considerable proficiency covering all key elements of the assignment in a substantive way. 18 points Demonstrates partial proficiency covering all key elements of the assignment in a substantive way. 14 points Demonstrates limited or poor proficiency covering all key elements of the assignment in a substantive way. Critical Thinking 9 points Demonstrates mastery conceptualizing the problem. Multiple information sources, expert opinion, and assumptions are analyzed, synthesized, and critically evaluated. Logically consistent conclusions are presented with appropriate rationale. 8 points Demonstrates considerable proficiency conceptualizing the problem. Information sources and viewpoints of experts are proficiently analyzed and evaluated. Assumptions are clearly stated and supported, but may not be questioned. Conclusions are logical, but may be somewhat disconnected from the analysis. 7 points Demonstrates partial proficiency conceptualizing the problem. Information sources and viewpoints of experts are stated, but not necessarily synthesized, or critically evaluated. Assumptions are stated but not supported. Conclusions may be logical, but are not connected to or supported by the preceding analysis. 6 points Demonstrates limited or poor proficiency conceptualizing the problem. Information sources and viewpoints of experts are either absent or poorly analyzed, synthesized, and evaluated. Assumptions are implied, but not clearly stated. Conclusions are either absent or poorly conceived and unsupported. Business Writing 4 points Demonstrates mastery in written communication and a skilled, knowledgeable, and error-free presentation to an appropriately specialized audience. 3 points Demonstrates considerable proficiency in written communication with a well-organized presentation to an appropriately specialized audience. 2 points Demonstrate partial proficiency in written communication with few grammatical or syntax errors, but may lack headings or be pitched at the wrong audience. 1 point Demonstrates limited or poor ability to write clearly, and uses poor grammar and syntax. Text may be disorganized and rambling. Effective Use of Information 6 points Demonstrates mastery in locating relevant and quality sources of information, using strong and compelling content to support ideas, convey understanding of the topic, and shape the whole work. 5 points Demonstrates considerable proficiency in retrieving information, and in using appropriate and relevant content to support ideas, and convey understanding of the topic. Few arguments left unsupported. 4 points Demonstrates partial proficiency to retrieve information, but may not be able to discriminate quality. Uses relevant content to partially support ideas, but leaves many arguments unsupported. May use immaterial or disparate content in an attempt to support arguments. 3 points Demonstrates inability to retrieve information, or use appropriate or relevant content to support ideas, convey understanding of the topic and shape the whole work. Makes unsupported arguments and assertions. Citing Sources 3 points Demonstrates mastery using in-text citations of sources, proper format for quotations, and correctly format full source information in the reference list using APA style (bibliography). 2 points Demonstrates considerable proficiency using of in-text citations of sources, proper format for quotations, and provides sufficient source information in the reference list, though not in APA format (bibliography). 1 point Demonstrates occasional use of in-text citations of sources and provides partial reference information, such as a URL or web link (bibliography). 0 points Demonstrates inability to cite sources or provide a reference list (bibliography). Timeliness 5 points Assignment submitted on time or collaborated with professor for an approved extension on due date. 3 points Assignment submitted 1-2 days after module due date. 2 points Assignment submitted 3-4 days after module due date. 0 points Assignment submitted 5 or more days after module due date. Overall Score Level 4 45 or more Level 3 40 or more Level 2 35 or more Level 1 0 or more

Module 3 – SLP

PIVOT TABLE AND MULTI-ATTRIBUTE DECISION ANALYSIS

Assumed Certainty: Multi-Attribute Decision Making (MADM)

Scenario: You are the Vice President of Franchise Services for the Lucky restaurant chain. You have been assigned the task of evaluating the best location for a new Lucky restaurant. The CFO has provided you with a template that includes 6 criteria (attributes) that you are required to use in your evaluation of 5 recommended locations. Following are the 6 criteria that you will use to evaluate this decision:

Traffic counts (avg. thousands/day)—the more traffic, the more customers, and the greater the potential sales.

Building lease and taxes (thousands $ per year)—the lower the building lease and taxes, the better.

Size of building (square feet in thousands)—a larger building is more preferable.

Parking spaces (max number of customers parking)—more customer parking is preferable.

Insurance costs (thousands $ per year)—lower insurance costs are preferable.

Ease of access (subjective evaluation from observation)—you will need to “code” the subjective data. Use Excellent = 4, Good = 3, Fair = 2, and Poor = 1.

Now that you have collected the data from various sources (your CFO and COO, local real estate listings, personal observation, etc.), you have all the data you need to complete an analysis for choosing the best location. Download the raw data for the 5 locations in this Word document: BUS520 Module 3 SLP.docx

Assignment

Review the information and data regarding the different alternatives for a new restaurant location. Then do the following in Excel:

Table 1: Develop an MADM table with the raw data.

Table 2: Convert the raw data to utilities (scaled on 0 to 1). Show the utility weights in a second table.

Table 3: Develop a third table with even weights (16.7%) for each variable.

Evaluate Table 3 for the best alternative.

Table 4: Complete a sensitivity analysis by assigning weights to each variable.

In a Word document, do the following:

• Discuss the process used to put together Tables 1–4 above.
• Provide the rationale you used for choosing for each of the weights you used in Table 4.
• Give your recommendation of which location the company should choose (based on results of Table 4).

SLP Assignment Expectations

Excel Analysis

Complete Excel analysis using MADM (all four tables noted above must be included).

Accurate Excel analysis (Excel file includes working formulas showing your calculations; all calculations and results must be accurate).

Written Report

• Length requirements: 2–3 pages minimum (not including Cover and Reference pages). NOTE: You must submit 2–3 pages of written discussion and analysis. This means that you should avoid use of tables and charts as “space fillers.”
• Provide a brief introduction to/background of the problem.
• Discuss the steps you used to compile the Excel analysis (i.e., the four tables).
• Discuss the assumptions used to assign weights to each variable of your sensitivity analysis (Table 4). That is, provide the rationale for your choice of weights for each variable.
• Provide a complete and meaningful recommendation related to the location that should be chosen as a new site.
• Write clearly, simply, and logically. Use double-spaced, black Verdana or Times Roman font in 12 pt. type size.
• Have an introduction at the beginning to introduce the topics and use keywords as headings to organize the report.
• Avoid redundancy and general statements such as “All organizations exist to make a profit.” Make every sentence count.
• Paraphrase the facts using your own words and ideas, employing quotes sparingly. Quotes, if absolutely necessary, should rarely exceed five words.
• Upload both your Excel file and written Word report to the SLP 3 Dropbox by the assignment due date.

Rubric Name: MBA/MSHRM/MSL SLP Grading Rubric – Timeliness v1

Criteria Level 4 – Excellent Level 3 – Proficient Level 2 – Developing Level 1 – Emerging Assignment-Driven Criteria 9 points Demonstrates mastery covering all key elements of the assignment in a substantive way. 8 points Demonstrates considerable proficiency covering all key elements of the assignment in a substantive way. 7 points Demonstrates partial proficiency covering all key elements of the assignment in a substantive way. 6 points Demonstrates limited or poor proficiency covering all key elements of the assignment in a substantive way. Critical Thinking 7 points Demonstrates mastery conceptualizing the problem. Multiple information sources, expert opinion, and assumptions are analyzed, synthesized, and critically evaluated. Logically consistent conclusions are presented with appropriate rationale. 6 points Demonstrates considerable proficiency conceptualizing the problem. Information sources and viewpoints of experts are proficiently analyzed and evaluated. Assumptions are clearly stated and supported, but may not be questioned. Conclusions are logical, but may be somewhat disconnected from the analysis. 5 points Demonstrates partial proficiency conceptualizing the problem. Information sources and viewpoints of experts are stated, but not necessarily synthesized, or critically evaluated. Assumptions are stated but not supported. Conclusions may be logical, but are not connected to or supported by the preceding analysis. 4 points Demonstrates limited or poor proficiency conceptualizing the problem. Information sources and viewpoints of experts are either absent or poorly analyzed, synthesized, and evaluated. Assumptions are implied, but not clearly stated. Conclusions are either absent or poorly conceived and unsupported. Business Writing 3 points Demonstrates mastery in written communication and a skilled, knowledgeable, and error-free presentation to an appropriately specialized audience. 2 points Demonstrates considerable proficiency in written communication with a well-organized presentation to an appropriately specialized audience. 1 point Demonstrates partial proficiency in written communication with few grammatical or syntax errors, but may lack headings or be pitched at the wrong audience. 0 points Demonstrates limited or poor ability to write clearly and uses poor grammar and syntax. Text may be disorganized and rambling. Effective Use of Information 5 points Demonstrates mastery in locating relevant and quality sources of information, using strong and compelling content to support ideas, convey understanding of the topic, and shape the whole work. 4 points Demonstrates considerable proficiency in retrieving information, and in using appropriate and relevant content to support ideas, and convey understanding of the topic. Few arguments left unsupported. 3 points Demonstrates partial proficiency to retrieve information, but may not be able to discriminate quality. Uses relevant content to partially support ideas, but leaves many arguments unsupported. May use immaterial or disparate content in an attempt to support arguments. 2 points Demonstrates inability to retrieve information, or use appropriate or relevant content to support ideas, convey understanding of the topic and shape the whole work. Makes unsupported arguments and assertions. Citing Sources 3 points Demonstrates mastery using in-text citations of sources, proper format for quotations, and correctly format full source information in the reference list using APA style (bibliography). 2 points Demonstrates considerable proficiency using in-text citations of sources, proper format for quotations, and provides sufficient source information in the reference list, though not in APA format (bibliography). 1 point Demonstrates occasional use of in-text citations of sources and provides partial reference information, such as a URL or web link (bibliography). 0 points Demonstrates inability to cite sources or provide a reference list (bibliography). Timeliness 3 points Assignment submitted on time or collaborated with professor for an approved extension on due date. 2 points Assignment submitted 1-2 days after module due date. 1 point Assignment submitted 3-4 days after module due date. 0 points Assignment submitted 5 or more days after module due date. Overall Score Level 4 27 or more Level 3 24 or more Level 2 21 or more Level 1 0 or more

Module 3 – Background

PIVOT TABLE AND MULTI-ATTRIBUTE DECISION ANALYSIS

Required Reading

One of the most useful tools in Excel that you can use for data analysis is the Pivot Table also called the Pivot Table Report. This is an interactive way to quickly summarize large amounts of data. You can query large amounts of data, do subtotaling, expand and collapse levels of data to focus your results, move rows to columns and vice versa (pivoting), and filter and sort the data.

In order to create a Pivot Table, you must first have a source of data organized in List format. It is easy to create an Excel table in list format with column labels in the first row. Each cell in subsequent rows should contain data appropriate to its column heading. There should not be any blank rows or columns within the data of interest. Excel uses your column labels for the field names in the report.

View a Pivot Table Report. Before you get into creating your own Pivot Table, use this link and download this Excel file that has a Pivot Table using an Excel table in list format. Pivot Table-Example.xlsx

Watch the videos that show you how to do Pivot Table:

https://support.office.com/en-us/article/Create-a-PivotTable-and-analyze-your-data-7810597d-0837-41f7-9699-5911aa282760?ui=en-US&rs=en-IN&ad=IN

PRACTICE: Now create your own Pivot Table using this Excel file that has the data in an organized manner.

• Download this Excel file: Pivot Table-Practice Data.xlsx
• Organize the data into a data table in list format.
• Create the Pivot Table (and the Pivot Chart).
• Go to Quiz Tab and answer the questions.
• Check your Pivot Table and answers by looking at the Solution Tab in the spreadsheet.
• Then you should be ready to do the Case 3 Assignment.

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 include 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. 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 C_i be the i^th criterion. We have five criteria and we number them from 1 to 5. So Price is C₁ and Color is C₅. Criterion C_i, i = 1 to n, and n =5.

Let A_j be the j^th alternative. We have three alternative and we number them from 1 to 3. A₁ is the Prexel, A₂ is the Criston, and A₃ is the Thrush. Alternative A_j, 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 v_ij, the value of the i^th criteria for the j^th alternative. Value v_ij, i = 1 to n, and j = 1 to m.

But we need to convert the values, v_ij to utilities, u_ij, 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, u_ij. 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 w_i denote the numerical weight for each of the i criterion. And we use this formula to insure the correct amount of total weight. Sum(w_i) = 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:

[C₂ and C₃], C₄, [C₁ and C₅].

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.

C₁ = 0.1

C₂ = 0.3

C₃ = 0.3

C₄ = 0.2

C₅ = 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

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.