Description
Here are the files with the instructions. There is two word documents and four excel documents. Week 6 Hand-on Lab 1 goes with Dairy excel. And Week 6 Hand On Practice goes with Calls, Organics, Vacation
Unformatted Attachment Preview
CIS 321 Introduction to Business Analytics
Spring 2024
Week 6 Hands-On Lab 1
Total Points: (7.5 Points)
Due By: 11:59 PM, Sunday, February 25
Instructions:
1. This is an individual based hands-on lab. Student will practice the skills of Chapter 8.
2. Save your analytic solutions in Excel. Write down your answers and discussion in word
document. After completion, submit all the Excel solution files and the answer/discussion file via
Canvas.
Problem 1: Revised from Page 443 Problem 10 (7.5 Points)
•
•
Data file: W6L1-Diary.xlsx
Please work on the following questions.
Demand for Dairy Products: United Dairies, Inc. supplies milk to several independent grocers
throughout Dade County, Florida. Managers at United Dairies want to develop a forecast for the number
of half gallons of milk sold per week. Sales data for the past 15 weeks are included in the data file.
a. Construct a time series plot. What type of pattern exists in the data?
b. Develop a 2-week moving average for this time series. Calculate the forecasting accuracy
measure, MSE.
c. Develop a 3-week moving average for this time series. Calculate the forecasting accuracy
measure, MSE.
d. Use α=0.2 to compute the exponential smoothing values for the time series. Calculate the
forecasting accuracy measure, MSE.
e. Use α=0.3 to compute the exponential smoothing values for the time series. Calculate the
forecasting accuracy measure, MSE.
f. Which forecasting method (b, c, d, or e) is better in turns of MSE.
g. Use the method you recommend in Question (f), predict the sales of Week 16. How much is the
forecast?
CIS 321 Introduction to Business Analytics
Instructor: Haiyan Huang
Page 1 of 1
Week
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sales
2750
3100
3250
2800
2900
3050
3300
3100
2950
3000
3200
3190
3220
3080
3150
CIS 321 Introduction to Business Analytics
Spring 2024
Week 6 Hands-On Practice
Total Points: (15 Points)
Due By: 11:59 PM, Sunday, February 25
Instructions:
1. You should work on hands-on practice independently. Contact the instructor if you have
questions or need assistance.
2. This is an individual based hands-on practice. Student will practice the skills of Chapter 8.
3. Save your analytic solutions in Excel. Write down your answers and discussion in word
document. After completion, submit all the Excel solution files and the answer/discussion file via
Canvas.
Problem 1: From external sources (7 Points)
•
•
Data file: W3P1-Calls.xlsx
Please work on the following questions.
Customer Services Calls: In preparation for staffing during the upcoming summer months, an online
retailer reviews the number of customer service calls received over the past three weeks (21 days).
a. Develop a time series plot. What type of pattern exists in the data?
b. Develop a three-day moving average for this time series. Calculate the forecasting accuracy
measure, MSE.
c. Develop a four-day moving average for this time series. Calculate the forecasting accuracy
measure, MSE.
d. Use α=0.3 to compute the exponential smoothing values for the time series. Calculate the
forecasting accuracy measure, MSE.
e. Use α=0.4 to compute the exponential smoothing values for the time series. Calculate the
forecasting accuracy measure, MSE.
f. Which forecasting method (b, c, d, or e) is better in turns of MSE.
g. Use the method you recommend in Question (f), predict the service calls of Day 22. How much is
the forecast?
Problem 2: From external sources (3.5 Points)
•
•
Data file: W3P2-Organics.xlsx
Please work on the following questions.
Organic Food Store: A local organic food store carries several food products for health-conscious
consumers. The store has witnessed a steady growth in the sale of chef-designed meals, which are
especially popular with college-educated millennials. For planning purposes, the manager of the store
would like to extract useful information from the weekly sales of chef-designed meals in the past 25
weeks.
a. Construct a time series plot. What type of pattern exists in the data?
b. Develop a simple linear regression model using time period t as the independent variable to
model the pattern.
• What is the regression model?
CIS 321 Introduction to Business Analytics
Instructor: Haiyan Huang
Page 1 of 2
• What is the MSE?
c. Use the regression model to forecast the sales for Week 26.
Problem 3: From external sources (4.5 Points)
•
•
Data file: W3P3-Vacation.xlsx
Please work on the following questions.
Vacation: Vacation destinations often run on a seasonal basis, depending on the primary activities in that
location. Amanda is the owner of a travel agency in Cincinnati, Ohio. She has built a database of the
number of vacation packages (Vacation) that she has sold over the last 9 years. The accompanying file
includes quarterly data on the number of vacation packages sold.
a. Construct a time series plot. What type of pattern exists in the data?
b. Use a multiple linear regression model with dummy variables as follows to develop an equation
to account for seasonal effects in the data: Qtr1=1 if quarter 1, 0 otherwise; Qtr2=1 if quarter 2, 0
otherwise; Qtr3=1 if quarter 3, 0 otherwise.
• What is the regression model?
• Calculate the forecasting accuracy measure, MSE.
c. What is the forecast of vacation packages for 2024, Quarter 3?
CIS 321 Introduction to Business Analytics
Instructor: Haiyan Huang
Page 2 of 2
Day
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Calls
309
292
284
294
292
285
271
276
295
309
296
297
288
308
318
311
292
309
326
327
309
Year
2015
2015
2015
2015
2016
2016
2016
2016
2017
2017
2017
2017
2018
2018
2018
2018
2019
2019
2019
2019
2020
2020
2020
2020
2021
2021
2021
2021
2022
2022
2022
2022
2023
2023
2023
2023
Quarter
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Qtr 1
Qtr 2
Qtr 3
Qtr 4
Vacation Packages
500
147
1314
673
984
584
1449
1125
209
458
1357
544
483
280
1392
780
340
1003
1692
783
781
409
1657
792
671
769
1068
520
616
775
1215
514
232
492
1581
1028
Purchase answer to see full
attachment
