Excel에서 민감도 분석을 수행하는 방법: 단계별 가이드

Excel에서 민감도 분석을 수행하려면 모델의 각 입력값을 개별 셀에 입력하십시오. 그런 다음 해당 입력값을 변경하면서 결과가 어떻게 변하는지 기록합니다. 하나의 입력값에는 단일 변수 데이터 테이블을, 두 개의 입력값에는 이중 변수 데이터 테이블을 사용하고, 전체 케이스 분석에는 시나리오 관리자를 사용하십시오. 마지막으로 영향도에 따라 입력값의 순위를 매기는 토네이도 차트로 마무리합니다.

이 가이드는 민감도 분석의 정의, 모델 설정 방법 및 각 Excel 도구의 사용법을 설명합니다. 실제 수식이 포함된 작업 예시, AI를 활용한 더 빠른 방법, 그리고 피해야 할 실수들을 포함하고 있습니다.

민감도 분석이란 무엇인가

민감도 분석은 하나의 가정이 바뀔 때 결과가 얼마나 변하는지를 보여줍니다. "가격이 10% 하락하면 이익은 어떻게 되는가?"와 같은 질문에 답을 줍니다.

대부분의 비즈니스 모델은 추측에 기반합니다. 판매량, 가격, 비용 및 성장률은 모두 추정치입니다. 민감도 분석은 이러한 추측 중 어떤 것이 가장 중요한지 테스트합니다.

Excel은 이러한 도구들을 가상 분석(What-If Analysis) 아래에 그룹화합니다. Microsoft는 이를 수식의 결과가 어떻게 변하는지 확인하기 위해 "셀의 값을 변경하는 프로세스"라고 정의합니다.

Microsoft는 세 가지 기본 도구인 "시나리오, 목표값 찾기, 데이터 테이블"을 나열합니다. 각 도구는 서로 다른 역할을 수행합니다.

• 데이터 테이블은 하나 또는 두 개의 입력값의 여러 수치에 따른 하나의 결과를 보여줍니다.
• 시나리오 관리자는 최악의 경우와 최선의 경우와 같은 전체 입력값 세트를 저장합니다.
• 목표값 찾기는 역방향으로 작동합니다. 목표 결과에 도달하기 위해 필요한 입력값을 찾습니다.

민감도 분석에는 보통 처음 두 가지 도구를 사용합니다. 목표값 찾기는 손익분기점 가격과 같은 관련 질문을 해결하는 데 도움이 됩니다.

시작 전 모델 설정하기

Excel 도구는 모델이 깔끔하게 구축된 경우에만 작동합니다. 먼저 다음 사항들을 확인하십시오.

모든 입력값을 개별 셀에 넣으십시오. 수량, 가격, 비용 및 비율은 각각 별도의 셀이 필요합니다. 수식 안에 숫자를 직접 입력하지 마십시오.

결과를 입력값과 연결하십시오. 이익과 같은 결과 셀은 반드시 입력 셀을 참조하는 수식이어야 합니다. 그렇지 않으면 Excel이 새 값을 대입해도 아무것도 변하지 않습니다.

입력값을 모아 두십시오. 시트 상단의 한 블록에 그룹화하십시오. 각 항목에 레이블을 붙이고, 입력 셀에 색을 칠해 눈에 띄게 만드십시오.

테스트할 결과를 하나 선택하십시오. 민감도 분석은 이익, 순현재가치 또는 연말 현금 흐름과 같은 하나의 출력값을 추적할 때 읽기 더 쉽습니다.

기준 케이스(Base case)를 기록하십시오. 시작 값을 적어 두십시오. 분석의 모든 결과는 이 기준과 비교됩니다.

다음은 따라 해 볼 수 있는 간단한 이익 모델입니다. 가상의 자전거 매장인 Northgate Cycles의 예시입니다.

Cell	Label	Value
B1	판매 수량	2,400
B2	단가	$850
B3	단위당 변동비	$510
B4	고정비	$520,000
B5	이익	=B1*(B2-B3)-B4

