Excel 가이드:데이터 기반 의사 결정을 위한 Monte Carlo 시뮬레이션 구축

몬테카를로 시뮬레이션은 불확실성을 모델링하고 재무 예측, 프로젝트 관리, 재고 분석과 같은 시나리오에서 위험의 영향을 평가하는 데 사용되는 통계 기법입니다. 수천 개의 시나리오 생성 Monte Carlo Simulation은 불확실성 하에서 데이터 기반 결정을 내리는 데 도움이 되는 변동성을 평가하는 방법을 제공합니다. 이번 글에서는 엑셀을 이용하여 몬테카를로 시뮬레이션을 구현해보겠습니다.

몬테카를로 시뮬레이션을 구현하기 위한 문제를 정의해 보겠습니다. 결과에 영향을 미치는 주요 측정항목을 기반으로 수익을 시뮬레이션할 소규모 전자상거래 데이터세트를 고려해 보세요.

1단계:Excel에서 데이터 설정

열에 주요 측정항목을 삽입하세요. 주요 측정항목은 다음과 같습니다.

• A열: 시뮬레이션 번호(1, 2, 3, …, 1000).
• B열: 판매 가격(단위당 가격을 시뮬레이션하려면 RAND 함수 사용).
• C열: 판매 단위(판매 단위 수 시뮬레이션)
• D열: 단위당 비용(임의의 비용).
• E열: 수익을 계산합니다(단위와 비용을 곱함).
• F열: 총 비용(고정 및 변동 비용 기준).
• E열 :이익 계산(수익과 총 비용 차감).

2단계:Excel 함수를 사용하여 무작위 입력 생성

각 주요 변수에 대해 난수를 생성하려면 Excel에서 RAND, RANDBETWEEN 및 NORM.INV 함수를 사용할 수 있습니다.

시뮬레이션 번호 

시리즈 옵션을 사용하여 숫자를 삽입할 수 있습니다. 처음에는 Simulation No. 열에 1을 입력합니다. 열을 드래그하면 시뮬레이션 번호가 자동으로 생성됩니다.

• A2 셀을 선택하고 1을 삽입하세요.

판매가:

• 평균이 $30인 정규 분포 사용 표준편차는 $4.5입니다. .
• B2 셀에 다음 수식을 삽입하세요.

수식:

=NORM.INV(RAND(), 30, 4.5)

이 공식은 표준 편차가 4.5인 평균(30) 주위에 정규 분포된 값을 생성합니다.

판매 수량:

• 무작위 포아송 값을 생성하기 위해 포아송 분포를 사용합니다.
• C2 셀에 다음 수식을 삽입하세요.

수식:

이 공식은 특정 제품의 판매 단위로 100에서 550 사이의 임의 값을 생성합니다.

단위당 비용:

• $10부터 균등 분포 생성 $18까지 .
• D2 셀을 선택하고 다음 수식을 삽입하세요.

수식:

수익:

• 수익을 계산하려면 판매 수량과 판매 가격을 곱하세요.
• E2 셀에 다음 수식을 삽입하세요.

수식:

총 비용:

고정비, 변동비, 생산비의 합으로 총비용을 계산하세요.

• F2 셀을 선택하고 다음 수식을 삽입하세요.

수식:

=($D2 * $C2) + 1000 + (E2 * 0.1)

이 공식은 고정 비용, 가변 비용 및 생산 비용을 결합하여 총 비용을 계산합니다.

• ($D2 * $C2): 변동비용입니다. 생산량(D2)과 단가(C2)를 기준으로 총비용을 계산하는 부분입니다.
• 1000: 생산 수준에 관계없이 일정한 금액의 고정 비용으로 추가됩니다.
• (E2 * 0.1): 수익에 10% 비율을 적용하여 제작 비용을 계산합니다.

이익:

• 수익을 계산하려면 다음 수식을 입력하세요.

수식:

이 공식은 총 비용에서 수익을 뺍니다.

출력:

변수의 모든 수식과 결과를 볼 수 있습니다. 나중에 모든 공식을 복사하여 시뮬레이션을 생성할 수 있습니다.

3단계:수식을 복사하여 모든 시뮬레이션 만들기

• B, C, D, E, F, 열의 수식을 아래로 드래그합니다. 및 G 각 시뮬레이션에 대해 무작위 데이터를 생성합니다.

