**Unformatted text preview: **3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A
1
2
3
4
5
6 B G H Ft (Yt - Ft)2 Ft (Yt - Ft)2 Ft (Yt - Ft)2 Week Actual
Sales 2-wk Mov. Ave Sq Error 3-wk Mov. Ave. Sq Error Expo. Smoothing Sq Error 17 2 9
10 3
4 21
19
23 11 5 18 12 6 16 13 7 20 14 8 18 15 9 22 16 10 20 17 11 15 18 12 22
? 25
26 F Yt 8 21
22
23
24 E t 1 20 D Smoothing Methods Example: Gasoline Sales 7 19 C How
How to
to create
create the
the data
data table:
table: -- Enter
Enter column
column headings
headings :: Alpha,
Alpha, Forecast,
Forecast, MSE
MSE
in
cells
J6:L6.
in
cells
J6:L6.
The
data
show
the
amount
of
gasoline
(in
1000's
of
gallons)
sold
by
a
gasoline
distributor
over
the
past
12
weeks.
We
want
to
make
a
forecast
for
the
sales
The data show the amount of gasoline (in 1000's of gallons) sold by a gasoline distributor over the past 12 weeks. --We
want
make
a forecast
for
Enter
the
trial
alpha
values:
0.2,
...,0.9
Enter
theto
trial
alpha
values: 0.1,
0.1,the
0.2,sales
...,0.9 in
in
for
the
13th
week.
for
the
13th
week.
J8:J16.
J8:J16.
MSE
-- Enter
Enter formulas
formulas =G19
=G19 and
and =H21
=H21 just
just below
below
Forecast
Forecast and
and MSE
MSE headings.
headings.
-- Select
Select the
the data
data table
table range
range J7:L16.
J7:L16.
-- Data
Data tab
tab >> What-If
What-If Analysis
Analysis >> Data
Data Table
Table
[Mac:
[Mac: Data
Data menu
menu >> Data
Data Table]
Table]
-- Leave
Leave Row
Row input
input cell
cell blank
blank and
and enter
enter G5
G5 for
for
Column
Column input
input cell,
cell, then
then hit
hit OK.
OK.
-- IfIf the
the values
values given
given in
in different
different rows
rows are
are the
the
same,
hit
F9
(recalculate).
[Mac:
Cmd
same, hit F9 (recalculate). [Mac: Cmd =]
=]
13 Smoothing 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A
1 B C D E F G H I J K L M Smoothing Methods Example: Gasoline Sales 2
3 Yt t Ft (Yt - Ft)2 Ft (Yt - Ft)2 Ft 4
5
6 Expo. Smoothing Data Table Sq Error Alpha 0.2
Actual
Sales Week 2-wk Mov.
Ave Sq Error 3-wk Mov.
Ave.
Sq Error 7 1 17 8 2 21 9 3
4
5
6 19
23
18
16 19.000
20.000
21.000
20.500 0.000
9.000
9.000
20.250 19.000
21.000
20.000 7
8
9 20
18
22 17.000
18.000
19.000 9.000
0.000
9.000 17 10
11 20
15 20.000
21.000 18 12 22 17.500 19 13 ? 10
11
12
13
14
15
16 20
21 MSE
22
23
24 (Yt - Ft)2 Expo.
Smoothing Forecast MSE 19.185
17.000 16.000 0.1 16.000
9.000
16.000 17.800
18.040
19.032
18.826 1.440
24.602
1.065
7.984 0.2
0.3
0.4
0.5 19.000
18.000
18.000 1.000
0.000
16.000 18.260
18.608
18.487 3.026
0.370
12.343 0.6
0.7
0.8 0.000
36.000 20.000
20.000 0.000
25.000 19.189
19.351 0.657
18.935 0.9 20.250 19.000 9.000 18.481 12.382 18.500 19.000
12.500 19.185
10.222 8.982 10.222 The
The data
data show
show the
the amount
amount of
of gasoline
gasoline (in
(in 1000's
1000'sof
of gallons)
gallons) sold
sold by
by aa gasoline
gasolinedistributor
distributor
over
over the
the past
past 12
12 weeks.
weeks. We
Wewant
want to
to make
makeaa forecast
forecast for
for the
the sales
sales for
for the
the 13th
13th week.
week. 25
26
27
28
29
30
31
32
33
34
35
36
37 Gasoline Sales
24
22
20 Smoothing - Completed 8.982 How
How to
to create
create the
the data
data table:
table:
-- Enter
Enter column
column headings
headings::Alpha,
Alpha, Forecast,
Forecast, MSE
MSE
in
in cells
cellsJ6:L6.
J6:L6.
-- Enter
Enter the
thetrial
trial alpha
alpha values:
values: 0.1,
0.1, 0.2,
0.2, ...,0.9
...,0.9 in
in
J8:J16.
J8:J16.
-- Enter
Enter formulas
formulas=G19
=G19 and
and =H21
=H21just
just below
below
Forecast
Forecast and
and MSE
MSEheadings.
headings.
-- Select
Select the
thedata
data table
tablerange
rangeJ7:L16.
J7:L16.
-- Data
Data tab
tab >>What-If
What-If Analysis
Analysis >>Data
DataTable
Table
[Mac:
[Mac: Data
Datamenu
menu >>Data
Data Table]
Table]
-- Leave
Leave Row
Row input
input cell
cellblank
blankand
and enter
enterG5
G5for
for
Column
Column input
input cell,
cell, then
then hit
hit OK.
OK.
-- IfIf the
thevalues
values given
given in
in different
different rows
rows are
are the
the
same,
same, hit
hit F9
F9(recalculate).
(recalculate). [Mac:
[Mac:Cmd
Cmd =]
=] 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
N
1
2
3
4
5
6
7
8 O P Q R S T U Smoothing Methods
- Used to smooth out irregular, random fluctuations.
- For fairly steady time series data with no significant trends, seasonality, or cycles.
- Appropriate for short-term forecasts. We use the following notations in the forecasting formulas:
Ft = forecast in period t
Yt = actual data value in period t 9
10
11
12
13
14
15
16
17
18 1. Moving Averages k-period moving average, Ft = (most recent k observations)
k 2. Exponential Smoothing Ft Yt 1 (1 ) Ft 1
(To start, we let F2 = Y1.) 19
20
21
22
23
24
25
26
27
28 Mean Squared Error (MSE)
MSE is one way to measure the accuracy of a forecasting method. The formula is: (Y F )
MSE t 2 t n k where smoothing constant (0 1)
where n-k = number of terms in the numerator.
(n = # of observations, k = number of observations not counted)
When comparing different methods, the best one is the one with the smallest MSE. 29
30
31
32
33
34
35
36
37
Smoothing - Completed V 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A B C D 1 Trend Projection Example: Bicycle Sales 2
3 Data 4
5
6 Year
1 7
2
8
3
9
4
10
5
11
6
12
7
13
8
14
9
15
10
16
17
18 Results
19 Year
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 E F G H I J K L M The
The data
data show
show bicycle
bicycle sales
sales of
of aa particular
particular manufacturer
manufacturerover
over the
the past
past 10
10 years.
years.
Forecast
Forecast the
the sales
sales for
foryears
years 11,
11, 12,
12, and
and 13.
13. Sales (1000's)
21.6 Trend Projection
Used when time series data shows a consistent,
The method of least squares (as in regression) d 22.9
25.5
21.9
23.9
27.5
31.5
29.7
28.6
31.4 Ft b0 b1t
where
b0 intercept
b1 slope The independent variable = time period, t
The dependent variable = observed value in the
Does not follow every up and down movement
It is only for estimating trends. Sales
11
12
13 How
How to
to make
make aascatter
scatter chart
chart with
with trend
trend equation:
equation:
1.
1. Create
Create aa scatter
scatter chart
chart
-- Highlight
Highlight the
the two
two columns
columns A6:B15.
A6:B15.
-- Insert
Insert >> Scatter
Scatter (in
(in Charts
Charts Group).
Group). [Mac:
[Mac: Charts
Charts ribbon]
ribbon]
Choose
Choose the
the first
first one
one with
with only
only markers.
markers.
Remember
Remember the
the data
data range
range should
should have
have XX column
column followed
followed by
by YY column.
column.
Optional
Optional --When
When there
there isis aa lot
lot of
of empty
empty space
space on
on your
your chart
chart above
above or
or below
below the
the data
data
points,
points,you
you may
may want
want to
to change
change the
the range
range of
of vertical
vertical axes.
axes. For
Forexample,
example,to
to have
have the
the
vertical
vertical axis
axis start
start at
at 15
15 and
and end
endat
at 35,
35,do
do the
thefollowing:
following:
-- Click
Click on
on Format
Format tab
tab under
under Chart
Chart Tools.
Tools.
-- In
top
Inthe
the Current
Current Selection
Selection group,
group,click
click on
on the
the down
down arrow
arrow on
on the
the
top to
to choose
choose
Vertical
Vertical (Value)
(Value)Axis.
Axis.
-- Click
Click on
on Format
Format Selection
Selection just
just below.
below.
-- Under
Under Axis
Axis Options:
Options:
Minimum:
Minimum: enter
enter 15
15
Maximum:
Maximum: enter
enter35
35
2.
2. Add
Add trend
trend line
line with
withequation
equation
-- Right
Right click
click on
onone
oneof
of the
the data
data points
points to
to select
select the
the data
data >> Add
Add Trendline....
Trendline....
-- Check
Check "Display
"Display equation
equation on
on chart"
chart" >> Close.
Close.
(With
(With some
some Excel
Excel versions,
versions, you
you need
need to
togo
go to
to Options
Options tab
tab to
to check
check "Display
"Display equation
equation
on
on chart.")
chart.") Trend 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A
1 B C D E F Trend Projection Example: Bicycle Sales
Data 6 Year
1 Sales (1000's)
21.6 7 2 22.9 8
9 3
4 25.5
21.9 10
11 5
6 23.9
27.5 12 7 31.5 Adjusted R Square
Standard Error 13 8 29.7 Observations 14 9 28.6 15 10 31.4 21
22 J K L M Trend Projection
Used when time series data shows a consistent,
The method of least squares (as in regression) d Regression Statistics
Multiple R
0.8745261666
R Square
0.7647960161 Ft b0 b1t 0.7353955181
1.9589538024 where
b0 intercept 10 b1 slope ANOVA
df 20 I SUMMARY OUTPUT 16
17
18 Results
19 Year H The
The data
datashow
show bicycle
bicycle sales
salesof
of aaparticular
particular manufacturer
manufacturer over
over the
the past
past 10
10 years.
years.
Forecast
Forecast the
the sales
sales for
for years
years 11,
11, 12,
12, and
and 13.
13. 2
3
4
5 G Sales
11
12 32.5
33.6 13 34.7 SS Regression
Residual 1
8 99.825
30.700 Total 9 130.525 Coefficients
Intercept 23 MS Year Standard Error F 99.825
3.838 t Stat 26.013 P-value The independent variable = time period, t
The dependent variable = observed value in the
Does not follow every up and down movement
It is only for estimating trends. Significance F
0.001 Lower 95% Upper 95% Lower 95.0% Upper 95.0% 20.4 1.338 15.244 0.000 17.314 23.486 17.314 23.486 1.1 0.216 5.100 0.001 0.603 1.597 0.603 1.597 24
25
26
27 Bicycle Sales 28
29 35 30
31 30 32
33
34 How
How to
to make
make aa scatter
scatterchart
chart with
with trend
trendequation:
equation:
1.
1. Create
Create aa scatter
scatter chart
chart
-- Highlight
Highlightthe
the two
two columns
columns A6:B15.
A6:B15.
-- Insert
Insert >>Scatter
Scatter (in
(in Charts
Charts Group).
Group). [Mac:
[Mac: Charts
Charts ribbon]
ribbon]
Choose
Choose the
the first
first one
one with
with only
only markers.
markers.
Remember
Remember the
the data
data range
range should
should have
have XX column
column followed
followed by
by YY column.
column. Sales Optional
Optional -- When
When there
there isis aalot
lot of
of empty
empty space
space on
on your
your chart
chart above
above or
or below
below the
the data
data
points,
points, you
you may
may want
want to
to change
change the
the range
range of
of vertical
vertical axes.
axes. For
For example,
example, to
to have
havethe
the
vertical
vertical axis
axis start
start at
at 15
15 and
and end
end at
at 35,
35, do
do the
the following:
following:
-- Click
Click on
on Format
Format tab
tab under
under Chart
Chart Tools.
Tools.
-- In
top
In the
the Current
Current Selection
Selection group,
group,click
clickon
onthe
thedown
downarrow
arrowon
onthe
the
topto
tochoose
choose
Vertical
Vertical (Value)
(Value) Axis.
Axis.
-- Click
Click on
on Format
Format Selection
Selection just
just below.
below.
-- Under
Under Axis
Axis Options:
Options:
Minimum:
Minimum: enter
enter 15
15
Maximum:
Maximum: enter
enter 35
35 f(x) = 1.1x + 20.4
R² = 0.7647960161 25
20
15 0 1 2 3 4 5 6 7 8 9 10 11 12 Year Trend - Completed 13 2.
2. Add
Add trend
trend line
line with
with equation
equation
-- Right
Right click
click on
on one
one of
of the
the data
datapoints
points to
to select
select the
the data
data >> Add
Add Trendline....
Trendline....
-- Check
Check "Display
"Display equation
equation on
on chart"
chart" >>Close.
Close.
(With
(With some
some Excel
Excelversions,
versions, you
you need
need to
to go
go to
to Options
Options tab
tab to
to check
check "Display
"Display equation
equation
on
onchart.")
chart.") 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A
1
2
3
4
5
6
7
8
9 B C D E F G H I J K L M N Seasonality with Trend: Smartphone Sales Data
Data consists
consists of
of smartphone
smartphone sales
sales (in
(in thousands
thousands of
of units)
units) for
for aa particula
particul
the
the past
past four
four years.
years.
(a)
(a) Obtain
Obtain quarterly
quarterly forecasts
forecasts for
for year
year 5.
5.
(b)
(b) Out
Out of
of the
the four
four quarters,
quarters, which
which quarter
quarter experiences
experiences the
the highest
highest dem
dem
quarter
quarter experiences
experiences the
the lowest
lowest demand?
demand?
(c)
(c) IfIf you
you compare
compare the
the sales
sales in
in Quarter
Quarter 33 this
this year
year and
and quarter
quarter 33 next
next ye
ye
smartphone
smartphone would
would you
you expect
expect to
to sell
sell Quarter
Quarter 33 of
of next
next year?
year?
(d)
(d) Compared
Compared to
to Quarter
Quarter 2,
2, how
how many
many more
more smartphones
smartphones would
would you
you ee
Quarter
Quarter 3?
3? Data & Computation
Sales
(1000s)
(Y)
4.8
4.1
6
6.5
5.8
5.2
6.8
7.4
6
5.6
7.5
7.8
6.3
5.9
8
8.4 10
Year
Quarter
Q1
11
1
1
12
1
2
13
1
3
14
1
4
15
2
1
16
2
2
17
2
3
18
2
4
19
3
1
20
3
2
21
3
3
22
3
4
23
4
1
24
4
2
25
4
3
26
4
4
27
28
29
30
31
32
b0
b1
33 Coeff's
34
35 Manual Computation of Forecast
36
37
38
Qtr
t
Ft
39 Year
5
1
17
40
2
18
41
3
19
42
43
4
20 Q2 Q3 Time (t) Forecast Regression for Seasonality and Trend
For data with seasonality and trend, we need to take into account
both the time period t and
the season of the year (e.g., quarter) in our regression.
Quarter is a categorical variable, not a quantitative variable
(quarter numbers, 1, 2, 3, 4, do not represent meaningful quantities).
So in place of Quarter number, we need to use dummy variables. For each quarter, we designate
a dummy variable that is 1 if we are in that quarter, and 0 if not.
We need only 3 dummy variables, say, for quarters 1, 2, 3.
Quarter 4 could be a “baseline” category (all the dummy variables equal to
Specifically, the dummy variables are: Qtr1t 1 if time period t corresponds to Qtr 1; 0 otherwise
Qtr 2t 1 if time period t corresponds to Qtr 2; 0 otherwise
b2 b3 b4 Qtr 3t 1 if time period t corresponds to Qtr 3; 0 otherwise
With t = time period, below is the regression equation. Ft b0 b1 * Qtr1t b2 * Qtr 2t b3 * Qtr 3t b4 * t Trend and Seasonality 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx
A
1
2
3
4
5
6
7
8 B C E F G H Sales
(1000s)
(Y) Year Quarter
1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
5
5
5
5 1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4 Q1
4.8
4.1
6
6.5
5.8
5.2
6.8
7.4
6
5.6
7.5
7.8
6.3
5.9
8
8.4 b0
Coeff's Q2
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0
0 b1
6.069 Q3
0
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0 b2
-1.363 Time (t)
0
0
1
0
0
0
1
0
0
0
1
0
0
0
1
0
0
0
1
0 b3
-2.034 I 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 Forecast
4.851
4.326
6.201
6.651
5.434
4.909
6.784
7.234
6.016
5.491
7.366
7.816
6.599
6.074
7.949
8.399
7.181
6.656
8.531
8.981 0.146 Ft 6.069 1.363* Qtr1t 2.034 * Qtr 2t 0.304 * Qtr3t 0.146* t
Qtr
5 t
1
2
3
4 c
d L M N Regression Statistics
Multiple R
0.988
R Square
0.976
Adjusted R S
0.968
Standard Erro
0.217
Observations
16
ANOVA
df
Regression
Residual
Total Intercept
Q1
Q2
Q3
Time (t) Ft
17 6.069 - 1.363(1) - 2.034(0) - 0.304(0) + 0.146(17) = 6.069 - 1.363 - 0 - 0 + 2.482 = 7.188
18 6.069 - 1.363(0) - 2.034(1) - 0.304(0) + 0.146(18) = 6.069 - 0 - 2.034 - 0 + 2.628 = 6.663
19 6.069 - 1.363(0) - 2.034(0) - 0.304(1) + 0.146(19) = 6.069 - 0 - 0 - 0.304 + 2.774 = 8.539
20 6.069 - 1.363(0) - 2.034(0) - 0.304(0) + 0.146(20) = 6.069 - 0 - 0 - 0 + 2.92 = 8.989 0.583
1.875 K Regression with Data Analysis
SUMMARY OUTPUT b4
-0.304 Manual Computation of Forecast Year J Data
Data consists
consists of
of smartphone
smartphone sales
sales (in
(in thousands
thousands of
of units)
units) for
for aa particular
particular manufacturer
manufacturer for
for the
the past
past four
four years.
years.
(a)
(a) Obtain
Obtain quarterly
quarterly forecasts
forecasts for
for year
year5.
5.
(b)
(b) Out
Out of
of the
the four
fourquarters,
quarters, which
which quarter
quarterexperiences
experiences the
the highest
highest demand?
demand? Which
Which quarter
quarter experiences
experiences the
the
lowest
lowest demand?
demand?
(c)
(c) IfIf you
you compare
compare the
the sales
sales in
in Quarter
Quarter 33 this
this year
year and
and quarter
quarter 33 next
next year,
year, how
how many
many more
more smartphone
smartphone would
would
expect
expect to
to sell
sellQuarter
Quarter 33 of
of next
next year?
year?
(d)
Compared
to
Quarter
2,
how
many
more
smartphones
would
you
expect
to
sell
in
Quarter
3?
(d) Compared to Quarter 2, how many more smartphones would you expect to sell in Quarter 3? Data & Computation 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 D Seasonality with Trend: Smartphone Sales 0.5825
1.875
Trend and Seasonality-Complete SS
4
11
15 21.248
0.516
21.764 Coefficients Standard Error
6.069
0.162
-1.363
0.157
-2.034
0.155
-0.304
0.154
0.146
0.012 MS
5.312
0.047 F
113.158 t Stat
37.347
-8.657
-13.112
-1.981
12.023 P-value
0.000
0.000
0.000
0.073
0.000 The result from a manual calculation is slightly different from that
from Excel formulas because we used here the coefficients that
were rounded to the 3rd decimal digit. Smartphone Sales
10 9 8 7 6 Sales (thousands) (Y)
Forecast 5 4 3 2 1 0 1 2 3 4 5 6 7 8 9 10 11 12 Time period (t) 13 14 15 16 ...

View
Full Document

- Fall '17
- Forecasting, Regression Analysis, Time series analysis, Smoothing