Blog của Lê Văn Luật

Đời người thì có hạn mà sự học thì vô hạn!

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 comments on “Dự báo với chuỗi thời gian trong Ms Excel

  1. Ẩn danh
    11/05/2016

    bạn ơi. Mình đang cần gấp cách tính này để dự báo. Tuy nhiên mình tính cho năm. Vậy cốt tính St mình sẽ tính như thế nào? mail của mình haidbtv311@gmail.com

    • lvluat
      11/05/2016

      Thực ra năm không quan trọng – chỉ để cho ta … nhìn thôi – quan trọng là cột t (số thứ tự từ 1, 2….)

  2. Ẩn danh
    11/05/2016

    em không hiểu lắm! Thế cột tính St theo năm thì làm thế nào ạ? 😦

  3. Hai Nguyen
    11/05/2016

    Nhưng dù sao cũng phải tính được St, St tính như thế nào ạ!

  4. Hai Nguyen
    12/05/2016

    Vậy thì tính St tính kiểu gì vậy ạ? EM vẫn chưa hiểu lắm

    • lvluat
      12/05/2016

      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.

  5. Bích Tuyền
    17/05/2016

    mình muốn tính yếu tố bất thường trong chuỗi thời gian khảo sát thì lấy Yt/(Tt*St), cho mình hỏi là Tt ở đây mình lấy cột CA (Tt) hay cột regression Tt ạ?

    • lvluat
      17/05/2016

      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.

  6. tiến sĩ
    19/08/2016

    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?

    • lvluat
      24/08/2016

      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

  7. Ẩn danh
    21/09/2016

    Cảm ơn bạn rất nhiều, mình đang tìm hiểu PP này để áp dụng vào dự báo

  8. Ẩn danh
    27/05/2017

    Cám ơn anh đã chia sẽ!

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 Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

Information

This entry was posted on 28/01/2015 by in Kinh tế, Ms Office.

Điều hướng

%d bloggers like this: