Hướng dẫn Excel:Xây dựng mô phỏng Monte Carlo để ra quyết định dựa trên dữ liệu

Mô phỏng Monte Carlo là một kỹ thuật thống kê được sử dụng để mô hình hóa sự không chắc chắn và đánh giá tác động của rủi ro trong các tình huống như dự báo tài chính, quản lý dự án và phân tích hàng tồn kho. Tạo ra hàng nghìn kịch bản Mô phỏng Monte Carlo cung cấp một cách đánh giá tính biến thiên giúp đưa ra quyết định dựa trên dữ liệu trong điều kiện không chắc chắn. Trong bài viết này, chúng tôi sẽ triển khai Mô phỏng Monte Carlo bằng Excel.

Hãy xác định một vấn đề để triển khai Mô phỏng Monte Carlo. Hãy xem xét một tập dữ liệu thương mại điện tử nhỏ nơi bạn sẽ mô phỏng lợi nhuận dựa trên các chỉ số chính ảnh hưởng đến kết quả.

Bước 1:Thiết lập dữ liệu của bạn trong Excel

Chèn các số liệu chính vào các cột. Các số liệu chính là

• Cột A: Số mô phỏng (1, 2, 3, …, 1000).
• Cột B: Giá bán (sử dụng hàm RAND để mô phỏng giá mỗi đơn vị).
• Cột C: Số đơn vị đã bán (mô phỏng số lượng đơn vị đã bán).
• Cột D: Chi phí trên mỗi đơn vị (số chi phí ngẫu nhiên).
• Cột E: Tính Doanh thu (nhân đơn vị và chi phí).
• Cột F: Tổng chi phí (dựa trên chi phí cố định và chi phí biến đổi).
• Cột E :Tính toán lợi nhuận (trừ doanh thu và tổng chi phí).

Bước 2:Tạo dữ liệu đầu vào ngẫu nhiên bằng hàm Excel

Để tạo số ngẫu nhiên cho từng biến chính, bạn có thể sử dụng các hàm RAND, RANDBETWEEN và NORM.INV trong Excel.

Số mô phỏng 

Bạn có thể sử dụng tùy chọn Series để chèn số. Ban đầu chèn 1 vào cột Số mô phỏng. Khi chúng ta kéo cột, nó sẽ tự động tạo ra số mô phỏng.

• Chọn ô A2 và chèn 1.

Giá bán:

• Sử dụng phân phối chuẩn với giá trị trung bình là $30 và độ lệch chuẩn là $4,5 .
• Chèn công thức sau vào ô B2.

Công thức:

=NORM.INV(RAND(), 30, 4.5)

Công thức này tạo ra các giá trị thường được phân phối xung quanh giá trị trung bình (30) với độ lệch chuẩn là 4,5.

Số đơn vị đã bán:

• Sử dụng phân phối Poisson để tạo các giá trị Poisson ngẫu nhiên.
• Chèn công thức sau vào ô C2.

Công thức:

Công thức này sẽ tạo ra các giá trị ngẫu nhiên trong khoảng từ 100 đến 550 khi số lượng sản phẩm cụ thể được bán.

Chi phí mỗi đơn vị:

• Tạo phân phối thống nhất từ $10 tới $18 .
• Chọn ô D2 và chèn công thức sau.

Công thức:

Doanh thu:

• Để tính doanh thu, hãy nhân Số lượng đã bán và Giá bán.
• Chèn công thức sau vào ô E2.

Công thức:

Tổng chi phí:

Tính tổng chi phí bằng tổng của Chi phí cố định, Chi phí biến đổi và Chi phí sản xuất.

• Chọn ô F2 và chèn công thức sau.

Công thức:

=($D2 * $C2) + 1000 + (E2 * 0.1)

Công thức này sẽ tính tổng chi phí bằng cách kết hợp Chi phí cố định, Chi phí biến đổi và Chi phí sản xuất.

• ($D2 * $C2): Đó là chi phí biến đổi. Phần này tính tổng chi phí dựa trên số lượng đơn vị sản xuất (D2) và giá thành đơn vị (C2).
• 1000: được thêm vào dưới dạng chi phí cố định, một lượng không đổi bất kể mức độ sản xuất.
• (E2 * 0.1): Nó tính toán chi phí sản xuất bằng cách áp dụng tỷ lệ 10% vào Doanh thu.

Lợi nhuận:

• Để tính lợi nhuận, hãy chèn công thức sau.

Công thức:

Công thức này lấy tổng chi phí trừ đi doanh thu.

Đầu ra:

Bạn có thể xem tất cả các công thức và kết quả của các biến. Sau này bạn có thể sao chép tất cả các công thức để tạo mô phỏng.

Bước 3:Sao chép công thức để tạo tất cả mô phỏng

