Case Description
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.
Sample Solution
