엑셀 가상 분석: 목표값 찾기, 시나리오, 데이터 표 사용법

모델은 거꾸로 쓰일 때가 많아요. 필요한 이익은 정해져 있고, 궁금한 건 그 이익을 내려면 가격이 얼마여야 하는지예요. 또는 입력값이 확실하지 않아서, 입력값이 흔들리면 결과가 얼마나 움직이는지 알고 싶을 때도 있죠. 그럴듯해 보일 때까지 숫자를 다시 입력하는 대신, 엑셀의 가상 분석(What-If Analysis) 도구로 이런 질문에 바로 답할 수 있어요. 목표값 찾기는 목표에서 거꾸로 계산하고, 데이터 표는 여러 입력값의 결과를 한 번에 보여 주고, 시나리오 관리자는 입력값 묶음을 통째로 저장해 두고 바꿔 가며 볼 수 있게 해 줘요.

예제 모델

아래 도구는 모두 같은 작은 이익 모델로 연습해요. 입력값은 B2:B4에 있고, B5에서 이익을 계산해요.

=(B2-B4)*B3복사

B5fx=(B2-B4)*B3

	A	B	C	D	E
1	항목	값		가격	이익
2	가격	40		30
3	판매량	500		35
4	단위 원가	25		40
5	이익	7,500		45

가상 분석 도구를 쓰려면 이런 구조가 필요해요. 입력값은 각자 자기 셀에 숫자로 입력되어 있고, 결과는 그 입력값을 참조하는 수식이어야 해요. 가격을 이익 수식 안에 숫자로 직접 넣었다면 어떤 도구도 그 값을 바꿀 수 없어요.

목표값 찾기로 거꾸로 계산하기

지금 판매량과 원가에서 이익 10,000을 내야 해요. 가격이 얼마여야 할까요?

1. 목표값 찾기를 여세요

   데이터› then 예측› then 가상 분석› then 목표값 찾기를 선택하세요. 목표값 찾기(Goal Seek) 대화 상자가 열려요.

2. 세 칸을 채우세요

   • 수식 셀: B5. 결과를 원하는 값으로 맞추고 싶은 수식이에요.
   • 찾는 값: 10000. 목표값이에요. 셀 참조가 아니라 숫자를 입력하세요.
   • 값을 바꿀 셀: B2. 가격이에요. 이 셀에는 수식이 아니라 입력된 값이 있어야 해요.

   확인을 클릭하세요.

3. 답을 남길지 정하세요

   목표값 찾기 상태 대화 상자에 해를 찾았다고 나오고, 시트에는 이미 답이 반영되어 있어요. B2는 45, B5는 10,000이 돼요. 새 가격을 남기려면 확인을, 원래 가격으로 되돌리려면 취소를 클릭하세요.

계산으로도 확인할 수 있어요. 한 개당 10,000 / 500 = 20의 이익을 내야 하고, 20 + 원가 25 = 45예요. 목표값 찾기는 값을 넣어 보면서 범위를 좁혀 가는 방식이라, 더 복잡한 수식에서는 딱 떨어지는 숫자 대신 44.99998처럼 아주 가까운 값이 나올 수도 있어요.

목표값 찾기는 입력값 정확히 하나를 바꿔서 목표 정확히 하나를 맞춰요. "판매량은 얼마여야 할까?"가 궁금하다면 값을 바꿀 셀을 B3로 정하고 다시 실행하세요(답은 666.67개예요).

데이터 표로 여러 입력값 비교하기

목표값 찾기는 답을 하나만 줘요. 데이터 표(Data Table)는 입력값 목록 전체의 결과를 한 번에 보여 주고, 계속 살아 있어요. 단위 원가를 바꾸면 표 전체가 다시 계산돼요.

변수 1개: 가격 다섯 가지의 이익

1. 시험해 볼 입력값을 나열하세요

   D3:D7에 가격 30, 35, 40, 45, 50을 입력하세요.

2. 결과를 가리키는 수식을 넣으세요

   첫 번째 가격보다 한 행 위, 한 열 오른쪽인 E2에 결과를 참조하는 수식을 입력하세요.

   =B5복사

3. 표를 만드세요

   입력값과 수식 행을 포함해서 D2:E7을 선택하세요. 데이터› then 예측› then 가상 분석› then 데이터 표를 선택하세요. 행 입력 셀은 비워 두고 열 입력 셀을 B2로 정한 다음 확인을 클릭하세요.

엑셀이 D열의 가격을 하나씩 B2에 넣어 모델을 다시 계산하고, 그 옆에 이익을 적어 줘요. B2 자체는 바뀌지 않아요.

E3fx=TABLE(,B2)

	A	B	C	D	E
