Skip to content

Problem Definition - Complete Documentation

This page is a single-reference version of the workbook contract used by the current engine. It is intended to stay aligned with parser behavior in engine/R/01_initialize_data.R and related helpers.

Workbook Overview

Mandatory sheets:

  • imp
  • fos
  • meta
  • cov
  • orgmeta

Optional sheets:

  • formulas

Optional supporting sheets:

  • custom distribution sheets referenced from imp

Parser load order:

  1. formulas
  2. imp
  3. cov
  4. fos
  5. meta
  6. orgmeta

Impact Multipliers - imp

Purpose:

  • Defines multiplier names, ranges, distributions, and weight types.

Terminology:

Term Meaning
Impact Differentiator The multiplier name defined in this sheet.
SOW State of the World. In implementation terms, this is the sampled multiplier value for a given differentiator and simulation.
OC Organization Characteristic. This is the organization-specific value from the fos sheet for the same differentiator.

Recognized columns:

Column Required Meaning
Impact Differentiator Yes Multiplier name. Spaces and dots are removed internally.
Min Yes Lower numeric input used by the distribution.
Max Yes Upper numeric input used by the distribution.
Distribution Yes Distribution family.
Weight Type No Alias for internal Type.
Category No Optional metadata used by some downstream grouping utilities and plots.

Supported distributions:

Distrib name Description Min/Max application
normal Samples from a normal distribution with mean = (Min + Max) / 2 and sd = (Max - Min) / 4. Interpreted as an approximate central 95% range around the mean, not as hard bounds.
lognormal Converts the same midpoint-and-quarter-range interpretation into lognormal parameters, then samples from a lognormal distribution. Used as rough anchors for the distribution on the original scale, not as literal bounds or exact percentiles.
uniform Samples uniformly between two endpoints. Used as literal lower and upper bounds.
custom Reads empirical values from a separate sheet named after the normalized Impact Differentiator, then samples from that empirical distribution. Used only to filter the empirical values before sampling.
formula Placeholder distribution for multipliers computed from formulas rather than sampled directly. Not used for formula-based multipliers.
unity Always returns 1. Ignored.

Important behavior:

  • For normal and lognormal, Min and Max are not literal hard bounds and are not implemented as p5/p95.
  • If Min = Max for a sampled distribution such as normal, lognormal, or uniform, the engine treats the multiplier as a constant internally. This does not apply to custom, formula, or unity.
  • For custom, Min and Max are used only to filter the empirical values before sampling.

Supported weight types:

  • SOW x OC
  • OC ^ SOW
  • SOW ^ OC
  • TempModifier-Compounded
  • TempModifier-SingleYear
  • UtilityDeclineParameter

How these types are used:

Type Role in the model
SOW x OC Used in the base expected-value calculation.
OC ^ SOW Used in the base expected-value calculation.
SOW ^ OC Used in the base expected-value calculation.
TempModifier-Compounded Applied later when expected values are propagated across years.
TempModifier-SingleYear Applied later as a year-specific adjustment when expected values are propagated across years.
UtilityDeclineParameter Used later in bracket-level utility adjustment for portfolio optimization.

See Expected Value Computation for a fuller explanation of:

  • how SOW x OC, OC ^ SOW, and SOW ^ OC feed into base expected values,
  • how temporal modifiers affect later years, and
  • how UtilityDeclineParameter interacts with bracket-level utility adjustment.

Defaults and validation:

  • Missing weight type defaults to SOW x OC for non-temporal rows.
  • Common aliases such as OC x SOW, sow*oc, Multiplier, and Exponent are normalized to current type labels.
  • Older templates may still use Class; the parser recognizes temporal modifier values there and maps them into Weight Type with a warning.
  • If no compounded temporal modifier exists, the engine adds a default DiscountAllYears = 1.
  • Duplicate multipliers keep the first row and record an error.
  • Multiple compounded temporal modifiers are rejected.
  • Multiple utility-decline parameters are rejected.

Organization Characteristics - fos

Purpose:

  • Defines organization-specific values for each differentiator.

Recognized structure:

  • Metadata columns: Impact Differentiator, optional Variable Type, optional SD, optional CoV
  • Organization mean columns: OrgName
  • Organization standard-deviation columns: OrgName_StDev or OrgName_SD

