최대 매출이익을 내는 판매가격 구하는 방법 (추세선 분석+매크로 활용)

by 오빠두엑셀

학습시간 16분
난이도 중급
작성일 2021.04.28

차트 추세선과 해찾기 기능을 활용한 최대 매출이익 판매가 분석 및 VBA 매크로를 활요한 업무 자동화까지 총정리

이 강의에서는 차트 추세선과 해찾기 기능을 사용해, 매출이익이 가장 높아지는 적정 판매가격을 엑셀에서 직접 분석하는 방법을 다룹니다. 다항식 추세선의 표시형식 보정부터 해찾기의 제약조건 설정까지 단계별로 살펴보고, 추세선 수식을 자동으로 계산해 주는 TrendX 사용자지정함수까지 함께 알아봅니다.

DOWNLOADS

실습자료를 준비했어요

수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇

실습파일.xlsx

로그인 후 다운로드

가격-매출이익 분석 기초 및 시장조사 방법

수요-공급 곡선과 가격-매출이익 분석에 필요한 기초 이론은 이전 영상강의에서 자세히 다뤘습니다. 이전 강의를 보지 못하셨다면 아래 영상강의를 먼저 참고해 주세요.

각 판매가격별 고객 수요량을 정리한 후 차트와 추세선으로 데이터를 분석하면, 판매가격에 따라 예측되는 수요를 매우 간단하게 계산할 수 있습니다.

차트 만들기 : 예제파일의 F10:G15 범위(가격:판매량)를 선택한 뒤, [삽입] 탭 - [추천 차트]로 이동합니다.
'차트 삽입' 대화상자가 실행되면, '추천 차트' 또는 '모든 차트' 탭에서 분산형 차트를 선택해 추가합니다.
차트 제목과 눈금선을 제거해 차트를 간소화합니다. 차트 간소화에 대한 자세한 설명은 아래 차트 만들기 5단계 강의를 참고하세요.
추세선 추가하기 : 차트를 클릭하면 우측 상단에 [+] 버튼이 나타납니다. [+] 버튼을 클릭한 뒤 '추세선' 항목을 추가합니다.
추가된 추세선을 우클릭한 뒤 '추세선 서식'으로 이동합니다. 추세선 옵션에서 종류를 '다항식'으로 변경합니다.[오빠두Tip] 분석 목적에 따라 선형, 로그, 이동평균 등 다양한 추세선 옵션을 사용할 수 있습니다. 다항식은 최대 6차수까지 입력할 수 있으며, 이동평균은 차트 수식 표시를 지원하지 않으므로 주의해야 합니다.
추세선 서식 화면을 아래로 스크롤하면 '수식을 차트에 표시'와 'R-제곱 값을 차트에 표시' 옵션이 있습니다. 두 항목을 모두 체크하면 차트 위에 추세선 수식과 R제곱(결정계수) 값이 함께 표시됩니다.[오빠두Tip] 결정계수(R제곱)는 추세선이 실제 데이터를 얼마나 잘 반영하는지 보여 주는 0~1 사이의 지표입니다. 일반적으로 0.6 이상이면 추세선 수식이 데이터를 어느 정도 신뢰할 수 있게 설명한다고 해석합니다.

추세선 서식 변경하기

차트에 표시된 추세선 수식으로 데이터를 분석할 때 반드시 주의해야 할 점이 있습니다. 추세선으로 계산되는 수식은 소수점에 매우 민감하므로 표시형식을 '지수'에서 '숫자'로 바꿔 주어야 한다는 점입니다.

엑셀은 기본적으로 매우 작거나 큰 단위의 숫자를 입력하면 자동으로 지수형식(예: E-05, E+10)으로 표시형식을 변경합니다.

엑셀 일반 지수 표시 형식

하지만 추세선 수식은 작은 숫자라도 계산 결과에 큰 영향을 줄 수 있으므로, 분석 정확도를 높이기 위해 표시형식을 반드시 '숫자'로 변경해야 합니다.

추세선 수식 표시형식 변경하기 : 추세선의 수식을 우클릭한 뒤, '추세선 레이블 서식'으로 이동합니다.
레이블 서식의 표시형식에서 범주를 '숫자'로 변경한 뒤, 소수자리수를 충분히 큰 값(예: 30)으로 입력하면 추세선 수식 표시형식이 정상적으로 변경됩니다.

