몬테카를로 시뮬레이션 엑셀 | 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation) 빠른 답변

당신은 주제를 찾고 있습니까 “몬테카를로 시뮬레이션 엑셀 – 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation)“? 다음 카테고리의 웹사이트 you.tfvp.org 에서 귀하의 모든 질문에 답변해 드립니다: https://you.tfvp.org/blog/. 바로 아래에서 답을 찾을 수 있습니다. 작성자 통계파랑 이(가) 작성한 기사에는 조회수 2,068회 및 좋아요 11개 개의 좋아요가 있습니다.

Table of Contents

몬테카를로 시뮬레이션 엑셀 주제에 대한 동영상 보기

여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!

d여기에서 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation) – 몬테카를로 시뮬레이션 엑셀 주제에 대한 세부정보를 참조하세요

몬테카를로 시뮬레이션(Monte Carlo Simulation)
– 오픈카톡 상담 문의 –
https://open.kakao.com/me/statparang
– 통계분석 수행방법 참고 포스팅 –
https://blog.naver.com/statstorm/222155560156
– SPSS/AMOS/SAS/STATA/R/PYTHON 과외문의 –
https://blog.naver.com/statstorm/222116340085

몬테카를로 시뮬레이션 엑셀 주제에 대한 자세한 내용은 여기를 참조하세요.

몬테카를로 시뮬레이션의 Excel – Microsoft Support

Monte Carlo 시뮬레이션은 서로 다른 결과의 확률을 모델링합니다. … 이 문서는 Wayne L. Winston의 Microsoft Excel 데이터 분석 및 비즈니스 모델링에서 적용 …

+ 여기에 자세히 보기

Source: support.microsoft.com

Date Published: 7/21/2021

View: 2523

엑셀로 풀어보는 몬테카를로 분석

몬테카를로 분석 또는 시뮬레이션에 대해서 얘기해본다. Monte Carlo simulation … 엑셀에서 rand 명령어를 통해 난수를 발생시켜보자.

+ 더 읽기

Source: hujubkang.tistory.com

Date Published: 8/26/2022

View: 9395

Excel을 사용하여 몬테카를로 시뮬레이션 만들기 – 조항 2022

Microsoft Excel과 주사위 게임을 사용하여 몬테카를로 시뮬레이션을 개발합니다. Monte Carlo Simulation은 무작위 추출을 사용하여 계산과 복잡한 문제를 수행하는 …

+ 여기에 더 보기

Source: kor.talkingofmoney.com

Date Published: 7/29/2022

View: 2180

엑셀에서 몬테카를로 시뮬레이션으로 원주율 구하기

내가 하려는 건, 프로그래밍과 무관하게 살아 온 사람이 엑셀로만 몬테카를로 시뮬레이션을 경험해 보기이다. 1) 우선 정사각형 안에 충분한 개수의 점을 …

+ 자세한 내용은 여기를 클릭하십시오

Source: wizmusa.tistory.com

Date Published: 5/9/2021

View: 370

Monte Carlo Simulation Formula In Excel – Tutorial And …

Monte Carlo Simulation is a process of using probability curves to determine the likelihood of an outcome. You may scratch your head here and say…

+ 여기에 표시

Source: excel.tv

Date Published: 5/1/2022

View: 3503

[금융수학] 8. 코스피지수의 몬테카를로 시뮬레이션 (엑셀 계산)

[금융수학] 8. 코스피지수의 몬테카를로 시뮬레이션 (엑셀 계산). 2019년 1월 7일; Posted by: 인사이트캠퍼스; Category: 금융/AI/IT 기사.

+ 여기에 더 보기

Source: insightcampus.co.kr

Date Published: 11/18/2022

View: 1149

주제와 관련된 이미지 몬테카를로 시뮬레이션 엑셀

주제와 관련된 더 많은 사진을 참조하십시오 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation). 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.

엑셀을 활용한 통계분석 - (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation)
엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation)

주제에 대한 기사 평가 몬테카를로 시뮬레이션 엑셀

  • Author: 통계파랑
  • Views: 조회수 2,068회
  • Likes: 좋아요 11개
  • Date Published: 2021. 5. 13.
  • Video Url link: https://www.youtube.com/watch?v=RIU65mZ31Us

몬테카를로 시뮬레이션의 Excel

이 섹션에서는 몬테카를로 시뮬레이션을 의사 결정 도구로 사용할 수 있는 방법을 설명합니다. Valentine’s Day 카드에 대한 수요는 다음과 같은 불연도 임의의 변수에 의해 관리된다고 가정합니다.

수요 probability 10,000 0.10 20,000,000 0.35 40,000 0.3 60,000 0.25

인사말 카드는 $4.00에 판매하며, 각 카드를 만드는 가변 비용은 $1.50입니다. 남은 카드는 카드당 $0.20의 비용으로 파기해야 합니다. 인쇄할 카드는 몇 장인가요?

기본적으로 가능한 각 프로덕션 수량(10,000, 20,000, 40,000 또는 60,000)을 여러 번 시뮬레이션합니다(예: 1000번의 이터니에이터리). 그런 다음, 1000개 이터니에 대한 최대 평균 이익을 얻을 수 있는 주문 수량을 결정합니다. 그림 60-4에 표시된 파일 Valentine.xlsx 섹션의 데이터를 찾을 수 있습니다. 셀 B1:B11의 범위 이름을 셀 C1:C11에 할당합니다. 셀 범위 G3:H6에는 이름 보기가 할당됩니다. 판매 가격 및 비용 매개 변수는 C4:C6 셀에 입력됩니다.

