A procurement lead wants to understand where supplier dependence deserves closer review. A list of the largest award totals will not answer that question: an award can appear on several transaction rows, modifications can reduce commitments, and different legal entities can share a reported parent. You will build a reproducible concentration view with explicit supplier keys, signed amounts and a defined positive-obligation denominator.
The case uses real National Science Foundation awarding-agency prime-contract transactions for federal FY2024. You will produce a supplier scorecard, Pareto and category views, a parent-grouping sensitivity analysis and a concise investigation note. The lesson supports a review shortlist; the records alone do not establish a procurement problem, noncompliance, price competitiveness or achievable savings. Lesson 6 remains complete and is intentionally skipped.
What you will learn
- Distinguish a contract transaction from an award total and obligations from cash payments.
- Preserve negative and zero commitments, audit supplier identifiers and reconcile every source row.
- Calculate supplier shares, top-five share and HHI using gross positive transaction obligations.
- Compare legal-entity and conservative reported-parent groups while keeping mapping conflicts visible.
- Turn numerical findings into evidence-linked questions a procurement team can investigate.
Comfort with Excel grouped sums, percentages and filters is sufficient. Lessons 2 and 3 provide useful preservation, reconciliation and join concepts; this package also works as a standalone reference. No programming is required. The blueprint allowed 55 minutes, an unvalidated planning estimate rather than measured learner time. Record your actual upload, waiting, reading and checking time.
Keep three kinds of statement separate
Source observations are recorded transaction amounts, dates, identifiers and labels. Analytical results are calculations from those observations, such as a supplier's positive-obligation share. Teaching conventions are explicit choices, including the concentration denominator, deterministic tie order and conservative parent-conflict rule. An identifier labeled as a parent in the source is an observation; treating it as verified current ultimate ownership would be an unsupported extra conclusion.
One source row is a contract transaction, including an initial action or modification. The selected period is action dates from 1 October 2023 inclusive to 1 October 2024 exclusive. The API recipe expresses the same window as 1 October 2023 through 30 September 2024 inclusive. It selects NSF as the awarding agency and award types A, B, C and D. A funding-agency query would answer a different question, even though both agency fields happen to be NSF on this frozen population.
Set up a fresh conversation
Extract the ZIP before opening files. Read this guide, then keep the reference workbook and instructor answer files closed until you have attempted the analysis. Start a fresh ChatGPT conversation with file upload, calculation and downloadable-file capabilities. A particular model name is not required. If the conversation cannot read or calculate on an attachment, resolve that access issue before accepting numerical findings.
Upload all seven starting files from 01_start. Use smaller batches if your account limits attachments, and wait until all seven are available before sending Prompt 1. The filenames below are exact.
| Starting file | Why you need it |
|---|---|
| usaspending_nsf_fy2024_contract_transactions.csv | Exact frozen 1,060-row, 17-field teaching extract. |
| L08_Parent_Text_Projection.csv | Aligned original parent-export strings for all 17 selected fields. |
| L08_Parent_Selection_Controls.csv | Aligned parent evidence for award type and agency/date selection. |
| L08_Source_Register.csv | Frozen identities, provenance and acquisition status. |
| L08_Supplier_and_Metric_Policy_v1.json | Versioned identity, grouping and metric rules. |
| L08_Data_Dictionary_and_Method_Card.pdf | Definitions and instructions independent of this guide. |
| L08_Source_and_Reuse_Notice.txt | Provenance, attribution, inclusion and reuse limits. |
The recovered source CSV matches the original SHA256, size and shape exactly. Its SHA256 is 1d316e8655dec0f2fda42d5187632de99ac7701ba7cb5c2db969a22b6aa164e2. The original 297-column parent archive also matched its registered hash. The standalone original request file and generation-response bytes were not recovered; the included request is a reconstruction of the blueprint's recorded body, not the original file.
The old preparation process changed the text formatting of 1,236 amount cells while retaining their exact decimal values. The release therefore keeps the byte-identical teaching CSV and the original-text projection side by side. Do not rewrite either to make the formatting look uniform. The broader parent archive is not republished here; its exact official URL, hash and tested retrieval route are documented. If that generated URL expires, a fresh export is a new version and must be inspected before replacing the frozen population.
Stage 1 Inspect the population and identity rules
Copy Prompt 1 in full from the text below or L08_Exact_Prompts.txt.
Act as an analytical assistant to a management consultant who is comfortable with Excel and does not program. This is Lesson 8, Analyze procurement and supplier concentration. Do not calculate concentration yet.
Actually open all seven starting files: usaspending_nsf_fy2024_contract_transactions.csv; L08_Parent_Text_Projection.csv; L08_Parent_Selection_Controls.csv; L08_Source_Register.csv; L08_Supplier_and_Metric_Policy_v1.json; L08_Data_Dictionary_and_Method_Card.pdf; L08_Source_and_Reuse_Notice.txt. If you cannot read files or execute calculations, state that limitation and stop instead of inventing findings.
Compute the frozen CSV SHA256 and compare it with 1d316e8655dec0f2fda42d5187632de99ac7701ba7cb5c2db969a22b6aa164e2. Check 1,060 parsed data rows, 17 fields and 385,746 bytes. Read original fields as strings, preserving IDs, UEI case, amounts, names and dates. Explain why the hash-bound source-row locator is not an operational transaction ID.
Verify one-to-one control alignment using original ordinal and exact contract_transaction_unique_key. Also align the 17-field parent text projection; compare every field, preserving text-format differences and checking exact numeric equality separately in the two amount fields. Stop on nonmonetary differences or unequal monetary values. Check every action date is >=2023-10-01 and <2024-10-01, awarding agency is National Science Foundation, and parent award_type_code is A/B/C/D. Distinguish this from funding agency and from all NSF expenditure. Audit exact transaction-key uniqueness, exact duplicate candidates, missing/invalid UEIs, names attached to multiple UEIs, and amount parse failures. Retain every row and treat duplicate candidates as evidence, not permission to delete.
Explain the signed obligation reconciliation, why unknown is not zero, why total_dollars_obligated must not be summed over transactions, and why concentration will use gross positive transaction obligations. State the legal-entity and conservative reported-parent rules, including unresolved cases. Distinguish source observations, analytical outputs and teaching choices. Report what you actually checked, discrepancies and stop conditions. Do not claim the original request JSON bytes, full-parent redistribution permission or human approval. Stop after this source/policy gate and wait for Prompt 2.
Checkpoint 1
Confirm 1,060 rows, 17 original fields and 1,060 distinct transaction keys. Verify every original field remains text. When you create the ledger in Stage 2, every source row must receive stable, hash-bound lineage. A row locator is not an operational transaction ID. All rows must remain visible, including negative and zero amounts. The source has no duplicate candidates under exact-row or exact-transaction-key tests; a future candidate would require review rather than automatic deletion.
Check the source signs before drawing a chart: 819 positive, 71 negative, 170 zero and no unknown transaction amounts. Parent controls show A 85, B 148, C 560 and D 267, with observed action dates from 2 October 2023 through 30 September 2024. The absence of a 1 October transaction is an observation, not a shortened selection window.
The legal key is the exact recipient UEI. A valid UEI has 12 ASCII letters or digits, matching [A-Za-z0-9]{12}. Do not trim whitespace, change case, use a company name as a join key or infer relationships from similar wording. This source has complete valid UEIs, so the disclosed unresolved fallback is not used. The name audit still matters: some identical names refer to different UEIs. Retain names as reported labels, including aliases and conflicts.
Stage 2 Build and explain the scorecards
Copy Prompt 2. Continue in the same conversation so the inspected inputs and policy remain available.
Run the legal-entity analysis now using only the accepted frozen source and L08-PROCUREMENT-v1.0 policy. Do not filter out negative, zero or unknown amounts. Preserve all 17 original strings in original order and append separately derived fields.
Create a full 1,060-row ledger. Add the source filename/hash, one-based source row and stable ID L08:sha256:<full source hash>:row:<six-digit ordinal>. Use the exact contract_transaction_unique_key as the source operational identifier, not the added ordinal. Append parent_federal_action_obligation_text and parent_total_dollars_obligated_text from the aligned parent text projection; do not overwrite the frozen strings or pretend shortened decimal formatting is a changed amount. Parse federal_action_obligation exactly into separate analytical values and mutually exclusive positive/negative/zero/unknown statuses. Calculate gross positive, signed negative, absolute deobligations, known signed sum and complete net only when unknown count is zero. Retain original total_dollars_obligated without using it as transaction spend.
Group legal entities by exact recipient UEI under the uploaded fallback policy; valid UEI is ASCII [A-Za-z0-9]{12}, with no trimming or case normalization. Do not merge names. Preserve aliases and name-to-key conflicts. Show each group's transaction/sign counts, positive obligations, signed negatives, absolute deobligations, net, positive-basis share, rank, cumulative share and HHI contribution. Rank only positive groups; display by positive obligations descending then exact supplier key ascending. Top five means exactly the first five positive groups in that order. HHI=sum((100*positive_share)^2), scale 0-10,000. Keep zero-positive and negative-net suppliers visible. Disclose the complete supplier and known-amount population and any unmapped share beside the metrics.
Build an exact-code NAICS summary, PSC summary and observed NAICS-by-PSC matrix with counts, supplier counts, positive and signed/net amounts. Keep missing categories explicit and audit description variants. Reconcile every group view to all source rows and amount/sign totals. Demonstrate positive plus signed negative equals net; never add absolute deobligations to positives to obtain net. Return the ledger, legal-supplier table, category tables, duplicate/key audit and reconciliation as downloadable CSVs, plus concise executed-check results. Do not invent procurement problems, agency approval, payments or savings.
Why one amount cannot serve every purpose
federal_action_obligation is the signed amount for the transaction. Sum it once per preserved row for net obligations. Gross positive obligations sum only known values above zero; signed negative obligations sum values below zero; absolute deobligations are the negative sum multiplied by minus one. Zero is a known amount. Blank or invalid amounts remain unknown and must be counted separately.
total_dollars_obligated is cumulative award information. Repeated award totals are not additive transaction commitments, so do not sum that field across rows. Neither obligations measure records cash paid to a supplier. The lesson does not infer invoices, payment timing or contract value remaining.
Why concentration uses a positive base
Shares need an explicit nonnegative denominator. Here it is gross positive transaction obligations for the complete frozen population. A supplier's share is its positive obligations divided by all positive obligations. HHI adds (100 × share)^2 across all groups, giving a 0–10,000 scale. Do not calculate ordinary concentration shares from negative net supplier amounts.
Rank suppliers with positive obligations. Keep all zero-positive suppliers in the scorecard as unranked evidence. Sort positive amounts descending and exact supplier keys ascending to resolve display ties; top five means exactly the first five positive groups. Share and cumulative-share columns must use the same denominator. No antitrust or procurement threshold is applied to this descriptive case.
The Pareto view shows every positive supplier, not just the top five, and discloses the zero-positive population. The category matrix groups exact NAICS and PSC codes and retains all observed pairs. Category descriptions are labels, not a substitute for codes. A category's supplier count is a within-category count and cannot be added across categories because one supplier can appear in several.
| Your output | Acceptance requirement |
|---|---|
| Full transaction ledger | 1,060 rows; all 18,020 original source cells unchanged; lineage, parsed amounts, sign status and identity/mapping fields appended. |
| Legal supplier and category tables | Signed amount components, counts, known/unknown coverage, one explicit positive share denominator and complete category evidence. |
| Formula workbook | Live shares, ranks, cumulative values, top-five share, HHI and guarded net calculations; prepared group inputs identified. |
| Client exhibits and values | Readable Pareto and concentration/category exhibits with units, population, denominator and omitted display groups disclosed. |
| Investigation note | Three priorities linked to rows or tables, with questions and needed evidence rather than unsupported savings or compliance claims. |
Stage 3 Build the workbook and client exhibits
Use Prompt 3 to turn the verified tables into a simple workbook, readable exhibits and an investigation note. Check every chart's title and denominator. The full ledger remains a companion CSV so the workbook can concentrate on a clear analytical build.
Turn the verified legal-entity analysis into an Excel reference and concise client exhibits. Use a simple workbook with Scorecard, Supplier detail, Parent practice (initially pending), Categories, Policy and Checks. Keep the full transaction ledger as a companion CSV. State which workbook inputs are prepared aggregates and which cells are formulas; a live share formula does not imply live transaction regrouping.
Include live formulas for net=positive+signed negative, shares, cumulative shares, positive-group ranks with deterministic display tie order, top-five share and HHI. Prepared supplier rows may retain their original physical order after an edit; recalculate rank, display-order index and cumulative share, and provide a separate leading-supplier view that updates its order. Preserve known sums separately from any incomplete net. Add checks for count/amount reconciliation, mapping coverage, unknowns and valid denominators; suppress unavailable rates rather than returning zero. Give source SHA, scope, policy versions and supplier key alongside labels. Include the complete observed NAICS-by-PSC matrix and a readable category summary. Format amounts with USD units and percentages consistently. Recalculate and inspect the exported workbook if your environment permits; report exactly what was tested.
Create publication-ready PNG exhibits and their full underlying CSV values: a Pareto view covering all positive legal suppliers (with zero-positive groups disclosed outside the positive ranking), and a category exposure exhibit that states any top-N display restriction and the amount/share in all omitted categories. Explain how the sign reconciliation differs from the concentration denominator. Make labels, axes, units, populations, denominators and date scope explicit.
Draft a concise client investigation note with three evidence-linked priorities, the data needed to investigate each and what the data cannot establish. Concentration is a diligence signal, not proof of a procurement problem, noncompliance or achievable savings. Do not infer ownership from names. Include Treasury/USAspending attribution and the applicable D&B business-name attribution and reuse notice. Return the workbook, PNGs, chart-value CSVs and note. Do not claim native Excel or human usability testing unless actually performed.
Stage 4 Practice with reported parents
Upload L08_Reported_Parent_Mapping_v1.csv and L08_Parent_Mapping_Evidence_v1.csv from 02_sources. Rebuild and audit the supplied teaching mapping against the original transactions before accepting it. Every legal supplier must map to exactly one practice group. Accept a reported parent only when every transaction row for that legal supplier has a valid nonblank parent UEI and the supplier has exactly one distinct parent UEI. If there is a conflict, missing value or invalid parent, keep the entire legal entity standalone with a visible unresolved status.
Copy Prompt 4. Do not replace historical parent values or infer ownership from names.
Now complete the practice using the two newly uploaded files L08_Reported_Parent_Mapping_v1.csv and L08_Parent_Mapping_Evidence_v1.csv. Rebuild and verify this teaching mapping against every original transaction; do not trust it merely because it is supplied.
Use one row per exact legal supplier. Accept a reported parent only when every transaction has a valid nonblank parent UEI and all rows give exactly one distinct parent UEI. For any conflicting, missing or invalid parent, retain the entire legal entity as its own UNRESOLVED_LEGAL:<supplier_key> group. Do not choose the latest parent, merge similar names, assume ultimate ownership or invent approval. Display an accepted group's latest nonblank reported parent name by action_date then original ordinal; display unresolved groups using the legal name plus [parent unresolved]. These label choices never resolve a parent-key conflict. Keep all source recipient/parent fields and evidence visible.
Audit mapping cardinality, aliases/conflicts, unmatched keys and row counts before and after the many-to-one assignment. Hold the 1,060 transactions, amount classification and gross positive denominator unchanged. Calculate parent group counts (all and positive), rankings, positive shares, top-five share and HHI using the same definitions and deterministic tie ordering. Show signed negative and net obligations alongside concentration. Report unresolved coverage by legal entities, transaction rows and gross positive amount/share, leaving those groups in the denominator.
Compare legal-entity and conservative reported-parent results, identify actual groups that merge and explain any metric changes or nonchanges without rounding away a small difference. Update Parent practice and Checks in the workbook. Provide parent scorecards, mapping/coverage audits, comparison CSV and a short interpretation. Describe this as a conservative reported-parent sensitivity analysis, not verified ultimate-owner concentration.
Questions to answer
1. How do all-group and positive-group counts change when the conservative reported-parent rule is applied?
2. Which legal entities merge, and what source rows support those links? Which entities remain unresolved?
3. How do top-five share, rankings and HHI change on the unchanged positive-obligation base?
4. Why can group count change while top-five share stays the same? Why does one merged pair change HHI and another not?
5. What additional evidence would be needed to describe actual current ultimate ownership?
Compare your rebuilt mapping with both supplied files by exact legal key and source row, not merely by total group count. A matching HHI does not prove a correct mapping. Keep the instructor scorecards closed until your own practice calculation is complete.
Stage 5 Check the work in Microsoft Excel
Use Prompt 5 to obtain instructions for the workbook ChatGPT actually generated. Your workbook's cell positions may differ from the supplied reference.
Prepare a verification handoff for an Excel-comfortable consultant. Supply exact sheet, column and formula instructions for checking the source row count, amount/sign partition, unique transaction keys, one legal-to-parent mapping, positive+signed-negative=net, supplier totals, top-five denominator, HHI and parent comparison. Use an imported working copy, preserve original CSV strings, and do not require programming. Include a full-list COUNTIF duplicate check before using Remove Duplicates on a copied identifier column for a distinct count.
Show how to recompute gross positive and signed negative sums from the raw transaction amount in Excel, how to count blanks and parse errors separately from zero, and how to calculate HHI from fractional shares using SUMSQ(range)*10000. Explain that workbook aggregate inputs must be rebuilt if the source population or mapping policy changes. Select actual positive, negative and zero source records and give exact keys/ordinals, source amounts and group assignments for a manual trace; do not fabricate illustrative transactions.
State acceptance tolerances: exact row/key counts and original strings; amounts exact to source cents; shares/HHI within normal floating-point tolerance (absolute 1e-12 for fractional shares and 1e-8 for HHI), with display rounding separate. Report calculation, exported-file, prompt replay, native Excel and human-usability checks separately, marking unperformed work unperformed. List any remaining mismatch rather than saying everything passed.
Import source text safely
In Excel for Windows, choose Data > From Text/CSV > Transform Data. Remove an automatic Changed Type step if one appears, then set all 17 original columns to Text before loading. Do not save over the source CSV. This avoids automatic conversion of identifiers, dates and numeric strings. Add numeric helper columns separately for calculations.
For a table named Transactions, add an Amount helper using =IF([@federal_action_obligation]="","",IFERROR(NUMBERVALUE([@federal_action_obligation],".",","),"")). Add AmountStatus with =IF(ISNUMBER([@Amount]),IF([@Amount]>0,"positive",IF([@Amount]<0,"negative","zero")),"unknown"). The source uses a decimal point. Your Excel locale may use semicolons between arguments.
Check =ROWS(Transactions[contract_transaction_unique_key]) equals 1,060. A duplicate-key helper =COUNTIF(Transactions[contract_transaction_unique_key],[@contract_transaction_unique_key]) must be 1 on every row. Filter an actual negative row, zero row and positive row and compare source text to the ledger. Do not use IFERROR(...,0) because that would turn an unknown amount into a known zero.
Recalculate the signed totals
Use SUMIF(Transactions[Amount],">0",Transactions[Amount]) for positives and SUMIF(Transactions[Amount],"<0",Transactions[Amount]) for signed negatives. Check the counts with COUNTIF on AmountStatus. Known net is positive plus signed negative. A complete-net formula should return blank if COUNTIF(Transactions[AmountStatus],"unknown") is nonzero. This rule does not suppress the separate known totals or the unknown count.
Create a PivotTable from the working table, placing exact recipient UEI in Rows and a positive-only helper in Values as Sum. Define that helper as =IF(ISNUMBER([@Amount]),MAX([@Amount],0),""). Keep a separate unknown count so Excel's ignoring blanks cannot conceal missing data. Confirm 322 legal groups and 297 with positive obligations, then compare selected sums to the supplied scorecard.
Check concentration and workbook boundaries
Divide each group positive total by the complete positive total; square its percentage share as (share*100)^2; sum all HHI terms. Do not square a rounded percentage displayed in a chart. Sort by positive obligations and key to identify exactly five members, then divide their combined positives by the same base. Check the final cumulative share equals 100% within normal floating-point tolerance.
The reference workbook has six tabs: Scorecard, Supplier detail, Parent practice, Categories, Policy and Checks. Supplier detail contains one row per legal entity; Parent practice derives group aggregates from the mapping with one row per legal supplier; Categories retains all 222 observed NAICS/PSC pairs. Full transaction evidence is in the companion CSV. The workbook recalculates group-level formulas; it is not a transaction import engine and does not automatically refresh from an edited CSV. Adding new source rows or identities requires rebuilding and checking the prepared tables.
Prepared supplier rows keep their released physical order after an input edit. The rank, display-order index and cumulative share recalculate, and the Scorecard's leading-supplier display updates its order. Do not treat the row position in Supplier detail as the current rank. Category inputs and exported PNGs are separate prepared snapshots; changing a supplier amount does not rebuild those artifacts.
Use the supplied Excel verification instructions for the released workbook's exact cells and all expected terminal checks. Keep the released copy intact. On a disposable copy, change one prepared input and confirm the dependent share, ranking and check results respond; then close without saving or restore the value. If a reference check fails, inspect the formula and its denominator before interpreting the display.
Stage 6 Compare with the reference answer
After completing your own work, upload seven files from 03_reference: L08_Legal_Supplier_Scorecard.csv, L08_Parent_Scorecard.csv, L08_Practice_Comparison.csv, L08_Reconciliation.csv, L08_NAICS_Summary.csv, L08_PSC_Summary.csv and L08_Category_Matrix.csv. Use Prompt 6 to compare by exact supplier/group keys or category-code pairs. Download both the original attempt and any repaired files so the learning record preserves what changed.
I have completed my own analysis and now uploaded seven instructor reference CSVs from 03_reference: L08_Legal_Supplier_Scorecard.csv, L08_Parent_Scorecard.csv, L08_Practice_Comparison.csv, L08_Reconciliation.csv, L08_NAICS_Summary.csv, L08_PSC_Summary.csv and L08_Category_Matrix.csv. Compare my generated outputs to those reference files by exact supplier/group keys or exact category-code pairs, not by display names or current row positions. Check every count, source amount, rank, denominator, share, HHI term and category/parent reconciliation that both versions contain. Identify the first divergence and its cause, propose a correction, and rerun only the affected analysis and downstream artifacts. Do not overwrite the frozen input or change a policy to force the answer. Distinguish differences in display rounding from analytical differences. If all compared values agree, state exactly which files/fields were checked and which checks still require Excel or a human learner.
Reference checkpoint after your own attempt
| Check | Executed reference result |
|---|---|
| Signed reconciliation | $780,677,938.48 positive plus −$5,015,835.93 negative equals $775,662,102.55 net. |
| Legal supplier population | 322 legal suppliers; 297 positive; 25 zero-positive; 17 negative-net. |
| Concentration base | $780,677,938.48 gross positive transaction obligations; no unknown amount and no unresolved legal-identity share. |
| Legal top-five share and HHI | 57.06375558% and 1654.35608537 on the defined positive base. |
| Reported-parent practice | 320 groups, 296 positive; same top-five share; HHI 1654.35610409. |
| Parent conflicts | 24 legal entities remain standalone: 113 transactions and $36,679,371.68 positive obligations, 4.6984% of the base. |
| Category coverage | 72 NAICS codes, 102 PSC codes and 222 observed code pairs. |
What the results do and do not establish
The largest legal supplier, LEIDOS, INC., accounts for 38.9316% of gross positive obligations in this selected agency-year population. That supports asking which services, award structures and continuity arrangements explain the exposure. It does not show an excessive price, lack of competition or avoidable risk. The source lacks the contextual evidence needed for those conclusions.
The conservative parent exercise changes the measured grouping only slightly. That outcome reflects this particular rule and source evidence, including the decision to leave 24 conflicts unresolved. It does not establish that a fully verified ownership analysis would have the same result. Treat the unresolved population as a follow-up queue.
Before using this approach on a client project, agree the population, supplier identifiers, time window, amount definition and mapping version. Ask whether data contain commitments, invoices or payments; whether all signs and unknowns are visible; and whether the source covers the full decision population. Revalidate the extract and the workbook when any of these change.
Validation and source conditions
The production package contains calculation, source-preservation, mapping, chart-value and exported-artifact evidence. Its validation report states exactly which checks ran, any failures and repairs, and the scope of the isolated prompt replay. It does not claim a native Microsoft Excel test, native Word test or human learner usability test. Complete the supplied learner test log when running this lesson yourself.
Source: U.S. Department of the Treasury, Bureau of the Fiscal Service, USAspending.gov; contract actions reported through FPDS. Frozen export generated 23 October 2026; inspected 24 October 2026. Where these records contain D&B-supplied business-name data, that data is attributed to Dun & Bradstreet. Retain this attribution and the applicable USAspending D&B notice. This bounded analytical teaching extract is not an unrestricted recipient directory; no publisher approval or general redistribution license is asserted.