이 기준 값에서 이익은 $296,000입니다.

데이터 테이블로 단일 변수 민감도 분석을 수행하는 방법

단일 변수 데이터 테이블은 하나의 입력값에 대한 범위를 테스트합니다. Microsoft는 이것이 "하나 이상의 수식에서 하나의 변수에 대한 서로 다른 값이 결과에 어떤 영향을 미치는지"를 보여준다고 설명합니다.

예시 모델에서 가격을 테스트하는 방법은 다음과 같습니다.

1. D2에서 D6 셀에 테스트할 가격인 $800, $825, $850, $875, $900를 입력합니다.
2. 첫 번째 가격의 한 행 위, 한 열 오른쪽인 E1 셀에 =B5를 입력합니다.
3. D1:E6 범위를 선택합니다.
4. 데이터 탭에서 가상 분석 > 데이터 테이블을 선택합니다.
5. 열 입력 셀에 가격 셀인 B2를 입력합니다. 행 입력 셀은 비워 둡니다.
6. 확인을 클릭합니다.

Excel이 E2:E6 셀을 각 가격에 따른 이익으로 채웁니다. 수식의 위치가 중요합니다. Microsoft는 수식을 "값 열의 한 행 위, 한 셀 오른쪽에" 입력하라고 안내합니다.

Northgate Cycles의 결과는 다음과 같습니다.

가격	이익
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

가격이 $25 변할 때마다 이익이 $60,000씩 움직입니다. 이것이 가격에 대한 이익의 민감도입니다.

이중 변수 민감도 분석을 수행하는 방법

이중 변수 데이터 테이블은 두 개의 입력값을 동시에 테스트합니다. Microsoft는 이것이 "하나의 수식에서 두 변수의 서로 다른 값이 해당 수식의 결과를 어떻게 바꾸는지"를 보여준다고 설명합니다.

가격과 수량을 함께 테스트하는 방법은 다음과 같습니다.

1. D10과 같은 빈 셀에 =B5를 입력합니다.
2. 그 아래 D11에서 D13 셀에 판매 수량인 2,000, 2,400, 2,800을 입력합니다.
3. 오른쪽 E10에서 G10 셀에 가격인 $800, $850, $900를 입력합니다.
4. D10:G13 범위를 선택합니다.
5. 가상 분석 > 데이터 테이블을 선택합니다.
6. 행 입력 셀에 B2를 입력합니다. 열 입력 셀에 B1을 입력합니다.
7. 확인을 클릭합니다.

결과는 다음과 같은 이익 그리드로 나타납니다.

수량 \ 가격	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

이 그리드는 테스트된 모든 조합에서 매장이 수익을 유지함을 보여줍니다. 가장 취약한 지점인 $800에 2,000개 판매 시에도 여전히 $60,000의 이익을 냅니다.

한 가지 엄격한 제한이 있습니다. Microsoft는 "데이터 테이블은 두 개 이상의 변수를 수용할 수 없다"고 명시합니다. 더 많은 입력값이 필요한 경우 시나리오 관리자를 사용하십시오.

또한 데이터 테이블은 시트가 재계산될 때마다 다시 계산됩니다. 대규모 통합 문서에서는 이로 인해 Excel 속도가 느려질 수 있습니다. Microsoft는 F9를 누를 때만 데이터 테이블이 재계산되도록 계산 설정을 변경할 것을 권장합니다.

시나리오 관리자로 전체 케이스를 비교하는 방법

시나리오 관리자는 전체 입력값 세트를 저장하고 이를 교체하여 보여줍니다. Microsoft는 시나리오를 "Excel이 저장하고 워크시트에서 자동으로 대체할 수 있는 값의 집합"으로 정의합니다.

세 가지 케이스를 설정하는 방법은 다음과 같습니다.