추세선으로 데이터 분석하기

이제 추세선 수식을 활용해 데이터를 분석해 보겠습니다. 이번 강의에서는 차트의 X축(가격)에 값을 입력해 Y축(수요)을 예측해 봅니다.

분석할 항목 추가하기 : 예제파일 B18셀부터 아래 항목을 차례대로 입력합니다. 입력 후 범위를 선택한 뒤 [홈] 탭 - [글꼴] 그룹 - [테두리] - [모든 테두리]를 클릭해 테두리를 적용합니다.[오빠두Tip] 모든 테두리는 Alt - H - B - A 단축키 조합으로 빠르게 적용할 수 있습니다.
원가는 3400, 가격은 8500으로 입력합니다.
추세선 수식 입력하기 : 차트의 추세선 수식을 복사한 뒤 예제파일 C20셀에 붙여넣기 합니다. 이후 수식 앞의 'y='를 지우고, x^2와 x를 C19셀(가격) 참조로 변경합니다.
= -0.0000103223585531978*C19^2 + 0.163277189013745*C19 - 603.652634684895
이제 가격을 변경하면 추세선 수식으로 예측된 예상 수요량이 자동으로 계산됩니다.
매출이익 분석하기 : 매출이익은 "(가격 - 원가) × 수요"로 계산됩니다. 예제파일 C21셀에 아래 수식을 입력하면 각 판매가격별 예측 매출이익이 계산됩니다.
숫자 표시형식 변경하기 : C18:C21 범위를 선택한 뒤 [홈] 탭 - [표시형식] - [쉼표 스타일](천 단위 구분기호 적용)을 클릭해 마무리합니다.

해찾기 추가기능으로 최대 매출이익 구하기

해찾기 추가기능을 사용하면 최고의 매출이익을 만드는 적정 판매가격을 자동으로 분석할 수 있습니다. 해찾기와 목표값 찾기의 동작 원리에 대한 보다 자세한 설명은 아래 관련 영상강의를 참고해 주세요.

해찾기 추가기능 활성화 : [파일] - [옵션] - [추가기능] - [Excel 추가 기능] - [해 찾기 추가기능]을 체크해 해찾기 추가기능을 활성화합니다.
해찾기 기능으로 최적의 매출이익 계산 : [데이터] 탭 - [해 찾기] 버튼을 클릭해 해찾기를 실행합니다. 이번 분석의 목표는 '최고의 매출이익'이므로, 목표 설정 셀에 C21셀(매출이익)을 입력한 뒤 대상으로 '최대값'을 선택합니다.
매출이익을 극대화하기 위해 변경할 변수는 가격입니다. 변수 셀에 C19셀(가격)을 입력합니다.
다음으로 제한 조건을 추가합니다. [추가] 버튼을 클릭해 아래 3가지 조건을 입력하고, 해법은 'GRG 비선형'을 선택한 뒤 [해 찾기] 버튼을 클릭합니다.
- C19 <= 9500 (가격은 9500원보다 작아야 한다.)
- C19 >= 3400 (가격은 3400원보다 커야 한다.)
- C19 = int (가격은 정수여야 한다.)
해 찾기 결과 확인 : 해찾기 분석 결과, 가격을 8,307원으로 책정할 때 수요가 40개 발생하며, 이때 매출이익이 198,163원으로 최대가 되는 것으로 계산되었습니다. [확인] 버튼을 클릭해 결과를 시트에 반영하면 분석이 마무리됩니다.

VBA로 추세선 쉽게 분석하는 법

엑셀 매크로 함수를 활용하면 차트 추세선 분석을 보다 편리하게 수행할 수 있습니다. 이번 강의에서는 TrendX 사용자지정함수를 사용해 추세선 분석을 자동화하는 방법을 살펴봅니다. TrendX 함수에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.

개발도구 활성화하기 : 예제파일에서 Alt + F11 키를 누르거나, [개발도구] 탭 - [Visual Basic] 버튼을 클릭해 매크로 편집창을 실행합니다. 만약 [개발도구] 탭이 보이지 않는 경우에는 아래 개발도구 활성화 포스트를 참고해 활성화한 후 진행하세요.

