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

차트 추세선과 해찾기 기능을 활용한 최대 매출이익 판매가 분석 및 VBA 매크로를 활요한 업무 자동화까지 모두 살펴봅니다.

홈페이지 » 최대 매출이익 판매가격 엑셀로 쉽게 구하는 방법 | 매크로까지 총정리

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

매출이익 판매가격 분석 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 최대 매출이익을 위한 판매가격 분석
    예제파일
  • [엑셀고급] 최대 매출이익을 위한 판매가격 분석
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


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

수요-공급 곡선 및 가격-매출이익 분석에 필요한 기초 이론은 이전 영상강의에서 자세히 다뤄드렸습니다. 이전 강의를 못 보고 오신 분이라면 이전 영상강의를 참고해주세요.

가격-수요 예측을 위한 차트 생성 및 추세선 추가

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

  1. 차트 만들기 : 예제파일의 F10:G15 범위(가격:판매량)을 선택합니다. [삽입] - [추천 차트]로 이동합니다.

    엑셀 분산형 차트 만들기
    범위를 선택 후 삽입 - 추천차트로 이동합니다.

  2. 차트 삽입 대화상자가 나오면, '추천차트' 또는 '모든차트'에서 분산형 차트를 추가합니다.

    엑셀 분산형 차트 추가
    차트 목록에서 분산형 차트를 추가합니다.

  3. 차트의 제목과 눈금선을 지워 차트를 간소화합니다. 차트 간소화 방법에 대한 보다 자세한 설명은 아래 차트만들기 5단계 강의를 참고하세요.

    엑셀 차트 간소화
    차트를 간소화합니다.

  4. 추세선 추가하기 : 차트를 클릭하면 차트 오른쪽 상단에 [+] 버튼이 나타납니다. [+] 버튼 클릭 후, '추세선'을 추가합니다.

    차트 요소 추세선 추가
    + 버튼 클릭 후, 추세선 요소를 추가합니다.

  5. 추세선을 우클릭한 뒤, '추세선 서식'으로 이동합니다. 추세선 옵션에서 종류를 '다항식'으로 변경합니다.
    추세선 옵션 다항식 변경
    추세선 옵션에서 종류를 다항식으로 변경합니다.

    [오빠두Tip] 필요에 따라 선형, 로그, 및 이동평균 등 다양한 추세선 옵션으로 활용할 수 있습니다. 다항식은 최대 6차수까지 입력할 수 있습니다. 단, 이동평균은 차트 수식을 지원하지 않습니다.

  6. 추세선 서식 아래로 이동하면 '수식을 차트에 표시'와 'R-제곱 값을 차트에 표시'가 있습니다. 각 체크박스를 체크하면 차트 위로 수식과 R제곱(결정계수)가 표시됩니다.
    추세선 수식 및 r제곱 차트 표시
    추세선 서식에서 수식과 R제곱을 차트에 표시합니다.

    [오빠두Tip] 결정계수는 추세선이 실제 데이터를 얼마나 반영할 수 있는지를 나타내는 0~1 사이의 숫자입니다. 보통 0.6 보다 크면 수식이 어느정도 데이터를 반영한다고 해석합니다.

차트 추세선 분석 시 주의사항

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

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

엑셀 일반 지수 표시 형식
엑셀은 아주 크거나 작은 숫자를 자동으로 지수형태로 변경합니다.

하지만 추세선으로 데이터를 분석할 때에는 아주 작은 숫자라도 계산 결과에 큰 영향을 줄 수 있으므로 반드시 표시형식을 숫자로 변경해야 합니다.

  1. 추세선 수식 표시형식 변경하기 : 추세선의 수식을 우클릭 한 뒤, '추세선 레이블 서식'으로 이동합니다.

    엑셀 추세선 레이블 서식 이동
    추세선 수식을 우클릭 - 추세선 레이블 서식으로 이동합니다.

  2. 추세선 표시형식에서 범주를 '숫자'로 변경합니다. 이후 소수자리수를 넉넉한 소수자리수(예: 30) 으로 입력하면 추세선 수식 표시형식이 변경됩니다.

    추세선 수식 표시형식 숫자 변경
    추세선 수식의 표시형식을 숫자로 변경 한 후, 자리수를 30으로 입력합니다.

