Mean Absolute Error (MAE)

What is Mean Absolute Error?

Mean Absolute Error is the average of the absolute differences between your forecasted values and the actual values. It tells you, on average, how far off your predictions are.

Why It Matters:

A smaller MAE indicates that your forecasts are closer to the real numbers, which means better planning for production, inventory, and distribution.

Key Principles of MAE

• Absolute Differences:
  Instead of looking at the raw differences (which could cancel each other out if some errors are positive and others negative), MAE uses the absolute value. This means every deviation contributes positively to the error measurement.
• Averaging Errors:
  After calculating the absolute errors for each data point, you take the average. This average gives you one clear number representing your forecast’s accuracy.
• Simple Calculation:
  Mathematically, if you have a list of forecast errors, the MAE is calculated as:
  MAE = (|Actual₁ - Forecast₁| + |Actual₂ - Forecast₂| + ... + |Actualₙ - Forecastₙ|)

Calculating MAE in Excel: Step-by-Step

Step 1: Organize Your Data

• Columns Needed:
  Create two columns in Excel: one for your Actual demand values and one for your Forecast values.

Step 2: Compute Absolute Errors

• Formula:
  In a new column (say, “Error”), use the formula:
  =ABS(Actual - Forecast)
• Example:
  If your actual value is in cell B2 and your forecast is in cell C2, type in D2:
  =ABS(B2 - C2)

Step 3: Calculate the Average Error

• Using Excel's AVERAGE Function:
  At the bottom of your "Error" column, use:
  =AVERAGE(D2:Dn)
  where Dn is the last cell in your error list.

Common Pitfalls When Using MAE

• Ignoring Outliers:
  Extreme values can skew your error measurements. Consider filtering out outliers or using complementary metrics.
• Relying Solely on MAE:
  MAE gives you an overall sense of error but doesn’t indicate if your forecasts are consistently too high or too low. Using MAE along with other metrics like Mean Absolute Percentage Error (MAPE) or bias can provide a fuller picture.
• Misinterpreting the Numbers:
  Remember that MAE should be interpreted relative to your data’s scale. An MAE of 10 units might be excellent for one dataset and poor for another.
• Over-Complexity in Excel:
  While Excel is powerful, ensure your formulas and data are correctly set up. Simple mistakes (like referencing the wrong cells) can lead to misleading MAE calculations.

Shower Thoughts

• How can you adjust your forecasting model or data collection to reduce your MAE and improve planning accuracy?

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!