1. 데이터 탭에서 가상 분석 > 시나리오 관리자를 선택합니다.
2. 추가를 클릭하고 시나리오 이름을 기준(Base)으로 지정한 뒤, 변경 셀로 B1:B4를 입력합니다. 현재 값을 유지합니다.
3. 최악의 경우(Worst case) 추가: 수량 2,000개, 가격 $825, 변동비 $530, 고정비 $540,000.
4. 최선의 경우(Best case) 추가: 수량 2,800개, 가격 $875, 변동비 $495, 고정비 $510,000.
5. 시나리오를 선택하고 표시를 클릭하여 해당 값을 로드합니다.
6. 요약을 클릭하고 결과 셀로 B5를 선택하여 보고서를 생성합니다.

Northgate Cycles의 경우, 요약 보고서에 세 가지 이익이 표시됩니다. 최악의 경우는 $50,000, 기준 케이스는 $296,000, 최선의 경우는 $554,000의 이익을 냅니다.

Microsoft는 몇 가지 제한 사항을 언급합니다. 각 시나리오는 최대 32개의 변경 값을 가질 수 있습니다. 요약 보고서는 자동이 아닙니다. "시나리오 보고서는 자동으로 재계산되지 않습니다." 시나리오를 변경했다면 새 요약 보고서를 만드십시오.

목표값 찾기로 손익분기점을 찾는 방법

민감도 분석은 결과가 얼마나 변하는지를 보여줍니다. 목표값 찾기는 그 반대의 질문에 답합니다. 즉, 목표 결과를 얻기 위해 필요한 입력값은 무엇인가?

Microsoft는 목표값 찾기가 "결과를 가져와서 해당 결과를 생성하는 가능한 입력값을 결정한다"고 설명합니다. 예시로는 대출 상환에 필요한 이자율을 찾는 경우가 있습니다.

Northgate Cycles의 손익분기점 가격을 찾는 방법은 다음과 같습니다.

1. 데이터 탭에서 가상 분석 > 목표값 찾기를 선택합니다.
2. 수식 셀에 이익 셀인 B5를 입력합니다.
3. 찾는 값에 0을 입력합니다.
4. 값을 바꿀 셀에 가격 셀인 B2를 입력합니다.
5. 확인을 클릭합니다.

Excel은 이익이 0이 될 때까지 가격을 변경합니다. 2,400개 판매와 기준 비용 하에서 손익분기점 가격은 약 $726.67입니다. 이는 현재 가격인 $850보다 약 15% 낮은 수준입니다.

목표값 찾기에는 한 가지 제한이 있습니다. Microsoft는 이것이 "단 하나의 변수 입력값으로만 작동한다"고 말합니다. 두 개 이상의 입력값이 필요한 경우 Microsoft는 Solver 추가 기능을 권장합니다.

토네이도 차트 작성 방법

토네이도 차트는 입력값이 결과를 얼마나 변화시키는지에 따라 순위를 매깁니다. 이는 민감도 분석을 제시하는 가장 명확한 방법입니다.

작성 방법은 다음과 같습니다.

1. 각 입력값을 10% 낮게, 10% 높게 설정하는 등 테스트 범위를 정합니다.
2. 한 번에 하나의 입력값만 변경하고 낮음 및 높음 결과를 기록합니다. 다른 입력값은 기준 상태를 유지합니다.
3. 결과를 입력값당 한 행씩 테이블에 정리합니다.
4. 범위가 가장 넓은 입력값이 맨 위에 오도록 행을 정렬합니다.
5. 낮음 및 높음 결과에 대한 막대형 차트를 삽입합니다.

Northgate Cycles의 경우, 각 입력값을 10%씩 가감했을 때의 결과입니다.

입력값	−10% 시 이익	+10% 시 이익	범위
가격	$92,000	$500,000	$408,000
변동비	$418,400	$173,600	$244,800
판매 수량	$214,400	$377,600	$163,200
고정비	$348,000	$244,000	$104,000

