Skip to content
Sem 5 Prep
← BI & Data ScienceMonte Carlo simulation and forecasting new products

Formulas for this chapter

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
Step 4 of 25
The real wordsPracticalUnverified

Generating a random value in Excel

RAND() uniform between 0 and 1 NORM.INV(RAND(), mean, sd) a normal draw NORM.INV(RAND(), 100, 20) demand, mean 100, sd 20 IF(RAND() < 0.3, "yes", "no") a 30 % event

RAND() supplies the randomness; the inverse distribution function turns it into the shape you want.

Every recalculation redraws every cell, which is what makes a Data Table with a thousand rows into a thousand trials.

← BackContinue →