Financial Model - Colgate Palmolive (solved template)

Prepared by Dheeraj Vaidya, CFA, FRM

dheeraj@wallstreetmojo.com

For step by step instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

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

visit - www.wallstreetmojo.com
 template)

al-modeling-in-excel/
Colgate-Palmolive Company For step by step instruction, please visi

Income Statement (Consolidated)

($ in Million Except Per Share Amounts) Dec-16 Dec-17 Dec-18 Dec-19

Net sales 15,195 15,454 15,544 15,693
Cost of sales 6,072 6,174 6,313 6,368
Gross profit 9,123 9,280 9,231 9,325

Selling, general and administrative expenses 5,143 5,400 5,389 5,575

Other (income) expense, net 25 173 148 196
EBIT 3,955 3,707 3,694 3,554
Non-service related postretirement costs 118 118 87 108
Interest expense, net 99 102 143 145

EBT 3,738 3,487 3,464 3,301

Provision for income taxes 1,152 1,313 906 774
Net income including noncontrolling interests 2,586 2,174 2,558 2,527
Less: Net income attributable to noncontrolling intere 145 150 158 160
Net income attributable to Colgate-Palmolive Comp 2,441 2,024 2,400 2,367
Earnings per common share, basic 2.74 2.30 2.76 2.76
Earnings per common share, diluted 2.72 2.28 2.75 2.75

Basic Weighted Average Shares 891.8 881.1 870.6 859.1

Diluted Weighted Average Shares 898.4 887.1 873.0 861.1

EBITDA

Vertical Analysis Dec-16 Dec-17 Dec-18 Dec-19

Net sales 100.0% 100.0% 100.0% 100.0%
Cost of sales 40.0% 40.0% 40.6% 40.6%
Gross profit Margin 60.0% 60.0% 59.4% 59.4%

Selling, general and administrative expenses 33.8% 34.9% 34.7% 35.5%

Other (income) expense, net 0.2% 1.1% 1.0% 1.2%
Operating profit Margin (EBIT Margin) 26.0% 24.0% 23.8% 22.6%
Non-service related postretirement costs 0.6% 0.7%
Interest expense, net 0.7% 0.7% 0.9% 0.9%

Income before income taxes (EBT Margin) 24.6% 22.6% 22.3% 21.0%
Provision for income taxes 7.6% 8.5% 5.8% 4.9%
Net income Margin including noncontrolling interest 17.0% 14.1% 16.5% 16.1%
Less: Net income attributable to noncontrolling inter 5.6% 6.9% 6.2% 6.3%
Net income Margin 16.1% 13.1% 15.4% 15.1%
 Effective Tax Rates 30.8% 37.7% 26.2% 23.4%

Horizontal Analysis Dec-16 Dec-17 Dec-18 Dec-19

Net sales 1.7% 0.6% 1.0%
Cost of sales 1.7% 2.3% 0.9%
Gross profit 1.7% -0.5% 1.0%

Selling, general and administrative expenses 5.0% -0.2% 3.5%

Other (income) expense, net 592.0% -14.5% 32.4%
Operating profit -6.3% -0.4% -3.8%
Interest expense, net 3.0% 40.2% 1.4%

Income before income taxes -6.7% -0.7% -4.7%

Provision for income taxes 14.0% -31.0% -14.6%
Net income including noncontrolling interests -15.9% 17.7% -1.2%
Less: Net income attributable to noncontrolling interests 3.4% 5.3% 1.3%
Net income attributable to Colgate-Palmolive Company -17.1% 18.6% -1.4%

Trend Analysis Dec-16 Dec-17 Dec-18 Dec-19

Sales 15,195 15,454 15,544 15,693
Net Profit 2,441 2,024 2,400 2,367
2016 2017 2018 2019
Sales 100.0% 101.7% 102.3% 103.3%
Net Profit 100.0% 82.9% 98.3% 97.0%

Business Risk Dec-16 Dec-17 Dec-18 Dec-19

Sales (% growth) 1.7% 0.6% 1.0%
Operating Profit (EBIT) (% growth) -6.3% -0.4% -3.8%
Net Income (% growth) -17.1% 18.6% -1.4%

Total Leverage -10.02x 31.90x -1.43x

Operating Leverage -3.68x -0.60x -3.95x
Financial Leverage 2.72x -52.97x 0.36x

Oral, Personal and Home Care

North America $ 3,117 $ 3,348 $ 3,424
% growth (yoy) 7.4% 2.3%

Latin America 3,887 3,605 3,606

% growth (yoy) -7.3% 0.0%

Europe/South Pacific 2,394 2,502 2,450