C1 셀에 평가판 프로덕션 수량(이 예제에서 40,000개)을 입력할 수 있습니다. 다음으로 = RAND() 수식을사용하여 셀 C2에서 난수 만들기 앞서 설명한 바와 같이 VLOOKUP(rand, lookup,2)을사용하여 셀 C3의 카드 수요를 시뮬레이션합니다. (VLOOKUP 수식에서 rand는 RAND 함수가 아닌 셀 C3에 할당된 셀 이름입니다.)

판매되는 단위 수는 생산 수량 및 수요보다 작습니다. 셀 C8에서 MIN(생산,수요)*unit_price. 셀 C9에서 생산된 수식*unit_prod_cost.

수요보다 더 많은 카드를 생산하는 경우 남은 단위 수가 프로덕션 수요를 희석하는 수와 같습니다. 그렇지 않으면 단위가 남지 않습니다. C10 셀의 폐기 비용을 unit_disp_cost*IF(생산된>수요,생산-수요,0)를 사용하여 처리 비용을 계산합니다. 마지막으로 C11 셀에서 수익을 수익으로 계산합니다. total_var_cost-total_disposing_cost.

각 생산 수량에 대해 F9를 여러 번(예: 1000)을 누르고 각 수량에 대한 예상 수익을 측정하는 효율적인 방법을 원합니다. 이 상황은 양측 데이터 테이블이 구조에 오는 상황입니다. (데이터 테이블에 대한 자세한 내용은 “데이터 테이블을 사용하여 민감도 분석” 15장을 참조합니다.) 이 예제에 사용된 데이터 테이블은 그림 60-5에 표시됩니다.

셀 범위 A16:A1015에서 숫자 1~1000(1000개 평가판에 해당)을 입력합니다. 이러한 값을 만드는 한 가지 쉬운 방법은 A16 셀에 1을 입력하여 시작하는 것입니다. 셀을 선택한 다음 편집 그룹의 홈 탭에서 채우기를 클릭하고 시리즈를 선택하여 시리즈 대화 상자를 표시합니다. 그림 60-6에 표시된 시리즈 대화 상자에 단계 값 1과 중지 값 1000을 입력합니다. 시리즈 인 영역에서열 옵션을 선택한 다음 확인 을 클릭합니다. 숫자 1~1000은 A16 셀에서 시작하는 열 A에 입력됩니다.

다음으로 B15:E15 셀에 가능한 생산 수량(10,000, 20,000, 40,000, 60,000)을 입력합니다. 각 평가판 번호(1~1000)와 각 생산 수량에 대한 수익을 계산합니다. =C11 을 입력하여 데이터 테이블(A15)의 왼쪽 위 셀에서 이익 수식(셀 C11에서 계산)을 참조합니다.

이제 각 생산 수량에 Excel 1000회의 수요를 시뮬레이션할 준비가되었습니다. 테이블 범위(A15:E1014)를 선택한 다음 데이터 탭의 데이터 도구 그룹에서 What If Analysis를 클릭한 다음 데이터 테이블을 선택합니다. 양면 데이터 테이블을 설정하려면 프로덕션 수량(셀 C1)을 행 입력 셀로 선택하고 빈 셀(셀 I14 선택)을 열 입력 셀로 선택합니다. 확인을 클릭한 Excel 주문 수량에 대한 1000개 수요 값을 시뮬레이션합니다.

이 값이 작동하는 이유를 이해하기 위해 셀 범위 C16:C1015의 데이터 테이블에 배치된 값을 고려합니다. 이러한 각 셀에 대해 Excel C1 셀에서 20,000 값을 사용하게 됩니다. C16에서 열 입력 셀 값 1은 빈 셀에 배치하고 C2 셀의 난수는 다시 계산됩니다. 그런 다음 해당 수익은 C16 셀에 기록됩니다. 그런 다음 2의 열 셀 입력 값이 빈 셀에 배치된 다음, C2의 난수는 다시 계산됩니다. 해당 수익은 C17 셀에 입력됩니다.

셀 B13에서 C13:E13으로 수식 AVERAGE(B16:B1015)을복사하여 각 생산 수량에 대한 평균 시뮬레이션된 수익을 계산합니다. 셀 B14에서 C14:E14로 수식 STDEV(B16:B1015)를복사하여 각 주문 수량에 대해 시뮬레이션된 수익의 표준 편차를 계산합니다. F9를 누를 때마다 각 주문 수량에 대해 1000회의 수요가 시뮬레이션됩니다. 40,000장의 카드를 생성하면 항상 가장 큰 예상 수익을 얻을 수 있습니다. 따라서 40,000장의 카드를 생성하는 것이 적절한 결정인 것으로 보인다.

결정에 대한 위험의 영향 카드 40,000개 대신 20,000개 생산한 경우 예상 수익은 약 22% 감소하지만 위험(표준 이익 편차로 측정)은 거의 73% 감소합니다. 따라서 위험에 매우 까다로우면 20,000장의 카드를 생산하는 것이 올바른 결정일 수 있습니다. 부수적으로, 10,000장의 카드를 생산하는 경우 남은 카드 없이 항상 모든 카드를 판매하기 때문에 항상 0장의 표준 편차가 있습니다.

