Home Depot Financial Model

Home Depot operates as a single segment, but revenue is modelled using two primary drivers: Customer Transactions and Average Ticket.

Retail Store and Online Sales

• Revenue driver formula: "Total Customer Transactions x Average Ticket"
• Historical growth rate: 3% to 5% normalised CAGR, though the company experienced a slight contraction in 2023 and 2024 due to high interest rates and deferred large-ticket projects.
• Key growth levers and headwinds: Housing turnover, home equity levels, mortgage rates, and the age of the housing stock are primary macro drivers. Deflation in core commodities like lumber acts as a headwind to the average ticket.
• Pricing dynamics: Highly competitive but rational. The company uses a "value" pricing strategy, adjusting daily based on local market competition and commodity input costs.
• Revenue recognition notes: Revenue is recognised at the point of sale for retail transactions. For installed services, revenue is recognised when the service is completed. Deferred revenue primarily consists of unredeemed gift cards and uncompleted installation services.
• Seasonality: The second quarter (spring and early summer) is historically the strongest period, driven by lawn and garden sales and exterior home projects. Q2 typically accounts for nearly 30% of annual revenue.

• Total assets are approximately $80 billion to $95 billion, heavily weighted towards physical infrastructure and inventory.
• Key asset categories are Merchandise Inventories (typically $22 billion to $25 billion) and Net Property and Equipment ($25 billion to $28 billion).
• Goodwill & intangibles historically represented less than 10% of assets but increased significantly following the $18.25 billion SRS Distribution acquisition.
• Working capital profile:
• Days Sales Outstanding (DSO): 7 to 10 days (most sales are cash or credit card, receivables are mostly from Pro customers).
• Days Inventory Outstanding (DIO): 75 to 85 days.
• Days Payable Outstanding (DPO): 45 to 55 days.
• Net working capital is structurally negative. The company uses its scale to negotiate favourable payment terms with suppliers, effectively funding its inventory through accounts payable.
• PP&E consists primarily of owned store locations, distribution centres, and fixtures. Useful lives range from 10 to 40 years for buildings.
• Right-of-use assets for operating leases are material (approximately $6 billion to $7 billion) as the company leases about 10% of its stores and various distribution facilities.

• Capex as a percentage of revenue typically runs between 1.8% and 2.2%.
• The split is approximately 40% maintenance (store refreshes) and 60% growth (supply chain buildout, digital platform enhancements, and new store openings).
• Major capex programmes include the ongoing interconnected retail strategy and the expansion of Pro-focused distribution centres.
• Capitalised software costs are included in PP&E but are not a massive driver compared to physical infrastructure.
• M&A pattern: Historically a bolt-on acquirer (e.g., HD Supply for $8 billion in 2020), but the SRS Distribution deal was transformational for its complex Pro strategy.

• Total debt is approximately $40 billion to $50 billion following the SRS acquisition.
• The company targets an adjusted Debt-to-EBITDAR ratio of 2.0x to 2.5x.
• Credit rating is strong investment grade (A-tier from major agencies).
• Key debt instruments are long-term senior notes and commercial paper used for short-term working capital needs.
• The maturity profile is well-laddered, with average maturities exceeding 10 years.
• Interest rate profile is predominantly fixed-rate debt.
• The share repurchase programme is highly active. The company historically repurchased $5 billion to $8 billion annually, though this was temporarily moderated to digest the SRS acquisition debt.
• Dividend policy targets a payout ratio of approximately 55% of net income.

• Operating cash flow conversion is excellent, typically 110% to 130% of Net Income.
• Free cash flow margin usually ranges from 8.0% to 10.0%.
• Major non-cash items bridging net income to OCF include Depreciation and Amortisation (over $2.5 billion annually) and deferred income taxes.
• Working capital is a source of cash during growth periods due to the negative net working capital dynamic.
• Capex intensity is low relative to cash generation, allowing for massive capital returns to shareholders.
• The cash tax rate closely tracks the GAAP effective tax rate of approximately 23.5% to 24.5%.

1. Assumptions: Hardcoded inputs for macro drivers, comparable sales, margins, working capital days, and capital return policies.
2. Store Roll-forward: Tracks beginning store count, new openings, closures, and ending store count, calculating total square footage.
3. Revenue Build: Calculates total sales using Customer Transactions and Average Ticket, reconciling comparable sales growth versus new store sales.
4. Income Statement: Standard P&L from Net Sales down to Net Income and EPS, mirroring the company's 10-K layout.
5. Balance Sheet: Assets, Liabilities, and Shareholders' Equity. Must include specific lines for Merchandise Inventories and Operating Lease Liabilities.
6. Cash Flow Statement: Indirect method starting from Net Income, adjusting for D&A, working capital changes, capex, debt issuance/repayment, dividends, and buybacks.
7. Working Capital Schedule: Calculates Receivables, Inventory, and Payables based on DSO, DIO, and DPO assumptions.
8. Debt & Interest Schedule: Tranches of senior notes, commercial paper, interest expense calculations, and debt paydown logic.
9. PP&E & Intangibles: Capex, depreciation waterfall, and goodwill tracking (including the SRS addition).
10. DCF Valuation: Unlevered free cash flow calculation, WACC assumptions, terminal value, and implied share price.

