Excel의 가정 분석: 목표값 찾기, 시나리오 관리자, 데이터 표

다음 세 가지를 지정합니다.

• 설정 셀: 수식이 들어있는 셀(출력)
• 값: 원하는 목표 결과
• 변경할 셀: Excel이 조정할 수 있는 입력 셀

실제로 해보겠습니다. 

소규모로 노트북(공책)을 판매하는 사업을 운영합니다. 현재 매출은 $25,000이고 목표는 $50,000입니다. 가격은 개당 $25로 고정되어 있으니, 판매해야 할 수량을 찾아야 합니다. 

다음 단계로 진행하세요:

1. 데이터 > 가정 분석 > 목표값 찾기로 이동

2. 설정 셀에서 B4 셀(매출 수식 셀)을 클릭

3. 값에 50000 입력

4. 변경할 셀에는 B2(판매 수량)를 선택

5. 확인을 클릭

목표값 찾기는 B2를 1,000에서 2,000개로 조정합니다. 개당 $25일 때 $50,000 매출에 필요한 정확한 수량입니다.

목표값 찾기로 특정 결과 달성. 이미지: 작성자.

Excel에서 시나리오 관리자 사용 방법

시나리오 관리자는 동일한 스프레드시트에서 여러 입력값 세트를 저장하고 전환할 수 있는 가정 분석 도구입니다. 저장된 각 버전을 시나리오라고 합니다. 

필요한 만큼 만들고 즉시 전환해 결과를 비교할 수 있습니다. 

별도의 파일을 세 개 만들지 않고도 최적·최악·기대 시나리오를 계획할 때 유용합니다. 

다음 세 가지를 지정합니다.

• 시나리오 이름: 각 가정 세트를 나타내는 레이블
• 변경 셀: 시나리오마다 달라지는 입력 셀
• 값: 각 시나리오의 구체적인 숫자

시나리오 만들기

노트북 사업의 분기별 매출을 계획한다고 가정해 봅시다.

현재 이익은 $15,000입니다. 최적, 최악, 기대 시나리오 세 가지를 모델링하려고 합니다. 

시나리오를 만드는 방법은 다음과 같습니다.

1. 데이터 > 가정 분석 > 시나리오 관리자로 이동

2. 시나리오 관리자 대화상자에서 추가 클릭

3. 시나리오 이름에 Best Case 입력

4. 변경 셀에는 B2:B4를 선택합니다.

5. 그러면 시나리오 값 대화상자가 열립니다. 다음 값을 입력합니다.

• B2 = 2000 

• B3 = $30

• B4 = $10

6. 확인을 클릭합니다. 

아래 값을 사용해 나머지 두 시나리오도 같은 방식으로 만듭니다.

세 가지 경우로 시나리오를 생성합니다. 이미지: 작성자.

시나리오를 전환하려면 목록에서 하나를 선택하고 표시를 클릭하세요. Excel이 값을 즉시 바꾸고 이익이 업데이트됩니다.

각 시나리오의 값을 보려면 표시를 클릭. 이미지: 작성자.

시나리오 편집

목록에서 아무 시나리오나 선택하고 편집을 클릭합니다. 이름을 바꾸거나, 포함할 셀을 변경하거나, 값을 업데이트할 수 있습니다. 스프레드시트는 표시를 클릭하기 전까지 변경되지 않습니다.

시나리오 보고서 생성

시나리오 관리자는 저장된 모든 시나리오를 나란히 비교하는 보고서를 만들 수도 있습니다. 

시나리오 보고서를 생성하려면:

1. 시나리오 관리자 안에서 요약을 클릭

2. 시나리오 요약에서 시나리오 요약을 선택

3. 결과 셀에서 B7(이익 셀)을 선택

4. 확인 클릭

Excel이 새 시트에 세 가지 시나리오를 나란히 보여주는 요약 표를 생성합니다.

시나리오 요약 보고서 생성. 이미지: 작성자.

Excel에서 데이터 표 사용 방법

데이터 표는 일정 범위의 입력값에 대해 반복 계산을 자동화합니다. 입력을 수동으로 바꾸고 출력을 기록하는 과정을 반복하는 대신, 한 번 범위를 정의하면 Excel이 모든 결과를 한꺼번에 채워 줍니다.

다음 세 가지를 지정합니다.

• 입력 범위: 시험할 값들
• 행/열 입력 셀: 해당 값들이 대체할 수식 내 셀
• 수식 셀: 표가 기반으로 하는 출력

단일 변수 데이터 표

단일 변수 데이터 표는 하나의 입력을 변경할 때 수식 결과가 어떻게 변하는지 테스트합니다.

예를 들어 대출 상환액 수식이 있고, 금리가 5%에서 10%로 변할 때 월 상환액이 어떻게 바뀌는지 보고 싶다고 합시다.

단일 변수 데이터 표를 만들려면:

1. 데이터셋을 준비합니다. 한 가지 유의할 점: 제 데이터셋에서는 E1 셀(첫 번째 금리 바로 위, 열 머리글 옆이 아님)에 =B5를 입력합니다. E1이 비어 있거나 수식 참조가 다른 셀에 있으면 데이터 표가 실행되더라도 상환액 대신 모두 0으로 반환됩니다.

단일 변수 데이터 표 만들기. 이미지: 작성자.

2. 이어서 D1:E9 전체 범위(E1 포함)를 선택

3. 데이터 > 가정 분석 > 데이터 표로 이동

4. 행 입력 셀은 비워 둠

5. 열 입력 셀에 B4(금리)를 선택

6. 확인을 클릭합니다.

Excel이 각 입력값에 대한 계산 결과로 표를 채웁니다. 상환액이 균등하게 증가하지 않는 점을 확인하세요. 3%에서 4%로는 $49 증가하지만, 9%에서 10%로는 $61 증가합니다. 금리 변화는 높아질수록 영향이 더 큽니다.

각 입력값에 대한 계산 결과. 이미지: 작성자.

이변수 데이터 표

이변수 데이터 표는 두 개의 입력을 동시에 변경할 때 수식 결과가 어떻게 변하는지 테스트합니다.

예를 들어 가격 모델은 제품 가격과 판매 수량 모두에 좌우될 수 있습니다. 이변수 데이터 표는 다양한 가격과 판매량에서의 이익을 한눈에 보여줍니다.

먼저 데이터셋을 구성합니다. 

사용할 데이터셋은 다음과 같습니다.

• D1 셀에 =B5를 입력합니다. 이는 수식 참조이며, 라벨 옆이 아니라 그리드의 좌상단 모서리에 있어야 합니다.

• 1행에서 E1부터 가로로 시험할 판매 수량을 나열합니다: 500, 1000, 1500, 2000

• D열에서는 D2부터 세로로 시험할 가격대를 나열합니다: $20, $25, $30, $35

이변수 데이터 표용 데이터셋 설정. 이미지: 작성자.

이변수 데이터 표를 만들려면:

1. 범위 전체 D1:H5를 선택합니다.

2. 데이터 > 가정 분석 > 데이터 표로 이동

3. 행 입력 셀에 B3(판매 수량)를 선택

4. 열 입력 셀에 B2(단가)를 선택

5. 확인을 클릭

Excel이 모든 가격과 판매 수량 조합에 대한 이익을 채워 넣습니다.

입력값에 대한 계산 결과. 이미지: 작성자.

이변수 데이터 표는 하나의 가정만으로는 전체 그림을 보여주지 못할 때 특히 유용합니다. 가격만 보면 괜찮아 보여도, 실제로 고객이 몇 개를 사느냐에 따라 이익이 달라집니다. 그 두 번째 변수가 모델을 더 유용하게 만듭니다.

목표값 찾기 vs. 시나리오 관리자 vs. 데이터 표

목표값 찾기, 시나리오 관리자, 데이터 표는 모두 Excel의 가정 분석 도구 모음에 속하지만, 해결하는 문제는 다릅니다. 

아래에서 세 도구를 간단히 비교해 봅니다. 

가정 분석 vs. Solver

가정 분석과 Solver는 Excel에서 다양한 결과를 탐색한다는 공통점이 있지만, 해결하는 문제 유형이 다릅니다.

• 가정 분석은 가정을 시험합니다. 하나 이상의 입력을 바꾸고 그 변화가 결과에 미치는 영향을 봅니다. 목적은 최적해를 찾기보다 가능한 결과를 이해하는 데 있습니다.
• Solver는 방식이 다릅니다. 이익 최대화나 비용 최소화처럼 목표를 주면, 그 목표를 가장 잘 만족하는 입력 조합을 탐색합니다. 가정을 시험하는 것이 아니라 최적의 가정을 구합니다. 

예를 들어 대출 신청자는 목표값 찾기로 월 $500 상환액이 되도록 하는 대출 금액을 구할 수 있습니다. 반면 제조 기업은 인력과 자재 제약 내에서 이익을 최대화하는 생산 조합을 찾기 위해 Solver를 사용할 수 있습니다. 

참고: Solver는 표준 가정 분석 도구에 포함되지 않습니다. 별도의 추가 기능(add-in)입니다. 대부분의 Excel 버전에서 데이터 탭에 표시되기 전에 Excel 옵션에서 수동으로 켜야 합니다.

가정 분석 모범 사례

가정 분석을 사용할 때 따라야 할 모범 사례는 다음과 같습니다. 

1. 가정과 수식을 분리하세요

입력값은 전용 셀에 저장하고 수식에서는 그 셀을 참조하세요. 숫자를 계산식에 직접 하드코딩하는 것은 피합니다. 

예를 들어 다음 수식은 업데이트하기 어렵습니다.

=(50*1000)-10000

하지만 이 버전은 유지보수가 훨씬 쉽습니다.

=(B2*B3)-B4