참고: 이 통합 문서에서 계산 옵션은 표를 제외한 자동으로 설정됩니다. (수식 탭의 계산 그룹에서 계산 명령을 사용합니다.) 이 설정을 사용하면 F9를 누르지 않으면 데이터 테이블이 다시 계산되지 않습니다. 워크시트에 무언가를 입력할 때마다 큰 데이터 테이블이 작업을 느리게 만들 수 있기 때문에 좋은 아이디어입니다. 이 예제에서는 F9를 누를 때마다 평균 이익이 변경됩니다. F9를 누를 때마다 1000개 난수의 다른 순서가 각 주문 수량에 대한 요구를 생성하는 데 사용하기 때문에 발생합니다.

평균 이익에 대한 신뢰 간격 이 상황에서 질문하는 자연스러운 질문은 어떤 간격으로 실제 평균 이익이 떨어질지 95%입니다. 이 간격을 평균 이익에 대한 95% 신뢰 간격이라고 합니다. 시뮬레이션 출력의 평균에 대한 95% 신뢰 간격은 다음 수식으로 계산됩니다.

셀 J11에서 D13-1.96*D14/SQRT(1000)수식으로 40,000개 달력을 생성하는 경우 평균 이익의 95% 신뢰 구간에 대한 하한을 계산합니다. 셀 J12에서는 수식 D13+1.96*D14/SQRT(1000)를사용하여 95% 신뢰 구간의 상한을 계산합니다. 이러한 계산은 그림 60-7에 표시됩니다.

40,000개 달력 주문 시 평균 수익이 $56,687에서 $62,589 사이인 것으로 95%입니다.

엑셀로 풀어보는 몬테카를로 분석

몬테카를로 분석 또는 시뮬레이션에 대해서 얘기해본다.

Monte Carlo simulation

워낙 유명한 분석기법이라 이해하기 쉽게 다양하게 기술된 블로그들이 많다.

여기서는 “난수를 이용한 모든 시뮬레이션” 이라고 정의해본다.

방법은 다음과 같다. 1. 모든 미래의 상황을 불확실하다고 가정한다. 2. 0과 1 사이의 난수를 뽑아서 미래의 상황이라고 가정한다. 3. 상황 내에서 평균 이익을 최대한으로 올리기 위한 의사결정을 파악한다.

비고. 평균 이익을 최대한으로 하는 것 뿐만 아니라 신뢰구간을 통해 최대의 이익을 구할 수 있도록 의사결정 변수를 선택한다.

문제를 다음과 같이 가정해본다. (첨부파일 참고)

몬테카를로 예제1.xlsx

(내가 근무하는 부서가 QA팀일 뿐, 사실상 QA팀에서 저런 일을 하지는 않는다. 대게~)

과거 실적을 통해 예상되는 수요량과 그에 따른 확률을 구해볼 수 있었다. (가정)

엑셀에다가 계산자료의 기본 정보를 입력한다.

대당 판매가 / 생산비용 / 재고 처리를 통한 이득 및 확률

위 문제에서 난수를 500번 생성한다고 하였으니,

엑셀에서 rand 명령어를 통해 난수를 발생시켜보자.

=rand() 입력하고, 드래그~

생성한 난수는 엑셀에서 다른 작업을 하면서 계속 바뀌게 된다.

일단 발생한 난수를 고정해보자.

다음과 같이 발생된 난수를 고정해볼 수 있다.

예상되는 수요량을 계산해보자.

Vlookup을 활용하여 난수에 따른 예상 수요량을 불러와 보자.

(첨부파일 참고)

예상 수요량에 따른 수익도 함께 고려해보자.

수요량이 생산량보다 많은 경우 생산량보다 더 팔 수가 없다. (물건이 없으니까)

또한 생산량이 많아도 수요량이 적다면 덜 팔 수 밖에 없다. (고객이 사질 않으니까)

그래서 min(생산량, 수요량)을 활용하여 수익을 다음과 같이 구해볼 수 있다.

다음은 생산비용을 구해본다.

생산비용의 경우 생산량에 생산비용을 곱하여 구해볼 수 있다.

(매우 단순화시킨 계산법을 이해해주기 바랍니다.)

다음은 이익을 구해본다.

이익의 경우, 수익에서 생산비용을 빼고, 재고처리 이득금액을 더하여 산출할 수 있다.

수익 – 생산비용 + 재고처리이득금액

자, 우리가 궁금했던건 이익이었다.

난수로 인해서 이익까지 뽑았으니 평균이익을 뽑아보자.

더불어 기술통계 몇개를 함께 뽑아보자.

신뢰구간도 함께 구해본다.

생산량의 변화가 평균 이익이 어떻게 달라지는지 확인하기 위해서는

위 과정을 여러번 돌려볼 필요가 있다.

물론 처음에만 작성해 놓으면 다음부터는 쉽게 구할 수는 있지만,.. 이 또한 불편하다.

엑셀의 데이터 표를 활용하여, 생산량 변화에 따른 평균이익의 변화를 체크해보자.

아래 표를 확인해보니, 평균이익이 최대가 되는 생산량이 3,000으로 계산되었다.

(1 Lot를 500개로 가정)

그래프도 함께 구해보자.

생산량 3000개까지는 평균이익이 증가하다가 넘어서부터는 감소하는걸 확인할 수 있다.

또한 생산량 6000부터는 오히려 생산하는 것이 손해를 보고 있음을 확인하였다.

무조건 만들어서 파는게 좋은게 아니다.

위와 같이 몬테카를로 분석 또는 시뮬레이션을 활용해 볼 수 있다.

실무에서 얼만큼 활용하는지 궁금하다.

내가 있는 곳은 위의 분석 기법을 모두가 안다.

그리고 모두가 활용하지 않는다.

