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

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

 

 

▶이전 포스팅 리뷰 

파워 쿼리를 사용하여 데이터 전처리와 시뮬레이션을 위한 수식 작성까지 완료된 상태로 지난 포스팅을 마치고 이번 글에서는 완성된 파워 쿼리 데이터를 데이터 모델에 로드하여 파워 피벗으로 보고서를 완성하는 작업을 수행합니다. 

지난 포스팅 링크 

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

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

 

▶작업 수행 (문제풀이)

먼저 파워 피벗으로 동적인 시뮬레이션 보고서를 만들기 전에 만들어 놓은 쿼리를 데이터 모델에 로드하는 작업이 먼저 수행해야 한다. 

쿼리 목록에서 마지막 작업 쿼리인 "병합 2-환율 추가"를 우클릭하여 '다음에 로드'를 클릭하여 로드 설정을 새 워크시트에 표 형식으로 로드하고 맨 아래 '데이터 모델에 이 데이터 추가'를 체크하여 파워 피벗을 사용할 수 있도록 데이터 모델로 생성하자. 

"병합 2-환율 추가"란 이름으로 데이터 모델이 생성되었고 파워 피벗 창에서 생성 결과를 확인할 수 있다. 또한 데이터를 표 형식으로 새 시트에 로드하였으므로 "Sheet1"에 데이터가 로드되었다. 쉽게 인식하기 위해서 Sheet1을 "매출 계획 데이터"로 수정하자. 

 

¶작업 2 (파워 피벗) 

이제 파워 피벗을 사용하여 피벗테이블을 만들어 보자. 파워피벗 창에서 '피벗테이블> 피벗테이블'을 클릭하고 새 워크시트에 피벗테이블을 만들자. 

Sheet2에 피벗테이블이 생성되었고 피벗 테이블 필드 창에 "병합 2-환율 추가" 테이블이 생성된 것을 확인할 수 있다. 시트 이름을 p_pivot으로 수정하였다. 

* '활성 | 모두'를 선택할 수 있는 탭이 있는데 처음 피벗 테이블이 구성될 때는 활성 탭 쪽에는 아무런 내역이 없을 것이다. 그리고 필드를 각 영역에 넣고 나서 다시 활성 탭을 확인하면 영역에 들어간 필드가 위치한 테이블이 표시된다. 일반 피벗테이블과 다르게 파워 피벗은 여러 테이블을 동시에 하나의 피벗테이블에 담아서 사용이 가능하다. 

다시 정리하자면 '모두'는 현재 사용이 가능한 모든 테이블인 셈이고 '활성'은 현재 피벗테이블의 영역에서 사용하고 있는 또는 사용할 테이블을 의미한다. 물론 각 테이블을 우클릭하고 활성 탭에 추가/제거를 할 수 있다. 

맨 아래에 위치한 '병합 2-환율 추가' 테이블의 필드를 영역으로 위치시켜 보자. 

문제가 발생하였다. 값 영역에 넣은 필드가 '합계'가 아니라 '개수'로 표시된다. 데이터 형식이 숫자 타입이어야 하는데 파워 피벗에서 텍스트로 읽고 있는 것 같다. 

파워 쿼리 편집기로 이동하여 데이터 형식을 변경해주자.  

확인해보니 '임의'를 의미하는 아이콘으로 표시되어 있다. 모두 숫자 형식으로 데이터 형식을 변경해주자. 금액 필드들은 통화로 나머지는 십진수로 수정하였다. 닫기 및 로드를 클릭해서 빠져나온 후 피벗테이블의 값 요약 기준을 합계로 변경하자. 

이번 Case는 그냥 값 영역에 금액과 수량을 넣어서 사용해도 무관하나 이왕 파워 피벗까지 왔으니 측정값을 만들어서 써보자. 

추가된 측정값들

새로 만든 측정값을 가지고 값 영역을 구성해보자. 

 

¶마무리 : 시뮬레이션을 해보자. 

이제 시뮬레이션을 해보자. 한눈에 확인하기 위해서 "풀이" 시트에 있는 'f환율' 테이블을 잘라내서 "p_pivot" 시트로 붙여 넣기로 옮긴 후 환율 값을 변경하고 '모두 새로고침(Ctrl+Alt+F5)'로 갱신해보자. 

Before

Best 환율이 1,200원인 상태에서 1,500원으로 수정한 뒤 모두 새로고침을 하면... 

자동 갱신된다. 이제 Base / Best / Worst Case 별로 환율을 정하고 새로고침으로 갱신 후 요약하여 보고하자. 

그리고 다음 달에 다음 3개월 롤링 매출 계획이 영업부서로부터 전달되면 이 파일에 해당 표 데이터를 붙여 넣고 새로고침을 하자. 더 일하지 말고...  

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

0.46MB

 

¶맺음말 

파워 쿼리를 이용한 동적으로 계산 결과를 만들어 내는 방법은 이번 Case와 같이 쿼리 병합을 통해서 할 수 도 있고 사용자 함수를 만들어서 처리할 수 도 있습니다. 뿐만 아니라 파워 피벗의 데이터 모델 간의 관계 정의를 통해서도 가능합니다. 변수를 입력하는 것을 슬라이서를 사용하는 등의 방법으로 사용할 수 도 있습니다. 

앞으로 다른 Case 연구에서 시뮬레이션을 하는 방법을 좀 더 다양하게 그리고 쉽게 적용할 수 있도록 연구를 해봐야겠습니다.  

처음에는 의욕적으로 시작했으나 포스팅이 점점 길어지니 설명이 빈약해지는 느낌이 드네요. 하지만 모든 과정의 설명과 함께 진행 사항을 다 캡처와 글로 표현하려니 너무 늘어져서 조금 다른 방법을 찾아야 할 것 같네요. 역시 대안은 유튜브로 설명과 함께 작업 내용을 보여주는 것이 최고 인듯합니다. 하지만 아직 아무런 녹화 준비가 안되어 있으므로 어느 정도 내용이 진행된 뒤에 시도해 볼 생각입니다. 

욕심 내면 지치고 지치면 포기하게 될 것 같아요. 슬슬 살살 취미로 채웁니다. 언젠가는 책 한 권이 될 만큼 Case 연구가 많아지는 날이 오겠죠~!