General Calculation Memory — User Guide

1. What is this file?#

This Excel workbook is the detailed calculation memory of the Average Individual Risk of the Exposed Population (IR_av) for projects generated with TekRisk under the general method of CCPS QRA risk contours (continuous probit curve, with or without wind rose).

Purpose: to document step by step, with explicit tables and live formulas, the specific contribution of each incident outcome case to the individual risk at each (x,y) point of interest, the initial frequencies and the applied probabilities. It is meant to be delivered to regulatory authorities and enables peer review.

Target audience:

• The regulatory authority reviewing the risk analysis.
• Process engineers validating the calculations through peer review.
• External auditors who need to reproduce the calculation with their own assumptions.

Difference from the simplified method: in the simplified method a scenario's contribution is binary (IF(distance ≤ radius; f×factor; 0)). In the general method the contribution is f × Pf(distance), where Pf comes from a continuous probit curve — linear interpolation of the fatality profile (distance→%) — and, for scenarios with a wind rose, from a sum over sectors Σ_θ P(θ)·Pf(d_θ). This memory reproduces that full chain with live formulas.

---

2. Workbook structure#

The workbook contains 9 sheets ordered sequentially. Each sheet starts with a grey introductory block explaining its content and purpose. Live-formula cells are rendered in blue italics.

#	Sheet	Content
1	Summary	Project metadata, applied method, IR criteria, population and IR_av result. Defines the named ranges INTOL_THR_GEN, TOL_THR_GEN, POBLACION_PROYECTO_GEN, DENSIDAD_PROYECTO_GEN.
2	Scenarios	One row per incident outcome case: initial frequency f_i, aggregation type, wind rose mode. Defines FREQ_SCENARIOS_GEN.
3	Fatality Profiles	For each radial/VCE scenario, the distance→fatality% table. Defines PERF_DIST_j / PERF_FAT_j.
4	Wind Rose	For each directional scenario, the 16 sectors {θ, P(θ)}. Defines ROSA_DIR_s / ROSA_PROB_s.
5	Receivers	(x,y) coordinates of interest (project receivers), with population and density.
6	Point Contributions	Receivers × Scenarios matrix. Per scenario: Pf (reference to Sheet 7) and Contribution = f_i × Pf. Final column IR(x,y) = SUM(contributions). Defines IR_POR_RECEPTOR_GEN.
7	Step-by-step Breakdown	The auditable core. For each receiver-scenario pair, the auxiliary columns of the Pf interpolation and, for directional scenarios, the breakdown over the 16 wind sectors.
8	Average IR	For each receiver, IR(x,y) × P(x,y), plus the background population contribution. IR_av Eq. 4.4.6 (total exposed population = receivers + background) and Eq. 4.4.7 (total population P_T). Defines IR_AV_EXPOSED_GEN / IR_AV_TOTAL_GEN.
9	Methodology	Canonical general-method equations, the interpolation logic, variable/unit table and bibliographic references.

---

3. How to audit IR_av step by step#

Step 1 — Sheet 1 (Summary)#

1. Verify that Method applied = General (CCPS QRA — continuous probit curve).
2. Confirm the IR criteria (intolerable / tolerable) and the applicable preset/country.
3. Note the total population P_T and the project density.
4. The IR_av total (Exposed Population) and IR_av (Total Population P_T) cells reference the final results from Sheet 8.

Step 2 — Input sheets (2, 3, 4, 5)#

• Sheet 2 (Scenarios): each row is an incident outcome case. The Frequency f_i column is the input from the frequency analysis. The Aggregation type indicates how the engine evaluated the scenario (radial, VCE directional, flash fire with rose, CCPS-simplified flash fire, envelope).
• Sheet 3 (Fatality Profiles): for each radial/VCE scenario, its distance→fatality% table. It is the input for the interpolations.
• Sheet 4 (Wind Rose): for each directional scenario, the 16 sectors with their probability P(θ). The verification row confirms Σ P(θ) ≈ 1.
• Sheet 5 (Receivers): the (x,y) points of interest, with population. If the geometry is not Point, the lat/lon is the centroid.

Step 3 — Sheet 7 (Step-by-step Breakdown) — the core of the audit#

For each receiver-scenario pair, it shows how Pf is obtained:

• Radial scenarios (thermal, radial VCE): one row per receiver with the auxiliary columns of the profile's linear interpolation — idx (segment found by MATCH), d1/d2 and f1/f2 (bracketing points), fraction, Fatality % (master formula of the 4 cases) and Pf = Fatality%/100.
• VCE directional: a block of sector rows per receiver with θ, P(θ) (live reference to the rose in Sheet 4), d_θ (representative sector distance), Pf(d_θ) (tabulated from the engine — in envelope mode it collapses multiple samples per direction and is not recalculable cell-by-cell) and Partial contribution = P(θ)·Pf(d_θ). The subtotal Σ contributions = the scenario Pf for that receiver. If the engine produced no directional breakdown (typically MAX aggregation), contrib.pf is tabulated in a single row.
• Flash fire with wind rose: a block of sector rows with θ, P(θ), inside_θ (0/1 from the rotated LEL polygon point-in-polygon ray-cast) and Partial contribution = P(θ)·inside_θ. The subtotal Σ contributions (clamped to 1) = the scenario Pf.
• CCPS-simplified flash fire: Pf = IF(d ≤ R; θ/360; 0).
• VCE envelope (worst case): Pf is a MAX over the envelope samples; the engine value is tabulated (not recalculable cell-by-cell).

Step 4 — Sheet 6 (Point Contributions) — the matrix the authority asks for#

1. The reference row brings each scenario's initial frequency f_i from Sheet 2 via INDEX(FREQ_SCENARIOS_GEN, #).
2. For each receiver (row) and scenario:
   • Pf: live reference to the Pf computed in Sheet 7.
   • Contribution: live formula = f_i × Pf (CCPS Eq. 4.4.1).
3. The final column IR(x,y) = SUM(contributions) is the individual risk at the point.

Step 5 — Sheet 8 (Average IR)#

1. For each receiver, IR(x,y) is referenced from Sheet 6 and multiplied by P(x,y).
2. The Receivers subtotal accumulates Σ(IR×P) and Σ(P) for the exposed receivers (via SUMIF on IR > 0: a receiver with IR = 0 is not exposed and does not enter the denominator).
3. The Background population section lists the IR bands from the persisted contour calculation and their Background subtotal (persisted scalars — see Limitations).
4. IR_av total (Exposed Population) = (Σ IR·P receivers + Σ IR·P background) / (Σ P receivers + Σ P background) — CCPS Eq. 4.4.6. This is the headline figure, consistent with the application's Individual Risk tab.
5. IR_av (Total Population) = (Σ IR·P receivers + Σ IR·P background) / P_T — CCPS Eq. 4.4.7.
6. The IR_av receivers only (no background) line is shown as a secondary reference.

---

4. How to run a sensitivity analysis#

The workbook is fully recalculable. Edit any input and watch the propagation:

• Frequency f_i (Sheet 2, column E): changes that scenario's contribution at every receiver.
• Fatality profile (Sheet 3): changes the interpolated Pf at every pair of that scenario.
• Probability P(θ) (Sheet 4): changes that sector's partial contribution in the directional breakdown.
• Population P(x,y) (Sheet 8, column D): changes IR_av without affecting IR(x,y).

The live-formula chain propagates the change: Sheets 3-4 → Sheet 7 → Sheet 6 → Sheet 8 → Sheet 1.

The workbook is generated with fullCalcOnLoad: Excel recalculates everything on open. If your reader does not recalculate automatically, press Ctrl+Alt+F9.

---

5. Glossary#

Symbol	Meaning	Unit
IR(x,y)	Individual risk at point (x,y)	yr⁻¹
IR_av	Average individual risk	yr⁻¹
f_i	Initial frequency of scenario i	yr⁻¹
Pf	Fatality probability of the scenario at the point	—
P(θ)	Probability that the wind blows from θ (wind rose)	—
Pf(d_θ)	Fatality probability at distance d_θ for direction θ	—
d_θ	Distance from the receiver to the effect zone for direction θ	m
θ	Wind direction / LEL polygon angle	°
P(x,y)	People at point (x,y)	persons
P_T	Predetermined total project population	persons

---

6. References#

