Simulation in Excel
RAND() uniform 0 to 1
NORM.INV(RAND(), mean, sd) a normal draw
P(outcome) = COUNTIF(results, criterion) / number of trials
Whenever an input is uncertain. A Data Table with one row per trial turns one model into a thousand.
- deterministic input
- Known and fixed, e.g. price or unit cost
- probabilistic input
- Uncertain, given a distribution, e.g. demand
- trial
- One draw of every uncertain input, run through the model
The classical forecasting methods
Naive: F(t+1) = Y(t)
MA(k): F(t+1) = ( Y(t) + Y(t-1) + ... + Y(t-k+1) ) / k
WMA: F(t+1) = w1*Y(t) + w2*Y(t-1) + ..., sum of w = 1
ES: F(t+1) = alpha*Y(t) + (1 - alpha)*F(t)
= F(t) + alpha*( Y(t) - F(t) )
Trend: F(t) = b0 + b1*t
Choose from the components present. Naive is the benchmark; a moving average lags a trend; seasonality needs dummies or deseasonalising.
- k
- Periods in the moving average; larger smooths more and lags more
- alpha
- Between 0 and 1; the fraction of the last error that is corrected
- t
- Time index 1, 2, 3, ... for a trend regression
Forecast accuracy
e(t) = Y(t) - F(t)
MAD = mean of |e(t)|
MSE = mean of e(t)^2
RMSE = sqrt(MSE)
MAPE = mean of ( |e(t)| / Y(t) ) x 100
bias = mean of e(t), signed
On held-back periods, never on the data the method was tuned to. Use MAPE to compare across series.
- MAD / RMSE
- In the data's own units, so not comparable between series
- MAPE
- Unitless percentage; undefined when an actual is zero
- bias
- Signed average error; consistently positive means under-forecasting
Bass diffusion model
S(t) = ( p + q * N(t-1)/m ) * ( m - N(t-1) )
year 1, with N = 0: S(1) = p * m
Forecasting a product with no sales history. Take p and q from an analogous product and m from market research.
- m
- Total market potential: everyone who will ever buy
- p
- Coefficient of innovation; drives sales when nobody owns one
- q
- Coefficient of imitation; multiplied by the ownership share N/m
- N(t-1)
- Cumulative sales up to the end of the previous period