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 5 of 25
Worked examplePracticalUnverified

Ten trials of a bakery day

Cakes sell at 200 rupees and cost 80 rupees to bake. Unsold cakes are wasted. The baker makes 100 cakes.

Profit = 200 x min(Demand, 100) - 80 x 100

Ten simulated days gave profits of 10,000, 12,000, -2,000, 8,000, 12,000, 4,000, -1,000, 12,000, 6,000 and -3,000 rupees.

Report what the simulation says.

0 of 7 lines shown.

← BackContinue →