• 모든 행은 하나의 시뮬레이션을 나타냅니다.

4단계:결과 분석

1000개의 시뮬레이션이 생성되면. 시뮬레이션 결과를 분석해 보겠습니다. 다음과 같은 통계적 척도를 사용하여 사업의 잠재 수익을 평가할 수 있습니다.

평균 이익 :

평균 이익을 계산하려면 다음 공식을 입력하세요.

수식:

평균 이익은 예상한 결과를 얻는 데 도움이 됩니다.

최소 이익:

최소 이익을 확인하려면 다음 공식을 입력하세요.

수식:

최소 이익은 최악의 시나리오를 보여줍니다.

최대 이익:

최대 이익을 확인하려면 다음 공식을 사용하세요.

수식:

최대 이익이 최선의 결과를 보여줄 것입니다.

손실 확률 :

이익이 마이너스인 시뮬레이션의 비율을 계산합니다.

수식:

=COUNTIF(G2:G1001,"<0")/COUNTA(G2:G1001)

이는 귀하가 직면할 수 있는 손실 규모를 파악하는 데 도움이 될 것입니다.

이익에 대한 95% 신뢰 구간(CI) :

95% CI의 하한과 상한을 계산합니다.

하한값:

=PERCENTILE(G2:G1001, 0.025)

이 공식은 2.5번째 백분위수를 계산합니다. 이는 수익 데이터의 2.5%가 이 값 아래에 있음을 의미합니다.

상한값:

=PERCENTILE(G2:G1001, 0.975)

이 공식은 97.5번째 백분위수를 계산합니다. 이는 수익 데이터의 97.5%가 이 값 아래에 있음을 의미합니다.

출력:

이익 히스토그램 만들기 :

• 이익 항목을 선택하세요.
• 삽입으로 이동 탭>> 차트에서>> 히스토그램을 선택하세요. .

이익에 대한 히스토그램 열이 표시됩니다.

이익 분배는 이익이 어떻게 분산되는지를 보여줍니다.

데이터 테이블 도구를 사용한 몬테카를로 시뮬레이션

데이터 테이블 도구를 사용하여 Monte Carlo 시뮬레이션을 구현할 수 있습니다. 시리즈 옵션을 사용하여 시뮬레이션 번호를 삽입할 수 있습니다.

• 열 이름으로 데이터 테이블을 만듭니다.
• 시뮬레이션 번호 열의 A2 셀에 1을 입력하세요.
• 홈으로 이동 탭>> 채우기에서>> 시리즈 선택 .

• 시리즈 대화 상자;
  • 시리즈: 열
  • 유형:  선형
  • 단계 값:  1
  • 중지 값:  1000
  • 확인을 클릭하세요. .

• A2:G1001 셀에서 전체 표를 선택합니다.
• 데이터로 이동 탭>> 가상 분석에서>> 데이터 테이블을 선택합니다. .

• 데이터 표 대화 상자;
  • 행 입력 셀: 공백으로 유지하세요.
  • 열 입력 셀: A2 셀을 선택하세요.
  • 확인을 클릭하세요. .

데이터 테이블은 1000개의 시뮬레이션 값을 생성합니다.

팁 및 모범 사례

• 변수 제한에 대한 셀 참조를 사용하면 쉽게 업데이트할 수 있습니다.
• 계산 모드 설정 수동으로 . 그렇지 않으면 Enter를 누를 때마다 데이터가 업데이트됩니다. . 셀을 계산하려면 지금 계산(F9)을 사용하세요. 옵션입니다.
• 더 정확한 예측을 위해 최소 1,000~10,000번의 시뮬레이션을 실행하세요.
• 수만 개의 시뮬레이션을 실행해야 하는 경우 VBA가 이를 보다 효율적으로 처리할 수 있습니다.

결론

모든 단계를 수행하면 Excel에서 Monte Carlo 시뮬레이션을 구현할 수 있습니다. 이는 불확실성 하에서 가능한 결과를 예측하는 다양한 방법입니다. Excel의 RANDBETWEEN, RAND 및 NORM.INV 기능을 사용하면 강력한 모델을 구축하여 위험을 분석하고 의사 결정을 개선할 수 있습니다. 몬테카를로 시뮬레이션은 재무 위험 분석, 프로젝트 일정 최적화 등에 도움이 됩니다.