E26: Ước lượng tương quan tuyến tính trong Excel

  • Post author:
  • Post category:Excel
  • Post comments:0 Comments
  • Reading time:7 mins read

Ước lượng tương quan tuyến tính trong hay một cái tên toán học đó là hồi quy tuyến tính (linear regression) là một phương pháp để mối tương quan giữa 2 biến số trong phân tích kinh doanh. Biến số cần tìm được gọi là biến phụ thuộc (dependent variable) và biến số được dùng để dự đoán trong mô hình được gọi là biến độc lập (independent variable). Trong toán học hàm tuyến tính được biểu diễn dưới dạng

y = a*x + b

Hay được biểu diễn dưới dạng đồ thị đường thẳng

Khi đó đầu ra y là biến phụ thuộc còn x là biến độc lập đóng vai trò đầu vào.

Trong thực tế có rất nhiều bài yêu cầu ước lượng đầu ra dựa trên tham số đầu vào kể trên

Biến độc lập (đầu vào) Biến phụ thuộc (đầu ra)
Số sản phẩm làm ra hàng tháng Chi phí hàng tháng
Chi phí quảng cáo hàng tháng Doanh thu bán hàng
Số nhân công Chi phí đi lại
Lợi nhuận doanh nghiệp Số nhân viên
Giá sản phẩm Doanh số bán hàng

Để xác định mối tương quan tuyến tính kể trên, chúng ta có thể sử dụng tính năng trend curve trong Excel thông qua Scatter Chart. Khi đó biến độc lập nằm trên trục x còn biến phụ thuộc nằm trên trục y.

Lấy ví dụ, bạn muốn mở một nhà máy sản xuất gót giầy, và bạn cần xác định xem số lượng gót giầy sản xuất hàng tháng sao cho phù hợp với chi phí vận hành hàng tháng tương ứng. Giả thiết chúng ta có số liệu thống kê như sau:

  • Month: tháng
  • Units Producced: Đơn vị sản phẩm làm ra trong tháng
  • Monthly cost: Chi phí vận hành trong tháng

Câu hỏi đặt ra là tháng 15 nếu sản xuất 1100 đơn vị thì chi phí vận hành trong tháng đó ước chừng bao nhiêu?

Trong trường hợp này, ta thiết lập đồ thị Scatter như sau, cột bên trái Units Produced sẽ thuộc về trục x và trục tiếp theo cần tính toán Monthly cost sẽ thuộc về trục y.

  • Chọn vùng dữ liệu C2:D16. Vùng dữ liệu này bao gồm cả nhãn của dữ liệu trên mỗi cột
  • Chọn Insert | Insert Scatter (X, Y) or Buddle Chart. Ta có một đồ thị dưới dạng những điểm trên mặt phẳng sẽ xuất hiện
  • Chọn một điểm xanh trên đồ thị, rồi chuột phải chọn Add Trendline…
  • Excel sẽ tự động thiết lập format cho Trendline với Trendline OptionsLinear. Có nghĩa rằng dữ liệu này phù hợp với hàm tuyến tính Linear.

  • Tích chọn vào lựa chọn Display Equation on chart để hiển thị hàm tuyến tính trên đồ thị
  • Tích chọn vào lựa chọn Display R-squared value on chart để hiển thị trị số
  • Ta có được đồ thị dữ liệu như sau:

  • Ở đây các hằng số trong công thức đã được làm tròn có thể phát sinh nhiều sai số. Chúng ta format giá trị hằng số trong công thức bằng cách chọn công thức trên đồ thị rồi format Category dạng Number với Decimal places là 4 như sau

Như vậy Excel xác định được hàm tuyến tính để ước lượng chi phí vận hành hàng dựa trên số đơn vị sản phẩm sản xuất như sau:

Y = 64.2687x + 37,894.0956 hay Monthy cost = 64.2687(Units produced) + 37,894.0956

Đây là một đường thẳng mà tổng khoảng cách theo phương thẳng đứng từ các điểm nằm bên trên đường thẳng (>0) và các điểm nằm bên dưới đường thẳng (<0) là nhỏ nhất. Khoảng cách theo phương thẳng đứng từ một điểm tới đường thẳng trên trục được gọi là độ lệch (error hay là residual). Đường thẳng này được gọi là đường bình phương nhỏ nhất least-squares line.

Như vậy ta xác định được giá trị các ô E3:E17 dựa vào công thức trên. Khi đó tháng 15 nếu sản xuất 1100 đơn vị thì dự kiến chi phí vận hành tháng đó là $108589.6665. Ngoài ra ta tính được độ lệch trên các ô F3:F17 và tổng độ lệch F1 = -0.043.

Chúng ta nên sử dụng hàm tuyến tính này để ước lượng nếu đầu ra trong phép toán nằm trong khoảng dữ liệu phân tích.

Trong công thức này, giá trị chặn y-intercept = 37,894.0956 có thể hiểu là chi phí cố định hàng tháng (monthly fixed cost). Độ dốc (slope) của đường thẳng slope = 64.2687 chỉ ra rằng mỗi đơn vị sản phẩm sản xuất ra phát sinh thêm chi phí hàng tháng là $64.2687 hay đây chính là chi phí biến đổi đơn vị trên mỗi sản phẩm.

Ở đây trị số R² = 0.6882 = 68,82% hay còn gọi là hệ số xác định bội (goodness of fit statistics). Giá trị này thể hiện mối liên hệ giữa biến độc lập x và biến phụ thuộc y. Hay trong bài toán này là mối liên hệ giữa số đơn vị sản phẩm sản xuất ra và chi phí vận hành hàng tháng. Từ mô hình này ta có thể nhận xét rằng số đơn vị sản phẩm sản xuất ra ảnh hưởng 68,82% tới chi phí vận hành hàng tháng. Còn lại 31,18% phụ thuộc vào các nhân tố khác. Nếu giá trị R² càng cao thì mối liên hệ giữa biến phụ thuộc và biến độc lập càng chặt chẽ.

Leave a Reply