Mô phỏng Monte Carlo trong Excel: Hướng dẫn đầy đủ

Vì sao dùng Excel cho mô phỏng Monte Carlo?

Khi bạn đã chọn triển khai mô phỏng Monte Carlo, bạn có nhiều công cụ hỗ trợ mô phỏng như Excel, Python, R, SAS và MATLAB.

Yếu tố quan trọng nhất cần cân nhắc, đặc biệt khi lần đầu thực hiện mô phỏng Monte Carlo, là mức độ quen thuộc tổng thể của bạn với công cụ. Excel là một trong những công cụ được dùng rộng rãi nhất trong môi trường doanh nghiệp, nghĩa là nhiều người đã quen với các thao tác cơ bản. Điều này rút ngắn thời gian đào tạo và loại bỏ nhu cầu học phần mềm mới từ đầu.

Excel cũng cung cấp các công cụ dễ dùng để tạo biểu đồ và đồ thị, hữu ích cho việc trực quan hóa kết quả mô phỏng. Ngoài ra, có sẵn một số tiện ích bổ sung mạnh mẽ cho Excel, giúp tăng cường khả năng thực hiện các mô phỏng Monte Carlo phức tạp.

Tuy nhiên, cũng cần lưu ý rằng với các mô phỏng nâng cao hơn, đặc biệt là những mô phỏng cần xử lý tập dữ liệu lớn hoặc chạy số lượng mô phỏng rất cao, các công cụ chuyên biệt khác ngoài Excel có thể phù hợp hơn.

Các hàm Excel then chốt cho Monte Carlo

Tiếp theo, chúng ta sẽ khám phá hai hàm Excel thiết yếu: RAND() và NORM.INV(), gồm cú pháp, tham số và các trường hợp sử dụng điển hình. Những hàm này giúp tạo số ngẫu nhiên và xác định phân phối xác suất, là các khía cạnh nền tảng của mọi mô phỏng.

Hàm RAND()

RAND() tạo ra một số ngẫu nhiên lớn hơn hoặc bằng 0 và nhỏ hơn 1. Các số được phân phối đều, nghĩa là bất kỳ số nào trong khoảng xác định đều có khả năng xuất hiện như nhau.

Cú pháp của RAND() như sau:

RAND()

Hàm RAND() không yêu cầu đối số nào. Chỉ cần dùng đơn giản là RAND().

Trong bối cảnh mô phỏng Monte Carlo, RAND() có thể được dùng để mô phỏng xảy ra của các sự kiện ngẫu nhiên hoặc để biến thiên các đầu vào trong mô hình.

Hàm NORM.INV()

Trong khi RAND() tạo các số ngẫu nhiên phân phối đều, NORM.INV() được dùng để tạo số ngẫu nhiên từ phân phối chuẩn, một yêu cầu phổ biến trong mô phỏng Monte Carlo. Hàm này trả về nghịch đảo của phân phối tích lũy chuẩn cho một giá trị trung bình và độ lệch chuẩn xác định.

Cú pháp của hàm NORM.INV() như sau:

NORM.INV(probability, mean, standard_deviation)

Các tham số gồm:

• probability: Một xác suất tương ứng với phân phối chuẩn, phải nằm trong khoảng từ 0 đến 1. Tham số này thường được tạo bởi hàm RAND().

• mean: Trung bình số học của phân phối chuẩn.

• standard_deviation: Độ lệch chuẩn của phân phối chuẩn, thước đo mức độ phân tán của các số quanh giá trị trung bình.

Hàm NORM.INV() được dùng để biến đổi các số ngẫu nhiên phân phối đều từ hàm RAND() thành các số tuân theo một phân phối chuẩn xác định. Điều này hữu ích khi mô hình hóa các biến được kỳ vọng thể hiện biến thiên tự nhiên theo đường cong chuẩn.

Giờ khi đã có đầy đủ các khối xây dựng, hàm và khái niệm đứng sau mô phỏng Monte Carlo, hãy triển khai một mô phỏng trong Microsoft Excel.

Triển khai mô phỏng Monte Carlo trong Microsoft Excel: Ví dụ

Hãy xem xét một tình huống bạn là nhà phân tích dữ liệu làm việc tại một công ty điện tử tiêu dùng năng động và được giao nhiệm vụ đánh giá tính khả thi tài chính của việc ra mắt một thiết bị đeo theo dõi sức khỏe mới.

