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

• Who 몬테카를로 시뮬레이션을 사용하나요?

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

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

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

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

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

다음 5개 챕터에서는 몬테카를로 시뮬레이션을 수행하는 데 Excel 예제를 볼 수 있습니다.

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

• General Motors, Proctor 및 Gamble, Pfizer, Bristol-Myers Squibb 및 Eli Lilly는 시뮬레이션을 사용하여 새 제품의 평균 수익률과 위험 요소를 모두 예측합니다. GM에서 이 정보는 CEO가 시장에 출시할 제품을 결정하는 데 사용됩니다.

• GM은 기업의 순이익 예측, 구조적 및 구매 비용 예측, 다양한 종류의 위험에 대한 감수성 결정(예: 금리 변동 및 환율 변동)과 같은 활동에 시뮬레이션을 사용합니다.

• Lilly는 시뮬레이션을 사용하여 각 약물에 대한 최적의 식물 용량을 결정합니다.

• Proctor 및 Gamble는 시뮬레이션을 사용하여 외환 위험을 모델링하고 최적으로 에지합니다.

• Sears는 시뮬레이션을 사용하여 공급업체에서 주문해야 하는 각 제품 라인의 단위 수(예: 올해 주문해야 하는 Dockers 바지 쌍의 수)를 확인할 수 있습니다.

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

• 재무 계획자는 Monte Carlo 시뮬레이션을 사용하여 고객의 은퇴에 대한 최적의 투자 전략을 결정합니다.

셀에 =RAND()를 입력하면 0과 1 사이의 값을 가정할 수 있는 숫자가 됩니다. 따라서 시간의 약 25%는 0.25보다 작거나 같아야 합니다. 0.90 이상인 숫자를 얻을 수 있는 시간의 약 10%입니다. RAND 함수의 작동 방법을 설명하기 위해 그림 60-1에 Randdemo.xlsx 파일을 살펴 봐야 합니다.

먼저 셀 C3에서 C4:C402로 수식 =RAND() 를 복사합니다. 그런 다음 C3:C402 데이터 범위의 이름을 지정합니다. 그런 다음 F 열에서 400개 난수(셀 F2)의 평균을 추적하고 COUNTIF 함수를 사용하여 0과 0.25, 0.25 및 0.50 및 0.75 및 0.75 및 1 사이의 분수를 확인할 수 있습니다. 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의 수요가 시간의 10%를 발생하도록 합니다.

수요	임의 번호 할당
10,000	0.10 미만
20,000,000	0.10보다 크거나 같고 0.45 미만
40,000	0.45보다 크거나 같고 0.75 미만
60,000	0.75보다 크거나 같음

수요 시뮬레이션을 설명하기 위해 다음 Discretesim.xlsx 그림 60-2에 표시된 파일 파일을 살펴 봐야 합니다.

시뮬레이션의 핵심은 임의 번호를 사용하여 테이블 범위 F2:G5(룩업이라는 이름)에서 보기를 시작하는 것입니다. 0보다 크거나 같거나 0.10 미만의 난수는 10,000의 수요를 생성합니다. 난수는 0.10보다 크거나 같고 0.45 미만은 20,000의 수요를 생성합니다. 난수는 0.45보다 크거나 같고 0.75 미만은 40,000의 수요를 생성합니다. 0.75보다 크거나 같을 경우 60,000의 수요가 생성됩니다. C3에서 C4:C402 수식 RAND()로복사하여 400개 난수 생성 그런 다음 B3에서 B4:B402로 수식 VLOOKUP(C3,lookup,2)을복사하여 일정 수요의 400개 평가판 또는 이터링을 생성합니다. 이 수식은 0.10 미만의 임의의 숫자가 10,000의 수요를 생성하고 0.10과 0.45 사이의 임의의 숫자는 20,000의 수요를 생성하도록 합니다. 셀 범위 F8:F11에서 COUNTIF 함수를 사용하여 각 수요를 산출하는 400개 이터링의 분수의 비율을 결정합니다. F9를 눌러 임의의 숫자를 다시 계산하면 시뮬레이션된 확률은 가정된 수요 확률에 가깝습니다.

모든 셀에 NORMINV(rand(),mu,sigma)를입력하면 평균 mu 및 표준 편차 시그마를 갖는 일반 임의 변수의 시뮬레이션된 값을 생성합니다. 이 절차는 그림 60-3에 Normalsim.xlsx 파일에서 설명되어 있습니다.

평균 40,000개와 표준 편차가 10,000개인 일반 임의 변수에 대해 400개 평가판 또는 이터니에이터를 시뮬레이션할 수 있습니다. (이러한 값을 E1 및 E2 셀에 입력하고 이러한 셀의 이름을 각각 평균 및 시그마로입력할 수 있습니다.) 수식 =RAND()를 C4에서 C5:C403으로 복사하는 경우 400개 다른 난수가 생성됩니다. B4에서 B5:B403으로 복사하는 수식 NORMINV(C4,mean,sigma)는 평균 40,000개와 표준 편차가 10,000개인 일반 임의의 변수에서 400개 평가판 값을 생성합니다. F9 키를 눌러 난수 다시 계산하면 평균은 40,000에 가깝고 표준 편차는 10,000에 가깝습니다.

기본적으로 임의 숫자 x의경우 NORMINV(p,mu,sigma)는 평균 뮤 및 표준 편차 시그마를 사용하여 일반 임의 변수의 pth 백분위수 를 생성합니다. 예를 들어 셀 C4의 난수 0.77(그림 60-3 참조)은 평균 40,000과 표준 편차가 10,000인 일반 임의 변수의 약 77번째 백분위수 B4에서 생성됩니다.

이 섹션에서는 몬테카를로 시뮬레이션을 의사 결정 도구로 사용할 수 있는 방법을 설명합니다. 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장의 표준 편차가 있습니다.

평균 이익에 대한 신뢰 간격      이 상황에서 질문하는 자연스러운 질문은 어떤 간격으로 실제 평균 이익이 떨어질지 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%입니다.

1. GMC 딜러는 2005 Envoys에 대한 수요가 일반적으로 평균 200 및 표준 편차 30으로 분산될 것으로 믿고 있습니다. 특사 수령 비용은 25,000달러로, 특사는 $40,000에 판매합니다. 전체 가격으로 판매되지 않은 모든 Envoys의 절반은 $30,000에 판매될 수 있습니다. 200, 220, 240, 260, 280 또는 300 Envoys를 주문하는 것이 고려 중입니다. 얼마나 많은 주문을 해야 하나요?

2. 작은 슈퍼마켓은 매주 주문해야 하는 People 잡지의 복사본 수를 확인하려고 합니다. 이러한 요구는 다음과 같은 불연도 임의 변수에 의해 결정됩니다.

   수요	probability
   15	0.10
   20	0.20
   25	0.30
   30	0.25
   35	0.15

3. 슈퍼마켓은 각 사람 복사본에 대해 $1.00를 지불하고 $1.95에 판매합니다. 판매되지 않은 각 복사본은 $0.50에 대해 반환될 수 있습니다. 매장에서 주문해야 하는 사람 복사본은 몇 개인가요?