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 2 of 25
The real wordsTheory

Risk analysis against what-if analysis

What-if analysisChange an input by hand and see what happens. Every input is treated as known.
Risk analysisTreat the uncertain inputs as probability distributions, generate many possible outcomes, and describe the distribution of the result.

What-if gives you three scenarios: pessimistic, expected, optimistic. Risk analysis gives you a probability for every outcome in between.

The difference matters when a decision hinges on how likely a bad case is, not on how bad it is. Check against your class slides.

← BackContinue →