Excel의 Monte Carlo 시뮬레이션 소개

이 문서는 Microsoft Excel 데이터 분석 및 웨인 Winston를 통한 비즈니스 모델링에서 적용 되었습니다.

  • 누가 Monte Carlo 시뮬레이션을 사용 하나요?

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

  • 불연속 임의 변수의 값을 시뮬레이션할 수 있는 방법은 무엇 인가요?

  • 일반 임의 변수의 값을 시뮬레이션할 수 있는 방법은 무엇 인가요?

  • 인사말 카드 회사에서 생성할 카드 수를 어떻게 알 수 있나요?

불확실 한 이벤트의 확률을 정확 하 게 예측 하고자 합니다. 예를 들어 새 제품의 현금 흐름에는 양수 순 현재 가치 (NPV)가 적용 되는 확률은 무엇 인가요? 투자 포트폴리오의 위험 요소는 무엇 인가요? Monte Carlo 시뮬레이션을 사용 하면 불확실성이 표시 되는 상황을 모델링 한 다음 컴퓨터에서 수천 번 재생할 수 있습니다.

참고:  이름 Monte Carlo 시뮬레이션 은 1930s 및 1940s 중에 수행 되는 컴퓨터 시뮬레이션에서 제공 하는 것으로, atom 폭탄에 필요한 체인 반응은 detonate 작동 하는 확률을 예측 합니다. 이 작업에 수반 되는 physicists는 도박의 큰 팬 이므로 시뮬레이션에 코드 이름 Monte Carlo을 제공 합니다.

다음 5 개 장에서는 Excel을 사용 하 여 Monte Carlo 시뮬레이션을 수행 하는 방법의 예를 보여 줍니다.

대부분의 회사는 의사 결정 프로세스의 중요 한 부분으로 Monte Carlo 시뮬레이션을 사용 합니다. 몇 가지 예는 다음과 같습니다.

  • 일반 모터, Proctor Gamble, Pfizer, Bristol-Myers Squibb, 엘리 Lilly을 사용 하 여 새 제품의 평균 수익률과 위험 요인을 예측 합니다. GM에서는이 정보를 사용 하 여 시장에 출시 될 제품을 결정 합니다.

  • GM은 기업의 수입 예측, 구조적 및 구매 비용 예측, 다양 한 종류의 위험 (예: 이율 변경 및 환율 변동)에 대 한 susceptibility 결정 등의 작업에 시뮬레이션을 사용 합니다.

  • Lilly는 시뮬레이션을 사용 하 여 각 약품의 최적 플랜트 용량을 결정 합니다.

  • Proctor Gamble는 시뮬레이션을 사용 하 여 외부 exchange 위험을 모델링 하 고 최적화 합니다.

  • Sears는이 연도를 정렬 해야 하는 Dockers 바지의 수와 같이 공급 업체 로부터 각 제품 라인의 단위 수를 결정 하는 데 사용 됩니다.

  • 석유 및 마약 회사는 프로젝트 확장, 계약 또는 연기 옵션의 값과 같은 "실제 옵션" 값에 시뮬레이션을 사용 합니다.

  • 재무 기획자는 Monte Carlo 시뮬레이션을 사용 하 여 클라이언트의 만료에 대 한 최적의 투자 전략을 결정 합니다.

셀에 수식 = RAND () 를 입력 하면 0에서 1 사이의 값으로 간주할 수 있는 숫자를 받습니다. 따라서 시간의 25%가 0.25 보다 작거나 같은 숫자를 가져옵니다. 10% 정도는 0.90 이상에 해당 하는 숫자를 받습니다. RAND 함수의 작동 방식에 대 한 자세한 내용은 그림 60-1에 표시 된 Randdemo 파일을 참조 하세요.

책 이미지

참고:  Randdemo 파일을 열면 그림 60-1에 나와 있는 것과 동일한 난수를 볼 수 없습니다. RAND 함수는 워크시트를 열 때 또는 워크시트에 새 정보를 입력할 때 항상 생성 되는 숫자를 자동으로 다시 계산 합니다.

