Giới thiệu về mô phỏng Monte Carlo trong Excel

Lưu ý:  Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

Bài viết này được điều chỉnh từ Microsoft Excel phân tích dữ liệu và lập mô hình kinh doanh bằng Wayne L. Winston.

  • Ai sử dụng mô phỏng Monte Carlo?

  • Điều gì xảy ra khi bạn nhập =RAND() trong một ô không?

  • Làm thế nào bạn có thể mô phỏng các giá trị của một biến số ngẫu nhiên rời rạc?

  • Làm thế nào bạn có thể mô phỏng các giá trị của một biến số ngẫu nhiên bình thường?

  • Làm thế nào là một công ty thiệp chúc mừng có thể xác định số lượng thẻ để tạo ra?

Chúng tôi muốn chính xác ước tính xác suất không chắc chắn sự kiện. Ví dụ, xác suất dòng tiền mặt của một sản phẩm mới sẽ có một giá trị hiện tại ròng dương (NPV) là gì? Hệ số rủi ro của danh mục dự án đầu tư của chúng tôi là gì? Mô phỏng Monte Carlo cho phép chúng tôi để tình huống mô hình mà trình bày không chắc chắn và sau đó phát trên máy tính hàng ngàn lần.

Lưu ý: Tên Monte Carlo mô phỏng xuất phát từ các mô phỏng máy tính, thực hiện trong các năm 1930 và 1940 để ước tính xác suất phản ứng chuỗi cần thiết cho một bom atom nổ sẽ hoạt động thành công. Vật lý liên quan đến việc này là quạt lớn của cờ bạc, để họ cung cấp cho các mô phỏng tên mã Monte Carlo.

Trong các chương tiếp theo năm, bạn sẽ thấy các ví dụ về cách bạn có thể sử dụng Excel để thực hiện Monte Carlo mô phỏng.

Nhiều công ty dùng Monte Carlo mô phỏng như là một phần quan trọng của quá trình quyết định của họ. Dưới đây là một số ví dụ.

  • Chung cơ, Proctor và đánh bạc, Pfizer, Bristol-Myers Squibb và Eli Lilly sử dụng mô phỏng để ước tính trả về trung bình và hệ số rủi ro của sản phẩm mới. GM, thông tin này được dùng bởi Tổng Giám đốc để xác định những sản phẩm đến thị trường.

  • GM dùng mô phỏng cho các hoạt động như dự báo thu nhập ròng cho công ty, cấu trúc và mua chi phí dự đoán và xác định của nó tính nhạy cảm khác nhau rủi ro (chẳng hạn như thay đổi lãi suất và tỷ biến động).

  • Lilly sử dụng mô phỏng để xác định công suất tối ưu cây cho mỗi ma túy.

  • Proctor và đánh bạc sử dụng mô phỏng mô hình và tối ưu hedge ngoại rủi ro.

  • Sears sử dụng mô phỏng để xác định số lượng đơn vị của mỗi dòng sản phẩm cần được sắp xếp từ nhà cung cấp — ví dụ, số lượng cặp quần Dockers cần được sắp xếp năm.

  • Công ty dầu và ma túy sử dụng mô phỏng giá trị "tùy chọn thực", chẳng hạn như giá trị của một tùy chọn để bung rộng, Thu gọn hoặc trì hoãn việc dự án.

  • Tài chính trình lập kế hoạch sử dụng mô phỏng Monte Carlo để xác định chiến lược khoản đầu tư tối ưu cho máy khách hưu trí.

Khi bạn nhập công thức =RAND() trong một ô, bạn nhận được một số đều có khả năng xem như bất kỳ giá trị nào giữa 0 và 1. Vì vậy, khoảng 25 phần trăm của thời gian, bạn sẽ nhận được một số nhỏ hơn hoặc bằng 0,25; khoảng 10 phần trăm của thời gian bạn sẽ nhận được một số mà được ít 0,90, v.v.. Để minh họa cách hoạt động của hàm RAND, hãy xem tại tệp Randdemo.xlsx, Hiển thị trong hình 60-1.

Ảnh Quyển sách
Hình 60-1 chứng minh hàm RAND

Lưu ý: Khi bạn mở tệp Randdemo.xlsx, bạn sẽ không nhìn thấy các số ngẫu nhiên cùng Hiển thị trong hình 60-1. Hàm RAND luôn tự động tính toán lại các số mà nó tạo ra khi mở một trang tính hoặc khi thông tin mới được nhập vào trang tính.