추세선을 활용한 데이터 분석 방법

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

  1. 분석할 항목 추가하기 : 예제파일 B18셀부터 아래 항목을 차례대로 입력합니다. 이후 범위를 선택 한 뒤, [홈] 탭 - [글꼴] 그룹 - [테두리] - [모든 테두리]를 선택하여 테두리를 적용합니다.
    추세선 분석 항목 입력
    추세선 분석을 위한 항목을 각 셀에 추가합니다.

    [오빠두Tip] 모든 테두리 추가하기는 Alt - H - B - A 로 빠르게 적용할 수 있습니다.

  2. 원가는 3400, 가격은 8500으로 입력합니다.

    엑셀 매출이익 판매가겨 분석 원가 가격
    원가는 3,400원, 가격은 8,500원으로 입력합니다.

  3. 추세선 수식 입력하기 : 차트 추세선 수식을 복사한 뒤, 예제파일 C20셀에 붙여넣기 합니다. 이후 수식 앞에 y를 지워준 후, 수식의 x^2와 x를 C19셀(가격)으로 변경합니다
    = -0.0000103223585531978*C19^2 + 0.163277189013745*C19 - 603.652634684895

    엑셀 매출이익 판매가격 추세선 수식
    추세선 수식을 복사해서 수요 셀에 붙여넣기 한 후, 수식을 수정합니다.

  4. 이제 가격을 변경하면 추세선 수식으로 예측된 예상 수요량이 자동으로 계산됩니다.

    엑셀 매출이익 적정 판매가격 수요 예측
    가격을 입력하면 예측된 수요가 자동 계산됩니다.

  5. 매출이익 분석하기 : 매출이익은 "(가격-원가)*수요"로 계산됩니다. 예제파일의 C21셀에 아래 수식을 입력하면 각 판매가격별 예측되는 매출이익이 계산됩니다.

    매출이익 계산
    매출이익을 계산합니다.

  6. 숫자 표시형식 변경하기 : C18:C21 범위를 선택한 후, [홈]탭 - [표시형식] - [쉼표 스타일] (천단위 구분기호가 추가된 숫자)로 변경해서 마무리합니다.

    엑셀 숫자 표시형식 변경
    범위의 표시형식을 변경하여 추세선 분석을 마무리합니다.

해찾기 추가기능으로 최적의 판매가 계산하기

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

  1. 해찾기 추가기능 활성화 : [파일] - [옵션] - [추가기능] - [Excel 추가 기능] - [해 찾기 추가기능]을 체크하여 해찾기 추가기능을 활성화 합니다.

    엑셀 해찾기 추가기능 추가
    해찾기 추가기능을 추가합니다.

  2. 해찾기 기능으로 최적의 매출이익 계산 : [데이터] 탭 - [해 찾기] 버튼을 클릭하면 해찾기가 실행됩니다. 목표는 '최고의 매출이익' 입니다. 따라서 목표 설정 셀로 C21셀(=매출이익)을 입력 후, 대상으로 '최대값'을 선택합니다.

    해찾기 목표셀 설정
    해찾기를 실행한 후, 목표 셀을 설정합니다.

  3. 매출이익을 구하기 위해 바꿔줄 변수는 가격입니다. 변수 셀로 C19셀(=가격)을 입력합니다.

    해찾기 변수 입력
    변수 셀로 판매가격셀을 입력합니다.

  4. 제한 조건을 추가합니다. [추가] 버튼을 클릭하여 아래 3개 조건을 추가합니다. 해법은 'GRG 비선형'을 선택한 후, [해 찾기] 버튼을 클릭합니다.
    - C19 <= 9500  (가격은 9500원보다 작아야 한다.)
    - C19 >= 3400  (가격은 3400원보다 커야 한다.)
    - C19 = int       (가격은 정수여야 한다.)

    엑셀 해찾기 조건 입력
    해찾기 과정에 적용할 조건을 입력합니다.

  5. 해 찾기 결과 확인 : 해찾기 결과로 가격이 8,307원이면 40개의 수요가 발생하고 그럴 경우 매출이익이 198,163원으로 최대가 되는 것으로 계산하였습니다. [확인] 버튼을 클릭하여 계산을 마무리합니다.

    최고 매출이익 판매가격 계산 완료
    확인 버튼을 누르면 해찾기가 실행됩니다. 결과값이 맞으면 확인버튼을 눌러 작업을 마무리합니다.

엑셀 TrendX 함수 사용방법

엑셀 매크로 함수를 사용하면 차트 추세선 분석을 보다 편리하게 할 수 있습니다. 이번 강의에서는 TrendX 함수를 사용하여 차트 추세선 분석을 보다 편리하게 하는 방법을 소개해드리겠습니다. TrendX 함수에 대한 보다 자세한 설명은 아래 관련 포스트를 확인하세요.

  1. 개발도구 활성화하기 : 예제파일에서 키보드 Alt + F11 키를 누르거나, [개발도구] - [Visual Basic] 버튼을 클릭하여 매크로 편집창을 실행합니다. 만약 개발도구가 안 보일 경우, 아래 개발도구 활성화 관련 포스트를 참고하여 개발도구를 활성화합니다.
  2. 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
  3. 다시 예제파일 시트로 돌아온 뒤, C20셀(수요)에 아래 수식을 입력합니다.
    =TrendX(,C19)

    엑셀 trendx 함수 예제
    가격별 예측 수요를 TrendX 함수로 계산합니다.

  4. 가격에 대한 수요가 실시간으로 계산됩니다.

    엑셀 trendx 함수 결과
    가격을 변경하면 추세선 수식으로 계산된 예측수요가 실시간으로 계산됩니다.

5 1 투표
게시글평점
guest
3 댓글
Inline Feedbacks
모든 댓글 보기
SeanniSean
SeanniSean
2021년 5월 7일 1:07 오후
게시글평점 :
     

강의 잘 봤습니다.

나비
나비
2021년 5월 7일 11:22 오후
답글 남기기  SeanniSean

감사합니다~

신일진
신일진
2021년 7월 20일 10:50 오전
게시글평점 :
     

최고입니다.^^

3
0
여러분의 생각을 댓글로 남겨주세요.x