실무에서 가장 많이 사용되는 엑셀 - ⅹ

 

엑셀 실무에서 가장 많이 사용되는 함수와 기능: 열 번째 글

이번 마지막 글에서는 What-If 분석과 시뮬레이션 도구를 활용하여 데이터를 기반으로 다양한 시나리오를 예측하고 의사결정을 지원하는 방법을 다룹니다. What-If 분석은 실무에서 재무 계획, 프로젝트 예산, 목표 설정 등 다양한 상황에 유용하며, 엑셀의 강력한 기능 중 하나입니다. 이번 글을 통해 엑셀의 고급 분석 도구를 마스터해보세요.

반응형

1. What-If 분석이란?

What-If 분석은 "만약 어떤 값이 변경된다면 결과는 어떻게 될까?"를 탐구하는 도구입니다. 엑셀에서는 시나리오 관리자, 데이터 테이블, 그리고 목표값 찾기(Goal Seek) 기능을 통해 다양한 가정을 설정하고 결과를 예측할 수 있습니다.

2. 목표값 찾기(Goal Seek)

목표값 찾기는 특정 결과를 얻기 위해 입력값을 자동으로 계산하는 기능입니다.

사용법:

1. 상단 메뉴에서 데이터 > What-If 분석 > 목표값 찾기를 클릭합니다.
2. 목표로 설정할 셀(결과 셀)을 선택합니다.
3. 원하는 목표값을 입력합니다.
4. 목표값을 달성하기 위해 변경할 셀(입력값)을 지정합니다.
5. 확인을 누르면 엑셀이 자동으로 계산합니다.

예시:

• 상황: 월 상환액이 $1,200일 때 대출 금액을 계산.
  

  이자율	기간(개월)	대출 금액	월 상환액
  4%	360	?	$1,200

   

1. 공식: =PMT(4%/12,360,-A3) (A3는 대출 금액)
2. 목표값 찾기 설정:
   • 목표 셀: 월 상환액 셀
   • 목표값: $1,200
   • 변경할 셀: 대출 금액 셀
3. 결과: 대출 금액은 약 $250,000.

728x90

3. 시나리오 관리자(Scenario Manager)

시나리오 관리자는 여러 입력값에 따른 결과를 비교하고 분석할 수 있는 도구입니다.

사용법:

1. 상단 메뉴에서 데이터 > What-If 분석 > 시나리오 관리자를 클릭합니다.
2. "새 시나리오"를 추가하고 변경할 셀과 값을 입력합니다.
3. 여러 시나리오를 추가한 후 결과를 비교하거나 요약 보고서를 생성합니다.

예시:

• 상황: 제품 가격과 판매량에 따라 총 매출을 비교.
  

  제품 가격	판매량	총 매출
  $100	500	?
  $120	450	?

   

1. 기본 시나리오:
   • 제품 가격: $100
   • 판매량: 500
2. 새로운 시나리오:
   • 제품 가격: $120
   • 판매량: 450
3. 결과 비교:
   • 기본 시나리오 총 매출: $50,000
   • 새로운 시나리오 총 매출: $54,000

4. 데이터 테이블(Data Table)

데이터 테이블은 여러 입력값에 따른 결과를 한눈에 확인할 수 있는 도구입니다.

사용법:

1. 데이터 테이블의 행 및 열에 변경할 값을 입력합니다.
2. 결과 셀에 수식을 작성합니다.
3. 상단 메뉴에서 데이터 > What-If 분석 > 데이터 테이블을 클릭합니다.
4. 행 입력 셀과 열 입력 셀을 지정한 후 확인합니다.

예시:

• 상황: 이자율과 대출 기간에 따른 월 상환액을 계산.
  

  이자율(%)	기간(개월)
  3	360
  4	240

   

1. 수식 작성: =PMT(A1/12,B1,-250000)
2. 데이터 테이블 설정:
   • 행 입력 셀: 이자율
   • 열 입력 셀: 기간
3. 결과:
   

   이자율(%)	기간(240개월)	기간(360개월)
   3	$1,386	$1,054
   4	$1,503	$1,193

    

SMALL

5. Solver 추가 기능

Solver는 최적화 문제를 해결하는 고급 도구로, 특정 조건하에서 최대 또는 최소 값을 찾습니다.

사용법:

1. 파일 > 옵션 > 추가 기능 > Solver 추가 기능 활성화.
2. 상단 메뉴에서 Solver 클릭 후 문제 설정:
   • 목표 셀 지정 (최대화 또는 최소화).
   • 변경 가능한 셀 지정.
   • 제약 조건 추가.
3. "해 해결" 버튼 클릭.

예시:

• 상황: 예산($10,000) 내에서 최대 매출을 달성하기 위한 생산량 결정.
  • 제품 A 생산 비용: $50/unit
  • 제품 B 생산 비용: $30/unit
  • 제품 A 매출 단위당 수익: $70/unit
  • 제품 B 매출 단위당 수익: $50/unit

Solver 설정:

• 목표 셀: 총 매출 (최대화)
• 변경 가능한 셀: 제품 A와 B 생산량
• 제약 조건:
  • 50*A + 30*B <=10,000 (예산 제한)

결과:

• 제품 A 생산량 = 100 units
• 제품 B 생산량 = 약 133 units