먼저 셀 C3에서 C4: C402 수식 = RAND ()를 복사 합니다. 그런 다음 C3: C402 Data범위의 이름을 표시 합니다. 그런 다음 F 열에서 400 임의 숫자 (F2 셀)의 평균을 추적 하 고 COUNTIF 함수를 사용 하 여 0과 0.25, 0.25 및 0.50, 0.50, 0.75, 0.75 및 1 사이의 분수를 확인할 수 있습니다. F9 키를 누르면 임의의 숫자가 다시 계산 됩니다. 400 번호의 평균은 항상 약 0.5 이며, 결과 중 25% 정도는 0.25의 간격을 기준으로 합니다. 이러한 결과는 임의 숫자의 정의와 일관성을 유지 합니다. 또한 서로 다른 셀의 RAND를 통해 생성 된 값이 독립적 이라고 주목 하세요. 예를 들어 C3 셀에 생성 된 난수 수가 큰 숫자 (예: 0.99) 인 경우 생성 되는 다른 난수 값에 대 한 정보를 알 수 없습니다.

일정에 대 한 요구가 다음과 같은 불연속 임의 변수로 제어 된다고 가정 합니다.

Demand

probability

10,000

0.10

20,000,000

0.35

4만

0.3

60,000

0.25

이러한 달력을 여러 번 사용 하 여 Excel을 재생 하거나 시뮬레이션할 수 있는 방법은 무엇 인가요? 이 트릭은 RAND 함수의 가능 값 각각을 달력의 가능 요구에 연결 하는 것입니다. 다음 과제를 통해 1만의 수요를 10%까지 수행할 수 있습니다.

Demand

무작위로 지정 된 번호

10,000

0.10 미만

20,000,000

0.10 보다 크거나 같고 0.45 보다 작아야 합니다.

4만

0.45 보다 크거나 같고 0.75 보다 작아야 합니다.

60,000

0.75 보다 크거나 같음

요청 시뮬레이션을 설명 하려면 다음 페이지의 그림 60-2에 표시 된 Discretesim 파일을 참조 하세요.

책 이미지

시뮬레이션의 핵심은 난수를 사용 하 여 표 범위에서 조회를 시작 하는 것입니다 (named lookup). G5. 0 보다 크거나 같고 0.10 보다 작은 난수는 1만의 요구를 생성 합니다. 0.10 보다 크거나 같고 0.45 보다 작은 난수는 2만의 수요를 산출 합니다. 0.45 보다 크거나 같고 0.75 보다 작은 난수는 4만의 수요를 산출 합니다. 0.75 보다 크거나 같은 난수는 6만의 수요를 산출 합니다. C3에서 C4: C402 수식 RAND ()를 복사 하 여 400 난수를 생성 합니다. 그런 다음 B3에서 B4로 복사 하 여 B402 평가판 또는 반복을 400 생성 하 고 수식 VLOOKUP (C3, lookup, 2)을 클릭 하 여 일정을 요청 합니다. 이 수식은 0.10 보다 작은 임의의 숫자가 1만의 수요를 생성 하 고 0.10 및 0.45의 난수는 2만의 수요를 생성 합니다. 셀 범위 F8: F11, COUNTIF 함수를 사용 하 여 400 반복의 소수 부분을 확인 하 여 각 요청을 생성 합니다. F9 키를 눌러 난수를 다시 계산 하면 예상 되는 수요 확률에 가까운 확률을 시뮬레이션할 수 있습니다.

수식 NORMINV (rand (), mu, 시그마)를 입력 하면 평균 mu 와 표준 편차 시그마를 포함 하는 일반 임의 변수의 시뮬레이트된 값이 생성 됩니다. 이 절차는 그림 60-3에 나와 있는 파일 Normalsim에 설명 되어 있습니다.

책 이미지

400 시험 또는 반복을 사용 하 여 평균이 4만이 고 표준 편차가 1만 인 일반 임의 변수에 대 한 계산을 시뮬레이션 하려고 한다고 가정해 보겠습니다. (E1 및 E2 셀에 이러한 값을 입력 하 고 각 셀의 이름을 의미시그마로 지정할 수 있습니다.) Formula = RAND () 를 C4에서 C5로 복사 하면 C403에서 400의 다른 난수 번호가 생성 됩니다. B4에서 a 5까지 복사: B403 수식 NORMINV (C4, mean, sigma) 은 평균 4만 및 표준 편차가 1만 인 일반 임의 변수를 사용 하 여 400의 다른 평가판 값을 생성 합니다. F9 키를 눌러 난수를 다시 계산할 때 4만에 가까운 값과 1만에 대 한 표준 편차를 유지 합니다.

