Lab 2: Build an rNPV Model with Monte Carlo Sensitivity

COMPEL Specialization — AITE-VDT: AI Value & Analytics Expert Lab 2 of 5

Lab objective

Build a working risk-adjusted NPV (rNPV) model with Monte Carlo sensitivity analysis for the scenario described below. Produce a spreadsheet and a one-page sensitivity summary with tornado chart.

Duration: Two hours. Deliverable: A spreadsheet model (Excel, Google Sheets, or Causal) and a one-page PDF or Markdown summary. Linked articles: 7 (rNPV), 11 (sensitivity analysis and scenario planning).

Scenario

You are the AI value lead for a mid-sized retail bank. The risk team has proposed “FraudGuard+,” a machine-learning model that augments the existing fraud-detection pipeline with a GenAI component that investigates flagged transactions in real time by examining merchant history, customer purchase patterns, and contextual signals.

Project parameters

• Build cost (Year 0): $2.8M. Probability of reaching build-complete: 0.85.
• Pilot cost (Year 1 H1): $0.9M. Probability of reaching pilot-complete given build: 0.80.
• Rollout cost (Year 1 H2): $1.4M. Probability of rollout given successful pilot: 0.90.
• Annual run cost (Year 2 onward): $3.2M, growing at 8% per year.
• Expected benefit (Year 1 H2): $1.2M (half-year of rollout at 50% scale).
• Expected benefit (Year 2): $5.8M.
• Expected benefit (Year 3): $7.4M.
• Expected benefit (Year 4): $8.1M.
• Expected benefit (Year 5): $8.1M.
• Discount rate (base): 12% annual.
• Model-refresh cost (every 18 months starting Year 2): $0.6M per refresh.

The CFO has asked for: (1) the point rNPV estimate; (2) Monte Carlo sensitivity with 10,000 iterations; (3) tornado chart identifying the three most-sensitive inputs; (4) a go/no-go recommendation.

What to produce

Step 1 — Build the base-case rNPV

Construct a year-by-year cash flow table from Year 0 through Year 5. For each year, apply:

• The expected benefit multiplied by the cumulative probability of reaching that year’s benefit stage.
• The expected cost multiplied by the probability of reaching that year’s cost stage (build probability for Year 0, pilot probability for Year 1 H1, rollout probability for Year 1 H2).
• The discount factor at 12%.

Sum discounted cash flows to compute base-case rNPV.

Step 2 — Identify uncertain inputs for Monte Carlo

Choose the seven inputs that most plausibly vary. For each, specify a probability distribution with parameters drawn from the base case and plausible ranges. Suggested:

• Build cost: Triangular(min $2.3M, base $2.8M, max $3.6M).
• Rollout cost: Triangular(min $1.1M, base $1.4M, max $1.9M).
• Annual run cost growth: Normal(mean 8%, std 4%).
• Year 2 benefit: Normal(mean $5.8M, std $1.2M).
• Year 3–5 benefit multiplier: Beta distribution shifted to represent (0.7, 1.0, 1.3) range.
• Discount rate: Normal(mean 12%, std 2%).
• Pilot success probability: Beta(8, 2) giving mean 0.80.

Step 3 — Run the Monte Carlo simulation

Run 10,000 iterations. For each iteration, sample from each distribution, compute the rNPV using the same cash-flow structure as Step 1, and record.

Report:

• Mean rNPV.
• p10, p50, p90 rNPV.
• Probability of positive rNPV.
• Probability of rNPV above a $10M hurdle rate.

Step 4 — Produce the tornado chart

For each of the seven inputs, run a one-way sensitivity at the p10 and p90 values holding other inputs at their base values. Plot the rNPV impact as a horizontal bar sorted largest to smallest. The tornado chart visualizes which inputs most move the value outcome.

Step 5 — Write the one-page summary

One page, three sections.

1. Headline. Base-case rNPV in dollars; probability of positive rNPV; three-to-five most-sensitive inputs named.
2. Recommendation. Go, go-with-conditions, or no-go. Conditions specified if applicable.
3. Key uncertainties. The three inputs whose sensitivity is greatest, with their plausible ranges and the implications for decision.

Guidance

• Probability compounding. Don’t double-count probability. If the Year 1 H2 cash flow is conditional on both build success and pilot success, its cumulative probability is 0.85 × 0.80 = 0.68, not 0.80 alone.
• Discount rate. The base rate is 12% but can vary. When it moves, every future cash flow moves. The sensitivity analysis will show discount rate as one of the most influential inputs.
• Correlation. In the simple version, treat inputs as independent. For a more sophisticated version, model the correlation between pilot success probability and rollout cost (unsuccessful pilots often reveal cost underestimation). Document whichever approach is taken.
• Honest range specification. Triangular and normal distribution ranges reflect actual uncertainty, not the ranges that produce preferred results. A distribution chosen to make the rNPV look better will fail honest review.

Evaluation rubric

Reflection questions

1. Your base-case rNPV and your mean Monte Carlo rNPV should be roughly equal. If they differ by more than 15%, what does that tell you about your distribution choices?
2. The tornado chart identifies the Year 2 benefit as the most sensitive input. What measurement-plan implications does this have for the subsequent rollout?
3. Suppose Monte Carlo results show probability of rNPV above hurdle at 42%. How would you communicate the go/no-go recommendation?

Linked articles and further reading

• Article 7 — Risk-adjusted NPV for AI features.
• Article 8 — Total cost of ownership for AI.
• Article 11 — Sensitivity analysis and scenario planning.
• Standard finance texts on Monte Carlo simulation and real-options valuation.

Submission

Submit the spreadsheet and the one-page summary as separate files. Reviewer will validate the computation chain, the distribution choices, and the recommendation framing.