Case Descriiption
Magic Foods is a leading manufacturer of pickles, spices, pastes and instant mixes. With quality products and customer satisfaction, the company maintains a leading position in the processed food market in a number of regions. However, very recently, the company has witnessed an increase in competition resulting in a decrease of sales. Company leadership wants to know the critical factors driving sales and what should the company focus on to be able to increase sales.
Generally, the company would like to know how many potential customers are in their market space, how can they be reached and how fierce is the competition? However, the ultimate measure of performance is sales. As a result, the company collected data on:
a. Last year sales in various regions
b. Population in each region
c. Number of food stores in each region
d. Number of Magic Food’s dealers in each region
e. Number of popular competitor brands of similar products sold in each region
Instructions
This is an individual assignment and must be completed by each student without outside assistance. In order to complete the assignment, first review the practice activity of applying descriiptive models that is found in the course Canvas Module. Then, complete the assignment described below for each part of the case.
First, conduct necessary calculations and scatter plot visualizations to address the assignment parts below using he provided Excel spreadsheet model with data. You are to use the Excel spreadsheet model to determine the relationships between all of the data using Correlation Analysis (aka Causal Analysis). The first two relationships have been calculated and you are to complete the correlation coefficients (r) and coefficient of determination values (R2) for each of the remaining variables. The correlation coefficient is to be calculated using the Excel CORREL function. The coefficient of determination (R2) is the squaring of the correlation coefficient (r). The dependent variable is the objective measure (sales) and the independent variables are the population, No. of stores, No. of dealers, No. of competitive brands and the region number.
Finally, prepare a business memo report, using Microsoft Word, addressing all three assignment parts. In your report, you are to describe the relationships of each correlated set in terms of strength, polarity (positive or negative) using the r coefficient. The r (correlation) coefficient is always shown using a leading 0 with at least 3 decimal places – example: 0.9746 or -0.9748. You are also to describe the proportional quality of the relationships using the coefficient of determination R2 (r squared) as a percent with 2 decimal places – example 94.98%. Your report should interpret the results of the correlation findings that inform your recommendations to the company management.
Submittals:
1. Submit your Excel spreadsheet with calculations and scatter plot visualizations to the assignment drop box before the posted deadline. Your submittal is to show correlation calculations and scatter plots for all independent variables against the dependent variable (Sales).
2. Submit a business memo, in a format of your choice, using Microsoft Word to report and discuss your findings of this case to the assignment drop box before the posted deadline. Structure your report with the following 3 sections: problem presented, summary of analysis and recommendations. The report should not be more than 4 pages. Be sure to include a cover sheet with your name and date.
Business Memo Parts
Part 1 (1 point) Problem Presented:
In your own words, define a problem statement which reflects the challenge facing Magic Foods leadership.
Part 2 (2 points) Summary of Analysis:
Discuss the results of your calculations and causal analysis of the relationships and quality of impact between company sales and all predictor variables. Include implications for future data collection and analysis. Illustrate your answers within the report with appropriate statistics and visualizations.
Part 3 (2 points) Recommendations:
Provide recommendations for the Magic Foods’ management. Focus on what the correlation analysis implies – which independent variable relationships should the company focus on and why? In other words, what is most important for the company to focus on? How do you suggest the company address this area or areas to achieve additional sales?
