sec-10k-company-analysis

name

sec-10k-company-analysis

description

Comprehensive analysis of public companies using SEC EDGAR 10-K financial data stored in a SQLite database. Use this skill whenever the task involves analyzing a company by CIK (Central Index Key), querying SEC financial data, exploring a financial database with tables like companies, filings, financial_facts, or producing a structured financial analysis report. Covers schema navigation, metric discovery, industry-specific exploration, multi-year trend analysis, income statement structure, capital returns analysis, long-term obligations, and producing an insightful financial summary that connects metrics across dimensions.

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

Call get_database_info() then describe_table() for companies and financial_facts to confirm column names.

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 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 ranges reveal structural shifts, merger impacts, and cyclical patterns.

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'  fact_value )  OperatingIncome,
  (  fact_name    fact_value )  TaxExpense,
  (  fact_name    fact_value )  DilutedEPS,
  (  fact_name    fact_value )  Cash
 financial_facts
 cik    form_type    fiscal_period  
   fact_name  (
    , , , , ,
    , , ,
    , ,
    
  )
  end_date   end_date  LIMIT ;

If Liabilities is null for all years, compute it as Assets − Equity inline.

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  end_date   end_date  LIMIT ;

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  (, , , )
  end_date   end_date  LIMIT ;

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
 cik    form_type  
   fact_name  (
    , ,
    , 
  )
  end_date   end_date  LIMIT ;

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  end_date  LIMIT ;


  fact_name  financial_facts  cik  
   (fact_name    fact_name  
        fact_name    fact_name  
        fact_name  ) LIMIT ;

Step 11: Industry-Specific Metrics

After confirming the SIC code, discover unique metrics with LIKE patterns, then pivot-query those found.

REIT / Real Estate (SIC 6500–6799): fact_name LIKE '%RealEstate%' OR '%FundsFrom%' OR '%Rental%' OR '%NumberOfReal%'

Oil & Gas / Mining (SIC 1000–1499, 1311, 2900): fact_name LIKE '%AssetRetirement%' OR '%Depletion%' OR '%Exploration%' OR '%Proved%'

Defense / Aerospace (SIC 3720–3812): fact_name LIKE '%RemainingPerformance%' OR '%ContractWith%' OR '%Unbilled%' OR '%CustomerAdvance%'

Pharmaceutical / Biotech (SIC 2830–2836): fact_name LIKE '%Research%' OR '%Development%' OR '%Collaboration%' OR '%Milestone%'

Financial Services / Banks (SIC 6000–6499): fact_name LIKE '%Interest%' OR '%Loan%' OR '%Deposit%' OR '%AllowanceFor%'

Software / SaaS (SIC 7370–7379): Focus on ContractWithCustomerLiability (deferred revenue), 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 goodwill.

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

Compute gross margin and understand the full expense stack. Reveals operating leverage and investment intensity.

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    fact_value )  DDA,
  (  fact_name    fact_value )  SBC
 financial_facts
 cik    form_type    fiscal_period  
   fact_name  (
    , ,
    , ,
    
  )
  end_date   end_date  LIMIT ;

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

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

These dimensions reveal cumulative profit/payout history, unrealized FX/pension impacts, and whether comprehensive income diverges materially from net income.

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   end_date  LIMIT ;

---

Common Pitfalls

Mistake	Correct Approach
SELECT DISTINCT tag FROM financial_facts	Use fact_name, not tag
GROUP BY form_type FROM filings	filings uses form, not form_type
Assuming Revenues exists	Try multiple names; use LIKE fallback
Only fetching 3–5 year trends	Extend to 10–15 years — structural patterns require it
Skipping capital returns (buybacks, dividends)	Always run Step 9 — drives EPS trajectory
Skipping income statement structure	Always run Step 12 — COGS, SG&A, R&D reveal cost story
Liabilities null for all years	Compute as Assets − Equity, or query components
Skipping retained earnings and AOCI	Run Step 13 — retained earnings shows payout history
fiscal_period = 'FY' still mixes quarterly data	For December year-end, add AND strftime('%m-%d', end_date) = '12-31'
No LIMIT on SELECT *	Always add LIMIT to avoid huge results

---

Computed Ratios — Calculate Per Year Across the Full Trend

After collecting raw data, explicitly compute these derived metrics for each year and include them in insights and the final report. These ratios are the connective tissue of strong analysis.

Ratio	Formula	What It Reveals
Operating margin	OperatingIncome / Revenue	Profitability leverage vs. growth
Gross margin	(Revenue − COGS) / Revenue	Pricing power and cost structure
OCF / Net Income	OperatingCF / NetIncome	Earnings quality; >1× = non-cash charges dominate
Current ratio	CurrentAssets / CurrentLiabilities	Near-term liquidity; <1× signals stress
Interest coverage	OperatingIncome / InterestExpense	Debt service safety margin
Debt / Equity	(LTDebt + CurrentDebt) / Equity	Leverage trend
Free cash flow	OperatingCF − Capex	Cash available after maintenance and growth investment
Capex / DD&A	Capex / DepreciationAmortization	>1× = net capacity expansion
SBC as % revenue	SBC / Revenue	Dilution cost for growth companies
Effective tax rate	TaxExpense / (NetIncome + TaxExpense)	Tax efficiency and one-time impacts

