Case 연구 - 매출계획 시뮬레이션(할인,환율) 1/2

이번 포스팅은 Case 연구입니다. 매출 계획 수립 업무가 없는 회사는 거의 없을 겁니다. 업종마다 회사마다  세부적인 사항은 다르지만 기간을 정해두고 수요를 예측하고 계획하는 본질은 다르지 않을 것입니다. 파워 쿼리를 사용해서 이미 작성된 매출 계획을 시뮬레이션이 가능하도록 변환하는 과정을 소개합니다. 

Story : 

각 영업부서로부터 집계가 완료된 자료를 받았다. 간단히 검토 하고 결재를 넘기려는데… 환율이 오를 때를 감안하여 변동 시나리오별로 매출 계획을 여러 버전으로 작성해보라는 지시를 받았다. 그리고 잠시 뒤 영업부서로부터 유통업체에게 수량 베이스로 적용되는 할인금액이 계획에 빠졌다고 연락을 받았다. 아예 고려를 못해서 세부 계획 데이터도 없다고 한다. 이런 큰일이다. 받은 자료는 달랑 엑셀 파일 하나에 월별로 예쁘게? 합산된 표 하나뿐인데…

▶문제 

받은 문제의 매출 계획표 

* 환율 1,000원으로 작성되었음.  외화(USD)로 환산해야 함. 
** 전 기간의 매출단가는 동일함

1. 환율 변동에 따라 시뮬레이션 구성할 것.
2. 유통업체에게는 수량 기준 할인 적용 시뮬레이션 구성 할 것 (각 유통업체별 매출 비중은 아래 표를 참조)

할인 조건 : 업체별로 월 매출 수량이 3,000이 넘을 경우 5% 할인, 4,000이 넘을 경우 10% 할인

▶설계

¶문제 이해 

환율 변동에 대한 시뮬레이션이 되도록 USD로 금액을 돌려놓고 환율에 따라 원화 값이 변경되도록 해야 한다. 유통업체의 경우 할인금액을 적용해야 하나 주어진 Data에는 업체별로 나누어져 있지 않기 때문에 매월 다른 비중을 적용해서 매출 수량을 배분해야 한다. 그리고 업체별로 수량을 합산하여 할인금액을 적용해야 한다. 

작업 결과는 크로스 탭 형태로 최종 산출되어야 하며 환율과 유통업체별 매출 계획 비중은 동적으로 반영이 되어야 한다. 그리고 이 작업은 다음 달에도 또 비슷한 요건으로 수행될 가능성이 매우 높다. 

¶작업 방향 설정

1) 우선 현재 크로스탭 형식의 표는 작업효율이 떨어지므로 Table 형태로 피벗팅 해제가 필요하다. 

2) 환율 적용을 위해 외화 매출단가가 필드가 필요하고 이 필드와 동적으로 계산될 환율 테이블도 필요하다.

3) 업체별 매출 비중을 반영하여 계획 데이터가 업체별로 더 세분되어야 한다. 즉, 업체별로 배부가 필요하다. 

4) 배부된 결과에 2가지 제품을 모두 업체별로 매월 수량을 합산하여 할인 금액을 산정해야 한다. 

5)이 결과를 다시 크로스탭 형태로 변환하여 가독성을 확보해야 한다. 

이 모든 것을 파워 쿼리와 파워 피벗을 사용하여 처리해보자. ㅜㅜ; 

 

▶작업 수행 (문제풀이)

파워 쿼리를 통한 1단계 전처리로 피벗팅 해제를 수행한다. 2단계로 배부를 통해 업체별 매출 계획으로 세분화한다. 파워 쿼리에서 처리가 용이한 시뮬레이션 수식을 완성한다. 이렇게 전처리가 완성된 쿼리를 데이터 모델로 로드하여 파워 피벗으로 보고서를 완성한다. 

¶준비(전처리)

1) 피벗팅 해제 : 파워 쿼리로 피벗팅 해제를 하기 위해서는 쿼리로 만들기 위한 전처리부터 해야 한다.

아래 표(table)형태로 데이터를 변환해야한다. 

피벗 해제를 파워 쿼리로 수행하기 위해서 먼저 병합된 셀을 모두 해제하고 표로 구성하여 쿼리를 생성해야 한다. 

 - 병합 해제 > 표(Table)로 구성

병합 해제 후 표(Table)로 변환한 모습

병합 해제 후 표(Table)에서 "데이터 가져오기"를 통해 쿼리를 생성한다. 표의 이름은 "fSource"로 명명하였다. 

 - 파워 쿼리 생성 후 변환 수행 

이제 본격적인 변환에 들어간다. 

피벗 해제가 완성된 모습

직접 함수를 작성하지 않고 모두 파워 쿼리의 리본 메뉴에 있는 기능만으로 구성해보니 17단계를 거쳐 완성이 되었다. 

