Quản lý cuộc hẹn, kế hoạch, ngân sách — dễ dàng với Microsoft 365.

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

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

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

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

  • Làm thế nào bạn có thể mô phỏng giá trị của một biến 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 ngẫu nhiên bình thường?

  • Công ty thiệp chúc mừng xác định có bao nhiêu thẻ để tạo ra?

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

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

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

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

  • General Motors, Proctor và Gamble, Pfizer, Bristol-Myers Squibb và Eli Lilly dùng mô phỏng để ước tính cả lợi nhuận trung bình và yếu tố rủi ro của các sản phẩm mới. Tại GM, thông tin này được giám đốc điều hành sử dụng để xác định sản phẩm nào có mặt trên thị trường.

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

  • Lilly dùng mô phỏng để xác định dung lượng thực vật tối ưu cho từng loại thuốc.

  • Proctor và Gamble sử dụng mô phỏng cho mô hình và có nguy cơ trao đổi tối ưu trong ngoại hối.

  • Sears dùng mô phỏng để xác định có bao nhiêu đơn vị của từng dòng sản phẩm nên được đặt hàng từ các nhà cung cấp — ví dụ: số cặp quần của các quần thể được đặt hàng trong năm nay.

  • Các công ty dầu và dược dùng mô phỏng thành giá trị "tùy chọn thực tế", chẳng hạn như giá trị của một tùy chọn để bung rộng, hợp đồng hoặc trì hoãn dự án.

  • Các nhà lập kế hoạch tài chính dùng mô phỏng Monte Carlo để xác định các chiến lược đầu tư tối ưu cho quỹ hưu trí của khách hàng.

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

Ảnh Quyển sách

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 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ột trang tính được mở hoặc khi thông tin mới được nhập vào trang tính.

Trước tiên, hãy sao chép từ ô C3 đến C4: C402 The Formula = Rand (). Sau đó, bạn đặt tên cho phạm vi C3: dữ liệuC402. 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 trong khoảng từ 0 đến 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 sẽ được tính toán lại. Lưu ý rằng trung bình của các số 400 luôn xấp xỉ 0,5, và khoảng 25 phần trăm của kết quả đều nằm trong khoảng 0,25. Những kết quả này phù hợp với định nghĩa của một số ngẫu nhiên. Cũng nên lưu ý rằng các giá trị được tạo bởi RAND trong các ô khác nhau đều độc lập. Ví dụ, nếu số ngẫu nhiên được tạo trong ô C3 là số lớn (ví dụ, 0,99), nó sẽ cho chúng ta biết gì về các giá trị của các số ngẫu nhiên khác được tạo ra.

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

Ứng

Probability

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 ra hoặc mô phỏng, yêu cầu này cho lịch nhiều lần? Trick là kết hợp mỗi giá trị có thể có của hàm RAND với một nhu cầu có thể có cho lịch. Nhiệm vụ sau đây đảm bảo rằng nhu cầu của 10.000 sẽ xảy ra 10 phần trăm thời gian, v.v.

Ứng

Số ngẫu nhiên được gán

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

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

Ảnh Quyển sách

Khóa để mô phỏng của chúng tôi là sử dụng một số ngẫu nhiên để bắt đầu tra cứu từ phạm vi bảng 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 nhu 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 đến một nhu 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 đến một nhu cầu 40.000; và số ngẫu nhiên lớn hơn hoặc bằng 0,75 sẽ mang đến một nhu cầu của 60.000. Bạn tạo 400 số ngẫu nhiên bằng cách sao chép từ C3 sang C4: C402 công thức Rand (). Sau đó, bạn tạo 400 thử nghiệm hoặc lặp lại, theo yêu cầu lịch bằng cách sao chép từ B3 đến B4: B402 công thức Vlookup (C3, tra cứu, 2). Công thức này đảm bảo rằng bất kỳ số ngẫu nhiên nhỏ hơn 0,10 tạo ra nhu cầu 10.000, bất kỳ số ngẫu nhiên nào giữa 0,10 và 0,45 sẽ tạo ra một nhu cầu của 20.000, v.v. Trong phạm vi ô F8: F11, sử dụng hàm COUNTIF để xác định phân số của số lần lặp 400 của chúng tôi sẽ yielding 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, các xác suất mô phỏng sẽ được đóng lại với các xác suất yêu cầu của chúng tôi.