Trước tiên, sao chép từ ô C3 C4:C402 thức =RAND(). Sau đó bạn đặt tên cho phạm vi C3:C402 dữ liệu. Sau đó, trong cột F, bạn có thể theo dõi trung bình của các số ngẫu nhiên 400 (ô F2) và sử dụng hàm COUNTIF để xác định các phân số nằm giữa 0 và 0,25, 0,25 và 0,50, 0,50 và 0,75, và 0,75 và 1. Khi bạn nhấn phím F9, các số ngẫu nhiên được tính toán lại. Thông báo trung bình của các số 400 luôn là xấp xỉ 0,5, và khoảng 25 phần trăm của kết quả có trong khoảng thời gian của 0,25. Các kết quả là phù hợp với định nghĩa của một số ngẫu nhiên. Cũng lưu ý rằng các giá trị được tạo bởi RAND trong ô khác nhau độc lập. Ví dụ, nếu số ngẫu nhiên tạo trong ô C3 là một số lớn (ví dụ, 0,99), nó sẽ cho chúng tôi không có tác về các giá trị của các số ngẫu nhiên khác tạo ra.

Giả sử nhu cầu về lịch được quản lý bởi biến số ngẫu nhiên rời rạc sau đây:

Yêu cầu

Xác suất

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Làm thế nào chúng tôi có thể có Excel phát hoặc mô phỏng, này yêu cầu cho lịch nhiều thời gian? Mẹo là để liên kết mỗi giá trị có thể xảy ra hàm RAND với một yêu cầu có thể xảy ra cho lịch. Nhiệm vụ sau đây sẽ đảm bảo rằng một yêu cầu 10.000 sẽ xảy ra 10 phần trăm của thời gian, v.v..

Yêu cầu

Số ngẫu nhiên giao

10.000

Nhỏ hơn 0,10

20.000

Lớn hơn hoặc bằng 0,10, và nhỏ hơn 0,45

40.000

Lớn hơn hoặc bằng 0,45, và nhỏ hơn 0,75

60.000

Lớn hơn hoặc bằng 0,75

Để minh họa mô phỏng yêu cầu, xem tệp Discretesim.xlsx, Hiển thị trong hình 60-2 trên trang tiếp theo.

Ảnh Quyển sách
2-60 hình mô phỏng biến ngẫu nhiên rời rạc

Chìa khóa để mô phỏng của chúng tôi là sử dụng một số ngẫu nhiên để khởi tạo tra cứu từ bảng phạm vi F2:G5 (có tên là tra cứu). Số ngẫu nhiên lớn hơn hoặc bằng 0 và nhỏ hơn 0,10 sẽ mang lại một yêu cầu 10.000; số ngẫu nhiên lớn hơn hoặc bằng 0,10 và nhỏ hơn 0,45 sẽ mang lại một yêu cầu 20.000; số ngẫu nhiên lớn hơn hoặc bằng 0,45 và nhỏ hơn 0,75 sẽ mang lại một yêu cầu 40.000; và số ngẫu nhiên lớn hơn hoặc bằng 0,75 sẽ mang lại một yêu cầu 60.000. Bạn tạo số ngẫu nhiên 400 bằng cách sao chép từ C3 để C4:C402 thức RAND(). Bạn rồi tạo 400 phép thử hay lặp của yêu cầu lịch bằng cách sao chép từ B3 để B4:B402 công thức VLOOKUP(C3,lookup,2). Công thức này đảm bảo rằng bất kỳ số ngẫu nhiên ít hơn 0,10 tạo một yêu cầu 10.000, bất kỳ số ngẫu nhiên giữa 0,10 và 0,45 tạo một yêu cầu của 20.000, v.v.. Trong phạm vi ô F8:F11, hãy dùng hàm COUNTIF để xác định phân số của chúng tôi lặp 400 năng suất mỗi yêu cầu. Khi chúng tôi nhấn F9 để tính toán lại các số ngẫu nhiên, xác suất mô phỏng gần với xác suất giả định nhu cầu của chúng tôi.

Nếu bạn nhập vào ô bất kỳ thức NORMINV(rand(),mu,sigma), bạn sẽ tạo ra một giá trị mô phỏng của một biến số ngẫu nhiên thường gặp một giá trị trung độ mu và độ lệch chuẩn sigma. Quy trình này được minh họa trong tệp Normalsim.xlsx, Hiển thị trong hình 60-3.

