31 Jan Conduct accurate and

Module 1 – Case

DESCRIPTIVE STATISTICS, PROBABILITY, AND RISK ASSESSMENT

Assignment Overview

You are a consultant who works for the Diligent Consulting Group. In this Case, you are engaged on a consulting basis by Loving Organic Foods. 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 organic foods over the past 3 months. Past research done by the Diligent Consulting Group has shown that different age groups buy certain products for different reasons. Loving Organic Foods has sent a survey to 200 customers who have previously purchased organic foods, and 124 customers have responded. The survey includes age data of past customers who purchased organic foods in the previous quarter.

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. Be sure to conduct adequate research on organic foods industry, organic market analysis, and healthy food industry using IBISWorld database or other databases such as Business Source Complete (EBSCO) and Business Source Complete – Business Searching Interface in our online library. Provide a brief description on the industry background and the consumer changing attitudes and behavior toward healthy lifestyles. Also identify the customer demographics of organic food industry and explain how the customers of Loving Organic Foods are different from this target market.

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

Assignment Expectations

Excel Analysis

Complete analysis in Excel using the Histogram function. Check the following video on histogram: https://youtu.be/4eLJGG2Ad30

If you are not so familiar with Excel, refer to the following link on Excel training videos: https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=en-US&rs=en-US&ad=US

Check the professional market research reports from IBISWorld database to find the industry analysis for your cumulative Session Long Project.

IBISWorld Overview (n.d.). IBISWorld, Inc., New York, NY.

IBISWorld Forecast (n.d.). IBISWorld, Inc., New York, NY.

IBISWorld Data and Sources (n.d.). IBISWorld, Inc., New York, NY.

IBISWorld Navigation Tips (n.d.). IBISWorld, Inc., New York, NY.

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 brief description of organic food industry and target market characteristics such as their demographics, lifestyles and shopping behaviors.

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 Loving Organic Foods’s marketing strategy.

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 1 Dropbox.

Here are some guidelines on how to conduct information search and build critical thinking skills.

Emerald Group Publishing. (n.d.). Searching for Information. Retrieved from http://www.emeraldinsight.com/learning/study_skills/skills/searching.htm

Emerald Group Publishing. (n.d.). Developing Critical Thinking. Retrieved from http://www.emeraldinsight.com/learning/study_skills/skills/critical_thinking.htm

Module 1 – SLP

DESCRIPTIVE STATISTICS, PROBABILITY, AND RISK ASSESSMENT

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 1.

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. Let Excel do the work for you. Instead of typing the formula in a separate line, just enter it in the cell. Refer to the following link for more info on doing calculations in Excel: https://support.office.com/en-us/article/Use-Excel-as-your-calculator-a1abc057-ed11-443a-a635-68216555ad0a

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.

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 SLP1 Dropbox.

Module 2 – Case

LINEAR REGRESSION AND SIMPLE EXPONENTIAL SMOOTHING (SES) FORECASTING

Assignment Overview

Scenario: You are a consultant who works for the Diligent Consulting Group. Your client, the New Star Grocery 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, assuming all assumptions for linear regression have been met. Use the Excel template provided (see “Module 2 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 2 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 New Star Grocery Company to forecast future sales.

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

Assignment Expectations

Excel Analysis

Conduct accurate and complete Linear Regression analysis in Excel. Use Excel support to find information on linear regression in Excel: https://support.office.com/en-us/Search/results?query=linear+regression

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 (in 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 New Star Grocery 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?

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 2 Dropbox.

Here are some guidelines on how to build critical thinking skills.

Emerald Group Publishing. (n.d.). Developing Critical Thinking. Retrieved from http://www.emeraldinsight.com/learning/study_skills/skills/critical_thinking.htm

Module 2 – SLP

LINEAR REGRESSION AND SIMPLE EXPONENTIAL SMOOTHING (SES) FORECASTING

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

Using this Excel template: Data chart for BUS520 SLP 2, 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.

SLP Assignment Expectations

Analysis

Conduct accurate and complete SES analysis in Excel. You may also check the following link for your reference: https://support.office.com/en-US/article/data-analysis-7e71735c-c471-47e1-84ef-a8c23dc3098b

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.

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.

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 SLP 2 Dropbox .

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.

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.

Module 4 – Case

DECISION TREE AND VALUE OF INFORMATION

Assignment Overview

Scenario: You are a consultant who works for the Diligent 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

Case 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 or SmartArt graphic, as suggested in the Background page.

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 case 4 Dropbox.

Assignment Expectations

Excel Analysis

Conduct accurate and complete Excel analysis using decision tree add-in.

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).

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.

Module 4 – SLP

DECISION TREE AND VALUE OF INFORMATION

Scenario: You are deciding among three investments, as you do for Case 4. 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?

Develop a PowerPoint presentation to your private investment company and explain your analysis and your recommendation. Provide clear rationale/ justification for your decision. Use audio/video feature in PowerPoint to present each slide. Be sure to check the Oral Communication Rubric (under Assessments>Rubrics) to understand the requirements for the PowerPoint presentation.

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

SLP Assignment Expectations

Analysis

Conduct accurate and complete analysis in Excel. Check the following link on PowerPoint presentation:

https://support.office.com/en-US/article/PowerPoint-training-40e8c930-cb0b-40d8-82c4-bd53d3398787

Required:

Meet Length requirements: 10-15 slides (not including Cover and Reference pages).

Provide a brief introduction to/background of the problem.

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

Offer meaningful and accurate recommendation(s).

Oral presentation of each slide should use video/audio feature in PowerPoint.