안다. 그러나 활용하지 않는다.

그게 자랑이 아닐텐데?

끝.

Excel을 사용하여 몬테카를로 시뮬레이션 만들기

Microsoft Excel과 주사위 게임을 사용하여 몬테카를로 시뮬레이션을 개발합니다. Monte Carlo Simulation은 무작위 추출을 사용하여 계산과 복잡한 문제를 수행하는 수학적 수치 방법입니다. 오늘날, 금융, 물리학, 화학, 경제 등 여러 분야에서 널리 사용되고 있으며 핵심적인 역할을합니다.

몬테카를로 시뮬레이션

Monte Carlo 방법은 1947 년 Nicolas Metropolis에 의해 발명되었으며 무작위 및 확률 론적 방법을 사용하여 복잡한 문제를 해결하고자한다. “몬테카를로”라는 용어는 유럽 엘리트가 도박을하는 장소로 널리 알려진 모나코 행정 구역에서 유래합니다. 문제가 너무 복잡하고 직접 계산으로 처리하기 어려운 경우에는 Monte Carlo 방법을 사용합니다. 반복 횟수가 많으면 정규 분포를 시뮬레이션 할 수 있습니다.

Monte Carlo 시뮬레이션 방법은 적분에 대한 확률을 계산하고 편미분 방정식을 풀어서 확률 적 결정에서 위험에 대한 통계적 접근 방법을 도입합니다. 몬테 카를로 시뮬레이션을 생성하는 많은 고급 통계 도구가 있지만 Microsoft Excel을 사용하여 법칙 및 균일 법칙을 시뮬레이션하고 수학적 토대를 우회하는 것이 더 쉽습니다.

Monte Carlo 시뮬레이션의 경우 실험의 결과를 제어하고 설명하는 많은 주요 변수를 분리하고 많은 수의 무작위 샘플을 수행 한 후에 확률 분포를 지정합니다. 모형으로 주사위 게임을 봅시다.

주사위 게임

주사위 굴림 방법은 다음과 같습니다.

• 플레이어는 6면 3 번 주사위 3 개를 던집니다.

• 3 스로우의 합계가 7 또는 11이면 플레이어가 승리합니다.

• 3 스로우의 합계가 3, 4, 5, 16, 17 또는 18 인 경우, 플레이어는 패배합니다.

• 합계가 다른 결과라면, 플레이어는 다시 플레이하고 주사위를 굴린다.

• 플레이어가 다시 주사위를 던질 때, 게임은 첫 번째 라운드에서 결정된 합계와 총합이 같을 때 플레이어가이기는 것을 제외하고는 같은 방식으로 계속 진행됩니다.

데이터 테이블을 사용하여 결과를 생성하는 것이 좋습니다. 또한 몬테카를로 시뮬레이션을 준비하려면 5,000 개의 결과가 필요합니다.

1 단계 : 주사위 굴리기 이벤트

먼저, 3 주사위 각각 50 개 굴림의 결과를 사용하여 다양한 데이터를 개발합니다. 이렇게하려면 “RANDBETWEEN (1.6)”기능을 사용하는 것이 좋습니다. 따라서 F9를 클릭 할 때마다 새로운 롤 결과 세트가 생성됩니다. “결과”셀은 3 개의 롤에서 얻은 결과의 합계입니다.

.

2 단계 : 결과 범위

그런 다음 첫 번째 라운드와 그 이후 라운드에서 가능한 결과를 식별 할 수있는 범위의 데이터를 개발해야합니다. 3 열 데이터 범위 아래에 제공됩니다.첫 번째 열에는 1에서 18까지의 숫자가 있습니다.이 숫자는 주사위를 3 번 ​​굴린 후의 가능한 결과를 나타냅니다. 최대 값은 3 * 6 = 18입니다. 세포 1과 2의 경우 3 주사위를 사용하여 1 또는 2를 얻는 것이 불가능하기 때문에 결과는 N / A입니다. 최소값은 3입니다.

두 번째 열에는 첫 번째 라운드 이후의 가능한 결론이 포함됩니다. 초기 진술서에 명시된 바와 같이, 결과에 따라 플레이어가 승리 (승) 또는 패 (패) 또는 리플레이 (리 롤) 중 하나 (총 3 개의 주사위 굴림).

세 번째 열에는 후속 라운드에 대한 가능한 결론이 등록됩니다. 우리는 “If”함수를 사용하여 이러한 결과를 얻을 수 있습니다. “이렇게하면 얻은 결과가 첫 번째 라운드에서 얻은 결과와 같으면 승리합니다. 그렇지 않으면 원래 주사위의 초기 규칙을 따라 주사위를 다시 굴릴 지 여부를 결정합니다.

3 단계 : 결론

이 단계에서는 50 개의 주사위 굴림 결과를 확인합니다. 첫 번째 결론은 인덱스 함수로 얻을 수 있습니다. 이 함수는 첫 번째 라운드에서 가능한 결과를 검색합니다. 결과는 얻은 결과에 해당합니다. 예를 들어 아래 그림 에서처럼 6을 얻으면 다시 게임을합니다. 함수에 중첩 된 “Or”함수와 인덱스 함수를 사용하여 다른 주사위 굴림 결과를 얻을 수 있습니다. 이 함수는 Excel에 “이전 결과가 승패라면, 주사위를 굴리는 것을 멈 춥니 다. 왜냐하면 우리가이기거나 잃어 버렸기 때문에 완료되었습니다. 그렇지 않은 경우 다음 가능한 결론의 컬럼으로 이동하여 결과의 ​​결론을 확인합니다.

