엑셀 작업을 하다보면 목표값은 정해져있는데 그 목표값을 만들기 위해서 입력값이 얼마가 되어야 할지 알아야하는 경우가 있습니다. 예를 들면, 목표 판매 이익을 달성하기 위해 필요한 판매량을 계산하거나, 예산 한도내에서 특정 비용을 얼마나 사용할 수 있을지 계산하는 상황 등이 있겠죠.
이럴 때 많은 숫자를 대입하거나 복잡한 수식을 걸 필요없이 엑셀의 기본 기능인 목표값 찾기(Goal Seek) 기능으로 많은 케이스에 해결이 가능합니다.
1. 목표값 찾기란?
2. 목표값 찾기 실행, 단축키
3. 목표값 찾기 Case study
1. 목표값 찾기란?
목표값 찾기는 수식의 결과값을 미리 정해두고, 그 결과값을 얻기 위해 필요한 입력값이 얼마인지 역으로 계산해주는 기능입니다. 역산을 해주는 기능으로 What-if 분석의 한 종류로 생각하시면 되겠습니다.
다양한 사례를 생각해볼 수 있지만 회사의 실무에서는 아무래도 예산을 다루는 부서에서 많이 사용하시지 않을까 싶습니다.
주어진 인건비 예산이 정해져 있는 경우에 인당 줄수 있는 급여를 구하거나 간단하게는 회식비 예산에서 메뉴를 고를때에도 사용할 수 있는 이래저래 유용한 기능이죠.
2. 목표값 찾기 실행, 단축키
엑셀의 꽃은 단축키죠. 목표값 찾기로 바로 가기 위한 단축키는 Alt + A +W + G 입니다.
Alt + A는 [데이터] 탭 → W는 [예측] 그룹의 [가상 분석] → G는 [목표값 찾기] 로 가는 경로라는 점을 기억해주시구요.
목표값 찾기를 열면 위와 같은 박스가 열리는데 세가지 값을 넣어야 합니다.
1. 수식 셀(E)에는 결과값이 적힌 셀을
2. 찾는 값(V)에는 결과값에 부여된 고정 금액을
3. 값을 바꿀 셀(C)에는 결과값을 얻기 위해 필요한 입력값을 쓰는 셀. 즉, 찾고 싶은 금액이 적힐 셀을 넣으면 됩니다.
3. 목표값 찾기 Case Study
다양한 활용사례가 있겠지만 예시를 들기 위해 가상의 상황을 아래와 같이 가정해봅니다.
① A사라는 택배회사에서 파트타임 직원들에게 지급할 급여 수준을 정하는 와중에
② 수요를 반영하여 월별로 필요한 직원 수가 정해진 상황에서
③ 급여로 쓸 수 있는 예산과 정규직 직원의 급여가 정해져 있을 때, 파트타임 직원들 앞 지급 가능한 급여를 구해봅니다.
A회사의 정규직 직원은 50명이면 매월 필요한 인원 중 50명은 정규직으로 채워지게 되겠죠.
위의 캡쳐화면에서 월별 필요 직원수가 빨간 박스라고 하면 설이나 추석, 크리스마스와 같이 수요가 많은 월에는 필요한 직원수가 평월보다 많습니다. 주어진 사례에서 회사는 100명으로 예측했네요.
현재 예산팀에서 고려하고 있는 급여 수준은 정규직 2,500,000원과 파트타임 2,100,000원입니다.
이는 작년의 예산을 고려하여 일괄적으로 정한 금액이라고 하네요. 예측된 인력 상황에서 위의 급여 수준을 고려하면 22억 1,400만원이 지급될 급여액이 되겠네요.
여기서 문제가 생깁니다. 예산팀은 회사가 가용할 수 있는 급여액이 20억원으로 줄어들었다는 사실을 통보받았고, 이 수준을 지키기 위해서 파트타임 직원들의 급여를 조정하라는 오더를 받게 되는데요.
급여팀의 김대리님은 변화하는 가용 급여액이 계속 바뀔 것을 대비하여 계산하기 위한 워크시트를 만들어두었죠.
이제 김대리님은 해찾기 기능을 통해 20억원의 총급여액을 맞출 수 있는 파트타임 금액을 계산합니다.
랜선 선배 블로그를 구독하는 김대리님은 엑셀에서 목표값 찾기 기능을 사용합니다.
수식셀에는 전체 급여 목표값이 정해져 있는 I26셀을 기재하고, 찾는 값은 주어진 예산인 20억원을, 값을 바꿀 셀에는 파트타임 인당 급여를 의미하는 H25셀을 기재하고 확인을 누르면,
아래와 같이 목표값을 20억원으로 한 값이 찾아집니다.
I26셀은 20억원으로 바뀌면서 찾고 싶었던 금액인 파트타임 1인당 월급여는 H25셀에서 1,470,588원으로 변경이 되었네요.
이렇게 원하는 값을 찾을 수 있고, 20억원에서 급여 예산이 바뀌는 경우에는 위에서 찾는 값을 그에 맞게 계속 변동하기만 하면 바로바로 금액을 찾을 수 있습니다.
엑셀은 일잘러로 가는 확실하고 편한 프로그램인 것 같습니다. 목표값 찾기 기능도 적극적으로 써보시길 바래요.