Sec 10k Company Analysis

SEC 10-K Company Analysis

Database Schema

The SQLite database has 5 tables with these exact column names (wrong column names are a common failure):

companies (primary key: cik)

Key columns: cik, name, sic, sic_description, entity_type, category, fiscal_year_end, state_of_incorporation, phone, description, website, former_names, owner_org

company_addresses

Columns: cik, address_type ("business"/"mailing"), street1, city, state_or_country, zip_code

company_tickers

Columns: cik, ticker, exchange

filings — use column form (NOT form_type)

Key columns: cik, accession_number, filing_date, report_date, form, core_type, size, is_xbrl

financial_facts — use column fact_name (NOT tag), form_type (NOT form)

Key columns: cik, fact_name, fact_value, unit, fact_category, fiscal_year, fiscal_period, end_date, accession_number, form_type, filed_date, dimension_segment, dimension_geography

fiscal_period values: FY (annual), Q1, Q2, Q3, Q4 fact_category values: us-gaap, dei, ifrs-full

Analysis Workflow

Step 1: Database Discovery

get_database_info()
describe_table("companies")
describe_table("financial_facts")

Step 2: Company Basics

SELECT * FROM companies WHERE cik = '<CIK>'
SELECT * FROM company_tickers WHERE cik = '<CIK>'
SELECT * FROM company_addresses WHERE cik = '<CIK>'

Step 3: Filing History

-- Column is `form`, not `form_type`
SELECT form, COUNT(*) as count FROM filings
WHERE cik = '<CIK>' GROUP BY form ORDER BY count DESC

SELECT form, filing_date, report_date, accession_number
FROM filings WHERE cik = '<CIK>' AND form = '10-K'
ORDER BY filing_date DESC LIMIT 20

Step 4: Discover Available Financial Metrics

SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>' ORDER BY fact_name LIMIT 100

Step 5: Core Annual Metrics — use PIVOT queries for multi-year trends

Fetch 10–15 years of data. Long historical ranges reveal structural shifts, merger impacts, and cyclical patterns that short windows miss. Include current assets/liabilities for working capital and tax expense for effective rate computation.

SELECT end_date,
  MAX(CASE WHEN fact_name = 'Assets' THEN fact_value END) AS Assets,
  MAX(CASE WHEN fact_name = 'AssetsCurrent' THEN fact_value END) AS CurrentAssets,
  MAX(CASE WHEN fact_name = 'Liabilities' THEN fact_value END) AS Liabilities,
  MAX(CASE WHEN fact_name = 'LiabilitiesCurrent' THEN fact_value END) AS CurrentLiabilities,
  MAX(CASE WHEN fact_name = 'StockholdersEquity' THEN fact_value END) AS Equity,
  MAX(CASE WHEN fact_name = 'NetIncomeLoss' THEN fact_value END) AS NetIncome,
  MAX(CASE WHEN fact_name = 'OperatingIncomeLoss' THEN fact_value END) AS OperatingIncome,
  MAX(CASE WHEN fact_name = 'IncomeTaxExpenseBenefit' THEN fact_value END) AS TaxExpense,
  MAX(CASE WHEN fact_name = 'EarningsPerShareDiluted' THEN fact_value END) AS DilutedEPS,
  MAX(CASE WHEN fact_name = 'CashAndCashEquivalentsAtCarryingValue' THEN fact_value END) AS Cash
FROM financial_facts
WHERE cik = '<CIK>'
  AND form_type = '10-K'
  AND fiscal_period = 'FY'
  AND fact_name IN (
    'Assets', 'AssetsCurrent', 'Liabilities', 'LiabilitiesCurrent', 'StockholdersEquity',
    'NetIncomeLoss', 'OperatingIncomeLoss', 'IncomeTaxExpenseBenefit',
    'EarningsPerShareBasic', 'EarningsPerShareDiluted',
    'CashAndCashEquivalentsAtCarryingValue'
  )
GROUP BY end_date
ORDER BY end_date DESC
LIMIT 15

If Liabilities is null for all years, compute it as Assets − Equity inline, or separately query LiabilitiesCurrent plus long-term debt components.

Step 6: Revenue Discovery (many companies use non-standard names)

-- Try common names first
SELECT fact_name, fact_value, fiscal_year, end_date
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K'
  AND fact_name IN (
    'Revenues',
    'RevenueFromContractWithCustomerExcludingAssessedTax',
    'RevenueFromContractWithCustomerIncludingAssessedTax',
    'SalesRevenueNet', 'RevenuesNetOfInterestExpense'
  )
ORDER BY end_date DESC LIMIT 20

-- If empty, discover the actual name
SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>'
  AND (fact_name LIKE '%Revenue%' OR fact_name LIKE '%Sales%')
