시나리오 관리자를 사용하여 대출 계산을 모델링하는 방법

시나리오 관리자는 데이터 탭의 가상 분석 섹션에서 목표 추구 및 데이터 테이블과 함께 그룹화됩니다. 매우 유용한 다른 도구와 함께 그룹화하면 Scenario Manager도 알아야 할 중요한 도구라고 믿게 됩니다. 그러나 유용하게 들리는 이름과 좋은 회사에도 불구하고 Scenario Manager는 기능면에서 상당히 제한적이며 이름에서 알 수 있듯이 유용합니다! 따라서 전문 재무 모델러가 자주 사용하지 않습니다.

모델 설정

Scenario Manager를 사용하는 방법을 보여주기 위해 간단한 대출 계산 모델에 적용해 보겠습니다. 대출 계산에 대한 이론은 매우 복잡하지만 다행히 Excel은 대출을 매우 쉽게 처리합니다.

아래 표시된 예에서 이자율 및 대출 조건의 변화에 ​​대한 월별 상환의 민감도를 테스트할 수 있는 이자율 계산기를 볼 수 있습니다. 다음과 같이하세요:

파일 0801.xlsx를 다운로드하고 파일을 열고 8-14라고 표시된 탭을 선택하거나 단순히 하드 코딩된 입력 가정으로 모델을 설정합니다.

PMT 기능을 설정하여 월별 대출 상환액을 계산합니다.

B11 셀에 =PMT(를 입력하고 Ctrl+A를 누릅니다.

함수 인수 대화 상자가 나타납니다.

PMT 기능에는 다음 입력이 필요합니다.

• 이자율: 이자율.
• Nper: 대출 기간 동안의 기간 수입니다.
• Pv: 대출의 현재 가치(대출 금액).
• Fv: 대출 기간 종료 시 남은 금액. (대부분의 경우 대출 기간 동안 전액을 상환하고자 하므로 비워둘 수 있습니다.)
• 유형: 지불이 기간의 시작 또는 끝에서 발생하도록 할지 여부. (이 연습을 위해 이 칸을 비워둘 수 있습니다.)

함수 인수 대화 상자의 필드를 모델의 입력에 연결합니다.

PMT 함수는 연간 상환 금액을 반환합니다. 월 상환액을 계산하고 싶기 때문에 전체 수식을 12로 나누면 되지만, 이자가 복리되기 때문에 수식 내에서 각 필드를 12로 나누는 것이 더 정확합니다. 따라서 첫 번째 필드의 비율은 월별 비율로 변환되고 두 번째 필드의 기간 수도 월별 비율로 변환됩니다.

확인을 클릭합니다.

공식은 =PMT(B7/12,B9*12,B5)입니다.

이 함수는 비용이므로 음수 값을 반환합니다. 여기에서 목적을 위해 함수 앞에 빼기 기호를 붙여 양수로 변경합니다.

시나리오 관리자 적용

이제 시나리오 관리자를 사용하여 몇 가지 시나리오를 추가할 수 있습니다. 입력 변경이 월 상환액에 미치는 영향을 알고 싶습니다. 다음과 같이하세요:

데이터 탭에 있는 리본의 예측 섹션에서 가상 분석 아이콘을 클릭하고 드롭다운 목록에서 시나리오 관리자를 선택합니다.

시나리오 관리자 대화 상자가 나타납니다.

추가 버튼을 클릭하여 새 시나리오를 만듭니다.

시나리오 추가 대화 상자가 나타납니다.

시나리오 관리자를 사용하여 시나리오 작성.

시나리오 이름 상자에 첫 번째 시나리오의 이름을 입력합니다(예: 시나리오 1).

셀 변경 상자에 변수 셀에 대한 셀 참조를 입력합니다.

각 참조를 쉼표로 구분하되(둘 이상인 경우) 공백은 사용하지 마십시오. Ctrl 키를 누른 상태에서 스프레드시트의 각 셀을 클릭하여 상자에 참조를 삽입할 수도 있습니다.

확인을 클릭합니다.

시나리오 값 대화 상자가 기존 값(이자율의 경우 0.045, 연도의 경우 25)과 함께 나타납니다.

확인을 클릭하여 이러한 값을 시나리오 1로 승인합니다.

추가를 클릭하여 다른 시나리오를 추가합니다.

시나리오 추가 대화 상자가 다시 나타납니다.

시나리오 이름 상자에 두 번째 시나리오의 이름을 입력합니다(예: 시나리오 2).

확인을 클릭합니다.

시나리오 값 대화 상자가 다시 나타납니다.

이 시나리오에 대한 변수 값을 입력합니다(예: 이자율의 경우 0.05, 연도의 경우 30).

시나리오 관리자를 사용하여 시나리오 값 입력.

확인을 클릭합니다.

시나리오 관리자 대화 상자로 돌아갑니다.

7~9단계를 다시 수행하여 추가 시나리오를 만듭니다.

모든 시나리오를 만든 후 시나리오 관리자를 사용하여 하단의 표시 버튼을 클릭하여 각 시나리오를 볼 수 있습니다.

입력은 시나리오를 표시하도록 자동으로 변경됩니다.

시나리오는 시트에 따라 다르므로 시나리오를 만든 시트에만 존재합니다. 따라서 생성한 시나리오를 찾을 때 모델에서 올바른 시트를 선택해야 합니다.