Portfolio Simulation — Starter Spreadsheet

Purpose

This starter spreadsheet helps teams make trade-offs between exploration, validation, and scaling visible. Use it to model a portfolio of projects across discovery, validation (pilots), and scaling stages, estimate expected portfolio value, understand burn and runway impacts, and run simple sensitivity checks so decisions allocate capital, attention, and learning capacity intentionally.

When to use this model

• Early-stage planning: evaluate whether current projects justify continued investment.
• Portfolio balancing: compare many small experiments versus fewer higher-cost scale bets.
• What-if analysis: explore how success probabilities, costs, or capacity constraints change outcomes.

What this starter model does

• Accepts per-project inputs (costs by stage, conditional success probabilities, estimated return at scale).
• Computes per-project expected value (simple expected-value approach), total portfolio EV, burn rate, and runway under capacity constraints.
• Provides guidance for probability distributions and a path to basic Monte Carlo sensitivity checks.
• Includes charts you can create in Excel/Sheets for visual comparison (expected value, cumulative burn, sensitivity tornado).

Inputs — clear definitions

1. Number of projects — count of distinct initiatives you plan to simulate.
2. Cost per stage — cost to fund the project in each lifecycle stage (Discovery, Validation, Scale). Enter as amounts per project per stage.
3. Conditional success probability per stage — probability the project advances from the current stage to the next, given it has reached the current stage (values 0–1).
4. Expected return if scaled — simplified expected gross return if the project successfully reaches and is scaled (nominal amount over a chosen horizon).
5. Capacity constraints — available budget or headcount per period (quarter/year) limiting how many projects you can fund concurrently.
6. Time horizon — optional: number of periods to consider for burn/runway calculations.

Structure to build in a spreadsheet (recommended columns)

Create one row per project with columns like these:

• Project Name
• Cost_Discovery
• Cost_Validation
• Cost_Scale
• Prob_DiscoveryToValidation (p1)
• Prob_ValidationToScale (p2)
• Prob_ScaleSuccess (p3) — optional if you model market success after scaling
• Expected_Return_at_Scale
• Total_Cost = SUM(costs)
• Overall_Success_Prob = p1 * p2 * p3 (multiply only stages you include)
• Expected_Value = Overall_Success_Prob * Expected_Return_at_Scale - Total_Cost

Worked example (illustrative)

Example project numbers (single project):

• Cost Discovery = $10,000
• Cost Validation = $50,000
• Cost Scale = $500,000
• p1 = 0.5, p2 = 0.3, p3 = 0.6 → Overall probability = 0.5 × 0.3 × 0.6 = 0.09 (9%)
• Expected return if scaled = $5,000,000
• Expected value = 0.09 × $5,000,000 − ($10k + $50k + $500k) = $450,000 − $560,000 = −$110,000

Interpretation: despite a high upside, the low cumulative probability and high scaling cost produce a negative expected value at this costing and probability profile. That’s normal and shows why portfolio balance (many low-cost experiments vs. few expensive scale bets) matters.

Recommended portfolio outputs and KPIs

• Total expected portfolio value (sum of per-project Expected_Value).
• Total committed cost (sum of Total_Cost across all projects).
• Burn rate per period (apply timing assumptions to costs, or divide committed cost by horizon).
• Runway = Available budget / burn rate.
• Count of projects by lifecycle stage and by stage-dependent expected value.
• Share of portfolio investment by stage (exploration vs validation vs scaling).
• Number or percent of projects with positive expected value.

Visuals to create in the spreadsheet

• Bar chart: Expected_Value by project (ranked to show which projects drive portfolio EV).
• Stacked bar or pie: Investment by stage (shows concentration of spend).
• Burn chart over time: cumulative committed costs by period to visualize runway implications.
• Tornado/sensitivity chart: change one input (e.g., p2 or Cost_Scale) and show EV swing.

Sensitivity and scenario checks

Try these quickly in-sheet:

1. Deterministic sensitivity: create columns that show EV with ±10% or ±25% changes to success probabilities and costs.
2. Scenario rows: define Conservative, Base, and Optimistic scenarios (different probability sets and returns) and show portfolio EV for each.
3. Simple Monte Carlo (optional): in Sheets/Excel use random draws for each stage probability and run N iterations to produce a distribution of portfolio outcomes. This is more advanced but reveals skew and tail risk.

Capacity-aware allocation

Map projects to periods and compute per-period spend. Use capacity constraints to filter which projects can be funded in a given period (for example, limit number of scale-stage projects in a quarter). Then recompute burn and EV for funded subset. This helps you see the opportunity cost of funding a scale bet versus several validation experiments.

Practical tips

• Use conditional probabilities (probabilities that a project advances) rather than naive unconditional probabilities.
• Keep separate columns for upside assumptions, time-to-return, and follow-on costs — these affect whether an expected value is realized within your chosen horizon.
• Label assumptions clearly and capture source or confidence level for each probability and return estimate.
• Don’t treat expected value as the only decision criterion. Consider strategic option value, optionality, learning value, and alignment to risk appetite.

Next steps — turning this starter into a team tool

1. Copy the structure into a shared Google Sheet or Excel workbook and add a assumptions tab that documents how probabilities and returns were estimated.
2. Run scenario comparisons and save them as named tabs: each tab is a scenario (Base, Conservative, Aggressive).
3. For repeatable review, export key tables or capture scenario snapshots so decisions are auditable.

Suggested improvements that fit the platform (capability ideas)

This starter is intentionally spreadsheet-friendly. To make it more powerful on the platform, consider:

• Building an interactive calculator that renders the inputs as a form, computes EV and burn automatically, and stores scenario submissions (so teams can compare saved runs over time).
• Enabling scenario versioning and tagging (e.g., by owner, date, funding round) so portfolios can be audited and learned from.
• Packaging this model into a reusable toolkit or collection (Discovery & Innovation toolkit) that teams can copy and adapt to local cost structures and risk tolerances.

Downloadable starter checklist & template

Use the column list and worked example above to build your sheet. If you want, start with five rows (projects) and these sample columns to see how totals move as you change probabilities and costs.

Quick reflection questions for your team

• Which stage (discovery, validation, scale) consumes the most budget today?
• How many scale-stage projects can you realistically support without hurting operations?
• Which projects are funded primarily for learning value rather than positive expected monetary value?

Use this starter as a practical conversation tool: the numbers don’t have to be perfect to reveal imbalances, clarify trade-offs, and guide better portfolio decisions.