Excel 민감도 분석 실용 가이드: 수동에서 지능적 진화까지

스프레드시트에서 모델을 설계할 때, 하나의 변수 값을 변경하면 최종 결과에 어떤 영향을 미칠지 궁금해하는 경우가 많습니다. 이 과정은 Excel에서 민감도 분석을 실행하여 수행할 수 있습니다.

재무 모델링이나 의사 결정 과정에서 일하는 데이터 전문가라면 민감도 분석을 수행하는 방법을 아는 것이 중요합니다.

이 글에서는 민감도 분석에 대해 자세히 알아보겠습니다. 이 유용한 "가정 분석(what-if)" 기법의 기본 사항, Excel에서 기본 및 고급 민감도 분석을 수행하고 결과를 해석하는 방법, 그리고 마지막으로 더 강력한 민감도 분석을 가능하게 하는 AI와 머신러닝의 역할과 함께 모범 사례 및 일반적인 함정에 대해 논의하겠습니다.

Excel에서 민감도 분석이란 무엇인가요?

민감도 분석에 대한 가장 일반적인 접근 방식은 다른 모든 요소를 고정한 상태에서 하나의 변수 변화가 최종 결과에 어떤 영향을 미치는지 조사하는 것입니다. 이 과정은 고려된 모든 변수에 대해 반복됩니다. 이는 모델에서 가장 영향력 있는 동인을 식별하여 기업이 성과에 영향을 미치는 핵심 요소에 집중할 수 있도록 돕습니다.

민감도 분석은 어떤 요소가 성과에 가장 큰 영향을 미치는지 식별함으로써 기업이 중요한 위험과 기회에 우선순위를 두도록 돕습니다. 그 특성상 민감도 분석은 금융, 비즈니스 및 프로젝트 관리에서 일반적입니다. 예를 들어, 시간이 지남에 따라 이자율이 어떻게 변할지 계산하거나 다양한 성장 추세를 이해하는 데 사용됩니다.

더욱이, 민감도 분석은 일반적으로 변수 조합을 동시에 분석하는 시나리오 분석과 함께 사용됩니다. 두 가정 분석 모두 종속 변수가 특정 입력에 어떻게 반응할지 연구합니다. 그러나 시나리오 분석은 특정 "시나리오"를 위한 것이며, 민감도 분석은 입력과 값의 범위를 제공하기 때문에 더 개방적입니다.

민감도 분석의 목표는 종속 변수가 입력 값(독립 변수라고 함)의 범위에 어떻게 반응하는지 이해하는 것입니다.

이 기법을 의사 결정 과정에 통합함으로써 조직은 전략을 사전에 조정하고 불확실성에 대한 회복력을 강화할 수 있습니다.

종종 민감도 분석은 조건부 서식이 적용된 민감도 분석 테이블로 제시되며, 가장 높은 값부터 가장 낮은 값까지 강조 표시됩니다.

다음은 공급과 수요에 대한 가격 민감도의 예입니다. 공급과 수요는 축에 있는 독립 변수이고, 가격은 종속 변수로 테이블 내 값을 차지합니다.

다음 섹션에서는 유사한 Excel 민감도 분석 테이블을 사용하겠습니다.

Excel에서 민감도 분석 시작하기

과정의 각 단계를 따라 Excel에서 민감도 분석을 수행하는 방법을 살펴보겠습니다.

Excel의 기존 도구 및 기능

Excel은 가정 분석을 포함한 수학적 모델을 생성하는 편리한 도구입니다. 가정 분석은 본질적으로 민감도 분석과 같은 질문을 탐색하기 위한 수식을 만드는 것입니다.

대부분의 최신 Excel 버전에는 데이터 작업을 위한 다양한 도구와 가능성을 제공하는 데이터 탭이 있습니다. 여기에는 리본의 가정 분석 버튼이 포함됩니다. 여기에는 세 가지 도구가 있습니다. 데이터 테이블 버튼은 특히 민감도 분석 테이블을 생성하도록 설계되었습니다.

시나리오 분석을 수행하려면 시나리오 관리자 버튼을 클릭하기만 하면 됩니다.

개발자 탭에서 활성화할 수 있는 추가 기능인 솔버도 사용하겠습니다.

솔버는 워크시트의 다른 수식 셀 값에 대한 제약 조건 또는 제한 하에서 하나의 셀(목표 셀이라고 함)에 있는 수식의 최적(최대 또는 최소) 값을 찾기 위해 가정 분석에 사용됩니다.

솔버를 활성화하기 전에 기본적으로 표시되지 않는 개발자 탭에 액세스해야 합니다. 이 탭을 표시하려면:

• 파일 > 옵션으로 이동합니다.
• 왼쪽 패널에서 리본 사용자 지정을 선택합니다.
• 오른쪽 열의 "기본 탭"에서 개발자 확인란을 선택합니다.

단일 변수 민감도 분석 수행하기

이제 도구가 준비되었으므로 Excel에서 간단한 단일 변수 민감도 분석을 생성하는 것부터 시작해 보겠습니다.

