연평균 증가율 계산기 - yeonpyeong-gyun jeung-gayul gyesangi

답변

1단계 - 매개 변수 만들기

1. 데이터 패널에서 차원의 오른쪽에 있는 드롭다운 화살표를 클릭하고 매개 변수 만들기를 선택합니다.
   1. 매개 변수의 이름을 "N Years"로 지정합니다.
   2. 데이터 유형에서 정수를 선택합니다.
   3. 현재 값에 2 를 입력합니다.
   4. 표시 형식 및 허용 가능한 값은 그대로 둡니다. 
2. 확인을 클릭합니다.

2단계 - 계산된 필드 만들기

1. 분석 > 계산된 필드 만들기를 선택하고 필드 이름을 "CAGR"로 지정합니다.
2. 아래와 같이 수식을 입력합니다.

   POWER(ZN(SUM([Sales]))/LOOKUP(ZN(SUM([Sales])),-[N Years]), 
   ZN(1/[N Years])) - 1
   

3. 확인을 클릭합니다.

3단계 - 뷰 작성

1. "Order Date"를 열로 끌어오고 "분기"로 드릴다운합니다.
2. "Sales"를 행으로 끌어옵니다.
3. "CAGR"을 행으로 끌어옵니다. 
   1. "CAGR"을 마우스 오른쪽 단추로 클릭하고 이중 축을 선택합니다.
   2. "CAGR"을 마우스 오른쪽 단추로 클릭하고 테이블 계산 편집 > 특정 차원 선택 > Order Date 연도만 선택합니다.
4. Sales 카드에서 Sales를 세부 정보로 끌어오고 필요한 차트를 선택합니다(이 예제에서는 막대 차트를 선택함).
5. CAGR 카드에서 CAGR을 세부 정보로 끌어오고 필요한 차트를 선택합니다(이 예제에서는 라인 차트를 선택함).
6. CAGR 카드에서 측정값을 마우스 오른쪽 단추로 클릭하고 테이블 계산 편집 > 특정 차원 선택 > Order Date 연도를 선택합니다.
7. 필요한 경우 Sales를 도구 설명으로 끌어옵니다.

4단계(선택 사항) - 크로스탭에서 데이터 보기 

1. 시트를 마우스 오른쪽 단추로 클릭하고 크로스탭으로 복제를 선택합니다.
2. 필요한 경우 행과 열을 서로 바꿉니다.
3. “Order Date 연도”를 오른쪽으로 이동합니다.

CAGR(Compond Annual Growth Rate)은 ‘연평균 복합 성장률'이라고 부르는데, CAGR과 일반적 성장률과의 차이는 다음과 같습니다.

•일반적인 성장률 : 전년도 대비 올해의 성장 부분을 비율로 나타낸 수치

•CAGR : 분석기간 동안의 연도별 성장률을 평균한 수치

컨설턴트들은 성장률 대신 CAGR을 많이 사용합니다.

CAGR을 사용하면 갑자기 매출액이 커졌거나 떨어져서 생기는 데이터의 오류를 방지할 수 있기 때문이죠.

한번 CAGR을 계산해 볼까요? 

위의 문제가 있다면 어떻게 구할까요? 아래처럼 구하면 됩니다. 

CAGR을 계산하는 수식은 ‘=(최종연도값/최초연도값)^(1/연도간격)-1’입니다.

위에 문제를 수식에 대입하면, ‘=(254억 원/154억 원)^(1/5년)-1’로 결과는 10.5%가 나옵니다.

그런데 너무 복잡하죠?

엑셀에서 함수를 이용하면 편리한데요. 

엑셀 함수로는 POWER(), RATE() 함수를 주로 사용합니다. 

POWER()을 수식으로 표현하면

'=POWER(최종연도 값/최초연도 값, 1/기간)-1'입니다. 

또한, RATE() 함수를 이용하면 됩니다. 

수식으로 표현하면 ‘=RATE(연도 간격, 0, -최초연도 값, 최종연도 값)’입니다.