본질적으로 난수 x의 경우 수식 NORMINV (p, mu, 시그마) 에서는 평균 mu 와 표준 편차 시그마를 사용 하는 일반 임의 변수의 pth 백분위 수를 생성 합니다. 예를 들어 셀 C4의 난수 0.77 (그림 60-3 참조)는 평균이 4만이 고 표준 편차가 1만 인 일반 임의 변수의 77th 백분위 수를 각각 셀 B4에 생성 합니다.

이 섹션에서는 Monte Carlo 시뮬레이션이 의사 결정 도구에 사용 되는 방법을 알아봅니다. 발렌타인 데이의 날 카드에 대 한 수요는 다음과 같은 불연속 임의 변수로 제어 된다고 가정 합시다.

Demand

probability

10,000

0.10

20,000,000

0.35

4만

0.3

60,000

0.25

$4.00에 대해 판매 되는 인사말 카드와 각 카드를 생성 하는 가변 비용은 $1.50입니다. 남겨진 카드는 카드 당 $0.20의 비용으로 삭제 되어야 합니다. 인쇄 해야 하는 카드 수

기본적으로 각각의 가능 제품 수량 (1만, 2만, 4만 또는 6만)을 여러 번 시뮬레이트합니다 (예: 1000 반복). 그런 다음 1000 반복에서 최대 평균 이익을 산출 하는 주문 수량을 결정 합니다. 이 섹션에 대 한 데이터는 그림 60-4에서와 같이 발렌타인 파일에서 확인할 수 있습니다. B1: B11 셀의 범위 이름을 C1: C11 셀에 할당 합니다. 셀 범위 G3: H6에 이름 조회가지정 되어 있습니다. 판매 가격 및 비용 매개 변수는 C4: C6 셀에 입력 됩니다.

책 이미지

C1 셀에는 시험 생산 수량 (이 예에서는 4만)을 입력할 수 있습니다. 그런 다음 수식 = RAND ()를 사용 하 여 C2 셀에 임의의 숫자를 만듭니다. 앞에서 설명한 대로 C3 셀에 수식 VLOOKUP (rand, lookup, 2)을 사용 하 여 카드에 대 한 수요를 시뮬레이트합니다. VLOOKUP 수식에서 rand 는 rand 함수가 아니라 C3 셀에 할당 된 셀 이름입니다.

판매 되는 유닛 수는 생산 수량 및 수요에 더 적습니다. C8 셀에는 MIN (생성, 수요) * unit_price수식을 사용 하 여 수익을 계산 합니다. C9 셀에는 * unit_prod_cost 생성된 수식을 사용 하 여 총 생산 비용을 계산 합니다.

필요 이상으로 카드를 더 많이 생성 하는 경우에는 프로덕션에서 수요를 뺀 값 보다 왼쪽에 있는 유닛 수입니다. 그렇지 않으면 아무 단위도 남아 있지 않습니다. Unit_disp_cost * IF (생성>수요, 생성 – 수요, 0)공식을 사용 하 여 셀 C10에서 처리 비용을 계산 합니다. 마지막으로, C11 셀에서 수익을 total_var_cost 수익으로 계산 합니다 -total_disposing_cost.

각 제품 수량에 대해 여러 번 (예: 1000)을 누르고 각 수량에 대해 예상 수익을 집계 하는 효율적인 방법을 좋아합니다. 이러한 상황은 양방향 데이터 테이블을 가져오는 것입니다. (데이터 테이블에 대 한 자세한 내용은 15 장 "데이터 표로 구분 분석"을 참조 하세요.) 이 예제에 사용 된 데이터 테이블은 그림 60-5에 나와 있습니다.

책 이미지

셀 범위 A16: A1015에서 1 ~ 1000 (1000 평가판에 해당)을 입력 합니다. A16 셀에 1 을 입력 하 여 시작 하는 것이 이러한 값을 쉽게 만드는 방법 중 하나입니다. 셀을 선택한 다음, 탭의 편집 그룹에서 채우기를클릭 하 고 계열을 선택 하 여 계열 대화 상자를 표시 합니다. 그림 60-6에 나와 있는 계열 대화 상자에서 Step 값 1과 Stop 값 1000을 입력 합니다. 계열 영역에서 옵션을 선택한 다음 확인을 클릭 합니다. 1 ~ 1000의 숫자는 A16 셀부터 A 열에 입력 됩니다.

책 이미지

다음으로 B15: E15 셀에 가능 제품 수량 (1만, 2만, 4만, 6만)을 입력 합니다. 각 체험 번호 (1 ~ 1000)와 각 제품 수량의 이익을 계산 하려고 합니다. = C11를 입력 하 여 데이터 테이블 (A15)의 왼쪽 위 셀에 있는 수익에 대 한 수식 (C11 셀에서 계산 됨)을 참조 합니다.

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