기존 방법: 모델 설정하기

Excel에서 민감도 분석을 수행하는 첫 번째 단계는 모델이 기반을 둔 입력과 출력을 식별하는 것입니다.

이 튜토리얼에서는 판매된 기타 수, 기타당 가격 또는 생산 비용과 같은 일부 입력 변수 값을 변경할 경우 기타 판매 회사의 순이익이 어떻게 변할지 연구하기 위해 민감도 분석을 사용하겠습니다.

아래에서 입력 및 출력 변수를 포함하는 테이블을 볼 수 있습니다.

민감도 분석에 데이터 테이블을 사용할 때는 Excel이 모델 변수 간의 관계를 이해할 수 있도록 출력 셀을 입력 변수에 연결하는 것이 중요합니다. 이 과정은 수식을 통해 이루어집니다. 우리의 경우:

• 매출은 단가에 판매 단위 수를 곱하여 계산합니다(=B2*B4).
• 매출 원가는 단위 생산 비용에 판매 단위 수를 곱하여 계산합니다(=B3*B4).
• 이익은 매출에서 매출 원가를 빼서 계산합니다(=B7-B8).

기존 방법: 단방향 데이터 테이블 생성하기

생산 비용과 판매된 단위 수를 일정하게 유지하면서 단가를 인상하거나 인하하면 이익이 어떻게 변할지 궁금할 수 있습니다. 하나의 입력 변수 값을 변경할 때 하나의 출력 변수가 어떻게 변하는지 분석할 때는 소위 단방향 데이터 테이블이 필요합니다.

아래 GIF는 Excel에서 단방향 데이터 테이블을 생성하는 방법을 보여줍니다. 보시다시피, 테이블은 판매된 각 단위에 대한 이익을 자동으로 다시 계산합니다. 당연히 250단위를 판매할 때의 이익은 원래 매개변수(즉, €50,000)와 동일합니다.

더 스마트한 방법: Excelmatic을 이용한 단일 변수 분석

데이터 테이블 기능은 강력하지만 설정 과정은 정확한 셀 참조와 여러 단계가 필요합니다. 더 복잡하거나 동적인 모델의 경우 이는 지루하고 오류가 발생하기 쉬울 수 있습니다.

Excelmatic은 더 직관적인 대안을 제공합니다. Excel AI 에이전트로서 자연어를 사용하여 분석을 수행할 수 있게 해줍니다.

동일한 목표를 달성하려면 간단히:

1. 모델이 포함된 Excel 파일을 Excelmatic에 업로드합니다.
2. 간단한 언어로 요구 사항을 명시합니다: "단위 판매량(셀 B4)이 150에서 500까지 50씩 증가할 때 이익(셀 B9)이 어떻게 변하는지 보여주는 민감도 분석 테이블을 생성하세요."

Excelmatic은 모델 구조를 즉시 이해하고 테이블을 수동으로 설정하거나 셀을 연결하거나 메뉴를 탐색할 필요 없이 수동으로 생성된 단방향 데이터 테이블과 정확히 동일한 결과를 자동으로 생성합니다. 이 접근 방식의 장점은 속도와 단순성에 있으며, 도구를 조작하는 대신 결과 분석에 집중할 수 있게 해줍니다.

단위 판매량에 따라 다른 출력 변수가 어떻게 변하는지 검토하고 싶을 수도 있습니다. 기존 방법에서는 데이터 테이블 설정을 조정해야 합니다.

Excelmatic을 사용하면 요청을 약간 수정하기만 하면 됩니다: "단위 판매량(B4)이 150에서 500까지 변할 때 이익(B9)과 매출 원가(B8) 모두가 어떻게 변하는지 보여주세요." AI가 모든 세부 사항을 처리해 줍니다.

이중 변수 민감도 분석 수행하기

기존 방법: 양방향 데이터 테이블 생성하기

하나의 입력에 의해 하나의 출력이 어떻게 영향을 받는지 분석하는 대신, 두 입력 변수의 영향을 분석하기 위해 이중 변수 테이블을 생성할 수도 있습니다. 판매된 기타 수와 기타당 가격을 모두 수정하면 이익이 어떻게 변할지 알고 싶다고 가정해 보겠습니다.

먼저, 행이 단가 범위이고 열이 단위 판매량 범위인 2차원 테이블을 생성해야 합니다. 그런 다음 테이블의 왼쪽 상단 모서리에서 연구하려는 변수를 선택합니다. 마지막으로 입력 변수 테이블에서 독립 변수(즉, 단위 가격과 단위 판매량)에 대한 셀 참조를 선택합니다.

보시다시피, 테이블을 생성하면 왼쪽 상단 모서리에서 출력 변수를 다른 변수로 쉽게 변경할 수 있으며 Excel이 모든 값을 다시 계산합니다.

더 스마트한 방법: Excelmatic을 이용한 이중 변수 분석

양방향 데이터 테이블 설정은 단방향 테이블보다 더 복잡하며, 행, 열 및 모서리 셀에서 입력 및 출력의 적절한 참조가 필요합니다.