% growth (yoy) 4.5% -2.1%
 Asia 2,781 2,734 2,707
% growth (yoy) -1.7% -1.0%

Africa/Eurasia 983 967 981

% growth (yoy) -1.6% 1.4%

Total Oral, Personal and Home Care 0 13,162 13,156 13,168

0.0% 0.1%

Pet Nutrition 2,292 2,388 2,525

% growth (yoy) 4.2% 5.7%

Total Net sales $ - $ 15,454 $ 15,544 $ 15,693

instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

16,471 17,222 18,036 18,917 19,872 20,908
6,454 6,889 7,214 7,567 7,949 8,363
10,017 10,333 10,821 11,350 11,923 12,545

6,019 6,200 6,493 6,810 7,154 7,527

113 172 180 189 199 209
3,885 3,961 4,148 4,351 4,571 4,809
74 69 72 76 79 84
164 158 153 151 146 147

3,647 3,735 3,923 4,125 4,345 4,578

787 822 863 907 956 1,007
2,860 2,913 3,060 3,217 3,389 3,571
165 175 184 193 203 214
2,695 2,738 2,876 3,024 3,186 3,357
3.15 3.25 3.47 3.69 3.95 4.22
3.14 3.24 3.45 3.68 3.93 4.20

856.8 841.4 829.9 818.4 806.9 795.4

859.3 844.3 832.8 821.3 809.8 798.3

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

100.0% 100.0% 100.0% 100.0% 100.0% 100.0%
39.2% 40.0% 40.0% 40.0% 40.0% 40.0%
60.8% 60.0% 60.0% 60.0% 60.0% 60.0%

36.5% 36.0% 36.0% 36.0% 36.0% 36.0%

0.7% 1.0% 1.0% 1.0% 1.0% 1.0%
23.6% 23.0% 23.0% 23.0% 23.0% 23.0%
0.4% 0.4% 0.4% 0.4% 0.4% 0.4%
1.0%

22.1%
4.8%
17.4%
5.8% 6.0% 6.0% 6.0% 6.0% 6.0%
16.4% 15.9% 15.9% 16.0% 16.0% 16.1%
 21.6% 22.0% 22.0% 22.0% 22.0% 22.0%

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

5.0%
1.4%
7.4%

8.0%
-42.3%
9.3%
13.1%

10.5%
1.7%
13.2%
3.1%
13.9%

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

16,471
2,695
2020
108.4%
110.4%

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

5.0%
9.3%
13.9%

2.80x
1.88x
1.49x

$ 3,741 $ 4,078 $ 4,445 $ 4,845 $ 5,281 $ 5,756

9.3% 9.0% 9.0% 9.0% 9.0% 9.0%

3,418 3,452 3,487 3,522 3,557 3,592

-5.2% 1.0% 1.0% 1.0% 1.0% 1.0%

2,747 2,829 2,914 3,002 3,092 3,185

12.1% 3.0% 3.0% 3.0% 3.0% 3.0%
 2,701 2,701 2,701 2,701 2,701 2,701
-0.2% 0.0% 0.0% 0.0% 0.0% 0.0%

981 991 1,001 1,011 1,021 1,031

0.0% 1.0% 1.0% 1.0% 1.0% 1.0%

13,588 14,051 14,547 15,080 15,651 16,265

3.2% 3.4% 3.5% 3.7% 3.8% 3.9%

2,883 3,171 3,488 3,837 4,221 4,643

14.2% 10.0% 10.0% 10.0% 10.0% 10.0%

$ 16,471 $ 17,222 $ 18,036 $ 18,917 $ 19,872 $ 20,908

Colgate-Palmolive Company For step by step instruction, please visit - http

Consolidated Balance Sheets

Dec-16 Dec-17 Dec-18 Dec-19

Assets
Current Assets
Cash and cash equivalents 1,315 1,535 726 883
Receivables 1,411 1,480 1,400 1,440
Inventories 1,171 1,221 1,250 1,400
Other current assets 441 403 417 456
Total current assets 4,338 4,639 3,793 4,179

Property, plant and equipment, net 3,840 4,072 3,881 3,750

Goodwill, net 2,107 2,218 2,530 3,508
Other intangible assets, net 1,313 1,341 1,637 2,667
Deferred income taxes 301 188 152 177
Other assets 224 218 168 753
Total assets 12,123 12,676 12,161 15,034

Liabilities and Shareholders' Equity

Current Liabilities
Notes and loans payable 13 11 12 260
Current portion of long-term debt 254
Accounts payable 1,124 1,212 1,222 1,237
Accrued income taxes 441 354 411 370
Other accruals 1,727 1,831 1,696 1,917
Total current liabilities 3,305 3,408 3,341 4,038

