Dự báo với chuỗi thời gian trong Ms Excel

Trong kinh doanh rất thường gặp các chuỗi thời gian như doanh thu hàng tháng, sản lượng bán hàng tháng,… Việc dự báo giá trị tương lai của chuỗi thời gian là một việc quan trọng và đặc biệt hữu ích. Tuy nhiên, tính toán dự báo thủ công (tính trên giấy) khá khó khăn và mất thời gian, sử dụng các phần mềm thống kê chuyên dụng (như SPSS, SATA, EView, R,…) thì mất nhiều thời gian học (vì phải học từ cơ bản trở lên). Giải pháp đơn giản nhất, tận dụng kiến thức có sẵn) mà vẫn đảm bảo độ chính xác là sử dụng Ms Excel.

Tóm lược lý thuyết

Chuỗi thời gian là một chuỗi giá trị được ghi nhận theo thời gian (ngày, tuần, tháng, quý, năm).

Có nhiều phương pháp dự báo với chuỗi thời gian, ở đây chỉ trình bày phương pháp phân rã (Decomposition). Theo phương pháp này, chuỗi thời gian có thể được mô tả theo dạng tích như sau:

Y= St x Tt x It

Trong đó:
+ Y là giá trị quan sát (observed value)
+ S là yếu tố thời vụ (seasonality)
+ T là yếu tố xu hướng (trend)
+ I là yếu tố bất thường (irregularity)

Quá trình thực hiện dự báo:
1. Làm mịn bằng một trong các phương pháp: số trung bình động, đa thức, hàm mũ đơn giản,… Đơn giản nhất là dùng số trung bình động kép: bậc 4 (ký hiệu cột: MA4) sau đó là bậc 2 (ký hiệu cột: MA2) để cho thành phần T.
2. Xác định S (bằng cách lấy trung bình các kỳ  S x I).  St sau đó được làm mịn bằng trung bình động bậc 2 và hiệu chỉnh – loại bỏ I (theo tuần, tháng, quý,…).
3. Khử tính thời vụ (deseasonalize):  Y/S.
4. Hồi quy để xác định phương trình đường xu hướng  T = a + bt.
5. Dự báo Yt = T x S

Thực hiện dự báo trên Ms Excel (2013)

Có dữ liệu chuỗi thời gian như sau:

timeseries01

Thực hiện dự báo:

timeseries08

1. Làm mịn

E5 = MA(4) = AVERAGE(D3:D6) – copy công thức này tới E23
F6 = CA = AVERAGE(E5:E6) – copy công thức này tới F23

2. Xác định St

+ Tính St x It = G3 = Y / CA = D6/F6

+ Tính S cho từng quý = N3 =AVERAGEIF($C$6:$C$23;M3;$G$6:$G$23)

+ Hiệu chỉnh S (do chuỗi theo quí nên hiệu chỉnh để tổng St = 4):

N7 =SUM(N3:N6)
N8 =(4-N7)/4
O3 =N3+$N$8 – copy công thức này tới O6

+ Tính St = H3 =VLOOKUP(C3;$M$3:$O$6;3;0) – copy công thức tới H24

3. Khử tính thời vụ (deseasonalize):

I3 =  Yt / St = D3/H3 – copy công thức tới I24

4. Hồi quy

Thực hiện hồi quy T theo t: T = a + bt

+ Thêm Add-In Analysis ToolPak: Chọn File => Options => Add-Ins:

timeseries02

Kích chuột vào Go.. (ở phía dưới):

timeseries03

Đánh dấu ô Analysis ToolPak => Ok.

+ Chạy hồi quy: Chọn Data => Data Analysis:

timeseries04

Màn hình sau xuất hiện:

timeseries05

Chọn Regression => Ok:

timeseries06

Chọn 2 vùng dữ liệu (Input Y Range – màu xanh dương và Input X Range – màu đỏ) và ô sẽ lưu kết quả hồi quy (màu xanh lá cây) như trên hình => Ok.

Kết quả hồi quy như sau:

timeseries07

Các thông số cần lưu tâm:

+ Intercept: tung độ gốc (hệ số a)
+ X Variable 1: độ dốc (hệ số b)
+ Adjusted R Square (hệ số xác định): càng gần 1 càng tốt nhưng không được bằng 1. Nếu quá bé (gần 0) thì cần xem xét lại số liệu và phương pháp tính.
+ Significance F (độ tin cậy của kiểm định F): kiểm định F kiểm định sự phù hợp của mô hình. Sig. F < mức ý nghĩa là mô hình đạt (phù hợp).
+ P-Value: P-Value < mức ý nghĩa (<0,05 tức độ tin cậy 95%) thì chấp nhận hệ số hồi quy (a, b).

Như vậy ta có phương trình hồi quy như sau: T = a + bt = 31,343 + 1,387t = H42 + H43 x t

Tính T = J3 =$H$42+$H$43*A3   (trong đó H42 là a, H43 là b)

5. Dự báo Yt = T x S

+ Tính Yt = T x St  = K3 = J3*H3 – copy công thức tới J24

+ Thực hiện dự báo cho quý 3, 4 năm 2005:

timeseries09

Thêm 2 dòng 25 và 26 như hình trên trong đó cột H, J, K copy công thức từ dòng 24 xuống. giá trị trong ô K25, K26 chính là dự báo doanh thu cho quý 3 và quý 4 năm 2005.

Advertisements

14 thoughts on “Dự báo với chuỗi thời gian trong Ms Excel

    • Tôi đã hiểu ý bạn.
      St là thành phần thời vụ, theo số liệu trên, doanh thu có tính mùa vụ theo quý.
      Nếu dữ liệu của bạn theo năm thì có 2 trường hợp:
      1/ Không có tính mùa vụ => St=1
      2/ Có tính mùa vụ => bạn xác định chu kỳ mùa vụ của dữ liệu (2 năm hay 3 năm,…) và có thể đánh số 1, 2, 3, (chu kỳ 3 năm) và dùng công thức như trong bài.

    • Cột CA (Tt) là cột đã qua 2 lần làm mịn, nó gần thành đường thẳng; Tt (sau hồi quy) chính là đường xu hướng và nó gần trùng với CA.
      Như vậy, CA (Tt) chưa khử It còn Tt sau hồi quy thì nó là đường thẳng tuyệt đối rồi => Tt trong Yt/(Tt*St) phải là Tt đã loại bỏ hoàn toàn St và It tức là dùng Tt sau hồi quy.

  1. bạn ơi cho hỏi muốn chèn vào trục hoành thời gian giống như trong biểu đồ ở trên là quý ở dưới là năm thì làm sao bạn? Bí quá mà không biết hỏi ai?

    • Sau khi chèn chart vào, bạn vào Select Data, tại mục Horizontal (Category) Axis Lables kích chuột vào Edit => chọn vùng dữ liệu năm và quý => Ok

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Đăng xuất / Thay đổi )

Connecting to %s