ASSUMED CERTAINTY: PIVOT TABLES AND MULTI-ATTRIBUTE DECISION MAKING

Subject: Business    / General Business
Question
Module 1 – Case

ASSUMED CERTAINTY: PIVOT TABLES AND MULTI-ATTRIBUTE DECISION MAKING

Pivot Tables and Pivot Charts

Assignment Overview

You are the lead consultant for the Excellent Consulting Group. It is mid-October. One of your top clients, Buddy’s Floor Barn, has just closed the books for the first three quarters of the year (January through September). Buddy’s 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 Buddy’s 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 Buddy’s Floor Barn in which you discuss and analyze the data, and make appropriate recommendations relative to how Buddy’s Floor Barn should improve its sales performance going forward.

Assignment Expectations

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

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 Buddy’s 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.

Module 1 – SLP

ASSUMED CERTAINTY: PIVOT TABLES AND MULTI-ATTRIBUTE DECISION MAKING

Assumed Certainty: Multi-Attribute Decision Making (MADM)

Scenario: You are the Vice President of Franchise Services for the Happy Buns restaurant chain. You have been assigned the task of evaluating the best location for a new Happy Buns 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 SLP1V1.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.

Upload both your Excel file and written Word report to the SLP 1 Dropbox by the assignment due date.

Module 2 – Case

RISK: FREQUENCY DISTRIBUTION, PROBABILITIES, AND EXPECTED VALUE

Risk: Frequency Distribution, Probabilities, and Expected Value

Assignment Overview

In the Module 2 Case, you are again engaged on a consulting basis by Buddy’s Floor Barn. This time, in order to get a better idea of what might have motivated customers’ buying habits you are asked to analyze the ages of the customers who have purchased oak flooring over the past 12 months. Past research done by the Excellent Consulting Group has shown that different age groups buy certain products for different reasons. Buddy’s Floor Barn has sent a survey to 200 customers who have previously purchased oak premium flooring, and 124 customers have responded. The survey includes age data of past customers who purchased oak flooring in the past year.

Case Assignment

Using Excel, create a frequency distribution (histogram) of the age data that was captured from the survey. You should consider the width of the age categories (e.g., 5 years, 10 years, or other). That is, which age category grouping provides the most useful information? Once you have created this histogram, determine the mean, median, and mode.

After you have reviewed the data, write a report to your boss that briefly describes the results that you obtained. Make a recommendation on how this data might be used for marketing purposes.

Data: Download the Excel-based data file with the age data of the 124 customers: Data chart for BUS520 Case 2. Use these data in Excel to create your histogram.

Assignment Expectations

Excel Analysis

Accurate and complete analysis in Excel using the Histogram function.

Written Report

Length requirements: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must submit 4–5 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.

Provide a written analysis that supports your Histogram age groups (bins).

Based on your analysis of the histogram data, provide complete and meaningful recommendations as the data relates to Buddy’s Floor Barn marketing strategy.

Module 2 – SLP

RISK: FREQUENCY DISTRIBUTION, PROBABILITIES, AND EXPECTED VALUE

Risk: Probabilities and Expected Value

Scenario: You work for a private investment company that currently has numerous business investments in real estate development, restaurant franchises, and retail chains. Following an exhaustive search for new investment opportunities, you have found three possible alternatives, each of which will pay off in exactly 10 years from the date of initial investment. Because you only have enough money to invest in one of the three options, you recognize that you will need to complete a quantitative comparison of the three alternatives:

Option A: Real estate development.

Option B: Investment in the retail franchise “Just Hats,” a boutique that sells hats for men and women.

Option C: Investment in “Cupcakes and so forth,” a franchise that sells a wide variety of cupcakes and a variety other desserts.

Download the raw data for the three investments in this Excel document: Raw data for BUS520 SLP 2

Assignment

Develop an analysis of these three investments in Excel. Use expected value to determine which of the three alternatives you should choose.

Write a report to your private investment company, explaining your Excel analysis, giving your recommendation, and justifying your decision.

SLP Assignment Expectations

Excel Analysis

Using Excel, make an accurate and complete analysis of the three investment alternatives.

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 in completion of your Excel analysis.

Based on your Excel analysis, give your recommendation as to which of the three investment alternatives should be pursued.

Upload both your written report and Excel file to the SLP 2 Dropbox.

Module 3 – Case

