BUSN 312 WEEK 4 HOMEWORK

C8-6

Problem 6
		a=		a=
Week	Actual	0.1	Abs Error	0.7	Abs Error
1	430
2	289
3	367
4	470
5	468
6	365
		MAD

C8-8

Problem 8				=B5*D5+(1-B5)*(E4+F4)
				=C5*(E5-E4)+(1-C5)*F4
Time Period (t)	a	b	Actual (A)	Smoothed Avg (S)	Smoothed Trend (T)	Forecast (FIT)
Nov
Dec	0.20	0.10	1100
Jan

C8-10

Problem 10
Day	Week1	Week2
Tuesday	52	48
Wednesday	36	32
Thursday	35	30
Friday	89	97
Saturday	98	99
Sunday	65	69
Avg demand
Day	Week1	Week2	Avg
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Avg Daily Demand
Day	Forecast
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Level-Horiz

Naïve Method
At
Ft+1	0
Simple Average
Time	Actual	Forecast
1	51
2	53
3	48
4	52
5	50
6		50.8
Simple Moving Average
	Actual Sales	3-period	5-period
January	200
February	300
March	200
April	300	233.3
May	400	266.7
June	500	300.0	280
July	600	400.0	340
August	650	500.0	400
September		583.3	490
Weighted Moving Average
Month	Actual Sales	Weight	Forecast
May	400	0.25
June	500	0.25
July	600	0.50
August			525
Exponential Smoothing
Time Period (t)	Actual Demand (A)	a=	a=
		0.10	0.60
1	50
2	46	50	50	Use the naïve method to find this value
3	52	49.60	47.60
4	51	49.84	50.24
5	48	49.96	50.70		Note this formula. In order to set up this table, it is necessary to use
6	45	49.76	49.08		mixed referencing. This is an important concept to grasp and we will be
7	52	49.28	46.63		covering this in the Excel tutorial online. Until then, view this video:
8	46	49.56	49.85		http://www.youtube.com/watch?v=C2FTc_atkT0
9	51	49.20	47.54		=C$40*$B42+(1-C$40)*C42
10	48	49.38	49.62

Actual Sales January February March April May June July August 200 300 200 300 400 500 600 650 3-period January February March April May June July August 233.33333333333334 266.66666666666669 300 400 500 5-period January February March April May June July August 280 340 400 http://www.youtube.com/watch?v=C2FTc_atkT0

Trend

Trend Adjusted Exponential Smoothing
Time Period (t)	a	b	Actual (A)	Smoothed Avg (S)	Smoothed Trend (T)	Forecast (FIT)
June	0.2	0.1		57	15		=B5*D5+(1-B5)*(E4+F4)
July	0.2	0.1	62	70	14.8	72	=C5*(E5-E4)+(1-C5)*F4
August	0.2	0.1				84.8
Linear Trend Line
	Week	Sales
	X	Y	X2	XY	b	a
	1	2300	1	2300
	2	2400	4	4800
	3	2300	9	6900
	4	2500	16	10000	50	2250	Using formulas
Average	2.5	2375
Total			30	24000
Slope	50		Correlation Coefficient
Intercept	2250		+1	Positively correlated-move together
Corrcoeff	0.6741998625		-1	Negatively correlated-move inversely
			0	Not related

Linear Regression by Week with Trendline

Y 2300 2400 2300 2500

Seasonality

Computing Seasonality-5 Steps
Step 1: Calculate Average Demand for each Season
	Enrollment (thousands)
Quarter	Year 1	Year 2
Fall	24	26
Winter	23	22
Spring	19	19
Summer	14	17
Total Demand	80	84
Average Demand	20	21
Step 2 & 3: Calculate Seasonal Indices
	Individual
Quarter	Year 1	Year 2	Average
Fall	1.200	1.238	1.219
Winter	1.150	1.048	1.099
Spring	0.950	0.905	0.927
Summer	0.700	0.810	0.755
Step 4: Calculate Forecast for Next Year
Estimated annual enrollment		90000
Average per Quarter		22500
Step 5: Expected Quarterly Enrollment, Based on Historical Seasonal Indices
Quarter	Forecast
Fall	27429
Winter	24723
Spring	20866
Summer	16982

Forecast Accuracy

Forecast Accuracy
Mean Absolute Deviation (MAD)
Mean Squared Error (MSE)
		Method A				Method B
Month	Actual Sales	Forecast	Error	Absolute Error	Error2	Forecast	Error	Absolute Error	Error2
January	30	28	2	2	4	30	0	0	0
February	26	25	1	1	1	28	-2	2	4
March	32	32	0	0	0	36	-4	4	16
April	29	30	-1	1	1	30	-1	1	1
May	31	30	1	1	1	28	3	3	9
Totals			3	5	7		-4	10	30
			MAD	1.0			MAD	2.0
			MSE	1.4			MSE	6.0

1,3,5

Chapter 8-Problem 1				Problem 3
Simple Moving Average				Simple Moving Average
	Actual Sales	3-period		Month	Actual Values	Naïve method	Absolute Error	Squared Error	3 Pd Moving Avg	Absolute Error	Squared Error	5 Pd Moving Avg	Absolute Error	Squared Error
