Danh mục đầu tư chứng khoán mô phỏng monte caro excel
|
Bài viết này được điều chỉnh từ Microsoft Excel Data Analysis and Business Modeling của Wayne L. Winston Show
Tổng quan
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 dòng tiền của một sản phẩm mới sẽ có giá trị hiện tại ròng (NPV) dương là bao nhiêu? Ghi chú. Cái 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 để 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 đam mê cờ bạc, vì vậy họ đã đặt tên mã cho các mô phỏng 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 Ai sử dụng 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ụ
Điều gì xảy ra khi bạn gõ =RAND() trong một ô?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 nhận bất kỳ giá trị nào trong khoảng từ 0 đến 1 như nhau. Do đó, khoảng 25 phần trăm thời gian, bạn sẽ nhận được một số nhỏ hơn hoặc bằng 0. 25; . 90, v.v. Để chứng minh chức năng RAND hoạt động như thế nào, hãy xem tệp Randdemo. xlsx, thể hiện trong Hình 60-1  Ghi chú. Khi bạn mở tệp Randdemo. xlsx, bạn sẽ không thấy các số ngẫu nhiên như 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ở trang tính hoặc khi thông tin mới được nhập vào trang tính Đầu tiên, sao chép từ ô C3 sang C4. C402 công thức =RAND(). Sau đó, bạn đặt tên cho dãy C3. Dữ liệu C402. Sau đó, trong cột F, bạn có thể theo dõi giá trị 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. Chú ý rằng trung bình cộng của 400 số luôn xấp xỉ 0. 5 và khoảng 25 phần trăm kết quả 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 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ớn (ví dụ: 0. 99), nó không cho chúng ta biết gì về giá trị của các số ngẫu nhiên khác được tạo 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?Giả sử nhu cầu về lịch bị chi phối bởi biến ngẫu nhiên rời rạc sau Nhu cầu xác suất 10.000 0. 10 20.000 0. 35 40.000 0. 3 60.000 0. 25 Làm cách nào để chúng tôi có thể thực hiện hoặc mô phỏng nhu cầu về lịch này trong Excel nhiều lần? . 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. Nhu cầu Số ngẫu nhiên được chỉ định 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 Để 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 ở trang tiếp theo Chìa 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 (tra cứu có 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; . 10 và nhỏ hơn 0. 45 sẽ mang lại nhu cầu 20.000; . 45 và nhỏ hơn 0. 75 sẽ mang lại nhu cầu 40.000; . 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 sang C4. C402 công thức RAND(). Sau đó, bạn tạo 400 thử nghiệm hoặc lặp lại nhu cầu lịch bằng cách sao chép từ B3 sang B4. B402 công thức VLOOKUP(C3,tra cứu,2). Công thức này đảm bảo rằng mọi 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 trong khoảng từ 0. 10 và 0. 45 tạo ra nhu cầu 20.000, v.v. Trong phạm vi ô F8. F11, hãy sử dụng hàm COUNTIF để xác định tỷ lệ trong số 400 lần lặp lại của chúng tôi mang lại 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, 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 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?Nếu bạn gõ vào bất kỳ ô nào 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ó trung bình mu và sigma độ lệch chuẩn. Quy trình này được minh họa trong tệp Normalsim. xlsx, thể hiện trong Hình 60-3 Giả sử chúng ta muốn mô phỏng 400 lần thử hoặc lặp lại đối với một biến ngẫu nhiên thông thường có giá trị trung bình là 40.000 và độ lệch chuẩn là 10.000. (Bạn có thể nhập các giá trị này vào các ô E1 và E2, đồng thời đặt tên cho các ô này là mean và sigma tương ứng. ) Sao chép công thức =RAND() từ C4 sang C5. C403 tạo ra 400 số ngẫu nhiên khác nhau. Sao chép từ B4 sang B5. B403 công thức NORMINV(C4,mean,sigma) tạo ra 400 giá trị thử nghiệm khác nhau từ một biến ngẫu nhiên bình 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 một số ngẫu nhiên x, công thức NORMINV(p,mu,sigma) tạo ra phân vị thứ p của một biến ngẫu nhiên thông thường với trung bình mu và sigma độ 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 vị thứ 77 của một biến ngẫu nhiên bình thường với giá trị trung bình là 40.000 và độ lệch chuẩn là 10.000 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 thiệp để sản xuất?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ụ ra quyết định như thế nào. Giả sử rằng nhu cầu về thiệp Valentine bị chi phối bởi biến ngẫu nhiên rời rạc sau Nhu 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 được bán với giá 4 đô la. 00 và chi phí biến đổi để sản xuất mỗi thẻ là $1. 50. Thẻ còn lại phải được xử lý với chi phí $0. 20 mỗi thẻ. Nên in bao nhiêu thẻ? 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 lại). Sau đó, chúng tôi xác định số lượng đặt hàng nào mang lại lợi nhuận trung bình tối đa trong 1000 lần lặp lại. Bạn có thể tìm thấy dữ liệu cho phần này trong tệp Valentine. xlsx, thể hiện trong Hình 60-4. Bạn gán tên dãy trong ô B1. B11 đến các ô C1. C11. Phạm vi ô G3. H6 được gán tên tra cứu. Các thông số về giá bán và chi phí của chúng tôi được nhập vào các ô C4. C 6 Bạn có thể nhập số lượng sản xuất thử (40.000 trong ví dụ này) vào ô 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 thẻ trong ô C3 bằng công thức VLOOKUP(rand,lookup,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à nhỏ hơn số lượng sản xuất và nhu cầu của chúng tôi. Trong ô C8, bạn tính doanh thu của chúng tôi bằng công thức MIN(sản xuất,nhu cầu)*đơn vị_giá. 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*đơn_vị_sản_phẩm Nếu chúng tôi sản xuất nhiều thẻ hơn nhu cầu, số lượng đơn vị còn lại bằng sản lượng trừ đi nhu cầu; . Chúng tôi tính toán chi phí xử lý của mình trong ô C10 với công thức unit_disp_cost*IF(sản xuất>nhu cầu,sản xuất–nhu cầu,0). Cuối cùng, trong ô C11, chúng tôi tính toán lợi nhuận của mình dưới dạng doanh thu– total_var_cost-total_dispose_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 kỳ vọng của chúng tôi cho mỗi số lượng. Tình huống này là tình huống mà bảng dữ liệu hai chiều được giải cứu. (Xem Chương 15, "Phân tích độ nhạy 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 Trong phạm vi ô A16. A1015, nhập các số 1–1000 (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 vào ô A16. Chọn ô, sau đó trên tab Trang chủ trong nhóm Chỉnh sửa, bấm vào Tô và chọn Chuỗi để hiển thị hộp thoại Chuỗi. Trong hộp thoại Sê-ri, như trong Hình 60-6, nhập Giá trị Bước là 1 và Giá trị Dừng là 1000. Trong vùng Sê-ri Trong, 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 Tiếp theo, chúng tôi nhập số lượng sản xuất có thể (10.000, 20.000, 40.000, 60.000) vào các ô B15. E15. Chúng tôi muốn tính lợi nhuận cho từng số thử nghiệm (1 đến 1000) và từng 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 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 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, bấm vào Phân tích nếu xảy ra, rồi 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 Ô 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 nhấn OK, Excel sẽ 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ủa cột là 1 được đặt trong một ô trống và số ngẫu nhiên trong ô C2 sẽ tính toán lại. Lợi nhuận tương ứng sau đó được ghi vào ô C16. Sau đó, giá trị đầu vào của ô cột là 2 được đặt trong một ô trống và số ngẫu nhiên trong C2 sẽ tính toán lại một lần nữa. 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 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 sang 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 cho mỗi số lượng đặt hàng. Mỗi lần chúng ta nhấn F9, 1000 lần lặp lại nhu cầu được mô phỏng cho mỗi số lượng đặt hàng. Sản xuất 40.000 thẻ luôn mang lại lợi nhuận kỳ vọng lớn nhất. Do đó, có vẻ như sản xuất 40.000 thẻ là quyết định đúng đắn Tác động của rủi ro đối với 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 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 phần trăm. Do đó, nếu chúng ta cực kỳ ngại rủi ro, sản xuất 20.000 thẻ có thể là quyết định đúng đắn. Ngẫu nhiên, 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 dư thừa Ghi chú. 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ừ 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. ) Cài đặ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, đây là một ý tưởng hay vì bảng dữ liệu lớn sẽ làm 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 nào đó 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 bởi vì mỗi lần bạn nhấn F9, một chuỗi 1000 số ngẫu nhiên khác nhau được sử dụng để tạo nhu cầu cho mỗi số lượng đặt 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 đặt ra trong tình huống này là, chúng ta chắc chắn 95 phần trăm rằng lợi nhuận trung bình thực sự sẽ giảm vào khoảng nào? . 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 theo công thức sau Trong ô J11, bạn tính toán giới hạn dưới cho khoảng tin cậy 95 phần trăm đối với lợi nhuận trung bình khi 40.000 lịch được tạo bằng 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 tin cậy 95 phần trăm của chúng tôi bằng 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% 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 đô la đến 62.589 đô la Các vấn đề
|