Nếu bạn nhập vào bất kỳ ô nào của công 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 ngẫu nhiên bình thường có nghĩa là Mu và số độ lệch chuẩn Sigma. Quy trình này được minh họa trong tệp Normalsim. xlsx, được hiển thị trong hình 60-3.

Ảnh Quyển sách

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

Về cơ bản, đối với một số ngẫu nhiên x, công thức norminv (p, Mu, Sigma) tạo phân vị thứ ncủa biến ngẫu nhiên bình thường với giá trị có nghĩa là Mu và số độ lệch tiêu chuẩn Sigma. Ví dụ: số ngẫu nhiên 0,77 trong ô C4 (xem hình 60-3) tạo trong ô B4 xấp xỉ phần trăm phân vị của một biến ngẫu nhiên bình thường với giá trị là 40.000 và độ lệch chuẩn của 10.000.

Trong phần này, bạn sẽ thấy mô phỏng Monte Carlo có thể được sử dụng như một công cụ tạo ra quyết định. Giả sử rằng nhu cầu của thẻ ngày Valentine được quản lý bởi biến ngẫu nhiên rời rạc sau đây:

Ứng

Probability

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Thẻ lời chào bán cho $4,00 và chi phí biến số của mỗi thẻ là $1,50. Thẻ còn lại phải được xử lý tại một chi phí của $0,20 trên mỗi thẻ. Có bao nhiêu thẻ sẽ được in?

