Contracting-entity mapping workbook

This contracting-entity mapping workbook gives analysts a dated crosswalk between customer, site, signing entity, parent and agreement IDs. It keeps the legal-entity role separate from the parent group and exposes ambiguous time ranges. Live formulas identify active unambiguous records, overlaps and missing fields for a fixed as-of date. The existing hierarchy guide remains the explanation of why those levels differ.

What to enter

Enter a stable mapping ID, customer ID, site ID, contracting-entity ID, parent ID, agreement ID, inclusive effective-from and effective-to dates, and evidence reference. B4 controls the analytical as-of date. The sample key is site plus agreement, with one contracting entity in force per date. Use a more detailed key if product-specific agreements or other authorized structures require one.

Use the workbook

1. Establish the accepted contracting entity from the relevant agreement evidence before entering the crosswalk. Keep parent and site identifiers separate.
2. Add dated rows when an attribution changes, closing the earlier period instead of overwriting historical IDs. Inclusive end dates must not overlap the next start.
3. Set B4, resolve Overlap, Duplicate ID and Incomplete rows, then use active unambiguous records for the defined analytical review.

Keep the amber inputs separate from the pale blue calculation columns. The worksheet has 20 prefilled detail rows, from 13 through 32. If you need more records, extend every affected formula and summary range together, then check a first, middle and final row. Save a dated working copy before replacing the sample.

The synthetic example

EM-01 is active on October 6, 2026. EM-02 ended in 2025. EM-03 and EM-04 both cover SITE-03/AG-03 during overlapping periods, so neither is treated as an active unambiguous record. EM-05 has no contracting entity. The sample therefore has one active record, two overlap exceptions and one incomplete record. Closing EM-03 on May 31 removes the overlap without deleting its history.

How the results update

Mapping status checks identifier uniqueness, required fields and chronological dates. It then counts rows with the same site/agreement whose intervals overlap the current row’s interval. Only a complete, nonoverlapping record covering B4 becomes Active. Out-of-period rows remain available for historical use. The summary reports record counts rather than claiming a distinct customer or group count.

Limits and a useful next step

This is an analytical crosswalk, not a legal-entity verification service. A complete ID does not prove signatory authority, common ownership or a right to combine customer data. Resolve those points with the responsible owner and supporting evidence. The workbook does not join invoices, change CRM records or recalculate concentration automatically. Preserve source keys and the definition of the economic relationship when the mapping feeds a later report.

For the underlying method, read account hierarchy parents and subsidiaries. sold to bill to ship to covers the adjacent question. If you want to review the required fields and one reporting output, discuss your setup. Views depend on your records and agreed definitions.