들어가기 전에
Excel의 목표값 찾기 기능은 CVP(Cost Volume Profit) 분석 같은 재무 계획의 역방향 계산에 도움이 됩니다. 목표값 찾기를 사용하면 역 계산을 피하고 Excel에서 원하는 출력을 가져오는 입력을 찾을 수 있습니다. Excel에서 목표값 찾기를 활용하여 재무 분석하는 몇 가지 예제를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How Excel's Goal Seek Makes Any Financial Analysis a Breeze
- URL: https://www.makeuseof.com/excel-goal-seek-makes-financial-analysis-easy/
1. 비용 볼륨 이익 분석
비용 볼륨 이익(CVP) 또는 손익분기점 분석은 볼륨 또는 비용의 변화가 회사 이익에 미치는 영향을 분석하는 기술입니다. Excel의 목표값 찾기 기능을 사용하면 CVP 분석을 훨씬 더 효율적으로 수행할 수 있습니다.
간단한 예를 들어 설명하자면, 손익분기점에 도달하기 위해 얼마나 많은 단위를 판매해야 하는지 계산하고 싶다고 가정해 보겠습니다. 판매 가격, 가변 비용, 고정 비용을 보여주는 간단한 명세서를 만드는 것으로 시작합니다. 지금은 단위에 대한 가정 수치를 입력하고 기여 마진과 이익을 계산할 수 있습니다.
그런 다음 [데이터] 탭- [예측] 그룹 - [목표값 찾기]를 선택합니다. 세 개의 매개변수를 입력하라는 대화 상자가 나타납니다.
- 수식 셀: 여기에 목표 값이 있는 출력을 포함하는 셀 주소를 입력합니다.
- 찾는 값: 목표값을 입력합니다. 손익분기점의 경우 0으로 설정해야 합니다.
- 값을 바꿀 셀: 목표 출력에 도달하기 위해 변경하려는 셀입니다. 손익분기점에 도달하는 단위를 찾고 있으므로 이는 단위 수가 포함된 셀이 됩니다.
매개변수를 입력한 후 확인을 클릭하면 Excel에서 값을 변경하여 손익분기점에 도달하기 위해 판매해야 하는 단위 수를 알려줍니다.
5,000달러의 수익을 내기 위해 얼마나 많은 단위를 판매해야 하는지 알고 싶다면 목표값 찾기 기능을 다시 사용하여 '찾는 값'을 0 대신 5,000으로 설정합니다. 또는 목표 이익(단위는 일정하게 유지)에 도달하기 위한 판매 가격이나 가변 비용을 찾고 싶다면 셀 매개변수를 변경하여 판매 가격/비용 셀을 참조할 수 있습니다.
2. 순현재가치 분석
순현재가치(NPV: Net Present Value) 분석은 특정 프로젝트나 사업 결정을 내릴지 여부를 결정하는 데 사용되는 투자 평가 기법입니다. NPV 분석을 사용할 때 Goal Seek는 여러 계산에 유용할 수 있습니다. 예를 들어, 이를 사용하면 프로젝트에서 특정 NPV를 달성하기 위해 제품을 판매해야 하는 가격을 찾을 수 있습니다.
그렇게 하려면 모든 예상 비용과 가정 판매 가격을 사용하여 NPV 모델을 구축합니다. 그러면 가정한 판매 가격을 기반으로 NPV가 제공됩니다. 이제 Goal Seek를 사용하여 NPV가 포함된 셀을 Set cell 로 선택하고 To value 필드 에 목표 NPV(예: $1,000,000)를 입력하고 By changing cell 필드에 판매 가격이 포함된 셀 번호를 입력합니다 . Voilà! Excel에서 목표 NPV를 달성하기 위해 얼마를 청구해야 하는지 알려줍니다.
판매 가격 외에도 단위나 자본 비용/할인율 등의 다른 입력 항목을 변경할 수도 있습니다.
3. 비율 분석
은행에서 대출을 받았고 대출 조건에 따라 이자 보장 2.5, 신속 비율 1, 부채 대 자본 비율 0.5를 유지해야 합니다. 여기에서 Goal Seek은 계약 위반 없이 이자 비용, 현금 또는 부채가 얼마나 변경될 수 있는지 알아내는 데 도움을 줄 수 있습니다.
예를 들어, 재무제표를 Excel로 가져와서 신속 비율을 계산합니다. 그런 다음 Goal Seek를 사용하여 셀 설정을 신속 비율이 포함된 셀로 설정하고 To 값을 1(또는 목표 비율)로 유지하고 Excel에 무역 채무를 변경하여 비율을 조정하도록 요청합니다. Goal Seek는 비율을 한도 내로 유지하면서 무역 채무가 얼마나 증가할 수 있는지 알려줍니다.
4. 예산 책정
내년 예산을 세우는 동안 예상 수수료 비용이 상당히 높다는 것을 깨달았습니다. 비용을 목표 금액(예: $50,000)으로 줄이려면 Goal Seek를 사용하여 영업 담당자에게 제공해야 할 수수료 비율을 찾을 수 있습니다.
기존 커미션 비율과 판매 수익을 기준으로 예상 비용을 계산합니다. 여기의 Set 셀은 커미션 비용을 포함하는 셀이고, To 값은 50,000이고, By changing 셀은 커미션 비율을 포함하는 셀입니다. [확인]을 클릭하면 수수료 비율을 계산하여 예산 비용을 50,000달러로 줄여줍니다.
5. 대출 상환
목표값 찾기는 개인 재정에도 유용하게 사용할 수 있습니다. 대출을 받고 싶지만 월 할부금은 1,500달러만 납부할 수 있다고 가정해 봅시다. PMT 기능과 목표 찾기를 사용하면 고정 이자율로 얼마를 빌릴 수 있는지 알아볼 수 있습니다.
이렇게 하려면 먼저 PMT 기능을 사용하여 원하는 금액을 원금으로 가정하여 대출 상환액을 계산합니다. 그런 다음 다음 매개 변수와 함께 목표 찾기를 사용합니다.
- 셀을 월 할부금이 포함된 셀로 설정합니다.
- 값을 지불할 수 있는 월별 할부 금액으로 설정합니다.
- 셀을 대출 금액이 있는 셀로 변경합니다.
6. 은퇴 계획
은퇴를 계획하고 은퇴 후 30년 동안 매달 7,000달러를 인출하려는 경우, 목표값 찾기를 사용하여 은퇴 전에 매달 얼마를 기여해야 하는지 알아낼 수 있습니다. Excel에 다음 정보를 입력합니다.
- 가정된 월별 기여 금액
- 은퇴까지 남은 개월 수 (즉, 기여 횟수)
- 인출 횟수(즉, 은퇴 후 돈을 인출하려는 달)
그런 다음 FV 함수를 사용하여 Excel에서 미래 가치를 계산합니다.
=FV(interest rate/12,months till retirement,assumed monthly contribution)
이것은 은퇴 연령에 저축의 미래 가치를 알려줍니다. 이제 PMT를 사용하여 월 인출 금액을 찾습니다.
=PMT(interest rate/12,number of monthly withdrawals post-retirement,-future value of savings)
이제 기본 계산이 끝났으므로 목표값 찾기를 사용할 수 있습니다.
- 셀 필드에서 월별 인출 금액이 포함된 셀을 선택합니다.
- To 값은 7,000달러(또는 매달 인출하고자 하는 금액)가 됩니다.
- 셀 변경 필드에는 월별 기부금이 포함된 셀을 언급해야 합니다.
Excel은 은퇴 후 7,000달러를 인출하기 위해 지금 당장 은퇴를 위해 얼마를 따로 마련해야 하는지 결과를 알려줍니다.
마치며
목표값 찾기 및 Excel에서 사용 가능한 다른 재무 함수를 사용하면 일상적인 재무 분석 작업을 훨씬 더 간단하고 효율적으로 처리할 수 있습니다. 계획 및 분석 외에도 Excel을 사용하여 재무 제표를 준비할 수도 있습니다
'Excel' 카테고리의 다른 글
| 데이터 시각화를 위한 10가지 Excel 차트 유형 (12) | 2024.11.14 |
|---|---|
| Excel의 확인란 기능을 활용하는 5가지 방법 (4) | 2024.11.13 |
| 엑셀에서 파이썬 사용하는 법 (5) | 2024.11.11 |
| Excel로 캘린더를 만드는 3가지 방법 (2) | 2024.11.10 |
| Excel 표에서 데이터를 사용하는 3가지 방법 (3) | 2024.11.09 |
