금융 분야의 프로세스는 항상 상호 연결되어 있습니다. 한 요소는 다른 요소에 따라 달라지며 그에 따라 변경됩니다. 이러한 변경 사항을 추적하고 Excel 기능 및 스프레드시트 방법을 사용하여 향후 예상되는 사항을 이해합니다.
데이터 테이블로 여러 결과 얻기
데이터시트 기능은 종종 Microsoft Excel을 통해 수행되는 가정 분석의 요소입니다. 이것은 민감도 분석의 두 번째 이름입니다.
살펴보기
데이터 테이블은 일부 셀의 값을 변경하여 문제를 해결하는 데 사용할 수 있는 셀 범위 유형입니다. 수식 구성 요소의 변경 사항을 추적하고 이러한 변경 사항에 따라 결과 업데이트를 수신해야 할 때 생성됩니다. 연구에서 데이터 테이블을 사용하는 방법과 데이터 테이블의 유형에 대해 알아보겠습니다.
데이터 테이블에 대한 기본 사항
데이터 테이블에는 두 가지 유형이 있으며 구성 요소 수가 다릅니다. 확인해야 하는 uXNUMXbuXNUMXb 값의 수에 초점을 맞춰 테이블을 컴파일해야 합니다.
통계학자는 결과를 변경할 수 있는 하나 이상의 표현식에 변수가 하나만 있는 경우 단일 변수 테이블을 사용합니다. 예를 들어 PMT 기능과 함께 자주 사용됩니다. 공식은 정기 지불 금액을 계산하도록 설계되었으며 계약에 지정된 이자율을 고려합니다. 이러한 계산에서 변수는 한 열에 기록되고 계산 결과는 다른 열에 기록됩니다. 1개의 변수가 있는 데이터 플레이트의 예:
다음으로, 2개의 변수가 있는 플레이트를 고려하십시오. 두 가지 요인이 지표의 변화에 영향을 미치는 경우에 사용됩니다. 두 변수는 최적의 상환 기간과 월 지불 금액을 결정하는 데 사용할 수 있는 대출과 관련된 다른 테이블에서 끝날 수 있습니다. 이 계산에서는 PMT 기능도 사용해야 합니다. 2개의 변수가 있는 테이블의 예:
하나의 변수로 데이터 테이블 만들기
재고가 100권밖에 없는 작은 서점의 예를 사용하여 분석 방법을 고려하십시오. 그들 중 일부는 더 비싸게 판매될 수 있고($50), 나머지는 구매자에게 더 저렴할 것입니다($20). 모든 상품 판매로 인한 총 수입이 계산됩니다. 소유자는 책의 60%를 높은 가격에 판매하기로 결정했습니다. 더 많은 양의 상품 가격을 70% 등으로 올리면 수익이 어떻게 증가하는지 알아내야 합니다.
주의! 총 수익은 공식을 사용하여 계산해야 합니다. 그렇지 않으면 데이터 테이블을 컴파일할 수 없습니다.
- 시트 가장자리에서 떨어져 있는 자유 셀을 선택하고 그 안에 수식을 작성합니다. =총 수익 셀. 예를 들어 소득이 셀 C14에 기록된 경우(임의 지정이 표시됨) 다음과 같이 작성해야 합니다. =S14.
- 우리는 이 셀의 왼쪽 열에 상품 부피의 백분율을 씁니다. 그 아래가 아니라 매우 중요합니다.
- 백분율 열과 총 소득에 대한 링크가 있는 셀 범위를 선택합니다.
- "데이터" 탭에서 "가정 분석" 항목을 찾아 클릭합니다. 열리는 메뉴에서 "데이터 테이블" 옵션을 선택합니다.
- "...의 행으로 값 대체" 열에서 처음에 높은 가격으로 판매된 책의 비율이 있는 셀을 지정해야 하는 작은 창이 열립니다. 이 단계는 증가하는 비율을 고려하여 총 수익을 다시 계산하기 위해 수행됩니다.
테이블 컴파일을 위해 데이터를 입력한 창에서 "확인" 버튼을 클릭하면 계산 결과가 라인에 나타납니다.
단일 변수 데이터 테이블에 공식 추가하기
하나의 변수만으로 액션을 계산하는 데 도움이 된 표에서 수식을 추가하여 정교한 분석 도구를 만들 수 있습니다. 이미 존재하는 수식 옆에 입력해야 합니다. 예를 들어 표가 행 기반인 경우 기존 수식의 오른쪽에 있는 셀에 식을 입력합니다. 열 방향이 설정되면 이전 수식 아래에 새 수식을 씁니다. 다음으로 알고리즘을 따릅니다.
- 셀 범위를 다시 선택하지만 이제 새 수식이 포함되어야 합니다.
- "가정" 분석 메뉴를 열고 "데이터시트"를 선택합니다.
- 판의 방향에 따라 행 또는 열의 해당 필드에 새 수식을 추가합니다.
두 개의 변수가 있는 데이터 테이블 만들기
이러한 테이블의 시작은 약간 다릅니다. 백분율 값 위에 총 수익에 대한 링크를 넣어야 합니다. 다음으로 다음 단계를 수행합니다.
- 소득에 대한 링크와 함께 가격 옵션을 한 줄에 작성하십시오(각 가격에 대해 하나의 셀).
- 셀 범위를 선택합니다.
- 하나의 변수가 있는 테이블을 컴파일할 때와 같이 도구 모음의 "데이터" 탭을 통해 데이터 테이블 창을 엽니다.
- "...의 열로 값 대체"열에서 초기 가격이 높은 셀을 대체하십시오.
- "...의 행으로 값 대체" 열에 고가 책의 초기 판매 비율이 있는 셀을 추가하고 "확인"을 클릭합니다.
결과적으로 전체 테이블은 상품 판매 조건이 다른 가능한 소득 금액으로 채워집니다.
데이터 테이블이 포함된 워크시트의 계산 속도 향상
전체 통합 문서의 재계산을 트리거하지 않는 데이터 테이블에서 빠른 계산이 필요한 경우 프로세스 속도를 높이기 위해 수행할 수 있는 몇 가지 작업이 있습니다.
- 옵션 창을 열고 오른쪽 메뉴에서 "수식" 항목을 선택합니다.
- "통합 문서의 계산"섹션에서 "자동, 데이터 테이블 제외"항목을 선택하십시오.
- 테이블의 결과를 수동으로 다시 계산해 보겠습니다. 이렇게 하려면 수식을 선택하고 F 키를 누릅니다.
민감도 분석을 수행하기 위한 기타 도구
프로그램에는 민감도 분석을 수행하는 데 도움이 되는 다른 도구가 있습니다. 그렇지 않으면 수동으로 수행해야 하는 일부 작업을 자동화합니다.
- "파라미터 선택" 기능은 원하는 결과를 알고 있는 경우에 적합하며 이러한 결과를 얻으려면 변수의 입력 값을 알아야 합니다..
- "솔루션 검색"은 문제 해결을 위한 추가 기능입니다. 한계를 설정하고 이를 가리켜야 시스템이 답을 찾을 수 있습니다. 솔루션은 값을 변경하여 결정됩니다.
- 민감도 분석은 시나리오 관리자를 사용하여 수행할 수 있습니다. 이 도구는 데이터 탭의 가상 분석 메뉴에서 찾을 수 있습니다. 여러 셀의 값을 대체합니다. 숫자는 32에 도달할 수 있습니다. 디스패처는 사용자가 수동으로 값을 변경할 필요가 없도록 이 값을 비교합니다. 스크립트 관리자를 사용하는 예:
Excel에서 투자 프로젝트의 민감도 분석
What-if 분석은 투자와 같이 예측이 필요한 상황에서 특히 유용합니다. 분석가는 이 방법을 사용하여 일부 요인의 변화로 인해 회사 주식의 가치가 어떻게 변할지 알아냅니다.
투자민감도 분석방법
"what if"를 분석할 때 열거형을 사용하십시오(수동 또는 자동). 값의 범위는 알려져 있으며, 하나씩 공식에 대입됩니다. 결과는 값 집합입니다. 그 중에서 적절한 번호를 선택하십시오. 금융 분야에서 민감도 분석이 수행되는 네 가지 지표를 고려해 보겠습니다.
- 순 현재 가치 – 소득 금액에서 투자 금액을 빼서 계산합니다.
- 내부 수익률 / 이익 – XNUMX년 동안 투자로부터 얼마나 많은 이익을 받아야 하는지를 나타냅니다.
- 회수율은 초기 투자에 대한 모든 이익의 비율입니다.
- 할인된 이익 지수 - 투자의 효율성을 나타냅니다.
공식
임베딩 감도는 다음 공식을 사용하여 계산할 수 있습니다. 출력 파라미터 변경 % / 입력 파라미터 변경 %.
출력 및 입력 매개변수는 앞에서 설명한 값일 수 있습니다.
- 표준 조건에서 결과를 알아야 합니다.
- 변수 중 하나를 교체하고 결과의 변경 사항을 모니터링합니다.
- 설정된 조건에 대한 두 매개변수의 백분율 변화를 계산합니다.
- 얻은 백분율을 공식에 삽입하고 감도를 결정합니다.
Excel에서 투자 프로젝트의 민감도 분석의 예
분석 방법론을 더 잘 이해하려면 예가 필요합니다. 다음과 같은 알려진 데이터를 사용하여 프로젝트를 분석해 보겠습니다.
- 프로젝트를 분석하기 위해 테이블을 채우십시오.
- OFFSET 함수를 사용하여 현금 흐름을 계산합니다. 초기 단계에서 흐름은 투자와 동일합니다. 다음으로 공식을 적용합니다. =IF(OFFSET(숫자,1;)=2;SUM(유입 1:유출 1); SUM(유입 1:유출 1)+$B$ 5)수식의 셀 지정은 표의 레이아웃에 따라 다를 수 있습니다. 마지막에 초기 데이터의 값인 잔존 가치가 추가됩니다.
- 우리는 프로젝트가 성과를 낼 기간을 결정합니다. 초기 기간 동안 다음 공식을 사용합니다. =요약(G7: 지17;»<0″). 셀 범위는 현금 흐름 열입니다. 추가 기간에 대해서는 다음 공식을 적용합니다. =초기 주기+IF(첫 번째 e.stream>0; 첫 번째 e.stream;0). 이 프로젝트는 4년 만에 손익분기점에 도달했습니다.
- 프로젝트가 성과를 내는 기간의 수에 대한 열을 만듭니다.
- 우리는 투자 수익을 계산합니다. 특정 기간의 수익을 초기 투자로 나눈다는 표현이 필요합니다.
- 다음 공식을 사용하여 할인 계수를 결정합니다. =1/(1+디스크 %) ^번호.
- 우리는 곱하기를 사용하여 현재 가치를 계산합니다. 현금 흐름에 할인 요소를 곱합니다.
- PI(수익성 지수)를 계산해 봅시다. 시간 경과에 따른 현재 가치를 프로젝트 시작 시의 투자로 나눕니다.
- IRR 함수를 사용하여 내부 수익률을 정의해 보겠습니다. =IRR(현금흐름 범위).
데이터시트를 이용한 투자민감도 분석
투자 분야의 프로젝트 분석에는 데이터 테이블보다 다른 방법이 더 적합합니다. 많은 사용자가 수식을 컴파일할 때 혼동을 경험합니다. 다른 요소의 변경에 대한 한 요소의 종속성을 확인하려면 계산을 입력하고 데이터를 읽을 수 있는 올바른 셀을 선택해야 합니다.
계산 자동화를 통한 Excel의 요인 및 분산 분석
민감도 분석의 또 다른 유형은 요인 분석과 분산 분석입니다. 첫 번째 유형은 숫자 간의 관계를 정의하고 두 번째 유형은 한 변수가 다른 변수에 종속되어 있음을 나타냅니다.
Excel의 분산 분석
이러한 분석의 목적은 값의 변동성을 세 가지 구성요소로 나누는 것입니다.
- 다른 값의 영향으로 인한 변동성.
- 영향을 미치는 값의 관계로 인한 변경.
- 무작위 변경.
Excel 추가 기능 "데이터 분석"을 통해 분산 분석을 수행해 보겠습니다. 활성화되지 않은 경우 설정에서 활성화할 수 있습니다.
초기 테이블은 두 가지 규칙을 따라야 합니다. 각 값에 대해 하나의 열이 있고 그 안의 데이터는 오름차순 또는 내림차순으로 정렬됩니다. 갈등 상황에서 교육 수준이 행동에 미치는 영향을 확인할 필요가 있다.
- 데이터 탭에서 데이터 분석 도구를 찾아 창을 엽니다. 목록에서 단방향 분산 분석을 선택해야 합니다.
- 대화 상자의 줄을 채웁니다. 입력 간격은 머리글과 숫자를 제외한 모든 셀입니다. 열별로 그룹화합니다. 결과를 새 시트에 표시합니다.
노란색 셀의 값이 XNUMX보다 크므로 가정이 잘못된 것으로 간주될 수 있습니다. 교육과 충돌하는 행동 사이에는 관계가 없습니다.
Excel의 요인 분석: 예
판매 분야에서 데이터의 관계를 분석해 보겠습니다. 인기 있는 제품과 인기 없는 제품을 식별하는 것이 필요합니다. 초기 정보:
- 두 번째 달에 어떤 상품 수요가 가장 많이 증가했는지 알아내야 합니다. 수요 증가와 감소를 파악하기 위해 새 테이블을 작성하고 있습니다. 성장은 다음 공식을 사용하여 계산됩니다. =IF((수요 2-수요 1)>0; 수요 2-수요 1;0). 감소 공식: =IF(성장=0; 수요 1- 수요 2;0).
- 상품 수요 증가를 백분율로 계산합니다. =IF(성장/성과 2 =0; 감소/성과 2; 성장/성과 2).
- 명확성을 위해 차트를 만들어 보겠습니다. 셀 범위를 선택하고 "삽입" 탭을 통해 히스토그램을 만듭니다. 설정에서 채우기를 제거해야 합니다. 이 작업은 데이터 계열 서식 도구를 통해 수행할 수 있습니다.
Excel의 양방향 분산 분석
분산 분석은 여러 변수로 수행됩니다. 예를 들어 이것을 고려하십시오. 다른 볼륨의 소리에 대한 반응이 남성과 여성에게 얼마나 빨리 나타나는지 알아야합니다.
- "데이터 분석"을 엽니다. 목록에서 반복 없이 분산에 대한 양방향 분석을 찾아야 합니다.
- 입력 간격 – 데이터가 포함된 셀(헤더 제외). 결과를 새 시트에 표시하고 "확인"을 클릭합니다.
F 값은 F 임계값보다 크며, 이는 바닥이 소리에 대한 반응 속도에 영향을 미친다는 것을 의미합니다.
결론
이 기사에서는 각 사용자가 적용 방법을 이해할 수 있도록 Excel 스프레드시트의 민감도 분석에 대해 자세히 설명했습니다.