ORDER BY fact_name LIMIT 30

Step 7: Cash Flow Analysis

SELECT end_date,
  MAX(CASE WHEN fact_name = 'NetCashProvidedByUsedInOperatingActivities' THEN fact_value END) AS OperatingCF,
  MAX(CASE WHEN fact_name = 'NetCashProvidedByUsedInInvestingActivities' THEN fact_value END) AS InvestingCF,
  MAX(CASE WHEN fact_name = 'NetCashProvidedByUsedInFinancingActivities' THEN fact_value END) AS FinancingCF,
  MAX(CASE WHEN fact_name = 'PaymentsToAcquirePropertyPlantAndEquipment' THEN fact_value END) AS Capex
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K' AND fiscal_period = 'FY'
  AND fact_name IN (
    'NetCashProvidedByUsedInOperatingActivities',
    'NetCashProvidedByUsedInInvestingActivities',
    'NetCashProvidedByUsedInFinancingActivities',
    'PaymentsToAcquirePropertyPlantAndEquipment'
  )
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

Step 8: Debt & Capital Structure

SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>' AND fact_name LIKE '%Debt%' LIMIT 30

SELECT end_date,
  MAX(CASE WHEN fact_name = 'LongTermDebt' THEN fact_value END) AS LTDebt,
  MAX(CASE WHEN fact_name = 'LongTermDebtNoncurrent' THEN fact_value END) AS LTDebtNoncurrent,
  MAX(CASE WHEN fact_name = 'DebtCurrent' THEN fact_value END) AS CurrentDebt,
  MAX(CASE WHEN fact_name = 'InterestExpense' THEN fact_value END) AS InterestExpense
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K' AND fiscal_period = 'FY'
  AND fact_name IN ('LongTermDebt', 'LongTermDebtNoncurrent', 'DebtCurrent', 'InterestExpense')
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

Step 9: Capital Returns (Share Repurchases, Dividends, Shares Outstanding)

Declining share counts combined with net income growth creates compounding EPS expansion.

SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>'
  AND (fact_name LIKE '%Repurchase%' OR fact_name LIKE '%Treasury%'
       OR fact_name LIKE '%Dividend%' OR fact_name LIKE '%SharesOut%')
LIMIT 30

SELECT end_date,
  MAX(CASE WHEN fact_name = 'PaymentsForRepurchaseOfCommonStock' THEN fact_value END) AS Buybacks,
  MAX(CASE WHEN fact_name = 'TreasuryStockValue' THEN fact_value END) AS TreasuryStock,
  MAX(CASE WHEN fact_name = 'CommonStockDividendsPerShareCashPaid' THEN fact_value END) AS DividendPerShare,
  MAX(CASE WHEN fact_name = 'EntityCommonStockSharesOutstanding' THEN fact_value END) AS SharesOutstanding
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K'
  AND fact_name IN (
    'PaymentsForRepurchaseOfCommonStock', 'TreasuryStockValue',
    'CommonStockDividendsPerShareCashPaid', 'EntityCommonStockSharesOutstanding'
  )
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

Step 10: Goodwill, Intangibles, and Long-Term Obligations

Acquisition-driven companies carry substantial goodwill; impairments signal overvaluation.

SELECT end_date,
  MAX(CASE WHEN fact_name = 'Goodwill' THEN fact_value END) AS Goodwill,
  MAX(CASE WHEN fact_name = 'IntangibleAssetsNetExcludingGoodwill' THEN fact_value END) AS Intangibles,
  MAX(CASE WHEN fact_name = 'GoodwillImpairmentLoss' THEN fact_value END) AS GoodwillImpairment,
  MAX(CASE WHEN fact_name = 'AmortizationOfIntangibleAssets' THEN fact_value END) AS Amortization
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K' AND fiscal_period = 'FY'
  AND fact_name IN ('Goodwill', 'IntangibleAssetsNetExcludingGoodwill',
                    'GoodwillImpairmentLoss', 'AmortizationOfIntangibleAssets')
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

-- Discover long-term obligation types
SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>'
  AND (fact_name LIKE '%Environmental%'
       OR fact_name LIKE '%AssetRetirement%'
       OR fact_name LIKE '%Pension%'
       OR fact_name LIKE '%PostRetirement%'
       OR fact_name LIKE '%OperatingLease%')
LIMIT 40

Step 11: Industry-Specific Metrics

After confirming the SIC code, discover what unique metrics exist for this company using LIKE patterns. Then query the ones found with the pivot pattern.

REIT / Real Estate (SIC 6500–6799):

SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%RealEstate%' OR fact_name LIKE '%FundsFrom%'
       OR fact_name LIKE '%Rental%' OR fact_name LIKE '%NumberOfReal%')
LIMIT 30