LINEAR REGRESSION FORECASTING AND DECISION TREES

Linear Regression Forecasting

Assignment Overview

Scenario: You are a consultant who works for the Excellent Consulting Group. Your client, the ABC Furniture Company, believes that there may be a relationship between the number of customers who visit the store during any given month (“customer traffic”) and the total sales for that same month. In other words, the greater the customer traffic, the greater the sales for that month. To test this theory, the client has collected customer traffic data over the past 12-month period, and monthly sales for that same 12-month period (Year 1).

Case Assignment

Using the customer traffic data and matching sales for each month of Year 1, create a Linear Regression (LR) equation in Excel. Use the Excel template provided (see “Module 3 Case – LR –Year 1” spreadsheet tab), and be sure to include your LR chart (with a trend line) where noted. Also, be sure that you include the LR formula within your chart.

After you have developed the LR equation above, you will use the LR equation to forecast sales for Year 2 (see the second Excel spreadsheet tab labeled “Year 2 Forecast”). You will note that the customer has collected customer traffic data for Year 2. Your role is to complete the sales forecast using the LR equation from Step 1 above.

After you have forecast Year 2 sales, your Professor will provide you with 12 months of actual sales data for Year 2. You will compare the sales forecast with the actual sales for Year 2, noting the monthly and average (total) variances from forecast to actual sales.

To complete the Module 3 Case, write a report for the client that describes the process you used above, and that analyzes the results for Year 2. (What is the difference between forecast vs. actual sales for Year 2—by month and for the year as a whole?) Make a recommendation concerning how the LR equation might be used by ABC Furniture Company to forecast future sales.

Data: Download the Module 3 Case template here: Data chart for BUS520 Case 3. Use this template to complete your Excel analysis.

Assignment Expectations

Excel Analysis

Accurate and complete Linear Regression analysis in Excel.

Written Report

Length requirements: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must submit 4–5 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.

Your written (Word) analysis should discuss the logic and rationale used to develop the LR equation and chart.

Provide complete, meaningful, and accurate recommendation(s) concerning how the ABC Furniture Company might use the LR equation to forecast future sales. (For example, how reliable is the LR equation in predicting future sales?) What other recommendations do you have for the client?

Module 3 – SLP

LINEAR REGRESSION FORECASTING AND DECISION TREES

Decision Trees

Scenario: You are a consultant who works for the Excellent Consulting Group. You have learned about three different investment opportunities and need to decide which one is most lucrative. Following are the three investment options and their probabilities:

Option A: Real Estate development. This is a risky opportunity with the possibility of a high payoff, but also with no payoff at all. You have reviewed all of the possible data for the outcomes in the next 10 years and these are your estimates of the cash payoff and probabilities:

Required initial investment: $0.75 million

High NPV: $5 million, Pr = 0.5

Medium NPV: $2 million, Pr = 0.3

Low NPV: $0, Pr = 0.2

Option B: Retail franchise for Just Hats, a boutique-type store selling fashion hats for men and women. This also is a risky opportunity but less so than Option A. It has the potential for less risk of failure, but also a lower payoff. You have reviewed all of the possible data for the outcomes in the next 10 years and these are your estimates of the payoffs and probabilities:

Required initial investment: $0.55 million

High NPV: $3 million, Pr = 0.75

Medium NPV: $2 million, Pr = 0.15

Low NPV: $1 million, Pr = 0.1

Option C: High Yield Municipal Bonds. This option has low risk and is assumed to be a Certainty. So there is only one outcome with probability of 1.0:

Required initial investment: $0.75 million

NPV: $1.5 million, Pr = 1.0

Assignment

Develop an analysis of these three investments, and determine which of them you should choose. Be sure to account for cash paid for each of the three alternatives. If you do not recall how to do this, review the practice exercises in the Background page. Do your analysis in Excel using the Decision Tree add-in.

Write a report to your private investment company and explain your analysis and your recommendations. Provide a rationale for your decision.

Upload both your written report and Excel file with the decision tree analysis to the SLP 3 Dropbox.

SLP Assignment Expectations

Analysis

Accurate and complete Excel analysis.

Written Report

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

Provide a brief introduction to/background of the problem.

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

Complete, meaningful, and accurate recommendation(s).

Module 4 – Case

RISK: EXPONENTIAL SMOOTHING FORECASTING AND VALUE OF INFORMATION

