재무 모델에서 드롭 다운 시나리오 작성 방법 - 가장 일반적으로 사용되는 더미

시나리오를 구축 할 때 가장 일반적으로 사용되는 방법은 수식과 드롭 다운 상자를 함께 사용하는 것입니다. 재무 모델에서 가능한 시나리오 및 해당 입력의 테이블을 만들고 시나리오 이름을 입력 셀 드롭 다운 상자에 연결합니다. 모델의 입력은 시나리오 테이블에 연결됩니다. 모든 입력이 출력으로 흐르는 상태에서 모델이 올바르게 빌드 된 경우 사용자가 드롭 다운 상자에서 다른 옵션을 선택하면 모델 결과가 변경됩니다.

데이터 유효성 검사 드롭 다운 상자는 시나리오 분석을 포함하여 재무 모델링에서 다양한 용도로 사용됩니다.

데이터 유효성 검사를 사용하여 수익성 시나리오 모델링

Download File 0801. xlsx. 그것을 열고 8-1-start라고 표시된 탭을 선택하십시오.

이 모델을 모델화 한 방식으로 입력은 B 열에 정렬되어 있습니다. 입력 중 하나를 변경하여 민감도 분석을 간단하게 수행 할 수 있습니다. 예를 들어 B3 셀의 통화 별 고객을 40에서 45로 변경하고 모든 종속 숫자가 바뀌는 것을 보게 될 것입니다. 이는 하나의 변수 만 변경하기 때문에 민감도 분석이됩니다. 대신이 전체 시나리오 분석 작업에서 여러 변수를 한꺼번에 변경해야하므로 몇 가지 값을 수동으로 조정하는 것 이상을 수행해야합니다.

데이터 유효성 검사 드롭 다운 상자를 사용하여 시나리오 분석을 수행하려면 다음 단계를 따르십시오.

다운로드 한 모델을 가져 와서 C 열의 설명을 잘라내어 F 열로 붙여 넣습니다. 셀 C6: C8을 강조 표시하고 Ctrl + X를 누른 다음 셀 F6을 선택하고 Enter 키를 누릅니다.
셀 B3에서 B8의 입력은 모델을 구동하는 활성 범위이므로 그대로 유지됩니다. 그러나 사용자는 만들려는 드롭 다운 상자에 따라 변경되는 수식이되어야합니다.
B 열의 범위를 C, D 및 E 열로 복사합니다.
B3: B8을 강조 표시하고 Ctrl + C를 누르고 C3: E3 셀을 선택하고 Enter 키를 눌러이 작업을 수행 할 수 있습니다. 이 금액은 변경하기 전까지 각 시나리오마다 동일합니다.
2 행에는 우수 사례 , 기본 사례 , 및 최악 사례를 입력하십시오.
시나리오 분석을위한 모델 설정.
수식은 C12 셀을 선택하고 F2 바로 가기 키를 눌러 볼 수 있듯이 여전히 B 열의 입력에 연결됩니다.
각 시나리오 아래의 입력을 편집하십시오.
가능성이 있다고 생각하는 것을 넣을 수 있지만이 예에서 숫자와 일치 시키려면 값을 입력하십시오. 지금은 열 B를 무시하십시오.

시나리오 분석을위한 입력.
이제 상단에있는 드롭 다운 상자를 추가해야 시나리오가 진행됩니다. 드롭 다운 상자를 정확히 어디에 놓을 지는 중요하지 않지만 일반적으로 페이지 상단에서 찾을 수있는 위치에 있어야합니다.
E1 셀에 Scenario 라는 제목을 입력합니다.
셀 F1을 선택하고이 셀을 편집 가능한 것으로 볼 수 있도록 입력 형식을 변경하십시오.
이 작업을 수행하는 가장 쉬운 방법은 다음 단계를 수행하는 것입니다. 셀 E3과 같이 입력으로 이미 포맷 된 셀 중 하나를 클릭합니다.
1. 홈 탭의 왼쪽에있는 클립 보드 섹션에서 형식 복사 아이콘을 누릅니다. 커서가 페인트 브러시로 바뀝니다.
2. 셀 F1을 선택하여 서식을 붙여 넣습니다.
3. 서식 복사는 일반적으로 일회용입니다. 셀을 선택하면 붓이 커서에서 사라집니다. 서식 복사가 "고정적"이되고 여러 셀에 적용하려면 홈 탭에서 아이콘을 두 번 클릭하십시오.
  이제 셀 F1에서 데이터 탭의 데이터 도구 섹션에서 데이터 유효성 검사를 선택합니다.
