앞선 글에서 엑셀의 해 찾기 기능의 기본적인 사용법과, 제품의 생산계획을 결정하는데 적용하는 사례를 소개한 바 있었다.
이러한 엑셀 해 찾기 기능은 비단 생산계획 뿐만 아니라, 의사결정이 필요한 다양한 상황에 적용이 가능한데, 본 글에서는 판매 등의 영업활동이나, 마케팅 활동에도 적용하는 예제를 소개한다.
다음과 같이 A~E까지 5가지 제품을 취급하고, 각각의 제품별 판매가와 제품별 마진이 다음과 같은 경우에서, 각각의 제품에 대한 고객의 호응도를 1-10 사이로 점수화 시켜놓은 상태일 때, 일별 제품 판매 목표를 어떻게 설정하는 것이 가장 바람직할까?
단, 하루 최대 1000개까지의 제품만 판매할 수 있는 상황이다.
물론 이 테이블에서 판매량의 합과, 이익은 다음과 같은 식들이 미리 채워져 있는 상태이다.
이러한 조건하에서, 여러 가지의 목표에 따라, 엑셀의 해 찾기 기능을 이용하여, 최적의 판매 목표량을 계산하는 방법을 소개한다.
1. 판매 이익을 극대화하려는 경우의 판매 목표
엑셀의 해 찾기 기능에서, 목표를 다음과 같이 총 판매 이익에 해당되는 F10 셀(붉은 표기 부분)로 지정해 준다.
목표 달성을 위해 조절할 변수로는 각 제품별 판매수량에 해당되는 E5~E9 셀(녹색 ㅍ기 부분)을 지정해 준다.
제한조건으로는 하루 판매량이 미리 설정해둔 값인 1000을 넘지 않게 다음과 같이 지정해 준다.
이렇게 조건 설정이 완료되면, 다음의 “해 찾기” 메뉴를 선택해 준다.
그러면 다음과 같이 최적해가 찾아졌다는 메시지가 나오면서
다음과 같이 각 제품들의 판매수량이 결정되어 채워진 것을 볼 수가 있다.
이 경우에는 개당 마진이 가장 큰 제품 C만을 판매하도록 목표가 계산된 것을 볼 수 있고, 총 마진은 120만 원이다.
2. 판매 평점 합계치를 극대화하려는 경우의 판매 목표
이 상태에서 이익과 비슷한 방식으로 다음과 같이 평점 x 판매량을 계산하는 열을 G 열에 추가하고,
이 값의 합을 다음과 같이 계산하였다.
이 상태에서, 이 판매 평점 합계치를 극대화하는 조건을 계산해 보기로 했다.
다음과 같이 목표로 하는 값을 기존의 이익 대신 판매 평점 계산치인 G10 셀로 변경해 주고
해 찾기를 시도해 보면
다음과 같이 최적해가 찾아지면서, 해당 테이블의 판매 목표량이 변경되는 것을 볼 수 있다.
기존 이익 극대화 경우에 C 제품만을 판매하던 것이, 이번에는 고객 평점이 가장 높은 제품 A만을 판매하는 것으로 변경된 것을 볼 수가 있다. 다만 이 경우 총이익은 50만 수준으로 기존의 120만 원 보다 감소한 것을 볼 수 있다.
3. 판매 평점 합계치를 일정 수준(5000 이상) 유지하면서 이익 극대화 경우 판매 목표
다음과 같이 제한 조건에 평점 x 판매량 계산 시의 합계가 5000 이상이 되도록 조건을 추가해 주었다.
다음과 같이 제한 조건에 해당 사항이 반영된 것을 확인할 수 있다.
이 상태에서 목표 설정 부분은 총이익에 해당되는 F10 셀로 지정해 준다.(녹색 표시 부분)
아 상태로 해 찾기를 진행하면 다음과 같이 판매 목표가 제품 E와 제품 C 위주로 구성된 것을 알 수 있다.
이 경우 평점 x 판매량 합계는 5000이고, 총 마진도 120만 수준으로 양호하다.
4. 판매 평점 합계치를 일정 수준(5000 이상) 유지하고, 각 제품을 최소 100개 이상씩은 판매하는 조건하에서, 이익을 극대화하려는 경우
위에서 평점 x 판매량 계산 시의 합계가 5000 이상이 되는 경우를 계산해 보았으나, 제품 C와 제품 E만을 판매하도록 목표가 계산되었는데, 현실에 있어서는 5개 제품을 모두 취급하려면 각각의 제품을 일정 수량 이상씩은 판매해야 하는 경우들이 많아.
이에 위의 조건에 각각의 제품을 최소 100개 이상씩은 판매해야 하는 조건을 추가해 보았다.
다음과 같은 방식으로 추가하면 되며, 이러한 과정을 제품 A부터 제품 E까지 하나씩 반복해서 추가해 준다.
이렇게 조건을 추가하고 나면 해 찾기 대화상자의 화면은 다음과 같이 설정된다.
아래 화면에서 각각 제품의 최소 판매량 조건(>=100개)는 녹색 부분으로, 평점 x 판매량 계산지 합계는 5000 이상이 되도록 반영된 것을(붉은 부분) 확인할 수 있다.
이 상태로 해 찾기를 진행해 보면, 각 제품별 수량이 최소 100개 이상을 유지하면서,
고객 평점 x 판매량 합계도 목표한 대로 5000을 유지하고,
총판매 이익도 104만 원 수준으로 계산되는 것을 볼 수 있다.
참고 – 본 예제에 사용한 예제 파일은 다음에 첨부한다.