Colgate-Palmolive Financial Model

For each of the six reporting segments (North America, Latin America, Europe, Asia Pacific, Africa/Eurasia, and Hill's Pet Nutrition), the revenue dynamics are modelled identically but with different regional assumptions:

• Segment Name: e.g., Latin America OPHC, Hill's Pet Nutrition.
• Revenue Driver Formula: Prior Year Segment Revenue x (1 + Organic Volume Growth + Net Selling Price Growth + Foreign Exchange Impact).
• Historical Growth Rate: The company targets 3% to 5% long-term organic sales growth, though it achieved 7.4% organic growth in 2024 (driven heavily by pricing). Hill's has historically grown faster, often in the high single to low double digits.
• Key Growth Levers and Headwinds: Premiumisation (e.g., optic white, therapeutic toothpaste), increasing household penetration, and veterinary endorsements for Hill's Prescription Diet. Headwinds include private label competition and severe foreign exchange translation drags.
• Pricing Dynamics: Highly dependent on local inflation. Latin America frequently sees high pricing growth to offset currency devaluation (including hyperinflation accounting in Argentina).
• Revenue Recognition Notes: Recognised at the point in time when control transfers to the customer (retailers or distributors). Trade promotions and couponing are recorded as a reduction of gross sales.
• Seasonality: Relatively stable throughout the year, though Hill's can see slight fluctuations based on veterinary stocking patterns and promotional calendars.

• Total Assets: Approximately $16 billion to $17 billion.
• Key Asset Categories: Property, plant and equipment (manufacturing facilities) and a large balance of goodwill and intangibles from historical acquisitions (e.g., Tom's of Maine, PCA Skin, Hello Products).
• Goodwill & Intangibles: Represents roughly 25% to 30% of total assets.
• Working Capital Profile:
• Days Sales Outstanding (DSO): 30 to 35 days.
• Days Inventory Outstanding (DIO): 55 to 65 days.
• Days Payable Outstanding (DPO): 75 to 85 days.
• Net working capital is typically negative or very low as a percentage of revenue, providing a structural cash flow advantage as the business grows.
• PP&E: Global manufacturing footprint, particularly heavy in the Hill's segment which requires specialised extrusion and canning facilities.
• Right-of-use assets: Operating leases are present but not a dominant feature of the capital structure.

• Capex as % of revenue: Typically 3.5% to 4.5%.
• Maintenance vs. growth capex: Roughly 40% maintenance, 60% growth and productivity initiatives.
• Major capex programmes: Significant recent investments in Hill's manufacturing capacity, including a new plant in Tonganoxie, Kansas, and the integration of Red Collar facilities.
• M&A pattern: Bolt-on acquirer. Focuses on premium personal care brands and veterinary/pet nutrition assets.

• Total Debt: Approximately $8.5 billion to $9.5 billion.
• Debt/EBITDA ratio: Typically managed between 1.5x and 2.0x.
• Credit rating: AA- (S&P) / Aa3 (Moody's), reflecting an exceptionally strong balance sheet.
• Key debt instruments: Commercial paper for short-term liquidity, backed by a $3.0 billion revolving credit facility, and long-term senior unsecured notes.
• Interest rate profile: Predominantly fixed-rate long-term bonds with a weighted average cost of debt around 3.0% to 4.0%.
• Share repurchase programme: Highly active. The company repurchased $1.7 billion in shares in 2024.
• Dividend policy: The company has paid uninterrupted dividends since 1895 and increased the dividend for 62 consecutive years. The payout ratio targets 55% to 65% of net income.

• Operating cash flow conversion: Exceptionally strong. OCF to Net Income typically runs at 1.2x to 1.4x ($4.1 billion OCF in 2024).
• Free cash flow margin: Consistently between 14% and 17% of revenue.
• Major non-cash items: Depreciation, amortisation, and stock-based compensation.
• Working capital cash flow impact: Favourable payment terms with suppliers (high DPO) mean working capital is often a source of cash during periods of growth.
• Cash tax rate: Generally tracks close to the GAAP effective tax rate of 22% to 24%.

1. Assumptions: Hardcoded inputs for macroeconomic drivers, segment volume/price/FX growth, margin targets, and capital return policies.
2. Revenue Build: Calculates revenue for North America, Latin America, Europe, Asia Pacific, Africa/Eurasia, and Hill's Pet Nutrition using Volume, Price, and FX drivers.
3. Income Statement: Consolidated P&L from Gross Sales down to Net Income and EPS. Must explicitly separate Advertising Expense from SG&A.
4. Balance Sheet: Standard assets, liabilities, and shareholders' equity. Must include separate lines for Goodwill and Intangible Assets.
5. Cash Flow Statement: Indirect method starting from Net Income, adjusting for D&A, working capital changes, capex, debt issuance/repayment, dividends, and share repurchases.
6. Working Capital & Capex: Schedules for Accounts Receivable, Inventory, Accounts Payable, and PP&E roll-forward.
7. Debt Schedule: Tranches of short-term debt (commercial paper) and long-term debt, calculating interest expense based on average balances.
8. Valuation: Discounted Cash Flow (DCF) using unlevered free cash flow, WACC calculation, and terminal value, alongside a comparable company multiples table.

1. `Segment Revenue = Prior Year Segment Revenue * (1 + Organic Volume Growth + Net Selling Price Growth + FX Impact)`
2. `Total Net Sales = Sum of all 6 Segment Revenues`
3. `Gross Profit = Total Net Sales * Gross Margin %`
4. `Advertising Expense = Total Net Sales * Advertising % of Sales`
5. `SG&A Expense (ex-Ad) = Total Net Sales * SG&A % of Sales`
6. `Base Business Operating Profit = Gross Profit - Advertising Expense - SG&A Expense (ex-Ad)`
7. `GAAP Operating Profit = Base Business Operating Profit - Restructuring Charges`
8. `Interest Expense = Average Total Debt Balance * Weighted Average Interest Rate`
9. `Net Income = (GAAP Operating Profit - Interest Expense + Interest Income) * (1 - Effective Tax Rate)`
10. `Accounts Receivable = (Total Net Sales / 365) * DSO`
11. `Inventory = (COGS / 365) * DIO`
12. `Accounts Payable = (COGS / 365) * DPO`
13. `Dividends Paid = Prior Year Net Income * Target Payout Ratio`
14. `Share Repurchases = Free Cash Flow - Dividends Paid - Minimum Cash Build Requirement`
15. `Ending Share Count = Beginning Share Count - (Share Repurchases / Average Share Price)`

• The Assumptions sheet dictates the growth rates and margin profiles used in the Revenue Build and Income Statement.
• The Revenue Build feeds 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 & Capex sheet uses Revenue and COGS from the Income Statement to calculate balance sheet items, and the period-over-period changes flow to the Cash Flow Statement.
• The Debt Schedule uses the cash shortfall or surplus from the Cash Flow Statement to determine commercial paper borrowing or paydown, which in turn calculates Interest Expense for the Income Statement (creating a circular reference that must be managed via an iterative calculation or toggle).

• Income Statement: Revenue is positive. All expenses (COGS, Advertising, SG&A, Interest, Taxes) are positive numbers but are subtracted in the subtotal formulas (e.g., `Gross Profit = Revenue - COGS`).
• Balance Sheet: All assets, liabilities, and equity balances are positive.
• Cash Flow Statement: Cash inflows are positive. Cash outflows (capex, dividends, share repurchases, debt paydown) are negative.
• Working Capital: An increase in an asset (e.g., Accounts Receivable) is a negative adjustment on the Cash Flow Statement. An increase in a liability (e.g., Accounts Payable) is a positive adjustment.

1. Foreign Exchange Masking Growth: Colgate generates roughly 70% of its revenue outside the US. The model must separate organic growth (volume + price) from FX translation, otherwise projected revenue will be highly inaccurate.
2. Hyperinflation in Latin America: Operations in Argentina distort pricing growth metrics. Ensure Latin America pricing assumptions reflect a blend of hyperinflationary pricing and normal regional pricing.
3. Advertising vs. SG&A: The company reports advertising separately in its footnotes and management commentary. Combining it into a single SG&A line obscures management's primary lever for driving brand health.
4. GAAP vs. Base Business Margins: The company heavily promotes "Base Business" metrics which exclude restructuring. The model must calculate both to reconcile to historical SEC filings accurately.
5. Hill's Margin Profile: Hill's operates with a different margin structure and growth trajectory than the OPHC segments. Applying a consolidated growth rate across the board will underestimate the mix-shift benefit as Hill's becomes a larger portion of total sales.
6. Negative Working Capital Dynamics: The builder might incorrectly assume working capital consumes cash as the business grows. Colgate's high DPO means growth often generates working capital cash inflows.
7. Share Count Drift: The company aggressively buys back stock. Failing to model the declining share count will severely understate EPS growth.
8. Pricing Power Exhaustion: Historical 2023 and 2024 results show massive pricing growth. The model must taper pricing growth down to historical norms (1% to 2%) in the forecast period, relying more on volume.

1. "Consolidated Gross Margin must remain between 58.0% and 61.5%; flag if outside this band."
2. "Advertising spend must be between 11.0% and 13.0% of Total Net Sales."
3. "Hill's Pet Nutrition revenue must grow at a faster rate than the consolidated OPHC segments in the base case."
4. "Operating Cash Flow must exceed Net Income in every projected year (OCF/NI > 1.1x)."
5. "Total Assets must exactly equal Total Liabilities plus Shareholders' Equity in all periods."
6. "Dividend payout ratio must not fall below 50% or exceed 75% of Net Income."
7. "Debt/EBITDA ratio should remain below 2.5x to maintain the current credit rating profile."
8. "Effective tax rate should remain between 22.0% and 24.0%."

• Filings: SEC EDGAR (10-K, 10-Q, 8-K) and the Colgate-Palmolive Investor Relations website.
• Key Peers: Procter & Gamble (PG), Unilever (UL), Kimberly-Clark (KMB), Church & Dwight (CHD), Clorox (CLX).
• Industry Data: Kantar Brand Footprint report (for global penetration metrics), Nielsen/IRI scanner data for US market share trends.
• Consensus Estimates: FactSet or Bloomberg for forward EPS and revenue estimates to validate model outputs.

• Colgate-Palmolive 2024 Annual Report on Form 10-K (filed February 13, 2025)
• Colgate-Palmolive Q4 2024 Earnings Press Release (January 31, 2025)
• Colgate-Palmolive Investor Relations Presentations (CAGNY 2024/2025)
• SEC.gov EDGAR Database (https://www.sec.gov/edgar/searchedgar/companysearch)
• Colgate-Palmolive Investor Center (https://investor.colgatepalmolive.com/)