가격이 압도적으로 가장 중요합니다. 가격이 10% 하락하면 이익은 $296,000에서 $92,000로 급감합니다. 고정비가 가장 덜 중요합니다. 이는 소유주가 어떤 가정을 확인하는 데 시간을 써야 할지 알려줍니다.

Excel의 막대형 차트는 막대를 왼쪽에서 오른쪽으로 그립니다. 토네이도 모양을 만들려면 원시 값 대신 기준 이익으로부터의 각 입력값 변화량을 플롯하십시오. 그러면 낮은 케이스 막대는 왼쪽을, 높은 케이스 막대는 오른쪽을 가리키게 됩니다.

AI로 민감도 분석을 수행하는 방법

대규모 모델을 위해 데이터 테이블, 시나리오 및 토네이도 차트를 만드는 것은 시간이 걸립니다. AI 도구는 통합 문서에서 동일한 테스트를 실행할 수 있습니다.

다음은 Powerdrill Bloom을 사용한 세 단계 방법입니다.

1단계: 모델 업로드

모델이 포함된 스프레드시트로 시작하십시오. AI Sensitivity Analysis Generator는 "DCF, 비즈니스 케이스, 운영 계획 또는 모든 스프레드시트 모델"을 업로드하도록 요청합니다.

입력값과 결과 셀에 레이블이 명확하게 지정되어 있는지 확인하십시오. 도구는 어떤 숫자가 가정치인지 알아야 합니다.

2단계: 자연어로 민감도 뷰 선택

원하는 테스트를 설명하십시오. 예: "각 입력값을 10%씩 가감했을 때의 이익에 대한 토네이도 차트를 보여줘. 가격과 판매 수량에 대한 이중 변수 테이블도 추가해줘."

도구 페이지에는 "토네이도, 이중 변수, 몬테카를로 또는 손익분기점"의 네 가지 뷰가 나열되어 있습니다. 토네이도 출력은 "결과를 가장 많이 변화시키는 입력값의 순위를 매기는" 차트로 설명됩니다. 또한 "중요한 두 가지 가정에 대한 민감도 테이블"도 제공합니다.

3단계: 결과 확인 및 분석 내보내기

공유하기 전에 한두 개의 결과를 직접 만든 데이터 테이블과 비교해 보십시오. 기준 케이스는 모델과 정확히 일치해야 합니다.

그런 다음 내보내기를 수행합니다. 도구 페이지에는 "Excel 통합 문서, 브랜드 PDF 또는 캔버스 링크"가 나열되어 있습니다. 프레젠테이션용으로 Excel to PPT 도구를 사용하면 동일한 파일을 슬라이드로 변환할 수 있습니다. AI Financial Modeling 페이지에서는 모델 구축 자체에 대해 다룹니다.

결과를 읽고 제시하는 방법

민감도 분석은 사람들이 그에 따라 행동할 때만 유용합니다. 다음 습관들이 도움이 됩니다.

순위부터 보여주십시오. 토네이도 차트로 시작하십시오. 어떤 가정이 결과를 좌우하는지 한눈에 보여줍니다.

범위를 의사결정으로 전환하십시오. "가격이 10% 하락하면 이익이 $92,000로 떨어진다"는 사실입니다. "임대 계약을 하기 전에 가격을 테스트해야 한다"는 결정입니다.

손익분기점을 보여주십시오. 결과가 0이 되는 값과 분석을 함께 제시하십시오. Northgate Cycles의 경우, 기준 가격과 비용 하에서 손익분기점은 약 1,530개입니다. Excel에서의 손익분기점 분석 가이드에서 해당 계산법을 다룹니다.

현실적인 범위를 사용하십시오. 10% 변화는 안정적인 입력값에 적합합니다. 신제품의 판매량은 훨씬 더 넓은 범위가 필요할 수 있습니다.