1. "Total Net Sales = Total Customer Transactions x Average Ticket"
2. "Comparable Sales Growth = (Current Period Sales from Comparable Stores / Prior Period Sales from Comparable Stores) - 1"
3. "New Store Sales = Total Net Sales - Comparable Store Sales"
4. "Sales per Square Foot = Total Net Sales / Average Total Square Footage"
5. "Cost of Sales = Total Net Sales x (1 - Gross Margin Percentage)"
6. "Gross Profit = Total Net Sales - Cost of Sales"
7. "SG&A Expense = Total Net Sales x SG&A Margin Percentage"
8. "Operating Income = Gross Profit - SG&A Expense - Depreciation & Amortisation"
9. "Ending Inventory = (Cost of Sales / 365) x Days Inventory Outstanding"
10. "Accounts Payable = (Cost of Sales / 365) x Days Payable Outstanding"
11. "Interest Expense = Average Debt Balance x Weighted Average Interest Rate"
12. "Dividends Paid = Net Income x Target Payout Ratio"
13. "Share Repurchases = Free Cash Flow - Dividends Paid - Net Debt Reduction"

The Assumptions sheet feeds the Revenue Build and Working Capital Schedule. The Revenue Build drives the top line of the Income Statement. The Income Statement generates Net Income, which flows to the top of the Cash Flow Statement and into Retained Earnings on the Balance Sheet. The Working Capital Schedule calculates the period-over-period changes that flow into the operating section of the Cash Flow Statement. The Debt & Interest Schedule uses cash flow available for debt service from the Cash Flow Statement to determine ending debt balances, which flow to the Balance Sheet, while the calculated interest expense flows back to the Income Statement. This creates a circular reference between interest expense, net income, cash flow, and debt balances, which requires an iterative calculation toggle.

• Revenue and asset balances are entered as positive numbers.
• Expenses (COGS, SG&A, Interest, Taxes) on the Income Statement are entered as negative numbers.
• Contra-asset accounts (Accumulated Depreciation) are entered as negative numbers.
• On the Cash Flow Statement, cash inflows (e.g., net income, increases in payables, debt issuance) are positive. Cash outflows (e.g., increases in inventory, capex, dividends, share repurchases) are negative.

• Failing to account for the SRS Distribution acquisition will result in a massive understatement of 2024 and 2025 revenue and debt levels.
• Modelling working capital as a percentage of revenue rather than using DIO and DPO will misrepresent cash flow, as inventory and payables scale with COGS, not revenue.
• Ignoring the impact of lumber price deflation can cause the model to overestimate Average Ticket growth.
• Assuming a linear share repurchase programme without adjusting for the temporary pause required to pay down SRS acquisition debt will overstate EPS.
• Miscalculating comparable sales by including new store openings in the numerator will artificially inflate the core growth metric.
• Forgetting to include operating lease liabilities in the enterprise value calculation will result in an inaccurate DCF valuation.
• Overestimating store count growth is a common error. Home Depot opens very few new stores annually (typically fewer than 10); growth is driven by throughput, not footprint expansion.
• Applying the 55% dividend payout ratio to operating cash flow instead of net income will result in dividends that are far too high.

• Gross margin must remain tightly bound between 33.0% and 33.5%. Flag any period outside this range.
• Operating margin should stabilise between 14.0% and 14.5%.
• Capex as a percentage of revenue must remain between 1.8% and 2.2%.
• The Balance Sheet must balance perfectly in every period (Total Assets = Total Liabilities + Shareholders' Equity).
• Operating Cash Flow must exceed Net Income (conversion ratio > 1.0x).
• Adjusted Debt-to-EBITDAR should trend towards the company's target of 2.0x to 2.5x over the forecast period.
• The effective tax rate should remain between 23.5% and 24.5%.
• Return on Invested Capital (ROIC) should remain exceptionally high, typically exceeding 30%.

• Filings: SEC EDGAR for The Home Depot (10-K, 10-Q, 8-K).
• Investor Relations: Home Depot IR website for quarterly earnings presentations and SRS Distribution acquisition call transcripts.
• Key Peers: Lowe's Companies, Inc. (LOW) is the primary direct competitor. Floor & Decor (FND) and Tractor Supply (TSCO) serve as secondary retail benchmarks.
• Industry Data: US Census Bureau data on Monthly Retail Trade, US housing starts, and existing home sales data from the National Association of Realtors.
• Commodity Data: CME Group for random length lumber futures pricing.

• The Home Depot, Inc. Annual Report on Form 10-K for the fiscal year ended January 28, 2024.
• The Home Depot, Inc. Quarterly Reports on Form 10-Q for fiscal year 2024.
• The Home Depot, Inc. Press Release: "The Home Depot to Acquire SRS Distribution", March 2024.
• US Securities and Exchange Commission EDGAR database.