Revenue, Margin and Volume Trend Last 12 Months
First, the respective metric (revenue, margin or volume) is calculated for every single time unit.
Customer level: time unit = month
Product level: time unit = month
The least-square approach is used to compute the trend.
For more information see the Fitting a trend: Least-squares (Wikipedia).
Formula
Formula Text Version
â = [Σᵢ₌₁ⁿ (xᵢ – x̄)(yᵢ – ȳ)] / [Σᵢ₌₁ⁿ (xᵢ – x̄)²]
-
x is the month number (like month 1 would be the first month in scope)
-
x̄ is the average month number
-
y is the metric to consider, like margin
-
ȳ is the average metric to consider, like average margin
To convert the trend value â back to a percentage, the following formula is used: â / average(metric)
Periods with no transactions are considered to have zero revenue, margin, and volume, and are included in the trend computation.
Example
Find the example in the the following attachment:
Trend Value with least square .xlsx