Exponential Soothing

What Is Exponential Smoothing?

At its core, exponential smoothing calculates the next forecast using a weighted average of the current actual demand and the previous forecast. The formula is:

  Forecast₍ₜ₊₁₎ = α × Actual₍ₜ₎ + (1 − α) × Forecast₍ₜ₎

Where:

• α : Smoothing constant between 0 and 1.
• Actual₍ₜ₎ : Actual demand at time t.
• Forecast₍ₜ₎ : Forecast for time t.

How does it work?

Imagine you’re trying to predict next month’s sales. You have sales numbers from previous months, but not every month is the same. Instead of treating all past months equally, exponential smoothing gives more weight to the sales from the most recent month. This makes your forecast more responsive to recent changes.

Here’s the basic idea in plain language:

1. Start with an Initial Forecast:
   You need a starting point, often taken as the first month’s sales or an average of early months.
2. Apply the Smoothing Formula:
   For each new month, the forecast is calculated by taking a fraction of the actual sales from the previous month and adding it to a fraction of the previous forecast. The formula looks like this:

• New Forecast = (α × Last Month’s Actual Sales) + ((1 - α) × Last Month’s Forecast)
• α (Alpha): This is a number between 0 and 1 that decides how much weight to give to the most recent actual sales.
  • If α is close to 1, you focus more on recent sales.
  • If α is close to 0, you rely more on the previous forecast, making it less sensitive to recent changes.

Real-World Example

Imagine a small store that sells 100 units in January. For simplicity, we’ll start with January as both the actual and the initial forecast. Now, in February, the store sells 120 units. If we choose an α of 0.3 (30%), the forecast for February (which we calculate at the end of January for February) would be:

• Initial Forecast (January): 100 units
• Actual Sales (January): 100 units (since it's the starting point)
• Forecast for February:
  = 0.3 × (January’s Actual) + 0.7 × (January’s Forecast)
= 0.3 × 100 + 0.7 × 100
= 30 + 70 = 100 units

Now, suppose in February, the actual sales are 120 units. Then, the forecast for March would be:

• Forecast for March:
  = 0.3 × (February’s Actual: 120) + 0.7 × (February’s Forecast: 100)
= 36 + 70 = 106 units

This method continuously adjusts the forecast, allowing the latest changes in sales to influence the prediction more heavily while still being anchored by past trends.

Why Is It Useful?

• Simple to Understand and Use:
  No complicated math is required. Just a basic formula that can be implemented in Excel, Python or Power BI
• Responsive to Recent Changes:
  It quickly adapts if there is a sudden increase or decrease in demand.
• Smooths Out Noise:
  By averaging out fluctuations, it helps you see the underlying trend in your data.

Common Challenges

• Choosing the Right α:
  The smoothing constant is crucial. Too high, and your forecast might be too jumpy; too low, and it might not react quickly enough to changes.
• Initial Value Sensitivity:
  The first forecast value can impact the results in the early stages, so choosing a good starting point is important.

Step-by-step guide on building an exponential smoothing demand forecast in Excel

Step 1: Prepare Your Data Fields

Your Excel workbook should contain the following columns:

• SKU (A): The product identifier.
• Period/Date (B): The period for each record (e.g., Month, Week, or Date).
• Region (C): The sales region (e.g., North, South).
• Country (D): The country where the sales occurred.
• Sales Quantity (Actual Demand) (E): The actual number of units sold.
• Forecast (F): The predicted demand for the period, calculated with exponential smoothing.
• Absolute Error (G): The absolute difference between actual sales and forecast.
• Percentage Error (H): The absolute error as a percentage of the actual sales.

You’ll also use a dedicated cell (for example, cell J1) to store the smoothing constant, Alpha (α).

Step 2: Input and Organize Your Data

1. Create a New Worksheet:
   Open Excel and start with a blank workbook.
2. Enter Your Data:
   • SKU (Column A): Fill in the SKU for each product.
   • Period (Column B): List the periods (e.g., Jan-2023, Feb-2023, etc.).
   • Region (Column C) & Country (Column D): Enter the corresponding region and country for each row
   • Sales Quantity (Column E): Input the actual demand numbers for each period.
3. Initial Forecast (Column F):
   • For the very first period of each SKU, set the forecast equal to the actual sales.
   • For example, if SKU001 in January has 100 units, enter 100 in F2.
4. Alpha (Smoothing Constant):
   • In cell J1, enter your initial alpha value. A common starting point is 0.3.

Step 3: Enter the Exponential Smoothing Formula

1. In the Forecast Column (F):
   Calculate the forecast using the exponential smoothing formula for every period after the initial period. In cell F3(for the same period), enter:

= $J$1 * E2 + (1 - $J$1) * F2

Here, E2 is the actual sales from the previous period, F2 is the previous forecast, and $J$1 is the alpha value (using absolute references so it doesn’t change when you copy the formula).

Copy the Formula Down:
Drag the formula from F3 down to all subsequent rows for the same SKU.

• If you have multiple SKUs in one worksheet, consider using filters or separate sections to ensure the formula resets when the SKU changes.

Step 4: Calculate Forecast Errors

1. Absolute Error (Column G):
   In cell G3, enter:

=ABS(E3 - F3)

2. Percentage Error (Column H):
   In cell H3, enter:

=IF(E3=0, 0, ABS((E3 - F3) / E3) * 100

And copy this formula down as well.

Overall Error Metrics:
At the bottom of your table or in a summary area, calculate:

• Mean Absolute Error (MAE)

=AVERAGE(G3:G[n])

Mean Absolute Percentage Error (MAPE)

=AVERAGE(H3:H[n])

Replace [n] with the last row number of your data

Step 5: Optimize Alpha Using Historical Data (MAPE)

5.1: Prepare for Optimization

Summary Cell for MAPE:

• Choose a cell (for example, K1) to hold the MAPE value calculated from your historical data.
• Enter the formula for MAPE in K1

=AVERAGE(H3:H[n])

Set Up a Data Range:
Ensure your data range covers all relevant periods for the SKU (or aggregate across SKUs if needed).

5.2: Use Excel Solver to Find the Optimal Alpha

1. Enable Solver:
   • Go to File > Options > Add-ins and ensure Solver is enabled.
2. Open Solver:
   • Navigate to the Data tab and click on Solver.
3. Set the Solver Parameters:
   • Objective: Set cell K1 (which contains MAPE) as the target to minimize.
   • Variable Cell: Set cell J1 (alpha) as the variable cell.
   • Constraints: Add a constraint to ensure alpha stays between 0 and 1

0 <= $J$1 <= 1

Run Solver:
Click Solve. Solver will adjust the alpha value in J1 to minimize the MAPE calculated in K1.

Review Results:
Once Solver finishes, the optimized alpha value will be displayed in J1. Review the new forecast errors to confirm that the error metric (MAPE) has improved.

Step 6: Visualization and Further Analysis

1. Create a Pivot Table (Optional):
   If your dataset includes multiple SKUs, regions, and countries, create a pivot table to analyze forecasts by these categories.
   • Drag fields such as SKU, Region, and Country into the rows and aggregate Sales Quantity, Forecast, and Errors as needed.
2. Build a Line Chart:
   • Highlight your Period, Sales Quantity, and Forecast columns.
   • Insert a Line Chart to visualize the actual demand versus your forecast.
3. Analyze by Region/Country:
   Use slicers or filters in Excel to dynamically view forecasting performance by Region or Country.

Ready to dive even deeper into supply chain insights? Sign up now for our newsletter and get clear, actionable articles delivered straight to your inbox—no spam, just valuable tips to boost your forecasting and supply chain strategies. Join our community today!