이 방법이 작동 하는 이유를 이해 하려면 C16: C1015 셀 범위의 데이터 테이블에 있는 값을 고려 하세요. 이러한 각 셀에 대해 셀 C1의 2만 값이 사용 됩니다. C16에서 1의 열 입력 셀 값이 빈 셀에 배치 되 고 C2 셀의 임의 숫자가 다시 계산 됩니다. 그런 다음 C16 셀에 해당 수익이 기록 됩니다. 그러면 2의 열 셀 입력 값이 빈 셀에 배치 되 고 C2의 난수는 다시 계산 됩니다. 해당 수익이 셀 C17에 입력 됩니다.

B 13 셀에서 C13: E13 수식 평균 (B16: B1015)을 복사 하 여 각 제품 수량의 평균 시뮬레이트된 수익을 계산 합니다. B14 셀에서 C14: E14 수식 (B16: B1015)을 복사 하 여 각 주문 수량에 대해 시뮬레이트된 수익에 대 한 표준 편차를 계산 합니다. F9 키를 누를 때마다 1000 반복 요청이 각 주문 수량에 대해 시뮬레이션 됩니다. 4만 카드를 생성 하면 항상 가장 큰 예상 수익이 생성 됩니다. 따라서 4만 카드를 생성 하는 것이 적절 한 의사 결정을 하 게 됩니다.

의사 결정에 따른 위험의 영향     4만 카드 대신 2만를 생성 하는 경우 예상 되는 수익이 약 22%에 해당 하지만, 우리의 위험 (수익의 표준 편차를 기준으로 측정 됨)은 거의 73%를 모두 삭제 합니다. 따라서 위험에 대 한 메시지를 매우 많이 하는 경우 2만 카드를 생성 하는 것이 적절 한 결정을 내릴 수 있습니다. 1만 카드를 생성 하는 경우에는 항상 1만 카드를 생성 하는 것이 유일한 표준 편차를 leftovers 수 있습니다.

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

평균 이익을 위한 신뢰 구간     이 상황에 대 한 자연 스러운 질문은 사실 실제 수익 이익을 의미 하는 95%에 해당 하는 기간입니다. 이 간격을 평균 수익에 대 한 95 퍼센트 신뢰구간 이라고 합니다. 모든 시뮬레이션 출력의 평균에 대 한 95 백분율 신뢰 간격은 다음 수식으로 계산 됩니다.

책 이미지

J11 셀에서 4만 달력이 D13 – 1.96 * D14/SQRT (1000)공식을 사용 하 여 생성 될 때 평균 수익에서 95 백분율 신뢰 기간의 하한값을 계산 합니다. J12 셀에서 D13 + 1.96 * D14/SQRT (1000)공식을 사용 하 여 95 백분율 신뢰 기간의 상한 값을 계산 합니다. 이러한 계산은 그림 60-7에 나와 있습니다.

책 이미지

4만 달력이 주문 될 때의 평균 수익이 $56687와 $62589 사이에 있는 95%입니다.

  1. GMC 딜러는 2005 Envoys에 대 한 요구가 일반적으로 평균 200 및 표준 편차 30과 함께 배포 되는 것으로 간주 합니다. 엔보이의 수신 비용은 $25000 이며 $4만에 대 한 엔보이를 판매 합니다. Envoys 전체 가격으로 판매 되지 않은 모든 수의 절반은 $3만에 대해 판매할 수 있습니다. 주문 200, 220, 240, 260, 280 또는 300 Envoys를 고려 하 고 있습니다. 주문을 얼마나 많이 처리 해야 하나요?

  2. 작은 supermarket는 각 주가 주문 해야 하는 사용자 의 복사본 수를 결정 하려고 합니다. 이러한 사용자 에 대 한 요구는 다음과 같은 불연속 임의 변수로 제어 됩니다.

    Demand

    probability

    15

    0.10

    20

    0.20

    km

    0.30

    30

    0.25

    35

    0.15

  3. Supermarket는 각 사용자 의 사본에 대해 $1.00을 지불 하 고 $1.95에 판매 합니다. $0.50에 대해 판매한 각 복사본을 반환할 수 있습니다. 상점 주문에 대 한 사용자 복사본 수

추가 지원

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

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

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

이 정보가 유용한가요?

의견 주셔서 감사합니다!

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

×