Excel에서 Holt-Winters 지수 평활화로 미래 매출 예측하기(11단계 완벽 가이드)

Excel은 방대한 데이터 세트를 다룰 때 가장 널리 사용되는 도구입니다. 다양한 차원의 수많은 작업을 Excel에서 수행할 수 있는데요. 이 글에서는 Excel에서 Holt-Winters 지수 평활화(Holt-Winters Exponential Smoothing)를 적용하는 방법을 단계별로 자세히 설명해 드리겠습니다. 이 기법은 시계열 데이터를 활용한 미래 예측에 특히 유용합니다.

Holt-Winters 지수 평활화란 무엇인가?

Holt-Winters 기법은 값을 예측하는 고급 시계열 분석 방법입니다. 이 방법은 예측 과정에서 계절성(seasonality)과 추세(trend) 효과를 모두 고려하기 때문에, 일부 무작위 변동을 제외하면 실제 값에 매우 근접한 예측 결과를 얻을 수 있습니다.

Excel에서 Holt-Winters 지수 평활화를 사용해 예측값을 계산하는 공식은 다음과 같습니다.

Ft+k = (Lt+k × Tt) × St-m+k

각 항목의 의미는 다음과 같습니다.

• F = 예측값(Forecasted Value)
• L = 수준(Level)
• T = 추세(Trend)
• M = 분기 데이터일 경우 4, 월별 데이터일 경우 12
• S = 계절 지수(Seasonality Index)

Excel에서 Holt-Winters 지수 평활화 수행하는 11단계

이번 실습에 사용할 데이터 세트는 2022년까지의 분기별 매출 데이터입니다.

우리는 이 데이터를 바탕으로 2023년의 예측값을 계산해 보겠습니다.

1단계: 알파, 베타, 감마 값 임의 설정

가장 먼저 할 일은 상수인 알파(alpha), 베타(beta), 감마(gamma)에 임의의 값을 할당하는 것입니다.

이 값들은 나중에 최적화 과정을 통해 조정하게 됩니다.

2단계: 초기 계절 지수 계산

다음으로 처음 4개 분기에 대한 초기 계절 지수를 구합니다. 초기 계절 지수는 각 분기의 매출을 첫 4개 분기의 평균 매출로 나누어 산출하며, 이때 AVERAGE 함수를 활용합니다.

• 셀 F11로 이동하여 다음 수식을 입력합니다.

• ENTER 키를 누르면 Excel이 결과를 반환합니다.

• 이후 채우기 핸들(Fill Handle)을 사용해 F14까지 자동 채우기(AutoFill)합니다.

3단계: 초기 수준과 추세 산출

이제 데이터 세트의 초기 수준(Level)과 추세(Trend)를 계산할 차례입니다.

연간 4개 분기로 구성되어 있으므로, 초기 수준은 5번째 분기를 기준으로 정합니다.

초기 수준의 계산 공식은 다음과 같습니다.

L5 = Y5 / S1

여기서 Y5는 5번째 분기의 매출, S1은 1분기의 계절 지수를 의미합니다.

• 셀 D15에 아래 수식을 입력합니다.

• ENTER 키를 눌러 결과를 확인합니다.

이어서 초기 추세(역시 5번째 분기 기준)를 계산합니다. 초기 추세의 공식은 다음과 같습니다.

T5 = L5 − Y4/S4

여기서 L5는 5번째 분기의 수준, Y4는 4번째 분기의 매출, S4는 4분기의 계절 지수입니다.

• 셀 E15에 아래 수식을 입력합니다.

• ENTER 키를 눌러 결과를 얻습니다.

4단계: 다음 계절 지수들 계산

이제 일반 공식을 활용해 이후 분기들의 계절 지수를 구합니다. 계절 지수의 일반 공식은 다음과 같습니다.

St = γ(Yt/Lt) + (1−γ) St-m

여기서 각 변수의 의미는 다음과 같습니다.

• L = 수준(Level)
• T = 추세(Trend)
• M = 분기 데이터일 경우 4, 월별 데이터일 경우 12
• S = 계절 지수
• γ = 감마 계수

• 셀 F15에 다음 수식을 입력합니다.

• ENTER 키를 누릅니다.

• F22까지 자동 채우기합니다.

참고: 이 단계에서 오류가 표시되더라도 당황하지 마세요. 다음 단계에서 새로운 수준과 추세를 계산하면 자동으로 해결됩니다.

5단계: 다음 수준(Level) 산출

이제 다음 공식을 사용해 이후 분기들의 수준을 구하는 방법을 알아보겠습니다.

Lt = α(Yt/St-m) + (1−α)(Lt-1 + Tt-1)

여기서 각 변수의 의미는 다음과 같습니다.

