Simulation for Managerial Decisions

The spreadsheet said $2.4M profit. The covenant broke in March.

A board-approved forecast showed mean quarterly profit of $2.4M. March profit was −$1.1M. The lender invoked a covenant requiring profit above $0. Leadership protested that −$1.1M was a bad draw, not the plan. The lender replied that a plan without a distribution is not a risk plan. Mean-only forecasting fails when tails (extreme low or high outcomes) trigger legal and operational consequences. Monte Carlo simulation (repeated random sampling from input distributions to build an output distribution) answers questions closed-form math cannot: What is the probability profit falls below zero? What is the fifth percentile (p5) profit? How much does correlation between revenue and cost widen the loss tail?

This capstone lesson for Unit 3 integrates Lesson 1 scenario thinking, Lesson 3 distribution choice, and Lesson 4 EV and downside metrics into a computational workflow you can run in Excel or Google Sheets. You will build simulation logic step by step, interpret percentiles for service levels and covenants, and model correlation (statistical tendency for two variables to move together) between inputs.

Ethics note from Unit 1: simulation assumptions shown to investors must not cherry-pick tails. Show sensitivity to adverse assumptions.

Data Table and paste-values workflow in Excel

For board-ready output without RAND() flicker:

1. Build one iteration row with formulas (revenue draw, cost draw, profit).
2. Copy row down to row 10,001 (10,000 iterations).
3. Select entire block, Copy, Paste Special → Values (locks one snapshot).
4. On pasted column, compute AVERAGE, PERCENTILE.INC, COUNTIF for P(loss).
5. Label snapshot date and iteration count in assumptions tab.

Data Table (one-variable) sensitivity: row input cell RAND seed logic, column of percentiles tracks output as you change λ. Useful for tornado charts: which input moves p5 profit most?

Python and Sheets alternatives (oversight level)

Analysts may use Python numpy.random with seed=42 for reproducibility. Managerial requirement: receive mean, p5, p95, P(loss), and assumptions CSV, not only code. Google Sheets ARRAYFORMULA with RANDARRAY(10000,1) fills columns; same paste-values discipline applies.

Bootstrap when you distrust parametric families

If historical daily revenue has weird spikes, bootstrap resampling draws 10,000 simulated months by sampling 30 days with replacement from last year. No Poisson or normal assumption required. Downside: bootstrap inherits only past regimes; structural breaks (new competitor) are invisible. Combine bootstrap with judgment scenarios from Lesson 1.

When to simulate instead of closed-form

Simulate when:

• Profit is a nonlinear function of inputs (caps, tiers, bonuses, piecewise costs)
• Many correlated inputs drive one output
• You need percentiles and P(loss), not only mean
• Distributions are mixed (triangular minimum/mode/maximum, discrete spikes)

Closed-form EV may suffice for simple discrete scenarios. Simulation adds work but returns the full empirical distribution of outcomes.

Managerial contract: every simulation deliverable includes (1) assumptions tab with distributions and correlations, (2) iteration count, (3) seed/documentation for reproducibility, (4) summary table with mean, p5, p50, p95, P(loss).

Monte Carlo logic in plain language

One iteration draws one random value per uncertain input according to its distribution, computes output via the spreadsheet formula, and stores the result. Repeat N times (often 5,000 to 50,000). The collection of N outputs approximates the output distribution.

Law of large numbers intuition: as N grows, the sample mean approaches the true expected value if the model is correct. Percentiles stabilize with enough iterations; 10,000 is a common starting point for profit models.

Pseudo-random numbers: RAND() in Excel generates uniform(0,1). Transform uniforms into normals, Poissons, etc., with inverse CDF functions (NORM.INV, POISSON.INV, etc.).

Never present simulation output without stating N and assumptions. A jagged distribution from 200 iterations is not board-ready.

Building blocks in Excel and Google Sheets

Uniform(0,1): =RAND()

Normal(μ, σ): =NORM.INV(RAND(), mu, sigma)

Triangular(min, mode, max): Excel lacks a native inverse, use:

=IF(RAND()<(mode-min)/(max-min), min + SQRT(RAND()*(mode-min)*(max-min)), max - SQRT((1-RAND())*(max-mode)*(max-min)))

or add-in tools (@RISK, Crystal Ball). Triangular encodes expert minimum, most likely, maximum.

Poisson(λ): =POISSON.INV(RAND(), lambda)

Bernoulli(p): =IF(RAND()<p,1,0)

Lognormal: if ln(X)~Normal(μ_ln,σ_ln), then =LOGNORM.INV(RAND(), mu_ln, sigma_ln).