데이터 유효성 검사 대화 상자가 나타납니다.
설정 탭에서 허용 드롭 다운을 목록으로 변경하고 마우스를 사용하여 range = $ C $ 2: $ E $ 2를 선택하고 확인을 클릭하십시오.
데이터 유효성 검사 드롭 다운 시나리오 생성.
셀 F1 옆에 나타나는 드롭 다운 상자를 클릭하고 시나리오 중 하나 (예: 기본 사례)를 선택합니다.
시나리오에 수식 적용

B 열의 셀은 여전히 모델을 구동하며 수식으로 바꿔야합니다. 그러나 수식을 추가하기 전에 해당 범위의 셀 서식을 하드 코딩 된 숫자 대신 수식이 들어 있음을 나타내도록 변경해야합니다. 다음 단계를 따르십시오.

셀 B3: B8을 선택하고 홈 탭의 글꼴 그룹에서 채우기 색상을 선택하십시오.

채우기 색을 흰색 배경으로 변경하십시오. 모델에서 수식과 입력 셀을 구별하는 것은 매우 중요합니다. 모델을 여는 모든 사용자가이 범위의 셀에 수식이 포함되어 있으며 재정의되어서는 안됨을 분명히해야합니다.
이제 B 열의 하드 코딩 된 값을 드롭 다운 상자가 변경 될 때 변경되는 수식으로 바꿔야합니다. 이 작업은 여러 가지 다른 기능을 사용하여 수행 할 수 있습니다. HLOOKUP, 중첩 된 IF 문, IFS 및 SUMIF가 모두이 트릭을 수행합니다. 다음 단계에 따라 수식을 추가하십시오.
셀 B3을 선택하고 셀 F1에있는 값에 따라 값을 변경할 수식을 추가하십시오.

= HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)

이 솔루션을 사용하려면 수식을 변경해야합니다. 수식을 복사 할 때 행 색인 번호는 2에서 3 사이입니다. 대신 HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)
= IF ($ F $ 1 = $)와 같이 세 번째 필드에서 ROW 함수를 사용할 수 있습니다. ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = C $ 2, C3, IF ($ F $ 1 = $ D $ 2, D3, E3) $ E $ 2, E3)
- = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
  항상 그렇듯이 선택할 수있는 몇 가지 옵션이 있습니다. 가장 간단하고 이해하기 쉽습니다.이러한 함수 중 하나라도 정확히 동일한 결과를 생성하지만 HLOOKUP에서 행 인덱스 번호를 변경해야하는 것은 강력하지 않으며 ROW를 추가하면 사용자에게 혼동을 줄 수 있습니다. 중첩 된 IF 문은 작성하고 따르는 것이 까다 롭습니다. 새 IFS 함수는 중첩 된 IF 함수를 더 간단하게 만들지 만 여전히 다루기 힘듭니다. SUMIF는 제작하고 따르기가 아주 간단하며 나중에 추가 시나리오를 추가해야하는 경우 쉽게 확장 할 수 있습니다.
- IFS는 Office 365 및 Excel 2016 이상에서만 사용할 수있는 새로운 기능입니다. 이 함수를 사용하고 누군가가 이전 버전의 Excel에서이 모델을 열면 수식을 볼 수는 있지만 편집 할 수는 없습니다.
- B3 셀의 수식을 열 아래로 복사합니다.
- 완성 된 시나리오 분석.
  일반 복사 및 붙여 넣기를 사용하면 모든 서식을 잃게됩니다. 어떤 입력 값이 달러 값, 백분율 또는 고객 번호인지를 한 눈에 볼 수 있도록 모델의 형식을 유지하는 것이 중요합니다. 수식 붙여 넣기를 사용하여 서식을 유지합니다. 셀을 클립 보드에 복사하고 대상 범위를 강조 표시 한 다음 마우스 오른쪽 버튼을 클릭하고 수식 붙여 넣기 아이콘을 선택하여 수식 만 붙여넣고 서식을 그대로두면됩니다.
  
  재미있는 부분을 위해! 모델에서 시나리오 기능을 테스트 할 차례입니다.
셀 F1을 클릭하고 드롭 다운 상자를 변경하고 여러 시나리오간에 전환 할 때 모델 출력이 변경되는지 확인합니다.