엑셀 What-If 분석 완벽 마스터: 비즈니스 시나리오를 예측하고 모델링하는 3가지 검증된 기법

What-If(가상) 분석은 중요한 의사결정을 내리기 전에 불확실성을 탐색하고 다양한 시나리오를 시험해볼 수 있는 강력한 방법입니다. "가격이 오르면 어떻게 될까?", "비용이 줄면 이익은 얼마나 늘어날까?"처럼 비즈니스나 연구에서 궁금한 질문에 답을 찾는 데 큰 도움이 됩니다. 엑셀의 What-If 분석 도구를 활용하면 수식을 다시 작성할 필요 없이 여러 가능성을 손쉽게 탐색할 수 있습니다.

이 튜토리얼에서는 엑셀에서 시나리오를 예측하고 모델링하는 데 활용할 수 있는 세 가지 What-If 분석 기법을 소개합니다.

핵심 기법 세 가지는 다음과 같습니다.

• 목표값 찾기(Goal Seek): 원하는 결과를 얻기 위해 필요한 입력값을 역으로 계산
• 데이터 테이블(Data Tables): 한두 개 변수가 결과에 미치는 영향을 분석
• 시나리오 관리자(Scenario Manager): 여러 가정 조합을 저장하고 비교

1. 목표값 찾기(Goal Seek): 원하는 결과를 위한 입력값 구하기

목표값 찾기는 모델을 역으로 설계하는 역산 분석에 적합한 기능입니다. 하나의 입력값을 자동으로 조정하여 특정 결과값에 도달하도록 해주며, "원하는 결과를 얻으려면 어떤 값을 넣어야 할까?"라는 질문에 답을 줍니다.

목표값 찾기는 반복 계산(iteration)을 통해 입력 셀의 값을 변경하다가, 수식 결과 셀이 목표값에 도달하면 계산을 멈춥니다. 간단한 최적화 문제에는 매우 유용하지만 변수를 하나만 다룰 수 있다는 제한이 있습니다.

목표값 찾기를 활용하면 좋은 경우:

• 목표 이익 달성에 필요한 판매 수량 산출
• 월 상환액 조건에 맞는 대출 이자율 계산
• 제품이나 서비스의 손익분기점 찾기
• 특정 학점을 받기 위해 기말고사에서 필요한 점수 확인

그럼 "2만 달러의 이익을 얻으려면 몇 개를 팔아야 할까?"라는 문제를 함께 풀어보겠습니다.

단계:

• 엑셀에서 모델을 먼저 구성합니다 (예: 제품 정보와 이익 계산식 입력)
• 아래와 같은 수식을 입력해 이익을 계산합니다

• 데이터 탭 → 가상 분석(What-If Analysis) → 목표값 찾기 선택

• 대화상자에서 다음과 같이 설정합니다
  • 값 셀(Set cell): 결과 셀 지정 (예: 이익 수식이 들어 있는 E2)
  • 목표값(To value): 목표 출력값 입력 (예: 목표 이익 20000)
  • 변경할 셀(By changing cell): 조정할 입력 셀 지정 (예: 판매 수량 B2)
• 확인 클릭

• 엑셀이 자동으로 반복 계산을 수행하며 목표 이익에 맞는 판매량을 찾아냅니다

활용 포인트: 손익분기점이나 목표 매출처럼 이미 알고 있는 목표로부터 거꾸로 계산해야 하는 상황이라면 목표값 찾기가 가장 적합합니다.

• 장점: 단일 변수 문제를 빠르고 간단하게 해결
• 단점: 여러 변수를 동시에 처리할 수 없으며, 모델이 복잡하면 수렴하지 않을 수 있음

2. 데이터 테이블(Data Table): 한두 개 변수가 결과에 미치는 영향 확인하기

데이터 테이블을 사용하면 하나 또는 두 개의 입력 변수가 변할 때 결과가 어떻게 달라지는지 하나의 표에서 한눈에 확인할 수 있습니다. 이는 잠재적 결과의 범위를 보여주는 민감도 분석(sensitivity analysis)을 손쉽게 만들어 주는 도구입니다.

• 변수 1개 테이블: 하나의 입력값을 행 또는 열 방향으로 변화시키며 단일 변수의 다양한 값을 테스트
• 변수 2개 테이블: 두 개의 입력값(행 1개, 열 1개)을 조합해 매트릭스 형태로 구성하며, 모든 조합에 대해 모델을 재계산하여 추세를 시각화

변수 1개 데이터 테이블

단가가 달라지면 이익이 어떻게 변하는지 살펴보겠습니다. 단가가 40달러에서 70달러까지 오르면 이익은 얼마나 변할까요?

단계:

• 모델을 구성합니다
  • 가격들을 한 열에 세로로 나열합니다
  • 첫 번째 가격 옆 셀(예: B5)에 메인 이익 결과 셀(예: E2)을 참조하는 수식을 입력합니다

• 제목 행을 포함한 전체 테이블 범위를 선택합니다 (예: A5:B12)
• 데이터 탭 → 가상 분석(What-If Analysis) → 데이터 테이블 선택

• 행 입력 셀은 비워 둡니다
• 열 입력 셀에 원래 단가 셀(A2)을 지정합니다
• 확인 클릭

