Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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) ------------->>>

  1. 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:

  1. 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

  1. 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

  1. 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) ------------->>>

  1. 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) ------------->>>

  1. 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:

  1. 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

  1. 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

  1. 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) ------------->>>

  1. 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) ------------->>>image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Accounting questions