Capstone brief: a demand analysis
An optional, independent project for students who want to go beyond the graded problem sets. Suitable as an end-of-term project for any single module or as a cross-module option after completing the midterm. No API key is required unless a student chooses to extend the project with the optional code extension described below, in which case an instructor can supply a shared class key. This brief has no separate answer key; instructors grade against the rubric at the end of this document.
Objective
Choose either a single brand (using the designer field) or a single publicly traded luxury company (using the stock_ticker field) and build a short, evidence-based demand analysis using ALT/FNDATA's sandbox and tutorials. Your analysis should combine at least two of the core signals covered in this course, pricing power, sell-through rate, and a quarterly demand index, into a coherent picture of demand for your chosen brand or company, while being explicit about the limitations of the data and the methodology.
Required steps
- Choose your subject. Pick one brand (a specific designer value) or one publicly traded company (a specific stock_ticker value) that appears in at least one category table in the sandbox. If you choose a company with brands in more than one category, note this, since it opens the door to a cross-category roll-up (see worked example 1 below).
- Confirm your data. In the sandbox coverage browser and schema tab, confirm the exact table name(s) and column names you will query. Use only documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker.
- Compute pricing power. Write and run a SQL query in the sandbox that computes pricing power (median, or clearly labeled average, of usd_price_decimal divided by sale_estimates_high_usd_price, restricted to sold records with a valid, non-zero high estimate) for your subject.
- Compute sell-through rate. Write and run a SQL query that computes sell-through rate (sold count divided by total offered count, correctly including both sold and unsold records) for your subject.
- Build a quarterly demand index. Write and run a SQL query that groups sold records for your subject by quarter of sale_date and returns a count and an average or median usd_price_decimal per quarter. Treat this step explicitly as a methodology exercise: state the recency caveat (the most recent one to two quarters are typically under-represented because ingestion lags behind sale_date) and describe what you did to account for it, such as excluding or separately flagging the final quarter or two.
- Optional code extension. If you want to go further, use the tutorials notebook and the Python client from docs.altfndata.com, or the production API with an instructor-supplied class key, to reproduce one of the steps above programmatically and to pull a larger sample than is convenient to inspect by hand in the sandbox.
- Write the brief. Synthesize your findings into the deliverable described below.
Deliverable
A two to three page brief (or the equivalent in a slide deck, at the instructor's discretion) containing:
- A one paragraph statement of your chosen subject and why you picked it.
- Your three computed figures (pricing power, sell-through rate, and a description or simple chart of your quarterly demand index), each clearly labeled with the query or method used to produce it.
- A synthesis section, 300 to 500 words, that interprets what the three signals together suggest about demand for your subject, referencing at least one comparison point (a second brand, a second time window, or a general benchmark such as a pricing power figure near or above 1.0) to give your reader context.
- A limitations section, at least 150 words, that explicitly addresses the recency caveat on your quarterly index, sample size, and any other limitation specific to your subject (for example, a brand with very few records, or a company whose ticker maps to brands you excluded from your analysis).
- Your underlying SQL queries (and, if used, your API request bodies), included as an appendix so an instructor can verify your methodology.
Worked example directions
These are starting points to illustrate the range of acceptable projects, not the only acceptable subjects, and not worked answers.
1. Richemont via CFR.SW, a cross-category roll-up. Richemont's brands span both the watches and jewelry and gems categories in this dataset. A student choosing this direction would compute pricing power and sell-through separately for stock_ticker equal to CFR.SW in each category table, then discuss why a diversified conglomerate's true demand picture benefits from being read across categories rather than from a single one, and note any meaningful difference between the two category-level results.
2. Van Cleef and Arpels pricing power. Van Cleef and Arpels has shown a pricing power figure of roughly 1.36 in course materials, meaning buyers have historically paid about 36 percent over the auction house's high estimate. A student choosing this direction would recompute the figure for a defined time window in the sandbox, compare it against one or two other jewelry houses, and discuss what a consistently above-1.0 pricing power figure suggests about a brand's collector base and pricing discipline at auction.
3. A sell-through comparison across two competing brands. A student choosing this direction would pick two brands that compete directly in the same category (for example two watch brands at a similar price point) and compute sell-through rate and pricing power for each, then build a short comparative narrative about which brand shows broader demand (higher sell-through) versus which shows more concentrated, intense demand (higher pricing power but possibly lower sell-through), explicitly avoiding any claim about which brand is "winning" in an absolute sense, since the two metrics can tell different stories.
Grading rubric (100 points)
| Component | Points | What earns full credit |
|---|---|---|
| Subject selection and framing | 10 | A clearly stated, well-motivated subject with a specific designer or stock_ticker value named |
| Pricing power calculation | 15 | Correctly scoped SQL query (sold records, valid non-zero high estimate) with the resulting figure clearly reported |
| Sell-through calculation | 15 | Correctly scoped SQL query (both sold and unsold in the denominator) with the resulting figure clearly reported |
| Quarterly demand index and recency caveat | 20 | Correctly bucketed query, a clear description of the series, and an explicit, specific statement of the recency caveat and how it was handled |
| Synthesis | 20 | A coherent, evidence-based interpretation that combines all three signals and includes at least one comparison point |
| Limitations | 10 | A specific, genuine limitations discussion of at least 150 words that goes beyond a generic disclaimer |
| Methodology appendix | 10 | Complete, runnable SQL (and API body, if used) that reproduces every reported figure |
A capstone project does not require a fixed correct answer, since the brand or company chosen will produce different figures for every student. Grade for methodological soundness, clarity, and appropriate caution around the recency caveat and single-snapshot limitations, not for matching a specific number.