4 단계 : 주사위 롤 수

이제 잃거나 이기기 전에 필요한 주사위 굴림의 수를 결정합니다. 이렇게하려면 “Counting”기능을 사용할 수 있습니다.이 기능을 사용하려면 Excel에서 “Re-roll”결과를 계산하고 숫자 1을 추가해야합니다. 추가 라운드가 있기 때문에 1을 더하고, 최종 결과를 얻습니다 (이기 든 지든).

5 단계 : 시뮬레이션

다양한 시뮬레이션 결과를 추적 할 수있는 범위를 개발합니다. 이렇게하려면 세 개의 열을 만듭니다. 첫 번째 열에 포함 된 숫자 중 하나는 5,000입니다. 두 번째 열에서는 50 개의 주사위 굴림 결과를 찾습니다. 세 번째 열, 열의 제목에서 우리는 최종 상태 (승리 또는 손실)를 얻기 전에 주사위 굴리기 횟수를 찾습니다.

그러면 피처 데이터 또는 테이블 데이터 테이블을 사용하여 민감도 분석 테이블을 만듭니다 (이 감도는 두 번째 테이블과 세 번째 열에 삽입됩니다). 이 민감도 분석에서 1 – 5,000의 이벤트 수가 파일의 A1 셀에 삽입되어야합니다. 실제로 빈 셀을 선택할 수 있습니다. 아이디어는 단순히 매번 다시 계산해야하므로 공식을 손상시키지 않고 새로운 주사위 굴림 (새로운 시뮬레이션 결과)을 얻을 수 있습니다.

6 단계 : 확률

우리는 최종적으로 이기고지는 확률을 계산할 수 있습니다. 우리는 “Countif”기능을 사용하여이 작업을 수행합니다.이 공식은 “승리”와 “잃음”의 수를 계산하여 총 이벤트 수 5,000으로 나눈 다음 각각의 비율을 구합니다. 마침내 우리는 승리 결과를 얻는 확률이 73.2 %이고 패배를 얻는 것이 26.8 %라는 것을 아래에서 확인합니다.

엑셀에서 몬테카를로 시뮬레이션으로 원주율 구하기

최근에 새삼스럽게 ‘몬테카를로 시뮬레이션’을 다시 파고 있다. 추론과 예측에 효과적인 방법임을 절감했기 때문이다. 복습 삼아 몬테카를로 시뮬레이션의 유명한 예제 중에 ‘원주율 구하기’를 찾아 봤다가, 재미 삼아 엑셀에서 VBA 없이 구현해 보았다.

난수로 점을 찍어 정사각형 넓이와 해당 정사각형의 한 변의 길이와 동일한 길이의 지름인 원의 넓이의 비로서 원주율 구하기

출처: 몬테카를로 방법으로 원주율 구하기 (http://www.playsw.or.kr/repo/cast/109)

먼저 정말 친절하게 설명한 몬테카를로 방법으로 원주율 구하기(http://www.playsw.or.kr/repo/cast/109) 문서를 꼭 참조하길 바란다. 진국은 이미 저 문서에 있다. 내가 하려는 건, 프로그래밍과 무관하게 살아 온 사람이 엑셀로만 몬테카를로 시뮬레이션을 경험해 보기이다.

1) 우선 정사각형 안에 충분한 개수의 점을 찍어야 한다.

아래 수식을 셀에 복사하고 백만 줄 전체에 붙여 넣어도 된다. ([Ctrl]+[↓]를 알아서 잘 쓰시길.)

X좌표 수식 Y좌표 수식

=RAND()*(IF(MOD(RANDBETWEEN(1, 2), 2)=0,1,-1)) 좌동

위 수식은 (1,1), (1,-1), (-1, 1), (-1,-1)을 꼭지점으로 하는 정사각형 내에 점을 찍는 취지로서, 이런 식으로 보이게 된다.

X좌표 예시

Y좌표 예시

0.066007122

-0.268583866

2) 점과 원 중심 간의 거리를 구한다.

수식 예시

=SQRT((A1-0)^2+(B1-0)^2) 0.276575909

3) 점이 원 밖인지 원 안인지 구분한다. (지름이 2인 원)

수식 예시

=IF(C1>1, “밖”, “원”) 원

아래와 같은 셀을 복사하여 대량으로 붙여 넣으면 된다. PC 성능에 따라 백만 줄 정도 만들 때에는 수십 초가 걸리기도 한다.

A

B

C

D

1

0.152992 0.618691 0.637327 원 2

-0.33291 -0.68673 0.763168 원 3

-0.54012 -0.8925 1.043209 밖

4) 충분히 복사한 원 안팎에 있는 난수 점들의 비율을 계산하여 대략적인 원주율을 구한다.

시험 삼아 천 개, 십만 개, 백만 개의 점들을 두고 원주율을 계산해 보았다.

회수 수식 결과 오차 1,000회 =COUNTIF($D1:$D1000, “원”)/COUNTA($D1:$D1000)*4 3.16 -0.018407346 십만회 =COUNTIF($D1:$D100000, “원”)/COUNTA($D1:$D100000)*4 3.13516 0.006432654 백만회 =COUNTIF($D:$D, “원”)/COUNTA($D:$D)*4 3.142059 -0.000466673

(=PI()-결과값)

