Hướng dẫn can you do a monte carlo simulation in excel? - bạn có thể làm mô phỏng monte carlo trong excel không?

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

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

• Điều gì xảy ra khi bạn gõ = rand () trong một ô?

• 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 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 thông thường?

• Làm thế nào một công ty thiệp chúc mừng có thể xác định có bao nhiêu thẻ để sản xuất?

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 mà một sản phẩm mới của dòng tiền sẽ có giá trị hiện tại ròng dương (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 mô hình hóa các tình huống không chắc chắn và sau đó phát chúng trên máy tính hàng ngàn lần.

Lưu ý: & nbsp; Tên mô phỏng Monte Carlo xuất phát từ các mô phỏng máy tính được thực hiện trong những năm 1930 và 1940 để ước tính xác suất phản ứng dây chuyền cần thiết cho một quả bom nguyên tử để phát nổ sẽ hoạt động thành công. Các nhà vật lý tham gia vào công việc này là những người hâm mộ cờ bạc lớn, vì vậy họ đã đưa ra các mô phỏng tên mã Monte Carlo. The name Monte Carlo simulation comes from the computer simulations performed during the 1930s and 1940s to estimate the probability that the chain reaction needed for an atom bomb to detonate would work successfully. The physicists involved in this work were big fans of gambling, so they gave the simulations the code name 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 sử dụng mô phỏng Monte Carlo như một phần quan trọng trong quá trình ra 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 sử 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 CEO sử dụng để xác định sản phẩm nào xuất hiện trên thị trường.

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

• Lilly sử dụng mô phỏng để xác định công suất thực vật tối ưu cho mỗi loại thuốc.

• Proctor và Gamble sử dụng mô phỏng để mô hình hóa và phòng ngừa rủi ro ngoại hối tối ưu.

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

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

• Các nhà hoạch định tài chính sử dụng mô phỏng Monte Carlo để xác định các chiến lược đầu tư tối ưu cho khách hàng của họ nghỉ hưu.

Khi bạn nhập công thức = rand () vào một ô, bạn sẽ nhận được một số có khả năng giả sử bất kỳ giá trị nào từ 0 đến 1. Do đó, khoảng 25 phần trăm thời gian, bạn sẽ nhận được số thấp 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 làm thế nào chức năng RAND hoạt động, hãy xem tệp randdemo.xlsx, được hiển thị trong Hình 60-1.

Lưu ý: & nbsp; Khi bạn mở tệp randdemo.xlsx, bạn sẽ không thấy các số ngẫu nhiên tương tự đượ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 bảng tính được mở hoặc khi thông tin mới được nhập vào bảng tính. When you open the file Randdemo.xlsx, you will not see the same random numbers shown in Figure 60-1. The RAND function always automatically recalculates the numbers it generates when a worksheet is opened or when new information is entered into the worksheet.

Đầu tiên, sao chép từ ô C3 đến C4: C402 Công thức = rand (). Sau đó, bạn đặt tên cho phạm vi dữ liệu C3: C402. Sau đó, trong cột F, bạn có thể theo dõi trung bình của 400 số ngẫu nhiên (ô 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 được tính toán lại. Lưu ý rằng trung bình của 400 số luôn là khoảng 0,5 và khoảng 25 phần trăm kết quả là 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 lưu ý rằng các giá trị được tạo bởi RAND trong các ô khác nhau là độc lập. Ví dụ: nếu số ngẫu nhiên được tạo trong ô C3 là một số lượng lớn (ví dụ: 0,99), nó không 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 về lịch được điều chỉnh bởi biến ngẫu nhiên rời rạc sau đây:

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

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

Chìa khóa cho 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 (Tra cứu được đặt tên). 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 lại nhu cầu 20.000; số ngẫu nhiên lớn hơn hoặc bằng 0,45 và dưới 0,75 sẽ mang lại nhu cầu 40.000; và số ngẫu nhiên lớn hơn hoặc bằng 0,75 sẽ mang lại nhu cầu 60.000. Bạn tạo 400 số ngẫu nhiên bằng cách sao chép từ C3 đến C4: C402 Công thức Rand (). Sau đó, bạn tạo ra 400 thử nghiệm hoặc lặp lại nhu 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 nào nhỏ hơn 0,10 đều tạo ra nhu cầu 10.000, bất kỳ số ngẫu nhiên nào trong khoảng từ 0,10 đến 0,45 đều tạo ra nhu cầu 20.000, v.v. Trong phạm vi ô F8: F11, sử dụng chức năng Countif để xác định tỷ lệ 400 lần lặp của chúng tôi mang lại cho mỗi nhu 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 gần với xác suất nhu cầu giả định của chúng tôi.

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

Hãy giả sử rằng chúng tôi muốn mô phỏng 400 thử nghiệm hoặc lặp lại, cho một biến ngẫu nhiên thông thường với giá trị trung bình là 40.000 và độ lệch chuẩn là 10.000. . Sao chép từ B4 đến B5: B403 Công thức Norminv (C4, trung bình, Sigma) tạo ra 400 giá trị thử nghiệm khác nhau từ một biến ngẫu nhiên thông thường với giá trị trung bình là 40.000 và độ lệch chuẩn là 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 số X ngẫu nhiên, công thức Norminv (P, MU, Sigma) tạo ra phần trăm PTH của một biến ngẫu nhiên thông thường với MU trung bình và độ lệch chuẩn. Ví dụ, số ngẫu nhiên 0,77 trong ô C4 (xem Hình 60-3) tạo ra trong ô B4 xấp xỉ phần trăm thứ 77 của một biến ngẫu nhiên thông thường với giá trị trung bình là 40.000 và độ lệch chuẩn là 10.000.

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

Thẻ chúc mừng được bán với giá 4,00 đô la và chi phí biến đổi của mỗi thẻ là 1,50 đô la. Thẻ còn lại phải được xử lý với chi phí 0,20 đô la mỗi thẻ. Có bao nhiêu thẻ nên được in?

Về cơ bản, chúng tôi mô phỏng từng số lượng sản xuất có thể (10.000, 20.000, 40.000 hoặc 60.000) nhiều lần (ví dụ: 1000 lần lặp). Sau đó, chúng tôi xác định số lượng đơn hàng nào mang lại lợi nhuận trung bình tối đa trong 1000 lần lặp. 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 tên phạm vi trong các ô B1: B11 cho các ô C1: C11. Phạm vi ô G3: H6 được chỉ định tra cứu tên. Giá bán và các thông số chi phí của chúng tôi được nhập vào ô C4: C6.

Bạn có thể nhập số lượng sản xuất 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 đây, bạn mô phỏng nhu cầu về 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, không phải hàm RAND.)

Số lượng đơn vị được bán là số lượng sản xuất và nhu cầu nhỏ hơn của chúng tôi. Trong Cell C8, bạn tính toán doanh thu của chúng tôi với công thức tối thiểu (sản xuất, nhu cầu)*unter_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*unter_prod_cost.

Nếu chúng ta sản xuất nhiều thẻ hơn nhu cầu, số lượng đơn vị còn lại tương đương với nhu cầu sản xuất trừ đi; nếu không thì không có đơn vị nào còn lại. 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 đơn vị_disp_cost*if (sản xuất> Nhu cầu, sản xuất, 0). Cuối cùng, trong Cell 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 mỗi số lượng sản xuất và kiểm đếm 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 trong đó một bảng dữ liệu hai chiều đến giải cứu của chúng tôi. .

Trong phạm vi ô A16: A1015, nhập các số 1 Hàng1000 (tương ứng với 1000 thử nghiệm 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 ô, sau đó trên tab Home trong nhóm chỉnh sửa, nhấp vào điền và chọn loạt để hiển thị hộp thoại Sê -ri. Trong hộp thoại sê-ri, được hiển thị trong Hình 60-6, nhập giá trị bước là 1 và giá trị dừng là 1000. Trong chuỗi trong khu vực, chọn tùy chọn Cột, sau đó bấm OK. Các số 1 Hàng1000 sẽ được nhập vào cột A bắt đầu trong ô A16.Home tab in the Editing group, click Fill, and select Series to display the Series dialog box. In the Series dialog box, shown in Figure 60-6, enter a Step Value of 1 and a Stop Value of 1000. In the Series In area, select the Columns option, and then click OK. The numbers 1–1000 will be entered in column A starting in cell A16.

Tiếp theo, chúng tôi nhập số lượng sản xuất có thể 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 số dùng thử (1 đến 1000) và mỗi số lượng sản xuất. Chúng tôi đề cập đến công thức cho lợi nhuận (tính toán trong ô C11) trong ô 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.

Bây giờ chúng tôi đã sẵn sàng để lừa Excel để mô phỏng 1000 lần lặp lại nhu cầu cho mỗi 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, nhấp vào những gì nếu phân tích, sau đó chọn bảng dữ liệu. Để thiết lập bảng dữ liệu hai chiều, hãy chọn số lượng sản xuất của chúng tôi (ô C1) làm ô đầu vào hàng và chọn bất kỳ ô trống nào (chúng tôi chọn ô I14) làm ô đầu vào cột. Sau khi nhấp vào OK, Excel mô phỏng 1000 giá trị nhu cầu cho mỗi số lượng đặt hàng.

Để hiểu lý do tại sao điều này hoạt động, hãy xem xét các giá trị được đặt bởi bảng dữ liệu trong phạm vi ô C16: C1015. Đối với mỗi ô này, Excel sẽ sử dụng giá trị 20.000 trong C1. Trong C16, giá trị ô đầu vào cột là 1 được đặt trong một ô trống và số ngẫu nhiên trong ô C2 được 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ột là 2 được đặt trong một ô trống và số ngẫu nhiên trong C2 một lần nữa tính toán lại. Lợi nhuận tương ứng được nhập trong ô C17.

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

Tác động của rủi ro đối với quyết định của chúng tôi & nbsp; & nbsp; & nbsp; & nbsp; Nếu chúng tôi sản xuất 20.000 thay vì 40.000 thẻ, lợi nhuận dự kiến ​​của chúng tôi giảm khoảng 22 phần trăm, nhưng rủi ro của chúng tôi (được đo bằng độ lệch chuẩn của lợi nhuận) giảm gần 73 %. Do đó, nếu chúng ta cực kỳ không thích rủi ro, việc sản xuất 20.000 thẻ có thể là quyết định đúng đắn. Ngẫu nhiên, việc sản xuất 10.000 thẻ luôn có độ lệch chuẩn là 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ần bất kỳ thức ăn thừa nào.     If we produced 20,000 instead of 40,000 cards, our expected profit drops approximately 22 percent, but our risk (as measured by the standard deviation of profit) drops almost 73 percent. Therefore, if we are extremely averse to risk, producing 20,000 cards might be the right decision. Incidentally, producing 10,000 cards always has a standard deviation of 0 cards because if we produce 10,000 cards, we will always sell all of them without any leftovers.

Lưu ý: & nbsp; Trong sổ làm việc này, tùy chọn tính toán được đặt thành tự động ngoại trừ các bảng. . Bạn gõ một cái gì đó vào bảng tính của bạn. 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 bởi vì mỗi lần bạn nhấn F9, một chuỗi khác nhau gồm 1000 số ngẫu nhiên được sử dụng để tạo ra nhu cầu cho mỗi số lượng đặt hàng. In this workbook, the Calculation option is set to Automatic Except For Tables. (Use the Calculation command in the Calculation group on the Formulas tab.) This setting ensures that our data table will not recalculate unless we press F9, which is a good idea because a large data table will slow down your work if it recalculates every time you type something into your worksheet. Note that in this example, whenever you press F9, the mean profit will change. This happens because each time you press F9, a different sequence of 1000 random numbers is used to generate demands for each order quantity.

Khoảng tin cậy cho lợi nhuận trung bình & nbsp; & nbsp; & nbsp; & nbsp; Một câu hỏi tự nhiên cần đặt ra trong tình huống này là, chúng ta 95 % chắc chắn rằng lợi nhuận có nghĩa là thực sự sẽ giảm? Khoảng thời gian này được gọi là khoảng tin cậy 95 phần trăm cho lợi nhuận trung bình. Khoảng tin cậy 95 phần trăm cho giá trị trung bình của bất kỳ đầu ra mô phỏng nào được tính bằng công thức sau:     A natural question to ask in this situation is, into what interval are we 95 percent sure the true mean profit will fall? This interval is called the 95 percent confidence interval for mean profit. A 95 percent confidence interval for the mean of any simulation output is computed by the following formula:

Trong Cell J11, bạn tính toán giới hạn thấp hơn cho khoảng tin cậy 95 phần trăm trên lợi nhuận trung bình khi 40.000 lịch được tạo ra với công thức D13 Tiết1.96*D14/sqrt (1000). Trong ô J12, bạn tính toán giới hạn trên cho 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). Những tính toán này được thể hiện trong Hình 60-7.

Chúng tôi chắc chắn 95 phần trăm rằng lợi nhuận trung bình của chúng tôi khi 40.000 lịch được đặt hàng là từ $ 56,687 đến $ 62,589.

1. Một đại lý GMC tin rằng nhu cầu đối với các đặc phái viên năm 2005 sẽ được phân phối bình thường với mức trung bình 200 và độ lệch chuẩn là 30. Chi phí nhận được một đặc phái viên của anh ta là 25.000 đô la, và anh ta bán một đặc phái viên với giá 40.000 đô la. Một nửa trong số tất cả các đặc phái viên không được bán với giá đầy đủ có thể được bán với giá 30.000 đô la. Ông đang xem xét đặt hàng 200, 220, 240, 260, 280 hoặc 300 phái viên. Anh ta 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í mọi người mà họ nên đặt hàng mỗi tuần. Họ tin rằng nhu cầu của họ đối với người dân bị chi phối bởi biến ngẫu nhiên riêng biệt 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 thị trả $ 1 cho mỗi bản sao của mọi người và bán nó với giá $ 1,95. Mỗi bản sao chưa bán có thể được trả lại với giá 0,5 đô la. Cửa hàng nên đặt hàng bao nhiêu bản sao?

Cần sự giúp đỡ nhiều hơn?

Bạn luôn có thể hỏi một chuyên gia trong cộng đồng công nghệ Excel & nbsp; hoặc nhận được hỗ trợ trong cộng đồng câu trả lời.