Thị trường cho các thiết bị như vậy mang tính cạnh tranh và nhu cầu của người tiêu dùng có thể biến động mạnh, bị ảnh hưởng bởi yếu tố mùa vụ, hiệu quả marketing và động thái của đối thủ. Bên cạnh đó, chi phí sản xuất các thiết bị này cũng biến thiên do thay đổi giá vật liệu và bất định trong chuỗi cung ứng.

Bạn quyết định sử dụng mô phỏng Monte Carlo trong Excel để giải quyết các thách thức này. Bạn tin rằng cách tiếp cận này sẽ giúp ước tính khả năng sinh lời tiềm năng dưới các kịch bản khác nhau, qua đó cho phép công ty ra quyết định đúng đắn về chiến lược định giá, sản lượng và đầu tư marketing.

Bạn cũng đã phân tích dữ liệu quá khứ từ các đợt ra mắt sản phẩm tương tự và nghiên cứu thị trường trong ngành điện tử tiêu dùng. Từ phân tích này, bạn đi đến một số chỉ số sẽ định hướng mô phỏng của mình:

• Nhu cầu trung bình 10.000 đơn vị cho thiết bị mới trong năm đầu sau khi ra mắt, với độ lệch chuẩn 2.000 đơn vị, phản ánh sự bất định trong mức độ chấp nhận của người tiêu dùng.
• Giá bán đơn vị thường nằm trong khoảng 50 đến 70 USD, tùy thuộc vào mức độ cạnh tranh về giá và độ bão hòa thị trường.
• Chi phí đơn vị, chịu ảnh hưởng bởi giá vật liệu biến động và hiệu quả sản xuất, trung bình 30 USD mỗi đơn vị với độ lệch chuẩn 5 USD.

Dữ liệu lịch sử này tạo thành các giả định nền tảng cho tham số mô phỏng của bạn, giúp xây dựng mô phỏng phản ánh chính xác hơn điều kiện thị trường hiện tại.

Các bước bạn có thể thực hiện để triển khai mô phỏng Monte Carlo cho ví dụ cụ thể này như sau:

Bước 1: Thiết lập trang tính Excel

Đầu tiên, chuẩn bị bảng tính Excel với các cột cho từng biến và một cột cho lợi nhuận được tính.

Ban đầu sẽ trông như sau:

Thiết lập trang tính Excel.

Bước 2: Nhập công thức cho các biến

Ở mỗi hàng, bạn sẽ nhập công thức để tạo giá trị ngẫu nhiên cho nhu cầu, giá bán và chi phí dựa trên các phân phối đã xác định:

• Nhu cầu: Phân phối chuẩn (trung bình = 10.000 đơn vị, độ lệch chuẩn = 2.000 đơn vị)
• Giá bán: Phân phối đều (50 đến 70 USD)
• Chi phí: Phân phối chuẩn (trung bình = 30 USD, độ lệch chuẩn = 5 USD)

Để nhập lần lượt các công thức này, chọn ô A2 và gõ:

=NORM.INV(RAND(), 10000, 2000)

Phương trình trên tạo một phân phối chuẩn với trung bình và độ lệch chuẩn đã cho như dưới đây:

Tạo phân phối cho nhu cầu.

Tiếp theo, chọn ô B2 và gõ:

=50 + (70-50) * RAND()

Phương trình trên tạo phân phối đều giữa 50 và 70 USD cho giá bán như sau:

Tạo phân phối cho giá bán.

Chọn ô C2 và gõ:

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

Tương tự phương trình cho nhu cầu, phương trình trên tạo một phân phối chuẩn với trung bình và độ lệch chuẩn đã cho như sau:

Tạo phân phối cho chi phí.

Bước 3: Tính biến phụ thuộc

Giờ hãy tính lợi nhuận, tức biến phụ thuộc, cho mỗi mô phỏng bằng công thức ở cột D:

=(B2 - C2) * A2

Tính lợi nhuận.

Bước 4: Điền xuống để mô phỏng nhiều kịch bản

Những gì ta đã làm đến giờ mới tạo ra một mô phỏng duy nhất. Hãy mở rộng thành nhiều mô phỏng, ví dụ một nghìn lần.

