엑셀 목표값 찾기 / 해찾기 기능 사용법 및 분석예제

목표값 찾기는 '하나의 변수를 바꿔 목표값을 찾을 때 사용'합니다. 예를들어, 특정 매출을 달성하기 위한 판매량을 계산하거나 영업이익 5%를 높이기 위한 판매원가등을 계산할 때 사용합니다. 변수를 하나만 설정하면 되기 때문에 해찾기 기능보다 쉽게 사용할 수 있고, 기본적으로 엑셀에 내장된 기능이므로 별도의 추가기능을 설치하지 않아도 어느 버전에서나 사용할 수 있다는 장점이 있습니다.

제한사항

목표값 찾기는 변수와 목표값이 '선형'으로 변할 때만 사용가능하다는 제한사항이 있으므로 주의해야 합니다. 또한 변수의 다양한 옵션(예: 변수는 정수여야만 하거나 변수는 0보다 커야 하거나 등..)의 다양한 설정이 불가능합니다.

시나리오 분석

시나리오 분석은 '여러 변수를 바꾸며 다양한 시나리오의 결과를 계산하고 예측할 때 사용'합니다. 예를들어 '판매량이 10% 증가하고 가격이 그대로일 경우, 판매량이 5% 증가하고 가격이 5% 늘어날 경우, 판매량이 그대로이고 가격이 10% 증가할 경우' 등으로 나누어 각 상황별 결과를 예측할 수 있습니다.

제한사항

시나리오 분석은 '특정 결과를 만족하는 조건'을 계산할 때에는 사용할 수 없다는 제한사항이 있습니다.

해찾기 기능

해찾기 기능은 'Solver'라고 알려진 엑셀에서 기본으로 제공되는 추가기능입니다. 해찾기 기능을 사용하면 여러 변수를 변경하여 특정 조건을 만족하는 결과를 계산할 수 있습니다. 예를들어 '매출액이 5%~10% 사이로 증가하고 인건비를 10%~15% 높였을 때 영업이익을 그대로 유지하려면 매출액과 인건비가 서로 몇 %씩 증가해야 하는지' 등을 계산할 수 있습니다.

제한사항

해찾기 분석의 결과값은 수학적으로 계산된 결과이고, 현실에서 고려해야 하는 여러가지 조건은 계산에 반영되지 않는다는 점을 주의해야 합니다.

목표값 찾기 제한사항

목표값 찾기는 비선형 자료에서는 사용할 수 없습니다. 비선형 자료란, 2차 이상의 방정식, 즉 x축이 증가할 때 y축이 계속 증가하거나 감소하는 것이 아닌 y축 값이 증가/감소를 반복하는 경우를 이야기합니다.

예를들어 아래 상황을 가정하겠습니다. 시작값이 0이고, y 축 값이 4000인 경우를 계산하다고 가정할 경우 목표값 찾기는 기준값인 0에서 값이 증가하는 방향인 음의방향으로 목표값 찾기를 시작합니다.

목표값 찾기 음의방향 — 기준값인 0 부터 음의 방향으로 목표값 찾기를 시작합니다.

음의방향으로 계속 이동할 경우 극대값으로 1041 (x축이 -9.51일 경우) 을 찍은 뒤 음의 무한대로 값이 계속 감소합니다. 따라서 목표값 찾기는 변수가 -9.51 일 때, 결과값으로 1041.914649이 계산되고 우리가 구하고자하는 4000은 찾지 못했다는 결과를 반환합니다.

목표값 찾기 틀린 결과 — 목표값 찾기로 원하는 값을 찾을 수 없다는 안내메시지가 출력됩니다.

따라서, 자료가 비선형일 때에는 해찾기 기능을 Evolutionary 해법으로 사용하여 해결합니다.

해찾기 추가기능 설치방법

[파일] - [옵션] - [추가기능] 에서 관리에 [Excel 추가 기능]을 선택한 뒤, [이동] 버튼을 클릭합니다.
파일 - 옵션 - 추가기능 에서 Excel 추가기능으로 이동합니다.
[해 찾기 추가 기능]을 선택한 뒤 [확인] 버튼을 눌러 추가기능 설치를 마무리합니다.

해찾기 추가기능을 선택한 뒤, [확인] 버튼을 클릭합니다.
[데이터] 탭으로 이동하면 우측에 [해 찾기] 추가기능이 설치되었습니다.
해찾기 추가기능 설치가 완료되었습니다.

엑셀 목표값 찾기 실전예제

목표값 찾기는 실무의 다양한 분야에 적용할 수 있는 아주 유용한 기능입니다. 간단한 예제를 살펴보겠습니다.

