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

참고: 사용자 언어로 가능한 한 빨리 가장 최신의 도움말 콘텐츠를 제공하고자 합니다. 이 페이지는 자동화를 통해 번역되었으며 문법 오류나 부정확한 설명을 포함할 수 있습니다. 이 목적은 콘텐츠가 사용자에게 유용하다는 것입니다. 이 페이지 하단의 정보가 도움이 되었다면 알려주세요. 쉽게 참조할 수 있는 영어 문서가 여기 있습니다.

이 문서는 L. Wayne Winston Microsoft Excel 데이터 분석 및 모델링 비즈니스를 조정 합니다.

  • 대상 사용자 몬테 카를로 시뮬레이션

  • 셀에 = rand ()를 입력 하면 어떻게 되나요?

  • 개별 임의의 변수 값을 어떻게 시뮬레이션할 수 있나요?

  • 일반 임의의 변수 값을 어떻게 시뮬레이션할 수 있나요?

  • 인사말 카드 회사 생성 하기 위해 얼마나 많은 카드를 어떻게 확인할 수 있나요?

정확 하 게 확정 되지 이벤트 추정 하고자 합니다. 예를 들어 새 제품의 현금 흐름 양수 순 현재 가치 (NPV) 있다고 확률 란? 우리 투자 포트폴리오의 위험 요소는 무엇 인가요? 몬테 카를로 시뮬레이션 불확실성 표시 한 다음 컴퓨터 수천 횟수에 재생 하는 모델 상황을 수 있습니다.

참고:  이름을 몬테 카를로 시뮬레이션 1930 및 1940 년대 필요한 지, 비용을 atom 폭탄 체인 반응 정상적으로 작동 하는 확률을 예상 하는 동안 수행 컴퓨터 시뮬레이션에서 가져옵니다. 이 작업에 관련 된 physicists 제공 받은 시뮬레이션 몬테 카를로코드명 되므로, 도박 큰 팬 했습니다.

다음 5 개의 장 몬테 카를로 시뮬레이션 수행 하려면 Excel을 사용 하는 방법의 예 표시 됩니다.

대부분의 회사 몬테 카를로 시뮬레이션 의사 결정 프로세스의 중요 한 부분으로 사용합니다. 다음은 몇 가지 예입니다.

  • 일반 모터, Proctor 및 도박을 할 Pfizer, 로마 myers 라는 Squibb 및 김소미 Lilly 사용 하 여 시뮬레이션 예측 평균 구하기와 새 제품의 위험 요소입니다. 으로에이 정보가 제품은 시장로 돌아와 확인 하려면 CEO에서 사용 됩니다.

  • 으로 회사에 대 한 순이익 예측, 구조적 및 구매 비용 예측의 민감도 위험 (예: 이자율 변경 내용 및 환율 변동)의 다양 한 종류를 결정 하는 데 등 활동에 대 한 시뮬레이션을 사용 합니다.

  • Lilly 시뮬레이션을 사용 하 여 각 마약에 대 한 최적의 공장 용량을 확인 합니다.

  • Proctor 및 도박을 할 시뮬레이션 모델링 하 고 울타리 외환 위험이 최상으로 사용 합니다.

  • Sears 시뮬레이션을 사용 하 여 공급 업체에서 각 제품 라인 단위 순서를 지정 해야 결정 — 예를 들어 올해 주문 해야 할 Dockers 바지 쌍의 수입니다.

  • 유 및 마약 회사 확장, 축소, 또는 프로젝트를 연기 하는 옵션의 값 등 시뮬레이션 "실제 옵션" 값을 사용 합니다.

  • 재무 계획 몬테 카를로 시뮬레이션을 사용 하 여 해당 클라이언트의 은퇴에 대 한 최적의 투자 전략 결정 합니다.

셀에 수식 = rand ()를 입력 하면 듣게를 동일 하 게 0과 1 사이의 값 가정 하는 숫자입니다. 따라서 약 25%의 시간을 구해야 숫자 0.25; 보다 작거나 같음 정도의 구해야 숫자는 시간의 10% 이상의 0.90 등에입니다. RAND 함수 작동 방식을 보여 주기 위해 살펴보세요 파일 Randdemo.xlsx, 그림 60-1을 표시 합니다.

책 이미지

참고:  Randdemo.xlsx 파일을 열 때 그림 60-1 같은 난수 표시 되지 않습니다. RAND 함수 생성 된 워크시트를 열 때 또는 워크시트에 새 정보를 입력 하면 숫자가 항상 자동으로 다시 계산 됩니다.

