가정 분석(What-If Analysis)은 생각보다 간단한 개념입니다. 한마디로 정리하면 "만약 이런 일이 발생하면, 내 수치나 최종 결과는 어떻게 될까? 예를 들어 앞으로 몇 달간 매출이 2만 달러 발생한다면, 순이익은 얼마나 나올까?"라는 질문에 답하는 것이죠. 가정 분석의 가장 기본적인 목적은 바로 이러한 미래 예측(projection)입니다.
엑셀의 다른 기능들과 마찬가지로 가정 분석 기능도 매우 강력합니다. 비교적 단순한 What-If 예측부터 고도화된 시나리오 분석까지 다양하게 활용할 수 있으며, 엑셀 기능 특성상 이 짧은 튜토리얼에서 모든 가능성을 다루기는 어렵습니다.
따라서 오늘은 기본기를 중심으로, 바로 시작할 수 있는 몇 가지 쉬운 가정 분석 개념을 소개해 드리겠습니다.
기본적인 매출 예측 만들기
누구나 아시다시피 올바른 손에 들어간 숫자는 무엇이든 말하게 만들 수 있습니다. 흔히 '쓰레기를 넣으면 쓰레기가 나온다(Garbage in, garbage out)' 또는 '예측은 그 전제 조건만큼만 정확하다'는 말로 표현되곤 하죠.
엑셀은 가정 분석을 설정하고 활용할 수 있는 다양한 방법을 제공합니다. 여기서는 비교적 간단하고 직관적인 예측 방법인 데이터 표(Data Table)를 살펴보겠습니다. 이 방법을 사용하면 세금 납부액처럼 한두 개 변수를 변경했을 때 회사의 최종 손익에 어떤 영향을 미치는지 확인할 수 있습니다.
그 밖에도 두 가지 중요한 개념이 있습니다. 바로 목표값 찾기(Goal Seek)와 시나리오 관리자(Scenario Manager)입니다. 목표값 찾기는 '100만 달러 순이익 달성' 같은 미리 정해진 목표를 이루기 위해 어떤 조건이 충족되어야 하는지 역산하는 기능이고, 시나리오 관리자는 여러 가정 시나리오를 직접 만들고 체계적으로 관리할 수 있게 해줍니다.
데이터 표 방식 – 변수 하나 사용하기
먼저 새 표를 만들고 데이터 셀에 이름을 지정해 보겠습니다. 왜 이름을 지정할까요? 수식에서 셀 좌표 대신 이름을 사용할 수 있어서입니다. 큰 규모의 표를 다룰 때 훨씬 더 정확하고 실수를 줄일 수 있으며, 많은 사용자가 이 방식을 더 편리하게 느낍니다.
우선 변수 하나짜리 예제부터 시작한 뒤, 두 변수로 확장해 보겠습니다.
- 엑셀에서 빈 워크시트를 엽니다.
- 아래와 같은 간단한 표를 만듭니다.
1행의 표 제목을 만들기 위해 A1과 B1 셀을 병합했습니다. 병합하려면 두 셀을 선택한 후 홈 리본에서 병합 후 가운데 맞춤 드롭다운 화살표를 클릭하고 셀 병합을 선택하세요.
- 이제 B2와 B3 셀에 이름을 지정합니다. B2 셀을 마우스 오른쪽 버튼으로 클릭하고 이름 정의를 선택하면 새 이름 대화 상자가 열립니다.
새 이름 대화 상자는 매우 직관적입니다. 범위(Scope) 드롭다운에서는 통합 문서 전체 기준으로 이름을 지정할지, 활성 워크시트에만 적용할지 선택할 수 있습니다. 이번 예제에서는 기본값을 그대로 사용하면 됩니다.
- 확인을 클릭합니다.
- B3 셀에는 Growth_2019라는 이름을 지정합니다. 이 경우에도 기본값과 동일하므로 확인을 누르면 됩니다.
- C5 셀의 이름을 Sales_2019로 변경합니다.
이름을 지정한 셀을 클릭하면 워크시트 왼쪽 위 이름 상자(아래 이미지에서 빨간색으로 표시)에 셀 좌표 대신 지정한 이름이 표시됩니다.
가정 시나리오를 만들려면 C5 셀(현재 Sales_2019)에 수식을 입력해야 합니다. 이 작은 예측 시트를 통해 성장률에 따라 얼마의 수익을 올릴 수 있는지 확인할 수 있습니다.
현재 성장률은 2%입니다. 서식이 완성된 후에는 B3 셀(현재 Growth_2019)의 값만 변경하면 다양한 성장률에 따른 결과를 손쉽게 얻을 수 있습니다.
- C5 셀(아래 이미지에서 빨간색으로 표시)에 다음 수식을 입력합니다.
=Sales_2018+(Sales_2018*Growth_2019)
수식 입력을 마치면 C5 셀에 예측 금액이 표시됩니다. 이제 B3 셀의 값만 바꿔주면 성장률 기반의 매출 예측이 가능합니다.
직접 해보세요. B3 셀 값을 2.25%로 바꿔보고, 이어서 5%로도 변경해 보세요. 감이 잡히시나요? 방법은 단순하지만, 그 안에 담긴 활용 가능성은 무궁무진합니다.
데이터 표 방식 – 변수 두 개 사용하기
수입이 곧 이익인 세상, 즉 비용이 전혀 없는 세상이라면 얼마나 좋을까요? 하지만 현실은 그렇지 않습니다. 따라서 우리의 가정 분석 시트도 항상 낙관적일 수만은 없습니다.
예측에는 비용도 반영해야 합니다. 다시 말해 예측 변수는 수입(성장률)과 비용, 두 가지가 되는 것입니다.
이를 설정하기 위해 앞서 만든 스프레드시트에 새 변수를 추가해 보겠습니다.
- A4 셀을 클릭하고 Expenses 2019라고 입력합니다.
- B4 셀에 10.00%를 입력합니다.
- C4 셀을 마우스 오른쪽 버튼으로 클릭하고 팝업 메뉴에서 이름 정의를 선택합니다.
- 새 이름 대화 상자의 이름 입력란에 Expenses_2019를 입력합니다.
여기까지는 어렵지 않았죠? 이제 남은 일은 수식에 C4 셀의 값을 포함하도록 수정하는 것뿐입니다.
- C5 셀의 수식을 다음과 같이 수정합니다. 괄호 안 데이터 끝에 *Expenses_2019를 추가하세요.
=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)
짐작하셨겠지만, 포함하는 데이터의 종류와 수식 작성 능력 등 여러 요소에 따라 가정 분석은 훨씬 더 정교하게 만들 수 있습니다.
어쨌든 이제 수입(성장률)과 비용, 두 가지 관점에서 예측을 할 수 있습니다. B3와 B4 셀의 값을 자유롭게 바꿔보며 여러분만의 What-If 워크시트를 직접 실험해 보세요.
더 깊이 학습하기 위한 추가 자료
엑셀의 거의 모든 기능이 그렇듯, 가정 분석 역시 상당히 복잡하고 정교한 시나리오까지 확장할 수 있습니다. 사실 예측 시나리오만 주제로 여러 편의 글을 써도 주제를 완전히 다루기 어려울 정도입니다.
그동안 더 심화된 What-If 스크립트와 시나리오를 학습할 수 있는 유용한 링크 몇 가지를 소개합니다.
- What-If Analysis: 삽화가 풍부한 실습 가이드로, 엑셀 시나리오 관리자를 활용해 나만의 가정 시나리오를 만들고 관리하는 방법을 다룹니다.
- Introduction to What-If Analysis: 마이크로소프트 오피스 공식 지원 사이트의 가정 분석 입문 자료입니다. 방대한 정보와 함께 유용한 What-If 튜토리얼 링크를 다수 제공합니다.
- How to use Goal Seek in Excel for What-If analysis: 엑셀의 목표값 찾기 기능을 활용한 가정 분석 입문 가이드입니다.