1	항목	값		가격	이익
2	가격	40			7,500
3	판매량	500		30	2,500
4	단위 원가	25		35	5,000
5	이익	7,500		40	7,500
6				45	10,000
7				50	12,500

입력값이 열 방향(아래로)으로 나열되어 있으면 열 입력 셀을, 행 방향(옆으로)으로 나열되어 있으면 행 입력 셀을 쓰세요.

변수 2개: 가격과 판매량을 함께

변수 2개짜리 표는 입력값 하나를 왼쪽에 세로로, 다른 하나를 위쪽에 가로로 놓고, 둘이 만나는 모서리 셀에 수식을 넣어요.

1. 격자를 배치하세요

   A7에 =B5를 입력하세요. B7:F7에는 판매량 300부터 700까지를 가로로, A8:A12에는 가격 30부터 50까지를 세로로 입력하세요.

2. 표를 만드세요

   A7:F12를 선택하고 다시 데이터 표를 여세요. 행 입력 셀은 B3(위쪽의 판매량), 열 입력 셀은 B2(왼쪽의 가격)로 정하고 확인을 클릭하세요.

fx

	A	B	C	D	E	F
1	항목	값
2	가격	40
3	판매량	500
4	단위 원가	25
5	이익	7,500
6
7	7,500	300	400	500	600	700
8	30	1,500	2,000	2,500	3,000	3,500
9	35	3,000	4,000	5,000	6,000	7,000
10	40	4,500	6,000	7,500	9,000	10,500
11	45	6,000	8,000	10,000	12,000	14,000
12	50	7,500	10,000	12,500	15,000	17,500

이 격자는 답의 한 점이 아니라 전체 모양을 보여 줘요. 예를 들어 목표 10,000은 500개를 45에 팔아도, 400개를 50에 팔아도 달성할 수 있어요.

시나리오 관리자로 경우 저장하기

시나리오는 이름이 붙은 입력값 묶음이에요. 최악의 경우를 보려고 입력값 세 개를 다시 입력하는 대신, 클릭 한 번으로 모델 전체를 바꿀 수 있어요.

1. 시나리오 관리자를 여세요

   데이터› then 예측› then 가상 분석› then 시나리오 관리자를 선택하고 추가를 클릭하세요. 시나리오 관리자(Scenario Manager)에서 시나리오를 만들고 관리해요.

2. 현재 입력값부터 저장하세요

   시나리오 이름을 Base Case로 정하고, 변경 셀을 B2:B4로 지정한 다음 확인을 클릭하세요. 시나리오 값 대화 상자에서 현재 값(40, 500, 25)을 그대로 두고 확인을 클릭하세요. 기본 경우를 먼저 저장하는 게 중요해요. 다른 시나리오를 표시하면 입력 셀의 값이 덮어써지거든요.

3. 나머지 경우를 추가하세요

   추가를 다시 클릭해서 Best Case와 Worst Case를 만들고, 위 표의 값을 입력하세요.

4. 시나리오를 표시하세요

   목록에서 시나리오를 선택하고 표시를 클릭하세요. 입력값이 바뀌고 B5가 다시 계산돼요. Best Case는 14,950, Worst Case는 2,450이에요.

5. 나란히 비교하세요

   요약을 클릭하고 시나리오 요약을 고른 다음, 결과 셀을 B5로 정하고 확인을 클릭하세요. 엑셀이 시나리오 요약 시트를 추가하고, 모든 시나리오의 입력값과 이익을 표 하나에 정리해 줘요.

해 찾기가 필요할 때

목표값 찾기는 입력값 하나를 바꿔서 목표 하나를 맞춰요. 해 찾기(Solver)는 여러 입력값을 한꺼번에 바꾸고, 정해 둔 제한 안에서 최대값, 최소값 또는 특정 값을 찾아요. "숫자 하나가 얼마여야 하나?"가 아니라 "가장 좋은 조합은 뭘까?"에 답하는 도구예요.

예를 들어 가격은 45 이하, 판매량은 600 이하라는 조건에서 가격과 판매량(B2:B3)을 바꿔 이익(B5)을 최대화한다고 해 볼게요. 이 모델에서는 두 제한에 모두 닿는 모서리, 즉 45와 600이 가장 좋은 답이고 이익은 12,000이에요. 입력값과 제한이 많은 실제 모델에서는 눈으로는 찾기 어려운 답을 해 찾기가 찾아 줘요.

해 찾기는 엑셀에 포함된 무료 추가 기능이에요. 켜려면 파일 > 옵션 > 추가 기능으로 가서 관리 상자에서 Excel 추가 기능을 고르고 이동을 클릭한 다음, 해 찾기 추가 기능을 체크하고 확인을 클릭하세요. 그러면 데이터› then 분석› then 해 찾기에 나타나요. 자세한 사용법은 이 과정 뒷부분의 해 찾기 튜토리얼에서 다뤄요.

