A4 Transport to Site — Reverse-Engineering Spec & Reconciliation¶
Sources read (read-only, verified 2026-07-25):
| File | Sheet(s) read | Tool |
|---|---|---|
reference/excel/2_lca_vn.xlsx |
all 12 sheets enumerated; A4 and LCI Final extracted in full; C2 (Transport) read for cross-check |
openpyxl 3.x, raw formulas (data_only=False) and computed values (data_only=True) |
reference/thesis/pedrazzi_compressed_eng.pdf |
pp. 58–61 ("From the gate to the site", "Results and observations"), p. 79 (C2 cross-ref) | pypdf text extraction |
Verdict tags follow PROJECT.md decision #23 (two-axis scheme). Geographic tags follow PROJECT.md decision #26 (per-stage geographic-provenance policy). Vendorability tags follow PROJECT.md decision #25.
Fork outcome (Session 7 Step 1): an A4 transport calculation EXISTS in
2_lca_vn.xlsx (dedicated A4 sheet, rows 4–23, columns B–P). Fork (a) applies.
§1 Where the A4 calculation lives¶
Sheet enumeration of 2_lca_vn.xlsx:
| Sheet | Dimensions | Relevance to A4 |
|---|---|---|
LCI Final |
A1:N21 | Mass source (column F) — feeds A4 column F |
A1-A3 |
B2:F23 | — |
A4 |
B2:P32 | The A4 transport calculation |
A5 |
B4:H8 | — |
B1 |
B2:F10 | — |
B4 (Replacement) |
A4:I23 | — |
C1(Demolition) |
B4:G6 | — |
C2 (Transport) |
B2:R27 | End-of-life transport — same truck model, cross-check only |
C3(Waste Processing) |
B2:G25 | — |
C4 (Disposal) |
B2:G23 | — |
D |
B2:G24 | — |
INTERPRETATION |
B2:N27 | — |
No other sheet contains a transport-to-site calculation. C2 (Transport) reuses the
identical truck-power model for waste haulage and pulls its masses from A4!F5:F22;
it is out of scope for this module (Phase 2, lca.c2) but is noted because any correction
to the A4 truck model propagates to it.
§2 A4 sheet — raw formula structure¶
Column headers (row 4) and the raw formulas for a representative sea-freighted row
(row 11, Steel Rebars) and a truck-only row (row 5, Rock), extracted with
data_only=False:
| Col | Header (verbatim) | Row 5 (Rock) | Row 11 (Steel Rebars) |
|---|---|---|---|
| B | Material |
Rock(m³) |
Steel Rebars(Kg) |
| C | Engine Power (kW) |
380 |
380 |
| D | Full load speed (km/h) |
60 |
60 |
| E | Conversion factor (kWh/km) |
=C5/D5 |
=C11/D11 |
| F | Weight (kg) |
='LCI Final'!F3 |
='LCI Final'!F9 |
| G | Maximum Load of truck (kg) |
36500 |
36500 |
| H | Load Factor |
=F5/G5 |
=F11/G11 |
| I | truck Emission Factor (kg CO2-eq/kWh) |
0.26 |
0.26 |
| J | Truck Emission Impact (kg CO2-eq/km) |
=I5*H5*E5 |
=I11*H11*E11 |
| K | Distance truck (km) |
23 |
88 |
| L | sea Emission Factor (kg CO2-/KG*km |
(empty) | =182.4/(F11*N11) |
| M | freight Emission Impact (kg CO2-eq/km) |
(empty) | =F11*L11 |
| N | Distance freight (km) |
(empty) | =19155+2124 |
| O | Total Impact (kg CO2-eq) |
=(J5*K5)+(M5*N5) |
=(J11*K11)+(M11*N11) |
| P | % impact |
=O5/$O$23 |
=O11/$O$23 |
Row 23: O23 = =SUM(O5:O22), P23 = =SUM(P5:P22).
Column-F row mapping verified for all 18 rows: A4!F{r} = 'LCI Final'!F{r-2} with no
off-by-one and no transposition. Rock→F3 … Floor Tiles→F20. No wrong-row reference exists
in this sheet.
L broadcast: L11 is the only computed sea factor. L12, L13, L14, L19, L20,
L21, L22 are all =$L$11 (absolute reference to the rebar-derived cell). Rows 5–10, 15–18
have an empty L/M/N (no sea leg), so their (M*N) term evaluates to 0.
Routing note (cell A4!B25, verbatim):
Rock, Gravel, Sand, Earth → Sindia Quarry
Cement → quarry cement du sahel
Bitumen, Glass → Dakar (local factories)
Steel (rebars, plates, profiles) →) Shanghai Port China → Dakar Port
Polyethylene →Shanghai Port China → Dakar Port
Anti-termite →Shanghai Port China → Dakar Port
Wall Paint → Shanghai PortChina → Dakar Port
Anti-rust Paint → Shanghai PortChina → Dakar Port
Floor Tiles → Europe (rotterdam Port) → Dakar Port
Water, Crude Oil (waste), Waste Oil → On-site (no transport)
§3 The two models, and their algebraic reduction¶
§3.1 Truck model as written¶
E_r = C_r / D_r = engine power / full-load speed [kWh/km]
H_r = F_r / G_r = mass / max truck load [-]
J_r = I_r · H_r · E_r [kg CO2-eq/km]
Truck_r = J_r · K_r [kg CO2-eq]
Substituting:
C = 380 kW, D = 60 km/h, G = 36 500 kg, I = 0.26 kg CO₂-eq/kWh are identical in
every one of the 18 rows. They are therefore not per-material data — they are one global
constant:
EF_truck = I · (C / D) / G
= 0.26 × (380 / 60) / 36 500
= 4.511 415 525 114 155 5 × 10⁻⁵ kg CO₂-eq / (kg·km)
= 45.114 155 g CO₂-eq / t·km
so that
Finding A4-F1 (structural). The Excel's power-based formulation is algebraically
identical to the EN 15804+A2 per-tkm form mandated by PROJECT.md decision #26
("Emission factor per tkm sourced from IPCC EFDB or an ÖKOBAUDAT transport EPD").
The engine-power / speed / max-load columns add no information: they are a fixed
decomposition of a single constant. lca.a4 therefore implements the per-tkm form
directly. This is a presentation difference, not a numerical one — parity is exact
to machine precision (§6).
§3.2 Sea-freight model as written¶
L_11 = 182.4 / (F_11 · N_11) [kg CO2-eq/(kg·km)]
L_r = $L$11 for r ∈ {12, 13, 14, 19, 20, 21, 22}
M_r = F_r · L_r [kg CO2-eq/km]
Sea_r = M_r · N_r [kg CO2-eq]
Substituting the definition of L11:
with F_11 = 1 330.6 kg (steel rebar mass) and N_11 = 21 279 km, giving
EF_sea = 182.4 / (1 330.6 × 21 279)
= 6.442 079 800 882 095 × 10⁻⁶ kg CO₂-eq / (kg·km)
= 6.442 080 g CO₂-eq / t·km
Finding A4-F2 (structural — critical). L11 is not a factor; it is a back-solve.
An absolute total of 182.4 kg CO₂-eq is divided by the rebar mass × route length to
manufacture a per-kg-km rate, which is then broadcast to every other sea-freighted
material via $L$11. The consequence is that the sea leg of all seven sea-freighted
materials is anchored to a single unexplained constant:
Sea_11 = 182.4 kg CO₂-eq exactly, and every other row is a pro-rata scaling of it.
The constant 182.4 appears nowhere in the workbook, in any cell comment, or in the
Pedrazzi thesis. It has no unit label, no route description, and no citation. It cannot be
reconstructed from any other value in the reference set.
§3.3 Distance decomposition¶
| Route | Cell | Value | Reading |
|---|---|---|---|
| Shanghai Port → Dakar Port | N11–N21 |
=19155+2124 = 21 279 km |
Two undocumented segments |
| Rotterdam Port → Dakar Port | N22 |
=4830.93+3.65 = 4 834.58 km |
Two undocumented segments |
The segment split is not explained. 2124 and 3.65 are plausibly a port-approach or
inland leg, but this is inference, not evidence — recorded here as an open question, not
as a finding.
§4 Parameter-by-parameter reconciliation¶
§4.1 I = 0.26 kg CO₂-eq/kWh — truck diesel emission factor¶
Excel: literal 0.26 in A4!I5:I22 (and C2 (Transport)!N5:N22). No source note, no
cell comment, no entry in the workbook's source column.
Thesis (p. 58–59): the same model is described, but with different numbers:
"the formula used to obtain the power in kilowatt hours which takes into account the weight of the material transported and the power of the truck (386 kW)"
and the diesel factor table on the same page gives GWP = 0.27 kg CO₂e/kW·h — not 0.26. The thesis's own sourcing statement is:
"the emissions of diesel fuel per kilowatt hour were considered, obtained like the previous data, through a search among various certifications and LCA studies already carried out, considered reliable with regards to the data referring to diesel, as the variations depending on the country appear to be minimal."
No programme operator, no dataset identifier, no publication is named — in the thesis or in the workbook. The workbook further diverges from its own stated ancestor (0.26 vs 0.27; 380 kW vs 386 kW) with no recorded justification.
Verdict — Axis 2: BLOCKED. This is an impact factor entering a reported LCA result;
decision #23 requires SOURCED. PROJECT.md decision #26 names the only two admissible
origins for an A4 tkm factor — IPCC EFDB or an ÖKOBAUDAT transport EPD. 0.26
is neither, and its provenance is not merely undocumented but unrecoverable from the
reference set. No default, no placeholder: lca.a4 raises.
Plausibility cross-check (not a substitute for a source). The derived 45.11 g CO₂-eq/t·km sits below the published range for articulated HGV freight (ÖKOBAUDAT and EN 16258 truck datasets for >20 t vehicles typically report ~60–100 g CO₂-eq/t·km at realistic capacity utilisation including empty return). Two structural reasons for the low value:
- The model is linear in payload with zero intercept — an empty truck consumes zero
energy under
H = F/G. Real heavy-vehicle consumption has a large payload-independent base. - There is no capacity-utilisation or empty-return-trip term, which EN 15804+A2 requires A4 to include.
These are recorded as limitations of the source model, not as a CORRECTED verdict:
decision #23 requires that a CORRECTED formula produce a corrected value, and no
sourced replacement factor is on file. Correcting the value is exactly what the BLOCKED
verdict defers to the sourcing task (§8).
§4.2 C = 380 kW, D = 60 km/h, G = 36 500 kg — vehicle parameters¶
Row-invariant. Enter the result only through EF_truck (§3.1), which is BLOCKED as a
whole. Individually they are unattributed (380 kW contradicts the thesis's 386 kW;
60 km/h and 36 500 kg appear in neither the thesis text nor any source column).
They are documented here for traceability but carry no independent verdict — the composite
factor is the reported parameter.
§4.3 EF_sea = 6.442 080 × 10⁻⁶ kg CO₂-eq/(kg·km) — sea-freight emission factor¶
Verdict — Axis 2: BLOCKED. Derived circularly from the uncited constant 182.4
(finding A4-F2). Not IPCC EFDB, not an ÖKOBAUDAT transport EPD. lca.a4 raises.
Plausibility cross-check (not a substitute for a source). 6.44 g CO₂-eq/t·km is within the published container-ship band (roughly 6–15 g CO₂-eq/t·km depending on vessel class and utilisation). The number being plausible is precisely what makes the circular derivation dangerous: it would survive a smell test while being untraceable under ISO 14071 review.
§4.4 Distances K (truck) and N (sea)¶
Per PROJECT.md decision #26, distance inputs are project-specific and require no
geographic substitution — they are model inputs, not impact factors, and are therefore
outside the Axis-2 factor gate. They are carried into lca.a4 as project data.
Their attribution is nonetheless incomplete: cell A4!B25 names the routes (Sindia Quarry,
Cement du Sahel, Dakar local factories, Shanghai Port, Rotterdam Port) but no author, date,
or measurement basis. Recorded as a pending Chiwara attribution item (§8), consistent
with the treatment of the quantities module's EXPERT-JUDGEMENT constants. The values are
used as given; the module reports the route note alongside every distance.
Zero-distance rows. Water, Crude oil and Waste Oil carry K = 0 and no sea leg, per the
B25 on-site assumption. Their A4 impact is exactly zero regardless of the emission
factor, so these three rows are COMPUTED (value 0) and are not blocked by §4.1/§4.3.
The on-site assumption itself is a project modelling choice recorded in B25, not a
sourced parameter.
§4.5 The load factor H = F/G — answering the ROADMAP's "fix load-factor formula"¶
ROADMAP.md Phase 1 carried the note "lca.a4 — parity vs A4 sheet (fix load-factor
formula per Excel review)". That note is resolved here as "no formula fix required",
and the reason is worth stating because the surface reading is alarming.
H = F/G exceeds 1 for most rows — Earth reaches H = 40.96. Read as a capacity
utilisation ratio, a value of 40.96 is nonsense. But H is not a utilisation ratio: under
the thesis's linear-with-zero-intercept energy model, H = 40.96 is arithmetically identical
to 40.96 full-load truck trips, which is the correct scaling for hauling 1 495 t with a
36.5 t vehicle. The formula is right; the column header ("Load Factor") is what misleads.
The genuine defect in this region of the model is the one recorded in §4.1 — the absence
of an empty-return-trip term. Under H = F/G, the 41 return legs are charged zero
emissions, because an empty truck has zero payload and therefore zero modelled energy.
EN 15804+A2 requires A4 to account for them. That is a parameter problem (it is fixed by
adopting a t·km factor that already embeds a stated utilisation and empty-return
assumption), not a formula problem — which is precisely why it lands as BLOCKED on
Axis 2 rather than CORRECTED on Axis 1.
§4.6 Materials with no sea leg but an 88 km truck leg¶
Bitumen (row 15) and Glass (row 18) carry K = 88 with empty L/M/N. This is
consistent with B25 ("Bitumen, Glass → Dakar (local factories)") — a Dakar-manufactured
product has no import leg. No finding.
§5 Verdict summary¶
Axis 1 — Formula verdicts¶
| Formula | Verdict | Basis |
|---|---|---|
Truck_r = I·(F_r/G)·(C/D)·K_r |
VALIDATED |
Faithfully implements the thesis model; row mapping correct for all 18 rows; algebraically identical to the decision #26 per-tkm form (§3.1). 0.1% parity gate applies. |
Sea_r = F_r·$L$11·N_r |
VALIDATED (structure) |
The arithmetic mass × factor × distance is correct and reproduces to machine precision. The defect is in the parameter, not the formula — see Axis 2. |
Total_r = (J_r·K_r)+(M_r·N_r) |
VALIDATED |
Correct summation of legs; empty legs evaluate to 0. |
O23 = SUM(O5:O22) |
VALIDATED |
Correct range, no omitted or double-counted row. |
No CORRECTED formula exists in the A4 sheet. There is therefore no divergence fixture
for this module — per decision #23, divergence tests exist only for CORRECTED formulas.
Axis 2 — Parameter provenance¶
| Parameter | Value | Verdict | Consequence in lca.a4 |
|---|---|---|---|
EF_truck (road) |
45.114 155 g CO₂-eq/t·km | BLOCKED |
factor_for(ROAD_TRUCK) raises ValueError |
EF_sea (sea freight) |
6.442 080 g CO₂-eq/t·km | BLOCKED |
factor_for(SEA_FREIGHT) raises ValueError |
Truck distances K |
0 / 23 / 32 / 88 km | project input; attribution pending | used as given, route note carried |
Sea distances N |
21 279 / 4 834.58 km | project input; attribution pending | used as given, route note carried |
Both transport factors are BLOCKED. Consequently 15 of 18 materials are BLOCKED
and the aggregate A4 total is None. The three zero-distance materials (Water, Crude oil,
Waste Oil) are COMPUTED at 0 kg CO₂-eq.
Geographic provenance tags (decision #26)¶
| Mode | Tag | Note |
|---|---|---|
ROAD_TRUCK |
blocked |
Geographic assessment premature — the factor itself has no admissible origin. Once sourced from IPCC EFDB, decision #26 states no ÖKOBAUDAT-interim tag is required for A4 tkm factors; a Senegalese/SSA diesel-quality and fleet-age caveat will still apply. |
SEA_FREIGHT |
blocked |
As above. International shipping is geographically neutral by nature; the block is a provenance block, not a geographic one. |
| zero-distance rows | not_applicable |
Zero impact by construction. |
Vendorability (decision #25)¶
Neither factor is vendorable, because neither exists in a vendorable form: no ÖKOBAUDAT dataset is currently cited for A4. When sourced from IPCC EFDB, the factor is a published intergovernmental dataset and carries no redistribution restriction of the kind that gates INIES/EPD International (decision #25). No new legal-review blocker is created by this module.
Weidema pedigree axis-4 (geographic representativeness)¶
| Mode | Score | Note |
|---|---|---|
ROAD_TRUCK |
5 | Unknown origin; cannot be scored until a dataset is identified. Senegalese fleet age, diesel sulphur content and load practice all diverge from any EU default. |
SEA_FREIGHT |
5 | Unknown origin; circular derivation. Vessel class and route utilisation unknown. |
§6 Parity fixture — A4 sheet computed values¶
data_only=True extraction of A4!O5:O23, matched against
EF_truck · mass · d_truck + EF_sea · mass · d_sea recomputed in Python from the §3
primitives. Maximum relative error across all 18 rows and the total: 0.000000%
(exact to double precision).
| Row | Material | Mass (kg) | K truck (km) |
N sea (km) |
Truck (kg CO₂-eq) | Sea (kg CO₂-eq) | O total |
|---|---|---|---|---|---|---|---|
| 5 | Rock | 191 345.766 | 23 | — | 198.5453 | 0 | 198.545 260 |
| 6 | Gravel | 52 187.311 | 23 | — | 54.1509 | 0 | 54.150 888 |
| 7 | Sand | 85 196.234 | 23 | — | 88.4018 | 0 | 88.401 791 |
| 8 | Earth | 1 494 888.525 | 23 | — | 1 551.1346 | 0 | 1 551.134 559 |
| 9 | Water | 299 583.550 | 0 | — | 0 | 0 | 0 |
| 10 | Cement | 23 301.047 | 32 | — | 33.6386 | 0 | 33.638 626 |
| 11 | Steel Rebars | 1 330.600 | 88 | 21 279 | 5.2825 | 182.4000 | 187.682 543 |
| 12 | Steel plates | 2 415.872 | 88 | 21 279 | 9.5911 | 331.1702 | 340.761 316 |
| 13 | Steel profiles | 1 491.500 | 88 | 21 279 | 5.9213 | 204.4563 | 210.377 659 |
| 14 | Polyethylene film | 5 025.600 | 88 | 21 279 | 19.9519 | 688.9144 | 708.866 216 |
| 15 | Bitumen | 376.476 | 88 | — | 1.4946 | 0 | 1.494 627 |
| 16 | Crude oil | 60.115 | 0 | — | 0 | 0 | 0 |
| 17 | Waste Oil | 120.229 | 0 | — | 0 | 0 | 0 |
| 18 | Glass | 21.138 | 88 | — | 0.0839 | 0 | 0.083 921 |
| 19 | Anti-termite | 1 445.750 | 88 | 21 279 | 5.7397 | 198.1849 | 203.924 573 |
| 20 | Wall paint | 393.210 | 88 | 21 279 | 1.5611 | 53.9016 | 55.462 693 |
| 21 | Antirust paint | 138.000 | 88 | 21 279 | 0.5479 | 18.9172 | 19.465 047 |
| 22 | Floor Tiles | 3 379.898 | 88 | 4 834.58 | 13.4184 | 105.2661 | 118.684 442 |
| 23 | TOTAL | 3 772.674 159 |
Masses are LCI Final!F3:F20 as already fixtured in
backend/tests/fixtures/test_a1_a3_material_database.py (BOQ_WEIGHT_KG) — lca.a4
consumes mass from the quantities/LCI mass path and does not re-derive it.
Use of this table. It is a formula-parity fixture only. It is reproduced by
lca.a4 solely when the caller explicitly passes EXCEL_REFERENCE_FACTORS — the
BLOCKED Excel-derived factor set. The default (production) factor set produces None.
This keeps decision #23's rule intact: a BLOCKED parameter gets no default, while the
VALIDATED formula still gets its 0.1% gate.
§7 Routes and distances for the Keur Songho reference project¶
| Material | Truck leg | Sea leg | Route (A4!B25) |
|---|---|---|---|
| Rock, Gravel, Sand, Earth | 23 km | — | Sindia Quarry |
| Water | 0 km | — | On-site (no transport) |
| Cement | 32 km | — | Cement du Sahel quarry |
| Steel rebars / plates / profiles | 88 km | 21 279 km | Shanghai Port → Dakar Port |
| Polyethylene film | 88 km | 21 279 km | Shanghai Port → Dakar Port |
| Bitumen | 88 km | — | Dakar (local factory) |
| Crude oil, Waste Oil | 0 km | — | On-site (no transport) |
| Glass | 88 km | — | Dakar (local factory) |
| Anti-termite | 88 km | 21 279 km | Shanghai Port → Dakar Port |
| Wall paint | 88 km | 21 279 km | Shanghai Port → Dakar Port |
| Antirust paint | 88 km | 21 279 km | Shanghai Port → Dakar Port |
| Floor Tiles | 88 km | 4 834.58 km | Europe (Rotterdam Port) → Dakar Port |
§8 New BLOCKED items → sourcing tasks and attribution requests¶
| # | Item | Type | Action required |
|---|---|---|---|
| A4-1 | Road-freight GWP factor (kg CO₂-eq/t·km) for a Senegalese/SSA heavy-goods vehicle | BLOCKED factor |
Source from IPCC EFDB or an ÖKOBAUDAT transport EPD (decision #26). Must state capacity utilisation and empty-return treatment per EN 15804+A2. |
| A4-2 | Sea-freight (container) GWP factor (kg CO₂-eq/t·km) | BLOCKED factor |
Source from IPCC EFDB or an ÖKOBAUDAT transport EPD. Must state vessel class. |
| A4-3 | Origin of the constant 182.4 in A4!L11 |
provenance | Chiwara attribution: who computed it, from what, in what units. If unrecoverable, A4-2 supersedes it entirely. |
| A4-4 | Truck distances (23 / 32 / 88 km) and sea distances (21 279 / 4 834.58 km) | attribution pending | Chiwara attribution: author, date, measurement basis; and the meaning of the 19155+2124 and 4830.93+3.65 segment splits. |
| A4-5 | On-site (zero-transport) assumption for Water, Crude oil, Waste Oil | attribution pending | Confirm the Keur Songho water source is on-site (borehole); confirm oils are delivered with plant rather than hauled separately. |
| A4-6 | C2 (Transport) reuses the same BLOCKED truck factor |
forward dependency | lca.c2 (Phase 2) inherits A4-1. Do not implement lca.c2 against 0.26 either. |
Items A4-1 and A4-2 are the gate: until both are closed, no A4 total can be reported for any project, and the module says so loudly.
§9 Session prerequisite status¶
| Prerequisite | Status |
|---|---|
All sheets of 2_lca_vn.xlsx inspected for A4 content |
✅ 12/12 enumerated; A4 extracted in full |
Raw formulas read with data_only=False |
✅ |
Computed values read with data_only=True |
✅ |
| Thesis cross-check for factor provenance | ✅ pp. 58–61 |
| Every factor traced to an Excel cell | ✅ §4 |
| Decision #23 verdict per formula and per parameter | ✅ §5 |
| Decision #25 vendorability assessed | ✅ §5 |
| Decision #26 geographic tags assigned | ✅ §5 |
| No non-permitted-source factor (ICE / ecoinvent / Sphera-GaBi) introduced | ✅ none used |
| No invented number | ✅ every value is an A4-sheet cell or an exact algebraic reduction of one |