(DOWNLOADABLE VERSION TO THE RIGHT)
Projection Documentation
The Projection uses one of the following methods to calculate projected values: Holt-Winters Exponential Smoothing, Exponential Smoothing, Trend Analysis, or Direct Calculation.
Holt-Winters Exponential Smoothing
The Holt-Winters Smoothing Algorithm uses weighted historical trending to predict the future values of an account. It is more accurate for accounts that tend to trend in one direction over time. The modified version of this algorithm looks at the financial data from past years and determines a value to place on the trend itself. For example, if a company’s sales rises for 3 consecutive periods, we will weight the trend value more than if sales oscillates over the 3 periods. The following variables are used in this calculation:
Variables
alpha: weight to place on previously predicted values (0 < alpha < 1)
(1-alpha): weight to place on the most recent actual value
beta: weight to place on historical trend (0 < beta < 1)
(1-beta): weight to place on most recent trend
tw: weight to place on the overall trend
at = weighted average component of the forecast at time t for the period t+1
tt = trend component of the forecast at time t for the period t+1 (expected increase from
time t to time t+1)
ft = at + (tt * tw) = forecast at time t for the period t+1
Xt = actual value at time t
Calculation
Step 1: Initialize a, t, and f using oldest historical data
a2 = X2
t2 = X2 - X1
f2 = a2 + (t2 * tw)
Step 2: Iteratively calculate a, t, and f
a3 = alpha * f2 + (1-alpha) * X3
t3 = beta * t2 + (1-beta) * (a3 - a2)
f3 = a3 + (t3 * tw)
an = alpha * fn-1 + (1-alpha) * Xn
tn = beta * tn-1 + (1-beta) * (an - an-1)
fn = an + (tn * tw)
Example
Suppose we had the following historical data for Sales:
Sales2005 = $5,000 [X3]
Sales2004 = $2,500 [X2]
Sales2003 = $1,000 [X1]
For simplicity, we will let alpha=0.3 and beta=0.3. Since sales rose all three years, we will assign tw to be 1 (its greatest possible value)
Step 1: Initialize a, t, and f using oldest historical data
a2 = X2
a2 = $2,500
t2 = X2 - X1
t2 = $2,500 - $1,000 = $1,500
f2 = a2 + (t2 * tw)
f2 = $4,000
Step 2: Iteratively calculate a, t, and f
a3 = alpha * f2 + (1-alpha) * X3
a3 = 0.3 * $4,000 + 0.7 * $5,000 = $4,700
t3 = beta * t2 + (1-beta) * (a3 - a2)
t3 = 0..3 * $1,500 + 0.7 * ($4,700 - $2,500) = $1,990
f3 = a3 + t3 * tw
f3 = $4,700 + ($1,990 * 1) = $6,690
So, our prediction for Sales2006 would be $6,690
Exponential Smoothing
Exponential smoothing is a forecasting method that relies on a weighted average of historical data values with the more recent values carrying more weight. The following variables are used in this calculation:
Variables
alpha: weight to place on previously predicted values (0 < alpha < 1)
(1-alpha): weight to place on the most recent actual value
ft = forecast at time t for the period t+1
Xt = actual value at time t
The Exponential Smoothing Algorithm is computed as follows:
Calculation
Step 1: Initialize f1 using oldest historical data
f1 = X1
Step 2: Iteratively calculate ft from historical data
f2 = (alpha*f1) + (1-alpha) * X2
ft = (alpha*ft-1) + (1-alpha) * Xt
Example
Gross Profit Margin2005 = 58% [X3]
Gross Profit Margin2004 = 45% [X2]
Gross Profit Margin2003 = 60% [X1]
For this example, we will let alpha=0.3
Step 1: Initialize f using oldest historical data
f1 = X1
f1 = 60%
Step 2: Iteratively calculate f
f2 = (alpha * f1) + (1-alpha) * X2
f2 = (0.3 * 60) + (1-0.3) * 45 = 49.50
f3 = (alpha * f2) + (1-alpha) * X3
f3 = (0.3 * 49.50) + (1-0.3) * 58 = 55.45
So, our prediction for Gross Profit Margin2006 would be 55.45%
Trend Analysis
a2 = Amortization Percent12/31/2006
t2 = Amortization Percent12/31/2006 - Amortization Percent12/31/2005
f2 = a2 + t2
Direct Calculation
Calculated accounts do not need to be predicted separately because their values are dictated by financial formulas (for example, Gross Profit = Sales - Cost of Sales). For these accounts, we simply determine the expected values for each account in the associated formula and then compute the result of the formula.
Calculation Methods for Each Account
A table detailing the method used to calculate the projected values for each account can be seen in the attached PDF.