Ảnh Quyển sách
3-60 hình mô phỏng biến ngẫu nhiên thường

Hãy giả sử chúng tôi muốn mô phỏng 400 phép thử hay lặp cho một biến số ngẫu nhiên thường với một giá trị trung 40.000 và độ lệch chuẩn của 10.000. (Bạn có thể nhập các giá trị trong ô E1 và E2 và đặt tên cho các ô có nghĩa là gìsigma, tương ứng.) Sao chép công thức =RAND() từ C4 C5:C403 tạo số ngẫu nhiên 400 khác nhau. Sao chép từ B4 B5:B403 công thức NORMINV(C4,mean,sigma) tạo ra 400 giá trị khác nhau dùng thử từ một biến số ngẫu nhiên thường với một giá trị trung 40.000 và độ lệch chuẩn của 10.000. Khi chúng tôi nhấn F9 để tính toán lại các số ngẫu nhiên, giá trị trung bình vẫn nằm gần với 40.000 và độ lệch chuẩn gần với 10.000.

Cơ bản, cho một ngẫu nhiên số x, thức NORMINV(p,mu,sigma) tạo ra phân vị thứ pcủa một biến số ngẫu nhiên thường với một giá trị trung độ mu và độ lệch chuẩn sigma. Ví dụ, số ngẫu nhiên 0,77 trong ô C4 (xem hình 60-3) tạo ra trong ô B4 xấp xỉ 77 phân vị của một biến số ngẫu nhiên thường với một giá trị trung 40.000 và độ lệch chuẩn của 10.000.

Trong phần này, bạn sẽ thấy cách Monaco mô phỏng có thể được dùng như một công cụ quyết định. Giả sử rằng yêu cầu cho một thẻ ngày lễ tình nhân được quản lý bởi biến số ngẫu nhiên rời rạc sau đây:

Yêu cầu

Xác suất

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Thiệp chúc mừng bán cho $4,00, và chi phí biến đổi sản xuất mỗi thẻ là $1,50. Còn lại của thẻ phải được xử lý chi phí của $0,20 trên mỗi thẻ. Số lượng thẻ sẽ được in ra?

Cơ bản, chúng tôi mô phỏng mỗi số lượng các sản xuất (10.000, 20.000, 40.000 hoặc 60.000) nhiều lần (ví dụ, 1000 lặp). Sau đó, chúng tôi xác định số lượng đơn hàng nào tạo ra tối đa lợi nhuận trung bình qua lặp 1000. Bạn có thể tìm thấy dữ liệu cho này trong tệp Valentine.xlsx, Hiển thị trong hình 60-4. Bạn gán các phạm vi tên trong ô B1:B11 cho ô C1:C11. Phạm vi ô G3:H6 được gán tên tra cứu. Giá bán hàng và tham số chi phí của chúng tôi đang được nhập vào ô C4:C6.

Ảnh Quyển sách
Hình 60-4 ngày lễ tình nhân thẻ mô phỏng

Bạn có thể nhập một số lượng bản dùng thử sản xuất (40.000 trong ví dụ này) trong ô C1. Tiếp theo, tạo số ngẫu nhiên trong ô C2 với thức =RAND(). Như đã mô tả, bạn mô phỏng yêu cầu về thẻ trong ô C3 với thức VLOOKUP(rand,lookup,2). (Trong công thức VLOOKUP, rand là ô tên được gán cho ô C3, không có hàm RAND.)

Số đơn vị đã bán là nhỏ hơn số lượng sản xuất và yêu cầu của chúng tôi. Trong ô C8, bạn tính của chúng tôi doanh thu với công thức MIN (sản xuất, yêu cầu) * unit_price. Trong ô C9, bạn tính tổng sản xuất chi phí với công thức sản xuất * unit_prod_cost.

Nếu chúng ta tạo ra nhiều thẻ hơn trong yêu cầu, số lượng đơn vị trái qua bằng sản xuất trừ yêu cầu; Nếu không đơn vị không còn lại. Chúng tôi tính chi phí của chúng tôi xử lý trong ô C10 với công thức unit_disp_cost * nếu (sản xuất > yêu cầu, sản xuất – yêu cầu, 0). Cuối cùng, trong ô C11, chúng tôi tính lợi nhuận của chúng tôi như doanh thu – total_var_cost-total_disposing_cost.

