Excel로 하는 미래 예측(1): 가상분석(What-If Analysis)

미래 시나리오(scenarios) 자체가 ‘이론(theory)’ 없이 데이터만으로 고안될 수는 없다. 예컨대 내년도 경기 전망 시나리오는 경제 이론에 기반해서 만들어지고 남북한 관계 시나리오는 정치 이론에 기반해서 만들어 진다. 아직까지 어떤 컴퓨터 소프트웨어도 그러한 시나리오를 개발하지 못한다. 언젠가 도메인 이론, 시나리오 개발 방법론, 그리고 데이터를 충분히 입력해주면 AI가 그러한 시나리오를 개발해 낼 수 있겠지만 아직 시나리오 개발은 인간의 영역이다.

하지만 일단 시나리오가 주어지면 그것에 기반한 예측은 엑셀로 구현된다. 엑셀 상단 메뉴 중 ‘데이터’ 하위 메뉴에 가상분석(what-if analysis)을 이용하면 된다.

어떤 서점이 있다고 하자. 그 서점에 A라는 책 100권이 들어왔다. 그 책은 모두 정가에 팔릴 수도 있고 일부가 세일 가격에 팔릴 수도 있다. 그에 따라 기대 수익이 달라질 것이다. 만약 60%가 정가에 팔리고 그 때 권당 수익이 3천원이며, 나머지 40%가 세일 가격에 팔리고 그 때 권당 수익이 1천원이라면 A를 판매한 총수익은 22만원일 것이다. 그런데 정가에 판매되는 비율을 70%, 80%, 90%, 100%로 올리면 총수익이 어떻게 달라질까? 이러한 다양한 시나리오를 엑셀을 가지고 보여줄 수 있다.

먼저 기본 정보와 계산식을 가지고 데이터 시트를 작성한다(아래 그림 참조). 그림에서 C6 셀에는 =B3*C3을 넣고, C7 셀에는 =B3*(1-C3)를 넣는다. 총수익이 표시되는 D9에는 =(C6*D6)+(C7*D7)을 넣는다. 그런 다음 상단 메뉴에서 데이터-가상분석-시나리오 관리자를 연다.

위 창이 열리면 오른편 상단의 추가 단추를 눌러서 시나리오를 정의한다. 먼저 60%가 정가로 팔리는 시나리오를 “60% 정가”로 이름 붙이자. 변경 셀은 $C$3를 넣는다. 고정주소로 표기하자. 그리고 확인 단추를 누르면 변경 셀에 해당하는 값을 입력하라는 메시지가 뜬다. 0.6을 넣고 확인. 그러면 아래 그림처럼 60% 정가라는 시나리오가 정의되어 있을 것이다.

동일한 방식으로 70% 정가, 80% 정가, 90% 정가, 100% 정가 시나리오를 추가한다. 그러면 아래처럼 다섯가지 다른 시나리오가 정의될 것이다.

위 창에서 표시를 누르면 선택된 시나리오에 따라 계산 총수익이 표시된다. 예컨대 70% 정가를 선택하고 표시를 누르면 원래의 데이터 시트가 아래처럼 변할 것이다. 70%가 정가에 팔리면 총수익이 24만원이 된다.

이 시나리오들에 의해 예상되는 총수익을 한꺼번에 보여줄 수 있는 기능이 ‘요약’이다. 시나리오 관리자 창에 있는 요약 버튼을 누르고 시나리오 요약 창이 뜨면 보고서 종류를 선택하고 결과 셀에 $D$9을 넣는다. 그러면 아래 그림처럼 새 시트에 요약 보고서가 뜰 것이다. 정말 사용이 쉽다.

내친 김에 가상분석에 있는 ‘목표값 찾기’도 사용해 보자. 그 기능은 여러가지로 사용될 수 있지만 여기서는 위 사례에서 수익 28만원이 나려면 몇 %를 정가에 팔아야 하는가 하는 문제에 답을 찾아보자. 목표값 찾기를 클릭하여 나타난 창에 아래 그림과 같이 해당 값을 넣는다.

그러면 원 데이터 시트에서 C3 셀이 90%로 바뀔 것이다. 정가로 90%를 팔아야 28만원이 남는다는 말이다.

가상분석에서 다른 하나의 기능은 ‘데이터 표’이다. 이는 엑셀에게 계산식을 알려주고 입력값을 변경하면 결과값이 어떻게 달라지는 지를 보여달라는 명령이다. 예컨대 가격이 100원인 품목를 판매한다고 하자. 다양한 판매 개수에 따른 매출액을 제시할 수 있다. 5개를 팔면 500원이 나오는 계산식 =B1*B2을 E2에 넣는다. 그런 다음 계산을 원하는 품목 B의 개수를 D 열에 나열한다. 이제 D2부터 E6까지를 모두 선택한다. 메뉴에서 데이터-데이터표를 연다. 그리고 아래 그림처럼 B 품목의 개수가 있는 셀인 $B$1을 열에 넣는다. 그러면 원 데이터 시트의 E3부터 E6에 각 개수에 따른 판매액이 표시된다.

이상으로 엑셀의 가상분석(What-If Analysis)의 세 가지 기능을 살펴보았다. 이는 간단하지만 현업에 유용한 기능으로 생각된다. 이제 엑셀에 있는 좀 더 심각한 미래 예측 기능에 대해 알아보자.