• L = 수준(Level)
• T = 추세(Trend)
• M = 분기 데이터일 경우 4, 월별 데이터일 경우 12
• S = 계절 지수
• α = 알파 계수

• 셀 D16에 다음 수식을 입력합니다.

• ENTER 키를 누릅니다.

• D22까지 자동 채우기합니다.

6단계: 다음 추세(Trend) 측정

이번에는 추세 효과를 계산합니다. 공식은 다음과 같습니다.

Tt = β(Lt − Lt-1) + (1−β) Tt-1

여기서 각 변수의 의미는 다음과 같습니다.

• L = 수준(Level)
• T = 추세(Trend)
• M = 분기 데이터일 경우 4, 월별 데이터일 경우 12
• S = 계절 지수
• β = 베타 계수

• 셀 E16에 다음 수식을 입력합니다.

• ENTER 키를 눌러 진행합니다.

• E22까지 자동 채우기합니다.

7단계: 실제 매출과 비교할 예측값 산출

이제 실제 매출과 비교하기 위한 예측값을 계산합니다. 첫 번째 예측 대상은 6번째 분기입니다. 비교용 예측값의 계산 공식은 다음과 같습니다.

Ft = (Lt-1 + Tt-1) × St-M

• 셀 G16에 다음 수식을 입력합니다.

• ENTER 키를 누릅니다.

• G22까지 자동 채우기합니다.

8단계: 예측 오차 계산

이제 실제 매출에서 예측값을 빼서 예측 오차(forecasting error)를 계산합니다.

• 셀 H16에 다음 수식을 입력합니다.

• ENTER 키를 누릅니다.

• H22까지 자동 채우기합니다.

9단계: 예측 대상 분기의 K값 지정

이제 본격적인 예측을 시작할 차례입니다. 그 전에 계수 k(co-efficient k)의 의미를 이해해야 합니다. k는 예측하려는 미래 시점을 나타냅니다. 우리는 2023년의 4개 분기를 예측하고 있으며, 2022년까지의 데이터가 준비되어 있습니다.

따라서 2023년 1분기의 k값은 1, 2분기는 2, 이런 식으로 순차적으로 증가합니다.

10단계: 최종 예측값 계산

이제 예측값을 계산할 준비가 되었습니다. 마지막으로 확보된 수준, 추세, 계절성 값을 활용해 예측을 수행합니다.

• 셀 G23에 다음 수식을 입력합니다.

• ENTER 키를 눌러 결과를 확인합니다.

• G25까지 자동 채우기합니다.

11단계: 알파, 베타, 감마 값 최적화

마지막으로 오차를 최소화하기 위해 알파, 베타, 감마 값을 최적화합니다. 이 작업에는 Excel 솔버(Solver)를 활용합니다.

• 먼저 평균 제곱근 오차(RMSE)를 계산해야 합니다. 셀 C7에 다음 수식을 입력합니다.

수식 분석:

• COUNT(H15:H21) → 셀의 개수를 셉니다.
  • 결과 → 7
• SUMSQ(H15:H21) → H15:H21 범위 값들의 제곱합을 계산합니다.
  • 결과 → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE(평균 제곱근 오차)를 산출합니다.

• ENTER 키를 누릅니다.

• 이제 데이터(Data) 탭으로 이동하여 솔버(Solver)를 선택합니다.

• 솔버 매개 변수(Solver Parameters) 창이 열립니다. 오차를 최소화하는 것이 목표이므로, 목표를 RMSE 최소화로 설정하고 계수 값 변경을 통해 이를 달성하도록 지정합니다.
• 그다음 제약 조건을 추가하기 위해 추가(Add) 버튼을 클릭합니다.

• 제약 조건 추가 창이 나타납니다. 제약 조건은 0 ≤ α, β, γ ≤ 1입니다. 첫 번째 제약 조건을 추가하려면 셀 참조와 값을 설정하세요. (아래 이미지 참조)

• 같은 방식으로 두 번째 제약 조건까지 추가하면 다음과 같은 화면이 됩니다. 이후 풀기(Solve)를 클릭합니다.

• Excel이 알파, 베타, 감마를 최적화하여 오차를 최소화합니다.

주의사항

• 솔버 애드인(solver add-in)을 반드시 활성화해야 합니다.
• 실제 매출과 비교할 예측값을 계산할 때는 k값을 고려할 필요가 없습니다.

마무리

이 글에서는 Excel에서 Holt-Winters 지수 평활화를 적용하는 방법을 11단계에 걸쳐 자세히 살펴보았습니다. 계절성과 추세를 모두 반영하는 이 강력한 예측 기법을 통해 더욱 정확한 매출 예측이 가능해질 것입니다. 궁금한 점이 있다면 언제든지 댓글로 문의해 주세요.