Defaults and inference:

  • If organization-specific standard deviation exists, use it.
  • Else if row SD exists, use SD.
  • Else if row CoV exists, use CoV * Mean.
  • Else record an error if a mean exists with no usable standard deviation.

Validation and parser behavior:

  • Entire organization columns that are all NA are dropped.
  • Spaces and dots are removed from organization and differentiator names.
  • Missing organization-by-differentiator combinations are completed later with NA.
  • Indicator means must be 0, 1, or NA.
  • Indicator mean 0 is currently converted to NA.
  • Large mismatch between implied and row-level CoV generates a warning.
  • Rows for differentiators that do not exist after multiplier processing are removed later.

Important cross-sheet behavior:

  • fos can reference formula-generated differentiators.
  • Final initialization keeps only organizations present in both fos and orgmeta.

Funding Parameters - meta

Purpose:

  • Defines budget, horizon, and optional virtual-organization parameters.

Recognized variables:

  • BudgetTotal
  • legacy Budget
  • BudgetYearly
  • Years
  • VirtualOrgsPerYear
  • VirtualOrgsSamplingStrategy

Defaults:

  • BudgetTotal falls back to Budget.
  • BudgetYearly defaults to BudgetTotal.
  • Years defaults to the caller-provided default_budget_years.
  • VirtualOrgsPerYear defaults to 0 if missing or invalid.
  • VirtualOrgsSamplingStrategy defaults to fit_sample if missing or invalid.

Accepted sampling strategies:

  • fit_sample
  • fit_percentile
  • percentile
  • weighted_fit_sample
  • weighted_fit_percentile
  • weighted_percentile

Validation:

  • After defaulting, BudgetTotal and BudgetYearly must exist.
  • BudgetTotal must be at least BudgetYearly.

Correlation Matrix - cov

Purpose:

  • Defines multiplier correlation, and optionally noise correlation for organizations.

Accepted formats:

Matrix format

  • First column named Impact Multiplier
  • Remaining columns named by multiplier
  • Lower triangle is sufficient

Vertical format

Required columns:

  • Variable1
  • Variable2
  • Correlation

Optional column:

  • Type with values Multiplier or Noise

See Noise And Model Coverage for how Type = Noise correlations are used after expected values are computed.

Parser behavior:

  • Detects format automatically.
  • Matrix labels are normalized by removing spaces and dots.
  • Duplicate rows, columns, or variable pairs keep the first occurrence and record an error.
  • Missing pairs default to 0.
  • Symmetry is enforced by mirroring (A, B) and (B, A).
  • Unrecognized names are filtered out.
  • Noise rows only matter if organization names are available.

Safer authoring convention:

  • Prefer explicit diagonal 1s.
  • Prefer lower-triangle matrix input or explicitly symmetric vertical input.

Organization Funding - orgmeta

Purpose:

  • Defines room for funding, diminishing returns, year availability, and model coverage.

Recognized variable families:

  • Room for funding
  • Discount
  • Utility decline
  • Utility multiplier
  • Available in year
  • Model Coverage or Model_Coverage

Important bracket behavior:

  • Brackets are assigned by row order within a variable family.
  • Square-bracket labels such as [1] and [2] are helpful for humans but are not the authoritative source of bracket numbering.

From a modeling perspective:

  • earlier Room for funding brackets usually represent the first and most attractive dollars you can grant,
  • later brackets represent additional funding after those earlier opportunities have been filled,
  • later brackets often have higher Discount values to reflect diminishing returns,
  • later brackets may also have lower Utility multiplier values if you want to scale down their value directly.

Defaults:

  • Missing Available in year X entries default to 1.
  • Missing Model Coverage defaults to 1.
  • Missing bracketed Discount defaults to 0.
  • Missing bracketed Utility multiplier defaults to 1.
  • Missing bracketed Room for funding defaults to 0.

Validation:

  • Availability values outside [0, 1] are corrected to 1.0 with a warning.
  • Availability rows beyond meta$Years are removed with a warning.
  • If an organization has mismatched counts of non-missing room-for-funding and discount rows, the parser records an error.
  • If both Discount and Utility multiplier are given for the same organization and bracket, the engine warns and applies both.
  • Later validation warns when discounts decrease across brackets.

