An airline strategy team needs to decide which service-reliability differences deserve investigation. You will build an Excel scorecard that keeps cancellations, diversions and arrival outcomes separate, shows the volume behind each rate and tests how comparisons change when the airport and route population changes.
What you will learn
- Define a KPI by its numerator, denominator, population and period before ranking groups.
- Preserve every scheduled record while making unknown and contradictory outcomes visible.
- Calculate weighted carrier and airport rates from counts rather than averaging percentages.
- Compare raw results with a declared common-route restriction and report what the restriction leaves out.
- Use Excel and source-record checks to turn observed differences into specific investigation questions.
You need basic proportions and grouped summaries, as introduced in Lesson 4. You do not need an earlier conversation, programming experience or a local Python installation. The scenario is teaching context; the flight observations are actual public BTS data. The blueprint allows 50 minutes, but that is an unvalidated planning estimate rather than a measured completion time.
What you will produce
Your outputs are a reconciled record-evidence CSV, carrier and airport scorecards, common-route and practice tables, a simple Excel workbook, a benchmark heatmap and a memo with three evidence-linked investigation priorities. Keep a checkpoint of your own calculations before opening the supplied reference workbook and instructor answers.
The source and the scope
The population is all 32,347 January 2024 records whose Origin is JFK, LGA or EWR in the 547,271-row BTS reporting-carrier release. Each row is one reported scheduled nonstop domestic flight record. These are departures from three origin airports; they are not all flights serving New York. The arrival outcome refers to arrival at each flight's destination.
Two 24-field extracts are supplied. The original frozen file was recovered exactly, including its registered checksum. The working source_strings_v1 file contains the same records and values in the same order, while preserving the parent's original decimal strings, clock leading zeros and blanks. Use only the working file as flight data. The frozen copy is provenance, not a second set of flights.
Reporting_Airline is the reporting-carrier key. The reporting table covers flights those carriers operate and report; a marketing-network comparison would require a different verified mapping or source. Do not combine regional reporting carriers into another carrier's network by guessing. File-hash plus source-row identifiers are lineage, not operational flight IDs.
The three KPI questions
| KPI | Numerator | Denominator |
|---|---|---|
| Cancellation rate | Cancelled = 1 | All reported scheduled records |
| Diversion rate | Diverted = 1 | All reported scheduled records |
| On-time arrival share | Eligible arrival delay below 15 minutes | Eligible noncancelled and nondiverted records with known, nonconflicting arrival outcome |
Finite ArrDelay is the primary arrival measure. A valid ArrDel15 cross-check must agree that 15 minutes or more is late. When no finite delay is available, a valid ArrDel15 can supply the threshold outcome, explicitly labeled as a fallback. Contradictory arrival indicators leave the arrival denominator; the scheduled record remains. Missing or invalid status flags never become zero.
The reference policy ranks carrier rates only above declared volume minima: 1,000 scheduled records, and also 1,000 eligible arrivals for on-time ranking. Smaller groups stay in the evidence. These minima are teaching conventions, not airline targets or statistical guarantees. Do not compute flight duration by subtracting local departure and arrival clocks across time zones.
Prepare a fresh conversation
Extract L05_Learner_Practice_Files.zip. Open this lesson locally. Start a fresh conversation and attach the seven files below from its inputs folder; keep the reference answer files out of that conversation until Prompt 7. If uploading in batches, complete the attachments before sending Prompt 1.
- bts_nyc_origin_jan2024_source_strings_v1.csv
- bts_nyc_origin_jan2024.csv
- L05_Reporting_Carrier_Lookup.csv
- L05_Source_Row_Lineage.csv
- L05_KPI_and_Eligibility_Policy_v1.json
- L05_Source_Verification.json
- L05_Data_Dictionary_and_Method_Card.pdf
The exact prompts also appear in L05_Copyable_Prompts.txt and individual files under learner/prompts. Copy one at a time. Inspect the requested checkpoint and download its outputs before proceeding. If the assistant reports a source mismatch, stop and resolve the file identity; do not ask it to force the registered totals.
Run the staged prompts
Prompt 1 inspect the files and freeze the policy
Act as an airline strategy analyst. I am learning to build an operational performance scorecard. Use only the attached January 2024 BTS materials. The population is every reported scheduled record whose Origin is JFK, LGA or EWR, not all flights serving New York.
Attached: bts_nyc_origin_jan2024_source_strings_v1.csv; bts_nyc_origin_jan2024.csv; L05_Reporting_Carrier_Lookup.csv; L05_Source_Row_Lineage.csv; L05_KPI_and_Eligibility_Policy_v1.json; L05_Source_Verification.json; L05_Data_Dictionary_and_Method_Card.pdf.
Use the source_strings_v1 file as the sole flight-record input. The other flight CSV is the identical-population frozen provenance copy; never append the two. Verify both files' actual hashes, sizes, row counts and 24-field schema against L05_Source_Verification.json. The working SHA256 is 381d8d5a5c71f2357ec523fe5eacc4b14bfb5792f82221a0771bd00caca0f8e9 and has 3,489,000 bytes. The original frozen SHA256 is 7b0f3185b3d7f7faf8f0c21af7b57b6b72a0cc492e457d350f42cc9cab0c6adc and has 3,189,849 bytes. Both must have 32,347 records in the same order.
Read source values as strings. Preserve blanks, original precision, leading zeros and every record. Check the date/origin scope, one-to-one source lineage, numeric/text equivalence of the two extracts and a many-to-one reporting-carrier lookup. Keep unmatched keys visible; stop for duplicate lookup keys. The carrier fields describe reporting carriers, not a marketing network. Source-row identifiers are file lineage, not operational flight IDs.
Read and retain the supplied policy unchanged. Explain the three KPI denominators, arrival precedence/conflicts, unknown handling, ranking minima, common-route selection and heatmap benchmark. Save L05_Input_Inspection.json and an unchanged L05_Frozen_Policy.json. Report any mismatch and stop before analysis; do not substitute new data or thresholds. Do not rank carriers yet.
Prompt 2 build reconciled carrier and airport scorecards
Use the verified working CSV and frozen policy. Preserve all 32,347 source records and original strings. Add separately named lineage, eligibility, disposition and numerator/denominator columns to L05_Record_Evidence.csv.
Cancellation numerator is Cancelled=1 and diversion numerator is Diverted=1; both denominators are all scheduled records. Only Cancelled=0 and Diverted=0 records can enter the on-time arrival denominator. Use finite ArrDelay as primary; on time means strictly below 15 minutes. Cross-check valid ArrDel15. Exclude a contradiction from arrival eligibility; if no finite ArrDelay exists, use a valid ArrDel15 as a labeled threshold fallback. Keep unknown statuses/outcomes visible. A record with both status flags=1 remains in both affirmative-flag counts, once in scheduled count and separately in the conflict count. Do not subtract local clocks or remove unusual finite delays.
Produce FULL carrier, origin-airport, carrier-by-origin and carrier-by-Origin-Dest tables. Show all counts, every KPI numerator and denominator, eligibility coverage, unknowns and conflicts. Calculate aggregate rates by summing numerators and denominators. Retain smaller and absent groups clearly. Apply the supplied 1,000 scheduled-record minimum and, for on-time ranking, also 1,000 eligible arrivals. Use shared competition ranks for ties. Show cancellation/diversion ranks only where their corresponding status flags have no unknowns.
Export L05_Carrier_Scorecard.csv, L05_Airport_Scorecard.csv, L05_Carrier_Airport_Counts.csv, L05_Carrier_Route_Counts.csv, L05_Scope_Summary.csv and L05_Disposition_Reconciliation.csv. Reconcile every count to source rows and every mutually exclusive disposition to the scheduled total. Save and explain the checks before continuing.
Prompt 3 compare the same reporting carriers on common routes
Apply the frozen common-route rule without using performance to choose comparators. Select the two reporting carriers with the most scheduled records in the full three-origin scope; break a volume tie by ascending carrier code. A route is the ordered Origin-Dest pair. Include a route only if each selected carrier has at least 30 scheduled records and 20 eligible arrivals on it.
Freeze that pair and route set. Retain all their scheduled records on the included routes, including cancellations and diversions. Export every candidate route with both carriers' scheduled/eligible counts and its inclusion reason in L05_Common_Route_Membership.csv. Export L05_Common_Route_Comparison.csv with full raw and restricted counts/rates, excluded counts, scheduled and eligible coverage, retained and original route counts, and route coverage.
Show each carrier's raw full-scorecard rank alongside the restricted numerical comparison. Distinguish a within-pair rank from a rank among all qualifying carriers. Keep the global minimum-volume rule unchanged: show restricted rates but no formal rank below that rule. If a subset has no records, show unavailable rather than zero performance.
Explain that common-route restriction still uses each carrier's actual route mix. It is not route-mix standardization and does not identify a causal carrier effect. Do not attribute differences to weather, scheduling or airline decisions without additional evidence.
Prompt 4 complete the JFK and LGA practice
Now independently recalculate the practice from the original working source rows for FULL, JFK-only and LGA-only. Use exactly the same KPI definitions, ranking thresholds, reporting-carrier lookup, frozen comparison pair and common-route set. Do not switch carriers or routes to improve an airport subset's coverage.
For every scope, show scheduled/eligible counts, cancellations/diversions/on-time counts and rates, carrier ranks with minimum-volume labels, carriers present and routes present. Include the share of full scheduled records, eligible arrivals, carriers and routes retained. Keep all full-scope carriers visible; an absent carrier has zero records and unavailable performance. Add scope fields to the scorecards and export L05_Practice_Coverage.csv.
For the frozen pair, show raw versus restricted results and coverage in each scope. Explicitly identify empty comparisons. Write a short explanation of which conclusions change and which comparisons become unsupported when the origin scope changes. Do not equate a change in observed ranking with a change in underlying carrier quality.
Prompt 5 check the calculations through a separate route
Review the calculations through a second implementation using the original source rows and frozen policy as inputs. Label this assistant-side calculation review, not a human audit. Do not use the completed scorecards or workbook totals as inputs to their own check; use them only as comparison targets.
Independently recompute all scope, carrier, airport and route counts; each KPI numerator/denominator; weighted aggregation; lookup cardinality; common-route membership, excluded rows and coverage; and the full/JFK/LGA practice. Check all original strings and lineage fields in the evidence CSV.
Select ten actual records by ascending SHA256 of UTF-8 text L05-check-v1| followed by the WORKING input SHA256, then | and the one-based source data-row number. Add the first available cancellation, diversion, completed-arrival unknown and completed-arrival conflict records, plus the records with minimum and maximum finite ArrDelay. Deduplicate by source row. Export L05_Selected_Record_Checks.csv with selection reasons and source strings.
For every carrier-origin heatmap cell, independently calculate eligible volume, on-time share, the same-origin pooled benchmark and the percentage-point difference. Mute color below 200 scheduled or 200 eligible records; display no-record cells as unavailable. Export L05_Heatmap_Values.csv.
Record mismatches, repairs and affected retests. Save L05_Assistant_Review.txt and supporting checks. Stop before publishing if a count or membership mismatch remains.
Prompt 6 create the workbook exhibits and investigation memo
Using the checked results, produce a simple Excel scorecard with Scorecard, Common routes, Practice, KPI definitions and Inputs sheets. Keep full record evidence in the accompanying CSV. Label imported prepared counts and calculate rates, weighted totals, ranking eligibility and ranks with readable formulas. Show numerator/denominator fields, volume, unknowns, reporting-carrier labels and unavailable values. Explain that changing policy or source data requires regenerating the prepared counts.
Create a benchmark heatmap and underlying values. Its benchmark is the observed same-origin weighted on-time share, not an airline target. Include raw-versus-common-route and practice exhibits with counts and coverage. Keep smaller groups visible with the frozen display/ranking rules.
Write an investigation memo with exactly three priorities, each tied to a specific result table and workbook cell, a proposed next evidence request and a limitation. Use descriptive language; do not invent causal explanations, approved targets or airline/human approval.
Export L05_Operational_Scorecard.xlsx, L05_Client_Exhibits.pdf, PNG charts with underlying CSVs and L05_Investigation_Memo.docx. Recalculate and inspect the saved workbook, render the documents/charts and report actual checks. Distinguish artifact checks from native Microsoft Excel and human usability tests. If an export cannot be produced, report the error; do not invent a download link.
Prompt 7 compare with the reference answer and verify in Excel
I have completed and saved my independent analysis. I am now attaching the supplied reference workbook, instructor notes, Excel verification instructions and validation report.
Compare my saved counts, eligibility, ranks, route membership, coverage, heatmap values and practice answers with those references. Investigate differences at source-record level rather than forcing totals to match. Preserve my earlier checkpoint and record any repair.
Guide me through the specified Excel cells and source-record checks, including recomputing rates from their numerators and denominators, a weighted aggregate, zero-denominator handling and the small common-route population. Ask me to report what I actually observed in Excel. Do not claim that I opened Excel, confirmed a value or completed a timed session unless I tell you. Record any actual test in the blank usability log. Finish with what evidence I would need before adapting this scorecard to a client project.
Read the scorecard as evidence
Read counts before percentages. A cancellation rate answers whether a scheduled record was cancelled. On-time share answers whether an eligible completed arrival was within the threshold. A carrier can have a low cancellation rate and a weaker on-time share, so a single composite score would conceal a useful distinction. There is no composite score or invented service target in this lesson.
For an aggregate rate, add the relevant numerators and denominators, then divide. For example, the pooled on-time result across origins is total on-time arrivals divided by total eligible arrivals. An average of the three origin percentages gives each airport equal weight even when their eligible volumes differ.
Common-route restriction removes routes outside the intersection, but each carrier can still fly a different proportion of the retained routes. The restriction is a sensitivity analysis, not route-mix standardization. Report the retained records and routes beside the rate. A very small restricted population may be more comparable in one dimension and less representative of the carrier's full activity.
Verify your work in three layers
- Source layer: recheck the exact inputs, record count, field strings, scope, source lineage and lookup cardinality. Inspect the selected actual records, including cancellations, diversions and unusual delays.
- Calculation layer: reconcile dispositions, every numerator and denominator, pooled rates, common-route membership and the independently recomputed practice. Do not let a workbook total validate itself.
- Artifact layer: use L05_Excel_Verification_Instructions.txt to inspect formulas and saved results, and compare charts with their underlying CSVs. Record what you actually test in native Excel.
Explain the JFK and LGA practice
Keep the same rules and compare the full scope with JFK-only and LGA-only. Report scheduled and eligible coverage, carrier and route coverage, and changes in which carriers meet the ranking minimum. Retain the frozen comparison pair and route set. If an airport has no qualifying common-route comparison, that absence is an answer rather than a reason to select a new pair.
After saving your checkpoint, compare with the instructor notes. A useful explanation distinguishes an observed percentage change, a change in which records are included and an unsupported claim about the cause. The reference answers include exact counts and the expected empty comparison.
Adapt the method to a client project
- Agree on the record grain, source version, reporting entity and time window. Preserve a reproducible raw copy.
- Write the KPI policy before ranking. Specify unknown handling, minimum volumes and the role of each reference lookup.
- Choose comparable operating segments using business context and declared rules. Report excluded volume and avoid changing the comparison after seeing performance.
- Request weather, schedule, route, equipment and disruption information before proposing a causal explanation or an operational intervention.
- Define ownership and refresh controls for the prepared counts. Revalidate a changed source or policy; the workbook is not an automatic connection to BTS.
Troubleshooting
Wrong row count: check that both extracts were not appended, no outcomes were filtered and no records were deduplicated. Wrong cancellation rate: restore the scheduled denominator. A missing carrier name: keep the code and inspect the lookup rather than dropping records. A zero denominator: show unavailable. A surprising rank: inspect the minimum-volume rule and the selected population before changing it. A mismatched file hash: obtain the supplied frozen file again or document a separately inspected version; never silently replace it.
Reference and version note
This recreated edition follows the original Lesson 5 blueprint and inspected Lessons 1-4 and accepted Lesson 6 workflow patterns. It contains no next-lesson prompt. Production source and calculation checks are documented separately from prompt replay, exported-artifact inspection, native Excel and human usability. Read L05_Validation_Report.txt for actual completed tests and remaining limits; the blank usability log is not a completed test.
Source references
Bureau of Transportation Statistics, Reporting Carrier On-Time Performance, January 2024. The original monthly archive and exact reconstruction receipts are supplied in source_evidence and provenance.
Field definitions: https://www.transtats.bts.gov/Fields.asp?gnoyr_VQ=FGJ
Reporting and marketing table definitions: https://www.transtats.bts.gov/Tables.asp?QO_VQ=EFD
See L05_Source_and_Reuse_Notice.txt for retrieval, reference lookup, attribution and reuse evidence. Analytical rules are L05-KPI-1.0.1 teaching conventions unless identified as publisher definitions.
