Excel에서 민감도 분석을 하는 방법
Excel에서 단일 변수와 이중 변수 데이터 표를 만들고 어떤 가정이 재무 의사결정을 바꿀 수 있는지 확인하는 방법을 알아봅니다.
- 단일 변수 표로 판매량을, 이중 변수 표로 가격과 판매량의 조합을 시험합니다.
- 표 가운데 값과 모서리 한 곳의 값을 모델과 대조합니다.
- 예시에서 단가 100달러의 손익분기 판매량은 875개입니다.
예산상 영업이익이 5,000달러라면 가격을 얼마나 낮출 수 있을까요? 판매 목표에 미달한다면 어느 정도까지 감당할 수 있을까요? 민감도 분석은 가정을 바꿔 같은 모델을 다시 계산합니다. 이 가이드에서는 Excel로 단일 변수 표와 이중 변수 표를 만들고, 기준값과 결과를 대조해 손익분기 판매량을 찾습니다.
민감도 분석이란?
민감도 분석은 선택한 입력값을 변경했을 때 모델 결과가 얼마나 달라지는지 측정합니다. 기준 시나리오에서 시작해 시험할 입력만 바꾸고 다른 가정은 고정합니다. 결과의 차이가 해당 변화에 대한 민감도를 보여 줍니다.
예산의 판매 단가가 100달러이고 판매량이 1,000개라고 가정합니다. 가격과 판매량은 입력이고 영업이익은 결과입니다. 단일 변수 분석은 가격을 100달러로 고정하고 판매량의 변화만 시험합니다. 이중 변수 분석은 가격과 판매량의 조합별 결과를 보여 줍니다.
다른 재무 모델에도 같은 방법을 쓸 수 있습니다. 현금 예측에서는 대금 회수 지연이 월말 현금 잔액에, 가치 평가에서는 할인율 변화가 순현재가치에 미치는 영향을 확인할 수 있습니다. 입력 범위는 모델이 지원할 결정에 맞춥니다.
민감도 분석과 시나리오 분석의 차이
민감도 분석은 가격이나 판매량이 일정 범위에서 변할 때 결과의 변화를 파악합니다. 시나리오 분석은 함께 일어날 수 있는 가정을 묶어 평가합니다. 판촉이라면 할인, 판매량 증가 예상, 추가 마케팅 비용을 포함할 수 있습니다. 민감도 표로 개별 영향을 파악하고 시나리오로 계획 전체를 평가합니다.
분기가 끝났다면 차이 분석으로 실제 이익과 예산의 차이를 설명할 수 있습니다. 목표 결과를 알고 있다면 Excel의 목표값 찾기로 입력값 하나를 역산할 수 있습니다. 가격을 고정한 상태에서 영업이익이 0달러가 되는 판매량을 찾는 경우가 그 예입니다.
민감도 분석의 입력과 범위 선택 방법
결정할 사항과 결정을 바꿀 수 있는 지표부터 정합니다. 할인 승인이라면 영업이익과 손익분기 판매량을 봅니다. 유동성이 문제라면 최소 현금 잔액이 더 중요할 수 있습니다.
기준값, 시험 범위의 최솟값과 최댓값, 근거와 확인 담당자를 기록합니다. 가격은 최근 견적이나 승인된 할인 정책에서, 판매량은 비교 가능한 기간의 실적, 확보한 주문 또는 예측에서 범위를 정합니다. 기간과 단위를 맞추고 비율은 상대 변화인지 퍼센트포인트 변화인지 명시합니다. 10%에 2%포인트를 더하면 12%, 10%를 2% 높이면 10.2%입니다.
예시에서는 단가 95~105달러와 분기 판매량 800~1,200개를 시험합니다. 비용은 기준 가정으로 고정합니다. 실제 예산에서는 시험 범위가 생산 한도나 공급업체의 가격 구간을 넘어서 비용을 바꾸는지 확인합니다.
Excel에서 민감도 분석을 실행하는 방법
데스크톱 Excel의 워크시트 한 장에 다음 가정과 이익 수식을 입력해 기준 모델을 만듭니다.
| 셀 | 모델 항목 | 값 또는 수식 |
|---|---|---|
B2 | 판매 단가 | 100 |
B3 | 분기 판매량 | 1000 |
B4 | 단위당 변동비 | 60 |
B5 | 분기 고정비 | 35000 |
B6 | 분기 영업이익 | =(B2-B4)*B3-B5 |
B6의 기준 결과는 5,000달러입니다. 두 표 모두 이 셀을 결과로 참조합니다.
단일 변수 데이터 표 만들기
단가를 100달러로 유지하고 판매량부터 시험합니다.
=$B$6에E3을 입력합니다.800에1000,1200,D4:D6을 한 행에 하나씩 입력합니다.D3:E6을 선택하고 데이터 → 가상 분석 → 데이터 표를 엽니다.- 열 입력 셀을
$B$3으로 설정하고 행 입력 셀은 비운 뒤 확인을 선택합니다.
Excel은 E4:E6에 −3,000달러, 5,000달러, 13,000달러를 입력합니다. 1,000개 결과는 기준 이익 5,000달러와 같아야 합니다. 이 가격에서는 한 개를 더 판매할 때마다 고정비와 이익에 충당할 금액이 40달러 늘어납니다.
이중 변수 데이터 표 만들기
가격은 위쪽 행에, 판매량은 왼쪽 열에 둡니다.
=$B$6에H3을 입력합니다.95에100,105,I3:K3을,800에1000,1200,H4:H6을 입력합니다.H3:K6을 선택하고 데이터 → 가상 분석 → 데이터 표를 엽니다.- 위쪽 가격의 행 입력 셀에
$B$2을, 왼쪽 판매량의 열 입력 셀에$B$3을 지정하고 확인을 선택합니다.
가격과 판매량 조합별 분기 영업이익.
| 분기 판매량 | 개당 95달러 | 개당 100달러 | 개당 105달러 |
|---|---|---|---|
| 800 | −$7,000 | −$3,000 | $1,000 |
| 1,000 | $0 | $5,000 | $10,000 |
| 1,200 | $7,000 | $13,000 | $19,000 |
가운데 값을 B6과 대조한 뒤 모서리의 값 하나를 직접 계산합니다. 단가 95달러에 800개를 판매하면 개당 공헌이익은 35달러입니다. 고정비 차감 전에는 28,000달러, 차감 후에는 −7,000달러입니다. 100달러 열을 단일 변수 표와 비교합니다. 이런 검사는 잘못된 결과 참조, 뒤바뀐 입력 셀, 갱신되지 않은 결과를 확인하는 데 도움이 됩니다.
민감도 표를 해석하는 방법
결정과 가까운 값부터 봅니다. 1,000개 판매 시 단가를 100달러에서 95달러로 낮추면 기준 이익 5,000달러가 사라집니다. 단가를 100달러로 유지하면 875개에서 이익이 0입니다. 고정비 35,000달러를 개당 공헌이익 40달러로 나눈 결과입니다. 단가가 95달러면 개당 공헌이익은 35달러이고 손익분기 판매량은 1,000개입니다.
표는 정해진 조합별 결과를 보여 줍니다. 가격을 낮추면 수요가 늘어날 것으로 예상한다면 그 근거와 추가 수량을 감당할 수 있는지 확인합니다. 현재 비용 조건에서 단가 95달러로 기준 이익 5,000달러를 유지하려면 최소 1,143개를 판매해야 합니다. 판촉 시나리오에서 추가 비용과 과거 실적에 근거한 판매량 추정치를 함께 고려할 수 있습니다.
경영진 보고에는 다음과 같이 정리할 수 있습니다.
단가 95달러, 판매량 1,000개에서는 분기 이익이 5,000달러에서 0달러로 줄어듭니다. 현재 비용 조건에서 5,000달러를 유지하려면 최소 1,143개를 팔아야 합니다. 할인 승인 전에 지난 판촉 실적이 예상 판매량을 뒷받침하는지, 초기 주문이 예상대로 들어오는지 확인해야 합니다.
통합 문서 버전과 시험 범위의 출처를 보고서와 함께 보관합니다. 확인한 표와 가정 메모, 근거 파일을 바탕으로 Kuse에서 민감도 표를 의사결정 요약문으로 정리할 수 있습니다. 기준 결과, 판단이 바뀌는 지점, 필요한 추가 증거를 담습니다. 모델 담당자가 Excel과 숫자 및 문구를 대조한 뒤 다음 예측에 같은 형식을 활용합니다.
자주 묻는 질문
Excel 민감도 표의 모든 셀이 같은 값을 표시하는 이유는 무엇인가요?
모델의 입력 하나를 바꿔 결과 변화를 확인한 뒤 기준값을 복원합니다. 모델 결과가 달라져도 표가 그대로라면 결과 참조와 입력 셀 지정을 확인합니다. 자동 계산에서 데이터 표가 제외됐다면 표의 수식을 선택해 다시 계산합니다.
민감도 표의 기준 결과와 모델 값은 왜 다른가요?
표의 머리글이 기준값과 정확히 같은지 봅니다. 표시한 수치가 같아도 반올림 때문에 Excel에 전달되는 값이 다를 수 있습니다. 결과 참조를 확인하고 표를 다시 계산한 뒤 다른 가정이 같은지 확인합니다.
기존 데이터 표가 사용하는 입력 셀은 어떻게 확인하나요?
표의 계산 셀을 선택해 수식 입력줄을 봅니다. 데스크톱 Excel의 데이터 표 수식에는 행과 열의 입력 참조가 그 순서대로 표시될 수 있습니다. 참조를 따라 모델의 가정과 결과가 연결되는지 확인합니다.
이중 변수 데이터 표와 목표값 찾기는 언제 사용하나요?
가격과 판매량별 이익처럼 두 가정의 조합별 결과를 보려면 이중 변수 표를 사용합니다. 가격을 고정해 이익이 0이 되는 판매량처럼 하나의 목표에 맞는 입력값을 구할 때는 목표값 찾기를 사용합니다.
현실적인 민감도 분석 입력값은 어떻게 정하나요?
승인된 기준 또는 현재 기준에서 시작해 과거 범위, 계약 조건, 견적, 예측 가정으로 범위를 정합니다. 결정에 영향을 주는 기준점에 가까운 값을 포함하고 탐색용 수치는 표시합니다.
민감도 표에서 손익분기점을 어떻게 찾나요?
이익이 0인 셀이나 이익의 부호가 바뀌는 이웃 셀을 찾습니다. 손익분기점이 표의 값 사이에 있으면 모델 수식이나 목표값 찾기를 사용합니다. 판매량은 필요에 따라 정수로 올림한 뒤 모델에서 이익을 확인합니다.