Cross-sheet behavior:

  • Spaces and dots are removed from organization names for matching against fos.
  • Organizations not present in both orgmeta and fos are dropped during initialization.
  • Model Coverage is used to add post-EV noise when coverage is below 1; see Noise And Model Coverage.

Formulas - formulas

This optional sheet defines derived variables using a small formula language. It is primarily useful when a multiplier or organization characteristic should be computed from other variables rather than sampled directly.

Example formulas:

Formula What it does
CombinedEV = Hedginess * FundingAdditionality Creates a derived variable from two existing variables.
PolicyWeight_{region} = probit(PolicyProbability_{region}) Uses placeholders to create one formula per matching region.
Mixture_{region} = draw_from(BaseCase_{region}, 0.7, Upside_{region}, 0.3) Creates a weighted mixture between two variables.
LognormalEV = lognormal_ev_from_xpR(ValueAtMedian, 0.5, 4) Converts a percentile and percentile ratio into an expected value.
Result{a}{b} = x{a}{b} + z{a} + u{b} Example of a valid multi-placeholder formula.

Accepted structure:

  • Only the first column is read.
  • Each non-empty row should contain one formula assignment of the form Name = expression.
  • Empty rows are ignored.
  • Rows beginning with # are comments.
  • Additional columns are ignored.

Supported operators:

  • +
  • -
  • *
  • /
  • ^
  • ()
  • × is normalized to *
  • ÷ is normalized to /

Supported functions:

Function Purpose
ln(x) / LN(x) Natural logarithm
exp(x) / EXP(x) Exponential function
sqrt(x) Square root
abs(x) Absolute value
lognormal_ev_from_xpR(x, p, R, cap_p, cap_R) Computes a lognormal expected value from a percentile and a percentile ratio
probit(p, cap_p) Inverse standard normal CDF
draw_from(m1, w1, m2, w2, ...) Row-wise stochastic choice between candidate variables

Notes:

  • Function names are case-insensitive where documented.
  • draw_from expects alternating value, weight pairs.
  • cap_p and cap_R let you disable default capping behavior.

Custom function details:

lognormal_ev_from_xpR(x, p, R, cap_p = TRUE, cap_R = TRUE)

  • Computes the expected value of a lognormal distribution from a known percentile value x, percentile p, and ratio R = P95 / P05.
  • x must be positive, p must be in (0, 1), and R must be greater than 1.
  • By default, p is capped to [0.001, 0.999] and R is capped to at least 1.001.

probit(p, cap_p = TRUE)

  • Returns the inverse standard normal CDF, equivalent to qnorm(p).
  • By default, p is capped to [0.001, 0.999].

draw_from(m1, w1, m2, w2, ...)

  • Chooses between candidate variables row by row using alternating value, weight pairs.
  • Weights may be scalars or simulation-length vectors.
  • Negative weights are capped to 0 with a warning.

Placeholder behavior:

  • Single-placeholder patterns are expanded against matching available variable names.
  • Multiple-placeholder formulas must be unambiguous.
  • Ambiguous placeholder patterns are warned about and ignored.
  • Later formulas can reference variables created by earlier formulas in the same sheet.

Validation behavior:

  • Formulas that cannot expand are ignored with warnings.
  • Formulas with unresolved variables after expansion are ignored with warnings.
  • Preview validation additionally checks parameters used inside lognormal_ev_from_xpR, probit, and draw_from.

Output behavior:

  • Formula-defined variables matter only if referenced by later formulas or by fos.
  • If fos references a formula-defined variable missing from imp, the engine can add it automatically.
  • If a formula-defined variable overrides an existing multiplier name and is used in fos, the engine marks it as formula and resets its correlations to 0.

Custom Distributions

Important clarification:

  • There is no workbook tab literally named custom_distributions.

Actual pattern:

  1. In imp, set Distribution = custom.
  2. Create a separate sheet named after the normalized multiplier name.
  3. Put numeric values in the first column.
  4. Optionally filter those values using Min and Max from imp.

Parser behavior:

  • Reads the custom sheet using the normalized differentiator name.
  • Uses only the first column.
  • Coerces values to numeric.
  • Drops NAs.
  • Applies optional Min and Max filtering.
  • Samples later from empirical quantiles of the stored values.

Failure mode:

  • Missing custom sheets produce parsing errors.
  • Empty or unusable custom sheets can break downstream simulation, so examples should always leave at least one valid numeric value after filtering.