위 결과는 아름답게도 백만 회의 난수 생성을 거친 결과가 실제 π와 제일 유사하지만, 몇 차례 돌려 본 결과로는 십만 개의 점을 가지고 원주율을 구했을 때의 결과가 π에 더 가까웠던 적도 많았다. 그렇다면 십만 정도로 해도 현실적으로는 효과가 있다고 판단할 만하며, 미심쩍으면 이삽십만 개만 만들어도 충분하다고 본다.

예제 파일은 아래 링크에 두었다.

링크 https://1drv.ms/f/s!AAk_f8uIr69wgpVy 폴더에서 GettingPiWithExcel.xlsx 파일을 열거나 다운로드 하세요.

이렇게 엑셀의 난수 만들기(RAND, RANDBETWEEN) 기능이 꽤 강력하니, 중간과정의 수식을 좀 더하면 프로그래밍 없이도 몬테카를로 시뮬레이션이 가능하다. 막연한 느낌에 휘둘리는 게 싫었다면, 보다 일관적인 예측과 추정으로 이끌어 주는 몬테카를로 시뮬레이션의 세계를 접해 보길 바란다. 재미 들리면 VBA부터 시작하여 여러 프로그래밍 언어로 개발을 시작하게 될지도 모른다. ☺

Monte Carlo Simulation Formula in Excel – Tutorial and Download

What is Monte Carlo Simulation?

Monte Carlo Simulation is a process of using probability curves to determine the likelihood of an outcome. You may scratch your head here and say… “Hey Rick, a distribution curve has an array of values. So how exactly do I determine the likelihood of an outcome?” And better yet, how do I do that in Microsoft Excel without any special add-ins

Thought you would never ask.

This is done by running the simulation thousands of times and analyzing the distribution of the output. This is particularly important when you are analyzing the output of several distribution curves that feed into one another.

Example:

# of Units Sold may have a distribution curve

multiplied by Market price, which may have another distribution curve

minus variable wages which have another curve

etc., etc.

Once all these distributions are intermingled, the output can be quite complex. Running thousands of iterations (or simulations) of these curve may give you some insights. This is particularly useful in analyzing potential risk to a decision.

Describe Monte Carlo

When describing Monte Carlo Simulation, I often refer to the 1980’s movie War Games, where a young Mathew Broderick (before Ferris Bueller) is a hacker that uses his dial up modem to hack into the Pentagon computers and start World War 3. Kind of. He then had the Pentagon computers do many simulations of the games Tic Tac Toe to teach the computer that no one will will a nuclear war – and save the world in the process.

Thanks Ferris. You’re a hero.

Here’s a glimpse of the movie to show you big time Monte Carlo in action. I am assuming that you will overlook the politics, the awkward man hugging and of course, Dabney Coleman.

The Monte Carlo Simulation Formula

Distribution Curves

There are various distribution curves you can use to set up your Monte Carlo simulation. And these curves may be interchanged based on the variable. Microsoft doesn’t have a formula called “Do Monte Carlo Simulation” in the menu bar 🙂

Uniform Distribution

In a uniform distribution, there is equal likelihood anywhere between the minimum and a maximum. A uniform distribution looks like a rectangle.

Normal (Gaussian) Distribution

This is also your standard bell shaped curve. This Monte Carlo Simulation Formula is characterized by being evenly distributed on each side (median and mean is the same – and no skewness). The tails of the curve go on to infinity. So this may not be the ideal curve for house prices, where a few top end houses increase the average (mean) well above the median, or in instances where there is a hard minimum or maximum. An example of this may be the minimum wage in your locale. Please note that the name of the function varies depending on your version.

Lognormal Distribution

A distribution where the logarithm is normally distributed with the mean and standard deviation. So the setup is similar to the normal distribution, but please note that the mean and standard_dev variables are meant to represent the logarithm.

Poisson Distribution

This is likely the most underutilized distribution. By default, many people use a normal distribution curve when Poisson is a better fit for their models. Poisson is best described when there is a large distribution near the very beginning that quickly dissipates to a long tail on one side. An example of this would be a call center, where no calls are answered before second ZERO. Followed by the majority of calls answered in the first 2 intervals (say 30 and 60 seconds) with a quick drop off in volume and a long tail, with very few calls answered in 20 minutes (allegedly).

The purpose here is not to show you every distribution possible in Excel, as that is outside the scope of this article. Rather to ensure that you know that there are many options available for your Monte Carlo Simulation. Do not fall into the trap of assuming that a normal distribution curve is the right fit for all your data modeling. To find more curves, to go the Statistical Functions within your Excel workbook and investigate. If you have questions, pose them in the comments section below.

Building The Model

For this set up we will assume a normal distribution and 1,000 iterations.

Input Variables

The setup assumes a normal distribution. A normal distribution requires three variables; probability, mean and standard deviation. We will tackle the mean and standard deviation in our first step. I assume a finance forecasting problem that consists of Revenue, Variable and Fixed Expenses. Where Revenue minus Variable Expenses minus Fixed Expenses equals Profit. The Fixed expenses are sunk cost in plant and equipment, so no distribution curve is assumed. Distribution curves are assumed for Revenue and Variable Expenses.

First Simulation

The example below indicates the settings for Revenue. The formula can be copy and pasted to cell D6 for variable expenses. For Revenue and expenses we you the function NORM.INV() where the parameters are:

Probability = the function RAND() to elicit a random number based on the other criteria within the distribution.

Mean = The mean used in the Step 1. For Revenue it is C3.

Standard Deviation = The Standard Deviation used in Step 1. For Revenue it is C4

Since RAND() is used as the probability, a random probability is generated at refresh. We will use this to our advantage in the next step.

1,000 Simulations

