엑셀 해찾기로 최대 이윤을 내는 가격 구하기
엑셀로 하는 마케팅 데이터 분석
해찾기에 대해 알아보자.
마케팅 뿐만 아니라 엑셀을 활용해 적정한 값을 찾고자 할 때 해찾기는 매우 강력한 기능이다. 엑셀의 [파일]에서 [옵션]을 선택한 후 '추가 기능'에서 '해 찾기 추가 기능'을 선택하면 [데이터] 탭의 오른쪽에 '해 찾기' 버튼이 나타난다. 해찾기 버튼을 눌러보면 아래와 같은 화면을 확인할 수 있다.
해찾기는 앞서 우리가 함께 연습했던 목표값 찾기와 매우 유사한 구조로 되어 있다. ①의 값이 ②와 같은 조건이 되도록 ③의 값을 변경하는 것이며, ④에서는 부가적인 조건을 설정하게 된다.
해찾기로 최대 이윤을 내는 가격 구하기
앞서 우리가 알아본 선형 수요곡선과 함께 해찾기 기능을 이용하면 최대 이윤을 창출하기 위한 제품의 적정 가격을 구할 수 있다. 무선 이어폰을 예시로 들어 직접 가격을 책정해 보자.
우리는 현재 무선 이어폰을 150,000원의 가격으로 1년간 약 30만 개를 판매하고 있다. 무선 이어폰을 생산하는 비용은 약 50,000원 수준이다. 이 때 무선 이어폰에 대한 가격탄력성은 2이고, 수요곡선은 선형이라고 가정하자. 이윤을 최대화 하기 위한 무선 이어폰의 적정 가격은 얼마일까?
엑셀에 현재 갖고 있는 정보인 '가격', '수요', '단위비용', '가격탄력성' 등을 입력하자
가격과 수요 아래 가격탄력성 2를 적용한 또 다른 가격과 수요를 입력하자.
가격이 1% 증가할 때 수요는 2% 감소하기 때문에 가격의 공식은 150,000 x 1.01, 수요는 300,000 x 0.98 이다.
셀에서 수식을 입력할 경우 C4 = C3 * 1.01, D4 = D3 * 0.98 이다.
가격 탄력성을 기반으로 주어진 가격 및 수요에 대한 데이터를 통해 수요곡선을 그려보자.
[삽입] 탭에서 '분산형 차트'를 선택한다.
'차트 데이터 범위'를 선택하고 X값에는 가격의 항목을, Y값에는 수요의 항목을 선택한다.
완성된 차트 위의 점을 우클릭하고 '추세선 추가'를 선택한다.
'추세선 옵션'에서 '선형'을 선택하고 아래 '수식을 차트에 표시'를 선택한다.
추세선의 수식을 통해 수요 q = 900000 - 4p 라는 것을 확인했다. 이제 해당 수식을 기반으로 해찾기를 통해 이익을 최대화 하기 위한 적정 가격을 알아볼 것이다. 2번과 같이 가격, 수요, 이익 등의 항목을 추가로 만들어 보자.
첫 번째 가격 항목에는 임의의 가격을 입력한다. 기존 가격보다 조금 낮춘 140,000원이라는 가격을 입력해 보자. 이어 수요 항목을 입력해야 하는데, 이 항목은 추세선의 수요 수식 (q = 900000 - 4p)를 적용한다. 엑셀에서의 서식은 D8 = 900000 - 4 * C8 이다.
마지막 이익 항목은 가격에서 단위비용을 뺀 숫자에서 수요 만큼을 곱하면 나온다. 이익 = (가격 - 단위비용) x 수요 이기 때문에 엑셀 수식은 E8 = (C8 - E3) * D8 이다.
이제 해찾기로 적정 가격을 산출하면 된다. '특정 값(이윤)이 특정 조건(최대)이 되도록 특정 셀(가격)의 값을 변경한다' 라는 논리를 다시 떠올리면 이해하기 쉽다. [데이터] 탭의 가장 우측 '해 찾기'를 선택한다.
목표는 이익이기 때문에 '이익' 항목의 E8 셀을 선택한다.
바로 아래 '대상'은 '최대'로 선택한다.
변수 셀은 '가격' 항목의 C8 셀을 선택하고 가장 아래 '해 찾기' 버튼을 클릭한다.
해 찾기가 완료되면 위와 같이 '가격'과 '이익' 셀의 값이 변한다. 이 결과에 의하면 무선 이어폰의 가격을 147,500원으로 설정해야 최대 이익을 창출할 수 있다.