먼저, 복사할 C3 셀에서 C4:C402 수식 = rand (). 그런 다음 C3:C402 데이터범위의 이름을 지정 합니다. 그런 다음 F 열에서 400 임의의 숫자 (F2 셀)의 평균을 추적 한 0 및 0.25, 0.25 및 0.50, 0.50 및 0.75, 0.75과 1 사이의 분수를 확인 하려면 COUNTIF 함수를 사용 하 여 수 있습니다. F9 키를 누르면 임의의 숫자 다시 계산 됩니다. 400 숫자의 평균은 약 0.5와 정도의 결과의 25% 인지 0.25 교대에서 항상 표시 합니다. 이러한 결과 난수를 정의와 일관성을 유지 합니다. 다른 셀의 RAND에서 생성 된 값은 독립적 참고 수도 있습니다. 예를 들어 난수를 생성 하는 경우 C3 셀은 큰 숫자 (예: 0.99),이 알려줍니다. 아무 다른 난수 생성 된 값에 대 한 합니다.

가정 달력에 대 한 요구는 다음과 같은 개별 임의의 변수 적용 됩니다.

요청

probability

10,000

0.10

20,000,000

0.35

40, 000

0.3

60,000

0.25

아웃을 재생 하거나 많은 시간을 일정에 대 한이 요구 시뮬레이션 Excel는 어떻게 보유할 수는? 요령은 일정에 대 한 가능한 요구와 RAND 함수 수 있는 각 값을 연결 하는 것입니다. 다음과 같은 배정은 10, 000 demand는 사이의 발생 하는 10%의 시간을 확인 합니다.

요청

할당 된 난수

10,000

0.10 미만

20,000,000

보다 큼 또는 0.10, 크거나 같고 0.45 보다 작은

40, 000

보다 큼 또는 0.45, 크거나 같고 0.75 미만인

60,000

0.75 보다 크거나

Demand 시뮬레이션을 보여 주기 위해 그림 60-2의 다음 페이지에 표시 되는 파일 Discretesim.xlsx 살펴봅니다.

책 이미지

우리 시뮬레이션 키를 조회 테이블 범위 F2:G5 ( 조회라고 함)에서 시작 하 난수를 사용 하는 것입니다. 보다 큰 또는 0으로 및 0.10 미만의 난수 10, 000; demand 필요할지 보다 큰 또는 0.10 크거나 같고 0.45 미만의 난수 20000; demand 필요할지 보다 큰 또는 0.45 크거나 같고 0.75 미만의 난수 40, 000; demand 필요할지 하 고 임의의 숫자 0.75 보다 크거나 60, 000 demand 필요할지 합니다. 복사 하 여 C3에서 C4:C402 수식 rand ()400 난수를 생성 합니다. 그런 다음 400 평가판 또는 반복 수식 VLOOKUP(C3,lookup,2)B4:B402에 b 3을 복사 하 여 일정 요청을 생성 합니다. 이 수식은 0.10 보다 작은 모든 난수 생성 10, 000 demand, 0.10과 0.45 사이의 모든 난수 생성 20000 등에 요청을 부여할 수 있습니다. F8:F11 셀 범위에서 각 요청 생성 400 반복 분수를 확인 하려면 COUNTIF 함수를 사용 합니다. 임의의 숫자를 다시 계산 하려면 F9 키를 눌러 우리 시뮬레이션 된 확률 우리 가정된 demand 확률 가까이 됩니다.

모든 셀에 수식 NORMINV(rand(),mu,sigma)입력 시뮬레이션된 평균 mu 와 표준 편차 시그마하는 데 기본 임의의 변수 값을 생성 합니다. 이 절차는 그림 60-3에 표시 되는 파일 Normalsim.xlsx에 나와 있습니다.

책 이미지

400 trials 또는 기본 임의의 변수 40, 000의 평균 및 10, 000의 표준 편차에 대 한 반복 시뮬레이션할 한다고 가정 하겠습니다. (E1 및 E2 셀에 다음이 값을 입력 한 있습니다 이러한 셀의 의미시그마각각 이름을.) 400 다른 난수를 생성 C5:C403 C4에서 수식 = rand ()을 복사 합니다. 1에서 b 4로 복사 B5:B403 NORMINV(C4,mean,sigma) 수식 40, 000 평균과 10, 000의 표준 편차와 일반 임의의 변수에서 다양 한 400 평가판 값을 생성 합니다. 임의의 숫자를 다시 계산 하려면 F9 키를 눌러 우리 평균 40, 000 및 10, 000 가까이 표준 편차 가까이 남아 있습니다.