There are several ways to do 1,000 or more variations. The simplest option is to take the formula from step #2 and make it absolute. Then copy and paste 1,000 times. That’s simple, but not very fancy. And if Ferris Bueller can save the world by showing a new Tic Tac Toe game to a computer, then we can spice up this analysis as well. Let’s venture into the world of tables.

First we want to create an outline for a table. We do this by listing the numbers 1 to 1,000 in rows. In the example image below, the number list starts in B12.

in the next column, in cell C12, we will reference the first iteration.

Next highlight the area where we want to house the 1,000 iterations

Select Data > Data Tables

For Column input cell: Select a blank cell. In the download file, cell D11 is selected

Select OK

Once OK is selected from the previous step, a table is inserted that autopopulates the 1,000 simulations

Summary Statistics

Once the simulations are run, it is time to gather summary statistics. This can be done a number of ways. In this example I used the COUNTIF() function to determine the percentage of simulations that are unprofitable, and the likelihood of a profit greater than $1 Million. As expected, the likelihood of greater than $1M hovers around 50%. This is because we used normal distribution curves that are evenly distributed around the mean, which was $1M. The likelihood of losing money is 4.8%. This was gathered by using the COUNTIF() function to count the simulations that were less than zero, and dividing by the 1,000 total iterations.

[금융수학] 8. 코스피지수의 몬테카를로 시뮬레이션 (엑셀 계산) – 인사이트캠퍼스

금융 수학 (8)

코스피지수의 몬테카를로 시뮬레이션 (엑셀 계산)

[update 2016.11.24] 아래 수식은 주가모형의 시뮬레이션과 ELS 상품의 이해 (3) 에 동영상으로 설명되어 있습니다.

이전 포스트에서 기하 브라운 운동으로 만든 주가 모형을 이용하면, 미래 주가에 대한 시뮬레이션을 해볼 수 있다. 주가 모형에 대한 확률분포를 이용하여 몬테카를로 시뮬레이션이라는 기법을 적용해 보면 아래 그림과 같이 시뮬레이션을 해볼 수 있다. 아래 그림은 2012년 3월 23일 현재부터 100일 후의 KOSPI 지수를 10회 시뮬레이션 해본 결과이다. 현재의 변동성 (역사적 변동성 : HV), 연간 KOSPI 지수의 평균 수익률 등의 확률요소를 이용하여 100일 후의 주가를 예측해 보는 것이다. 시행 횟수가 많아지면 100일 후 주가의 분포는 정규분포를 나타낼 것이고, 정규분포 상에서 신뢰구간을 정하면, 주가의 폭 (밴드)을 예측해 볼 수 있다.

몬테카를로 시뮬레이션 기법을 적용하기 위해, 주가의 모형을 분석해 보자. 아래 식은 이전 포스트에서 설명한 주가의 수리적 모델이다. 식 4) 까지가 평균 수익률을 계산한 것이고, 식 5) 는 수익률에 대한 정규분포 특성을 나타낸 것이다. 주가의 로그 수익률 분포는 평균과 분산이 식 5)와 같이 되는 정규분포로 나타나고, 주가는 로그 정규분포 (Log Normal Distribution)로 나타난다. 식 3)에서 주가의 로그 수익률은 평균에서부터 표준편차의 위너 과정 (W)배 만큼 떨어진 지점으로 표현할 수 있고, 그림으로 그려보면 아래 그림과 같이 표현할 수 있다. 위너 과정 (W)에 따라 주가의 로그 수익률이 확률적으로 나타나는 것이다.

위의 식을 이용하면 엑셀에서 간단히 시뮬레이션을 해볼 수 있다. 주가의 연간 평균 수익률 (Drift 혹은 성장률)을 10%로 가정하고, 최근의 연간 변동성 (HV : 역사적변동성)은 금일 HTS에 표시된 20.59%를 기준으로 계산해 보았다. 일일 데이터로 100일간 시뮬레이션 할 것이므로, 수익률과 변동성은 모두 일일을 기준으로 단위를 조정한다 (단위를 맞추는 것이 대단히 중요하다). 각 셀의 계산식은 아래와 같다.

* 셀(B5) = 셀(B3) / 252

