엑셀 목표값 찾기, 시나리오 관리자

목표값 찾기

엑셀에서 목표값 찾기 기능은 내가 원하는 결과 값이 있는데 이 결과 값에 도달하려면 어떤 값이 입력되어야 하는 지를 찾 아주는 함수입니다. 예를 들어 1년 동안 1억원의 판매금액이 원하는 결과값인데 이 1억원의 판매량을 달성하려면 컴퓨터 를 몇대를 팔아야 하는 지를 알려준다는지, 보험 사원의 경우 보험 계약을 몇건을 성사시켜야 1억원어치의 계약을 달성할 수 있는 지 등을 알려주게 됩니다.

그럼 사용법에 대해 단계별로 알아보도록 하겠습니다.

 

엑셀에 위와 같은 데이터가 들어 있다고 가정을 하겠습니다. 여기에서 만약 1억원의 판매 금액을 달성하려면 수량을 몇대 팔아야 되는 지를 계산하기 위해 목표값 찾기를 이용해 보도록 하겠습니다.

 

목표값 찾기는 데이터 - 가상분석을 클릭하면 나옵니다.

 

 

목표값 찾기를 클릭했습니다. 그러면 수식 셀, 찾는 값, 값을 바꿀 셀에 데이터를 입력해야 하는 데

1. 수식 셀은 금액이 입력되어 있는 셀을 클릭합니다. 목표로 구하고자 하는 값이 들어있는 셀이 수식 셀 입니다.

2. 찾는 값은 1억원을 입력합니다. 실제 내가 구하려고 하는 값을 입력하면 됩니다.

3. 값을 바꿀 셀은 1억원 목표를 달성하려면 수량을 몇대 팔아야 하는 지를 알고 싶은 것이므로 수량이 들어 있는 셀을 클 릭해주면 됩니다. 만약 수량이 고정적이고, 단가를 바꾸어서 목표값을 찾고 싶으면 값을 바꿀 셀은 단가가 입력되어 있는 셀을 클릭해 주면 됩니다.

 

 

값을 모두 입력한 후에 확인을 누르면 위와 같이 결과 값이 나오고, 기존에 입력된 데이터도 목표값 찾기의 결과값에 해당 되는 값으로 변경되어 나오게 됩니다.

 

시나리오 관리자

엑셀의 시나리오 관리자는 가상의 예측을 해서 변경되는 값을 확인해보는 기능입니다. 예를 들어 물가가 상승하는 데 따른 가계지출의 변동 금액을 예상한다든지, 은행 대출이 있는데 연이율은 고정되어 있는데 대출 기간을 1년,2년,3년 등 대출

 

기간이 바뀌면 월 상환금액이 얼마나 되는 지를 계산하는 등 가상의 시나리오를 생각해서, 기준치의 값이 어떻게 변하는 지를 예측할 수 있습니다.

그러면 시나리오 관리자를 사용하는 법에 대해 하나씩 알아보도록 하겠습니다.

 

시나리오 관리자로 예측해볼 내용은 대출 원금이 백만원이고 연이율은 6%, 대출 기간이 24개월인데 이 대출 기간을 변 경할 시 월 상환액은 얼마가 되는 지를 예측해보는 내용입니다.

월 상환액의 계산은 PMT 함수를 사용해서 계산식이 입력되어 있습니다.

PMT 함수는 정기적으로 지불하고 일정한 이율이 적용되는 대출금의 월 상환액을 계산해 주는 함수입니다.

PMT(rate, nper, pv, fv, type) 와 같이 사용되는 함수인데

rate: 연이율입니다. 월 상환액이 되어야 하므로 B2/12 를 해줍니다.

nper: 대출금의 총 상환 횟수입니다. 24번 상환으로 지정되어 있는 기간이 입력되어 있는 B3를 선택합니다. pv: 원금을 선택합니다. 원금이 입력되어 있는 B1을 선택합니다.

fv: 미래가치를 나타내는 데 대출금의 미래가치는 0 이 되므로 그냥 숫자 0을 입력해 주면 됩니다. type: 납입 시점을 나타내는 데 그냥 생략하면 됩니다.

 

시나리오 관리자를 사용하려면 위의 화면에 나와 있듯이 데이터 - 가상분석 - 시나리오 관리자를 선택하면 됩니다. 그리고 B5셀을 선택하고 이름을 월 상환액으로 지정해 줍니다. 꼭 이름을 정의해주어야 하는 건 아닙니다.

그냥 이름 정의의 사용법도 설명해 드릴 겸 만들어 보았습니다.

 

하나의 셀이나 선택 범위의 이름을 정의해 주려면 위와 같이 이름 정의를 클릭합니다.

 

 

이름 정의 대화상자에서 위와 같이 이름을 만들어 줍니다. 그러면 이름 상자를 이용해서 어떤 곳에서도 쉽게 정의된 이름 이 있는 곳으로 이동이 가능하게 됩니다.

 

 

시나리오 관리자를 누르면 위처럼 정의된 시나리오가 없다고 나옵니다. 여기에서 추가버튼을 클릭합니다.

 

 

시나리오 추가 대화상자에서 시나리오 이름을 적당히 지정해 줍니다. 3년이라고 지정했습니다. 변경 셀은 위와 같이 기간을 변경해야 되므로 B3 셀을 지정하고 설명도 입력해 줍니다.

 

 

그러면 위와 같이 시나리오 값을 입력하는 화면이 나오는데 3년으로 했을 경우 월 상환액을 계산해야 하므로 36을 입력해 줍니다.

 

 

그러면 다시 시나리오 관리자 화면으로 바뀌는데 여기에서 계속 시나리오를 똑같은 방법으로 추가해 주면 됩니다.

 

필자는 3년, 5년, 1년의 경우를 추가해 주었습니다. 원하는 만큼 추가를 다 해주었다면 요약 버튼을 클릭합니다.

 

 

마지막으로 시나리오 요약에서 결과 셀을 지정해 주어야 하는데 납입 기간에 따른 월 상환액을 알기 위해서 시나리오 관리 자를 사용하는 것이므로 결과 셀은 월 상환액이 입력되어 있는 B5 셀을 선택해 줍니다.

 

 

그러면 위와 같이 시나리오 요약 결과가 새로운 시트가 생성되면서 나타나게 됩니다.

이렇게 시나리오를 만들어 놓은 것은 이후에 시나리오를 추가하거나 삭제, 수정이 가능하므로 얼마든지 다른 값으로 변경 해서 사용할 수 있습니다.