• 엑셀이 각 가격에 해당하는 이익을 자동으로 계산해 채워줍니다

해석 방법:

• 결과 열은 이익이 가격 변화에 얼마나 민감한지 보여줍니다
• 일반적으로 가격이 높을수록 이익이 증가하지만, 수요 감소를 모델에 반영했다면 그 시점부터는 달라질 수 있습니다

변수 2개 데이터 테이블

이번에는 "단가와 판매 수량이 동시에 변한다면?"이라는 질문을 다뤄보겠습니다.

단계:

• 단가는 세로(왼쪽 열)로, 판매 수량은 가로(맨 윗행)로 입력합니다
  • 테이블의 맨 왼쪽 위 셀(40 위, 800 왼쪽)에 메인 이익 결과 셀을 참조하는 수식을 입력합니다 (예: D5에 =E2)

• 제목 행·열을 포함한 전체 테이블을 선택합니다 (예: D5:H9)
• 데이터 탭 → 가상 분석(What-If Analysis) → 데이터 테이블 선택
• 행 입력 셀에 판매 수량(B2)을 지정합니다
• 열 입력 셀에 단가(A2)를 지정합니다
• 확인 클릭

• 엑셀이 모든 가격–판매량 조합에 따른 이익을 담은 완전한 매트릭스를 생성합니다

참고: 데이터 테이블을 사용할 때는 입력 셀이 반드시 데이터 테이블과 같은 시트에 있어야 합니다. 테이블을 채우는 수식 자체는 참조를 통해 다른 시트에 둘 수 있습니다.

활용 포인트: 데이터 테이블은 가격 탄력성이나 판매량–이익 분석처럼 빠른 민감도 테스트에 가장 적합합니다.

• 장점: 결과 범위를 시각적으로 보여주며, 두 개 변수까지 효율적으로 처리
• 단점: 대규모 테이블에서는 계산 부담이 클 수 있고, 새로 고침 전까지 결과가 정적으로 유지됨

3. 시나리오 관리자(Scenario Manager): 여러 What-If 모델 비교하기

시나리오 관리자를 사용하면 수식을 덮어쓰지 않고도 서로 다른 입력값 조합(시나리오)을 저장하고 비교할 수 있습니다. 시나리오 사이를 빠르게 전환하거나, 모든 시나리오를 나란히 비교하는 요약 보고서를 만들 수도 있습니다. 최적/최악 예측처럼 여러 변수가 얽힌 복잡한 시나리오에 특히 유용합니다.

활용 사례:

• 낙관·비관·기본(baseline) 비즈니스 전망 비교
• 여러 가정이 동시에 바뀌는 다양한 전략 옵션 평가
• 여러 시나리오를 명확하고 체계적인 형식으로 이해관계자에게 제시
• 시간에 따른 모델 버전 관리

최선의 경우(Best Case), 기본 경우(Base Case), 최악의 경우(Worst Case) 세 가지 시나리오로 비즈니스 전망을 만들어 보겠습니다.

단계:

• 데이터 탭 → 가상 분석(What-If Analysis) → 시나리오 관리자 선택

• 추가 클릭
  • 시나리오 이름: 첫 번째 시나리오 이름 입력 (예: Best Case)
  • 변경할 셀에 단가(A2), 판매 수량(B2), 고정비(C2), 변동비(D2)를 지정합니다
  • 확인 클릭

• Best Case 시나리오의 값을 입력합니다
  • 단가: 65
  • 판매 수량: 1600
  • 고정비: 8500
  • 변동비: 25
• 확인 클릭

• 추가를 다시 누르고 같은 방식으로 "Base Case"를 등록합니다

• 추가를 한 번 더 누르고 "Worst Case"도 등록합니다

• 원하는 시나리오 선택 → 표시 클릭으로 해당 값 즉시 적용
• 요약 클릭으로 전체 시나리오 비교

• 시나리오 요약 선택
• 결과 셀(Result cell)로 이익 셀 지정 (예: E2)
• 확인 클릭

• 엑셀이 모든 시나리오의 결과를 비교하는 새로운 시나리오 요약 시트를 자동 생성합니다

활용 포인트: 시나리오 관리자 What-If 분석을 활용하면 엑셀에서 다양한 시나리오를 예측하고 모델링할 수 있습니다. 투자 판단, 예산 수립, 리스크 평가처럼 여러 가정을 함께 저장하고 검토해야 하는 의사결정 상황에서 가장 강력한 도구 중 하나입니다.

• 장점: 여러 변수를 한 번에 관리하며, 보고서로 손쉽게 비교 가능
• 단점: 설정을 수작업으로 진행해야 하며, 시나리오당 변경 셀은 최대 32개로 제한됨

연습용 워크북 다운로드

마무리

이 튜토리얼에서는 엑셀에서 시나리오를 예측하고 모델링하는 세 가지 What-If 분석 기법을 살펴봤습니다. 목표값 찾기, 데이터 테이블, 시나리오 관리자를 활용해 다양한 가능성을 탐색하면 불확실성이 장애물이 아니라 기회로 바뀝니다. 비즈니스 예산 수립, 투자 옵션 평가, 프로젝트 결과 모델링 등 어떤 작업이든 이 엑셀 도구들은 가정의 변화가 결과에 어떤 영향을 미치는지 명확히 이해하도록 도와줍니다.