무엇이 고정되었는지 명시하십시오. 한 번에 하나씩 수행하는 테스트는 다른 입력값을 기준 상태로 유지합니다. 슬라이드나 각주에 이를 명시하십시오. 그러면 독자들은 10% 가격 변화가 동일한 수량, 비용 및 고정 지출을 가정한다는 것을 알게 됩니다. 이 한 줄이 방법론에 대한 대부분의 후속 질문을 방지합니다.

결과가 나온 후 다시 확인하십시오. 실제 수치가 나오면 이를 설정했던 범위와 비교해 보십시오. 예산 대비 실적 보고서는 자연스러운 후속 작업입니다.

흔히 발생하는 민감도 분석 실수

이러한 실수들은 흔하지만 피하기 쉽습니다.

수식에 숫자를 직접 입력(Hard-coding). 수식에 B2 대신 850이 들어 있으면 데이터 테이블이 이를 변경할 수 없습니다. 모든 입력값을 셀에 유지하십시오.

너무 많은 입력값을 동시에 테스트. 데이터 테이블은 한두 개를 처리합니다. 그 이상은 시나리오 관리자나 토네이도 차트를 사용하십시오.

모든 입력값에 동일한 범위 사용. 가격과 수량이 동일한 불확실성을 갖는 경우는 드뭅니다. 실제로 알고 있는 바를 반영하는 범위를 선택하십시오.

입력값 간의 연결 무시. 가격이 낮아지면 수량이 늘어날 수 있습니다. 한 번에 하나씩 테스트하는 방식은 이를 놓치므로, 두 가지를 모두 움직이는 시나리오를 추가하십시오.

오래된 시나리오 요약. 시나리오 보고서는 자동으로 업데이트되지 않습니다. 변경 사항이 있으면 다시 생성하십시오.

기준 케이스 망각. 모든 테이블은 기준 결과를 보여주어야 독자들이 각 변화의 규모를 파악할 수 있습니다.

다음 모델을 스트레스 테스트할 준비가 되었다면, 계획에 사용하는 통합 문서로 Powerdrill Bloom을 사용해 보십시오.

자주 묻는 질문

Excel에서 민감도 분석이란 무엇입니까?

입력값이 변할 때 결과가 어떻게 변하는지 테스트하는 방법입니다. Excel에서는 가상 분석 도구, 주로 데이터 테이블과 시나리오 관리자를 사용하여 새로운 입력값을 대입하고 결과를 기록합니다.

Excel에서 단일 변수 민감도 분석은 어떻게 합니까?

입력값들을 한 열에 나열합니다. 결과를 참조하는 수식을 한 행 위, 한 열 오른쪽에 둡니다. 범위를 선택하고 가상 분석 > 데이터 테이블을 선택한 뒤, 입력 셀을 열 입력 셀로 입력합니다.

데이터 테이블과 시나리오 관리자의 차이점은 무엇입니까?

데이터 테이블은 여러 수치에 대해 하나 또는 두 개의 변수를 테스트하고 모든 결과를 하나의 그리드에 보여줍니다. 시나리오 관리자는 각각 최대 32개의 변경 값을 포함하는 전체 입력값 세트를 저장하고 한 번에 하나씩 교체하여 보여줍니다.

Excel에서 토네이도 차트는 어떻게 만듭니까?

각 입력값을 한 번에 하나씩 동일한 양만큼 가감하고 결과를 기록합니다. 범위 크기에 따라 입력값을 정렬한 다음, 기준 결과로부터의 각 입력값 변화를 가로 막대로 차트화합니다.

AI가 민감도 분석을 할 수 있습니까?

네. AI 도구는 스프레드시트 모델을 읽고 토네이도 및 이중 변수 테스트를 실행하며 결과를 통합 문서나 보고서로 내보낼 수 있습니다. 여전히 기준 케이스를 확인하고 현실적인 범위를 선택해야 합니다.

출처: Microsoft Support, 가상 분석 소개 · Microsoft Support, 데이터 테이블을 사용하여 여러 결과 계산 · Microsoft Support, 시나리오를 사용하여 다양한 값 집합 간 전환.