기본적으로, 수식 NORMINV(p,mu,sigma)x임의의 숫자에 대 한 평균 mu 와 표준 편차 시그마기본 임의의 변수 p번째 백분위 수를 생성합니다. 예를 들어 약 77th의 백분위 수 기본 임의의 변수 40, 000의 평균 및 10, 000의 표준 편차 난수 0.77 C4 셀에 B4 셀에 생성 (그림 60-3 참고).

이 섹션에서는 의사 결정 도구로 Monte 까를 시뮬레이션을 사용할 수 있는 방법을 표시 됩니다. 가정 발렌타인 카드에 대 한 요구는 다음과 같은 개별 임의 변수로 적용 됩니다.

요청

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에이 섹션에 대 한 데이터를 찾을 수 있습니다. 셀 C1:C11에 셀 B1:B11에서 범위의 이름을 지정 합니다. 셀 범위 G3:H6 이름 조회할당 됩니다. 우리 판매 가격 및 비용 매개 변수 셀 C4:C6에 입력 됩니다.

책 이미지

평가판 생산 수량 (이 예에서는 40, 000) C1 셀에 입력할 수 있습니다. 다음에 수식 = rand ()와 셀 c 2의에서 난수를 만듭니다. 앞에서 설명한 대로 수식 VLOOKUP(rand,lookup,2)와 C3 셀에 카드에 대 한 요구를 시뮬레이션할 수 있습니다. (VLOOKUP 수식에서는 rand 는 not RAND 함수, C3 셀에 지정 된 셀 이름입니다.)

판매 단위 수가 우리 프로덕션 수량 및 demand 작은 값입니다. 수식이 포함 된 우리 수익을 계산 C8 셀에 MIN (생성된, demand) * unit_price합니다. C9 셀에 수식이 포함 된 총 생산 비용 계산 생산 * unit_prod_cost합니다.

단위 수가 demand; 뺀 equals 프로덕션 남겨진 요청 하는 것 보다 더 많은 카드를 도출 하는 경우 그렇지 않으면 단위 남아 있습니다. 수식 unit_disp_cost*IF(produced>demand,produced–demand,0)와 C10 셀이 폐기 비용을 계산합니다. 마지막으로, 셀 c 11의에서 우리 계산 수익 수익 – total_var_cost-total_disposing_cost으로 합니다.

각 프로덕션 수량에 대 한 횟수를 계산 합니다 (예: 1000) F9 키를 누른 각 수량에 대 한 예상된 수익 수를 셉니다 효율적으로 하겠습니다. 이 문제를 보려면 편리 양방향 데이터 표는 하나입니다. (데이터 테이블에 대 한 자세한 내용은 15 장 "민감도 분석 된 데이터 테이블을"를 참조 합니다.) 이 예제에 사용 되는 데이터 테이블 그림 60-5에 표시 됩니다.

책 이미지

셀 범위 A16:A1015 번호 1-1000 (1000 평가판이에 해당)을 입력 합니다. 하나의 쉽게 이러한 값을 만드는 방법은 A16 셀에 1 을 입력 하 여 시작입니다. 셀을 선택 하 고 탭의 편집 그룹에서 채우기를 클릭 하 고 되풀이 대화 상자를 표시 하려면 계열 을 선택 합니다. 그림 60-6에 표시 된 계열 대화 상자에서 1 단계 값 및 1000 중지 값을 입력 합니다. 일련의 영역에서 옵션을 선택한 다음 확인을 클릭 합니다. 번호 1-1000 됩니다 A16 셀의 시작 열에 입력 합니다.

책 이미지

다음 셀 B15:E15에는 가능한 제작 수량 (10, 000, 20, 000, 40, 000, 60, 000) 입력합니다. 놓쳐서는 안 각 평가판 번호 (1 ~ 1000)에 대 한 수익을 계산 하 고 각 프로덕션 수량입니다. 언급할 수익 (에서 계산 된 셀 c 11) 우리 데이터 표 (a 15)의 왼쪽 위 셀에 대 한 수식에 = c 11을입력 하 여.

준비가 이제 요령은 Excel의 각 프로덕션 수량에 대 한 요구 1000 반복 시뮬레이션으로 합니다. 표 범위 (A15:E1014) 하 고 선택한 다음 데이터 탭의 데이터 도구 그룹에서 이유 분석을 클릭 다음 데이터 테이블을 선택 합니다. 양방향 데이터 표를 설정 하려면 행 입력 셀과 우리 프로덕션 수량 (C1 셀)을 선택 하 고 열 입력 셀으로 (I14 셀 선택) 빈 셀을 선택 합니다. 확인을 클릭 하면 Excel에서는 각 주문 수량에 대 한 1000 demand 값 시뮬레이션 합니다.

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