Month 1	200			January	32
Month 2	350			February	41	32	9	81
Month 3	287			March	38	41	3	9
Month 4	300	279.0		April	39	38	1	1	37.0	2.0	4.0
Month 5		312.3		May	43	39	4	16	39.3	3.7	13.4
				June	41	43	2	4	40.0	1.0	1.0	38.6	2.4	5.76
				July		41			41.0			40.4
							19	111		6.7	18.4		2.4	5.76
						MAD	3.8			2.2			2.4
						MSE		22.2			6.15			5.76
Problem 5:
Week	Actual Demand (A)	a=	Absolute	Squared	a=	Absolute	Squared
		0.10	Error	Error	0.70	Error	Error
1	330
2	350	330	20.0	400.0	330	20	400
3	320	332.00	12.0	144.0	344.00	24	576
4	370	330.80	39.2	1536.6	327.20	42.8	1831.84
5	368	334.72	33.3	1107.6	357.16	10.84	117.5056
6	343	338.05	5.0	24.5	364.75	21.748	472.975504
			109.4	3212.7		119.388	3398.321104
		MAD	21.89			23.88
		MSE		642.54			679.66

7,9,11

Chapter 9-Problem 7								Slope	3.86
								Intercept	21.00
Week (X)	Actual Demand (Y)	3 Pd Moving Avg	Abs Error	Sqd Error	a =	Abs Error	Sqd Error	Linear	Abs Error	Sqd Error
					0.20			Regression
1	20							24.86	4.9	23.6
2	31				20.0	11.0	121.0	28.71	2.3	5.2
3	36				22.2	13.8	190.4	32.57	3.4	11.8
4	38	29.0	9.0	81.0	25.0	13.0	170.0	36.43	1.6	2.5
5	42	35.0	7.0	49.0	27.6	14.4	208.3	40.29	1.7	2.9
6	40	38.7	1.3	1.8	30.5	9.5	91.1	44.14	4.1	17.2
Totals	207		17.3	131.8		61.8	780.9		18.0	63.1
		MAD	5.8			12.4			3.0
		MSE		43.9			156.2			10.5238095238
Problem 9:
	Visitors				Visitors
Season	Year 1	Year 2		Season	Year 1	Year 2	Avg		Season	Forecast
Fall	200	230		Fall	0.282	0.284	0.283		Fall	283
Winter	1400	1600		Winter	1.972	1.975	1.973		Winter	1973
Spring	520	580		Spring	0.732	0.716	0.724		Spring	724
Summer	720	831		Summer	1.014	1.026	1.020		Summer	1020
Total	2840	3241
Avg	710	810.25		Average Annual demand			4000
				Average Demand/Season			1000
Problem 11:
	Training Hours	Sales (in Ks)
	6	11
	10	25
	12	40
	12	36
	15	50
	18	64
CorrCoeff	0.9886791509	There is a direct positive correlation between training and sales.
Slope	4.4545454545
Intercept	-16.6

Sales (in Ks) 6 10 12 12 15 11 25 40 36 50

13,15,17

Chapter 8-Problem 13
Month	Avg Temp	Attendance
1	24	43
2	41	31
3	32	39
4	30	38
5	38	35
6	45	29.4
Slope	-0.65
Intercept	58.65
CorrCoeff	-0.9817730527	The two are inversely related, meaning that temperature negatively affects attendance.
Problem 15
Week	Cheesebrg Sales	Simple Avg	Abs Error	3 Pd Mov Avg	Abs Error	a=0.30	Abs Error
1	354
2	345
3	367
4	322			355.3333333333	33.3333333333
5	356			344.6666666667	11.3333333333	328.0
6	368	348.8	19.2	348.3333333333	19.6666666667	336.4	31.6
	MAD		19.2		19.6666666667		31.6
Problem 17:
Month	Attendance
1	3.4
2	3.9
3	4.5
4	5
5	5.8
6	5.9
7	6.5
8	6.7
9	7.4
10	7.9
Slope	0.4893
Intercept	3.0107

19,21,23,25

Chapter 8: Problem 19
			3 mo WA	Naïve Mtd
Month	Pies	Weight	Forecast	Forecast
September	230	0.1
October	304	0.3
November	415	0.6
December	420		363.2	415
		MAD	56.8	5
Problem 21
Period	Actual Demand	Forecast1	Abs Error	Forecast2	Abs Error
1	90	78	12	87	3
2	87	85	2	88	1
3	92	84	8	90	2
4	95	92	3	97	2
5	98	100	2	102	4
6	98	102	4	101	3
			31		15
		MAD	5.17		2.5
Problem 23:						Problem 25:
Month	Sales					Advertising	Sales
1	239		slope	7.42		17378	28830
2	248		intercept	243.08		19143	36149
3	256		corrcoeff	0.97		19928	38552
4	260					23283	45186
5	271					25437	47404
6	280					28237	48758
7	295					31760	52060
8	305					34379	55039
9	310					37051	57880
10	335					39046	57601
11	348					44995	67643.80
12	353
13	355					slope	1.20
14	368					intercept	13699.00
15	379					corrcoeff	0.96
16	358
17	369
18	378
19	367
20	383
21	394
22	393
23	405
24	412
25	429