Chúng tôi muốn một cách hiệu quả để nhấn F9 nhiều lần (ví dụ, 1000) cho mỗi số lượng sản xuất và kiểm đếm của chúng tôi lợi nhuận dự kiến cho mỗi số lượng. Tình huống này là một mà một bảng dữ liệu hai chiều đến cứu của chúng tôi. (Hãy xem chương 15, "Nhạy cảm phân tích với bảng dữ liệu," để biết chi tiết về bảng dữ liệu.) Bảng dữ liệu được sử dụng trong ví dụ này được hiển thị trong hình 60-5.

Ảnh Quyển sách
Bảng dữ liệu hai chiều hình 60-5 cho thiệp chúc mừng mô phỏng

Trong phạm vi ô A16:A1015, hãy nhập số 1 – 1000 (tương ứng với bản dùng thử 1000 của chúng tôi). Một cách dễ dàng để tạo các giá trị này là bắt đầu bằng cách nhập 1 trong ô A16. Chọn ô, và sau đó trên trang đầu tab trong nhóm sửa , bấm và chọn chuỗi để hiển thị hộp thoại chuỗi . Trong hộp thoại chuỗi , Hiển thị trong hình 60-6, nhập giá trị bước 1 và giá trị dừng 1000. Trong Chuỗi trong vùng, chọn tùy chọn cột , sau đó bấm OK. Các số 1 – 1000 sẽ được nhập vào cột một bắt đầu từ ô A16.

Ảnh Quyển sách
Hình 60-6 cách sử dụng các chuỗi thoại hộp để điền vào các số dùng thử 1 đến 1000

Tiếp theo, chúng tôi nhập của chúng tôi có thể xảy ra sản xuất quantities (10.000, 20.000, 40.000, 60.000) trong ô B15:E15. Chúng tôi muốn tính toán lãi cho mỗi số dùng thử (1 đến 1000) và mỗi số lượng sản xuất. Chúng tôi tham khảo công thức cho lợi nhuận (được tính toán trong ô C11) trong ô phía trên bên trái của bảng dữ liệu của chúng tôi (A15) bằng cách nhập = C11.

Chúng tôi đã sẵn sàng để Mẹo Excel vào mô phỏng 1000 lặp của yêu cầu cho mỗi số lượng sản xuất. Chọn phạm vi bảng (A15:E1014), và sau đó trong nhóm công cụ dữ liệu trên tab dữ liệu, bấm phân tích gì nếu, sau đó chọn bảng dữ liệu. Để thiết lập một bảng dữ liệu hai chiều, chọn số lượng sản xuất của chúng tôi (ô C1) dưới dạng ô nhập hàng và chọn bất kỳ ô trống (chúng tôi đã chọn ô I14) là ô nhập cột. Sau khi bấm vào OK, Excel mô phỏng 1000 yêu cầu giá trị cho mỗi số lượng đơn hàng.

Để hiểu tại sao điều này hoạt động, hãy cân nhắc các giá trị được đặt bằng bảng dữ liệu trong phạm vi ô C16:C1015. Đối với mỗi những ô này, Excel sẽ sử dụng một giá trị của 20.000 trong ô C1. Trong C16, giá trị ô nhập cột của 1 được đặt trong một ô trống và số ngẫu nhiên trong ô C2 tính toán lại. Lợi nhuận tương ứng rồi ghi lại trong ô C16. Sau đó cột nhập giá trị ô của 2 sẽ được đặt trong một ô trống và số ngẫu nhiên trong C2 lại tính toán lại. Lợi nhuận tương ứng được nhập vào trong ô C17.

Bằng cách sao chép từ ô B13 để C13:E13 công thức AVERAGE(B16:B1015), chúng tôi tính trung bình mô phỏng lợi nhuận cho mỗi số lượng sản xuất. Bằng cách sao chép từ ô B14 để C14:E14 công thức STDEV(B16:B1015), chúng tôi tính độ lệch chuẩn của lợi nhuận của chúng tôi mô phỏng cho mỗi số lượng đơn hàng. Mỗi khi chúng tôi nhấn F9, 1000 lặp của yêu cầu được mô phỏng cho mỗi số lượng đơn hàng. Sản xuất 40.000 thẻ luôn tạo ra lợi nhuận dự kiến lớn nhất. Do đó, nó sẽ xuất hiện sản xuất 40.000 thẻ là quyết định thích hợp.