Oil & Gas / Mining (SIC 1000–1499, 1311, 2900):

SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%AssetRetirement%' OR fact_name LIKE '%Depletion%'
       OR fact_name LIKE '%Exploration%' OR fact_name LIKE '%Proved%')
LIMIT 30

Defense / Aerospace (SIC 3720–3812):

SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%RemainingPerformance%' OR fact_name LIKE '%ContractWith%'
       OR fact_name LIKE '%Unbilled%' OR fact_name LIKE '%CustomerAdvance%')
LIMIT 30

Pharmaceutical / Biotech (SIC 2830–2836):

SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%Research%' OR fact_name LIKE '%Development%'
       OR fact_name LIKE '%Collaboration%' OR fact_name LIKE '%Milestone%')
LIMIT 30

Financial Services / Banks (SIC 6000–6499):

SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%Interest%' OR fact_name LIKE '%Loan%'
       OR fact_name LIKE '%Deposit%' OR fact_name LIKE '%AllowanceFor%')
LIMIT 30

Software / SaaS (SIC 7370–7379): Focus on deferred revenue (ContractWithCustomerLiability), remaining performance obligations, available-for-sale securities, and stock-based compensation.

Industrial / Technology (SIC 3000–3999): Focus on R&D expense, PP&E, inventory, and acquisition-related goodwill.

Step 12: Income Statement Structure (Cost, Expenses, SBC)

Compute gross margin and understand the full expense stack. This reveals operating leverage, investment intensity, and how SBC distorts reported profitability for growth companies.

-- Discover cost/expense fact names
SELECT DISTINCT fact_name FROM financial_facts WHERE cik = '<CIK>'
  AND (fact_name LIKE '%CostOf%' OR fact_name LIKE '%SellingGeneral%'
       OR fact_name LIKE '%ResearchAndDevelop%'
       OR fact_name LIKE '%DepreciationDepletion%'
       OR fact_name LIKE '%AllocatedShareBased%'
       OR fact_name LIKE '%Restructuring%')
LIMIT 30

SELECT end_date,
  MAX(CASE WHEN fact_name = 'CostOfGoodsAndServicesSold' THEN fact_value END) AS COGS,
  MAX(CASE WHEN fact_name = 'SellingGeneralAndAdministrativeExpense' THEN fact_value END) AS SGA,
  MAX(CASE WHEN fact_name = 'ResearchAndDevelopmentExpense' THEN fact_value END) AS RD,
  MAX(CASE WHEN fact_name = 'DepreciationDepletionAndAmortization' THEN fact_value END) AS DDA,
  MAX(CASE WHEN fact_name = 'AllocatedShareBasedCompensationExpense' THEN fact_value END) AS SBC
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K' AND fiscal_period = 'FY'
  AND fact_name IN (
    'CostOfGoodsAndServicesSold', 'SellingGeneralAndAdministrativeExpense',
    'ResearchAndDevelopmentExpense', 'DepreciationDepletionAndAmortization',
    'AllocatedShareBasedCompensationExpense'
  )
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

If CostOfGoodsAndServicesSold returns null, try CostOfGoodsSold or search with LIKE '%CostOf%Sold%'.

Step 13: Balance Sheet Detail (Retained Earnings, AOCI, PP&E, Comprehensive Income)

These dimensions are frequently tested in analysis: retained earnings reveal cumulative profit/payout history; AOCI captures unrealized FX/pension impacts; comprehensive income diverges from net income when OCI items are material.

SELECT end_date,
  MAX(CASE WHEN fact_name = 'RetainedEarningsAccumulatedDeficit' THEN fact_value END) AS RetainedEarnings,
  MAX(CASE WHEN fact_name = 'AccumulatedOtherComprehensiveIncomeLossNetOfTax' THEN fact_value END) AS AOCI,
  MAX(CASE WHEN fact_name = 'ComprehensiveIncomeNetOfTax' THEN fact_value END) AS ComprehensiveIncome,
  MAX(CASE WHEN fact_name = 'PropertyPlantAndEquipmentNet' THEN fact_value END) AS PPE_Net
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K' AND fiscal_period = 'FY'
  AND fact_name IN (
    'RetainedEarningsAccumulatedDeficit',
    'AccumulatedOtherComprehensiveIncomeLossNetOfTax',
    'ComprehensiveIncomeNetOfTax',
    'PropertyPlantAndEquipmentNet'
  )
GROUP BY end_date ORDER BY end_date DESC LIMIT 15

Common Pitfalls

Analytical Synthesis

Strong analysis connects data across dimensions — not just listing each metric in isolation. After gathering data, identify and explain these linkages:

Capital allocation narrative: How did improving operating cash flow change priorities over time? (e.g., debt-heavy growth → debt reduction → share repurchases → EPS expansion)

