Case Set-Up
• The owner of the Vintage Restaurant, Karen Payne, would like to have the next year’s food and
everage sales forecasted by month.
• You are given an Excel file with the survey results and a template to provide your responses to
the case questions.
• In this project, we practice how to use time series analysis to make forecast. You should be able
to
o use visual inspection to identify the pattern and make the initial forecasting model
o check some signals to see how to improve the forecasting model. Some of the signals
include
R square (it indicates how much variability in the dependent variable is captured
y the model)
Measures of Forecast Accuracy, such as MSE
o interpret the forecasts in the right way
• You can refer to the slides in “Forecasting Model Procedure” for a general guideline.
Data Set:
• It is a time series data set, with both year and month time stamps. Each data point in a time
series needs a unique time stamp. In this data set, year number and month number together
make the unique time stamp for each data point. However, such combined time stamps are not
easy to handle in the model. We can insert a new column “Period”, using 1, 2… to consecutively
label each data point.
Excel Data Tools Needed:
You will need to use DataAnalysis Toolpak for some questions. DataAnalysis Toolpak is an Excel add-in.
You can find it in Data’s Analyze section (at the very end of the ri
on). However, if using it for the first
time, you may need to call it out first. Here is how to do it:
• Go to “File”, select “Options” at the bottom of the left panel
• Click “Add-ins” on the left panel
• Find “Manage Excel Add-ins” at the bottom, click “Go…” button
• Check “Analysis ToolPak” and click “OK” button
Question 1:
• Objectives: Make visual inspection to identify the patterns. You should be able to
o create the time series plot
o Identify the patterns of time series data
• Use Line chart to create the time series plot
o Slide: Important Concepts
o Excel example: 4. LinearTrend
o Video: Use Regression tools to forecast (Part 1)
• Identify the patterns of time series data.
o Slides: Time Series Patterns
o Seasonality pattern means that you can identify the repeated cycles in the data, and the
length of each cycle is shorter than a year. A cycle is not necessarily in quarters. Inside a
cycle, the data is not necessarily in quarters either. They could be in months, in days, in
hours, etc. Check Homework Q27 for the example of using hours to characterize the
seasonality.
• Comment on: What kinds of patterns can be observed in the time series plot? What pattern is
more obvious? Is there any trend pattern? If yes, what kind of trend pattern? How would you
propose the forecasting model basing on your observation?
Question 2:
• Objectives: Propose two forecasting models and compare them to decide which one is better
o Propose forecasting models basing on the observation from visual inspection
o Compare models using important signals such as R square or MSE
• Propose the forecasting models
o For first model, include only the most obvious pattern
o For second model, include the less obvious pattern too
o Use separate tab to create different models.
o You need to prepare data first to each model
• Compare the models
o Compare R square
o Calculate MSE and compare
o Refer to slide “Forecasting Model Procedure”
• You can also check the Excel examples “5. NoTrend” and “6. WithTrend”
• You can watch the video “B. Use Regression tools to forecast”, especially Parts 2 & 3.
• Comment on: Which model is better? Should the better model have a higher R square or a
lower R square? Should the better model have a higher MSE or a lower MSE?
Question 3 & Question 4:
• Objectives: Use the forecasting model you pick to do the forecasts for the next year’s monthly
sales.
o Prepare the data from the future years to plug in the model for the monthly forecasts
o Be able to explain why forecasted sales is different to the actual sales
• Calculate the forecast e
or and the percentage of forecast e
or. Do you think those e
ors are
ig ones?
• Comment on: Are the forecast e
or and the percentage of forecast e
or the big ones? How
would you comment on the forecasting model you pick?
Case Set-Up
Data Set:
Excel Data Tools Needed:
Question 1:
Question 2:
Question 3 & Question 4:
Data
Year Month Sales
1 January 242
1 Fe
uary 235
1 March 232
1 Apirl 178
1 May 184
1 June 140
1 July 145
1 August 152
1 September 110
1 October 130
1 November 152
1 December 206
2 January 263
2 Fe
uary 238
2 March 247
2 Apirl 193
2 May 193
2 June 149
2 July 157
2 August 161
2 September 122
2 October 130
2 November 167
2 December 230
3 January 282
3 Fe
uary 255
3 March 265
3 Apirl 205
3 May 210
3 June 160
3 July 166
3 August 174
3 September 126
3 October 148
3 November 173
3 December 235