셀(B6) = 셀(B4) / sqrt(252) <– 일일 변동성으로 변환할 때 루트(t)를 이용 셀(B7) = 식 4)로 표현된 평균 수익률 (정규분포의 중심) = B5 – 0.5 * B6^2 ~ 일일 단위이므로, t = 1 셀(B12) ~ 셀(B111) = NORMSINV(RAND()) ~ 확률 0 ~ 1을 랜덤값으로 가질 때 표준 정규분포의 x축의 값이 나옴 ~ ex: 확률이 0.5 지점이면 중심값이므로, NORMSINV(0.5) = 0 확률이 0.975 이면 2 시그마 지점이므로, NORMSINV(0.975) = 2 셀(C12) = 식 3)으로 표현된 로그 수익률 = $B$7 + $B$6 * B12 셀(C13) = $B$7 + $B$6 * B13 . . 셀(D11) = 2026.83 <– 2012년 3월 23일 코스피 종가 셀(D12) = D(11) * EXP(C12) 셀(D13) = D(12) * EXP(C13) . . 각 셀을 위와 같이 계산하고, 열(D)인 예측 주가를 그래프로 그리면 위의 그래프가 된다. F9 키를 눌러 RAND() 값을 재계산하면 그래프의 모양이 계속 바뀌면서 시뮬레이션이 된다. 이 그래프는 한 실험에 대한 실현 (Realization)이므로, 여러 개의 실현을 만들어서 그래프를 겹쳐 그려보면 맨 위의 그래프가 나온다. 여기까지 한 개의 실험에 대한 실현값을 구해 보았다. 실제로 의미 있는 시뮬레이션이 되려면 실험의 횟수를 증가 시켜야 한다. 아래의 실험은 위의 실험을 4,000번 수행하여, 100일 후의 종가만 계산한 후 종가에 대한 히스토그램을 그린 것이다 (히스토그램 그리는 방법 참조). * 셀(B5) = 100 * B3/252 <– 100일간 Drift 셀(B6) = sqrt(100) * B4 / SQRT(252) <– 100일간 변동성 셀(B7) = B5 – 0.5 * B6^2 <– 식 4) 평균 수익률 셀(B12) = NORMSINV(RAND()) 셀(C12) = $B$7 + $B$6 * B11 <– 100일간 로그 수익률 (예측치) 셀(D12) = $B$8 * exp(C12) <– 100일후 예상 주가 (연속 복리) 셀(D13) = $B$8 * exp(C13) 셀(E12) = ln(D12) 셀(E13) = ln(D13) 각 셀을 위와 같이 계산한 후 열(D)의 100일 후 종가에 대해 히스토그램을 그리면 우측의 그림과 같이 된다. 수익률 분포는 정규분포이지만, 주가의 분포는 로그정규분포의 모양으로 왼쪽으로 약간 기울어진 것을 볼 수 있다. 평균은 2,109.28 으로 B8*exp(B7)로 구한 평균값인 2,091.21과 비슷한 결과가 나왔다. 이 분포에서 신뢰구간을 90%로 하면, 100일 후 코스피 종가는 1,690 ~ 2,588 사이에 분포할 확률이 90%가 된다고 할 수 있다. * 셀(G3) : 100일 후 종가의 평균 = AVERAGE(D12:D4011) 셀(G4) : 100일 후 종가의 표준편차 = STDEV(D12:D4011) 셀(I3) : 100일 후 로그 종가의 평균 = AVERAGE(E12:E4011) 셀(I4) : 100일 후 로그 종가의 표준편차 = STDEV(E12:E4011) 셀(G7) : 로그정규분포의 95% 지점 = LOGINV(0.95, I3, I4) = 2,588.09 셀(G8) : 로그정규분포의 5% 지점 = LOGINV(0.05, I3, I4) = 1,690.45 몬테카를로 시뮬레이션에 의한 100일 후 코스피 지수의 평균값은 2,091이고, 예측 범위는 1,690 ~ 2,588로 나왔다 (90% 신뢰구간). 현재의 코스피 지수는 2,027인데, 연간 코스피의 성장률을 10%로 대입하였기 때문에 100일 후 코스피가 2,091부근에서 나온 것은 당연한 결과이다. 그리고 평균을 중심으로 한 밴드는 역사적변동성 (HV) 20.59%의 영향을 받게 된다. 따라서 예측 결과는 두 값 모두에 의해 영향을 받게 된다. 만약 코스피 성장률과 연간 변동성을 보다 정확하게 예측할 수 있다면, 시뮬레이션 결과도 더 정확해 질 것이다. 코스피의 연간 성장률은 경제동향과 밀접한 관련이 있으므로, 거시경제적 측면의 분석이 필요할 것이다. 또한, 변동성은 역사적 변동성 (HV) 대신에 옵션의 시장가격을 통해 얻은 내재변동성 (IV : Implied Volatility)을 이용하거나, 시계열분석 기법으로 예측된 변동성을 이용할 수도 있다. 예를 들어 역사적 변동성 (HV)대신 내재변동성 (IV)를 이용해 보자. 내재변동성은 옵션시장에서 참가자들에 의해 형성된 옵션의 가격을 통해 측정해 본 기초자산 (KOSPI200)의 변동성이다. 금일 HTS 상에는 IV = 15.00%로 기록되어 있다. 이 값을 사용하면 100일 후 코스피 지수의 평균값은 2,093 이고, 예측 범위는 1,780 ~ 2,406 이 나왔다. 평균은 성장률 (Drift)에만 영향을 받으므로 변화가 없고, 예측 범위는 변동성의 영향을 받아 폭이 약간 좁아졌다. 여기서는 기본적인 데이터를 사용해서 원리만 살펴본 것이므로 예측 밴드의 폭이 상당히 넓게 나온 편이다. 가끔 증권사에서 발표하는 예측 지수의 범위도 이러한 원리로 계산되었을 것이다. [출처]8. 코스피지수의 몬테카를로 시뮬레이션 (엑셀 계산)|작성자아마퀀트

키워드에 대한 정보 몬테카를로 시뮬레이션 엑셀

다음은 Bing에서 몬테카를로 시뮬레이션 엑셀 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.

이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!

사람들이 주제에 대해 자주 검색하는 키워드 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation)

  • 엑셀
  • EXCEL
  • 몬테카를로시뮬레이션
  • 몬테카를로방법
  • 통계파랑

엑셀을 #활용한 #통계분석 #- #(15) #몬테카를로 #시뮬레이션(Monte #Carlo #Simulation)


YouTube에서 몬테카를로 시뮬레이션 엑셀 주제의 다른 동영상 보기

주제에 대한 기사를 시청해 주셔서 감사합니다 엑셀을 활용한 통계분석 – (15) 몬테카를로 시뮬레이션(Monte Carlo Simulation) | 몬테카를로 시뮬레이션 엑셀, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.

Leave a Comment