RMBI 1020 Business Intelligence

for Data-Driven Decisions

Tutorial 06 – Time Series Forecast by Excel

General Information:

• This is a hands-on lab using Excel to forecast Hang Seng Index using Simple Exponential
Smoothing and Holt’s Double Exponential Smoothing methods.
• The tools we will use is Microsoft Excel 2013/2016/Office 365 and Excel Solver Adds-In.
• After you are done, you need to submit you Excel file onto Canvas, under Tutorial 6 in Assignment
Module.
Deadline: see Canvas
Weighting: 1% in Tutorial Exercises (total 10%)

Tasks 1: Get Ready

1. Go to Canvas to download tut06_HSI.xlsx file. Double click the file to open it in Excel.
The file contains 112 days of Hang Seng Index values extracted from Yahoo! Finance
(https://hk.finance.yahoo.com/q/hp?s=%5EHSI). The task is to use these historical data to
forecast the Hang Seng Index Closing Price for the week of Nov 13-19, 2018.

The ‘HSI 2018’ worksheet contains the Hang Seng Index data from 06/1/2018 to 11/12/2018
(mm/dd/yyyy). Beside the Date column, other columns are listed below. In the exercise, we will
only use the Closing Price for forecast (i.e., column F).
- Open: the opening price of Hang Seng Index of that day
- High: the highest price of Hang Seng Index of that day
- Low: the lowest price of Hang Seng Index of that day
- Close: the closing price of Hang Seng Index of that day
- Volume: the transaction volume in Hong Kong Exchange stock market

Tasks 2: Simple Exponential Smoothing

2. Go to cell J1 and enter 0.5, which tentatively sets a value of 0.5 for parameter α in simple
Exponential Smoothing method.
3. Go to cell J3, enter =F3 to set the forecast for the first period (i.e., to initialize 𝑦𝑦�1 ).
4. Referring to the Simple Exponential Smoothing equation shown as below, enter a formula into
cell J4 to forecast the current period’s HS index. Make sure your formula uses the correct
absolute/relative cell references so that it can be easily copied and pasted for other periods.
𝑦𝑦�𝑡𝑡+1 = 𝛼𝛼𝑦𝑦𝑡𝑡 + (1 − 𝛼𝛼)𝑦𝑦�𝑡𝑡

5. Copy the formula in cell J4 into cells J5:J115, which calculate the forecast values for days in Jun-
Nov till Nov 13, 2018.
6. Note that the Simple Exponential Smoothing only works for 1-step ahead forecast, because it
needs the previous day’s actual value to predict the next day’s value. Assuming that we don’t
have the HSI values for Nov 13 - Nov19, therefore, we enter =J115 into cells J116:J119.
 RMBI 1020 Business Intelligence
for Data-Driven Decisions

7. Referring to the MSE (Mean Square Error) equation below, enter a formula into cell K3 to
calculate the MSE for forecasts in J4:J114. Remember to press Ctrl+Shift+Enter if you are using
an array formula. [Note: if your formula is correct, it should return 160381.6 when alpha in J1 is
set to 0.5.]
∑𝑛𝑛𝑡𝑡=𝑘𝑘+1 𝑒𝑒𝑡𝑡 2
𝑀𝑀𝑀𝑀𝑀𝑀 =
𝑛𝑛 − 𝑘𝑘
[What happened so far?] Now we have built a simple exponential smoothing forecast model with
α=0.5.
[What will happen next?] We will build a double exponential smoothing forecast model with α and β
values.

Tasks 3: Holt’s Double Exponential Smoothing

8. Go to cells N1 and O1, enter 0.5 for both cells, which tentatively sets a value 0.5 for parameter
α and parameter β in Holt’s Double Exponential Smoothing method.
9. Go to cell N3, enter =F3 to set the level component for the first period (i.e., to initialize 𝑙𝑙1 ).
10. Go to cell O3, enter =F4-F3 to set the trend component for the first period (i.e., to initialize𝑏𝑏1 ).
11. Go to cell P4 and enter the formula =N3+O3, which calculates the forecast value for the
current day.
12. Referring to Holt’s Double Exponential Smoothing equation below for the estimation of level
component, enter a formula into cell N4. Make sure your formula uses the correct
absolute/relative cell references so that it can be easily copied and pasted for other periods.
𝑙𝑙𝑡𝑡 = 𝛼𝛼 ∗ 𝑦𝑦𝑡𝑡 + (1 − 𝛼𝛼) ∗ 𝑦𝑦�𝑡𝑡

13. Referring to Holt’s Double Exponential Smoothing equation below for the estimation of trend
component, enter a formula into cell O4. Make sure your formula uses the correct
absolute/relative cell references so that it can be easily copied and pasted for other periods.
𝑏𝑏𝑡𝑡 = 𝛽𝛽 ∗ (𝑙𝑙𝑡𝑡 − 𝑙𝑙𝑡𝑡−1 ) + (1 − 𝛽𝛽) ∗ 𝑏𝑏𝑡𝑡−1

14. Copy the formulas in cells N4:P4 into cells N5:P114, which calculate the forecast values for days
in Jun-Nov till Nov 12, 2018.
15. Note that Holt’s Double Exponential Smoothing method can work for p-period ahead forecast,
where 𝑦𝑦�𝑡𝑡+𝑝𝑝 = 𝑙𝑙𝑡𝑡 + 𝑝𝑝 ∗ 𝑏𝑏𝑡𝑡 . Referring to this equation, enter formulas for p-period ahead
forecast into cells P115:P119. [Note: you can use the period IDs in column A to get the “p” by
some simple calculation.]
16. Copy the formula in cell K3 to cell Q3, which calculates the Mean of Squared Error of the Holt’s
method. Make sure the cell references are correct in your formula.
[What happened so far?] Now we have built a double exponential smoothing forecast model with
α=0.5 and β=0.5.
[What will happen next?] We will use Excel Solver to find the optimal parameters of these two
models which result in a minimal MSE.
 RMBI 1020 Business Intelligence
for Data-Driven Decisions

[Homework]
1) Use Excel Solver Adds-In to find an optimal value for α (i.e., cell J1) that minimizes the
MSE (i.e., cell K3).

Note that the constraint for this optimization problem is 0 ≤ 𝛼𝛼 ≤ 1.

2) Use Excel Solver Adds-In to find optimal values for α and β (i.e., cell N1 and O1) that
minimize the MSE (i.e., cell Q3).

Note that there are two constraints for this optimization problem, which are 0 ≤ 𝛼𝛼 ≤ 1 and 0 ≤
𝛽𝛽 ≤ 1. You may choose Evolutionary Solving method. (If your solver setting is right, it probably
takes 1-2 minute for the Solver to find a solution).

3) Suppose the actual Hang Seng Index values on Nov13 - Nov19 are now known. Enter
25792.87, 25654.43, 26103.34, 26183.53, 26372.0 into cells F115:F119.

4) In cell L2 and R2, count and show how many days that the forecast values of 6/4/2018
to 11/19/2018 (mm/dd/yyyy), using simple Exponential Smoothing and using Holt’s Double
Exponential Smoothing) are within ±1% of the actual values.
Hint: you can enter IF() formulas in column L and R to test whether the forecast values are
within ±1% of the actual values, and then use COUNTIF() function to count the days. (Fill the
answers in the cell L2 and R2.)

After you are done, remember to submit your Excel file onto Canvas before the deadline.