Ảnh hưởng của rủi ro trên quyết định của chúng tôi     Nếu chúng tôi sản xuất 20.000 thay vì 40.000 thẻ, lợi nhuận của chúng tôi mong đợi sẽ bỏ các khoảng 22 phần trăm, nhưng chúng tôi rủi ro (được đo bằng độ lệch chuẩn của lợi nhuận) giọt hầu như 73 phần trăm. Do đó, nếu chúng tôi rất không thích để rủi ro, sản xuất 20.000 thẻ có thể quyết định bên phải. Ngẫu nhiên, sản xuất 10.000 thẻ luôn có độ lệch chuẩn của 0 thẻ vì nếu chúng ta tạo ra 10.000 thẻ, chúng tôi sẽ luôn bán tất cả chúng mà không còn dư bất kỳ.

Lưu ý: Trong sổ làm việc này, tùy chọn tính toán được đặt để Tự động ngoại trừ cho bảng. (Sử dụng tính toán lệnh trong nhóm tính toán trên tab công thức). Thiết đặt này đảm bảo rằng bảng dữ liệu của chúng tôi sẽ không tính toán lại, trừ khi chúng tôi nhấn F9, mà có thể sử dụng bởi vì một bảng dữ liệu lớn sẽ chậm công việc của bạn nếu nó tính toán lại mỗi khi bạn nhập nội dung vào trang tính của bạn. Lưu ý rằng trong ví dụ này, mỗi khi bạn nhấn F9, lợi nhuận trung bình sẽ thay đổi. Điều này xảy ra vì mỗi khi bạn nhấn F9, trình tự khác nhau của số ngẫu nhiên 1000 được dùng để tạo ra các yêu cầu cho mỗi số lượng đơn hàng.

Khoảng tin cậy có nghĩa là gì lợi nhuận     Câu hỏi tự nhiên hỏi trong tình huống này là, vào khoảng thời gian là chúng tôi 95 phần trăm đảm bảo rằng lợi nhuận trung bình đúng sẽ rơi vào? Khoảng thời gian này được gọi là phần trăm 95 khoảng tin cậy của trung bình lợi nhuận. Phần trăm 95 khoảng tin cậy cho giá trị trung bình của bất kỳ đầu ra mô phỏng được tính bằng công thức sau đây:

Ảnh Quyển sách

Trong ô J11, bạn tính thấp hơn giới hạn đối với phần trăm 95 khoảng tin cậy trên trung bình lợi nhuận khi 40.000 lịch với thức D13–1.96*D14/SQRT(1000)sản xuất. Trong ô J12, bạn tính upper giới hạn đối với khoảng tin cậy 95 phần trăm của chúng tôi với công thức D13+1.96*D14/SQRT(1000). Các tính toán được hiển thị trong hình 60-7.

Ảnh Quyển sách
Hình 60-7 95 phần trăm khoảng tin cậy của trung bình lợi nhuận khi 40.000 lịch được sắp xếp thứ tự

Chúng tôi đang 95 phần trăm đảm bảo rằng lợi nhuận trung bình của chúng tôi khi 40.000 lịch được sắp xếp thứ tự nằm trong khoảng $56,687 và $62,589.

  1. Bán buôn GMC tin rằng yêu cầu cho 2005 sứ sẽ thường được phân bố với trung bình 200 và độ lệch chuẩn của 30. Chi phí của người đó nhận sứ là $25.000, và người bán sứ cho $40.000. Nửa tất cả các sứ không bán tại đầy đủ giá có thể đã bán cho $30.000. Anh ấy xem xét sắp xếp thứ tự 200, 220, 240, 260, 280 hoặc sứ 300. Số lượng nên he thứ tự?

  2. Một siêu nhỏ đang cố gắng xác định số lượng bản sao của mọi người tạp chí họ nên đặt mỗi tuần. Họ tin của họ yêu cầu cho mọi người được quản lý bởi biến số ngẫu nhiên rời rạc sau đây:

    Yêu cầu

    Xác suất

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Siêu trả $1,00 cho từng bản sao của mọi người và bán cho $1,95. Mỗi bản sao chưa bán có thể được trả về cho $0,50. Số lượng bản sao của mọi người sẽ cửa hàng thứ tự?

Bạn cần thêm trợ giúp?

Bạn luôn có thể yêu cầu một chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm hỗ trợ trong Cộng đồng Giải đáp hay đề xuất tính năng hoặc cải tiến mới ở Excel User Voice.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×