Master Excel의 What-If 분석:비즈니스 시나리오를 예측하고 모델링하는 3가지 입증된 기술

What-if 분석은 중요한 결정을 내리기 전에 불확실성을 탐색하고 다양한 시나리오를 테스트하는 강력한 접근 방식입니다. “가격이 오르면 어떻게 될까요?”와 같은 비즈니스 또는 연구 결과를 달성하는 데 도움이 됩니다. 또는 “비용이 감소하면 얼마나 많은 이익을 얻을 수 있나요?” Excel의 What-If 분석 도구를 사용하면 수식을 다시 작성하지 않고도 다양한 가능성을 탐색할 수 있습니다.

이 튜토리얼에서는 Excel에서 시나리오를 예측하고 모델링하는 세 가지 What-If 분석 기술을 보여줍니다.

세 가지 필수 What-If 분석 기술을 살펴보겠습니다.

• 목표 탐색: 특정 결과에 필요한 입력을 찾기 위해 뒤로 작업
• 데이터 테이블: 하나 또는 두 개의 변수가 결과에 어떤 영향을 미치는지 분석하세요
• 시나리오 관리자: 여러 가정 세트 저장 및 비교

1. 목표 탐색:원하는 결과에 대한 입력 찾기

Goal Seek은 리버스 엔지니어링 모델에 이상적입니다. 특정 출력을 달성하기 위해 단일 입력 값을 조정하여 "원하는 결과를 얻으려면 어떤 입력 값이 필요합니까?"와 같은 질문에 답하는 데 도움이 됩니다.

Goal Seek은 반복을 사용하여 수식 종속 셀(출력)이 원하는 값에 도달할 때까지 하나의 셀(입력)을 변경합니다. 간단한 최적화에 적합하지만 하나의 변수로 제한됩니다.

목표 탐색을 사용하는 경우:

• 이익 목표를 달성하는 데 필요한 판매량 결정
• 대출 지불 제약 조건을 충족하는 데 필요한 이자율 계산
• 제품이나 서비스의 손익분기점 찾기
• 특정 성적을 달성하기 위해 최종 시험에 필요한 점수를 식별

"20,000달러의 이익을 얻으려면 몇 개를 판매해야 합니까?"에 대해 알아봅시다.

단계:

• Excel에서 모델 설정(예:제품 세부정보 및 수익 공식 입력)
• 다음 수식을 입력하여 수익을 계산하세요

• 데이터로 이동 탭>> 가정 분석 선택>> 목표 탐색을 선택하세요.

• 대화상자에서:
  • 셀 설정: 출력 셀을 선택하세요(예:이익 공식 E2)
  • 대상 값: 목표 출력을 입력하세요(예:목표 이익 20000)
  • 셀 변경: 조정할 입력 셀을 선택합니다(예:B2) , 판매 수량)
• 확인을 클릭하세요.

• Excel은 목표 이익이 달성될 때까지 판매 가치를 자동으로 반복하여 조정합니다.

통계: 공식이 알려진 목표(예:손익분기점 또는 목표 매출)에서 역방향으로 작동하는 경우 목표 탐색을 사용하세요.

• 장점: 단일 변수 문제에 대한 빠르고 간단한
• 단점: 여러 변수를 처리하지 않습니다. 모델이 복잡하면 수렴하지 않을 수 있습니다

2. 데이터 테이블:하나 또는 두 개의 변수가 결과에 미치는 영향 확인

데이터 테이블을 사용하면 하나 또는 두 개의 입력 변수를 변경하면 결과에 어떤 영향을 미치는지 확인하고 모든 가능성을 단일 테이블에 표시할 수 있습니다. 이를 통해 잠재적인 결과의 범위를 보여주는 민감도 분석이 생성됩니다.

• 변수가 하나인 표: 행이나 열에 걸쳐 하나의 입력을 변경합니다. 단일 입력에 대해 서로 다른 값을 테스트합니다
• 변수가 2개인 표: 두 개의 입력(행 1개, 열 1개)을 변경하여 행렬을 만듭니다. 각 조합에 대한 모델을 다시 계산하고 추세를 시각화하는 데 도움이 됩니다.

변수가 하나인 데이터 테이블

단가가 이익에 어떤 영향을 미치는지 살펴보겠습니다. 단가가 40달러에서 70달러로 오르면 이익은 어떻게 변하나요?

단계:

• 모델 설정
  • 이러한 가격을 한 열에 세로로 입력하세요.
  • 첫 번째 가격에 인접한 셀(예:B5)에서 주요 수익 결과 셀(예:E2)을 참조합니다.

• 헤더를 포함한 전체 표 범위 선택(예:A5:B12)
• 데이터로 이동 탭>> 가정 분석 선택>> 데이터 테이블을 선택합니다.

• 행 입력 셀 나가기 공백
• 열 입력 셀 , 원래 단가 셀(A2)을 선택하세요
• 확인을 클릭하세요.

• Excel은 각 가격에 대한 이익을 자동으로 채웁니다.

해석:

• 결과 열에는 가격에 대한 이익 민감도가 표시됩니다.
• 높은 가격은 일반적으로 수요가 감소하는 지점까지(모델링된 경우) 이익을 증가시킵니다.

두 개의 변수 데이터 테이블

"단가와 판매량이 모두 다르다면 어떻게 될까요?"를 살펴보겠습니다.

단계:

• 세로 가격 입력(왼쪽 열) 및 판매 단위 가로(맨 위 행) 입력
  • 표의 왼쪽 상단 셀(40 이상, 800 왼쪽)에서 주요 수익 결과 셀을 참조합니다(예:=E2 입력). D5에서)

• 헤더를 포함한 전체 표 선택(예:D5:H9)
• 데이터로 이동 탭>> 가정 분석 선택>> 데이터 테이블을 선택합니다.
• 행 입력 셀 , 판매 수량(B2)을 선택하세요
• 열 입력 셀 , 단가(A2)를 선택하세요.
• 확인을 클릭하세요.

• Excel은 모든 가격-판매 조합에서 이익을 보여주는 전체 매트릭스를 생성합니다.

참고: 데이터 테이블의 경우 입력 셀은 데이터 테이블과 동일한 시트에 있어야 합니다. 표를 채우는 데 사용된 수식은 참조를 통해 다른 시트에 있을 수 있습니다.

통계: 데이터 테이블은 가격 탄력성이나 거래량 이익 분석과 같은 빠른 민감도 테스트에 가장 적합합니다.

• 장점: 범위를 시각화합니다. 최대 2개의 변수를 효율적으로 처리
• 단점: 큰 테이블의 경우 계산 집약적일 수 있습니다. 결과는 새로 고쳐질 때까지 정적입니다

3. 시나리오 관리자:여러 "What-If" 모델 비교

시나리오 관리자를 사용하면 수식을 덮어쓰지 않고도 다양한 입력 값 집합(시나리오)을 저장하고 비교할 수 있습니다. 빠르게 전환하거나 모든 시나리오를 나란히 비교하는 요약 보고서를 만들 수 있습니다. 최고/최악의 예측과 같이 여러 변수가 있는 복잡한 시나리오에 적합합니다.

사용 시기:

• 최상의 경우, 최악의 경우, 가장 가능성이 높은 비즈니스 예측 비교
• 여러 가지 변화하는 가정을 통해 다양한 전략적 옵션을 평가합니다.
• 이해관계자에게 명확하고 체계적인 형식으로 여러 시나리오를 제시
• 시간 경과에 따른 모델의 다양한 버전 추적

최선의 경우, 기본 사례, 최악의 경우라는 세 가지 시나리오를 사용하여 비즈니스 예측을 만들어 보겠습니다.

단계:

• 데이터로 이동 탭>> 가정 분석 선택>> 시나리오 관리자를 선택하세요.

• 추가를 클릭하세요.
  • 시나리오 이름: 첫 번째 시나리오의 이름을 지정하세요(예:Best Case)
  • 셀 변경에서 , 단가(A2), 판매 수량(B2), 고정 비용(C2)을 선택합니다. 및 가변 비용(D2)
  • 확인을 클릭하세요.

• 최고 사례에 대한 값을 입력하세요. 시나리오
  • 단가: 65
  • 판매 수량: 1600
  • 고정 비용: 8500
  • 가변 비용: 25
• 확인을 클릭하세요.

• 추가를 클릭하세요. 다시 한 번 “기본 케이스”에 대해 반복

• 추가를 클릭하세요. 다시 한 번 '최악의 경우'를 반복합니다.

• 시나리오 선택>> 표시 클릭 즉시 값을 적용하려면
• 요약을 클릭하세요. 모든 사례를 표시하려면

• 시나리오 요약을 선택합니다.
• 수익 셀을 '결과 셀'(예:E2)로 선택하세요.
• 확인을 클릭하세요.

• Excel은 모든 시나리오 결과를 비교하는 새로운 시나리오 요약 시트를 생성합니다.

통계: 시나리오 관리자 What-If 분석을 사용하여 Excel에서 시나리오를 예측하고 모델링할 수 있습니다. 이는 여러 가정을 저장하고 함께 검토해야 하는 최고의 의사 결정 도구(예:투자, 예산 책정, 위험 평가) 중 하나입니다.

• 장점: 여러 변수를 관리합니다. 보고서와 쉽게 비교
• 단점: 수동 설정; 시나리오당 변경 셀 수는 32개로 제한됩니다.

연습 문제집 다운로드

결론

이 자습서에서는 세 가지 What-If 분석 기술을 사용하여 Excel에서 시나리오를 예측하고 모델링하는 방법을 보여줍니다. What-If 분석은 목표 탐색, 데이터 테이블 및 시나리오 관리자를 통해 다양한 가능성을 탐색하여 불확실성을 장벽에서 기회로 전환합니다. 사업 예산을 계획하든, 투자 옵션을 평가하든, 프로젝트 결과를 모델링하든, 이러한 Excel 도구는 가정의 변화가 결과에 어떤 영향을 미치는지 이해하는 데 도움이 됩니다.