목표값 찾기와 시나리오 관리자로 비즈니스 예측 모델링하기

(Mode 2에서 작성 예정)

“그래서, 몇 개 팔아야 본전인데?” 질문에 답하기

안녕하세요. 여러분의 비즈니스 인사이트를 넓혀드리는 엑셀 파트너 직장인 꿀팁 저장소입니다.

지난 포스팅에서는 파워쿼리의 Unpivot 기능으로 뒤죽박죽인 데이터 구조를 올바르게 고치는 법을 배웠습니다.

이제 데이터가 깔끔하게 준비되었으니, 이를 바탕으로 미래를 예측하고 전략을 짤 차례입니다.

사업을 하거나 기획 업무를 하다 보면 이런 질문을 수시로 받습니다.
“마진율을 5% 올리면 이익이 얼마나 늘어날까?”
“이번 달 목표 매출 1억 원을 달성하려면 하루에 커피를 몇 잔 팔아야 할까?”

이런 질문을 받을 때마다 계산기를 두드리거나 수식을 이리저리 고치며 숫자를 때려 맞추고 계신가요?

엑셀에는 정답(목표)을 정해놓으면 그 과정을 알아서 계산해 주는 가상 분석(What-If Analysis) 도구가 숨어 있습니다.

오늘은 엑셀이 미래를 예측하는 두 가지 방식, 목표값 찾기와 시나리오 관리자에 대해 완벽하게 파헤쳐 봅니다.

가상 분석(What-If Analysis)이란?

단어 그대로 “만약에(What If)… 라면 어떻게 될까?”를 시뮬레이션하는 기능입니다.

엑셀의 [데이터] 탭 – [예측] 그룹에 보면 작은 물음표 아이콘과 함께 숨겨져 있습니다.

우리가 보통 엑셀을 쓸 때는 [입력값 A + 입력값 B = 결과값 C] 순서로 계산합니다.
하지만 가상 분석은 반대입니다. [결과값 C]가 나오려면 [입력값 A]는 얼마여야 하는가?를 역으로 추적합니다.

이를 전문 용어로 역산(Back-solving)이라고 합니다.

도구 1: 목표값 찾기 – 역산(Back-solving)의 마법

가장 단순하고 강력한 도구입니다. 변수가 딱 하나일 때 사용합니다.

• “순이익이 0원(손익분기점)이 되려면 판매량은 얼마여야 하는가?”
• “평균 90점을 맞으려면 기말고사에서 몇 점을 받아야 하는가?”

이처럼 고정된 하나의 목표(Goal)를 달성하기 위해 바뀌어야 할 하나의 변수를 찾아냅니다.

실전 1단계: 손익분기점(BEP) 계산기 만들기

카페를 운영한다고 가정해 봅시다. 먼저 엑셀에 간단한 수식을 짜야 합니다. (수식이 없으면 목표값 찾기는 작동하지 않습니다.)

순이익 = (판매가 – 원가) × 판매량 – 고정비

예제 데이터를 입력합니다.

• 판매가: 5,000원
• 원가: 1,500원
• 고정비(월세 등): 3,000,000원
• 판매량: 100잔 (현재)
• 순이익(수식): -2,650,000원 (적자)

지금 100잔 팔아서는 적자입니다. 도대체 몇 잔을 팔아야 적자를 면할까요?

실전 2단계: 목표 순이익 달성을 위한 판매량 찾기

이제 엑셀에게 일을 시켜봅시다.

1. 순이익이 계산된 셀(수식 셀)을 클릭합니다.
2. [데이터] 탭 – [가상 분석] – [목표값 찾기]를 클릭합니다.
3. 대화 상자가 뜨면 3가지만 입력하면 됩니다.
   • 수식 셀: 순이익 셀 (이미 선택됨)
   • 찾는 값: 0 (손익분기점이니까 0원)
   • 값을 바꿀 셀: 판매량 셀 (여기가 궁금한 거니까요)
4. 확인을 누르는 순간, 판매량 셀의 숫자가 857.14...로 바뀝니다.

즉, 최소 858잔을 팔아야 본전이라는 계산이 0.1초 만에 나옵니다. 만약 “목표 순이익 500만 원”을 넣었다면? 판매량은 약 2,286잔으로 바뀔 것입니다.

목표값 찾기의 더 다양한 예제가 궁금하다면 MS 공식 목표값 찾기 가이드를 참고해 보세요.

도구 2: 시나리오 관리자 – 최선과 최악을 대비하라

목표값 찾기는 변수가 하나라서 좋지만, 비즈니스 현실은 복잡합니다. 판매량도 바뀌고, 원가도 오르고, 환율도 바뀔 수 있죠. 여러 변수가 동시에 움직일 때의 결과를 비교하고 싶다면 시나리오 관리자가 정답입니다.

시나리오란?

미래에 일어날 수 있는 여러 가지 상황(Case)입니다.

• Best Case (낙관): 원가 하락, 판매량 폭증
• Normal Case (일반): 현재 추세 유지
• Worst Case (비관): 원가 상승, 판매량 저조

이 3가지를 저장해 두고 버튼 한 번으로 결과를 비교할 수 있습니다.

실전 3단계: 낙관적 vs 비관적 시나리오 비교 보고서

1. [데이터] – [가상 분석] – [시나리오 관리자]를 클릭합니다.
2. [추가] 버튼을 누르고 시나리오 이름(예: 최악 / 최상) 을 입력합니다.
3. 변경 셀에 변수들(판매가, 원가, 판매량 셀)을 다중 선택하여 넣습니다.
4. 각 변수에 해당하는 값(예: 판매가는 올리고, 원가는 내린 숫자)을 입력하고 확인을 누릅니다.
5. 같은 방식으로 ‘최악의 상황’ 시나리오도 추가합니다.

여기서 끝이 아닙니다. 창 하단에 있는 [요약] 버튼을 눌러보세요.
새로운 시트가 생성되면서, 모든 시나리오의 입력값과 결과값(순이익)을 비교하는 [시나리오 요약 보고서]가 짠 하고 나타납니다.

상사에게 보고할 때는 이 요약 시트 하나면 충분합니다.

비교 정리: 목표값 찾기 vs 시나리오 관리자

헷갈리시는 분들을 위해 딱 정리해 드립니다.

마무리하며

오늘은 엑셀을 단순 계산기가 아닌 ‘경영 시뮬레이터’로 활용하는 엑셀 목표값 찾기와 시나리오 관리자에 대해 알아봤습니다.

이제 감(Feeling)으로 예측하지 말고, 엑셀이 보여주는 정확한 숫자로 비즈니스 전략을 세워보세요.

다음 시간에는 이렇게 만든 보고서의 숫자를 더 직관적으로 보여주는 기술, 보이는 대로 믿지 마라, 사용자 지정 표시 형식(Custom Format)의 비밀 편으로 찾아오겠습니다.

여러분의 칼퇴를 꿀팁 저장소가 응원합니다!