가정이 바뀌면 통합 문서 전체의 수식을 고치지 않고 입력 셀만 업데이트하면 됩니다.

2. 시나리오에 명확한 라벨을 붙이세요

시나리오 이름은 즉시 무엇을 의미하는지 알려줘야 합니다. "Scenario 1", "Scenario 2", "Scenario 3" 같은 이름은 여섯 달 뒤에 아무 의미도 주지 못합니다. "Best Case: 15% Growth"처럼 실제 의미를 드러내는 이름을 사용하세요. 

3. 입력값을 검증하세요

가정 분석의 신뢰도는 시험하는 입력값만큼입니다. 분석을 실행하기 전에 다음을 확인하세요.

• 백분율이 올바르게 입력되었는지
• 날짜 형식이 일관적인지
• 측정 단위가 일관적인지
• 입력값이 현실적인 범위 안에 있는지

판매 500% 증가에 기반한 매출 예측은 기술적으로 맞을 수 있어도 의사결정에는 무용할 수 있습니다.

4. 가정을 문서화하세요

지금은 이해되는 모델도 석 달 뒤에는 본인이나 다른 사람에게 이해되지 않을 수 있습니다. 

각 가정 옆에 "Q3 실적 기준" 같은 짧은 메모만 있어도 나중에 추측하는 시간을 크게 줄일 수 있습니다. 문서화는 숫자의 출처를 이해하게 하고 향후 업데이트도 훨씬 쉽게 만듭니다.

가정 분석에서 흔한 문제

가정 분석 문제의 대부분은 도구 자체보다 시트 설정에서 비롯됩니다. 목표값 찾기, 시나리오 관리자, 데이터 표가 예상과 다른 결과를 낸다면, 몇 가지 일반적인 문제 지점을 확인하세요. 보통 그걸로 해결됩니다.

1. 수식이 업데이트되지 않음 

입력을 바꿨는데 아무 일도 일어나지 않습니다. 보통은 계산 모드가 어느 순간 수동으로 바뀌었기 때문입니다. 

해결 방법: 수식 > 계산 옵션으로 가서 자동으로 설정되어 있는지 확인하세요. 이미 자동이라면 F9를 눌러 강제 재계산합니다.

2. 잘못된 셀 참조

가정 분석 도구는 정확한 셀 참조에 의존합니다. 잘못된 셀을 선택하면 Excel이 계산을 완료하더라도 결과는 잘못될 수 있습니다. 

예를 들어 목표값 찾기가 잘못된 입력 셀을 변경할 수 있습니다. 데이터 표가 잘못된 행 또는 열 입력 셀을 참조할 수 있습니다. 시나리오 관리자가 잘못된 셀의 값을 저장할 수도 있습니다.

해결 방법: 분석 실행 전 입력 셀을 검토하고, 각 도구의 대화상자에서 선택된 항목을 재확인하며, 각 셀이 무엇을 의미하는지 분명히 알 수 있도록 설명 라벨을 사용하세요. 

3. 목표값 찾기의 한계 오해

많은 사용자가 목표값 찾기가 여러 변수를 동시에 바꾸는 문제를 해결해 주길 기대합니다. 그렇지 않습니다. 목표값 찾기는 목표값에 도달하기 위해 한 번에 하나의 입력 셀만 변경할 수 있습니다. 

예를 들어 목표값 찾기는 $15,000 이익을 내기 위한 판매량을 구할 수 있습니다. 그러나 판매량, 가격, 원가를 동시에 조정해 그 목표에 도달하게 하지는 못합니다.

해결 방법: 하나의 변수만 바꾸면 되는 경우에 목표값 찾기를 사용하세요. 여러 변수가 관련되면 시나리오 관리자나 Solver를 사용합니다. 

4. 지나치게 복잡한 시나리오 모델

시나리오 관리자는 기술적으로 시나리오당 최대 32개의 변경 셀을 허용하지만, 가능하다고 해서 반드시 그리해야 하는 것은 아닙니다. 

변수가 네다섯 개를 넘기면 시나리오를 비교하기 어렵고, 다른 사람에게 설명하기는 더 어려워집니다. 움직이는 부품이 너무 많다면 하나의 시나리오에 모두 욱여넣기보다 모델 구조를 재정비할 때일 수 있습니다.

마무리 생각 

모든 스프레드시트에는 어딘가에 가정이 있습니다. 성장률, 가격, 금리일 수 있습니다.

가정 분석은 이러한 가정을 없애주지 않습니다. 그 가정이 틀렸을 때 무슨 일이 일어나는지 보여줍니다. 좋은 의사결정의 대부분은 결국 여기에 달려 있습니다.

처음이라면 변수 다섯 개, 중첩 시나리오 세 개짜리 모델로 시작하지 마세요. 입력 하나, 출력 하나, 목표값 찾기 하나처럼 작은 것부터 시작해 입력과 결과가 함께 어떻게 움직이는지 익히세요. 기본이 이해되면 복잡한 작업은 나중에 해도 늦지 않습니다.