Compute these ratios for each available year, not just the most recent. Trends in ratios (improving vs. deteriorating) are more analytically valuable than point-in-time snapshots.

---

Analytical Synthesis

Strong analysis connects data across dimensions — not just listing each metric in isolation. After gathering data and computing ratios, identify and explain these linkages with specific dollar amounts and year ranges:

Capital allocation narrative: How did improving (or declining) operating cash flow change priorities over time? E.g., debt-heavy growth → debt reduction → share repurchases → EPS expansion. Connect buyback amounts to share count reduction to diluted EPS trajectory explicitly, year by year.

Operating leverage: Is revenue growing faster or slower than operating income? Report operating margin for each year — expanding margins signal leverage; compressing margins signal cost pressure.

Gross margin and cost structure: Compute gross margin per year. Explain whether SG&A or R&D is growing as a % of revenue (investment phase vs. harvest phase). Express SBC as % of revenue for growth companies.

Earnings quality (OCF/NI ratio): Compute per year across the trend. Values consistently >1× indicate non-cash charges dominate (depreciation, amortization, SBC). Values <1× signal working capital consumption or aggressive accruals. Changes in this ratio often precede earnings quality issues.

AOCI and comprehensive income: If ComprehensiveIncome diverges materially from NetIncome, explain the source — FX translation losses, pension remeasurement, or unrealized securities gains/losses. Persistent negative AOCI signals cumulative foreign exposure or underfunded pension obligations.

Debt and coverage: Is debt growth supported by earnings and cash flow? Report interest coverage per year. Note any debt spikes linked to acquisitions (cross-reference goodwill jumps in same year).

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

Working capital and liquidity: Report current ratio per year. Deterioration below 1.0× signals near-term stress. Connect to cash flow trends to explain whether it's structural or temporary.

Retained earnings trajectory: Growing retained earnings = earnings exceed distributions. Erosion or negative retained earnings = aggressive buybacks/dividends exceeded cumulative earnings.

Historical inflection points: Identify years where metrics shifted sharply (acquisitions, divestitures, downturns, regulatory changes). Long-term data (10+ years) surfaces these. State the year, the metric change, and the likely cause. E.g.: "Goodwill jumped from $X to $Y in [year], consistent with the [acquisition] announced in [year]; long-term debt rose simultaneously by $Z to fund the deal."

Each insight should stand alone: Include specific dollar amounts, year references, percentage changes, and at least one cross-metric connection. Avoid observations that name only one metric without context.

---

Output Structure

Always produce a comprehensive final report with a "FINISH:" prefix. Aim for 10–15 year trends. Include specific dollar amounts, percentages, computed ratios, and multi-dimensional 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 (10–15 year trend)
- Revenue: [values by year with % YoY change]
- Gross Margin (%) per year [where COGS is available]
- Operating Income and margin (%) per year
- Net Income and Comprehensive Income (note divergence if material)
- Effective Tax Rate per year [TaxExpense / (NetIncome + TaxExpense)]
- EPS (Diluted, multi-year trend)

## Balance Sheet Composition (5–10 year trend)
- Total Assets vs. Liabilities vs. Stockholders' Equity
- Cash & Cash Equivalents
- Current Assets / Current Liabilities → current ratio per year
- Retained Earnings trajectory (positive growth or erosion?)
- AOCI trend (persistent negative = FX/pension exposure; explain source)
- Goodwill / Intangibles (% of total assets; spike years linked to acquisitions)
- PP&E Net (for capital-intensive sectors)
- Long-term Debt (with interest expense and coverage ratio per year)

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

## Cash Flow & Capital Allocation (5–10 year trend)
- Operating / Investing / Financing cash flows
- Earnings quality: OCF / Net Income ratio per year
- Free cash flow = OCF − Capex per year
- Capex / DD&A ratio (net capacity expansion indicator)
- Share repurchases (annual amounts and cumulative)
- Dividends per share (trend)
- Shares outstanding (trend — connect to EPS impact with $ amounts)

## 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, computed ratios, and interpretation]

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

## Key Analytical Observations
- Capital allocation narrative: how strategy evolved (debt → buybacks → dividends) with dollar amounts
- Operating leverage trends (revenue vs. operating income growth rates)
- Gross margin trajectory and cost structure evolution
- Earnings quality (OCF/NI ratio trend — improving or deteriorating?)
- AOCI and comprehensive income divergence — source and magnitude
- Debt trajectory, leverage ratio, and interest coverage evolution
- Working capital / current ratio trend (tightening or improving?)
- Retained earnings trajectory (cumulative earnings vs. distributions)
- Shareholder returns mechanics: buyback $ → share count reduction → EPS amplification
- Historical inflection points with year, metric change, and cause
- Industry-specific strategic observations