Summary stats on simulation column (say Z2:Z10001):

Check: p5 ≤ p50 ≤ p95 ✓.

Correlation between inputs

Independent draws assume revenue shocks and cost shocks are unrelated. In recessions, revenue falls while fixed costs stay high; ρ (rho, correlation coefficient) is positive between revenue shortfall and cost overrun. Independent simulation underestimates joint bad outcomes and P(loss).

Gaussian copula sketch (Excel-level): to correlate two normals with ρ:

1. Draw Z₁ = NORM.INV(RAND(),0,1)
2. Draw Z₂ independent standard normal
3. Set X = Z₁
4. Set Y = ρ×Z₁ + √(1−ρ²)×Z₂
5. Transform X,Y to revenue and cost marginals via inverse CDFs

If ρ = 0.3 between revenue and cost shocks, loss tail widens versus ρ = 0. Managers should stress ρ = 0.5 in downturn planning even if historical estimate is 0.2.

Interpreting output for decisions

• Mean: debate central tendency for planning; not for covenants
• p5: conservative benchmark; "bad but plausible" downside
• p95: upside capacity needs, staffing peaks
• P(loss): covenant and runway risk

Compare simulation to discrete EV tables (Lesson 4). They should align on mean if assumptions match. If not, find the bug (wrong sum of probabilities, missing correlation, accidental double counting).

Reproducibility and governance

Set a seed if your tool supports it (RAND() in Excel reruns each recalc; copy-paste values or use VBA/Python with seed for audit). Document who owns each input distribution. Finance should own profit formula ties to chart of accounts.

Ethics note from Unit 1: simulation assumptions shown to investors must not cherry-pick tails. Show sensitivity to adverse assumptions.

Meridian Components models next quarter profit = Revenue − Cost.

Part A: Assumptions

10,000 iterations. Covenant: profit > $0. Board policy: contingency plan if P(loss) > 10%.

Part B: Spreadsheet layout (one row per iteration)

For teaching clarity, simplified model: Revenue ~ Triangular(8,10,14) independent; Cost = 7 + 0.8×D where D is standard normal correlated with revenue shock derived from B.

Revenue cell: triangular draw from RAND in column F.

Cost cell: =7+0.8*D

Profit: =F - G

Part C: Illustrative results (representative run)

After paste-values or 10k rows:

Check ordering: −0.8 < 2.3 < 5.1 ✓. Mean roughly mode minus expected cost: 10 − 7 = 3 before variance shrinkage to 2.4 ✓ plausible.

Part D: Managerial read

P(loss) = 12% exceeds 10% policy. Meridian should prepare contingency: cut discretionary opex $0.8M, draw revolver line, or hedge raw materials. Board should not treat $2.4M mean as covenant-safe. Lender conversation: present p5 −$0.8M and mitigation plan. If ρ stressed to 0.5, P(loss) rises further; correlation sensitivity belongs in appendix.

Trailhead Software must promise a delivery date for an enterprise integration.

Part A: Task model

Tasks in series: Total = A + B + C. 5,000 iterations.

Part B: Simulation mechanics

A: =10 + 10*RAND()

B: =MAX(5, NORM.INV(RAND(),15,3))

C: =IF(RAND()<0.7,5,15)

Total: sum of three columns.

Part C: Output

Check: mean near E[A]=15, E[B]=15, E[C]=0.7(5)+0.3(15)=8 → 38 ✓.

Part D: Managerial read

Customer SLA should use p90 = 45 days, not mean 38. Promising 38 days misses roughly 10% of simulated paths beyond 45. Product can parallelize tasks if possible; simulation quantifies value of crashing task C.

Tornado sensitivity without full re-simulation

After baseline simulation, vary one input at a time (revenue mode, cost σ, correlation ρ) and record Δp5 profit and ΔP(loss). Rank inputs by absolute impact. Executives see which assumption deserves data investment. Tornado is not a substitute for joint simulation when interactions matter, but it guides diligence.

Cumulative distribution function readout

Sort simulated profits ascending. Plot cumulative percent vs profit. Read p5 at 5% crossing, p95 at 95% crossing. Excel: =PERCENTILE.INC(range, k) matches sorted readout. Board slides should show the CDF curve, not only three numbers, so directors see skew.

Unit 3 integration checklist

Before closing any forecast deck:

1. Lesson 1: scenario probabilities sum to 1 and are calibrated.
2. Lesson 2: any diagnostic claims use posteriors, not raw accuracy.
3. Lesson 3: each input has a named distribution with estimated parameters.
4. Lesson 4: report mean, SD or percentiles, and P(loss) versus policy.
5. Lesson 5: simulation N stated, assumptions pasted as values, correlation stressed.