Chọn các ô từ A2 đến D2 và kéo chốt điền (ô vuông nhỏ ở góc dưới bên phải vùng chọn) xuống để sao chép công thức qua số hàng bạn muốn mô phỏng (ví dụ, 1000 hàng cho 1000 mô phỏng).

Bảng tính sẽ trông như thế này:

Tạo các mô phỏng.

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

Sau khi chạy mô phỏng, bạn có thể phân tích kết quả bằng các hàm thống kê như giá trị nhỏ nhất, lớn nhất, trung bình và độ lệch chuẩn. Đừng ngần ngại tham khảo nhanh bảng gian lận Excel để ôn lại các hàm tích hợp mà chúng ta sẽ dùng tiếp theo.

Để tìm lợi nhuận trung bình kỳ vọng mỗi tháng, gõ công thức sau vào một ô, chẳng hạn G6:

=AVERAGE(D2:D1001)

Để tìm lợi nhuận nhỏ nhất kỳ vọng mỗi tháng, gõ công thức sau vào một ô, chẳng hạn G7:

=MIN(D2:D1001)

Để tìm lợi nhuận lớn nhất kỳ vọng mỗi tháng, gõ công thức sau vào một ô, chẳng hạn G8:

=MAX(D2:D1001)

Để tìm độ lệch chuẩn của lợi nhuận, gõ công thức sau vào một ô, chẳng hạn G9:

=STDEV.P(D2:D1001)

Sau khi thực hiện, trang tính Excel sẽ trông như sau:

Phân tích kết quả mô phỏng.

Chúng ta có thể diễn giải các kết quả ước tính và hàm ý cho việc ra mắt sản phẩm như sau:

• Lợi nhuận trung bình thể hiện lợi nhuận kỳ vọng từ việc ra mắt thiết bị theo dõi sức khỏe mới. Trung bình, mỗi lần chạy mô phỏng dự đoán chúng ta có thể đạt khoảng 298.278,67 USD lợi nhuận. Con số này hữu ích như một ước lượng trung tâm về khả năng sinh lời theo các giả định đã cho.
• Lợi nhuận tối thiểu 67.598,78 USD là mức lợi nhuận thấp nhất quan sát được trong tất cả các mô phỏng. Nó cho thấy kịch bản xấu nhất theo các giả định của mô hình, vẫn có lãi nhưng thấp đáng kể so với trung bình. Điều này có thể do nhu cầu đặc biệt thấp hoặc điều kiện chi phí bất lợi trong mô phỏng cụ thể đó.
• Lợi nhuận tối đa 641.955,42 USD đại diện cho kịch bản tốt nhất, khi nhu cầu và giá có khả năng ở mức cao nhất và chi phí ở mức thấp nhất trong tất cả mô phỏng. Điều này cho thấy tiềm năng tăng trưởng nếu điều kiện trở nên rất thuận lợi.

Với khoảng chênh lớn giữa lợi nhuận tối thiểu và tối đa cùng độ lệch chuẩn đáng kể, có rủi ro tài chính đáng kể gắn với việc ra mắt sản phẩm mới.

Nhà quản lý nên cân nhắc liệu công ty có thoải mái với mức độ bất định này và khả năng lợi nhuận thấp hơn trung bình hay không.

Ngoài ra, dù là tùy chọn, chúng tôi khuyến khích bạn tạo các trực quan hóa như Biểu đồ tần suất (Histogram) để có cái nhìn trực quan về kết quả mô phỏng.

Kỹ thuật cải thiện mô phỏng Monte Carlo trong Excel

Khi chạy lại cùng mô phỏng như trên, bạn có thể quan sát sự khác biệt nhỏ trong các phép tính, như hình dưới:

Kết quả mô phỏng biến động.

Đó là vì các giá trị của mô phỏng gốc có thể thay đổi giữa các lần lặp, ảnh hưởng đến các ước tính thu được. Dù biến động nhỏ, khi giá trị ước tính thay đổi, các nhà quản lý có thể lo ngại về độ chính xác và độ tin cậy của mô phỏng.

Hãy cùng khám phá một vài kỹ thuật nâng cao có thể dùng để cải thiện độ chính xác và độ tin cậy của mô phỏng.