B13 셀에서 수식 AVERAGE(B16:B1015)C13:E13를 복사 하 여 각 프로덕션 수량에 대 한 평균 시뮬레이션된 수익을 계산 합니다. 셀 b 14 C14:E14 STDEV(B16:B1015)수식에 복사 하 여 표준 편차는 시뮬레이션된 수익에 대 한 각 주문 수량을 계산 합니다. F9 키를 눌러 우리 때마다 1000 반복 demand의 각 주문 수량에 대 한 시뮬레이션 됩니다. 항상 40, 000 카드를 만드는 가장 큰 예상된 수익을 생성 됩니다. 따라서 40, 000 카드 생성 적절 한 의사 결정 인지 표시 됩니다.

위험 결정에 미치는 영향     40, 000 카드 대신 20000 생산는 예상된 수익 22% 정도 삭제 하지만 (으로 수익의 표준 편차도 측정 됨)이 위험 거의 73%를 삭제 합니다. 따라서 우리는 위험을 매우 averse 면 20, 000 카드 생성 올바른 결정 수 있습니다. 참고로, 10, 000 카드를 항상 생성 10, 000 카드를 도출 하는 경우는 언제 든 지 판매 남아 있는 모든 개체 없이 들은 모두 때문에 0 카드의 표준 편차에 있습니다.

참고:  이 통합 문서 계산 옵션 테이블을 제외 하 고 자동으로 설정 됩니다. (수식 탭의 계산 그룹에서 계산 명령 사용 합니다.) 이렇게이 설정 하면 우리 F9 키를 눌러을 하는 것이 좋습니다 대용량 데이터 표 느려집니다 작업 내용을 워크시트에 입력할 때마다 다시 계산 하는 경우 때문에 하지 않는 한 우리 데이터 테이블 계산 되지 않습니다. 메모는이 예제에서는 F9 키를 누를 때 평균 수익 변경 됩니다. F9 키를 눌러 때마다 다른 난수 1000 시퀀스가 각 주문 수량에 대 한 요구를 생성 하 사용 되기 때문에 발생 합니다.

평균 수익에 대 한 신뢰 구간     이 경우에는 자연 질문은 어떤 간격으로 우리가 true 평균 수익에 있는지 95%? 이 간격 평균 수익에 대 한 신뢰 구간을 95%라고 합니다. 다음 수식을 통해 시뮬레이션 출력 평균의 95% 신뢰 구간은 계산 됩니다.

책 이미지

셀 J11 40, 000 일정 수식 D13–1.96*D14/SQRT(1000)를 사용 하 여 만든 경우 평균 수익에 95% 신뢰 구간에 대 한 하한값을 계산 합니다. J12 셀의 상한값 우리 95% 신뢰 구간을 D13+1.96*D14/SQRT(1000)수식 사용 하 여 계산합니다. 이러한 계산 그림 60-7 표시 됩니다.

책 이미지

평균 수익 40, 000 일정 정렬 하는 경우 $56,687 $62,589 사이의 인지 95% 있습니다.

  1. GMC 대리점 생각 2005 사용한 엔보이 대 한 요구 200 평균과 30의 표준 편차와 함께 정상적으로 배포 합니다. 엔보이 받을 그의 비용 $25000, 이며 엔보이 $ 40, 000에 대 한 판매입니다. 반 가격으로 전체 판매 되지 모든 사용한 엔보이 30, 000 달러에 대 한 판매 수 있습니다. 상대가 200, 220, 240, 260, 280, 또는 300 사용한 엔보이 순서 고려 하는 합니다. 얼마나 많은 그 주문 해야?

  2. 작은 슈퍼마켓 사람 잡지 각 주를 주문 해야 매수를 결정 하려고 합니다. 하다 사용자 에 대 한 요구는 다음과 같은 개별 임의의 변수 적용 됩니다.

    요청

    probability

    15

    0.10

    20

    수가 0.20

    25

    0.30을 입력

    30

    0.25

    35

    0.15

  3. 사용자 의 각 복사본에 대 한 $1.00 지급 $1.95에 대 한 판매 하는 수퍼마켓 합니다. $0.50에 대 한 각 unsold 복사본을 반환할 수 있습니다. 사용자 의 매수 해야 저장소 순서?

추가 지원

Excel 기술 커뮤니티의 전문가에게 질문하고, Answers 커뮤니티에서 지원을 받고, Excel 사용자 의견에서 새로운 기능이나 개선 사항을 제안해 보세요.

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×