아래 일일 손익예상을 보면 판매갯수와 평균원가, 그리고 각 고정지출(인건비, 임대로, 기타잡비 등..)로 영업이익을 계산하고 있습니다. 그리고 물가상승으로 인해 제품원가가 1,450원에서 1,550원으로 증가한다고 가정하겠습니다. 그럴 경우, 현재 영업이익인 138,000원을 그대로 유지하려면 하루 몇잔을 팔아야 할까요?

엑셀 목표값찾기 예제 설명 — 원가가 1,550원으로 증가할 경우 영업이익을 그대로 유지하려면?

그냥 계산한다면 상당히 복잡한 계산이 이루어 질 것 같지만, 목표값 찾기를 이용하면 아주 쉽게 계산할 수 있습니다.

예제파일의 [1] 시트로 이동합니다. 평균원가를 1,550으로 변경합니다.
평균 원가를 1,550원으로 변경합니다.
[데이터] - [가상분석] - [목표값 찾기]를 선택한 뒤 목표값 찾기로 아래와 같이 입력합니다.
목표값 찾기 설정을 아래와 같이 입력합니다.

수식셀 : $D$20 (=영업이익)
찾는값 : 138,000
값을 바꿀 셀 : $D$6 (=판매갯수)
[확인] 버튼을 누르면 계산이 시작되고, 판매갯수가 약 262잔 일 때 영업이익이 138,000원으로 계산되는 것을 확인할 수 있습니다. [확인] 버튼을 누르면 계산된 결과로 시트가 업데이트 되고, [취소]버튼을 누르면 기존값으로 복구뒵니다.
엑셀 목표값 찾기 계산이 완료되었습니다.

엑셀 시나리오분석 실전예제

시나리오 분석은 특정 목표를 위한 결과 값을 계산하는 것 대신 여러가지 상황별로 다양한 결과를 계산하고 예측하는 작업에 사용됩니다. 아래 간단한 실전예제를 통해 시나리오 분석 기능을 살펴보겠습니다.

상황 :
새로운 매장자리가 나서 이사를 고민중입니다. 공사비용은 1억이 예상 (공사비로 1억이 증가되며 감가상각은 5년) 되며, 신규 매장의 월세는 300만원으로 지금보다 100만원 증가합니다.
그럴 경우, 현재상황 vs 신규매장에서 각 250잔/300잔/350잔을 판매할 경우를 비교하여 시나리오 예측을 해보겠습니다.

예제파일의 [1] 시트로 이동합니다. 우리가 변경해야 할 조건은 '판매갯수, 공사비, 월세' 3가지입니다. 따라서 키보드 Ctrl 키를 누른채로 각 판매갯수, 월세, 공사비 셀을 선택합니다. 이후 [데이터] - [가장분석] - [시나리오 관리자] 로 이동한 뒤, [추가] 버튼을 눌러 시나리오를 생성합니다.
시나리오 분석으로 변경할 셀을 선택한 뒤, 시나리오 관리자를 실행합니다.
시나리오 이름으로 '250' 을 입력한 뒤 [확인]을 눌러 첫번째 시나리오를 추가합니다.

시나리오 이름으로 '250'을 입력한 뒤, [확인]을 클릭합니다.
첫번째 시나리오는 신규매장일 경우, 즉 월세 300만원 / 공사비 3억일 경우 판매잔수가 250잔일 경우입니다. 따라서 시나리오 값을 아래와 같이 설정합니다.
시나리오 값을 설정합니다.

$D$6 : 250
$G$11 : 3000000
$G$13 : 300000000
값을 입력한 뒤 [추가] 버튼을 눌러 두번째, 세번째 시나리오를 추가합니다.
두번째 시나리오 이름 : 300
$D$6 : 300
$G$11 : 3000000
$G$13 : 300000000

세번째 시나리오 이름 : 350
$D$6 : 350
$G$11 : 3000000
$G$13 : 300000000
모든 시나리오가 추가되었으면 [요약]버튼을 눌러 요약보고서를 생성합니다.

시나리오가 모두 추가되었으면, [요약] 버튼으로 요약보고서를 생성합니다.
요약보고서로 생성할 결과 셀은 영업이익과 영업이익률이므로 결과셀로 D20:E20 범위를 선택한 뒤, [확인] 을 눌러 마무리합니다.
시나리오 결과셀로 '영업이익/영업이익률'을 선택합니다.
요약보고서가 생성되었습니다. 불필요한 부분은 숨겨주고 각 셀의 머릿글을 수정하여 요약보고서를 완성합니다.
시나리오 요약 보고서를 완성합니다.

엑셀 해찾기 기능 실전 예제

