Question

Chez Julia, a restaurant located in Portland Maine operated by Charles Wagon has just completed its third year of operations. Charles, Chuck to his friends, has strived to establish a reputation for high quality food, and service which is both elegant and friendly. Specializing in sea food, Chuck and his staff are proud of becoming one of the best and busiest restaurants in the community.

In order to maintain Chez Julia’s reputation and growth Chuck needs to develop some method to forecast food and beverage sales by month for up to one year in advance. The associated excel file shows the value of food and beverages sales ($1,000) for the first three years of operation.

Perform an analysis using the sales data for Chez Julia Prepare a report for Chuck that summarizes your findings, forecast, and recommendations. Your report should include, but not necessarily be limited to the following:

1. Make this using a time series plot and then comment on the underlying pattern in the time series.
2. Then, Forecast sales for the fourth year.

	Chez Julia Food and Beverage Sales ($1,000)
	Year
Month	First	Second	Third
January	242	263	282
February	235	238	255
March	232	247	265
April	178	193	205
May	184	193	210
June	140	149	160
July	145	157	166
August	152	161	174
September	110	122	126
October	130	130	148
November	152	167	173
December	206	230	235

Answer 1

Let us first draw the time series plot with below steps:

Put the data into excel work sheet

arrange data in single column and then select data

Go to insert>Line graph>line with markers

Time series plot is given as

Clearly from the above graph

Periodic fluctuations are visible in above graph

which represents the seasonility exists in the data

Below table showing the calculations

Month	January	February	March	April	May	June	July	August	September	October	November	December
First year	242	235	232	178	184	140	145	152	110	130	152	206
Second Year	263	238	247	193	193	149	157	161	122	130	167	230
Third Year	282	255	265	205	210	160	166	174	126	148	173	235
Total	787	728	744	576	587	449	468	487	358	408	492	671
Mean	262.33	242.67	248.00	192.00	195.67	149.67	156.00	162.33	119.33	136.00	164.00	223.67
Variance	400.33	116.33	273.00	183.00	174.33	100.33	111.00	122.33	69.33	108.00	117.00	240.33
Standard deviation	20.01	10.79	16.52	13.53	13.20	10.02	10.54	11.06	8.33	10.39	10.82	15.50
Average of total sales	187.63	187.63	187.63	187.63	187.63	187.63	187.63	187.63	187.63	187.63	187.63	187.63
Seasonal index	1.40	1.29	1.32	1.02	1.04	0.80	0.83	0.87	0.64	0.72	0.87	1.19

Seasonal index was being calculated for each month by adding the sales of each month in each year and then dividing the average of total sales

Let us perform the linear regression with below data

Performed regression using excel with below mentioned steps:

Go to Data>Data Analysis>Regression>fill input and output range

Whose summary is given below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.999951
R Square	0.999903
Adjusted R Square	0.999806
Standard Error	2.041241
Observations	3
ANOVA
	df	SS	MS	F	Significance F
Regression	1	42924.5	42924.5	10301.88	0.006272
Residual	1	4.166667	4.166667
Total	2	42928.67
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	1958.667	3.118048	628.1708	0.001013	1919.048	1998.285	1919.048	1998.285
Year	146.5	1.443376	101.4982	0.006272	128.1602	164.8398	128.1602	164.8398

So regression equation is given as

a=intercept

b=regresssion coeffcient for year or slope

Let us calculate the fourth year sales after substitute the value

in thousand dollars

Average monthly sales during the fourth year will be

Forecast for particular month will be calculated by multiplying the average monthly forecast by that month seasonal index

Monthly forecast for 12 months of fourth year will be

Month	January	February	March	April	May	June	July	August	September	October	November	December
Seasonal index	1.40	1.29	1.32	1.02	1.04	0.80	0.83	0.87	0.64	0.72	0.87	1.19
Average monthly sale fourth year	212.06	212.06	212.06	212.06	212.06	212.06	212.06	212.06	212.06	212.06	212.06	212.06
Forecaste monthly sale (4th year)	296.49	274.26	280.29	217.00	221.14	169.15	176.31	183.47	134.87	153.71	185.35	252.79