한 가지 항목으로 피벗팅 되어 있는 경우에는 피벗 해제가 매우 간단하지만 지금처럼 여러 열 항목으로 계층화된 구조는 빈 값을 채우는 것 부터해서 다소 복잡한 단계가 필요하다. 

모든 단계를 하나씩 표현하기에는 스크롤의 압박이 심한 관계로 별도의 포스팅에서 다루기로 하고 주요 단계에 대한 간략한 설명만 남긴다. 

단계명	설명
채우기	병합이 해제되었으므로 빈셀이 존재한다. 병합해제 전의 값으로 모두 채워넣어야 한다.제품코드는 채우기 기능만으로 처리가 가능하다.
행/열 바꾸기	파워쿼리에서 열의 내용을 상/하로 채우기는 가능하지만 행을 기준으로 채우기 기능이 없기 때문에 행/열을 바꿔서 채우기를 수행해야한다. 월,업체유형,값필드명(수량,금액)의 빈값을 채우기 위해 사용되었다.
열 병합	월,업체유형,값필드명(수량,금액)을 하나의 열로 합쳐서 행/열 바꾸기를 수행하면 계층화된 열 항목들이 하나의 행으로 정리되므로 열 헤더로 승격이 가능해진다.
첫 행을 머리글로 사용	첫행으로 항목이 합쳐진 상태로 승격을 한다.
열 피벗 해제	열 피벗 해제로 각 열 헤더를 값으로 돌려준다.
열 분할	합쳐놓은 월,업체유형,값필드명(수량,금액)의 조합열을 다시 풀어준다.
피벗 열	값필드명(수량,금액)는 각 필드로 표현되어야 하므로 피벗 열 기능으로 각각의 값으로 돌려준다.

2020.07.25 추가) 전처리 내용을 영상으로 찍어 포스팅 했습니다. 링크 추가합니다. 

2020/07/25 - [엑셀/엑셀 작업 Case 연구] - 파워쿼리 전처리 - 다중 필드 언피벗팅

파워쿼리 전처리 - 다중 필드 언피벗팅

 

2) 업체별 매출 계획 상세화 

이제 업체별로 매출 계획을 상세하게 나누어 주어야 한다. 쿼리 병합을 통해서 먼저 데이터 구조를 업체별로 행이 늘어나게 한다. 업체별 비중 테이블을 엑셀 시트에 작성하여 쿼리로 생성한다. 표 이름은 'fC_rate'로 명명함.

업체 유형이 '유통업체'인 경우에만 해당하므로 조인을 위해서 업체유형 값이 주어진 표에서 추가되었다. 쿼리 병합 시 조인 조건이 '업체 유형', '계획 월' 이 되어야 하므로 'fC_rate' 도 피벗 해제를 한다. 

이제 2개의 쿼리를 병합하여 데이터를 구성한다. 매출 계획(fUnpivoting) 쿼리를 선택하고 "쿼리를 새 항목으로 병합"을 클릭한다. 기존 쿼리를 그대로 두고 새로운 쿼리를 만들어서 작업하기 위함이다.
*업체별 비중('fC_rate')의 계획 월을 '01월'로 했어야 하는데 '1월'로 하여 수정하였다. ㅡㅡ;

하단의 테이블을 업체별 비중('fC_rate')으로 놓고 조인 종류를 '왼쪽 외부'로 놓고 업체 유형, 계획 월을 Ctrl+클릭으로 각각의 테이블에 지정해주고 확인을 클릭한다. 

① "병합 1" 이란 이름의 새 쿼리가 생성되었다. 

② fC_rate 필드가 추가되었고 ③ Table이란 값이 있는 셀을 클릭하면 하단에 내역이 표시된다. 

이제 'fC_rate' 필드에서 필요한 값을 추출하면 된다. 

모든 항목을 추출하여 데이터를 살펴보면 매출 계획 쪽은 같은 값이 반복되고 추가된 비중 쪽은 원래 값으로 늘어나 있는 것을 확인할 수 있다.

자~ 이제 수량에 비중을 곱해서 업체별 수량을 만들어준다. 업체 유형이 '유통업체'가 아닌 경우에는 값이 null 이므로 곱하기 이전에 먼저 null을 1로 변경해주어야 한다. 

값 필드를 선택하고 '홈> 값 바꾸기'를 클릭해서 아래와 같이 null 은 1로 바꾼다.

이제 '열추가> 사용자 지정열'로 열을 추가한다. 

이제 업체별 매출 계획 비중별로 매출수량이 배분되었다. 이 즈음에서 결과를 엑셀 시트에 로드하여 전처리 결과를 살펴보자. 

계산된 결과인 '병합 1' 쿼리를 엑셀 시트에 피벗 테이블 형태로 로드하고 수량을 비교해보자.

배부 전~후의 값이 일치 함을 확인해보자. 일치한다. 이제 기본적인 전처리가 완료되었다. 이제 환율을 시뮬레이션할 수 있도록 엑셀 시트에 환율 테이블을 만들고 해당 값이 변경될 때마다 갱신되는 형태로 데이터를 구성하자.

