A client is comparing Home Depot, Lowe's and Floor & Decor before deciding where to focus operational diligence. The first request sounds simple: put revenue, margins and turnover ratios side by side. The difficult work is deciding which facts represent the same period, whether the units agree, which denominators are actually available, and how much the resulting comparison can establish.
You will build an Excel comparable-company workbook, a common-size chart and a two-page benchmarking note using real SEC filings. You will retain the original source trail and explain limitations in terms a client can use. Lesson 6 is already complete and is intentionally skipped; this package produces only Lesson 7.
What you will learn
- Extract and verify annual financial facts using company, concept, unit, fiscal dates and exact filing accession.
- Separate income-statement durations from balance-sheet instants and prevent duplicate or cumulative-quarter observations.
- Calculate margins and turnover with explicit denominator availability, then test an alternative definition.
- Explain fiscal-calendar, acquisition, accounting-policy and business-model differences without overstating what a ratio proves.
You need basic income-statement and balance-sheet literacy and comfort with Excel formulas. Lesson 2's preservation and reconciliation habits are useful prerequisites, but this package is self-contained. You do not need to write or run code. The original blueprint allowed 65 minutes; this is an unvalidated planning estimate, not a measured learner time. Record actual upload, analysis, reading and Excel-check time in the supplied test log.
The three layers of evidence
A source observation is an amount, date, context or disclosure reported through a filing. A calculated result is a ratio or comparison built from those observations. A teaching convention is a choice made for this lesson, such as retaining original-accession facts, using a two-point average or making no 53-week normalization. Keep these layers visible in your output.
One frozen source row represents a company / fiscal-label / concept fact. It is not an entire financial statement. The table contains 102 facts, including useful reference concepts that do not feed the core ratios. The nine financial rows you create will be a derived view; they must not replace or discard the complete source evidence.
Set up a fresh conversation
Extract the ZIP into a folder before opening the lesson. Read this guide and keep the instructor and reference-answer folder closed until you have produced your own results. Start a new ChatGPT conversation that supports file upload, calculation and downloadable files. Capabilities depend on your account and configuration; a particular model name is not required. If the conversation cannot actually read and calculate on the files, use a configuration that can rather than accepting guessed findings.
Upload these six files from 01_start. If an upload limit requires smaller batches, attach them in batches and wait until all six are available before sending Prompt 1. Do not upload the reference workbook or answers.
| Starting file | Purpose |
|---|---|
| sec_selected_annual_facts_2022_2024.csv | Exact frozen 102-row, 13-field selection. |
| L07_Fact_Provenance.csv | Original fields plus verified filing/context and hash-bound fact locators. |
| L07_Concept_Mapping.csv | Company-specific metric mapping. |
| L07_Metric_and_Comparability_Policy_v1.json | Versioned calculation and selection rules. |
| L07_Data_Dictionary_and_Method_Card.pdf | Standalone definitions, method and cautions. |
| L07_Source_and_Reuse_Notice.txt | Exact identity, inclusion/exclusion and retrieval/reuse limits. |
The frozen CSV and all three Companyfacts JSONs match the blueprint's recorded hashes. The selected CSV was recovered by reconstruction from the verified JSONs, producing identical bytes. The nine current downloads at the recorded accession URLs differ from the original HTML hashes. They are explicitly versioned verification copies; all 102 selected facts were matched, but whole-document equivalence is not claimed.
Full corporate reports are not republished in this ZIP because blanket redistribution terms were not established. Exact official links and a tested retrieval route are included. The guided analysis works with packaged factual extracts. You can open the official filing links yourself for visual source checks; do not assume a calculation environment can download web pages.
Stage 1 Inspect the facts and policy
Copy the complete prompt below or Prompt 1 from L07_Copyable_Prompts.txt.
Act as a financial-analysis assistant to a management consultant who uses Excel and does not program. This is Lesson 7, Extract financial information and benchmark companies. Analyze only Home Depot, Lowe's and Floor & Decor, fiscal labels 2022-2024, under the uploaded L07-METRIC-v1.0 policy. Do not calculate the benchmark yet.
Actually open all six uploaded starting files: sec_selected_annual_facts_2022_2024.csv; L07_Fact_Provenance.csv; L07_Concept_Mapping.csv; L07_Metric_and_Comparability_Policy_v1.json; L07_Data_Dictionary_and_Method_Card.pdf; L07_Source_and_Reuse_Notice.txt. If you cannot read or execute calculations on a file, say so instead of guessing.
Compute the frozen CSV SHA256 and compare it with da69812fb07e92f887a17c320c54b926068c13249b18ad677b1a47280d2685c7. Verify 102 rows, 13 original columns, three companies and nine company-year combinations. Preserve all original field strings, CIK zeros, signed values, units, dates, accessions and source precision. Check unique company/fiscal-year/concept keys, the complete provenance join, and whether all 13 source fields are unchanged in order. Distinguish duration from instant facts and report missing/unknown values without replacing them with zero.
Explain the accession-specific selection, fiscal-year versus calendar-year distinction, 300-400-day selection rule versus inclusive week count, two-point average-balance requirements, and why an unavailable FY2022 ratio must not become zero. Identify the different cost-of-sales concepts. State what this first gate can establish from the files and what still requires the Companyfacts JSONs or direct filing review. Do not claim that fp or source_filing_url were original CSV columns. Do not claim original HTML byte recovery, human approval or a current filing review that you have not performed.
Return a concise source/policy gate, a list of discrepancies, and the exact four additional files needed for Stage 2. Stop before benchmarking.
Checkpoint 1
Confirm 102 rows, 13 original columns, three companies, nine company-years and unique company/year/concept keys. The provenance file must preserve every original cell and provide one fact locator per row. fp and filing URL are appended verified metadata, not columns that were present in the original CSV. Blank start values for balance-sheet instants are expected; do not fill them with invented fiscal starts.
Look at several source rows. A dollar amount can be very large because SEC expands a filing's millions or thousands into USD. That conversion does not increase the original precision. Preserve negative values such as Lowe's equity. A blank optional frame is not a zero and does not justify substituting a calendar year.
Stage 2 Extract, calculate and interpret
Add four files: HD_companyfacts.json, LOW_companyfacts.json and FND_companyfacts.json from 02_sources/source_archive, plus L07_Comparability_Register_v1.csv from 02_sources. You now have ten named files in the conversation. Verify each additional attachment is readable; use smaller upload batches if needed. If a limit prevents access, record it and resolve the file availability before proceeding. Do not ask the model to improvise the missing source.
The JSONs contain many periods and repeated facts because the same amount can appear in several filings or contexts. Prompt 2 requires a fresh selection check rather than treating every JSON observation as additive revenue.
The additional Stage 2 files are now uploaded: HD_companyfacts.json, LOW_companyfacts.json, FND_companyfacts.json, and L07_Comparability_Register_v1.csv. Use the prior starting files too. Execute the extraction and analysis; show results only after the checks run.
Independently rebuild each selected company/concept/fiscal-label fact from the JSONs: us-gaap, USD, form 10-K, fp FY, filed through 2025-12-31; for duration facts require end minus start between 300 and 400 days; instant facts have no start. Within each group choose the latest end, then latest filed. Stop on conflicting ties. Match all 102 frozen observations, including value, dates, accession, filed, form and optional frame. Do not substitute latest-restated comparatives, sum repeated or cumulative-quarter facts, or insert FY2021 balances. Preserve all source facts, even those not used in the core ratios.
Using the company-specific concept mapping, build nine company-year financial rows with exact USD revenue, cost of sales, derived and reported gross profit, operating income, net income, ending assets and inventory. Keep actual fiscal dates and 52/53-week counts. Trace every revenue and operating-income value to the exact official filing URL, accession and supplied inline context/element IDs. Cross-check the packaged factual evidence; if you cannot open a filing yourself, label that check as reliance on packaged inspected evidence, not a new full-filing review. Explain original-versus-current HTML hash status.
Calculate common-size income statements, gross margin=(revenue-cost of sales)/revenue, operating margin=operating income/revenue, asset turnover=revenue/average beginning and ending total assets, and inventory turnover=cost of sales/average beginning and ending inventory. Require both known balances and verify the previous closing date is the day before the current start. Leave unavailable results blank with explicit reasons. Never use AVERAGE on a range that silently ignores a missing beginning balance. Preserve scale and source precision; display USD millions without rounding the calculation inputs.
Produce a simple Excel workbook with live formulas, source fact references, blank/missing and duplicate guards, metric definitions, the nine financial rows, ratios and a terminal checks sheet. Supply the full provenance CSV, company-year and common-size CSVs, and all chart values. Make a clearly labeled FY2024 100% revenue composition PNG with cost of sales, calculated other operating costs=(gross profit-operating income), and operating income. Calculate every segment from unrounded inputs and distinguish the residual from a reported SG&A line.
Write a two-page benchmarking note with actual evidence, business-model/accounting/fiscal-period limitations, and three focused diligence questions. Use the comparability register with its exact sources; do not claim causation, a homogeneous industry, investment merit, normalization or company approval. Explain the finite later-comparative screen without claiming absence of all later restatements. Independently check arithmetic, denominator availability, complete source preservation, all chart values and workbook formula results. Inspect exported artifacts as your tools allow. Report calculation checks, artifact checks, native Excel tests and human usability tests separately; do not claim tests you did not perform. Provide downloadable files and a concise validation report.
Why the period and accession matter
The fiscal label identifies the company's fiscal reporting year. HD FY2024 ends on 2 February 2025, LOW FY2024 on 31 January 2025, and FND FY2024 on 26 December 2024. HD FY2024 and LOW FY2022 each have 53 weeks. Keep the actual dates beside the label. A matching label does not create a common calendar window.
The frozen rule chooses facts filed through 31 December 2025 using the original recorded accessions. A later annual filing may repeat an earlier period, but its fy metadata can describe the later filing year. Match actual start/end dates and accession; do not use the fiscal label alone to select a comparative. A different latest-restated series would require a separate, explicitly versioned policy and table.
Why both balances are required
Revenue and cost of sales accumulate over a duration. Assets and inventory are measured at a point in time. A two-point average is (beginning + ending) / 2, using the same company's adjacent fiscal closes. It is a convention for relating a flow to a stock, not a time-weighted record of every day's balance.
Excel's AVERAGE can ignore blank cells. If the beginning balance is missing, it can return only the ending balance and still produce a plausible ratio. Require two numeric balances explicitly. This release adds no FY2021 balances, so all three FY2022 average-asset and average-inventory ratios remain unavailable with reasons. The ending-balance practice is a different, separately labeled definition.
Read the chart correctly
The FY2024 chart divides each company's revenue into cost of sales, other operating costs and operating income. Other operating costs is a calculated residual: gross profit minus operating income. It is not a reported SG&A amount. The unrounded stack totals 100%; rounded labels may differ slightly from 100%. Net income is shown in the common-size table, not added as another piece of the operating-income stack.
| Your output | Acceptance requirement |
|---|---|
| Sourced financial and common-size tables | Nine company-years, exact USD inputs, actual periods, all seven source metrics, derived/reported gross profit and source references. |
| Complete provenance CSV | All 102 frozen facts and all 13 original strings retained, with verified context metadata and visible original-versus-current HTML identity. |
| Formula Excel workbook | Live, guarded calculations; explicit reasons for missing denominators; complete source trail and terminal checks. |
| Common-size PNG and values | All three FY2024 companies, exact segment definitions, period/week labels and unrounded underlying values. |
| Two-page note | Evidence-backed findings, accounting/business-model cautions and three diligence questions; no unsupported causal or investment conclusion. |
Stage 3 Verify in Microsoft Excel
Use Prompt 3 to get cell references for the workbook ChatGPT actually created. The supplied reference file has the fixed layout described below; a learner-created layout may differ.
Help me verify this analysis in Microsoft Excel without programming. Map your actual workbook cells to revenue, cost of sales, gross profit, operating income, ending/beginning/average assets and inventory, and each ratio. Give exact formulas using your layout, with guards requiring both average-denominator balances. Explain how to import the frozen CSV as Text so CIK, accessions and source values are preserved, and how to make separate numeric helper columns for calculation.
Give a checklist to verify 102 unique source facts, nine company-years, original source-string preservation, reported-versus-derived gross profit, all 18 revenue/operating-income traces, missing FY2022 average ratios and the common-size chart total. Use several actual records, including the Home Depot FY2024 revenue and assets, a Floor & Decor fact reported in thousands and a negative Lowe's equity fact. Show how to check the FY2024 Home Depot average-assets ratio directly with its two balances and how to distinguish the ending-balance alternative. In a disposable copy, describe a missing-balance and duplicate-key test and what should happen; do not mark it completed for me. Ask me to record my Excel version, formulas, results and any repair prompts. Do not assert a native Excel pass based on your generated file or calculation engine.
Import the source without changing it
In Excel for Windows, use Data > From Text/CSV > Transform Data. Remove an automatic Changed Type step, then set the 13 original fields to Text before loading. Keep the source CSV unchanged. A direct double-click can lose leading CIK zeros or alter date interpretation. Create a numeric helper column separately when you need arithmetic; numeric-looking source text is intentionally preserved as evidence.
For an independent working table called Facts, check =ROWS(Facts[ticker]) and add a count helper using =COUNTIFS(Facts[ticker],[@ticker],Facts[fiscal_year],[@fiscal_year],Facts[concept],[@concept]). Every result must be 1. The supplied dictionary identifies all 37 provenance fields, including the original 13.
Reference checks after your own attempt
| Check | Executed reference result |
|---|---|
| Original source preservation | 102 facts; all 1,326 original cells preserved; 57 duration and 45 instant facts. |
| Core mapping | 63 fact bindings: seven sourced metrics for each of nine company-years. |
| Average-denominator availability | Six asset-turnover and six inventory-turnover values; three unavailable of each for FY2022. |
| Reported gross profit | Equals revenue minus cost of sales in all nine company-years. |
| HD FY2024 revenue / operating income | USD 159,514 million / USD 21,526 million, from the original selected accession. |
| FY2024 HD average assets and turnover | 86,324.5 USD million; 1.8478x. |
Trace all 18 revenue/operating-income values using the source table and exact filing links. Read the consolidated statement heading and units, then compare the annual column and amounts. Use L07_Revenue_Operating_Income_Trace.csv after your own extraction to locate the evidence. A filing value can occur more than once in inline XBRL without representing multiple observations. Record which links and values you actually checked; do not copy a production pass into your personal test log.
Checkpoint 2
Record your Excel version, source controls, direct ratio checks, missing-balance behavior and all three chart stacks. In a disposable workbook, remove a beginning balance and confirm the related average and ratio become unavailable. Duplicate a fact key and confirm the affected lookup refuses to silently sum or select one. Restore the input or discard the copy. These are instructions for your test, not claims that native Excel tests were performed during automated production.
Practice Change the denominator
The client asks whether using ending assets changes the comparison. Predict the direction first: when assets rise, ending assets exceed the simple beginning/end average, so the ending-based ratio will normally be lower for the same positive revenue. A different pattern of growth, disposal or acquisition timing changes the interpretation.
Practice: rebuild asset turnover for all nine company-years using ending total assets as the denominator. Keep the original revenue and all frozen facts unchanged. Show revenue, beginning assets, ending assets, two-point average assets, the original average-balance ratio, the ending-balance ratio and ending-minus-average where both are available. State both formulas and units. FY2022 average-based results must stay unavailable; do not invent FY2021 balances or use a one-number average.
Independently calculate the practice answer from source facts. Compare the size and direction of the FY2024 difference for each company and explain how rising or falling balance sheets affect the denominator. Discuss Home Depot's acquisition timing using the supplied sourced comparability evidence, without claiming that either denominator is established truth or that the difference proves operational efficiency. Submit the comparison CSV, live-formula Excel practice section and an interpretation of at most 250 words.
Submit all nine company-years, both definitions, both denominators, availability reasons and an interpretation of no more than 250 words. Discuss HD FY2024 acquisition timing using the source register. Do not invent FY2021 or assume that the denominator giving a preferred ranking is the correct one.
Use the method on a client project
Agree the peer set, reporting perimeter, accounting basis, source cutoff and metric definitions before interpreting rankings. Preserve exact source versions, retrieve the actual filing and record missing values. For a different peer, inspect its concept mapping and cost classifications rather than copying this mapping blindly. When a result surprises you, inspect the numerator, denominator, period and operating context before proposing a causal explanation.
The package includes repair prompts for altered source facts, incomplete denominators and unsupported claims. Record the exact failure, fix and affected retest. Source/calculation checks, artifact inspection, isolated prompt replay, native Excel and human usability are different evidence categories. The release report states what was actually done; your fresh-chat and Excel test remains a separate activity.
Sources and next step
Home Depot FY2024 10-K; Lowe's FY2024 10-K; Floor & Decor FY2024 10-K. Earlier accessions and every source fact are linked in the source register and workbook.
Read the source/reuse notice for access and publication scope, and the comparability register for disclosure locators. Lesson 6 stays accepted and is not regenerated. Use L07_Next_Prompt_Lesson_8.txt when ready to create Lesson 8, Analyze procurement and supplier concentration.