Total shareholders' equity (243) (60) (102) 117

Noncontrolling interests 260 303 299 441
Total shareholders' equity 17 243 197 558
Total liabilities and shareholders'
equity 12,123 12,676 12,161 15,034

Check - - - -

Dec-16 Dec-17 Dec-18 Dec-19

Assets
Current Assets
Cash and cash equivalents 10.8% 12.1% 6.0% 5.9%
Receivables 11.6% 11.7% 11.5% 9.6%
Inventories 9.7% 9.6% 10.3% 9.3%
Other current assets 3.6% 3.2% 3.4% 3.0%
Total current assets 35.8% 36.6% 31.2% 27.8%
Property, plant and equipment, net 31.7% 32.1% 31.9% 24.9%
Goodwill, net 17.4% 17.5% 20.8% 23.3%
Other intangible assets, net 10.8% 10.6% 13.5% 17.7%
Deferred income taxes 2.5% 1.5% 1.2% 1.2%
Other assets 1.8% 1.7% 1.4% 5.0%
Total assets 100.0% 100.0% 100.0% 100.0%

Liabilities and Shareholders' Equity

Current Liabilities
Notes and loans payable 0.1% 0.1% 0.1% 1.7%
Current portion of long-term debt 0.0% 0.0% 0.0% 1.7%
Accounts payable 9.3% 9.6% 10.0% 8.2%
Accrued income taxes 3.6% 2.8% 3.4% 2.5%
Other accruals 14.2% 14.4% 13.9% 12.8%
Total current liabilities 27.3% 26.9% 27.5% 26.9%

Revolver

Long-term debt 53.8% 51.8% 52.2% 48.8%

