90.2K

Verified Solution

Link Copied!

Question 2)

The Excel file AnalyticalExercise3_S21.xlsx has two worksheets. The second worksheet, SoftDrink represent quarterly sales (in millions of dollars) for a soft drink company from quarter 1 of 2014 through quarter 4 of 2020. There has been an upward trend in sales during this period, and there is also a fairly regular seasonal pattern. Sales in the warmer quarters, 2 and 3, are consistently higher than in the colder quarters, 1 and 4.

1. Create a scatter plot for sales versus period.
   1. Use appropriate chart and axis titles with no legend.
   2. Place the scatter plot on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab. Name this sheet chart 3.
   3. Display the regression line with equation and R2 value on the chart using Excel options.

Delete this text after you insert the image) ------------->>>

2. What is the estimated regression equation for a model to predict average quarterly sales based on time, using Excels functions or tools?

Linear Regression Model:

Intercept:

Slope:

3. Calculate the seasonal Index for each quarter and fill the following table. Round your answers to the nearest thousandth.

Season	Seasonal Index
Q1
Q2
Q3
Q4

4. Calculate the sale forecast for Q1, Q2, Q3, and Q4 of 2021 based on the simple linear regression and seasonally adjusted linear regression models. Fill the following table and round your answers to the nearest thousandth.

Season	Linear Regression (trend)	Seasonally adjusted linear regression
Q1- 2021
Q2-2021
Q3-2021
Q4-2021

5. Using the actual demand and seasonally adjusted linear regression data draw a scatter plot of actual sale and forecast versus period.

1. Use appropriate chart and axis titles with no legend.
2. Place the plot on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab. Name this sheet chart 4.

Delete this text after you insert the image) ------------->>>

6. Insert two screenshots of first ten rows of your Excel file, one of formula view and one of normal view to show the details of your calculations for finding seasonal indices. Adjust column widths to have the first five rows and all columns on the same screen.

Delete this text after you insert the image) ------------->>>

Question 2)

The Excel file AnalyticalExercise3_S21.xlsx has two worksheets. The second worksheet, SoftDrink represent quarterly sales (in millions of dollars) for a soft drink company from quarter 1 of 2014 through quarter 4 of 2020. There has been an upward trend in sales during this period, and there is also a fairly regular seasonal pattern. Sales in the warmer quarters, 2 and 3, are consistently higher than in the colder quarters, 1 and 4.

1. Create a scatter plot for sales versus period.
   1. Use appropriate chart and axis titles with no legend.
   2. Place the scatter plot on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab. Name this sheet chart 3.
   3. Display the regression line with equation and R2 value on the chart using Excel options.

Delete this text after you insert the image) ------------->>>

2. What is the estimated regression equation for a model to predict average quarterly sales based on time, using Excels functions or tools?

Linear Regression Model:

Intercept:

Slope:

3. Calculate the seasonal Index for each quarter and fill the following table. Round your answers to the nearest thousandth.

Season	Seasonal Index
Q1
Q2
Q3
Q4

4. Calculate the sale forecast for Q1, Q2, Q3, and Q4 of 2021 based on the simple linear regression and seasonally adjusted linear regression models. Fill the following table and round your answers to the nearest thousandth.

Season	Linear Regression (trend)	Seasonally adjusted linear regression
Q1- 2021
Q2-2021
Q3-2021
Q4-2021

5. Using the actual demand and seasonally adjusted linear regression data draw a scatter plot of actual sale and forecast versus period.

1. Use appropriate chart and axis titles with no legend.
2. Place the plot on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab. Name this sheet chart 4.

Delete this text after you insert the image) ------------->>>

6. Insert two screenshots of first ten rows of your Excel file, one of formula view and one of normal view to show the details of your calculations for finding seasonal indices. Adjust column widths to have the first five rows and all columns on the same screen.

Delete this text after you insert the image) ------------->>>

E F G A 1 Period 2 3 4 3 Seasonal index Q1 Q2 Q3 Q4 5 6 7 0 8 9 00 10 11 12 13 14 15 B Quarter Year 1 Q1 2014 2 Q2 2014 3 Q3 2014 4 Q4 2014 5 Q1 2015 6 Q2 2015 7 Q3 2015 8 Q4 2015 9 Q1 2016 10 Q2 2016 11 Q3 2016 12 Q4 2016 13 Q1 2017 14 Q2 2017 15 Q3 2017 16 Q4 2017 17 Q1 2018 18 Q2 2018 19 Q3 2018 20 Q4 2018 21 Q1 2019 22 Q2 2019 23 Q3 2019 24 Q4 2019 25 Q1 2020 26 Q2 2020 27 Q3 2020 28 Q4 2020 D Sales 3995.07 5178.43 5010.64 4453.38 4306.70 5321.93 4888.10 4554.65 4176.79 5125.40 4962.65 4917.63 4542.60 5284.71 4817.43 4634.50 4431.36 5602.21 5349.85 5036.00 4534.61 5836.17 5818.28 5070.42 4497.47 6075.52 5868.67 5432.24 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20 E F G A 1 Period 2 3 4 3 Seasonal index Q1 Q2 Q3 Q4 5 6 7 0 8 9 00 10 11 12 13 14 15 B Quarter Year 1 Q1 2014 2 Q2 2014 3 Q3 2014 4 Q4 2014 5 Q1 2015 6 Q2 2015 7 Q3 2015 8 Q4 2015 9 Q1 2016 10 Q2 2016 11 Q3 2016 12 Q4 2016 13 Q1 2017 14 Q2 2017 15 Q3 2017 16 Q4 2017 17 Q1 2018 18 Q2 2018 19 Q3 2018 20 Q4 2018 21 Q1 2019 22 Q2 2019 23 Q3 2019 24 Q4 2019 25 Q1 2020 26 Q2 2020 27 Q3 2020 28 Q4 2020 D Sales 3995.07 5178.43 5010.64 4453.38 4306.70 5321.93 4888.10 4554.65 4176.79 5125.40 4962.65 4917.63 4542.60 5284.71 4817.43 4634.50 4431.36 5602.21 5349.85 5036.00 4534.61 5836.17 5818.28 5070.42 4497.47 6075.52 5868.67 5432.24 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20

Answer & Explanation Solved by verified expert

See Answer