Về cơ bản, chúng tôi mô phỏng từng số lượng sản phẩm có thể (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 sẽ tạo ra lợi nhuận trung bình tối đa so với số lần lặp lại 1000. Bạn có thể tìm thấy dữ liệu cho phần này trong tệp Valentine. xlsx, được hiển thị trong hình 60-4. Bạn gán các tên phạm vi trong các ô B1: B11 cho các ô C1: C11. Phạm vi ô G3: H6 được giao tra cứutên. Giá bán của chúng tôi và các tham số chi phí được nhập vào trong các ô C4: C6.

Ảnh Quyển sách

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

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

Nếu chúng tôi tạo ra nhiều thẻ hơn là theo yêu cầu, số đơn vị còn lại bằng dấu theo yêu cầu sản xuất trừ; Nếu không, không có đơn vị nào được bỏ qua. Chúng tôi tính toán chi phí xử lý của chúng tôi trong ô C10 với công thức unit_disp_cost * if (sản xuất>Demand, được sản xuất-Demand, 0). Cuối cùng, trong ô C11, chúng tôi tính toán lợi nhuận của chúng tôi là 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 từng số lượng sản xuất và kiểm đếm các lợi nhuận dự kiến của chúng tôi cho mỗi số lượng. Tình huống này là một bảng dữ liệu hai chiều đi kèm với quá trình cứu hộ của chúng tôi. (Xem chương 15, "phân tích độ nhạy với bảng dữ liệu" để biết chi tiết về các 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

Trong phạm vi ô A16: A1015, hãy nhập các số 1 – 1000 (tương ứng với thử nghiệm 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 ô, rồi trên tab trang đầu trong nhóm sửa , bấm vào tô, rồi chọn chuỗi để hiển thị hộp thoại chuỗi . Trong hộp thoại chuỗi , được hiển thị trong hình 60-6, nhập giá trị bước của 1 và giá trị dừng của 1000. Trong chuỗi trong khu vực, chọn tùy chọn cột , rồi bấm OK. Các số 1 – 1000 sẽ được nhập vào cột A bắt đầu từ ô A16.

Ảnh Quyển sách

Tiếp theo, chúng tôi nhập số lượng sản phẩm có thể có của chúng tôi (10.000, 20.000, 40.000, 60.000) trong các ô B15: E15. Chúng tôi muốn tính lợi nhuận cho từng bản dùng thử (1 đến 1000) và mỗi số lượng sản phẩm. Chúng tôi tham khảo công thức cho lợi nhuận (được tính toán trong ô C11) ở ô 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 hiện đã sẵn sàng để lừa Excel vào mô phỏng 1000 của yêu cầu cho từng số lượng sản xuất. Chọn phạm vi bảng (A15: E1014), sau đó trong nhóm công cụ dữ liệu trên tab dữ liệu, hãy bấm vào nếu phân tích, rồi chọn bảng dữ liệu. Để thiết lập một bảng dữ liệu hai chiều, hãy chọn số lượng sản phẩm của chúng tôi (ô C1) là ô nhập hàng và chọn bất kỳ ô trống nào (chúng tôi đã chọn ô I14) làm ô nhập cột. Sau khi bấm OK, Excel mô phỏng các giá trị nhu cầu 1000 cho mỗi số lượng đơn hàng.

Để tìm hiểu lý do tại sao hoạt động này, hãy cân nhắc các giá trị được đặt bởi bảng dữ liệu trong phạm vi ô C16: C1015. Đối với mỗi trong số các ô này, Excel sẽ sử dụng giá trị 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 sau đó được ghi lại trong ô C16. Sau đó, giá trị đầu vào ô của cột 2 đượ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 được nhập vào ô C17.

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

Ảnh hưởng đến quyết định của chúng tôi     Nếu chúng tôi đã sản xuất 20.000 thay vì thẻ 40.000, giảm lợi nhuận dự kiến của chúng tôi khoảng 22 phần trăm nhưng rủi ro của chúng tôi (như được đo độ lệch chuẩn của lợi nhuận), giảm gần như 73 phần trăm. Vì vậy, nếu chúng ta rất mong muốn có nguy cơ, hãy sản xuất thẻ 20.000 có thể là quyết định đúng. Ngẫu nhiên, việc sản xuất thẻ 10.000 luôn có độ lệch chuẩn của 0 thẻ vì nếu chúng tôi sản xuất 10.000 thẻ, chúng tôi sẽ luôn bán tất cả chúng mà không có bất kỳ dư thừa.

Lưu ý:  Trong sổ làm việc này, tùy chọn tính toán được đặt là tự động ngoại trừ các bảng. (Sử dụng lệnh tính toán 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 được tính toán lại trừ khi chúng tôi nhấn F9, đó là một ý tưởng tốt vì bảng dữ liệu lớn sẽ làm chậm hoạt động của bạn nếu nó tính toán lại mỗi khi bạn nhập vào trang tính của mình. Lưu ý rằng trong ví dụ này, bất cứ khi nào 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 lần bạn nhấn F9, một chuỗi số 1000 ngẫu nhiên được dùng để tạo yêu cầu cho từng số lượng đơn hàng.

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

Ảnh Quyển sách

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

Ảnh Quyển sách

Chúng tôi là 95 phần trăm đảm bảo lợi nhuận của chúng tôi khi 40.000 lịch được đặt hàng nằm giữa $56.687 và $62.589.

  1. Một đại lý GMC tin rằng nhu cầu cho 2005 phái đại sứ sẽ được phân bố bình thường bằng 200 và độ lệch tiêu chuẩn là 30. Chi phí của ông nhận được một phái viên là $25.000, và ông ấy bán một phái viên cho $40.000. Một nửa của tất cả các phái kỳ không được bán với giá đầy đủ có thể được bán với $30.000. Anh đang cân nhắc việc đặt hàng 200, 220, 240, 260, 280 hoặc 300 phái. Ngài nên đặt hàng bao nhiêu?

  2. Một siêu thị nhỏ đang cố gắng xác định có bao nhiêu bản sao của tạp chí People , họ nên đặt hàng mỗi tuần. Họ tin rằng nhu cầu của họ đối với mọi người được điều chỉnh bởi các biến ngẫu nhiên rời rạc sau đây:

    Ứng

    Probability

    15

    0,10

    31

    0,20

    29

    0,30

    25

    0,25

    35

    0,15

  3. Siêu thị trả $1,00 cho mỗi bản sao của mọi người và bán nó cho $1,95. Mỗi bản sao không bán được có thể được trả về cho $0,50. Có bao nhiêu bản sao của những người cần lưu trữ đơn hàng?

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.

Lưu ý:  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. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

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

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.

×