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 1 of 25
The ideaTheory

One number hides the risk

A bakery expects to sell 100 cakes a day and asks whether to bake 100. Average profit looks fine.

But demand is not 100 every day. Some days it is 60 and the extra cakes are thrown away; some days it is 140 and sales are lost. The average answer never mentions either.

Simulation replaces the single number with a thousand possible days and reports the whole spread: the average, yes, but also how often you lose money. Check against your class slides.

← BackContinue →