Operating leverage: Is revenue growing faster or slower than operating income? Compute: operating margin = OperatingIncome / Revenue for each year.

Gross margin and cost structure: Compute gross margin = (Revenue − COGS) / Revenue. Explain whether SG&A or R&D is growing as a % of revenue (investment phase vs. harvest phase). For growth companies, express SBC as % of revenue — it often distorts reported losses.

Earnings quality: OCF / Net Income ratio — values >1x indicate non-cash charges dominate (depreciation, amortization, SBC); values <1x signal working capital consumption or aggressive accruals. Note this ratio across the trend, not just one year.

AOCI and comprehensive income: Does ComprehensiveIncome diverge materially from NetIncome? Explain the source (FX translation losses, pension remeasurement, unrealized securities gains/losses). Negative persistent AOCI signals cumulative foreign exposure or underfunded pension obligations.

Debt and coverage: Is debt growth supported by earnings and cash flow? Compute: interest coverage = OperatingIncome / InterestExpense; debt-to-equity trend.

Balance sheet composition: What drives asset growth — organic PP&E, acquisitions (goodwill), or financial assets? Note goodwill as % of total assets; flag if goodwill > 40% as acquisition concentration risk. For capital-intensive industries, track PP&E net as % of total assets.

Working capital and liquidity: Current ratio = CurrentAssets / CurrentLiabilities. Below 1.0x signals potential near-term liquidity stress. Note whether the trend is tightening.

Retained earnings trajectory: Has retained earnings grown (earnings exceed dividends) or eroded (dividends/buybacks exceeded earnings)? Negative retained earnings signals aggressive capital returns.

Shareholder returns mechanics: Declining share count × rising net income → compounding diluted EPS. Connect buyback amounts to share count reduction to EPS trajectory explicitly.

Historical inflection points: Identify years where metrics shifted sharply (mergers, downturns, business model changes). Long-term data (10+ years) often reveals these better than short windows.

Always include specific dollar amounts, year ranges, and percentage changes in insights.

Output Structure

Always produce a comprehensive final report with a "FINISH:" prefix. Aim for 5–10 year trends. Include specific dollar amounts, percentages, and multi-dimensional analytical observations.

FINISH:

## Company Overview
- Name, CIK, Ticker (Exchange), SIC code and description
- Entity type, Filer category, State of incorporation
- Fiscal year end, Address, Phone, Website
- Former names (if any)

## Financial Performance (5–10 year trend)
- Revenue: [values by year with % YoY change]
- Gross Margin (%) trend [where COGS is available]
- Operating Income and margin (%)
- Net Income and Comprehensive Income (note divergence if material)
- Effective Tax Rate trend [TaxExpense / PreTaxIncome]
- EPS (Diluted, multi-year trend)

## Balance Sheet Composition (5-year trend)
- Total Assets vs. Liabilities vs. Stockholders' Equity
- Cash & Cash Equivalents
- Current Assets / Current Liabilities (→ current ratio trend)
- Retained Earnings trajectory (positive growth or erosion?)
- AOCI trend (persistent negative = FX/pension exposure)
- Goodwill / Intangibles (if significant — note % of total assets)
- PP&E Net (for capital-intensive sectors)
- Long-term Debt (with interest expense trend)

## Income Statement Structure (expense stack)
- COGS and Gross Margin (%)
- SG&A expense (as % of revenue trend)
- R&D expense (as % of revenue trend, where applicable)
- D&A expense (note non-cash weight vs. operating income)
- Stock-Based Compensation (% of revenue for growth companies)
- Restructuring / impairment charges (if recurring or material)

## Cash Flow & Capital Allocation (5-year trend)
- Operating / Investing / Financing cash flows
- Earnings quality: OCF / Net Income ratio
- Capital expenditures
- Share repurchases (annual amounts)
- Dividends per share (trend)
- Shares outstanding (trend — connects to EPS impact)

## Long-Term Obligations (where applicable)
- Environmental loss contingencies
- Asset retirement obligations
- Pension / post-retirement liabilities
- Operating lease liabilities

## Industry-Specific Metrics
[Sector-relevant metrics with historical trend and interpretation]

## SEC Filing Activity
- Total filings, key form types and counts
- Most recent 10-K date

## Key Analytical Observations
- Capital allocation evolution with dollar amounts and timeframes
- Operating leverage trends (revenue vs. profit growth rates)
- Gross margin trajectory and cost structure evolution
- Earnings quality (OCF/Net Income ratio trend)
- AOCI and comprehensive income divergence explanation
- Debt trajectory and interest coverage
- Working capital / current ratio trend
- Retained earnings trajectory
- Shareholder returns mechanics (buybacks → share count → EPS)
- Industry-specific strategic observations
- Any historical inflection points (mergers, impairments, crises)