Question

Using Excel

John Taylor Salons want to forecast monthly customer demand from June through August using trend adjusted exponential smoothing. Given alpha (a) 0.20, Beta (B) -0.40, the Forecast for May 45 (FMay-45) customers, and the trend for May 0 (Tmay-0), forecast a FIT (forecast including trend) for the months of June through August. 3. Month Actual Sales May June July August 50 61 73 80 Jay Sharp Guard wants to compare the accuracy of two methods that it has used to forecast monthly expenses. Forecast for monthly expenses from September to November are shown below against the actual expenses. Which method provided the best forecast accuracy? 4. Month Expenses (Smillion) Method A Forecast (Smillion) Method B Forecast (Smillion) September October November 3.5 4.1 5.3 3.1 3.8 5.1 4 5

Answer 1

3. Forecast is calculated using Excel as follows:

1 Trend Adjusted Exponentially Smoothed Forecast Ft = FIT-1 +α (At-1-FIT ) 4. α= 0.20 FIT, = Ft+T, P- 0.40 6 Actual Forecasting 7 Month(t Sales Forecast Trendincluding 50 61 73 80 (T)Trend (FIT) 0.0 0.4 1.6 3.3 45.00 46.40 50.89 58.65 May 45.00 46.00 49.32 55.31 June 10 July 11 August 12 13 14 15

EXCEL FORMULAS:

Trend Adjusted Exponentially Smoothed Forecast
Ft =	FITt-1 +α (At-1 – FITt-1)
Tt =	β(Ft – Ft-1) + (1- β) Tt-1		α =	0.2
FITt =	Ft + Tt		β =	0.4
Month (t)	Actual Sales    (At)	Forecast (Ft)	Trend (Tt)	Forecasting including Trend (FITt)
May	50	45	0	=C8+D8
June	61	=E8+$E$4*(B8-E8)	=$E$5*(C9-C8)+(1-$E$5)*D8	=C9+D9
July	73	=E9+$E$4*(B9-E9)	=$E$5*(C10-C9)+(1-$E$5)*D9	=C10+D10
August	80	=E10+$E$4*(B10-E10)	=$E$5*(C11-C10)+(1-$E$5)*D10	=C11+D11