가장 중요한 입력값 보여 주기

데이터 표와 시나리오는 결과를 보여 줘요. 민감도 분석 배치는 한 걸음 더 들어간 질문에 답해요. 어떤 입력값이 결과를 가장 크게 움직일까? 입력값을 하나씩 같은 비율만큼 바꿔 보고, 결과가 흔들리는 폭을 비교하세요.

변동 폭이 큰 순서로 행을 정렬하세요. 맨 위 행이 모델을 믿고 쓰기 전에 다시 확인해 봐야 할 입력값이에요. 여기서는 가격이 10% 틀렸을 때의 영향이 판매량이 10% 틀렸을 때의 두 배가 넘어요. 이 배치를 가로 막대형 차트로 그리면 흔히 말하는 "토네이도" 차트가 돼요.

자주 하는 실수

• 찾는 값에 셀 참조 입력하기. 목표값 찾기의 찾는 값 상자에는 숫자만 넣을 수 있어요. 목표가 셀에 있다면 그 값을 직접 입력하세요.
• 수식이 든 셀을 바꿀 셀로 지정하기. 목표값 찾기, 데이터 표, 시나리오는 입력된 값을 바꿔요. 값을 계산하는 수식 셀이 아니라 숫자가 들어 있는 셀을 지정하세요.
• 기본 경우를 저장하지 않기. 시나리오를 표시하면 입력값이 바뀌어요. Base Case 시나리오가 없으면 원래 숫자가 사라져서 다시 입력해야 해요.
• 행 입력 셀과 열 입력 셀을 바꿔 넣기. 데이터 표 결과가 이상하면, 위쪽에 가로로 놓인 값이 행 입력 셀과, 왼쪽에 세로로 놓인 값이 열 입력 셀과 짝이 맞는지 확인하세요.
• 다른 시트에 있는 입력 셀. 데이터 표의 입력 셀은 표와 같은 시트에 있어야 해요. 표 옆에 입력값을 두거나, 표를 입력값이 있는 시트에 만드세요.

연습

연습

가격 결정 모델 만들기

아래 시트에서 도구 뒤에 숨은 숫자를 직접 계산해 보세요. B7은 목표값 찾기가 B6의 목표에 대해 찾아 줄 가격이고, E열은 변수 1개짜리 데이터 표를 손으로 만든 버전이에요. 그다음 엑셀에서 실제 도구를 써 보세요.

1. A1:B5에 모델을 다시 만들고 B5에 이익 수식을 넣으세요.
2. 목표값 찾기로 이익 10,000을 내는 가격(45)을 찾고, 가격이 40일 때 이익 10,000을 내는 판매량(666.67)도 찾아보세요.
3. 가격(30–50)과 판매량(300–700)에 따른 이익을 보여 주는 변수 2개짜리 데이터 표를 만드세요.
4. Base Case, Best Case, Worst Case 시나리오를 저장하고 시나리오 요약을 만드세요.

그다음 내 의사 결정에 맞는 입력값 세 개짜리 모델을 만들고, 가장 확신이 없는 입력값 두 개로 데이터 표를 만들어 보세요.

직접 해 보기4개 중 0개 완료

1. 1.B5에 이익을 계산해 보세요. 가격에서 단위 원가를 빼고 판매량을 곱하면 돼요.힌트 보기
2. 2.B7에 B6의 목표 이익을 달성하는 가격을 계산해 보세요. 목표값 찾기가 찾아 줄 답이에요.힌트 보기
3. 3.E2에 D2의 가격일 때 이익을 계산해 보세요. 아래로 복사할 수 있게 단위 원가와 판매량은 $로 고정하세요.힌트 보기
4. 4.E6에도 같은 수식으로 D6의 가격일 때 이익을 계산해 보세요.힌트 보기

B5fx수식 입력 셀 B5Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	항목	값		가격	이익
2	가격	40		30
3	판매량	500		35
4	단위 원가	25		40
5	이익			45
6	목표 이익	10,000		50
7	필요한 가격

실력 점검

• 목표값 찾기로 수식이 목표에 도달하는 데 필요한 입력값을 찾을 수 있나요?
• 가격 다섯 가지의 이익을 보여 주는 변수 1개짜리 데이터 표를 만들 수 있나요?
• 최상, 기본, 최악 시나리오를 설정하고 요약 보고서로 비교할 수 있나요?
• 해 찾기가 목표값 찾기와 달리 무엇을 할 수 있는지 설명할 수 있나요?