• CCPS, Guidelines for Chemical Process Quantitative Risk Analysis, 2nd Ed., AIChE/CCPS, 2000. Chapter 4 §4.4: Effect Models; Eq. 4.4.4 simplified direction factor; Eq. 4.4.6 / 4.4.7 IR_av.
• CCPS, Guidelines for Evaluating the Characteristics of Vapor Cloud Explosions, Flash Fires, and BLEVEs, AIChE, 1994.
• TNO, Methods for the Determination of Possible Damage (Green / Yellow / Purple Book), Voorburg, 2005.
• Lees, Loss Prevention in the Process Industries, 4th Ed.; Eisenberg 1975; Hurst RISKAT 1989.
• Applicable technical standard (e.g. NTE-002-CGPC) per project jurisdiction.

---

7. Discrepancies with previous calculations (audit)#

If, when comparing the IR_xy column of a receiver between the PDF "Export PDF" (IR Average) and this Excel Calculation Memory, the values do not match, the cause is almost always the following:

• The PDF reads the IR_xy from the last persisted snapshot, generated the last time "Calculate IR Contour" was pressed. Earlier engine versions (pre-2026-05-14) evaluated that IR_xy by locating the closest contour-grid cell to the receiver — an approximation with proximity error (typical grid resolution: 50 m) that additionally used the rose interpolated to 72 directions (the map's visual smoothing).
• This Excel Calculation Memory always rebuilds the IR_xy on the fly with the current engine: IR_xy = Σ_j (f_j × Pf_j) evaluated at the receiver's exact coordinate with the real 16-sector rose. It is the value that parameterises the CCPS probabilistic model.

What to tell the auditor:

1. The defensible value is the one in the Excel. It is the output of the probabilistic engine parameterised by the real 16-sector wind rose, evaluated at the exact receiver point. Sheet 7 shows step-by-step how that Pf is obtained (distance-fatality profile or directional sum Σ_θ P(θ)·Pf(d_θ)) and Sheet 6 shows Contribution = f_i × Pf.
2. The PDF aligns once you recalculate. Press "Calculate IR Contour" once with the current software version. The persisted snapshot is updated using the same engine as the Excel Memory and the per-receiver IR_xy start matching byte-identically. The application shows an amber banner in the "Summary" view when it detects the snapshot comes from an earlier engine.
3. The total RI can match even when per-receiver IR_xy is out of sync, because receiver-by-receiver discrepancies partially cancel out when summed and population-weighted. This is expected and does not signal a calculation error in either path.

Starting with engine version ir-xy-point-evaluation-v1, the metadata.engineVersion field is sealed into the persisted snapshot and the UI automatically detects when a project was calculated with an earlier version, inviting the user to recalculate before exporting.

---

8. Limitations and assumptions#

1. 16-sector rose: the memory is evaluated with the real 16-sector wind rose. This memory's per-receiver IR(x,y) is byte-identical to the breakdown table in the application's Individual Risk tab (both use the same point-evaluation engine). The contour map uses a rose interpolated to 72 sectors as visual smoothing: there may be a minor residual difference between this memory's IR(x,y) and the contour passing through that point — that residual is the smoothing artifact, not a real risk gap.
2. Recalculated from current models: the memory is rebuilt from the project's current risk models at export time. If the models are edited after the contours are calculated, the memory and the map may diverge. The export date is recorded in Sheet 1.
3. Background population: the background contribution (density × area outside the receivers) comes from integration over the persisted contour grid and does enter the total IR_av in Sheet 8. Its values (population and IR·P subtotals) are persisted scalars from the contour calculation: they are not recalculable cell-by-cell in this sheet because the angular integration used the grid's interpolated rose. The receiver part of IR_av is a live, recalculable formula.
4. Probit applied upstream: the general method does not evaluate a probit function with erf in the IR engine. The distance→fatality% profile already has the probit applied (when the risk model's fatalities were computed). This memory interpolates that profile — the documented traceability starts from the fatality profile.
5. VCE envelope: for VCE scenarios in envelope (worst-case) mode, Pf is a MAX over the envelope samples and the engine value is tabulated without a cell-by-cell breakdown.
6. Requires Excel with standard function support (INDEX, MATCH, IF, SUM). It does not use recent-version functions — it is compatible with any modern Excel version and with LibreOffice Calc.