Financial Model - Colgate Palmolive (unsolved template)

Prepared by Dheeraj Vaidya, CFA, FRM

[email protected]

Table of Contents
Income Statements
Balance Sheet
Cash Flows
Depreciation Schedule
Amortization Schedule
Working Capital Schedule
Other Long Term Schedule
Shareholder's Equity Schedule
Shares Outstanding Schedule
Debt Schedule

visit - www.wallstreetmojo.com

https://www.wallstreetmojo.com/free-financial-modeling-training-course/
Tüm ayrıntılı bilgiler için:
d template)
Colgate-Palmolive Company
Income Statement (Consolidated)

($ in Million Except Per Share Amounts) Dec-10 Dec-11

Net sales 15,564 16,734
Cost of sales 6,360 7,144
Gross profit 9,204 9,590

Selling, general and administrative expenses 5,414 5,758

Other (income) expense, net 301 (9)
EBIT 3,489 3,841
Interest expense, net 59 52

EBT 3,430 3,789

Provision for income taxes 1,117 1,235
Net income including noncontrolling interests 2,313 2,554
Less: Net income attributable to noncontrolling interests 110 123
Net income attributable to Colgate-Palmolive Company 2,203 2,431
Earnings per common share, basic 2.23 2.49
Earnings per common share, diluted 2.16 2.47

Basic Weighted Average Shares

Diluted Weighted Average Shares

EBITDA

Vertical Analysis Dec-10 Dec-11

Net sales
Cost of sales
Gross profit

Selling, general and administrative expenses

Other (income) expense, net
Operating profit
Interest expense, net

Income before income taxes

