A B2B technology firm wants an initial country shortlist before commissioning local diligence. Your task is to compare a defined set of countries with consistent indicators, expose the scoring choices and identify rankings that change when those choices change. The output helps decide where to investigate further. It does not establish product demand, obtainable market size or an investment recommendation.
You will use ChatGPT to inspect and calculate from the supplied files, then inspect the live formulas in Excel. No programming is required. The lesson works in a fresh conversation; earlier lessons are helpful concepts rather than required chat history.
What you will learn and produce
- Align countries, indicator definitions, units and years before comparing performance.
- Convert different measures to a common scale while preserving the source values and unknowns.
- Keep weights, the inflation target, tie handling and shortlist rules visible as teaching choices.
- Test ranking stability, explain panel dependence and prepare a sourced shortlist memo.
Your outputs are a source/coverage profile, a ten-country comparison table, a live scoring workbook, ranking and sensitivity exhibits, two practice comparisons and a short client note. Familiarity with Excel filters, percentages and ordinary formulas is sufficient. The blueprint estimates 55 minutes; this has not been measured in a learner session. Allow extra time for reading and checking.
Prepare your files and conversation
- Extract L14_Complete_Publication_Package.zip. For self-study, use the separate L14_Learner_Practice_Files.zip: its stage01 and stage02 folders contain the inputs without instructor answers. Read the source/reuse notice and retain attribution when using or adapting the data.
- Keep an untouched copy of the source CSV and JSON. Do not open and re-save the CSV in Excel before uploading; automatic formatting can change strings, dates or bytes. The full source has only 500 rows, but the lesson scores only the common 2024 year.
- Start a fresh ChatGPT conversation with file-analysis capability. Record the actual model label, regular or Temporary mode and your Excel version/platform in L14_Usability_Test_Log.txt. A subscription name is not the model identifier. Interface labels and capabilities can change.
- Attach all four Stage 1 files and wait until they appear before sending Prompt 1. Add the five Stage 2 files only when requested. Copy the exact prompts from L14_Exact_Prompts.txt, save each returned artifact, and resolve identity or coverage failures before proceeding. Ask for executed checks, not only a proposed method.
| Stage | Files to attach | Result |
|---|---|---|
| 1 | Frozen CSV; parent JSON; method-card PDF; input manifest | Identity and coverage profile |
| 2 | Source-row ledger; country lookup; indicator lookup; scoring policy; assumption register | Preserved source and 2024 inputs |
| 3 | No new inputs | Normalization and baseline workbook |
| 4 | No new inputs | Weight sensitivity and practices |
| 5 | Reference workbook; baseline ranking; all-case scores/ranks; practice transitions; validation report | Independent comparison and client note |
In the complete ZIP, source/ contains data, metadata, lookups and the manifest; policies/ contains the declared rules and assumption register; results/ contains reference calculations; output/ contains the lesson, workbook and exhibits. The standalone dictionary is a reference for you; the method card and policy provide the required staged definitions.
Understand what the score means
The observed panel is Brazil, Chile, Colombia, Mexico, Peru, Canada, United States, United Kingdom, Germany and France. It represents selected countries in the Americas and Western Europe, not a global opportunity universe. Five national indicators are retained for 2015–2024: population, GDP per capita, real GDP growth, Internet use and CPI inflation. Only 2024 enters the core score.
Population is a count of people, not businesses buying your product. GDP per capita uses current US dollars, so it is not a purchasing-power-parity measure. Internet use concerns individuals, not enterprise technology readiness. Growth and inflation are in percentage units: the hypothetical 2% inflation target is entered as the number 2, not 0.02. Negative growth remains an observation.
Normalization places measures on a 0–1 scale relative to this panel. A score of 1 means highest under the chosen direction among these countries, not universally best. All five baseline weights are 20%. Those weights, favorable directions, inflation target and shortlist cutoff are teaching judgments. Correlated indicators can count similar advantages more than once even when the arithmetic weights are equal.
Starting and follow up prompts
Prompt 1 Verify The Frozen Source And Coverage
Attach: wdi_ten_countries_2015_2024.csv; wdi_2015_2024.json; L14_Data_Dictionary_and_Method_Card.pdf; L14_Input_Manifest.json.
Act as a data-analysis partner for a management consultant who uses Excel but does not program. Execute checks on the attached files. Verify the CSV has 500 rows, 8 fields, 41,442 bytes and SHA256 6ef8dad1fa6dd309ff0fabc6bf59a66582dc5a15145fe2728571af651fc7692d. Verify the parent JSON has 500 observations, 109,353 bytes and SHA256 7b07e2a7564b7060ab5b6b8bab9d056f19d462beaa7d552f147fe76248b9a639. Stop on any mismatch; do not fetch newer data, silently replace a file, change the sample or round values to match controls.
Read the eight CSV fields as original strings. Preserve country_iso3, country, indicator_code, indicator_name, year, value, obs_status and decimal. Verify ten countries BRA,CHL,COL,MEX,PER,CAN,USA,GBR,DEU,FRA, the five specified indicator codes and every year 2015–2024. Check unique country × indicator × year keys and JSON pagination. Separate observed years from the parent lastupdated metadata. Audit known, zero, negative and unavailable numeric values; negative growth or inflation is not an error. Compare CSV numeric values with exact parent numeric lexemes and retain spelling differences without asserting they are value differences. Do not use decimal metadata to truncate source precision. Save an identity/coverage profile. Stop before calculating scores or ranking countries.
Prompt 2 Join Metadata And Prepare The Common Year
Attach now: L14_Source_Row_Ledger.csv; L14_Country_Lookup.csv; L14_Indicator_Lookup.csv; L14_Scope_Scoring_and_Practice_Policy.json; L14_Assumption_Register.csv.
Check these files against the input manifest. Verify each of the 500 original rows and its eight strings against the source ledger. Check the hash-bound source_record_id, parent observation ordinal, JSON pointer, exact object byte offsets and hash. This is source lineage, not an operational country record identifier. Preserve both the original CSV value string and the parent numeric lexeme.
Check lookup key types, cardinality and country/indicator names before many-to-one left joins. Keep original names separate from current metadata names, and retain any metadata aliases or discrepancies visibly. Verify there are ten nonaggregate country keys and five indicator keys. Do not add regional or income-group aggregates. Preserve the raw blank unit field and use the sourced unit definitions separately: persons; current USD per person; annual growth percent; Internet-use percent; annual CPI inflation percent. The hypothetical inflation target is 2 in percentage units, not 0.02.
Retain all 500 rows in an analytical ledger and prepare exactly 50 cells for the common 2024 comparison. Produce a ten-country × five-indicator numerical input table with source IDs, explicit availability states and a trace to the preserved strings. Do not mix years or use later metadata coverage to extend the frozen data. Confirm the policy and assumptions were declared before scoring. Explain what is observed, calculated and a teaching choice.
Prompt 3 Normalize And Build The Baseline Workbook
Use the frozen policy unchanged. For population, GDP per capita, real GDP growth and Internet use, calculate (value−panel minimum)/(panel maximum−panel minimum), treating higher as better only for this fictional screen. For inflation calculate distance = abs(CPI inflation percent−2), then score = (maximum distance−distance)/(maximum distance−minimum distance). Use 0.5 only for a known zero range and disclose that the criterion provides no discrimination. An unknown range is not a zero range.
Calculate the five-criterion weighted score with 20% weights each. Retain full calculation precision; round only the final score to 12 decimals for the declared rank key, using Excel-compatible ROUND. Use descending competition ranks so ties share a rank, with ISO3 only as display order within a tie. Displayed three-decimal equality does not establish a rank tie. Apply the declared shortlist rule of rank at most 3, including boundary ties. Label weights, directions, target and shortlist rule as teaching judgments, not World Bank recommendations or verified product-market demand.
Create a simple Excel workbook with centralized assumptions, live normalization/score/rank formulas and a clear prepared-data boundary. Preserve all original source strings, and make the 50 separately parsed 2024 numerical inputs identifiable. If these are prepared inputs rather than formulas linked to raw text, say so prominently; editing raw history must not be presented as an automatic refresh. Require complete valid inputs for each declared active panel, known weights within 0–1 that sum to one, and a known numeric target. Unknown inputs must block the affected score/rank and show a reason, never become a healthy zero. Do not silently drop countries or reweight around missing values. Export source trails, min/max values, normalized cells, weighted scores and ranks.
Prompt 4 Test Weights And The Two Practices
Calculate ten weight cases: for each criterion set its weight to 10% and then 30%, scaling the other four baseline weights proportionately so the case totals 100%. Include the baseline when reporting each country's best/worst rank and number of top-three appearances across 11 declared cases. These counts describe sensitivity cases, not probabilities. Create ranking and rank-sensitivity PNG exhibits with underlying unrounded values and explicit population/year/assumptions.
Practice A removes the population criterion and assigns the other four 25% each across the same ten countries. Practice B separately returns to the original five-criterion baseline, removes the predeclared USA country, retains 20% weights and recomputes all min/max values across the remaining nine countries. Do not apply Practice B sequentially to the four-criterion Practice A. Preserve all original history and keep USA visible as excluded only from Practice B. Report baseline rank, baseline relative rank among the same nine survivors, new practice rank and score; separate rank-slot compression from genuine reordering after rescaling. Do not choose a different removed country after seeing results.
Test the live workbook on disposable copies: a valid weight change with the sum preserved, an invalid weight sum, a changed inflation target, a blank and a text numeric input, a genuine numeric zero, a negative growth value, a complete equal-valued criterion giving the zero-range fallback, and a tie case. Deliberate test values are fixtures, not substitute public observations. Missing population must still block the five-criterion baseline but need not block Practice A, which explicitly removes that criterion. Restore all inputs and confirm the baseline again. Report the exact calculation engine and checks actually run; do not claim native Excel testing unless performed.
Prompt 5 Independently Verify And Write The Shortlist Note
Save your own outputs first. Attach now: L14_Country_Screening_Workbook.xlsx; L14_Baseline_Ranking.csv; L14_All_Case_Scores_and_Ranks.csv; L14_Practice_Transitions.csv; L14_Validation_Report.txt.
Compare source identities, 2024 coverage, units, policy version, criterion directions, target, weights and tie rule before comparing answers. Recompute directly from the frozen raw values rather than using workbook totals as their own expected inputs. Compare all 50 baseline input cells, every min/max and normalization, all 129 case-country scores/ranks, both practice transitions and every plotted value. Report actual differences, tolerances and any explicit alternative assumptions without silently tuning them to match the reference.
Write a concise shortlist memo for the fictional B2B technology firm. Distinguish the baseline screen, rankings stable across declared weights, and borderline countries needing more evidence. Explain panel dependence, overlapping indicators, national-average limitations and why growth, Internet use and GDP per capita do not establish product-specific opportunity. Identify product/customer, regulatory, competitive, channel, delivery-cost and local research needed before market-entry decisions. Preserve World Bank and provider attribution, the WDI-channel reuse basis and the no-endorsement notice.
Document source/calculation checks, exported-artifact inspection, isolated numerical replay, native Excel and human usability separately. Record failures, repairs and affected retests. Do not invent findings, approvals, deterministic model behavior or a measured lesson duration. Keep the baseline, source inputs and your original run for review.
Verify the output in Excel
Save your original analysis before opening the reference workbook. The reference uses a prepared-data boundary: the 500 raw records remain as original strings, and a separate block contains 50 parsed 2024 numerical inputs that feed live formulas. Parsing, identity and metadata checks occur in the supplied preparation evidence. Editing a raw history cell does not automatically regenerate the prepared input block. Treat a refresh as a new documented preparation step.
- Read the Source and Assumptions sheets before interpreting Scores. Verify 500 preserved observations, ten country keys, five criteria and the 50 common-year inputs. Use the source IDs and original strings to check three inputs directly.
- Recompute at least one favorable normalization and one inflation-distance normalization outside the model. Check the complete panel minimum and maximum, not just two convenient countries. Preserve negative growth and do not round an intermediate result.
- Inspect the live weighted-score formula, its 12-decimal ranking key and the competition-rank formula. A true tie shares a rank. Three-decimal display equality alone does not imply identical rank keys.
- Test a valid weight change while keeping the total at one, an invalid total, a target change, a missing numerical input and a genuine zero. On disposable copies, test a known zero-range criterion and tied rows, then restore. Follow L14_Excel_Verification_Instructions.txt for exact cells and expected behavior.
- Compare the baseline and ten sensitivity cases, then each practice separately. For the country-removal practice, use the same-survivor comparator to distinguish rank compression from reordering caused by new min/max values. Record the application in which each check actually ran.
Practice and client interpretation
Practice A removes population and gives the remaining four criteria 25% each, retaining all ten countries. Practice B independently removes the predeclared United States from the original five-criterion, equal-weight panel. Recompute the nine-country ranges and retain USA as an excluded row in the practice evidence. Do not combine the two changes unless you deliberately create and label an additional scenario.
Submit both definitions, score/rank tables and actual transitions. Explain why a country can improve its numerical score or rank without any change in its source values. Compare your answer with the instructor notes after saving it. Do not tune the removed country or weights to produce a preferred shortlist.
The client memo should distinguish the baseline shortlist, stable positions across the declared cases and borderline alternatives. Ask for product-specific customer segments, buying behavior, regulation, competition, channels, acquisition/delivery costs and local evidence before an entry decision. A precise score is an arithmetic result, not a measure of forecast certainty.
Use this method as a project reference
Agree the decision, candidate-country universe, indicators, year, missingness policy and assumption owners before ranking. Keep a versioned snapshot and evidence trail. Test alternative weights and panel composition, and report material changes instead of presenting one rank order as definitive. Do not silently impute values, mix years or relabel a changed method as the same baseline.
The release distinguishes executed source/calculation checks, formula-runtime and exported-file tests, rendered-artifact review and the isolated numerical prompt replay. No native Microsoft Excel or human fresh-chat usability session is claimed. The blank test log enables those checks. No World Bank, provider or client approval is implied, and pending work in other lessons is not completed here.
Sources and reference files
Data: World Bank, World Development Indicators, source 2; the frozen parent API response and CSV retain 500 observations for 2015–2024. Snapshot last updated 2026-10-08. Exact API request, file identities, provider attribution and preparation changes are in L14_Source_and_Reuse_Notice.txt and the source register.
Definitions: https://databank.worldbank.org/metadataglossary/world-development-indicators and the five exact indicator metadata files supplied with the package. The frozen source names remain separate from current metadata wording.
Reuse terms: https://www.worldbank.org/ext/en/legal/terms-conditions/datasets. All five selected WDI series explicitly state CC BY 4.0. The World Bank ITU distribution also states CC BY 4.0: https://datacatalog.worldbank.org/search/dataset/0067055/itu-data-hub. This does not relicense separate direct-provider databases or imply provider endorsement.
Calculation evidence: results/L14_Normalized_Cells.csv, L14_All_Case_Scores_and_Ranks.csv, L14_Rank_Stability.csv and L14_Practice_Transitions.csv. Independent checks, executed workbook tests, rendered-artifact inspection and isolated prompt replay are documented separately in L14_Validation_Report.txt. Release date October 24, 2026 New York; execution receipts retain October 25 UTC timestamps.
