[엑셀, 어렵지 않다 XVIII] 시나리오로 상황 가정하기

[호영]

시나리오란 뭘까? 우리가 흔히 말하는 시나리오는 드라마나 영화를 만들기 위해 쓴 각본을 뜻한다. 넓은 의미에서 시나리오는 어떤 사건에서 일어날 수 있는 여러 가지 가상적인 결과나 그 구체적인 과정을 말하기도 한다. 그렇다면 엑셀에서 시나리오는 어떤 형태일까?

엑셀에서 시나리오는 어떤 상황을 가정할 때 사용하는 데이터 분석도구이다. 예를 들어, 최선의 시나리오와 최악의 시나리오를 미리 만들어서 특정상황에 대비하는 것이다. 말 그대로 아직 일어나지 않은 일에 대한 가상의 시나리오(Scenario)를 만드는 것이다.  

시나리오는 엑셀에서 워크시트에 저장하고 자동으로 바꿀 수 있는 값의 집합이다. 여러 가지 값의 그룹을 시나리오로 만들어서 워크시트에 저장한 다음 여러 시나리오 사이를 전환하면서 서로 다른 결과를 볼 수 있다.

시나리오에서 사용하려는 정보가 여러 사용자에게 분산되어 있는 경우 여러 통합 문서에 해당 정보를 수집한 다음 서로 다른 통합 문서의 시나리오를 하나로 병합할 수 있다. 필요한 시나리오가 모두 준비되면 모든 시나리오의 정보를 통합하는 시나리오 요약 보고서를 만들 수 있다.

1. 시나리오 만들기
그럼 시나리오를 만들어보자. 예를 들어 예산을 작성하고 싶지만 수입이 불확실할 경우 시나리오를 사용하면 수입에 대해 여러 개의 예상 값을 정의한 다음 시나리오를 전환하면서 가상 분석을 실행할 수 있다. 시나리오를 만들려면 워크시트에 이미 초기 값 집합이 있는 상태여야 한다. 다음과 같은 데이터를 입력하자. B3셀에는 =B1-B2의 수식이 입력되어 있다.

1) 좌측 그림과 같은 시나리오 이름을 최악의 시나리오라 하고, 셀B1의 값을 \50,000으로, 셀 B2의 값을 \13,200으로 설정한다.



2) 두 번째 시나리오 이름은 최선의 시나리오라 하고, B1의 값을 \150,000으로, B2의 값을 \26,000으로 변경한다.

3) [데이터]에서 [가상분석]의 [시나리오 관리자]를 선택한다. 

4) 다음과 같은 대화 상자가 뜬다. 시나리오를 추가하기 위해 [추가] 버튼을 클릭한다. 

5) 시나리오 편집 상자가 나타난다. 첫 번째 시나리오의 이름을 “최악의 시나리오”로 입력하고, 변경 셀의 범위는 B1:B2를 선택한다. 변경 셀은 가상으로 값을 변경하는 셀이다. 

 
6) 이제 시나리오 값을 입력하는 부분이다. 최악의 시나리오는 총수입(50000원)과 판매금액(13200원)이 얼마씩인지 각 입력란에 입력해준다.

7) 값을 입력하고 확인을 누르면 시나리오 관리자 창이 다시 뜬다. 그곳에서 [추가]버튼을 클릭해 다음 시나리오를 만든다.

 
8) 이번에는 “최선의 시나리오”라는 이름으로 만든다. 위의 그림과 같으면 [확인]을 누른다. 이어서 시나리오 값을 입력한다. 각각 150000과 26000을 입력한다. 그리고 [확인]을 누른다.

9) 다시 시나리오 관리자 창으로 돌아온다. 시나리오 관리자는 현재 등록된 시나리오를 시트에 표시하거나 편집 작업을 하는 곳이다. 더 추가할 시나리오가 있으면 여기서 추가시키면 된다. 

 
10) 이제 시나리오를 워크시트에 적용해 보자. 시나리오 관리자에서 적용할 시트를 선택한 후 [표시] 버튼을 누른다. 그러면 시나리오에 입력된 값이 시트에 표시된다. 자동으로 연결된 셀의 계산 값도 변경되므로 특정 상황을 가정한 시나리오를 한 눈에 알아 볼 수 있다.

11) 최악의 시나리오의 결과가 아래와 같이 표시된다.

 
12) 최선의 시나리오의 결과도 아래의 그림과 같이 표시된다.

어떤가? 한 눈에 그 변화 상황을 알아볼 수 있다.

2. 시나리오 요약하기
이번에는 시나리오를 요약해서 보고서를 만들어보자. 작성된 시나리오가 미래 예측에 도움이 된다고 판단되면 보고서를 통해 시나리오를 인쇄할 수가 있다. 시나리오 요약 보고서는 “시나리오 요약”이라는 시트를 자동으로 만들어 그 시트에 요약된다. 자, 시나리오 관리자 대화 상자에서 [요약] 버튼을 눌러보자.

보고서의 종류를 선택하는 ‘시나리오 요약’ 대화 상자가 나왔다. 여기서 시나리오 요약을 선택하고 결과 셀은 그대로 B3로 놓는다. [확인]을 누르자.

자, 순식간에 ‘시나리오 요약’이라는 새로운 시트가 생기고 시나리오 요약 보고서가 만들어졌다. 

 
어떤가? 정말 쓸만한 기능이지 않은가? 한 가지 알아둘 점은 시나리오 보고서에서는 자동으로 계산을 다시 수행하지 않는다는 것이다. 시나리오의 값을 변경하는 경우 기존 요약 보고서에는 변경 내용이 나타나지 않지만 새 요약 보고서를 만들면 해당 요약 보고서에 변경 내용이 나타난다. 또한 시나리오 요약 보고서를 만드는 데는 결과 셀이 필요하지 않지만 시나리오 피벗 테이블 보고서를 만들 때는 결과 셀이 있어야 한다는 것도 알아두자.

지난 시간에 언급했듯이 각 시나리오에는 최대 32개의 변수 값을 포함할 수 있다. 분석하려는 값이 32개가 넘고 이러한 값에 대응하는 변수가 하나 또는 두 개인 경우 데이터 표를 사용하면 된다. 데이터 표의 변수가 한 개 또는 두 개(행 입력 셀 변수 한 개와 열 입력 셀 변수 한 개)로 제한되기는 하지만 서로 다른 변수 값을 원하는 만큼 데이터 표에 포함할 수 있다. 시나리오에서는 서로 다른 값을 최대 32개까지만 사용할 수 있지만 시나리오는 필요한 만큼 제한 없이 만들 수 있다.@