Excelmatic을 사용하면 이 과정도 단일 간단한 지시로 단순화됩니다. 파일을 업로드한 후 직접 요청할 수 있습니다:

"단위 판매량(150에서 500까지)과 단위당 가격(€150에서 €400까지)을 기준으로 이익이 어떻게 변하는지 보여주는 양방향 민감도 테이블을 생성하세요."

Excelmatic은 전체 2차원 테이블을 생성하여 모든 계산된 값을 채워줌으로써 수동 설정에서 발생할 수 있는 잠재적 참조 오류를 제거합니다. "매출"과 같은 다른 출력을 분석하려면 새로운 질문을 하면 됩니다: "이제 동일한 테이블을 매출에 대해 보여주세요." 이 대화형 워크플로우는 복잡한 분석을 쉽게 만듭니다.

Excel 민감도 분석 결과 해석하기

이제 데이터 테이블이 있으므로 마지막 단계는 입력 변경이 출력에 어떤 영향을 미치는지에 대한 통찰력을 얻기 위해 결과를 해석하는 것입니다.

데이터 테이블 출력 분석하기

이 민감도 분석을 기반으로 이익에 대해 정보에 입각한 결정을 내릴 수 있습니다. 다음은 몇 가지 통찰력입니다:

• 기타 가격을 €350으로 인상하면 현재 €50,000의 이익을 달성하기 위해 200개의 기타만 판매하면 됩니다.
• 기타 가격을 €200으로 인하하면 현재 이익에 도달하기 위해 500단위를 판매해야 합니다.
• 판매된 기타 수를 150개로 줄이면 기타 가격을 €400 이상으로 인상하지 않는 한 현재 이익을 달성할 수 없습니다.

분석 해석은 회사의 특정 요구 사항에 따라 달라집니다. 얼마나 많은 기타를 생산할 수 있는지, 건강한 회사를 유지하기 위해 필요한 최소 이익 수준은 무엇인지, 기타 생산 비용을 줄이기 위해 무엇을 할 수 있는지와 같은 질문을 하는 것은 민감도 분석에서 의미 있는 통찰력을 추출하는 데 필수적입니다.

기존 방법: 솔버를 이용한 최적화 및 민감도 분석

민감도 분석을 수행할 때 모델의 입력 변수 변경을 기반으로 잠재적 출력 범위를 반환합니다. 그러나 모델의 내부 작동 방식에 대해 더 알고 싶을 수 있습니다.

다행히도 Excel에는 이 작업을 도와줄 수 있는 강력한 도구인 솔버가 있습니다. 앞서 언급했듯이, 솔버는 워크시트의 다른 수식 셀 값에 대한 제약 조건 또는 제한 하에서 수식의 최적(최대 또는 최소) 값을 찾기 위해 가정 분석에 사용됩니다.

솔버는 목표 찾기의 업그레이드된 버전 이상입니다. 첫째, 솔버를 사용하면 여러 변수를 기반으로 최적 결과를 계산할 수 있으며 모델에 제약 조건을 포함할 수 있습니다. 더 중요한 것은 솔버에 모델의 계수를 변경할 때 최적 솔루션이 어떻게 변하는지 볼 수 있는 선택적 민감도 기능도 함께 제공된다는 점입니다.

솔버의 힘을 설명하기 위해 기타 회사로 돌아가 보겠습니다. 회사가 두 가지 기타 모델(모델 A와 모델 B)을 생산하고 있으며, 각 모델에는 일정량의 마호가니와 삼나무 목재가 필요하다고 상상해 보세요. 각 모델은 가격이 다릅니다.

회사는 현재 마호가니와 삼나무 목재 가용성을 고려하여 이익을 극대화하기 위해 각 모델을 몇 단위씩 생산해야 하는지 알고 싶어합니다.

아래 GIF에서는 목재 가용성 제약 조건을 사용하고 단위가 정수임을 보장하면서 수익을 극대화하기 위한 최적의 단위 값을 찾기 위해 솔버를 사용하는 방법을 보여줍니다.

모든 매개변수 값을 추가하면 솔버는 모델 A 48단위와 모델 B 9단위가 이익(€17,591)을 극대화하기 위한 최상의 조합이라고 추정합니다. 솔버 대화 상자를 닫기 전에 민감도를 클릭하면 Excel이 민감도 보고서가 포함된 새 시트를 생성합니다.

민감도 보고서는 입력 변수를 변경하면 이익이 어떻게 변할지 추정하는 데 유용한 통찰력을 제공합니다. 섀도우 가격은 제한된 자원을 한 단위 더 추가하면 이익이 얼마나 증가할지 나타냅니다. 이 예에서 마호가니 추가 제곱미터당 이익은 €98 증가하는 반면, 삼나무는 이익을 €103 증가시켜 삼나무가 더 큰 투자 수익률 잠재력을 가지고 있음을 강조합니다.

더 스마트한 방법: Excelmatic을 이용한 고급 최적화

솔버의 기능