Covenant case extended

Meridian covenant requires profit > 0. Lender also requires p5 profit > −$0.5M. If p5 = −$0.8M, Meridian fails lender risk appetite even if mean = $2.4M. Renegotiate covenant to trailing average or post contingency plan with capital injection trigger at P(loss) > 12%.

Discrete spike plus continuous tasks (Trailhead refinement)

Task C mixed discrete spike is common in software: 70% chance integration is easy (5 days), 30% chance legacy API issues (15 days). Simulation captures bimodality that normal approximations on total duration would miss. Product should track which branch occurred to update future project priors (Bayesian learning).

Row 1 headers: Iter, Z1, Z2, CostShock, TriRand, Revenue, Cost, Profit.

• A2: =ROW()-1
• B2: =NORM.INV(RAND(),0,1)
• C2: =NORM.INV(RAND(),0,1)
• D2: =0.3*B2+SQRT(1-0.3^2)*C2
• E2: =RAND() for triangular
• F2: triangular formula using E2 with min 8 mode 10 max 14
• G2: =7+0.8*D2
• H2: =F2-G2

Fill to row 10001. Summary block:

Paste values before board meeting. Document ρ, triangular bounds, and normal cost parameters on Assumptions tab with owner names.

Illustrative numbers show mean stable while tail worsens with ρ. Present stress table alongside baseline.

BayBrew problem analytic check: P(S<60) for Poisson(100) negligible. Simulation with 10,000 draws should show sample P(loss) near 0. If empirical λ̂ from last year was 85 during recession, λ = 85 gives P(S<60) higher: use =POISSON.DIST(59,85,TRUE) ≈ 0.003 still small but 10× larger. Stress λ, not only mean profit.

Trailhead p90 total duration 45 days supports SLA "95% confidence delivery within 45 days" only if p95 ≤ 45. Check p95 from simulation; if p95 = 48, promise 48 or improve plan. Operations vocabulary links percentiles to customer contracts.

Part E: Meridian board narrative

Slide 1: assumptions. Slide 2: profit histogram from pasted simulation. Slide 3: covenant metrics (P(loss), p5). Slide 4: mitigation (opex cut $1M moves p5 from −0.8 to −0.3 illustrative). Slide 5: correlation stress. Narrative ties simulation to action triggers, not curiosity.

Part E: Trailhead parallel option

If tasks B and C can parallelize after A, total = A + max(B,C). Simulation changes: draw B and C, sum A + MAX(B,C). Mean drops; p90 may drop more. Project managers use simulation to value crashing critical path.

Cholesky correlation for two normals (Excel-friendly)

To correlate revenue shock R and cost shock C with ρ=0.3: draw independent Z1,Z2 standard normal. Set R_shock=Z1. Set C_shock=ρ*Z1+sqrt(1-ρ²)*Z2. Map R_shock to triangular revenue draw separately if needed; add C_shock to cost mean. Cholesky generalizes to more variables; two-variable case suffices for many P&L models.

Fixed random seed in Excel via VBA oversight

Application.Randomize 42 before loop, or export to Python with seed. Auditors prefer frozen outputs. Teach analysts paste-values discipline as default governance.

Service-level simulation for SaaS uptime

Uptime per month ~ mixture: 99.9% most months, 99.0% during incidents. Simulate 12-month contract uptime product. SLA credits trigger below 99.5% annual. Percentile of simulated annual uptime drives reserve accrual. Same Monte Carlo pattern as profit, different output metric.

Extended Meridian revenue triangular derivation

Triangular(min a, mode m, max b) inverse CDF uses two square roots for split at mode. Excel row formula with RAND() in E2:

=IF(E2<(m-a)/(b-a), a+SQRT(E2*(m-a)*(b-a)), b-SQRT((1-E2)*(b-m)*(b-a)))

Plug a=8,m=10,b=14. Mean triangular = (a+m+b)/3 = 10.667, close to simulation mean revenue before correlation effects. Cost mean 7. Mean profit rough 3.67 before correlation; simulation mean 2.4 shows correlation and nonlinear interaction; always reconcile back to assumptions.

Extended BayBrew simulation columns

A: iteration. B: =POISSON.INV(RAND(),100). C: =20*B-1200. Summary COUNTIF(C:C,"<0")/1000. Histogram bin width $100 on profit. Visual left tail tiny but visible.

Extended correlation copula step list for auditors

