# Sources and Verification — FCA Individual Portfolio

## 1. Original data source (genuine, public, documented)
- **Publisher:** Tableau Software — *Sample Superstore (Orders)*, provided for business-intelligence education.
- **Publisher page:** https://public.tableau.com/app/learn/sample-data ("Superstore Sales — information about products, sales, and profits… fictitious company", free sample).
- **File used:** `Sample – Superstore.csv` — 9,994 order lines × 21 columns. Preserved byte-identical at `source/superstore_full.csv` (2,298,597 bytes). Workbook sheet `01_Raw_Reference` shows the first 500 untouched rows for audit.
- **Download mirror (exact bytes):** https://raw.githubusercontent.com/praveen-kumar-maurya/Superstore-Sales-Dashboard-Power-BI/main/Sample%20-%20Superstore.csv
- **Cross-check mirror:** https://raw.githubusercontent.com/ThigasSantos/BaseSuperStore/main/sales.csv (same schema, same totals).
- **License:** Tableau free sample data for educational/analysis use; mirrors redistribute for education (see repo pages). Suitable for a student journal; attribution given here and in journal §14.
- **Why this dataset:** it contains BOTH recorded revenue (`Sales`) AND recorded profit (`Profit`) on every line, plus `Order Date`, `Category`, `Sub-Category`, `Quantity`, `Discount` — enough for credible Revenue/Cost/Profit/Margin plus category and 48-month trend analysis with no invention.

## 2. Dataset evaluated and rejected (honesty record)
- **UCI Online Retail (Chen, D., 2015)** — https://archive.ics.uci.edu/dataset/352/online+retail — 541,909 transactions, CC BY 4.0. Has `Quantity × UnitPrice` (= revenue) but **no cost field**. Using it would force fabricated costs, which the faculty rules forbid. Rejected for that reason; documented here so the choice is auditable.

## 3. Field definitions and financial meanings
| Field | Meaning |
|---|---|
| Sales | Recorded line revenue, USD. Revenue = Sales. |
| Profit | Recorded line profit/loss, USD (negatives kept). Profit = recorded Profit. |
| TotalCost (calculated) | Sales − Profit. Valid because the dataset defines Profit = Sales − Cost. No estimation. |
| Profit Margin | Total Profit ÷ Total Revenue (overall 12.47%); row margin = Profit ÷ Sales. |
| Order Date / YearMonth | Transaction date; trend dimension (2014-01-03 to 2017-12-30, 48 months). |
| Category / Sub-Category | Product hierarchy for mix analysis. |
| Ship Mode | Delivery option ONLY — never treated as a cost (no shipping-cost column exists). |
| Discount | Discount rate context (e.g. Tables 26%, Machines 31%). |

## 4. Methodology
1. Kept raw file untouched; all work on copies.
2. Parsed dates (`format=mixed`); 0 rows dropped (no null keys); added Year/Month/YearMonth; sorted chronologically.
3. Workbook built by `scripts/build_workbook.py` (pandas + openpyxl): Cost and Margin columns are **live Excel formulas** (`=L−N`, `=Profit/Sales`); Category sheet uses `SUMIFS`, Monthly uses `SUMIF`, KPIs reference cleaned totals; Validation sheet compares Python vs Excel to $0.01.
4. Charts built by `scripts/build_charts.py` (matplotlib) from the same reconciled frame.
5. Journal built by `scripts/build_journal.py` (python-docx + reportlab), Times New Roman, embedding the same charts and KPI values plus real Looker screenshots. The final PDF now embeds actual Times New Roman fonts, preserves image proportions and keeps each image with its caption.
6. Looker dashboard built with Playwright browser automation on the owner's Google account: `FCA_Looker_Source.csv` uploaded to Drive → opened with Google Sheets (`FCA_Looker_Source`, 9,994 records) → blank report created at lookerstudio.google.com → Sheets connector → bar/time-series/donut charts + date-range control placed by drag → report initially renamed with static KPI values → Share dialog set to Unlisted (later verified to grant Editor access) → URL saved. Every step screenshotted into `Step_By_Step_Evidence/01–09` (consent trail, connection, editor, share proof).

