[Excel] Basic Regression and Forecast

Posted byphamnguyenkhanhPosted indata analytics, excelTags:, , , ,

Bài viết này mình hướng dẫn các bạn sử dụng một số phương pháp hồi quy và dự báo trong Excel.

#1: Single Variable Model – Mô hình một biến

Dữ liệu bên dưới là doanh thu của Apple giai đoạn 2006-2022, hãy dự báo doanh thu năm 2023 của Apple:

#1.1 FORECAST.LINEAR – công thức dự báo tuyến tính

=FORECAST.LINEAR(x, known_y’s, known_x’s)

Trong đó:

  • x: giá trị y muốn tìm tương ứng với giá trị x
  • known_y’s: các giá trị y (tương ứng với x) đã biết,
  • known_x’s: các giá trị x (tương ứng với y) đã biết

Các bạn có thể thêm cột biến t và đặt năm 2006 = 1, 2007 = 2,… 2023 = 18.

trong đó:

  • B19: doanh thu muốn tìm tương ứng với năm 2023
  • C2:C18: doanh thu 2006-2022 đã biết,
  • B2:B18: năm 2006-2022 đã biết

#1.2 Regression Model

Sử dụng tính năng Regression Model trong Data Analysis:

Kết quả chạy mô hình hồi quy:

Phương trình hồi quy thu được:

Revenue = -24.89117647 + 22.66568627*t

Thay t = 18 để dự báo Revenue năm 2023:

Revenue 2023 = -24.89117647 + 22.66568627*18 = 383.0912

Thay t = 19 để dự báo Revenue năm 2024:

Revenue 2024 = -24.89117647 + 22.66568627*19 = 405.7568

#1.3 FORECAST.EST – công thức dự báo tuyến tính theo chu kỳ

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Trong đó:

  • target_date – thời điểm dự báo,
  • values – chuỗi giá trị đã biết
  • timeline – chuỗi thời gian chứa giá trị đã biết
  • [seasonality] – tính mùa vụ (0 = không theo mùa, 1 = tự động, 2 = semiannualy, n = thời gian tuỳ chỉnh (nhỏ nhất = 8786, tương ứng 8786 tiếng/năm),
  • [data_completion] – hoàn thiện dữ liệu (nếu = 1 (mặc định): dữ liệu bị thiếu sẽ được tính bằng cách lấy trung bình của các giá trị quanh nó, nếu = 0: dự liệu bị thiếu sẽ được xem là 0),
  • [aggregation] – kiểm soát hàm được sử dụng để tổng hợp các dữ liệu nếu timeline chứa các giá trị trùng lặp:
    1 hoặc bỏ qua: AVERAGE.
    2: COUNT,
    3: COUNTA,
    4: MAX,
    5: MEDIAN,
    6: MIN,
    7: SUM

Dữ liệu bên dưới là doanh thu của Apple giai đoạn 2009-2022 theo từng quý, bắt đầu từ Quý 1/2009 đến Quý 4/2022:

Sử dụng hàm FORECAST.EST, chúng ta dự báo được doanh thu Apple theo từng quý trong năm 2023:

Trong đó:

  • A58: thời điểm doanh thu Quý 1 muốn dự báo,
  • C2:C57: dữ liệu doanh thu đã biết,
  • A2:A57: dữ liệu thời gian đã biết,
  • 4: dữ liệu theo quý, 1 năm có 4 quý

#2: Multivariate Regression Model – Mô hình hồi quy đa biến

Dữ liệu bên dưới thể hiện Profit của các công ty và bốn trường thông tin của mỗi công ty gồm: chi phí R&D, chi phí Administration, chi phí Marketing và State mà công ty đặt tại đó. Sử dụng mô hình hồi quy đa biến để tìm ra phương trình hồi quy như sau:

Profit = a + (b*R&D Spend) + (c*Maketing Spend) + (d*State)

Vì State đang chứa dữ liệu text, chúng ta cần phải chuyển đổi các giá trị text sang dạng number theo quy luật sau:
California = 0, Florida = 1, New York = 2

Kết quả chạy mô hình hồi quy:

Phương trình hồi quy thu được:

Profit = 50164.827 + 0.806*R&D Spend - 0.027*Administration + 0.0272*Marketing Spend - 22.321*State

So sánh giữa dữ liệu Profit thực tế với Profit tính được từ mô hình:

Tải file thực hành tại đây!

Chúc các bạn thành công!

Leave a comment