Auditors ask how correlation was produced. Document: (1) draw Z1,Z2 standard normals; (2) form correlated normal shock; (3) map to cost via linear scale; (4) revenue draw method stated; (5) profit formula ties to financial statements. Reproducibility: paste values with date stamp.

Extended integration capstone narrative

Unit 3 begins with coherent probabilities (Lesson 1), updates beliefs with evidence (Lesson 2), chooses shapes for inputs (Lesson 3), aggregates to EV and risk metrics (Lesson 4), and simulates when reality is nonlinear (Lesson 5). A manager who masters the chain does not need to memorize formulas; they need to enforce checks at each handoff. That is the operational definition of data literacy for uncertainty.

Review drill: simulation sign-off sheet

Sign-off sheet fields: iteration count, paste-values date, assumption owners, ρ stress results, reconciliation of simulated mean to scenario EV, p5/p95/P(loss) versus policy limits. Risk and FP&A co-sign before lender or board distribution. Empty sign-off means the model is a draft, not a decision tool.

BayBrew monthly profit. Unit sales S ~ Poisson(λ=100). Price $50. Variable cost $30/unit. Fixed cost $1,200.

Profit = (50−30)×S − 1200 = 20S − 1200.

1. Analytically find break-even sales (S where profit = 0).
2. Set up 1,000-row simulation in Sheets/Excel with POISSON.INV(RAND(),100) for S.
3. Estimate mean profit, p5, p95, and P(loss).
4. Compare mean profit to 20×100−1200. Check consistency.

Solution

1. 20S − 1200 = 0 → S = 60 units. Loss when S < 60.

2. Column A iterations; B =POISSON.INV(RAND(),100); C =20*B-1200.

3. Representative output:

Poisson P(S<60) extremely small at λ=100.

4. Analytical mean profit = 20×100 − 1200 = $800 ✓ matches simulation mean.

Correlation sensitivity. Using Meridian-like structure, suppose independent revenue and cost shocks gave P(loss)=8%, but ρ=0.3 gives P(loss)=12%.

1. Explain in a paragraph why positive correlation increases P(loss).
2. What managerial action reduces correlation risk (not just mean cost)?
3. Name one Excel limitation of RAND() for board packs and one fix.

Solution

1. Positive correlation makes high cost more likely alongside low revenue. Independent draws rarely pair worst revenue with worst cost simultaneously; correlated draws align shocks, widening the left tail of profit.

2. Actions: hedge commodity costs linked to revenue drivers, flexible labor contracts, diversify customer segments whose demand moves opposite in macro shifts.

3. RAND() recalculates on every workbook change, making screenshots stale. Fix: paste values after simulation, use Data Table with fixed seed in VBA/Python, or document iteration snapshot date.

Governance: who signs the assumptions tab

Finance owns revenue and cost mappings to chart of accounts. Operations owns volume and capacity distributions. Engineering owns duration uncertainties. Legal owns contractual thresholds (covenants, SLAs). Simulation without named owners becomes orphan math. Quarterly refresh calendar should align with planning cycle.

Iteration count and stability

Run stability check: 5,000 vs 10,000 vs 20,000 iterations on p5 and P(loss). If p5 moves more than 5% relative between 10K and 20K, increase N or use antithetic variates (advanced). For board packs, 10,000 with pasted values is standard. For pricing engines, stability requirements are tighter.

When simulation misleads

Simulation inherits wrong assumptions smoothly. Garbage in, smooth garbage out. Stress priors and correlations. Compare simulated mean to Lesson 4 scenario EV when both claim to represent same quarter. Large gaps signal model inconsistency before anyone trusts p5.

Row-by-row Data Table alternative

Excel Data → What-If Analysis → Data Table: column input empty, row input cell links to cost sigma. Fill percentiles vs sigma rows 0.5, 0.8, 1.0, 1.2. Shows capital planning sensitivity without retyping formulas.

Python reproducibility snippet (oversight)

Analysts may share: rng = np.random.default_rng(42); revenue = rng.triangular(8,10,14,size=10000); cost = 7 + 0.8*rng.normal(0,1,size=10000). Manager receives CSV output and seed note. Governance matches Excel paste-values policy.

Simulation for inventory and stockouts

Demand D ~ Poisson(200), stock S=215. P(stockout)=P(D>215)=1-POISSON.DIST(215,200,TRUE). Holding cost vs stockout cost tradeoff simulated by adding unit economics per iteration. Operations extends same Monte Carlo engine as finance profit models.

Pre-mortem integration