Deferred income taxes 2.0% 1.6% 1.9% 3.4%
Other liabilities 16.8% 17.8% 16.7% 17.3%
Total liabilities 99.9% 98.1% 98.4% 96.3%
Commitments and contingent liabilities
Shareholders' Equity 0.0% 0.0% 0.0% 0.0%
Common stock 12.1% 11.6% 12.1% 9.8%
Additional paid-in capital 13.9% 15.7% 18.1% 16.5%
Retained earnings 164.3% 162.0% 177.7% 149.7%
Accumulated other comprehensive income ( -34.5% -30.4% -34.4% -28.4%
Unearned compensation -0.1% 0.0% 0.0% 0.0%
Treasury stock, at cost -157.8% -159.2% -174.3% -146.8%
Total Colgate-Palmolive Company shareholde -2.0% -0.5% -0.8% 0.8%
Noncontrolling interests 2.1% 2.4% 2.5% 2.9%
Total shareholders' equity 0.1% 1.9% 1.6% 3.7%
Total liabilities and shareholders' equity 100.0% 100.0% 100.0% 100.0%

Solvency Ratios
Current ratio 1.31x 1.36x 1.14x 1.03x
Quick ratio .82x .88x .64x .58x
Cash ratio .40x .45x .22x .22x

Sales 15,195 15,454 15,544 15,693

COGS 6,072 6,174 6,313 6,368
Purchases 6,224 6,342 6,518

Turnover Ratios
Receivables turnover 10.69x 10.79x 11.05x
Inventory Turnover 5.16x 5.11x 4.81x
Payables turnover 5.33x 5.21x 5.30x

Days 365
Average receivables collection period 34.1 33.8 33.0
Average inventory processing period 70.7 71.4 75.9
Average Payable Days (payment period) 68.5 70.0 68.9
Cash Collection Cycle 36.35 35.21 40.12

Operating Efficiency
Total asset turnover (Sales / Average Assets) 1.25 1.25 1.15
Net fixed asset turnover 3.91 3.91 4.11
Equity turnover (102.01) (191.90) 2,092.40

Operating Profitability
Gross Profit Margin 60.0% 60.0% 59.4% 59.4%
Operating Profit Margin 26.0% 24.0% 23.8% 22.6%
Net Profit Margin 16.1% 13.1% 15.4% 15.1%

Return on Total Assets 29.9% 29.7% 26.1%

Return on Equity (Net Income / Total Equity) 1672.3% 1162.7% 669.4%
Return on Owner's Equity -1336.0% -2963.0% 31560.0%

ROE DuPont Analysis

Profit Margin (Net Income / Sales) 14.1% 16.5% 16.1%
Asset Turnover (Sales / Average Assets) 124.6% 125.2% 115.4%
Asset Leverage (Average Assets / Average Equity) 9538.1% 5644.8% 3602.0%
Dupont ROE 1672.3% 1162.7% 669.4%

EBIT 3,694.0 3,554.0

Depreciation & Amortization 511.0 519.0
Interest Expense 143.0 145.0

Long Term Debt Maturities 0.0 0.0

Net Cash Interest 119.0 148.0
Lease 188.0 193.0

Financial Risk Ratios

Debt to equity ratio 32.31 14.06
Debt ratio 0.97 0.93
Interest coverage ratio 29 28
DSCR 12.03 10.42
Dividend Payout Ratios

Total Dividends Paid $1,508 $1,529 $1,591 $1,614

Net Income 2441 2024 2400 2367
Dividend Payout Ratio 62% 76% 66% 68%
Retention Ratio 38% 24% 34% 32%

Sustainable Growth 409.0% 391.9% 213.0%

ction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

888 500 500 500 500 500

1,264 1,567 1,398 1,712 1,555 1,882
1,673 1,536 1,825 1,700 2,002 1,893
513 534 559 586 616 648
4,338 4,137 4,281 4,498 4,673 4,923

3,716 3,713 3,704 3,689 3,670 3,646

3,824 3,824 3,824 3,824 3,824 3,824
2,894 3,155 3,433 3,729 4,043 4,378
291 291 291 291 291 291
857 857 857 857 857 857
15,920 15,977 16,391 16,888 17,358 17,919

258 258 258 258 258 258

9 413 896 498 130 100
1,393 1,197 1,681 1,173 1,992 1,174
403 427 447 469 493 519
2,341 2,067 2,164 2,270 2,385 2,509
4,404 4,361 5,447 4,668 5,257 4,559

743 842 900 910 869 771

358 533 716 909 1,113 1,327
1,101 1,375 1,616 1,820 1,982 2,098

15,920 15,977 16,391 16,888 17,358 17,919 -

- - - - - -
- - - -
Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

5.6% 3.1% 3.1% 3.0% 2.9% 2.8%

7.9% 9.8% 8.5% 10.1% 9.0% 10.5%
10.5% 9.6% 11.1% 10.1% 11.5% 10.6%
3.2% 3.3% 3.4% 3.5% 3.5% 3.6%
27.2% 25.9% 26.1% 26.6% 26.9% 27.5%
 23.3% 23.2% 22.6% 21.8% 21.1% 20.3%
24.0% 23.9% 23.3% 22.6% 22.0% 21.3%
18.2% 19.7% 20.9% 22.1% 23.3% 24.4%
1.8% 1.8% 1.8% 1.7% 1.7% 1.6%
5.4% 5.4% 5.2% 5.1% 4.9% 4.8%
100.0% 100.0% 100.0% 100.0% 100.0% 100.0%

1.6% 1.6% 1.6% 1.5% 1.5% 1.4%

0.1% 2.6% 5.5% 2.9% 0.7% 0.6%
8.8% 7.5% 10.3% 6.9% 11.5% 6.6%
2.5% 2.7% 2.7% 2.8% 2.8% 2.9%
14.7% 12.9% 13.2% 13.4% 13.7% 14.0%
27.7% 27.3% 33.2% 27.6% 30.3% 25.4%

46.1% 39.4% 33.0% 29.0% 27.5% 26.1%

2.7% 2.7% 2.6% 2.5% 2.5% 2.4%
16.7% 16.6% 16.2% 15.7% 15.3% 14.8%
93.1% 91.4% 90.1% 89.2% 88.6% 88.3%

0.0% 0.0% 0.0% 0.0% 0.0% 0.0%

9.2% 0.0% 0.0% 0.0% 0.0% 0.0%
18.6% 0.0% 0.0% 0.0% 0.0% 0.0%
148.9% 0.0% 0.0% 0.0% 0.0% 0.0%
-27.3% 0.0% 0.0% 0.0% 0.0% 0.0%
0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
-144.8% 0.0% 0.0% 0.0% 0.0% 0.0%
4.7% 5.3% 5.5% 5.4% 5.0% 4.3%
2.2% 3.3% 4.4% 5.4% 6.4% 7.4%
6.9% 8.6% 9.9% 10.8% 11.4% 11.7%
100.0% 100.0% 100.0% 100.0% 100.0% 100.0%

.99x .95x .79x .96x .89x 1.08x

.49x .47x .35x .47x .39x .52x
.20x .11x .09x .11x .10x .11x

16,471 17,222 18,036 18,917 19,872 20,908

6,454 6,889 7,214 7,567 7,949 8,363
6,727 6,752 7,503 7,442 8,252 8,253
 12.18x 12.17x 12.17x 12.17x 12.17x 12.17x
4.20x 4.29x 4.29x 4.29x 4.29x 4.29x
5.12x 5.21x 5.21x 5.21x 5.21x 5.21x

30.0 30.0 30.0 30.0 30.0 30.0

86.9 85.0 85.0 85.0 85.0 85.0
71.4 70.0 70.0 70.0 70.0 70.0
45.51 45.00 45.00 45.00 45.00 45.00

1.06 1.08 1.11 1.14 1.16 1.19

4.41 4.64 4.86 5.12 5.40 5.72
38.30 21.73 20.71 20.90 22.33 25.49

60.8% 60.0% 60.0% 60.0% 60.0% 60.0%

23.6% 23.0% 23.0% 23.0% 23.0% 23.0%
16.4% 15.9% 15.9% 16.0% 16.0% 16.1%

25.1% 24.8% 25.6% 26.1% 26.7% 27.3%

344.8% 235.3% 204.6% 187.3% 178.3% 175.0%
626.7% 345.4% 330.2% 334.2% 358.1% 409.2%

17.4% 16.9% 17.0% 17.0% 17.1% 17.1%

106.4% 108.0% 111.4% 113.7% 116.1% 118.5%
1865.8% 1288.2% 1082.1% 968.6% 900.8% 864.6%
344.8% 235.3% 204.6% 187.3% 178.3% 175.0%

3,885.0 3,961.1 4,148.2 4,350.9 4,570.6 4,808.8

539.0 602.6 633.1 665.1 698.6 733.9
164.0 157.6 153.4 150.7 146.0 147.2

267.0
153.0
167.0

6.90 5.07 4.06 3.11 2.60 2.40

0.87 0.84 0.80 0.76 0.72 0.71
27 29 31 33 36 38
6.62
$1,654 $1,643 $1,726 $1,814 $1,912 $2,014
2695 2738.261236 2876.119579 3024.14611 3185.852239 3356.563801
61% 60% 60% 60% 60% 60%
39% 40% 40% 40% 40% 40%

133.2% 94.1% 81.8% 74.9% 71.3% 70.0%

Colgate-Palmolive Company For step by step instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Consolidated Cash Flows

Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25
Operating Activities
Net income including noncontrolling interests $ 2,558 $ 2,527 $ 2,860 $ 2,913 $ 3,060 $ 3,217 $ 3,389 $ 3,571
Adjustments to reconcile net income
to net cash provided by operations:
Depreciation and amortization 511 519 539 603 633 665 699 734
Restructuring and termination benefits, net of cash -7 18 -71 0 0 0 0 0
Stock-based compensation expense 109 100 107 0 0 0 0 0
Loss on early extinguishment of debt 23 0 0 0 0 0
Charge for U.S. tax reform 80 0 0 0 0 0
Deferred income taxes 27 17 -120 0 0 0 0 0
Voluntary benefit plan contributions (67) (113) 0 0 0 0 0
Cash effects of changes in:
Receivables (79) 19 138 (303) 169 (314) 157 (327)
Inventories (58) (77) (251) 137 (289) 125 (303) 110
Accounts payable, accrued income tax & other accruals 18 36 520 (447) 603 (381) 957 (668)
Other non-current assets and liabilities (36) 87 (26) (21) (25) (27) (30) (32)
Net cash provided by operations 0 3,056 3,133 3,719 2,883 4,150 3,285 4,870 3,387

Investing Activities  
Capital expenditures (436) (335) (410) (861) (902) (946) (994) (1,045)
Purchases of marketable securities and investments (169) (184) (143)
Proceeds from sale of marketable securities and investments 156 131 124
Payment for acquisitions, net of cash acquired (728) (1,711) (353)
Other 7 3
Net cash used in investing activities 0 (1,170) (2,099) (779) (861) (902) (946) (994) (1,045)

Cash Flow for Financing Activities 0 1,886 1,034 2,940 2,021 3,249 2,339 3,876 2,341

Financing Activities  
Revolver 860 (17) 1,570 (151) 1,243
Short-term borrowing/(repayment) less than 90 days - net 546 294 497
Principal payments on debt (725) (1,441) (1,061) (631) (413) (896) (498) (130)
Proceeds from issuance of debt 2,595
Dividends paid (1,591) (1,614) (1,654) (1,643) (1,726) (1,814) (1,912) (2,014)
Purchases of treasury shares (1,238) (1,202) (1,476) (1,500) (1,597) (1,703) (1,819) (1,945)
Proceeds from exercise of stock options and excess tax benefits 329 498 874 504.0 504.0 504.0 504.0 504.0
Purchases of non-controlling interests in subsidiaries -99
Net cash used in financing activities 0 (2,679) (870) (2,919) (2,409) (3,249) (2,339) (3,876) (2,341)

Effect of exchange rate changes on Cash and cash equivalents (16) (7) (16) 0 0 0 0 0
Net increase (decrease) in Cash and cash equival 0 (809) 157 5 (388) 0 0 0 0

Cash and cash equivalents at beginning of year 1,535 726 883 888 500 500 500 500
Cash and cash equivalents at end of year $ - $ 726 $ 883 $ 888 $ 500 $ 500 $ 500 $ 500 $ 500

Supplemental Cash Flow Information

 
Income taxes paid $ 847 $ 803 $ 845
Interest paid 194 185 188
Colgate-Palmolive Company For step by step instruction, please vis

Consolidated Working Capital

Dec-16 Dec-17 Dec-18
Net Sales 15,195 15,454 15,544
Cost of Sales 6,072 6,174 6,313

Working Capital Balances

Receivables 1,411 1,480 1,400
Inventories 1,171 1,221 1,250
Other current assets 441 403 417
Total Non Cash Current Assets 3,023 3,104 3,067

Accounts payable 1,124 1,212 1,222

Accrued income taxes 441 354 411
Other accruals 1,727 1,831 1,696
Total Non-Debt Current Liabilities 3,292 3,397 3,329

Net Working Capital/ (Deficit) (269) (293) (262)

(Increase)/ Decrease in Working Capital 24 (31)

Purchases 6,224 6,342

Cash Flow from Individual line items

Receivables 80
Inventories (29)
Other current assets (14)
Accounts payable 10
Accrued income taxes 57
Other accruals (135)
(Incease)/ Decrease in Working Capital (31)

Check
p by step instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-19 Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

15,693 16,471 17,222 18,036 18,917 19,872 20,908
6,368 6,454 6,889 7,214 7,567 7,949 8,363

1,440 1,264 1,567 1,398 1,712 1,555 1,882

1,400 1,673 1,536 1,825 1,700 2,002 1,893
456 513 534 559 586 616 648
3,296 3,450 3,637 3,781 3,998 4,173 4,423

1,237 1,393 1,197 1,681 1,173 1,992 1,174

370 403 427 447 469 493 519
1,917 2,341 2,067 2,164 2,270 2,385 2,509
3,524 4,137 3,690 4,293 3,912 4,869 4,201

(228) (687) (54) (512) 86 (696) 222

(34) 459 (633) 458 (597) 782 (918)

6,518 6,727 6,752 7,503 7,442 8,252 8,253

(40) 176 (303) 169 (314) 157 (327)

(150) (273) 137 (289) 125 (303) 110
(39) (57) (21) (25) (27) (30) (32)
15 156 (196) 485 (508) 819 (818)
(41) 33 24 20 22 24 26
221 424 (274) 98 106 115 124
(34) 459 (633) 458 (597) 782 (918)
Number of 365
Colgate-Palmolive Company For step by step instruction, please visit - https://w

Consolidated Depreciation and Capex

Dec-16 Dec-17 Dec-18 Dec-19 Dec-20
Net Sales 15,195 15,454 15,544 15,693 16,471
Capital Expenditures 436 335 410
Capital Expenditures as % of Net Sales 2.8% 2.1% 2.5%

Beginning Net PP&E

Capital Expenditures
(Depreciation Expense)
Ending Net PP&E 3,840 4,072 3,881 3,750 3,716

PPE (Break-up) Dec-19 Dec-20

Land 153.0 166.0
Building Improvements 1,600.0 1,623.0
Machinery and equipment 5,309.0 5,409.0
Other Equipment 1,518.0 1,553.0
Total 8,580.0 8,751.0

Total Capex
Breakup
Land
Building Improvements
Machinery and equipment
Other Equipment

Building Improvements - Straight Line Method

Useful Life 40.0 Years
Building Improvement 1623.0
Depreciation Expense (existing)

Capex Useful Life

2021 $95.8 40.0 Years
2022 100.4 40.0
2023 105.3 40.0
2024 110.6 40.0
2025 116.3 40.0

Depreciation (Building Improvements)

Machinery & Equipments
Useful Life 15.0 Years
Machinery & Equipments 5409.0
Depreciation Expense (existing)

Capex Useful Life

2021 $319.4 15.0 Years
2022 334.4 15.0
2023 350.8 15.0
2024 368.5 15.0
2025 387.7 15.0

Total Depreciation (Machinery & Equipments)

Other Equipment
Useful Life 15.0 Years
Other Equipment 1553.0
Depreciation Expense (existing)

Capex Useful Life

2014 $91.7 15.0 Years
2015 96.0 15.0
2016 100.7 15.0
2017 105.8 15.0
2018 111.3 15.0

Total Depreciation (Other Equipment)

Total Depreciation Expense

please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

17,222 18,036 18,917 19,872 20,908
517 541 568 596 627
3.0% 3.0% 3.0% 3.0% 3.0%

3,716.0 3,713.1 3,704.0 3,689.5 3,670.0

516.7 541.1 567.5 596.2 627.2
(519.6) (550.1) (582.1) (615.6) (650.9)
3,713 3,704 3,689 3,670 3,646

Remaining useful life Proportion of Assets

NA 1.9%
40 18.5%
15 61.8%
15 17.7%

9.8 10.3 10.8 11.3 11.9

95.8 100.4 105.3 110.6 116.3
319.4 334.4 350.8 368.5 387.7
91.7 96.0 100.7 105.8 111.3

40.58 40.58 40.58 40.58 40.58

1.2 2.4 2.4 2.4 2.4

1.3 2.5 2.5 2.5
1.3 2.6 2.6
1.4 2.8
1.5

41.8 44.2 46.8 49.5 52.3

360.60 360.60 360.60 360.60 360.60

10.6 21.3 21.3 21.3 21.3

11.1 22.3 22.3 22.3
11.7 23.4 23.4
12.3 24.6
12.9

371.2 393.0 415.9 439.9 465.1

103.5 103.5 103.5 103.5 103.5

3.1 6.1 6.1 6.1 6.1

3.2 6.4 6.4 6.4
3.4 6.7 6.7
3.5 7.1
3.7

106.6 112.8 119.4 126.3 133.5

519.6 550.1 582.1 615.6 650.9

Colgate-Palmolive Company For step by step instruction, please visit - https://w

Consolidated Amortization & Intangible

Dec-16 Dec-17 Dec-18 Dec-19 Dec-20
Net Sales 15,195 15,454 15,544 15,693 16,471
Additions to Intangibles 347.0 1089.0 336.0
Additions to Intangibles as % of Net Sales 2.2% 6.9% 2.0%

Beginning Net Intangibles

Additions to Intangibles
(Amortization Expense) (62.0) (88.0)
Ending Net Intangibles 2,894.0
tion, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

17,222 18,036 18,917 19,872 20,908
344.4 360.7 378.3 397.4 418.2
2.0% 2.0% 2.0% 2.0% 2.0%

2,894.0 3,155.4 3,433.2 3,728.5 4,042.9

344.4 360.7 378.3 397.4 418.2
(83.0) (83.0) (83.0) (83.0) (83.0)
3,155.4 3,433.2 3,728.5 4,042.9 4,378.1
Colgate-Palmolive Company For step by step instruction, please visit - htt

Other Long Term Asset Liability Schedule

Dec-16 Dec-17 Dec-18 Dec-19

Assets
Goodwill, net 2,107.0 2,218.0 2,530.0 3,508.0
Deferred income taxes 301.0 188.0 152.0 177.0
Other assets 224.0 218.0 168.0 753.0

Liability
Deferred income taxes 246.0 204.0 235.0 507.0
Other liabilities 2,035.0 2,255.0 2,034.0 2,598.0
nstruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

3,824.0 3,824.0 3,824.0 3,824.0 3,824.0 3,824.0

291.0 291.0 291.0 291.0 291.0 291.0
857.0 857.0 857.0 857.0 857.0 857.0

426.0 426.0 426.0 426.0 426.0 426.0

2,655.0 2,655.0 2,655.0 2,655.0 2,655.0 2,655.0
Colgate-Palmolive Company For step by step instruction, please vis

Consolidated Shareholder's Equity

Dec-16 Dec-17 Dec-18
Beginning Equity Balance
Net Income
Issuance/ (Repurchase) of Equity
Dividends Paid
Option Proceeds
Ending Equity Balance (243) (60) (102)

Share Repurchase Assumptions

Current Year EPS 2.72 2.28 2.75
Assumed Current Year EPS Multiple 24.0x
Implied Share Price 65.9
Shares Repurchased (in millions) 18.79
Amount Repurchased ( $ outgo) 1,238

New Shares from Exercised Options

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

Restricted Stock Units (RSUs) 0.96

Dividend Assumptions
Total Dividends Paid 1,591
Net Income 2,400
Dividend Payout Ratio 66.3%

`
y step instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-19 Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

743 842 900 910 869
2,738 2,876 3,024 3,186 3,357
(1,500) (1,597) (1,703) (1,819) (1,945)
(1,643) (1,726) (1,814) (1,912) (2,014)
504 504 504 504 504
117 743 842 900 910 869 771

2.75 3.14 3.24 3.45 3.68 3.93 4.20

25.4x 25.1x 25.0x 25.0x 25.0x 25.0x 25.0x
69.8 78.9 81 86 92 98 105
17.22 18.70 18.50 18.50 18.50 18.50 18.50
1,202 1,476 1,500 1,597 1,703 1,819 1,945

8.15 13.0 7.0 7.0 7.0 7.0 7.0

61.1 67.1 72.0 72.0 72.0 72.0 72.0
498 874 504.0 504.0 504.0 504.0 504.0

0.86 0.88 2.88 2.88 2.88 2.88 2.88

1,614 1,654 1,643 1,726 1,814 1,912 2,014

2,367 2,695 2,738 2,876 3,024 3,186 3,357
68.2% 61.4% 60.0% 60.0% 60.0% 60.0% 60.0%
Colgate-Palmolive Company For step by step instruction, please visit - http

Consolidated Shares Outstanding

Dec-16 Dec-17 Dec-18 Dec-19
Beginning Balance - Basic (actual) 874.7 862.0
Shares Issued (actual realization of options) 6.04 8.15
Shares Repurchased (18.8) (17.2)
Ending Balance - Basic (actual) 874.7 862.0 852.9

Basic Weighted Average Shares 870.6 859.1

Restricted Stock Units 2.4 2.0
Diluted Weighted Average Shares 873.0 861.1
p instruction, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-20 Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

852.9 847.2 835.7 824.2 812.7 801.2
13.02 7.00 7.00 7.00 7.00 7.00
(18.7) (18.5) (18.5) (18.5) (18.5) (18.5)
847.2 835.7 824.2 812.7 801.2 789.7

856.8 841.4 829.9 818.4 806.9 795.4

2.5 2.9 2.9 2.9 2.9 2.9
859.3 844.3 832.8 821.3 809.8 798.3
Colgate-Palmolive Company For step by step instruction, please visit - http

Debt Schedule
Dec-16 Dec-17 Dec-18 Dec-19 Dec-20
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 -

Debt (Long Term + Current Debt)

Beginning Balance
Issuance
(Repayment/ Amortization)
Ending Balance 6,520.0 6,566.0 6,354.0 7,587.0 7,343.0

Long Term Debt

Current Portion of Long Term Debt

Revolving Credit Facility Average Balance

Interest Rate
Interest Expense

Long Term Debt Average Balance 6,460.0 6,970.5 7,465.0

Interest Rate 2.2% 2.1% 2.2%
Interest Expense 143.0 145.0 164.0

Total Interest Expense

Cash Balances Average Balance
Interest Rate
Interest Income
tion, please visit - https://www.wallstreetmojo.com/financial-modeling-in-excel/

Dec-21 Dec-22 Dec-23 Dec-24 Dec-25

2,021.4 3,248.7 2,339.0 3,876.2 2,341.4
(1,499.9) (1,597.2) (1,702.9) (1,819.5) (1,944.6)
(1,643.0) (1,725.7) (1,814.5) (1,911.5) (2,013.9)
504.0 504.0 504.0 504.0 504.0
888.0 500.0 500.0 500.0 500.0
(500.0) (500.0) (500.0) (500.0) (500.0)
(229.5) 429.8 (674.4) 649.2 (1,113.1)
- - - - -
(631.0) (413.0) (896.0) (498.0) (130.0)
(860.5) 16.8 (1,570.4) 151.2 (1,243.1)

- 860.5 843.6 2,414.0 2,262.8

860.5 (16.8) 1,570.4 (151.2) 1,243.1
860.5 843.6 2,414.0 2,262.8 3,506.0

7,343.0 6,712.0 6,299.0 5,403.0 4,905.0

(631.0) (413.0) (896.0) (498.0) (130.0) (100.0)

6,712.0 6,299.0 5,403.0 4,905.0 4,775.0

6,299.0 5,403.0 4,905.0 4,775.0 4,675.0

413.0 896.0 498.0 130.0 100.0

430.2 852.1 1,628.8 2,338.4 2,884.4

1.5% 1.5% 1.5% 1.5% 1.5%
6.5 12.8 24.4 35.1 43.3

7,027.5 6,505.5 5,851.0 5,154.0 4,840.0

2.2% 2.2% 2.2% 2.2% 2.2%
154.6 143.1 128.7 113.4 106.5

161.06 155.90 153.15 148.46 149.75

694.0 500.0 500.0 500.0 500.0
0.50% 0.50% 0.50% 0.50% 0.50%
3.47 2.50 2.50 2.50 2.50