## 5. Verification (independent reconciliation)
- Python totals: Revenue $2,297,200.86 · Cost $2,010,803.84 · Profit $286,397.02 · Margin 12.4672% — PASS vs stored (diff $0.00, re-run log in `09_Validation/validation_final.txt`).
- Matches the published community-verified totals for this file ($2,297,200.86 / $286,397.02).
- Category sums = total; Monthly sums = total (checked in Python; Excel formulas re-check on open in sheet 08).
- Category margins: Technology 17.40% ($145,455), Office Supplies 17.04% ($122,491), Furniture 2.49% ($18,451).
- Loss-makers kept visible: Tables −$17,725 (−8.56%), Bookcases −$3,473, Supplies −$1,189. Best month Nov-2017 ($118,448 revenue); worst Jan-2015 (−$3,281 profit).
- Final dashboard: all four genuine scorecards were independently inspected in the authenticated editor. Revenue, TotalCost and Profit use Sum and USD; Margin is the chart-level formula `SUM(Profit)/SUM(Revenue)` with Percent type. Source field IDs are retained in the configuration snapshots.
- Full-data totals agree with the CSV and workbook. Technology filter: Revenue $836,154.03, Cost $690,699.08, Profit $145,454.95, Margin 17.40%. 2017 date filter: Revenue $733,215.26, Cost $639,775.99, Profit $93,439.27, Margin 12.74%. Tests were performed through the genuine viewer controls and editor filter bar.
- The public report opens with no Google login and renders all three existing charts. Static KPI title values, test text and redundant cards were removed. Date, Category and Region controls were added because no functional date control remained in the inherited report.
- Final sharing: Unlisted with public-link **Viewer** access, saved and verified after the owner’s later instruction to complete all remaining work. The owner remains Owner. The earlier Editor choice is superseded; its screenshot remains historical evidence.
- Workbook: every cost and margin row formula was checked. Artifact Tool recalculation exposed an invalid row-count formula. It was repaired to COUNTA; null dates and negative-profit counts now have real checks. All nine validation rows are PASS, and 20,376 existing formula cells have calculated caches. Original style and non-worksheet assets were retained.
- Files open-tested: XLSX (nine sheets), editable DOCX (14 headings and seven images), handwritten DOCX (14 headings), PDF (five pages and seven visible images). The PDF was rendered and every page visually reviewed. Both DOCX files were subsequently paginated with Aspose.Words 26.9 and every page visually reviewed (ten journal pages, two handwritten pages). Corrected embedded image references were verified against the final screenshot bytes.
- Independent machine-readable results: `Step_By_Step_Evidence/09_Validation/2026-10-13_independent/qa_results.json`. All historical screenshots are preserved; file-size heuristics from the earlier run are not proof of visual readability.

## 6. Limitations (explicit, per faculty rules)
- **Contribution & BEP not computed:** no fixed/variable cost split exists in the source; any BEP number would be invented. Journal §5 gives the theoretical formulas and states this plainly.
- **Cost is derived, not separately recorded:** TotalCost = Sales − Profit is exact under the dataset's definition, but it cannot be split into material/labour/overhead.
- **Fictitious company:** Tableau's sample models a US office-supplies retailer; patterns are realistic but not a real firm's accounts.
- **Historical KPI automation limitation resolved:** the original metric-picker failures remain documented. The completed report now has four genuine live scorecards; the static substitutions have been removed. The in-app browser could configure fields but could not render some chart frames. A separate public Playwright browser rendered the existing charts and captured genuine final evidence.
- **Chart scope:** the preserved bar counts distinct sub-categories by Category, the existing time series shows daily Revenue, and the existing pie groups by Order Date. Figures 1–4 provide the required financial category, cost/profit, sub-category and monthly analyses. None of the historical chart defaults is relabelled as a different metric.
- **Recommendations:** suggested savings and discount effects are scenarios, not causal estimates or forecasts. Demand and detailed cost response are unavailable.
- **DOCX visual QA resolved:** the initial LibreOffice attempt failed, so a workspace-local Word-compatible renderer was used. Full content and all sections were verified with no truncation, and every page was reviewed. Evaluation watermarks occur only in internal preview exports; original DOCX files were never saved by the evaluation renderer. Rendering documentation: https://docs.aspose.com/words/python-net/rendering/.

## 7. Reproduce without overwriting verified work
- Back up final deliverables first. Raw CSV and historical screenshots must remain untouched.
- `scripts/independent_qa.py` performs read-only checks and renders the PDF.
- `scripts/recalculate_workbook.mjs` computes formula results with Artifact Tool; `scripts/save_verified_caches.py` transfers verified caches and targeted repairs while preserving workbook formatting.
- `scripts/update_final_documents.py` applies the screenshot/text corrections in place and uses the existing PDF builder. The old `build_docx()` function is a historical builder and must not be rerun over the verified editable journal.
- `scripts/complete_dashboard.py` provides supported Playwright UI actions. The saved authentication state is no longer authenticated, so its observed use here was the public viewer; final editor configuration used the owner's newly authenticated in-app session. Do not bypass authentication.
- Prior browser experiments remain in `scripts/archive/`. Updated screenshots are added with new filenames rather than replacing historical evidence.

## 8. Final completion review
- Corrected DOCX screenshot relationships, table page breaks, oversized text emphasis and handwritten heading fonts.
- Verified final Viewer sharing and a fresh anonymous browser without an Edit button.
- Re-rendered and visually reviewed all five final PDF pages after the sharing-note update.
- All local checks in the final QA result are PASS; all nine workbook validation rows remain PASS.
- Packaged final artifacts and genuine evidence in FCA_Submission_Ready.zip; temporary renderer previews and recalculation artifacts remain local QA files.