Question

The Fresh Detergent Case

Enterprise Industries produces Fresh, a brand of liquid detergent. In order to more effectively manage its inventory, the company would like to better predict demand for Fresh. To develop a prediction model, the company has gathered data concerning demand for Fresh over the last 33 sales periods. Each sales period is defined as one month. The variables are as follows:

Demand = Y = demand for a large size bottle of Fresh (in 100,000)

Price = the price of Fresh as offered by Ent. Industries

AIP = the average industry price

ADV = Ent. Industries Advertising Expenditure (in $100,000) to Promote Fresh in the sales period.

DIFF = AIP - Price = the "price difference" in the sales period

Question:

1. Obtain the correlation matrix for all six variables and list the variables that have strong correlation with Demand. High correlation is r > 0.50. Explain your findings in plain language.

Month/Yr.	PERIOD	PRICE	AIP	DIFF	ADV	DEMAND
June 2016	1	6.1	5.8	-0.3	5.3	14.4
	2	5.75	6	0.25	6.75	15.3
	3	5.7	6.3	0.6	7.25	16.5
	4	5.7	5.7	0	7.3	16.1
	5	5.6	5.85	0.25	7.2	16
	6	5.6	5.8	0.2	6.5	15.5
	7	5.6	5.75	0.15	6.75	15.2
Jan. 2017	8	6.3	5.85	-0.45	6.89	13.9
	9	6.4	5.65	-0.75	5.8	13.3
	10	6.2	6	-0.2	5.5	13.12
	11	5.9	6.1	0.2	6.5	13.8
	12	5.9	6	0.1	6.25	14.8
	13	5.7	6.1	0.4	7	15.3
	14	5.75	6.2	0.45	6.9	16.3
	15	5.75	6.1	0.35	6.8	17.5
	16	5.8	6.1	0.3	6.8	17.4
	17	5.7	6.2	0.5	7.1	17.1
	18	5.8	6.3	0.5	7	16.8
	19	5.7	6.1	0.4	6.8	16.5
Jan. 2018	20	5.8	5.75	-0.05	6.5	16
	21	5.8	5.75	-0.05	8.1	15.2
	22	5.75	5.65	-0.1	7.7	15.3
	23	5.7	5.9	0.2	7.3	15.9
	24	5.55	5.65	0.1	7.5	16.2
	25	5.6	6.1	0.5	8.1	17.5
	26	5.65	6.25	0.6	8.3	18.4
	27	5.7	5.65	-0.05	8.7	19.4
	28	5.75	5.75	0	9.2	19.1
	29	5.8	5.85	0.05	8.4	18.7
	30	5.3	6.25	0.95	8.8	18.2
	31	5.4	6.3	0.9	9.5	18.4
Jan. 2019	32	5.7	6.4	0.7	9.3	17.5
Feb. 2019	33	5.9	6.5	0.6	9.1	17.1

Answer 1

Using the data analysis option in excel I calculated the correlation matrix :

The steps are :

1)Go to data tab

2)Go to data analysis

3) Correlation

4)Input: Input the data range by selecting the data from the sheet.

Ok.

	Period	Price	AIP	DIFF	ADV	DEMAND
Period	1
Price	-0.38396	1
AIP	0.290259	-0.23374	1
DIFF	0.426187	-0.76244	0.807343	1
ADV	0.814258	-0.55717	0.299438	0.537413	1
DEMAND	0.691026	-0.64098	0.299191	0.588114	0.783047	1

We get the above correlation matrix.
Highly correlated variable with demand:

1)DEMAND and Period (0.691)=As the period increases demand increases
2)Demand and price(-0.641) As the price increases demand increases
3)Demand and DIFF(0.588) As the price difference increases the demand increases
4)Demand and ADV(0.783)As the advertising expenditure increases the demand increases.