2. Excel-based rating calculator architecture

A reliable NCAP rating calculator is not a single worksheet filled with formulas. It is a layered workbook in which each sheet has a single responsibility: capturing inputs, performing scoring calculations, and presenting results. Separating these concerns makes the calculator auditable, testable, and maintainable across protocol revisions. This chapter defines the workbook structure, the role of each sheet type, and the conventions that keep the calculation chain traceable from raw test data to the final star rating.

Why a Layered Workbook Beats a Monolithic Sheet

NCAP protocols (Euro NCAP, C-NCAP, Latin NCAP, and others) share a common pattern: test results are converted into scores per assessment area, those scores are weighted and aggregated into an overall score, and the overall score maps to a star rating. When inputs, calculations, and outputs live on one sheet, a single overwritten cell can silently corrupt the final rating, and reviewers cannot see which test result drove which score.

A layered design solves three practical problems. First, auditability: every output cell can be traced back through named ranges to a specific input. Second, version control: protocol parameters (weightings, thresholds, caps) live in one place, so a regulation update touches one sheet rather than dozens of formulas. Third, automation readiness: a clean input/output boundary is exactly what a Python layer needs later to read inputs, trigger recalculation, and extract results.

Workbook Structure at a Glance

The recommended workbook uses five sheet categories. The diagram below shows the data flow between them: inputs feed the calculation engine, the engine reads protocol parameters, and results flow to the dashboard.

Input Sheets: One Sheet per Assessment Area

Each assessment area gets its own input sheet, prefixed INP_. For a frontal impact area, the sheet might contain one row per test criterion: driver dummy head HIC, chest deflection, femur loads, and so on. Each row carries the measured value, the unit, the test configuration (e.g., 64 km/h ODB), and a free-text source reference to the test report.

Two conventions matter here. First, inputs are values, not scores. The sheet stores the measured HIC of 412, not the resulting 3.8 points. Scoring logic belongs in the calculation layer, so that a protocol change to the HIC scoring curve does not require re-entering test data. Second, every input cell is validated at entry using Data Validation: numeric ranges, allowed unit lists, and mandatory source references. A blank source reference should trigger a visible warning, because an untraceable input is an unauditable input.

A minimal input row looks like this:

Parameter Sheet: The Single Source of Truth

The PRM_Protocol sheet holds every number that the protocol defines rather than the test produces: area weightings, maximum points per criterion, score caps, penalty thresholds, and the overall-score-to-star boundaries. Each parameter is given a defined name (for example W_Frontal, Max_Frontal, Star_5_Threshold) so that formulas read as =FrontalScore * W_Frontal rather than =D14*0.4.

This sheet also records the protocol version and effective date. When a regulation is revised, you duplicate the parameter sheet, update the values, and point the calculation layer at the new named ranges. The input data and the calculation formulas remain untouched, which is precisely the property that makes regression testing feasible.

Calculation Sheets: Formulas Only, No Manual Entry

The calculation layer is split into three sheets to keep each formula short and reviewable. CALC_Area_Scores converts each measured value into points using the protocol's scoring curves, typically implemented with nested IF or LOOKUP against a step table. CALC_Weighted_Total applies area weightings and sums to the overall score, applying any caps or penalties defined in the parameter sheet. CALC_Star_Rating maps the overall score to a star count using the threshold table.

A representative formula in CALC_Area_Scores for a capped linear scoring curve might read:

=MIN(Max_Frontal, MAX(0, (HIC_Limit - INP_Frontal!B5) / HIC_Slope))

The MIN/MAX pair enforces the cap and the zero floor in one expression, so the score can never exceed the criterion maximum or fall below zero even if an out-of-range input slips through. Every calculation cell should be protected, and the sheet should contain no typed constants — if a number appears in a formula, it belongs in the parameter sheet.

Output Dashboard: Read-Only Presentation

The dashboard sheets, prefixed OUT_, contain no calculations of their own — only references to calculation cells, charts, and conditional formatting. OUT_Star_Rating shows the headline result: overall score, star count rendered as filled and empty star glyphs, and the protocol version used. OUT_Area_Breakdown presents a bar chart of each area's achieved score against its maximum, making weak areas immediately visible. OUT_Gap_Analysis lists the points lost per criterion, sorted descending, which is the sheet engineers actually use to decide where to improve the vehicle. OUT_Validation_Status aggregates the validation checks into a single PASS/FAIL banner.

Because the dashboard is read-only, it can be shared with stakeholders who should not edit the model. Protecting these sheets and hiding their formulas prevents accidental corruption while still allowing the charts to refresh automatically when inputs change.

Validation Checks and Failure Symptoms

The CALC_Validation_Checks sheet runs a small set of assertions: every required input is non-blank, every measured value lies within its physically plausible range, every area score is between zero and its maximum, and the overall score is consistent with the sum of weighted area scores. Each check returns TRUE or FALSE, and the dashboard surfaces any FALSE as a red flag.

Typical failure symptoms and their causes are worth memorizing. A star rating that jumps by one star after a trivial input edit usually indicates a threshold boundary being crossed by a rounding error — check whether the overall score is rounded before or after the star lookup. An area score stuck at its maximum despite a poor test result usually means the cap formula is inverted or the input reference points at the wrong row. A dashboard showing #REF! after a sheet rename means a named range was broken; use the Name Manager to repair it rather than retyping formulas.

Design Rules That Keep the Workbook Maintainable

• One responsibility per sheet. Inputs never contain formulas that produce scores; calculations never contain typed constants; outputs never contain calculations.
• Consistent prefixes. INP_, PRM_, CALC_, and OUT_ make sheet purpose obvious at a glance and simplify Python sheet selection later.
• Named ranges over cell addresses. Formulas should read like the protocol, not like a spreadsheet coordinate map.
• Protect the calculation layer. Lock formula cells and unlock only input cells, so reviewers can edit data without breaking the model.
• Version the parameter sheet. Record protocol version and effective date on every run, so archived results remain interpretable.

With this architecture in place, the workbook becomes more than a calculator: it is a documented, testable model of the NCAP scoring rules. That structure is also what makes the next step — driving the workbook from Python — tractable, because the automation layer only needs to write to input sheets, trigger recalculation, and read from output sheets.