Tăng số lượng mô phỏng

Chạy số lượng mô phỏng lớn hơn giúp triệt tiêu các dao động ngẫu nhiên và mang lại ước tính ổn định, chính xác hơn về kết quả.

Với ví dụ trên, chúng ta có thể tăng số lần chạy mô phỏng (ví dụ, từ 1.000 lên 10.000 hoặc hơn), đặc biệt khi xử lý các tham số có độ biến thiên cao.

Việc xác định số lượng mô phỏng “đúng” phụ thuộc vào nhiều yếu tố.

Mô hình càng phức tạp (tức có nhiều biến và phạm vi tương tác rộng), thường cần nhiều mô phỏng hơn để bao quát các kết quả khả dĩ và đảm bảo kết quả không do ngẫu nhiên.

Nếu đầu vào có độ biến thiên cao hoặc lệch mạnh, sẽ cần nhiều mô phỏng hơn để ước tính chính xác phần đuôi (các giá trị cực trị) của phân phối kết quả.

Đối với các phân tích chi tiết hơn, đặc biệt trong tài chính hoặc quản trị rủi ro, việc chạy 10.000 đến 100.000 mô phỏng là không hiếm. Khoảng này thường được dùng để đảm bảo kết quả vững chắc trên nhiều kịch bản và đầu vào. Tất nhiên, như đã đề cập, với phân tích quy mô lớn như vậy, Excel không phải lúc nào cũng là công cụ tốt nhất; R hoặc Python thường phù hợp hơn.

Tinh chỉnh các phân phối đầu vào

Độ chính xác của mô phỏng phụ thuộc phần lớn vào mức độ phản ánh đúng bất định và hành vi thực sự của các biến nền tảng trong các phân phối xác suất đầu vào. Trong ví dụ trên, chúng ta giả định phân phối chuẩn cho nhu cầu và chi phí, và phân phối đều cho giá bán.

Ngoài ra, chúng ta có thể phân tích dữ liệu lịch sử toàn diện hơn để tham số hóa phân phối tốt hơn. Có thể hiểu rõ hơn hành vi của chi phí, giá bán và nhu cầu trước các yếu tố bên ngoài dựa trên ý kiến chuyên gia lĩnh vực. Chúng ta cũng có thể cân nhắc sử dụng các phân phối như log-normal, beta hoặc gamma, hoặc tạo các phân phối tùy chỉnh dựa trên dữ liệu thực nghiệm.

Thực hiện phân tích độ nhạy

Phân tích này được thực hiện để hiểu biến đầu vào nào có tác động lớn nhất đến đầu ra bằng cách thay đổi có hệ thống từng đầu vào trong khi giữ các biến khác cố định.

Trong ví dụ trên, chúng ta có thể giữ cố định hai biến và thay đổi phân phối của một biến để hiểu sự thay đổi trong các ước tính. Sau đó lặp lại quy trình với hai biến còn lại lần lượt. Cuối cùng, kỹ thuật này giúp xác định biến nào cần tập trung nỗ lực để cải thiện độ chính xác.

Áp dụng lặp đi lặp lại các kỹ thuật trên và phân tích kết quả có thể dẫn đến kết quả chính xác và đáng tin cậy hơn.

Kết luận

Hướng dẫn này đã giới thiệu bạn về mô phỏng Monte Carlo và các khái niệm thống kê liên quan. Sau khi giới thiệu các hàm Excel phù hợp, hướng dẫn đã cung cấp quy trình từng bước để triển khai mô phỏng Monte Carlo trong Excel bằng một ví dụ thực tế.

Cuối cùng, bạn đã biết một số thực tiễn tốt nhất và kỹ thuật nâng cao để đảm bảo kết quả chính xác và đáng tin cậy hơn.

Nếu bạn đặc biệt quan tâm đến việc triển khai mô phỏng Monte Carlo trên các công cụ khác như Python hoặc R, hai tài nguyên sau sẽ hữu ích:

• Mô phỏng Monte Carlo trong R
• Mô phỏng Monte Carlo trong Python

Hoặc, nếu bạn muốn gắn bó với Microsoft Excel quen thuộc và muốn nâng cao kỹ năng với công cụ được sử dụng rộng rãi này, bạn nên xem lộ trình Excel Fundamentals của chúng tôi.