Risk: Simple Exponential Smoothing (SES)

Assignment Overview

Scenario: You are a consultant for the Excellent Consulting Group (ECG). You have completed the first assignment, developing and testing a forecasting method that uses Linear Regression (LR) techniques (Module 3 Case). However, the consulting manager at ECG wants to try a different forecasting method as well. Now you decide to try Single Exponential Smoothing (SES) to forecast sales.

Case Assignment

Using this Excel template: Data chart for BUS520 Case 4, do the following:

Calculate the MAPE for Year 2 Linear Regression forecast (use the first spreadsheet tab labeled “Year 2 Forecast – MAPE”).

Calculate forecasted sales for Year 2 using SES (use the second spreadsheet tab labeled “SES – MAPE”). Use 0.15 and 0.90 alphas.

Compare the MAPE calculated for the LR forecast (#1 above) with the MAPEs calculated using SES.

Then write a report to your boss in which you discuss the results obtained above. Using calculated MAPE values, make a recommendation concerning which method appears to be more accurate for the Year 2 data: SES or Linear Regression.

Assignment Expectations

Analysis

Accurate and complete SES analysis in Excel.

Written Report

Length requirements: 4–5 pages minimum (not including Cover and Reference pages). NOTE: You must submit 4–5 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.

Complete a written analysis that supports your Excel analysis, discussing the assumptions, rationale, and logic used to complete your SES forecast.

Give complete, meaningful, and accurate recommendation(s) relating to whether LR or SES is more accurate in predicting sales.

Module 4 – SLP

RISK: EXPONENTIAL SMOOTHING FORECASTING AND VALUE OF INFORMATION

Risk: The Value of Information

Scenario: Using the same situation from the Module 3 SLP, recall that you are deciding among three investments. You have heard of an expert who has a highly reliable “track record” in the correct identification of favorable vs. unfavorable market conditions. You are now considering whether to consult this “expert.” Therefore, you need to determine whether it would be worth paying the expert’s fee to get his prediction. You recognize that you need to do further analysis to determine the value of the information that the expert might provide.

In order to simplify the analysis, you have decided to look at two possible outcomes for each alternative (instead of three). You are interested in whether the market will be Favorable or Unfavorable, so you have collapsed the Medium and Low outcomes. Here are the three alternatives with their respective payoffs and probabilities.

Option A: Real estate development. This is a risky opportunity with the possibility of a high payoff, but also with no payoff at all. You have reviewed all of the possible data for the outcomes in the next 10 years and these are your estimates of the Net Present Value (NPV) of the payoffs and probabilities:

High/Favorable NPV: $7.5 million, Pr = 0.5

Unfavorable NPV: $2.0 million, Pr = 0.5

Option B: Retail franchise for Just Hats, a boutique-type store selling fashion hats for men and women. This also is a risky opportunity but less so than Option A. It has the potential for less risk of failure, but also a lower payoff. You have reviewed all of the possible data for the outcomes in the next 10 years and these are your estimates of the NPV of the payoffs and probabilities.

High/Favorable NPV: $4.5 million, Pr = 0.75

Unfavorable NPV: $2.5 million, Pr = 0.25

Option C: High Yield Municipal Bonds. This option has low risk and is assumed to be a Certainty. So there is only one outcome with probability of 1.0:

NPV: $2.25 million, Pr = 1.0

You have contacted the expert and received a letter stating his track record which you have checked out using several resources. Here is his stated track record:

True State of the Market

Expert Prediction

Favorable

Unfavorable

Predicts “Favorable”

.9

.3

Predicts “Unfavorable”

.1

.7

You realize that this situation is a bit complicated since it requires the expert to analyze and predict the state of two different markets: the real estate market and the retail hat market. You think through the issues of probabilities and how to calculate the joint probabilities of both markets going up, both going down, or one up and the other down. Based on your original estimates of success, here are your calculations of the single probabilities and joint probabilities of the markets.

Probabilities

Favorable

Unfavorable

A: Real Estate

0.50

0.50

B: Just Hats

0.75

0.25

Joint Probabilities

A Fav, B Fav (A+, B+)

0.375

A Unf, B Unf (A-, B-)

0.125

A Fav, B Unf (A+, B-)

0.125

A Unf, B Fav (A-, B+)

0.375

Finally, after a great deal of analysis and calculation, you have determined the Posterior probabilities of Favorable and Unfavorable Markets for the Real Estate business and the boutique hat business.

Real Estate

Just Hats

F

U

F

U

0.45

says “F/F”

0.75

0.25

0.90

0.10

0.15

says “F/U”

0.75

0.25

0.30

0.70

0.30

says “U/F”

0.125

0.875

0.90

0.10

0.10

says “U/U”

0.125

0.875

0.30

0.70

For example, this table says that there is 45% chance that the expert will predict Favorable for both markets (F/F), and when he makes this prediction, there is a 75% chance that the Real Estate market will be favorable and 25% chance that it won’t, and also a 90% chance that the Hat market will be Favorable and 10% chance it won’t.

You have developed a Decision Tree showing the original collapsed solution and also showing an expanded Decision Tree for evaluating the value of the expert’s information. You need to enter the probabilities into this tree to see if the expert’s information will increase the overall expected value of your decision. Download the Excel file with the incomplete Decision Tree: Decision Tree for BUS520 SLP 4

Assignment

Complete the information in the Decision Tree in the Excel file. Determine the Expected NPV of the decision if you were to consult the Expert. Does use of the Expert increase the value of your analysis? If so, by how much?

Write a report to your private investment company and explain your analysis and your recommendation. Provide clear rationale/ justification for your decision.

Upload both your written report and Excel file with the Decision Tree analysis to the SLP 4 Dropbox.

SLP Assignment Expectations

Analysis

Accurate and complete analysis in Excel.

Required:

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.

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

Complete, meaningful, and accurate recommendation(s).

Module 1 discussion

Bounded Rationality

Explain what Bounded Rationality is. Then discuss how this concept can affect the decision situation in SLP 1. Also discuss how this concept can affect the data analysis in Case 1. Explain your logic for both of your arguments. Are there any similarities between these two situations?

Week 1: Provide your initial discussion post to the question. Be sure to include references to any resources you used. You should use at least one resource to help you with your initial discussion.

Week 2: Respond to at least two of your classmates’ initial posts. Your response should be substantive and further the discussion. It is OK to be critical and use critical thinking. That means you can question what your classmates say. Do they provide their own critical thinking and logic?

Module 2 discussion

Estimating Probabilities: Availability Bias

One of the biases that has been proposed by Kahneman and Tversky and researched a great deal is the Availability bias. What is the Availability bias? How might it play a role in the decision in SLP 2? Consider that the estimated probabilities for both of the High NPV future states are relatively high (0.50 and 0.75). How could the Availability bias be at work here? How could using objective data analysis like frequency distributions mitigate the effect of the Availability bias?

Week 1: Provide your initial discussion post to the question. Be sure to include references to any resources you used. You should use at least one resource to help you with your initial discussion.

Week 2: Respond to at least two of your classmates’ initial posts. Your response should be substantive and further the discussion. It is OK to be critical and use critical thinking. That means you can question what your classmates say. Do they provide their own critical thinking and logic?

Module 3 discussion

Anchoring Bias

What is the Anchoring bias? How might is play a role in the decision in SLP 2/SLP 3? In what way might this bias cause error in the estimation of the probabilities? How could you mitigate the effect of this bias?

Week 1: Provide your initial discussion post to the question. Be sure to include references to any resources you used. You should use at least one resource to help you with your initial discussion.

Week 2: Respond to at least two of your classmates’ initial posts. Your response should be substantive and further the discussion. It is OK to be critical and use critical thinking. That means you can question what your classmates say. Do they provide their own critical thinking and logic?

Module 4 discussion

Heuristics

What is the definition of a heuristic decision method? What might be a heuristic method for hiring someone? Explain and justify your logic.

Week 1: Provide your initial discussion post to the question. Be sure to include references to any resources you used. You should use at least one resource to help you with your initial discussion.

Week 2: Respond to at least two of your classmates’ initial posts. Your response should be substantive and further the discussion. It is OK to be critical and use critical thinking. That means you can question what your classmates say. Do they provide their own critical thinking and logic?

Module 4 Reflective discussion

And Finally, We Reflect Back on the Course

As you complete this course, reflect back and answer these questions:

What two concepts do you think are the most relevant to the business and your MBA degree and why?

Which one or two concepts do you think are the least relevant to the business and your MBA degree and why?

Which concepts did you find particularly difficult to learn and why?