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:

Period = Time period

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

1-Download the data from Course Blackboard site into Excel spreadsheet.

2-Make time series scatter plots of all five variables (five graphs).Insert trend line, equation, and R-squared. Observe graphs and provide interpretation of results.

3-Construct scatter plots of Demand vs. DIFF and Demand vs. ADV, Demand vs. AIP, and Demand vs. Price.Insert fitted line, equation, and R-squared. Observe graphs and provide interpretation.

4-Obtain the correlation matrix for all six variables and list the variables ranking based on absolute value of correlation with demand.Explain your findings in plain language.

PERIOD

PRICE

AIP

DIFF

ADV

DEMAND

PERIOD

1

PRICE

-0.3400741326

1

AIP

0.2522315508

-0.2689223464

1

DIFF

0.3678910267

-0.7666142673

0.8246135553

1

ADV

0.8254421186

-0.4694066794

0.3569442126

0.5136607023

1

DEMAND

0.7144602283

-0.6011657303

0.2863751735

0.5440010757

0.7675888499

1

Period vs Demand is a positive correlation.

Price vs Demand is a negative correlation.

AIP vs Demand is a positive correlation.

DIFF vs Demand is a positive correlation.

ADV vs Demand is a positive correlation.

5-Use 3-month and 6-month moving averages to predict the demand for August 2021. Find MAD for both forecasts and identify the preferred one based on each calculation. Is the moving average suitable method for forecasting for this data set? Explain your reasoning.

3M MA= 13.0333

3M MAD = 0.9367

6M MA=13.45

6M MAD =1.3173

3-month moving average is a better way to predict the demand due the lower MAD value.

6-Use Exponential smoothing forecasts with alpha of 0.1, 0.2, ..., 0.9 to predict August 2021 demand.Identify value of alpha that results in the lowest MAD.

Alpha

Forecast

MAD

0.1

12.36727

1.260445

0.2

12.99794

1.038594

0.3

13.10194

0.935074

0.4

13.03734

0.864931

0.5

12.92707

0.800409

0.6

12.81717

0.729971

0.7

12.72036

0.677097

0.8

1.902036

0.635487

Lowest MAD Value

0.9

12.56405

0.59547

7-Find the monthly seasonal indices for the demand values using Simple Average (SA) method.Find the de-seasonalized demand values by dividing monthly demand by seasonal indices.

Month

2018

2019

2020

2021

Monthly Avg.

Seasonal index

Jan

11.5

12.5

14.7

12.9

1.14893617

Feb

11.1

12.4

14.1

12.53333333

1.11627907

Mar

11

12.1

14

12.36666667

1.101434933

Apr

10.5

11.8

13.5

11.93333333

1.062840178

May

10.2

11.5

13.5

11.73333333

1.045027214

Jun

8.9

11

13.1

11

0.979713013

Jul

8.3

10.2

12.5

10.33333333

0.920336467

Aug

8.2

10.3

9.25

0.823849579

Sep

8.8

10.9

9.85

0.877288471

Oct

9.8

11.2

10.5

0.935180604

Nov

9.4

10.1

12.5

10.66666667

0.95002474

Dec

10.3

11.3

13.4

11.66666667

1.03908956

Grand Avg

11.22777778

Month/Yr.

PERIOD

DEMAND

Seasonal Index

De-Seasonalized Demand

Nov. 2018

1

9.4

0.95002474

9.894479167

2

10.3

1.03908956

9.91252381

Jan. 2019

3

11.5

1.14893617

10.00925926

4

11.1

1.11627907

9.94375

5

11

1.101434933

9.986972147

6

10.5

1.062840178

9.879189944

7

10.2

1.045027214

9.760511364

8

8.9

0.979713013

9.084292929

9

8.3

0.920336467

9.01844086

10

8.2

0.823849579

9.953273273

11

8.8

0.877288471

10.03090807

12

9.8

0.935180604

10.47925926

13

10.1

0.95002474

10.63130208

14

11.3

1.03908956

10.87490476

Jan. 2020

15

12.5

1.14893617

10.87962963

16

12.4

1.11627907

11.10833333

17

12.1

1.101434933

10.98566936

18

11.8

1.062840178

11.10232775

19

11.5

1.045027214

11.00449811

20

11

0.979713013

11.22777778

21

10.2

0.920336467

11.08290323

22

10.3

0.823849579

12.50228228

23

10.9

0.877288471

12.42464749

24

11.2

0.935180604

11.9762963

25

12.5

0.95002474

13.15755208

26

13.4

1.03908956

12.89590476

Jan. 2021

27

14.7

1.14893617

12.79444444

28

14.1

1.11627907

12.63125

29

14

1.101434933

12.71069182

30

13.5

1.062840178

12.70181564

31

13.5

1.045027214

12.91832386

32

13.1

0.979713013

13.37126263

Jul-21

33

12.5

0.920336467

13.58198925

8-Use regression to perform trend analysis on the de-seasonalized demand values. Is trend analysis suitable for this data? Find MAD and explain the Excel Regression output (trend equation, r, r-squared, goodness of model).

The trend analysis is suitable for this data because the Significance F value is 1.13363E-15 which is lower than 1%

9-Find the seasonally adjusted trend forecasts for August through October 2021.

Seas. Ind.

Seas. For.

Aug. Demand =

13.44325785

0.823849579

11.07522

Sept. Demand =

13.57358021

0.877288471

11.90795

Oct. Demand =

13.70390257

0.935180604

12.81562

10- Perform simple linear regression analysis with ADV as the independent variable to predict demand.Write the complete equation, find MAD and explain the Excel Regression output.Make sure to use the de-seasonalized demand data for this model and all future models.

11- Repeat part (10) with DIFF as the independent variable.

12- Construct four variable regression model with Period, AIP, DIFF, and ADV as independent variables. Write the equation, find MSE, and explain the output. Rank variables based on their degree of contribution to the model.Observe significant F, R-squared, and p-values and explain.

Based on the table provided, R is 90% due to the four variables that we used which were Period, AIP, DIFF, and ADV. The significance F is 1.08345E-13 which is less than one percent, representing an effective regression model. The more significant variable in the P-Value was Period, followed by price difference, average industry price, and advertising.

13- Perform multiple linear regression analysis with Period, DIFF, and ADV as independent variables.Write the equation and find MAD. Which variable is the most significant predictor of demand?Rank the independent variables based on their degree of contribution to the model.Observe significant F, R-squared, and p-values and explain.

14- Use the model obtained in parts 13 and make forecasts for the following months.Make sure to seasonalize final forecasts.

DEMAND = 8.294 + .110(Period) + .323(Diff) + .132(ADV)

PeriodPriceAIPADV

Aug. 2021$7.30$7.65$11.7

Sep.2021$7.45$7.90$11.9

Oct.2021$7.50$7.95$12.3

15- Provide a paragraph of conclusion of your analysis.

I need help with #14 and #15