¶작업 1 (파워 쿼리)

3) 환율 시뮬레이션 수식 구성

환율 테이블을 만들기 전에 외화 단가를 만들어 놓어야 한다. 외화 단가가 있어야 '환율 x외화 단가 x 수량'으로 원화 매출금액을 자동 산출할 수 있기 때문이다. 

'열추가> 사용 자지 정열'을 클릭하여 수식 창을 넣고 '통화 코드'와 '외화 단가' 필드를 추가한다. 

시뮬레이션으로 적용할 환율 값을 가지고 오게 될 환율 테이블을 만들자.

시나리오와 통화 코드를 넣고 각 시나리오별 환율을 구성하였다. 테이블 명은 'f환율'로 명명함. 환율 테이블도 쿼리로 생성한다. 

환율을 각 시나리오 별로 적용하기 위해서 다시 한번 앞서 작업한 "병합 1" 쿼리와 "f환율" 테이블을 병합하여 데이터를 각 시나리오 별로 3배를 만들 것이다. "병합1" 쿼리를 선택하고 업체별 배부를 만들어 낼 때와 동일하게 '쿼리 병합> 쿼리를 새 항목으로 병합'으로 새로운 쿼리를 작성한다. 새로 생성된 쿼리는 '병합 2-환율 추가'로 명명한다. 

조인 조건을 각 테이블의 통화 코드로 하여 수행한다.

환율 시나리오와 환율 값을 확장하여 추가한다. 이제 '열추가> 사용 자지 정열'을 클릭하여 아래와 같이 식을 넣으면 환율 시뮬레이션이 반영될 원화금액 필드가 완성된다. 

 

4) 수량 할인 수식 구성

이제 마지막으로 월 판매수량에 따른 할인 금액을 산출하여 적용하는 일만 남았다. 먼저 업체별 월 판매수량을 산출해야 한다. 그리고 산출된 업체별 월별 판매수량 합계를 가지고 할인율을 계산할 것이다. 

할인율 쿼리를 만들기 위해서 쿼리 리스트 창에서 '병합 1' 쿼리를 우클릭하여 참조를 선택해서 '병합 1'을 참조하는 새 쿼리를 만들다. 

쿼리 이름을 'f할인율'로 수정한다. 

이제 'f할인율' 쿼리를 '계획 월', '업체명' 별로 수량을 합산하는 그룹화 처리를 수행하여 '월별 업체별 수량'을 만든다. 

할인은 업체 유형이 '유통업체'인 경우만 적용되므로 먼저 필터를 걸어서 유통업체만 남긴다. 그리고 그룹화를 클릭하여 계획 월과 업체명을 그룹화 기준으로 정하고 새열은 '수량 합계'로 이름을 넣으면서 '매출수량_배분' 필드를 합산하도록 설정한다. 

그룹화가 수행된 결과이다. 

이제 조건열 추가를 통해서 수량을 기준으로 할인율을 산정한다. 

할인율이 완성되었으니 'f할인율' 쿼리와 '병합 2-환율 추가'

이번에는 새 쿼리로 병합하지 않고 '병합 2-환율 추가'

조인 조건을 '계획 월'과 '업체명'으로 하고 병합을 수행한 뒤 '할인율' 필드를 확장하여 추출한다. 

확장까지 완료된 상태이다. 그런데 유통업체가 아닌 경우에는 업체명이 없으므로 조인이 안된다. 이럴 경우 null 값이 채워지게 되는데 할인금액과 할인 후 원화금액을 계산할 때 null 값은 문제가 되므로 '0'으로 값을 바꿔준다. 

'홈탬> 변환> 값 바꾸기'를 사용해서 값을 바꾼다. 이때 찾을 값에 'null'을 입력하고 0으로 바꾸면 된다. 

할인율이 업체별, 월별로 추출이 되었으니 '할인금액'과 '할인금액이 반영된 원화 금액'을 각각 사용자 지정 열 추가로 생성한다.

자! 드디어 파워 쿼리를 사용하여 크로스 탭으로 구성된 데이터를 환율과 업체별 매출 비중이 반영된 시뮬레이션이 가능한 데이터로 변환이 완료되었다. 

이 데이터를 피벗테이블로 구성하여 가독성이 확보된 멋진 보고서로 만드는 일만 남았다. 

 

 

여기서 지금까지의 작업을 요약정리하고 다음 포스팅에서 파워 피벗을 이용한 보고서 작업을 진행하겠습니다.

현재까지의 작업 파일을 첨부합니다. 

매출계획_시뮬레이션전환_데이터 변환_파워쿼리.xlsx

0.05MB

 

 

다음 글 추가 링크입니다. 

2020/07/13 - [엑셀/엑셀 작업 Case 연구] - Case 연구 - 매출계획 시뮬레이션(할인,환율) 2/2

Case 연구 - 매출계획 시뮬레이션(할인,환율) 2/2