Before launch, team lists failure scenarios (Lesson 1), assigns probabilities, simulates financial impact (Lesson 5). Pre-mortem probabilities feed simulation as discrete mixture. Connects judgment to computation.

Meridian lender meeting script (prose)

"We ran 10,000 correlated draws of revenue and cost. Mean profit $2.4M aligns with planning scenarios. Fifth percentile profit is −$0.8M, below the covenant buffer. Probability of loss is 12%, above our 10% policy. We propose $1M opex reduction and a revolver draw trigger at p5 below −$0.5M." The script ties Lesson 4 downside metrics to Lesson 5 outputs in complete sentences lenders accept.

Trailhead customer email prose

"We commit to delivery within 45 days with 90% confidence based on project simulation including legacy integration risk." That sentence maps p90=45 to customer language. Legal should verify SLA matches simulated percentile, not developer mean 38 days.

Closing the unit: from language to computation

You began Unit 3 by making uncertainty discussable with coherent probabilities. You end by simulating full profit and duration distributions when closed form is not enough. The through-line is disciplined arithmetic tied to named assumptions, explicit checks, and stakeholder-specific reads. Carry the sign-off sheet, Bayes template, distribution fit memo, EV footer, and simulation snapshot into every forecast review you chair.

Simulation quality gates before external sharing

External sharing means lenders, boards, investors, or regulators. Quality gates: (1) assumptions owned and dated, (2) N ≥ 10,000 unless stability shown at 5,000, (3) paste-values snapshot attached, (4) mean reconciled to scenario EV within explained tolerance, (5) correlation stressed at +0.2 above baseline, (6) P(loss) compared to written policy. Failing any gate keeps the output internal until fixed.

Worked habits for spreadsheet builders

Name ranges for lambda, rho, mu_cost, sigma_cost instead of burying constants in formulas. Color input cells blue and output cells gray. Put iteration count in cell SimConfig!B1 and reference it in summary labels. Future-you (and auditors) will trace assumptions faster. These habits cost ten minutes per model and prevent covenant surprises when someone edits a hard-coded 0.8 months later.

Full simulation readout template for boards

Boards absorb tables when every row maps to a decision. Mean feeds the annual plan debate; p5 feeds contingency; P(loss) feeds covenant negotiation.

One-line simulation discipline

Before any lender or board send, write: "We simulated ___ iterations with assumptions dated ___; p5 = ___, P(loss) = ___; contingency trigger is ___." Empty blanks mean the model stays internal.

Monte Carlo ethics and communication

Simulation can impress audiences with false precision. Round percentiles sensibly (p5 profit −$0.8M, not −$0.837291) while keeping full precision in the workbook. Show assumption ranges, not only point parameters. When presenting to non-technical leaders, pair every simulated metric with a plain sentence: "There is roughly a one-in-eight chance we miss the covenant." Ethical communication is part of the model, not an afterthought.

Final practice encouragement

Rebuild the BayBrew and Meridian models in your own spreadsheet from scratch without looking at formulas for five minutes, then check against this lesson. Fluency comes from building, not only reading. When your p5 and P(loss) reproduce the lesson examples within Monte Carlo noise, you are ready for Unit 4 inference tools that estimate parameters from samples instead of assuming them.

Unit 3 assessment preparation

Unit assessments will ask you to apply frameworks from this unit to a real company. Prepare by keeping one running example (your firm or a public company) where you: (1) list three scenarios with probabilities, (2) compute one posterior from a flag or test, (3) name distributions for two metrics, (4) compute EV and P(loss) for a decision, and (5) sketch a five-line simulation output table. That portfolio of artifacts is stronger preparation than rereading formulas alone.

Simulation lesson closing sentence

Treat every forecast as a distribution question: what could happen, how likely is each region of outcomes, and which percentile triggers action. When you can answer those three in numbers and prose, Unit 3 is working. Carry that discipline into Unit 4 Statistical Inference.

Key takeaways

• Monte Carlo simulation produces output distributions for nonlinear, multi-input decisions.
• Report mean, p5, p50, p95, and P(loss); tie percentiles to covenants and SLAs.
• Model correlation when shocks move together; independence is often optimistic.
• Excel builds simulations with RAND(), inverse CDFs, and PERCENTILE.INC.
• Assumptions tab and reproducibility are part of governance, not optional metadata.

1. Simulate one P&L line you own with min/mode/max revenue and normal costs. Report p5 and p95 to a stakeholder.
2. Increase input correlation from 0 to 0.5 in a test model. How does P(loss) change?
3. Return to the unit page for assessments, or continue to Unit 4: Statistical Inference.