연도별 평균 성장률을 의미하는 CAGR도 이자율과 동일한 개념으로 볼 수 있습니다.

수식을 외워서 사용하기보다는 엑셀의 RATE() 함수를 이용하면 CAGR을 쉽게 구할 수 있습니다.

반응형

RATE() 함수로 CAGR 계산하기

시장규모를 조사하다 보면 우리가 원하는 연도의 데이터를 제공하지 않는 경우가 많습니다. 

이럴 때 CAGR(연평균 성장률)을 활용하면 우리가 원하는 연도의 데이터를 쉽게 얻을 수 있습니다. 

아래 사례처럼 데이터가 비어 있는 연도의 수치를 CAGR을 이용해 계산해 보겠습니다.

예) OO서비스 국내 시장은 2020년 154억 원에서 2025년에 254억 원으로 성장할 것으로 전망된다.

사례 1 CAGR을 이용해 2021~2024년 데이터 구하기

01

해당 기능은 [수식> 함수 삽입]을 선택해 [함수 마법사] 창에서 [RATE]를 선택하면 됩니다.

02 

CAGR을 적용하여 2021~2024년 데이터를 계산해 보겠습니다. 

2021년 셀(D5)을 선택하고 전년도(2020년) 값에 ‘1+CAGR값’을 곱해주면 2021년 데이터가 계산됩니다. 

2022~2024년의 데이터도 같은 방식(전년도 값 ×(1+CAGR값))으로 계산하면 됩니다.

사례 2 CAGR을 이용해 2026~2028년 데이터 구하기

앞서 계산한 CAGR을 적용하여 동일한 계산방법으로 2026~2028년까지의 수치를 구하면 됩니다. 

다만 사례에서의 CAGR에는 2026년부터의 성장성은 고려되어 있지 않기 때문에 정확한 추정방식은 아니라는 점에 유의해야 합니다. 

따라서 CAGR 계산법은 원하는 데이터가 정말로 없을 때 활용하기 바랍니다.

이 포스팅은 <누구나 탐내는 실전 기획서>의 본문 일부를 발췌하여 작성했습니다.

일반적인 성장률 대신 CAGR도 사용하지만,

AAGR(연평균 성장률)이 있는데 차이점은 아래 포스팅을 참조하면 됩니다.

2021.11.24 - [보고서 작성기술/일잘러가 되기위한 상식] - 성장률 vs AAGR vs CAGR 비교

도움이 되는 다른 포스팅도 참조하세요^^

2022.03.25 - [보고서 작성기술/엑셀] - 엑셀 반올림, 올림, 버림 함수 한 번에 알아보기

2022.04.06 - [보고서 작성기술/엑셀] - 엑셀 랜덤 함수 : 엑셀 랜덤 추출, 순위 표시, 당첨자 뽑기

2021.08.05 - [보고서 작성기술/엑셀] - 엑셀 원하는 텍스트 추출(LEFT, RIGHT, LEN, FIND 함수)

2021.07.29 - [보고서 작성기술/엑셀] - 엑셀 셀 합치기(CONCATENATE 함수, 기호&)

2021.07.22 - [보고서 작성기술/엑셀] - 엑셀 COUNTIF 함수, COUNTIFS 함수로 원하는 값 개수 구하기

2021.07.16 - [보고서 작성기술/엑셀] - 엑셀 INDEX함수와 MATCH 함수로 원하는 값 가져오기

2021.07.14 - [보고서 작성기술/엑셀] - 엑셀 VLOOKUP 함수로 원하는 데이터 가져오기, 등급 부여하기

본 포스팅 내용을 설명과 동영상으로 보시고 싶다면 아래 영상을 참조하시기 바랍니다. 

반응형

공유하기

게시글 관리

저작자표시 비영리 변경금지

• 카카오스토리
• 트위터
• 페이스북

'보고서 작성기술 > 엑셀' 카테고리의 다른 글