Provision for income taxes
Net income including noncontrolling interests
Less: Net income attributable to noncontrolling interests (% o
Net income attributable to Colgate-Palmolive Company

Effective Tax Rates

Horizontal Analysis Dec-10 Dec-11
Net sales
Cost of sales
Gross profit

Selling, general and administrative expenses

Other (income) expense, net
Operating profit
Interest expense, net

Income before income taxes

Provision for income taxes
Net income including noncontrolling interests
Less: Net income attributable to noncontrolling interests
Net income attributable to Colgate-Palmolive Company

Segmental Information
Dec-10 Dec-11

Oral, Personal and Home Care

North America
% growth (yoy)
Latin America
% growth (yoy)
Europe/South Pacific
% growth (yoy)
Asia
% growth (yoy)
Africa/Eurasia
% growth (yoy)
Total Oral, Personal and Home Care

Pet Nutrition
% growth (yoy)

Total Net sales

Modeling Revenues and Costs

Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18

Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18

Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18

Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18

Colgate-Palmolive Company
Consolidated Balance Sheets
Dec-10 Dec-11 Dec-12 Dec-13
Assets
Current Assets
Cash and cash equivalents 490 878
Receivables 1,610 1,675
Inventories 1,222 1,327
Other current assets 408 522
Total current assets 3,730 4,402

Property, plant and equipment, net 3,693 3,668

Total assets 11,172 12,724

Liabilities and Shareholders' Equity

Current Liabilities
Total current liabilities 3,728 3,716

Revolver

Long-term debt 2,815 4,430

Deferred income taxes 108 252
Other liabilities 1,704 1,785
Total liabilities 8,355 10,183
Commitments and contingent liabilities - -
Shareholders'
TotalEquity
Colgate-Palmolive
Company shareholders'
equity 2,675 2,375
Noncontrolling interests 142 166
Total shareholders' equity 2,817 2,541
Total liabilities and
shareholders' equity 11,172 12,724

Check
Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Consolidated Cash Flows
### Dec-08 Dec-09 Dec-10 Dec-11 Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Operating Activities
Net income including noncontrolling interests $ 2,313 $ 2,554
Adjustments to reconcile net income
Depreciation and amortization 376 421
Receivables 40 (130)
Inventories (10) (130)
Accounts payable and other accruals (65) 199
Other non-current assets and liabilities 135 54
Net cash provided by operations 3,211 2,896

Investing Activities  
Capital expenditures (550) (537)
Net cash used in investing activities (658) (1,213)

Cash Flow for Financing Activities 2,553 1,683

Financing Activities  
Principal payments on debt (4,719) (4,429)
Proceeds from issuance of debt 5,015 5,843
Revolver
Dividends paid (1,142) (1,203)
Purchases of treasury shares (2,020) (1,806)
Proceeds from exercise of stock options and excess tax benefits 242 353
Net cash used in financing activities (2,624) (1,242)

Effect of exchange rate changes on Cash and cash equivalents (39) (53)
Net increase (decrease) in Cash and cash equivalents (110) 388

Cash and cash equivalents at beginning of year 600 490

Cash and cash equivalents at end of year $ 490 $ 878

Supplemental Cash Flow Information

 
Income taxes paid $ 1,123 $ 1,007 $ 1,280 $ 1,087
Interest paid 70 58 77 118
Colgate-Palmolive Company
Consolidated Depreciation and Capex
Dec-07 Dec-08 Dec-09 Dec-10 Dec-11
Net Sales
Capital Expenditures
Capital Expenditures as % of Net Sales

Beginning Net PP&E

Capital Expenditures
(Depreciation Expense)
Ending Net PP&E

Total Depreciation Expense

Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Consolidated Amortization & Intangible
Dec-07 Dec-08 Dec-09 Dec-10 Dec-11
Net Sales
Additions to Intangibles
Additions to Intangibles as % of Net Sales

Beginning Net Intangibles

Additions to Intangibles
(Amortization Expense)
(Intangible Sales and write offs)
Ending Net Intangibles
Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Consolidated Working Capital
Dec-09 Dec-10 Dec-11
Net Sales
Cost of Sales

Working Capital Balances

Receivables
Inventories
Other current assets
Total Non Cash Current Assets

Accounts payable
Accrued income taxes
Other accruals
Total Non-Debt Current Liabilities

Net Working Capital/ (Deficit)

(Increase)/ Decrease in Working Capital

Purchases

Ratios & Assumptions

Accounts Receivable, net (Collection period in days)
Inventory (Days outstanding)
Other Current Assets (% of Net Sales)

Accounts Payable (Days Payable)

Accrued Income Taxes (% of COGS)
Other accruals (% of COGS)

Cash Flow from Individual line items

Receivables (net of allowances of $49, $53, $52, $47 and $51 respectively)
Inventories
Other current assets
Accounts payable
Accrued income taxes
Other accruals
(Incease)/ Decrease in Working Capital

Check
Dec-12 Dec-13 Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Number of 365
Colgate-Palmolive Company
Other Long Term Asset Liability Schedule
Dec-10 Dec-11 Dec-12 Dec-13

Assets
Deferred income taxes
Other assets

Liability
Deferred income taxes
Other liabilities
Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Consolidated Shareholder's Equity
Dec-10 Dec-11 Dec-12 Dec-13
Beginning Equity Balance
Net Income
Issuance/ (Repurchase) of Equity
Dividends Paid
Option Proceeds
Ending Equity Balance

Share Repurchase Assumptions

Current Year EPS
Assumed Current Year EPS Multiple
Implied Share Price
Shares Repurchased - millions
Amount Repurchased ( $ outgo)

New Shares from Exercised Options

New Shares from Exercised Options - millions
Average Strike Price
Option Proceeds

Restricted Stock Units (RSUs)

Dividend Assumptions
Total Dividends Paid
Net Income
Dividend Payout Ratio
Shares and ESOPs

Balance, January 1, 2011

Common stock acquired

Shares issued for stock options
Shares issued for restricted stock units and other
Balance, December 31, 2011

Common stock acquired

Shares issued for stock options
Shares issued for restricted stock units and other
Balance, December 31, 2012

Common stock acquired

Shares issued for stock options
Shares issued for restricted stock units and other
Balance, December 31, 2013
Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Consolidated Shares Outstanding
Dec-10 Dec-11 Dec-12 Dec-13
Beginning Balance - Basic (actual)
Shares Issued (actual realization of options)
Shares Repurchased
Ending Balance - Basic (actual)

Basic Weighted Average Shares

Effects of Options & Restricted Stock Units
Diluted Weighted Average Shares
Dec-14 Dec-15 Dec-16 Dec-17 Dec-18
Colgate-Palmolive Company
Debt Schedule
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Cash Flow Available for Financing Activities
Proceeds from/ (Repurchase of) Equity
Dividends
Option Proceeds
+ Beginning Cash Balance
- Minimum Cash Balance
Cash Available for Debt Repayment
Long Term Debt Issuance
Long Term Debt (Repayments)
Cash Available for Revolving Credit Facility

Revolving Credit Facility

Beginning Balance
Discretionary (Paydown)/ Borrowings
Ending Balance -

Long Term Debt

Beginning Balance
Issuance
(Repayment/ Amortization)
Ending Balance -

Long Term Debt

Current Portion of Long Term Debt

Revolving Credit Facility Average Balance

Interest Rate
Interest Expense

Long Term Debt Average Balance

Interest Rate
Interest Expense

Total Interest Expense

Cash Balances Average Balance
Interest Rate
Interest Income
Dec-15 Dec-16 Dec-17 Dec-18