• Kéo xuống các công thức ở Cột B, C, D, E, F, và G để tạo dữ liệu ngẫu nhiên cho mỗi mô phỏng.

• Mỗi hàng sẽ thể hiện một mô phỏng.

Bước 4:Phân tích kết quả

Sau khi 1000 mô phỏng được tạo ra. Hãy phân tích kết quả mô phỏng. Bạn có thể sử dụng các biện pháp thống kê sau để đánh giá tiềm năng lợi nhuận của doanh nghiệp.

Lợi nhuận trung bình :

Chèn công thức sau để tính lợi nhuận trung bình.

Công thức:

Lợi nhuận trung bình sẽ giúp bạn đạt được kết quả như mong đợi.

Lợi nhuận tối thiểu:

Chèn công thức sau để xác định lợi nhuận tối thiểu.

Công thức:

Lợi nhuận tối thiểu sẽ cho bạn thấy tình huống xấu nhất.

Lợi nhuận tối đa:

Sử dụng công thức sau để xác định lợi nhuận tối đa.

Công thức:

Lợi nhuận tối đa sẽ cho bạn thấy kết quả tốt nhất có thể.

Xác suất thua lỗ :

Tính tỷ lệ phần trăm mô phỏng có lợi nhuận âm.

Công thức:

=COUNTIF(G2:G1001,"<0")/COUNTA(G2:G1001)

Nó sẽ giúp bạn biết được mức độ mất mát mà bạn có thể phải đối mặt.

Khoảng tin cậy (CI) 95% cho lợi nhuận :

Tính giới hạn dưới và giới hạn trên của CI 95%.

Giới hạn dưới:

=PERCENTILE(G2:G1001, 0.025)

Công thức này tính toán phân vị thứ 2,5, nghĩa là 2,5% dữ liệu lợi nhuận nằm dưới giá trị này.

Giới hạn trên:

=PERCENTILE(G2:G1001, 0.975)

Công thức này tính toán phân vị thứ 97,5, nghĩa là 97,5% dữ liệu lợi nhuận nằm dưới giá trị này.

Đầu ra:

Tạo biểu đồ lợi nhuận :

• Chọn cột Lợi nhuận.
• Đi tới Chèn tab>> từ Biểu đồ>> chọn Biểu đồ .

Bạn sẽ nhận được cột Biểu đồ lợi nhuận.

Việc phân phối lợi nhuận cho thấy lợi nhuận được phân bổ như thế nào.

Mô phỏng Monte Carlo bằng Công cụ bảng dữ liệu

Bạn có thể sử dụng công cụ Bảng dữ liệu để triển khai Mô phỏng Monte Carlo. Bạn có thể sử dụng tùy chọn Sê-ri để chèn số mô phỏng.

• Tạo Bảng dữ liệu với tên cột.
• Chèn 1 vào ô A2 của cột Số mô phỏng.
• Truy cập Trang chủ tab>> từ Điền>> chọn Loạt bài .

• Trong Loạt bài hộp thoại;
  • Sê-ri trong: Cột
  • Loại:  Tuyến tính
  • Giá trị bước:  1
  • Giá trị dừng:  1000
  • Nhấp vào OK .

• Chọn toàn bộ bảng từ ô A2:G1001.
• Đi tới Dữ liệu tab>> từ What-If_Analysis>> chọn Bảng dữ liệu .

• Trong Bảng dữ liệu hộp thoại;
  • Ô nhập hàng: Để trống.
  • Ô nhập cột: Chọn ô A2.
  • Nhấp vào OK .

Bảng dữ liệu sẽ tạo ra 1000 giá trị mô phỏng.

Mẹo và phương pháp hay nhất

• Sử dụng tham chiếu ô cho giới hạn biến để cập nhật dễ dàng.
• Đặt Chế độ tính toán của bạn sang Thủ công . Nếu không, dữ liệu sẽ cập nhật mỗi khi bạn nhấn Enter . Để tính toán các ô, hãy sử dụng Tính toán ngay (F9) tùy chọn.
• Chạy ít nhất 1000 đến 10.000 mô phỏng để có dự đoán chính xác hơn.
• Nếu bạn cần chạy hàng chục nghìn mô phỏng, VBA có thể xử lý chúng hiệu quả hơn.

Kết luận

Bằng cách làm theo tất cả các bước, bạn có thể triển khai Mô phỏng Monte Carlo trong Excel. Đó là một phương pháp linh hoạt để dự đoán kết quả có thể xảy ra trong điều kiện không chắc chắn. Bằng cách sử dụng các hàm RANDBETWEEN, RAND và NORM.INV của Excel, bạn có thể xây dựng các mô hình mạnh mẽ để phân tích rủi ro và cải thiện việc ra quyết định. Mô phỏng Monte Carlo giúp phân tích rủi ro tài chính, tối ưu hóa tiến độ dự án, v.v.