TrendX 함수 추가하기 : 매크로 편집기에서 [삽입] - [모듈]을 클릭해 새로운 모듈을 추가합니다. 추가된 모듈에 아래 명령문을 그대로 복사해 붙여넣기 합니다.

= TrendX ( [차트이름], [X값], [수식출력], [범례번호] )

Function TrendX(Optional ChartName As String, Optional Val As Double, Optional blnFormula As Boolean = False, Optional idx As Long = 1)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'수정 및 배포 시 출처를 반드시 명시해야 합니다.
 
'■ TrendX 함수
'■ 특정 차트의 추세선 수식을 출력하거나 X값에 따른 Y결과값을 계산합니다.
'■ 사용방법
'TrendX( 차트명, X값, [수식출력], [범레번호] )
'■ 인수 설명
'_____________ChartName         : [선택인수] 차트이름을 입력합니다. 값을 입력하지 않을 경우 시트 첫번째 차트를 대상으로 동작합니다.
'_____________Val               : [선택인수] Y 결과값을 도출할 X 값입니다. 기본값은 0 입니다.
'_____________blnFormula        : [선택인수]TRUE일 경우 추세선 수식을 반환합니다. FALSE일 경우 계산된 Y값을 반환합니다. 기본값은 FALSE 입니다.
'_____________idx               : [선택인수]차트에 여러 범례가 있을 경우, 추세선이 입력된 대상 범례 번호를 입렵합니다. 기본값은 1 입니다.
'###############################################################
Dim Cht As Chart: Dim WS As Worksheet
Dim strFormula As String: Dim result As Double
Dim strTemp As String
Dim arr As Variant: Dim i As Long
Dim vSplitsP As Variant: Dim vSplitP As Variant
Dim vSplitsM As Variant: Dim vSplitM As Variant
 
Set WS = Application.Caller.Parent
If Len(ChartName) = 0 Or IsMissing(ChartName) Then
    Set Cht = WS.ChartObjects(1).Chart
Else
    Set Cht = WS.ChartObjects(ChartName).Chart
End If
 
If Cht.SeriesCollection(idx).Trendlines.Count = 0 Then TrendX = CVErr(xlErrNull): Exit Function
 
With Cht.SeriesCollection(idx).Trendlines(1)
.DisplayRSquared = False
.DisplayEquation = True
strFormula = .DataLabel.Text
End With
 
If strFormula = "" Then TrendX = CVErr(xlErrNull): Exit Function
 
If blnFormula = True Then TrendX = strFormula: Exit Function
If InStr(1, strFormula, "ln(x)") > 0 Then
    ReDim arr(0 To 0)
    arr(0) = Application.WorksheetFunction.Ln(Val)
    strFormula = Replace(strFormula, "ln(x)", "*" & CStr(arr(0)))
    strFormula = Replace(strFormula, "(", "-")
    strFormula = Replace(strFormula, ")", "")
ElseIf InStr(1, strFormula, "e") > 0 Then
    ReDim arr(0 To 0)
    strFormula = Replace(strFormula, "(", "-")
    strFormula = Replace(strFormula, ")", "")
    i = InStr(1, strFormula, "e")
    arr(0) = Exp(CDbl(Replace(Right(strFormula, Len(strFormula) - i), "x", "")) * Val)
    strFormula = Left(strFormula, i - 1)
    strFormula = strFormula & "*" & arr(0)
Else
    For i = 1 To 6
    strFormula = Replace(strFormula, "x" & i, "x^" & i)
    Next
    strFormula = Replace(strFormula, "E-", "*10^|")
    strFormula = Replace(strFormula, "E+", "*10^")
    strFormula = Replace(strFormula, "x", "*" & Val)
End If
 
strFormula = Replace(strFormula, " ", "")
strFormula = Replace(strFormula, ",", "")
strFormula = Replace(strFormula, "y=", "")
 
vSplitsP = Split(strFormula, "+")
 
For Each vSplitP In vSplitsP
    vSplitsM = Split(vSplitP, "-")
    For i = 0 To UBound(vSplitsM)
        If i = 0 Then
            If vSplitsM(i) <> "" Then
 
            result = result + Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))
            End If
        Else
              result = result - Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))
        End If
    Next
Next
 
TrendX = result
 
End Function