시뮬레이션은 불확실성 속에서 결과를 예측하고 데이터 기반 의사결정을 내리는 데 도움을 주는 강력한 분석 기법입니다. 엑셀은 무작위 사건을 시뮬레이션하기에 매우 적합한 도구로, 확률을 탐색하고 다양한 시나리오의 결과를 모델링할 수 있습니다.
이 튜토리얼에서는 엑셀을 활용해 현실 세계의 무작위 사건을 시뮬레이션하는 방법을 소개합니다. 동전 던지기, 주사위 굴리기, 날씨 예측은 물론 실무에 바로 적용할 수 있는 고객 도착 예측과 몬테카를로 매출 예측까지 단계별로 살펴보겠습니다.
1. 간단한 동전 던지기 시뮬레이션
동전을 100번 던지는 상황을 시뮬레이션해 보겠습니다.
기본 설정
- 새 엑셀 통합 문서를 엽니다.
- A1 셀에 시행 번호라고 입력합니다.
- B1 셀에 동전 결과라고 입력합니다.
시행 번호 채우기
- A2 셀에 1을 입력합니다.
- A3 셀에 2를 입력합니다.
- A2:A3 범위를 선택한 후 채우기 핸들을 A101까지 드래그하면 1~100번까지 자동으로 번호가 매겨집니다.
- 엑셀 2021 이상 버전을 사용한다면 SEQUENCE 함수를 활용할 수 있습니다.
- A2 셀을 선택하고 아래 수식을 입력하세요.
이 수식은 100개의 시행 번호를 자동으로 생성합니다.
동전 던지기 결과 시뮬레이션
- B2 셀에 다음 수식을 입력합니다.
=IF(RAND()<0.5, "Heads", "Tails")
이 수식은 0과 1 사이의 무작위 값이 0.5보다 작으면 앞면(Heads), 그렇지 않으면 뒷면(Tails)을 반환합니다.
- B2 셀의 채우기 핸들을 B101까지 드래그합니다.
결과 분석하기
시뮬레이션된 결과에서 앞면과 뒷면의 개수를 세어 보겠습니다.
앞면 개수 세기:
- E2 셀을 선택하고 아래 수식을 입력합니다.
=COUNTIF(B2:B101, "Heads")
뒷면 개수 세기:
- E3 셀을 선택하고 아래 수식을 입력합니다.
=COUNTIF(B2:B101, "Tails")
참고: 워크북이 재계산될 때마다 수식은 새로운 결과를 생성합니다.
2. 주사위 굴리기 시뮬레이션
6면체 주사위를 100번 굴리는 상황을 시뮬레이션해 보겠습니다.
기본 설정
- A1 셀에 굴림 번호, B1 셀에 주사위 결과를 입력합니다.
주사위 굴림 시뮬레이션
- B2 셀을 선택하고 아래 수식을 입력합니다.
이 수식은 1부터 6 사이의 무작위 정수를 생성합니다.
- B2 셀의 수식을 B101까지 드래그하여 복사합니다.
결과 분석하기
각 눈금(1~6)별 빈도를 계산합니다. COUNTIF 함수를 이용해 1부터 6까지 각 숫자가 나온 횟수를 구하면 됩니다.
- 숫자 1: =COUNTIF(B2:B101, 1)
- 숫자 2: =COUNTIF(B2:B101, 2)
- 숫자 3: =COUNTIF(B2:B101, 3)
- 숫자 4: =COUNTIF(B2:B101, 4)
- 숫자 5: =COUNTIF(B2:B101, 5)
- 숫자 6: =COUNTIF(B2:B101, 6)
참고: 워크북이 재계산될 때마다 수식은 새로운 결과를 생성합니다.
3. 고객 도착 시뮬레이션 (실무 비즈니스 시나리오)
시간당 평균 20명의 고객이 방문하는 매장을 가정하고, 시간대별 고객 도착 수를 시뮬레이션할 수 있습니다.
기본 설정
- A1 셀에 시간, B1 셀에 도착 고객 수를 입력합니다.
시간 입력하기
- A2 셀에 오전 9:00를 입력합니다.
- 채우기 핸들을 드래그해 오후 4시까지 한 시간 간격으로 채웁니다.
도착 고객 수 시뮬레이션
- B2 셀에 다음 수식을 입력합니다.
=ROUND(NORM.INV(RAND(), 20, 4), 0)
이 수식은 평균 20명, 표준편차 4를 중심으로 현실적인 무작위 값을 생성하고, 결과를 가장 가까운 정수로 반올림합니다.
- B2 셀의 수식을 B9까지 드래그합니다.
평균 고객 수 계산하기
- D2 셀을 선택하고 AVERAGE 함수로 시뮬레이션된 도착 고객 수의 평균을 계산합니다.
이렇게 하면 주어진 평균값을 기반으로 현실적인 고객 도착 패턴을 재현할 수 있습니다.
참고: 워크북이 재계산될 때마다 수식은 새로운 결과를 생성합니다.
4. 무작위 날씨 조건 시뮬레이션
과거 데이터에 기반한 확률을 활용하면 한 달간의 일일 날씨를 시뮬레이션할 수 있습니다.
확률 설정
- 맑음(Sunny): 60%
- 흐림(Cloudy): 25%
- 비(Rainy): 15%
기본 설정
- A1 셀에 날짜, B1 셀에 날씨 상태를 입력합니다.
날짜 채우기
- A2 셀을 선택하고 날짜 수식을 입력하거나, 채우기 핸들로 31일치 날짜를 채웁니다.
날씨 시뮬레이션
- B2 셀에 다음 수식을 입력합니다.
=IF(RAND()<=0.6,"Sunny",IF(RAND()<=0.25/(0.25+0.15),"Cloudy","Rainy"))
이 수식은 미리 정의된 확률에 따라 '맑음', '흐림', '비'를 무작위로 배정하지만, RAND()를 두 번 호출하기 때문에 의도한 확률 분포가 약간 왜곡될 수 있습니다. 더 정확한 방법은 RAND()를 한 번만 호출하고 누적 구간으로 나누는 것입니다.
- 수식을 B32까지 드래그합니다.
결과 분석하기
날씨별 발생 횟수를 COUNTIF 함수로 계산합니다.
- 맑음: =COUNTIF(B2:B32,"Sunny")
- 흐림:
=COUNTIF(B2:B32,"Cloudy")
- 비: =COUNTIF(B2:B32,"Rainy")
빈도 분석
각 횟수를 전체 시행 수(31)로 나누어 빈도를 계산합니다.
- 맑음: 맑음 개수 ÷ 31
- 흐림: 흐림 개수 ÷ 31
- 비: 비 개수 ÷ 31
참고: 워크북이 재계산될 때마다 수식은 새로운 결과를 생성합니다.
5. 심화 시나리오: 몬테카를로 시뮬레이션을 활용한 매출 예측
불확실성을 고려하여 월간 매출을 추정해 보겠습니다. 조건은 다음과 같습니다.
- 예상 매출: 2,000개
- 표준편차: 300개
기본 설정
- A1 셀에 시뮬레이션 번호, B1 셀에 시뮬레이션된 매출을 입력합니다.
시뮬레이션 번호 채우기
- A2 셀을 선택하고 SEQUENCE 함수 등으로 1~1000번의 시뮬레이션 번호를 생성합니다.
매출 시뮬레이션
- B2 셀에 다음 수식을 입력합니다.
=NORM.INV(RAND(), 2000, 300)
이 수식은 평균 2,000, 표준편차 300인 정규분포에서 무작위 값을 생성하며, 월간 매출 시뮬레이션에 유용하게 활용됩니다.
- 이 수식을 B1001까지 드래그하여 1,000번의 시나리오를 만듭니다.
결과 분석하기
- 시뮬레이션된 매출의 평균을 계산합니다.
- 매출 목표 초과 확률을 계산합니다. 예를 들어 2,200개 초과 확률은 다음과 같습니다.
=COUNTIF(B2:B1001,">2200")/1000
참고: 워크북이 재계산될 때마다 수식은 새로운 결과를 생성합니다.
추가 팁
- 결과 고정하기: 무작위 결과를 그대로 유지하고 싶다면 해당 범위를 선택한 후 복사하고, '값 붙여넣기'를 실행하세요.
- 히스토그램과 시각화: 엑셀 차트를 활용하면 시뮬레이션 결과를 쉽게 시각화할 수 있습니다.
- 데이터 분석 도구(Analysis ToolPak) 활용: 대용량 데이터셋과 세부적인 통계 분석에 유용합니다.
- 틀 고정: 대규모 무작위 데이터를 만들 때 상단 행을 고정하면 스크롤하면서 데이터를 쉽게 파악할 수 있습니다.
- F9 키: F9를 누르면 무작위 결과를 수동으로 새로고침할 수 있습니다.
마무리
실용적인 데이터셋과 함께 이 단계들을 따라 하면, 엑셀로 무작위 사건을 자신 있게 시뮬레이션하고 분석할 수 있습니다. RAND(), RANDBETWEEN() 같은 강력하면서도 접근성 좋은 난수 함수와 NORM.INV, POISSON.DIST 같은 통계 함수를 활용하면 현실 세계의 다양한 사건을 효과적으로 모델링할 수 있습니다. 교육 목적, 수요 예측, 리스크 관리 등 어떤 용도든 엑셀은 불확실성을 시각화하고 합리적인 의사결정을 내리는 데 필요한 실용적인 도구를 제공합니다.