# Case Set-Up • The owner of the Vintage Restaurant, Karen Payne, would like to have the next year’s food and beverage sales forecasted by month. • You are given an Excel file with the survey results...

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
Answered 1 days AfterApr 10, 2022

## Solution

Subhanbasha answered on Apr 12 2022
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
Question1-Answe
The above plot is the scatter plot about the sales which is explainging the various components of the time series model. By observing some patter following that is same pattern in each year that means there is some seasonal effect and there is cyclical variation as well.
The above plot usually called as regression plot where it will find the linear relation between the variables and will give the trend line with the regression equation.By observing that there is no considered trend present in the data. We can observe that there is cyclical and seasonal variations where we need to deseasonalize the data as to build a model.
Sales By Month
Sales    242    235    232    178    184    140    145    152    110    130    152    206    263    238    247    193    193    149    157    161    122    130    167    230    282    255    265    205    210    160    166    174    126    148    173    235    Months
Sales
Sales Time Series Plot
Sales    242    235    232    178    184    140    145    152    110    130    152    206    263    238    247    193    193    149    157    161    122    130    167    230    282    255    265    205    210    160    166    174    126    148    173    235    Months
Sales
Question 2- Model1
t    Year    Month    Sales    MA(3)    CMA    St x It    St    Deseasonalize    Tt    CMA^    Forecast    E
o
1        1    242        0        1.0361976824    233.5461699128    196.2309100277    169.8014005602    203.3340141884    1495.0584587808
2        2    235        0        0.8735751688    269.0094778129    196.4736672881    170.0349206349    171.6345170739    4015.184426462
3        3    232    236.3333333333    225.6666666667    1.0280649926    1.00090723    231.7897134197    196.7164245486    170.2684407096    196.8948915893    1232.3686365305                SUMMARY OUTPUT
4    Year 1    4    178    215    206.5    0.8619854722    0.8645924763    205.877340921    196.959181809    170.5019607843    170.2894267293    59.4529401624
5        5    184    198    182.6666666667    1.0072992701    0.9656033805    190.5544281558    197.2019390695    170.735480859    190.4188590103    41.2017509936                Regression Statistics
6        6    140    167.3333333333    161.8333333333    0.8650875386    1.0754545587    130.1775131894    197.4446963299    170.9690009337    212.3427987596    5233.4805323675                Multiple R    0.0464410953
7        7    145    156.3333333333    151    0.9602649007    0.8371045779    173.2161116067    197.6874535904    171.2025210084    165.485072403    419.6381913576                R Square    0.0021567753
8        8    152    145.6666666667    140.6666666667    1.0805687204    0.97485891    155.9199987135    197.9302108508    171.4360410831    192.9540296149    1677.2325416976                Adjusted R Square    -0.0271915548
9        9    110    135.6666666667    133.1666666667    0.8260325407    1.0434851893    105.4159667362    198.1729681113    171.6695611578    206.7905571343    9368.4119503715                Standard E
or    55.8157781261
10        10    130    130.6666666667    130.6666666667    0.9948979592    1.1293980952    115.1055598146    198.4157253717    171.9030812325    224.0903422899    8852.9925122349                Observations    36
11        11    152    130.6666666667    146.6666666667    1.0363636364    1.1791887467    128.9021799325    198.6584826322    172.1366013072    234.255847154    6766.0243910152
12        12    206    162.6666666667    184.8333333333    1.1145175834    0.9782933512    210.5707861003    198.9012398926    172.3701213819    194.5837605334    130.3305235577                ANOVA
13        1    263    207    221.3333333333    1.188253012    1.0361976824    253.812573087    199.1439971531    172.6036414566    206.3525483164    3208.9337822421                    df    SS    MS    F    Significance F
14        2    238    235.6666666667    242.5    0.981443299    0.8735751688    272.4436413594    199.3867544135    172.8371615313    174.1793176513    4073.0794954557                Regression    1    228.9472749421    228.9472749421    0.0734888602    0.7879622291
15        3    247    249.3333333333    237.6666666667    1.0392706872    1.00090723    246.7761173045    199.629511674    173.070681606    199.8106215547    2226.837438057                Residual    34    105923.636986133    3115.4010878274
16        4    193    226    218.5    0.8832951945    0.8645924763    223.2265550436    199.8722689344    173.3042016807    172.8080599407    407.7144433566                Total    35    106152.584261075
17        5    193    211    194.6666666667    0.9914383562    0.9656033805    199.8750251852    200.1150261949    173.5377217554    193.2317457863    0.0537061095
18    Year 2    6    149    178.3333333333    172.3333333333    0.8646034816    1.0754545587    138.5460676087    200.3577834553    173.7712418301    215.4756915885    4419.0175721663                    Coefficients    Standard E
or    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper...
SOLUTION.PDF