엑셀에서 민감도/가상 분석 수행하기

이 튜토리얼에서는 엑셀에서 민감도 분석을 수행하는 방법을 설명하겠습니다.

민감도 분석은 다양한 재무 시나리오를 입력하고 그 중 어떤 시나리오가 가장 경제적인지 확인하는 작업입니다. 엑셀에서는 이러한 분석을 할 때 단일변수 또는 이중변수 데이터 표를 사용할 수 있습니다.

단일변수 데이터 표

단일변수 데이터 표의 경우 표에서 하나의 변수를 변경하여 결과를 확인하는 것으로, 가상 분석이라고 불립니다. 예를 들어 금액이 $2,000이고 이자율이 6%이며 12개월에 걸쳐 상환해야 하는 대출이 있다고 예로 들어 보겠습니다. 아래의 PMT 함수를 사용하여 C8셀에 월별 상환금을 계산합니다:

=PMT(C5/12, C6, -C4)

변수가 되는 기간별(6, 9, 18, 24개월 )로 특정 고정금리에 따라 가상의 월별 상환금을 계산하여 어떤 시나리오가 가장 적합한지 확인할 수 있습니다.

1. 가상 분석을 실행하려면 각각의 가상 기간(개월 수)에 대한 행이 포함된, 부분적으로 비어 있는 데이터 표 영역을 지정합니다.

2. 리본에서 데이터 > 가상 분석 > 데이터 표로 이동합니다

3. 원래의 기간을 “가상”의 기간으로 변경하고 이에 따른 상환금을 표시하여 표를 채우려고 하는 것입니다. 따라서 열 입력 셀에 C6(원래의 12개월 할부 기간)를 설정합니다.

4. 이렇게 하면 C8셀의 수식이 C6셀의 값인 12 대신 B열에 있는 각각의 값(예: 6, 9, 18, 24)을 선택하도록 변경됩니다. 확인을 클릭합니다.

5. 이렇게 하면 할부 기간을 조정했을 때 그에 따른 월별 상환금이 얼마일지 보여주는 새로운 시나리오가 표에 채워집니다.

이중변수 데이터 표

이중변수 데이터 표를 사용하면 두 가지 변수를 조정할 수 있습니다. 따라서 월 단위의 상환 기간을 변경하는 것뿐만 아니라 대출에 적용되는 이자율도 변경할 수 있습니다. 따라서 행과 열 입력 셀 중 하나만 사용하는 것이 아니라 행 입력 셀과 열 입력 셀을 둘 다 사용하는 것입니다.

아래의 워크시트를 살펴보겠습니다.

이 시나리오에서는 상환 기간과 금리가 모두 변수가 됩니다. B8에 있는 PMT 수식에서 C6의 값은 9~12행의 행 값으로 대체되고, C5의 값은 C~L열의 열 값으로 대체됩니다.

1. 먼저 데이터 표 영역을 지정합니다.
2. 그런 다음 리본에서 데이터 > 가상 분석 > 데이터 표로 이동합니다

3. 3. 행 입력 셀에 C5(금리)를 선택하고 열 입력 셀에 C6(할부 기간)을 선택합니다.

4. 확인을 클릭합니다. 아래 그림과 같이 엑셀에서 시나리오 표가 채워집니다.