예제파일의 [3] 시트로 이동합니다. 이번에는 여러가지 조건이 복잡하게 얽혀있는 상황에서 해찾기 기능으로 최고의 영업이익을 달성하기 위한 판매개수를 계산해보겠습니다. 계산시 고려해야 할 조건은 아래와 같습니다.

갑작스런 코로나 사태로 인해 일평균 커피 판매량이 80잔 이하로 줄어, 마케팅 업체를 고용하여 매출을 높이고자 합니다.
마케팅 업체에서 제안하기를, 주변 사무실 업체와 계약하여 일평균 k잔 판매를 보장하는 대신, 해당 판매건에는 x%의 할인율을 제공하고, 특정 판매잔수 달성시 일정금액(y)의 수수료를 지급해달라고 합니다.
판매잔수가 100잔 늘어날 때마다 직원을 1명씩 더 고용해야 합니다.
매출이 늘어나면 납부해야 할 가맹비가 늘어납니다.
하루 판매가능한 최대 잔수는 350잔 입니다.

조건을 보시다싶이, 판매잔수가 늘어날 경우 직원 고용에 대한 인건비 증가 및 납부해야 할 가맹비 증가로 인한 지출을 동시에 고려하여 최고의 영업이익을 내기 위한 판매잔수를 계산해야 하는데요. 계산에 사용되는 조건만 잘 설정한다면 해찾기 기능으로 손쉽게 계산할 수 있습니다.

예제파일의 [3] 시트로 이동한 뒤, [데이터] - [해찾기] 기능을 선택합니다. 보다 빠른 진행을 위해 해찾기에 사용된 변수를 예제파일에 미리 입력해 드렸는데요. 각 설정된 변수를 하나씩 살펴보겠습니다.
엑셀 해찾기 기능을 실행합니다.
목표설정으로는 $D$22셀 (=영업이익)을 선택합니다. 대상으로는 영업이익의 '최대값'을 구합니다.
목표셀로 영업이익을, 대상으로는 영업이익의 최대값을 계산합니다.
변수셀은 $D$6셀 (=판매갯수)를 변경하여 영업이익의 최대값을 계산합니다.
변수셀로 '판매갯수' 셀을 지정합니다.
제한조건에 종속되는 조건으로는 3개를 입력합니다.
변수 조건을 설정합니다.

1. $D$6 <= 350 : 판매잔수는 350잔 이하여야 합니다.
2. $D$6 = 정수 : 판매잔수는 정수여야 합니다.
3. $D$6 >=0 : 판매잔수는 양수여야 합니다.
해법으로는 Evolutionary를 선택합니다. 판매갯수가 증가할 때 영업이익이 불규칙하게 변하는 비선형자료이므로 해법으로는 반드시 Evolutionary를 선택해야 합니다.
해법으로 Evolutionary를 선택합니다. (비선형 데이터)
해법의 [옵션] 으로 이동한 뒤, 옵션에서 '최대시간'을 5초로 설정합니다. 해법을 Evolutionary로 설정하면 모든 조건을 하나씩 돌아가며 계산하므로 계산시간이 10분 넘게 소요되는 경우도 많으므로 최대시간을 약 5초~30초 사이로 설정합니다.
(정확한 값을 찾는 대신 실무에서 타협가능한 근사치를 찾는 것을 목표로 사용하는 것이 좋습니다.)

해찾기 옵션으로 최대시간을 5초~30초 사이로 설정합니다.
각 판매갯수별 할인율, 수수료 및 가맹비용, 그리고 인거비는 어디에서 계산되었을까요? 예제파일의 노란색으로 채워진 셀을 보시면 각 판매갯수에 따라 할인율, 수수료 및 마케팅비가 자동으로 계산되도록 수식이 입력되어 있습니다. 그리고 각 지출을 반영하여 영업이익이 계산됩니다.
나머지 조건은 시트 안에서 영업이익 계산에 자동으로 반영됩니다.
[해찾기] 버튼을 클릭하면 계산이 시작되며, 옵션에서 설정한 최대시간인 5초가 지나면 중간 해 표시 안내창이 나타납니다. [정지] 버튼을 눌러 계산을 중단합니다.

중간 해 표시 안내창이 나오면 [정지]를 선택합니다.
계산이 중단되며 시트가 업데이트 되고, 판매갯수가 199잔일 때 영업익이 19,488원으로 최대값이 되는 것을 확인할 수 있습니다. [확인]버튼을 누르면 계산된 결과로 시트가 업데이트되며, [취소] 버튼을 누르면 기존 값으로 복구됩니다.
해찾기 계산이 완료되었습니다.