A602 financial analysis template (colgate-Palmolive)

I only need help with Common size and Financial Ratio.

Note: Use care not to delete, hide or reorder workbook tabs (worksheets) in this workbook. . . . . Week 3 Analysis Adjustments Assignment Grading Rubric The facilitator will grade this assignment, assigning up to 100 points for it, as follows: Financial statements analysis adjustments are properly identified from the Annual Report on Form 10-K and the supporting analysis, computations, and presentation is complete, correct, concise, and clear: Type of Analysis Adjustment Maximum Operating Lease Commitments 30 - Oper Lease Adjust 5 and 6 Financial Elements of Pension Costs 20 - Pension Cost Analysis Adjust Inventory Cost-Flow Assumption 30 - Invent Cost-Flow Analysis Adj Noncancellable Commitments 10 - Noncancel Commit Analysis Adj - - Guar Uncons Debt Analysis Adj Guaranteed Unconsolidated Debt Capitalized Interest 10 Earned Worksheet Tab Label: - Capitalized Interest Analysis Adj Impairment or Restructuring Charges - - Impair, Restruct Analysis Adj Early Retirement, Restructuring Debt - - Debt Restruct Analysis Adj Gains on Investments or PP&E - - Sales Invest, PP&E Analysis Adj Total points 100 - . . . . . . . . . . . . . . . . . . . . . . INPUT US dollar amounts IN MILLIONS 2008 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Assets Current assets Cash and cash equivalents $ Investment securities AFS 555 $ 555 12 1,639 AR, gross Less: Allowance for bad debts 12 1,639 (47) (47) Note this is a DEBIT balance AR, net 1,592 Inventories, at LOCOM using FIFO and LIF 1,197 1,197 97 97 257 257 DIT assets-current Other current assets Total current assets 3,710 - PP&E, at cost 5,937 - - - - - - - - - - 1,196 Less: Accumulated depreciation of PP&E - - - 1,592 3,710 7,133 (2,818) (2,818) PP&E, net 3,119 Goodwill, net impairment write-downs 2,152 2,152 834 834 - - 164 164 Other intangibles, net amort., impairment DIT assets-noncurrent Other noncurrent assets Total assets $ 9,979 1,196 1,196 - - - - - - - - - - - - - - 4,315 $ 11,175 INPUT US dollar amounts IN MILLIONS 2008 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Liabilities and stockholders' equity Current liabilities AP $ Accrued liabilities 1,061 Note this is a CREDIT balance $ 1,061 1,421 1,421 272 272 DIT liabilities-current - - Capital lease obligation-current portion - Income taxes payable L-T debt-current portion 149 149 91 107 Other current liabilities 107 - Notes and loans payable-current 91 - Total current liabilities 2,952 Notes and loans payable-noncurrent 3,578 Capital lease obligation-noncurrent 7 Bonds, net discount [or, plus premium] 149 - - - - - - - 3,101 3,578 - DIT liabilities-noncurrent 82 1,047 1,054 - Note this is a CREDIT balance 82 Other noncurrent liabilities 1,316 Total noncurrent liabilities 4,983 1,047 - - - - - - - 6,030 Total liabilities 7,935 1,196 - - - - - - - 9,131 Preferred (or preference) stock 181 Unearned stock-based compensation (9,697) (187) Retained earnings 11,760 Noncontrolling interest in consolid subsid (187) Note this is a CREDIT balance 11,760 121 Total stockholders' equity 121 (2,477) Accum other comprehensive income (loss) CHECK: A - (L + E) (S/B -0-) 1,610 (9,697) Less: Treasury stock 733 1,610 Additional paid-in capital (APIC) - 733 Common stock (par or stated value) 181 - Premium on preferred stock Total liabilities and stockholders' equity 1,316 (2,477) 2,044 $ - - - - - - - - 2,044 9,979 1,196 - - - - - - - $ 11,175 - - - - - - - - - - Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges INPUT US dollar amounts IN MILLIONS 2009 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Assets Current assets Cash and cash equivalents Investment securities AFS AR, gross Less: Allowance for bad debts $ 600 $ 600 41 41 1,678 1,678 (52) (52) AR, net 1,626 - Inventories, at LOCOM using FIFO and LIF 1,209 1,209 DIT assets-current 105 105 Other current assets 229 229 Total current assets 3,810 - PP&E, at cost 6,700 - - - - - - - - - - - 1,161 Less: Accumulated depreciation of PP&E - - - 1,626 3,810 7,861 (3,184) (3,184) PP&E, net 3,516 Goodwill, net impairment write-downs 2,302 2,302 821 821 - - 685 685 Other intangibles, net amort., impairment DIT assets-noncurrent Other noncurrent assets Total assets $ 11,134 1,161 1,161 - - - - - - - - - - - - - 4,677 - $ 12,295 INPUT US dollar amounts IN MILLIONS 2009 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Liabilities and stockholders' equity Current liabilities AP Accrued liabilities $ 1,172 $ 1,172 1,679 1,679 387 387 DIT liabilities-current - - Capital lease obligation-current portion - Income taxes payable L-T debt-current portion Notes and loans payable-current Other current liabilities 162 162 326 326 35 35 - - Total current liabilities 3,599 Notes and loans payable-noncurrent 162 - - - - - - - 2,821 3,761 2,821 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] - - 82 82 Other noncurrent liabilities 1,375 1,375 Total noncurrent liabilities 4,278 999 - - - - - - - 5,277 Total liabilities 7,877 1,161 - - - - - - - 9,038 DIT liabilities-noncurrent Preferred (or preference) stock Premium on preferred stock Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) 999 999 169 169 - - 733 733 1,764 1,764 (10,478) (10,478) (133) (133) 13,157 13,157 141 141 (2,096) (2,096) 3,257 - - - - - - - - $ 11,134 1,161 - - - - - - - $ 12,295 - - - - - - - - - Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 3,257 - INPUT US dollar amounts IN MILLIONS 2010 Audited balance Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Assets Current assets Cash and cash equivalents Investment securities AFS AR, gross Less: Allowance for bad debts $ 490 $ 490 74 74 1,663 1,663 (53) (53) AR, net 1,610 - Inventories, at LOCOM using FIFO and LIF 1,222 1,222 DIT assets-current 117 117 Other current assets 217 217 Total current assets 3,730 - PP&E, at cost 7,160 - - - - - - - - - - - 1,115 Less: Accumulated depreciation of PP&E - - - 1,610 3,730 8,275 (3,467) (3,467) PP&E, net 3,693 Goodwill, net impairment write-downs 2,362 2,362 831 831 84 84 472 472 Other intangibles, net amort., impairment DIT assets-noncurrent Other noncurrent assets Total assets $ 11,172 1,115 1,115 - - - - - - - - - - - - - 4,808 - $ 12,287 INPUT US dollar amounts IN MILLIONS 2010 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Liabilities and stockholders' equity Current liabilities AP Accrued liabilities $ 1,165 $ 1,165 1,682 1,682 272 272 DIT liabilities-current - - Capital lease obligation-current portion - Income taxes payable L-T debt-current portion Notes and loans payable-current Other current liabilities 165 165 561 561 48 48 - - Total current liabilities 3,728 Notes and loans payable-noncurrent 165 - - - - - - - 2,815 3,893 2,815 Capital lease obligation-noncurrent - Bonds, net discount [or, plus premium] - - 108 108 Other noncurrent liabilities 1,704 1,704 Total noncurrent liabilities 4,627 950 - - - - - - - 5,577 Total liabilities 8,355 1,115 - - - - - - - 9,470 DIT liabilities-noncurrent 950 950 Preferred (or preference) stock 0 0 Premium on preferred stock 0 0 733 733 1,132 1,132 (11,305) (11,305) (99) (99) 14,329 14,329 142 142 (2,115) (2,115) Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) 2,817 - - - - - - - - $ 11,172 1,115 - - - - - - - $ 12,287 (0) - - - - - - - - Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2,817 (0) INPUT US dollar amounts IN MILLIONS 2011 Audited balance Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Assets Current assets Cash and cash equivalents Investment securities AFS AR, gross Less: Allowance for bad debts $ 878 $ 878 72 72 1,724 1,724 (49) (49) AR, net 1,675 - Inventories, at LOCOM using FIFO and LIF 1,327 1,327 DIT assets-current 204 204 Other current assets 246 246 3,668 Goodwill, net impairment write-downs 2,494 2,494 Other intangibles, net amort., impairment 1,504 1,504 DIT assets-noncurrent 115 115 Other noncurrent assets 541 541 $ 12,724 - - - - - - - - 4,402 PP&E, net Total assets - - 1,675 (3,656) - - - (3,656) - - - 7,324 - - - 7,324 - - - PP&E, at cost - - - 4,402 - - - Total current assets Less: Accumulated depreciation of PP&E - - 3,668 - $ 12,724 INPUT US dollar amounts IN MILLIONS 2011 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Liabilities and stockholders' equity Current liabilities AP Accrued liabilities $ 1,244 $ 1,244 1,700 1,700 392 392 DIT liabilities-current - - Capital lease obligation-current portion - - 346 346 34 34 - - Income taxes payable L-T debt-current portion Notes and loans payable-current Other current liabilities Total current liabilities 3,716 Notes and loans payable-noncurrent 4,430 4,430 Capital lease obligation-noncurrent - - Bonds, net discount [or, plus premium] - - 252 252 Other noncurrent liabilities 1,785 1,785 Total noncurrent liabilities 6,467 - - - - - - - - 6,467 10,183 - - - - - - - - 10,183 DIT liabilities-noncurrent Total liabilities - - - - - - - - 3,716 Preferred (or preference) stock 0 0 Premium on preferred stock 0 0 733 733 1,336 1,336 (12,808) (12,808) (60) (60) 15,649 15,649 166 166 (2,475) (2,475) Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) 2,541 - - - - - - - - $ 12,724 - - - - - - - - $ 12,724 (0) - - - - - - - - Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2,541 (0) INPUT US dollar amounts IN MILLIONS 2012 Audited balance Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Assets Current assets Cash and cash equivalents Investment securities AFS AR, gross Less: Allowance for bad debts $ 884 $ 884 116 116 1,729 1,729 (61) (61) AR, net 1,668 - Inventories, at LOCOM using FIFO and LIF 1,365 1,365 DIT assets-current 275 275 Other current assets 248 248 3,842 Goodwill, net impairment write-downs 2,500 2,500 Other intangibles, net amort., impairment 1,499 1,499 92 92 905 905 Other noncurrent assets Total assets $ 13,394 - - - - - - - - 4,556 PP&E, net DIT assets-noncurrent - - 1,668 (3,979) - - - (3,979) - - - 7,821 - - - 7,821 - - - PP&E, at cost - - - 4,556 - - - Total current assets Less: Accumulated depreciation of PP&E - - 3,842 - $ 13,394 INPUT US dollar amounts IN MILLIONS 2012 Audited Analysis Adjustment (See explanations below) balance (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance Liabilities and stockholders' equity Current liabilities AP Accrued liabilities $ 1,290 $ 1,290 1,888 1,888 254 254 DIT liabilities-current - - Capital lease obligation-current portion - - 250 250 54 54 - - Income taxes payable L-T debt-current portion Notes and loans payable-current Other current liabilities Total current liabilities 3,736 Notes and loans payable-noncurrent 4,926 4,926 Capital lease obligation-noncurrent - - Bonds, net discount [or, plus premium] - - 293 293 Other noncurrent liabilities 2,049 2,049 Total noncurrent liabilities 7,268 - - - - - - - - 7,268 11,004 - - - - - - - - 11,004 DIT liabilities-noncurrent Total liabilities - - - - - - - - 3,736 Preferred (or preference) stock 0 0 Premium on preferred stock 0 0 733 733 1,551 1,551 (14,386) (14,386) (41) (41) 16,953 16,953 201 201 (2,621) (2,621) Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) 2,390 - - - - - - - - $ 13,394 - - - - - - - - $ 13,394 (0) - - - - - - - - Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges 2,390 (0) INPUT US dollar amounts IN MILLIONS Adjusted balances (used for financial analysis) 2008 2009 2010 2011 2012 Assets Current assets Cash and cash equivalents Investment securities AFS $ 555 $ 600 $ 490 $ 878 $ 884 12 41 74 72 116 1,639 1,678 1,663 1,724 1,729 (47) (52) (53) (49) (61) AR, net 1,592 1,626 1,610 1,675 1,668 Inventories, at LOCOM using FIFO and LIF 1,197 1,209 1,222 1,327 1,365 97 105 117 204 275 257 229 217 246 248 Total current assets 3,710 3,810 3,730 4,402 4,556 PP&E, at cost 7,133 7,861 8,275 7,324 7,821 (2,818) (3,184) (3,467) (3,656) (3,979) PP&E, net 4,315 4,677 4,808 3,668 3,842 Goodwill, net impairment write-downs 2,152 2,302 2,362 2,494 2,500 834 821 831 1,504 1,499 - - 84 115 92 164 685 472 541 905 AR, gross Less: Allowance for bad debts DIT assets-current Other current assets Less: Accumulated depreciation of PP&E Other intangibles, net amort., impairment DIT assets-noncurrent Other noncurrent assets Total assets $ 11,175 $ 12,295 $ 12,287 $ 12,724 $ 13,394 INPUT US dollar amounts IN MILLIONS Adjusted balances (used for financial analysis) 2008 2009 2010 2011 2012 Liabilities and stockholders' equity Current liabilities AP 1,061 $ 1,172 $ 1,165 $ 1,244 $ 1,290 1,421 1,679 1,682 1,700 1,888 272 387 272 392 254 - - - - - 149 162 165 - - 91 326 561 346 250 107 35 48 34 54 - - - - - Total current liabilities 3,101 3,761 3,893 3,716 3,736 Notes and loans payable-noncurrent 3,578 2,821 2,815 4,430 4,926 Capital lease obligation-noncurrent 1,054 999 950 - - - - - - - 82 82 108 252 293 Other noncurrent liabilities 1,316 1,375 1,704 1,785 2,049 Total noncurrent liabilities 6,030 5,277 5,577 6,467 7,268 Total liabilities 9,131 181 9,038 169 9,470 0 10,183 0 11,004 0 - - 0 0 0 733 733 733 733 733 1,610 1,764 1,132 1,336 1,551 (9,697) (10,478) (11,305) (12,808) (14,386) (187) (133) (99) (60) (41) 11,760 13,157 14,329 15,649 16,953 121 141 142 166 201 (2,477) (2,096) (2,115) (2,475) (2,621) 2,044 3,257 2,817 2,541 2,390 11,175 12,295 12,287 12,724 13,394 - - (0) (0) (0) Accrued liabilities Income taxes payable DIT liabilities-current Capital lease obligation-current portion L-T debt-current portion Notes and loans payable-current Other current liabilities Bonds, net discount [or, plus premium] DIT liabilities-noncurrent Preferred (or preference) stock Premium on preferred stock Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity CHECK: A - (L + E) (S/B -0-) $ Check this condition for after posting each analysis adjustment, to identify potential errors Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see income statement for related adjustments) (B) Recognize obligations under noncancellable, fixed-price vendor supply agreements (C) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize guaranteed debt of unconsolidated affiliated entities and related assets (D) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust LIFO-basis inventory for "LIFO reserve" (NO related income statement adjustment needed) (F) See income statement for adjustments to reclassify financial elements of DB pension and OPEB expense (G) See income statement for adjustment to recognize capitalized interest as financial expense (H) See income statement for adjustment to recast or eliminate asset impairment and restructuring charges INPUT US dollar amounts IN MILLIONS 2008 Audited Analysis Adjustment (See explanations below) balance Sales, net returns and allowances (A) $ 15,330 COS (FIFO and LIFO cost-flow assumptions) 6,704 Gross profit 8,626 (B) (C) (D) (E) (G) (H) balance $ 15,330 Note this is a DEBIT balance - (F) Adjusted - - - - 6,704 - - - 8,626 Note this is a DEBIT balance Operating expenses SG&A expenses (excl D&A and advertising) 3,184 R&D expense 240 Advertising expense 1,650 D&A 348 Other operating expenses (income) (135) 3,049 240 Note this is a DEBIT balance 1,650 153 Total operating expenses 5,575 Operating income 3,051 79 427 153 (56) - - - - - - - 5,519 56 - - - - - - - 3,107 Note this is a DEBIT balance Other income (expense) Interest expense, net of interest income (96) (11) (107) Gain on sale of non-core product lines - - Gain (loss) on debt restruct/early extinguish - - Gain (loss) on disposal of invest sec's AFS - - Gain (loss) on disposal of PP&E - - Impairment loss on PP&E - - Impairment loss on goodwill - - Venuzuela hyperinflation transition charge - - 50 50 Restructuring gain (loss) Total other income (expense) (46) (11) - - - - - - - (57) 3,005 45 - - - - - - - 3,050 Taxes on income from continuing operations 968 14 Income from contin oper incl noncontrol int's 2,037 31 - - - - - - - 2,068 80 - - - - - - - - 80 1,957 31 - - - - - - - $ Income from cont. oper's before taxes Less: Income attrib to noncontrol interests Income from continuing operations Gain (loss) on discont'd oper's, net of tax 1,988 - Disregard 1,957 Disregard - Disregard $ 1,957 Disregard $ 3,005 45 - - - - - - - $ (96) (11) - - - - - - - 3,101 56 - - - - - - - $ Income before extraordinary items Extraordinary gain (loss), net of tax Net income 982 Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes $ 3,050 (107) 3,157 Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) INPUT US dollar amounts IN MILLIONS 2009 Audited Analysis Adjustment (See explanations below) balance Sales, net returns and allowances (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance $ 15,327 $ 15,327 COS (FIFO and LIFO cost-flow assumptions) 6,319 - Gross profit 9,008 - 3,141 (173) - - - - 6,319 - - - 9,008 Operating expenses SG&A expenses (excl D&A and advertising) R&D expense 2,968 256 1,534 Advertising expense 256 1,534 D&A 351 Other operating expenses (income) 109 460 134 134 Total operating expenses 5,416 (64) - - - - - - - 5,352 Operating income 3,592 64 - - - - - - - 3,656 (77) (24) Other income (expense) Interest expense, net of interest income Gain on sale of non-core product lines (101) 5 5 Gain (loss) on debt restruct/early extinguish - Gain (loss) on disposal of invest sec's AFS - Gain (loss) on disposal of PP&E - Impairment loss on PP&E - Impairment loss on goodwill - Venuzuela hyperinflation transition charge - Restructuring gain (loss) 18 Total other income (expense) 18 (54) (24) - - - - - - - (78) Income from cont. oper's before taxes 3,538 40 - - - - - - - 3,578 Taxes on income from continuing operations 1,141 13 Income from contin oper incl noncontrol int's 2,397 27 - - - - - - - 2,424 106 - - - - - - - - 106 2,291 27 - - - - - - - $ Less: Income attrib to noncontrol interests Income from continuing operations Gain (loss) on discont'd oper's, net of tax 2,318 - Disregard 2,291 Disregard - Disregard $ 2,291 Disregard $ 3,538 40 - - - - - - - $ (77) (24) - - - - - - - 3,615 64 - - - - - - - $ Income before extraordinary items Extraordinary gain (loss), net of tax Net income 1,154 Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes $ 3,578 (101) 3,679 Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) (H) [NOT USED nonoperating gains and losses (NO balance sheet adjustment needed) INPUT US dollar amounts IN MILLIONS 2010 Audited Analysis Adjustment (See explanations below) balance Sales, net returns and allowances (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance $ 15,564 $ 15,564 COS (FIFO and LIFO cost-flow assumptions) 6,360 6,360 Gross profit 9,204 - 3,126 (185) - - - - - - - 9,204 Operating expenses SG&A expenses (excl D&A and advertising) R&D expense 2,941 256 1,656 Advertising expense D&A 256 1,656 376 Other operating expenses (income) 106 482 (6) (6) Total operating expenses 5,408 (79) - - - - - - - 5,329 Operating income 3,796 79 - - - - - - - 3,875 Interest expense, net of interest income (59) (23) Gain on sale of non-core product lines 50 50 Gain (loss) on debt restruct/early extinguish - - Gain (loss) on disposal of invest sec's AFS - - Gain (loss) on disposal of PP&E - - Impairment loss on PP&E - - Impairment loss on goodwill - - (271) (271) (86) (86) Other income (expense) Venuzuela hyperinflation transition charge Restructuring gain (loss) Total other income (expense) (82) (366) (23) - - - - - - - (389) Income from cont. oper's before taxes 3,430 56 - - - - - - - 3,486 Taxes on income from continuing operations 1,117 18 Income from contin oper incl noncontrol int's 2,313 38 - - - - - - - 2,351 110 - - - - - - - - 110 2,203 38 - - - - - - - $ Less: Income attrib to noncontrol interests Income from continuing operations Gain (loss) on discont'd oper's, net of tax 2,241 - Disregard 2,203 Disregard - Disregard $ 2,203 Disregard $ 3,430 56 - - - - - - - $ (59) (23) - - - - - - - 3,489 79 - - - - - - - $ Income before extraordinary items Extraordinary gain (loss), net of tax Net income 1,135 Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes $ 3,486 (82) 3,568 Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) INPUT US dollar amounts IN MILLIONS 2011 Audited balance Sales, net returns and allowances Analysis Adjustment (See explanations below) (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance $ 16,734 $ 16,734 COS (FIFO and LIFO cost-flow assumptions) 7,144 7,144 Gross profit 9,590 - 3,341 (187) - - - - - - - 9,590 Operating expenses SG&A expenses (excl D&A and advertising) R&D expense 3,154 262 1,734 Advertising expense D&A 262 1,734 421 Other operating expenses (income) 111 532 95 95 Total operating expenses 5,853 (76) - - - - - - - 5,777 Operating income 3,737 76 - - - - - - - 3,813 Interest expense, net of interest income (52) (22) Gain on sale of non-core product lines 207 Other income (expense) (74) 207 Gain (loss) on debt restruct/early extinguish - Gain (loss) on disposal of invest sec's AFS - Gain (loss) on disposal of PP&E - Impairment loss on PP&E - Impairment loss on goodwill - Venuzuela hyperinflation transition charge - Restructuring gain (loss) (103) Total other income (expense) (103) 52 (22) - - - - - - - 30 Income from cont. oper's before taxes 3,789 54 - - - - - - - 3,843 Taxes on income from continuing operations 1,235 17 Income from contin oper incl noncontrol int's 2,554 37 - - - - - - - 2,591 123 - - - - - - - - 123 2,431 37 - - - - - - - $ Less: Income attrib to noncontrol interests Income from continuing operations Gain (loss) on discont'd oper's, net of tax 1,252 (60) Income before extraordinary items 2,468 Disregard 2,371 Extraordinary gain (loss), net of tax Net income - Disregard (30) Disregard $ 2,341 $ 3,789 54 - - - - - - - $ (52) (22) - - - - - - - 3,841 76 - - - - - - - $ Disregard Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes $ 3,843 (74) 3,917 Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) INPUT US dollar amounts IN MILLIONS 2012 Audited Analysis Adjustment (See explanations below) balance Sales, net returns and allowances (A) (B) (C) (D) (E) (F) Adjusted (G) (H) balance $ 17,085 $ 17,085 COS (FIFO and LIFO cost-flow assumptions) 7,153 7,153 Gross profit 9,932 - - - - - - - - 9,932 Operating expenses SG&A expenses (excl D&A and advertising) 3,454 259 Other operating expenses (income) 425 78 D&A 1,792 425 Advertising expense 259 1,792 R&D expense 3,454 78 Total operating expenses 6,008 - - - - - - - - 6,008 Operating income 3,924 - - - - - - - - 3,924 Other income (expense) Interest expense, net of interest income (15) (15) Gain on sale of non-core product lines - - Gain (loss) on debt restruct/early extinguish - - Gain (loss) on disposal of invest sec's AFS - - Gain (loss) on disposal of PP&E - - Impairment loss on PP&E - - Impairment loss on goodwill - - Venuzuela hyperinflation transition charge - - Restructuring gain (loss) (35) (35) Total other income (expense) (50) - - - - - - - - (50) Income from cont. oper's before taxes 3,874 - - - - - - - - 3,874 Taxes on income from continuing operations 1,243 Income from contin oper incl noncontrol int's 2,631 - - - - - - - - 2,631 159 - - - - - - - - 159 2,472 - - - - - - - - $ Less: Income attrib to noncontrol interests Income from continuing operations Gain (loss) on discont'd oper's, net of tax 2,472 - Disregard 2,472 Disregard - Disregard $ 2,472 Disregard $ 3,874 - - - - - - - - $ (15) - - - - - - - - 3,889 - - - - - - - - $ Income before extraordinary items Extraordinary gain (loss), net of tax Net income 1,243 Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes $ 3,874 (15) 3,889 Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) INPUT US dollar amounts IN MILLIONS Adjusted balances (used for financial analysis) 2008 Sales, net returns and allowances 2009 2010 2011 2012 $ 15,330 $ 15,327 $ 15,564 $ 16,734 $ 17,085 COS (FIFO and LIFO cost-flow assumptions) 6,704 6,319 6,360 7,144 7,153 Gross profit 8,626 9,008 9,204 9,590 9,932 3,049 2,968 2,941 3,154 3,454 240 256 256 262 259 1,650 1,534 1,656 1,734 1,792 D&A 427 460 482 532 425 Other operating expenses (income) 153 134 (6) 95 78 Total operating expenses 5,519 5,352 5,329 5,777 6,008 Operating income 3,107 3,656 3,875 3,813 3,924 (107) (101) (82) (74) (15) Gain on sale of non-core product lines - 5 50 207 - Gain (loss) on debt restruct/early extinguish - - - - - Gain (loss) on disposal of invest sec's AFS - - - - - Gain (loss) on disposal of PP&E - - - - - Impairment loss on PP&E - - - - - Impairment loss on goodwill - - - - - Venuzuela hyperinflation transition charge - - (271) - - 50 18 (86) (103) (35) (57) (78) (389) 30 (50) 3,050 3,578 3,486 3,843 3,874 Taxes on income from continuing operations 982 1,154 1,135 1,252 1,243 Income from contin oper incl noncontrol int's 2,068 2,424 2,351 2,591 2,631 80 106 110 123 159 1,988 2,318 2,241 - - - (60) - 1,988 2,318 2,241 2,408 2,472 - - - (30) - Operating expenses SG&A expenses (excl D&A and advertising) R&D expense Advertising expense Other income (expense) Interest expense, net of interest income Restructuring gain (loss) Total other income (expense) Income from cont. oper's before taxes Less: Income attrib to noncontrol interests Income from continuing operations $ Gain (loss) on discont'd oper's, net of tax Income before extraordinary items Extraordinary gain (loss), net of tax Net income $ 2,468 $ 2,472 1,988 $ 2,318 $ 2,241 $ 2,378 $ 2,472 3,050 3,578 3,486 3,843 3,874 (107) (101) (82) (74) (15) 3,157 3,679 3,568 3,917 3,889 Before-tax amounts from above: Income from cont. oper's before taxes Interest expense, net Operating profit before income taxes Explanations for Analysis Adjustments (A) Capitalize operating lease commitments (see balance sheet for related adjustments) (B) See balance sheet for adjustment to recognize oblig's under noncancellable, fixed-price vendor supply agreements (C) See balance sheet for adjustment to recognize guaranteed debt of unconsolidated affiliated entities (D) See balance sheet for adjustment to recognize undeclared prior-year dividends on cumulative preferred stock (E) Adjust FIFO-basis COS for "inflation (holding) profits" (NO balance sheet adjustment needed) (F) Reclassify financial elements of DB pension and OPEB expense (NO balance sheet adjustment needed) (G) Recognize capitalized interest as financial expense (NO balance sheet adjustment needed) (H) [NOT USED FOR Colgate-Palmolive ASSIGNMENTS] Recast or eliminate asset impairment and restructuring charges, nonoperating gains and losses (NO balance sheet adjustment needed) The A602 Learning Topic 2 Background Paper and the MBA F601 Topic 8 Background Paper, Financial Ratio Analysis, discuss and illustrate common size analysis. Do NOT modify the formatting in this template (to ensure a professionallooking presentation). Use care not to unintentionally overwrite formulas in this worksheet tab. Consolidated BS-Common-size analysis Sixth prior FY FS information used principally to compute average balances in fifth prior FY. (US dollars in millions) Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2012 2007 2008 2009 2010 2011 2012 ASSETS Current Assets Cash and cash equivalents 429 555 600 490 878 884 4% 5% 5% 4% 7% 7% Investment securities AFS 12 12 41 74 72 116 0% 0% 0% 1% 1% 1% 1,731 1,639 1,678 1,663 1,724 1,729 17% 15% 14% 14% 14% 13% (51) (47) (52) (53) (49) (61) -1% 0% 0% 0% 0% 0% AR, net 1,681 1,592 1,626 1,610 1,675 1,668 17% 14% 13% 13% 13% 12% Inventories, at LOCOM using FIFO and LIFO 1,171 1,197 1,209 1,222 1,327 1,365 12% 11% 10% 10% 10% 10% DIT assets-current 125 97 105 117 204 275 1% 1% 1% 1% 2% 2% Other current assets 201 257 229 217 246 248 2% 2% 2% 2% 2% 2% Total current assets 3,619 3,710 3,810 3,730 4,402 4,556 36% 33% 31% 30% 35% 34% PP&E, at cost 6,138 7,133 7,861 8,275 7,324 7,821 61% 64% 64% 67% 58% 58% (3,123) (2,818) (3,184) (3,467) (3,656) (3,979) -31% -25% -26% -28% -29% -30% PP&E, net 3,015 4,315 4,677 4,808 3,668 3,842 30% 39% 38% 39% 29% 29% Goodwill, net impairment write-downs 2,272 2,152 2,302 2,362 2,494 2,500 22% 19% 19% 19% 20% 19% 845 834 821 831 1,504 1,499 8% 7% 7% 7% 12% 11% - - - 84 115 92 0% 0% 0% 1% 1% 1% 362 164 685 472 541 905 4% 1% 6% 4% 4% 7% 10,112 11,175 12,295 12,287 12,724 13,394 100% 100% 100% 100% 100% 100% AR, gross Less: Allowance for bad debts Less: Accumulated depreciation of PP&E Other intangibles, net amort., impairment DIT assets-noncurrent Other noncurrent assets Total assets Consolidated BS-Common-size analysis (US dollars in millions) Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2012 2007 2008 2009 2010 2011 2012 LIABILITIES & STOCKHOLDERS' EQUITY Current Liabilities AP 1,067 1,061 1,172 1,165 1,244 1,290 11% 9% 10% 9% 10% 10% Accrued liabilities 1,539 1,421 1,679 1,682 1,700 1,888 15% 13% 14% 14% 13% 14% 263 272 387 272 392 254 3% 2% 3% 2% 3% 2% DIT liabilities-current - - - - - - 0% 0% 0% 0% 0% 0% Capital lease obligation-current portion - 149 162 165 - - 0% 1% 1% 1% 0% 0% L-T debt-current portion 138 91 326 561 346 250 1% 1% 3% 5% 3% 2% Notes and loans payable-current 156 107 35 48 34 54 2% 1% 0% 0% 0% 0% - - - - - - 0% 0% 0% 0% 0% 0% Total current liabilities 3,163 3,101 3,761 3,893 3,716 3,736 31% 28% 31% 32% 29% 28% Notes and loans payable-noncurrent 3,214 3,578 2,821 2,815 4,430 4,926 32% 32% 23% 23% 35% 37% Capital lease obligation-noncurrent 8 1,054 999 950 - - 0% 9% 8% 8% 0% 0% Bonds, net discount [or, plus premium] - - - - - - 0% 0% 0% 0% 0% 0% 264 82 82 108 252 293 3% 1% 1% 1% 2% 2% Other noncurrent liabilities 1,177 1,316 1,375 1,704 1,785 2,049 12% 12% 11% 14% 14% 15% Total noncurrent liabilities 4,663 6,030 5,277 5,577 6,467 7,268 46% 54% 43% 45% 51% 54% Total liabilities 7,826 9,131 9,038 9,470 10,183 11,004 77% 82% 74% 77% 80% 82% 198 181 169 0 0 0 2% 2% 1% 0% 0% 0% - - - 0 0 0 0% 0% 0% 0% 0% 0% 733 733 733 733 733 733 7% 7% 6% 6% 6% 5% 1,518 1,610 1,764 1,132 1,336 1,551 15% 14% 14% 9% 10% 12% (8,904) (9,697) (10,478) (11,305) (12,808) (14,386) -88% -87% -85% (219) (187) (133) (99) (60) (41) -2% -2% -1% -1% 0% 0% 10,628 11,760 13,157 14,329 15,649 16,953 105% 105% 107% 117% 123% 127% - 121 141 142 166 201 0% 1% 1% 1% 1% 2% (1,667) (2,477) (2,096) (2,115) (2,475) (2,621) -16% -22% -17% -17% -19% -20% 2,286 2,044 3,257 2,817 2,541 2,390 23% 18% 26% 23% 20% 18% 10,112 11,175 12,295 12,287 12,724 13,394 100% 100% 100% 100% 100% 100% 509.000 501.400 494.200 494.900 480.000 467.900 Income taxes payable Other current liabilities DIT liabilities-noncurrent Shareholders' Equity Preferred (or preference) stock Premium on preferred stock Common stock (par or stated value) Additional paid-in capital (APIC) Less: Treasury stock Unearned stock-based compensation Retained earnings Noncontrolling interest in consolid subsid Accum other comprehensive income (loss) Total stockholders' equity Total liabilities and stockholders' equity Common shares outstanding (in millions) -92% -101% -107% Consolidated IS-Common-size analysis (US dollars in millions, except per share amounts) Fiscal year ended (after analysis adjustments) Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 2012 2007 2008 2009 2010 2011 2012 13,790 15,330 15,327 15,564 16,734 17,085 100% 100% 100% 100% 100% 100% COS (FIFO and LIFO cost-flow assumptions) 6,043 6,704 6,319 6,360 7,144 7,153 44% 44% 41% 41% 43% 42% Gross profit 7,747 8,626 9,008 9,204 9,590 9,932 56% 56% 59% 59% 57% 58% 2,846 3,049 2,968 2,941 3,154 3,454 21% 20% 19% 19% 19% 20% 247 240 256 256 262 259 2% 2% 2% 2% 2% 2% 1,546 1,650 1,534 1,656 1,734 1,792 11% 11% 10% 11% 10% 10% 334 427 460 482 532 425 2% 3% 3% 3% 3% 2% 82 153 134 (6) 95 78 1% 1% 1% 0% 1% 0% Total operating expenses 5,055 5,519 5,352 5,329 5,777 6,008 37% 36% 35% 34% 35% 35% Operating income 2,692 3,107 3,656 3,875 3,813 3,924 20% 20% 24% 25% 23% 23% (157) (107) (101) (82) (74) (15) -1% -1% -1% -1% 0% 0% 49 - 5 50 207 - 0% 0% 0% 0% 1% 0% Gain (loss) on debt restruct/early extinguish - - - - - - 0% 0% 0% 0% 0% 0% Gain (loss) on disposal of invest sec's AFS - - - - - - 0% 0% 0% 0% 0% 0% Gain (loss) on disposal of PP&E - - - - - - 0% 0% 0% 0% 0% 0% Impairment loss on PP&E - - - - - - 0% 0% 0% 0% 0% 0% Impairment loss on goodwill - - - - - - 0% 0% 0% 0% 0% 0% Venuzuela hyperinflation transition charge - - - (271) - - 0% 0% 0% -2% 0% 0% (21) 50 18 (86) (103) (35) 0% 0% 0% -1% -1% 0% (129) (57) (78) (389) 30 (50) -1% 0% -1% -2% 0% 0% 2,563 3,050 3,578 3,486 3,843 3,874 19% 20% 23% 22% 23% 23% Taxes on income from continuing operations 759 982 1,154 1,135 1,252 1,243 6% 6% 8% 7% 7% 7% Income from contin oper incl noncontrol int's 1,804 2,068 2,424 2,351 2,591 2,631 13% 13% 16% 15% 15% 15% 67 80 106 110 123 159 0% 1% 1% 1% 1% 1% 1,737 1,988 2,318 2,241 2,468 2,472 13% 13% 15% 14% 15% 14% Sales, net returns and allowances Operating expenses SG&A expenses (excl D&A and advertising) R&D expense Advertising expense D&A Other operating expenses (income) Other income (expense) Interest expense, net of interest income Gain on sale of non-core product lines Restructuring gain (loss) Total other income (expense) Income from cont. oper's before taxes Less: Income attrib to noncontrol interests Income from continuing operations Consolidated Cash Flows Statements (SCFs) (US dollars in millions) Fiscal year ended (without analysis adjustments) 2007 2008 2009 2010 2011 2012 Cash flows from operating activities Income from contin oper, incl noncontrol int 1,737 2,037 2,397 2,313 2,554 2,631 Depreciation and amortization (D&A) 334 348 351 376 421 425 Stock-based compensation expense 110 100 117 121 122 120 Gain before tax on sale of non-core product lines (49) - (5) (50) (207) - Gain (loss) on debt restruct/early extinguish - - - - - - Gain (loss) on disposal of invest sec's AFS - - - - - - Gain (loss) on disposal of PP&E - - - - - - Impairment loss on PP&E - - - - - - Impairment loss on goodwill - - - - - - Venuzuela hyperinflation transition charge - - - 271 - - 21 (50) (18) 86 103 35 (66) (70) 57 40 (130) 19 (111) (135) 44 (10) (130) (21) 57 (47) 136 135 54 30 366 125 294 (65) 199 (5) Voluntary benefit plan contribution - - (73) (35) (178) (101) [Not used] - - - - - - (147) (6) (23) 29 88 63 - - - - - - 2,252 2,302 3,277 3,211 2,896 3,196 1.1 1.0 1.2 1.2 1.0 1.0 Adjustments to reconcile income to OCF Loss (gain) on retructuring Changes in operating working capital: AR, net Inventories [Not used] Other noncurrent assets and liabilities AP and other accruals DIT liabilities-current [Not used] Operating cash inflow (outflow) (OCF) OCF-to-NI before D&A ratio (US dollars in millions) Fiscal year ended 2007 2008 2009 2010 2011 2012 Cash flows from investing activities Capital expenditures (583) (684) (575) (550) (537) (565) Sale of property and non-core product lines 110 58 17 42 263 72 Purch of marketable securities, invest's AFS (11) - (289) (308) (356) (545) Sales of marketable securities, invest's AFS - 10 - 167 423 147 Pymnt for acquisitions, net of cash acquired (27) - - - (966) (29) Other, net (17) 3 6 (9) (40) 55 (528) (613) (841) (658) (1,213) (865) Net cash provided (used) by invest activities Cash flows from financing activities Principal payments on debt (1,738) (2,320) (3,950) (4,719) (4,429) (5,011) 1,513 2,515 3,424 5,015 5,843 5,452 (798) (889) (981) (1,142) (1,203) (1,277) (1,269) (1,073) (1,063) (2,020) (1,806) (1,943) 489 237 300 242 353 478 - - - - - - (1,803) (1,530) (2,270) (2,624) (1,242) (2,301) Effect exchng rate changes on cash/equiv's 18 (33) (121) (39) (53) (24) Net increase in cash and cash equivalents (61) 126 45 (110) 388 6 Cash and cash equivalents at begin of FY 490 429 555 600 490 878 Cash and cash equivalents at end of FY 429 555 600 490 878 884 Income taxes paid 647 862 1,098 1,123 1,007 1,280 Interest paid 163 119 98 70 58 77 54 64 74 - - - Proceeds from issuance of debt Dividends paid-preferred and common Purchases of treasury shares Proceeds exer of ESOs, excess tax benefits Other, net Net cash used in financing activities Supplemental cash flow information Princ pymts on ESOP debt guar'd by Company For each ratio, comment in this column on the (1) trend and (2) comparison with the key competitor and industry average ratios, emphasizing (i) its likely business or financial implications and (ii) the relationship of the trends to trends in other ratios included in this workbook tab. The A602 Learning Topic 2 Background Paper and the MBA F601 Topic 8 Background Paper, Financial Ratio Analysis, define and describe financial ratios. Do NOT modify the formatting in this template (to ensure a professional-looking presentation). Use care not to unintentionally overwrite formulas in this worksheet tab. Do not define the ratios (e.g., "this ratio indicates whether the company is able to pay its S-T obligations as they become due"). Do not only "state the obvious" (e.g., "the ratio's trend is favorable" or "the company's ratio compare's unfavorably to the industry average ratio"). - Write in clear and complete sentences (subject, verb, punctuation, etc). - Spell-check your comments. - Comments must fit within the space provided, without widening columns, increasing row heights, or other format changes. All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Sixth prior-year data is included only to permit computation of fifth prior-year financial ratios that use balance sheet averages. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) Key Competitor ratios Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 2012 Current (working capital) ratio NA 1.2 1.0 1.0 1.2 1.2 Total current assets NA 3,710 3,810 3,730 4,402 4,556 Total current liabilities NA 3,101 3,761 3,893 3,716 3,736 Acid-test (quick) ratio NA 0.7 0.6 0.6 0.7 0.7 Cash and cash equivalents NA 555 600 490 878 884 Investment securities AFS (market value) NA 12 41 74 72 116 AR, net NA 1,592 1,626 1,610 1,675 1,668 Total cash-and-equivalents, invest. securities, and AR NA 2,159 2,267 2,174 2,625 2,668 Total current liabilities NA 3,101 3,761 3,893 3,716 3,736 Average collection period (days) (see also turnover ratio) NA 38 38 37 35 35 1,681 1,592 1,626 1,610 1,675 1,668 Average AR, net NA 1,636 1,609 1,618 1,643 1,672 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Average daily sales (assume 360-day year) NA 42.6 42.6 43.2 46.5 47.5 Average days to sell inventory (see also turnover ratio) NA 64 69 69 64 68 1,171 1,197 1,209 1,222 1,327 1,365 Average inventory NA 1,184 1,203 1,216 1,275 1,346 COS NA 6,704 6,319 6,360 7,144 7,153 Average daily COS (assume 360-day year) NA 18.6 17.6 17.7 19.8 19.9 Avg days purch in AP, accr'd liab (see also AP turnover ratio) NA 78 86 92 84 87 2,606 2,482 2,851 2,847 2,944 3,178 Average AP and accrued liabilities NA 2,544 2,667 2,849 2,896 3,061 COS and total operating expenses NA 12,223 11,671 11,689 12,921 13,161 Less D&A [OCF section of SCF provides] NA 427 460 482 532 425 Total "cash costs" NA 11,796 11,211 11,207 12,389 12,736 Average daily "cash costs" (assume 360-day year) NA 32.8 31.1 31.1 34.4 35.4 Net operating cycle (days) NA 24 21 15 15 16 Industry average ratios AR, net Inventory AP and accrued liabilities 0.9 1.2 0.4 0.7 27 39 60 67 92 81 (5) 24 All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) 2007 CAPITAL STRUCTURE AND SOLVENCY (U.S. dollars in millions, except per-share amounts) 2012 Key Competitor ratios Industry average ratios 2012 Key Competitor ratios Industry average ratios Fiscal year ended (after analysis adjustments) 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 Total debt-to-equity (total debt) ratio NA 5.00 2.98 3.36 4.01 4.60 Total (current and noncurrent) liabilities and preferred stock NA 9,312 9,207 9,470 10,183 11,004 Total common (only) stockholders' equity NA 1,863 3,088 2,817 2,541 2,390 L-T debt-to-equity ratio NA 3.33 1.76 1.98 2.55 3.04 Total noncurrent liabilities and preferred stock NA 6,211 5,446 5,577 6,467 7,268 Total common (only) stockholders' equity NA 1,863 3,088 2,817 2,541 2,390 Times interest earned (interest coverage) ratio NA 29.5 36.4 43.5 52.9 259.3 Income from continuing operations before income taxes NA 3,050 3,578 3,486 3,843 3,874 Interest expense, net of interest income NA 107 101 82 74 15 Income from cont oper's before taxes and interest exp (EBIT) NA 3,157 3,679 3,568 3,917 3,889 OCF-to-L-T debt ratio NA 0.38 0.62 0.58 0.45 0.44 Operating cash flow (OCF) [from SCF] NA 2,302 3,277 3,211 2,896 3,196 Total noncurrent liabilities NA 6,030 5,277 5,577 6,467 7,268 0.47 0.89 0.33 0.77 17.3 71.2 0.36 0.39 All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) 2007 RETURN ON INVESTMENT (U.S. dollars in millions, except per-share amounts) 2012 Key Competitor ratios Industry average ratios 2012 Key Competitor ratios Industry average ratios Fiscal year ended (after analysis adjustments) 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 Return on assets (ROA) ratio NA 19.4% 20.3% 18.7% 20.1% 19.0% Income from continuing operations before income taxes NA 3,050 3,578 3,486 3,843 3,874 Interest expense, net of interest income NA 107 101 82 74 15 Income tax provision (i.e., taxes on income from contin oper's) NA 982 1,154 1,135 1,252 1,243 Income from continuing operations (after taxes) NA 1,988 2,318 2,241 2,468 2,472 Combined effective income tax rate NA 32.2% 32.3% 32.6% 32.6% 32.1% 10,112 11,175 12,295 12,287 12,724 13,394 Average total assets NA 10,644 11,735 12,291 12,506 13,059 Return on total common stockholders' equity (ROCE) ratio NA 99.2% 92.4% 74.7% 92.1% 100.2% Income from continuing operations (after analysis adjust's) NA 1,988 2,318 2,241 2,468 2,472 Less Dividends on preferred stock, net of any tax benefit NA 28 30 34 0 0 Income from contin oper's available to common stockholders NA 1,960 2,288 2,207 2,468 2,472 2,089 1,863 3,088 2,817 2,541 2,390 NA 1,976 2,476 2,953 2,679 2,466 Total assets Total common (only) stockholders' equity Average total common (only) stockholders' equity 7.9% 11.7% 16.4% 39.8% All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) 2007 OPERATING PERFORMANCE (U.S. dollars in millions, except per-share amounts) 2012 Key Competitor ratios Industry average ratios 2012 Key Competitor ratios Industry average ratios Fiscal year ended (after analysis adjustments) 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2007 Gross margin ratio NA Sales, net NA COS 2008 56.3% 2009 2010 2011 58.8% 59.1% 57.3% 58.1% 15,330 15,327 15,564 16,734 17,085 NA 6,704 6,319 6,360 7,144 7,153 SG&A-to-sales ratio NA 19.9% 19.4% 18.9% 18.8% 20.2% SG&A expense (exclud. D&A, advertising and R&D expense) NA 3,049 2,968 2,941 3,154 3,454 Sales, net NA 15,330 15,327 15,564 16,734 17,085 R&D-to-sales ratio NA 1.6% 1.7% 1.6% 1.6% 1.5% R&D expense NA 240 256 256 262 259 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Advertising-to-sales ratio NA 10.8% 10.0% 10.6% 10.4% 10.5% Advertising expense NA 1,650 1,534 1,656 1,734 1,792 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Other operating expense-to-sales ratio NA 1.0% 0.9% 0.0% 0.6% 0.5% Total other expense (income) NA 153 134 (6) 95 78 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Operating margin ratio NA 20.3% 23.9% 24.9% 22.8% 23.0% Operating income NA 3,107 3,656 3,875 3,813 3,924 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Pre-tax operating margin ratio NA 19.9% 23.3% 22.4% 23.0% 22.7% Income from continuing operations before income taxes NA 3,050 3,578 3,486 3,843 3,874 Sales, net NA 15,330 15,327 15,564 16,734 17,085 Net margin ratio NA 13.5% 15.8% 15.1% 15.5% 15.4% Income from contin oper's (after taxes) incl noncontrol int's NA 2,068 2,424 2,351 2,591 2,631 Sales, net NA 15,330 15,327 15,564 16,734 17,085 49.3% 52.3% 31.6% 29.3% 2.4% 4.2% 11.2% 8.4% 0.0% 0.1% 15.9% 19.5% 15.3% 18.2% 12.9% 13.1% All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) 2007 ASSET UTILIZATION (U.S. dollars in millions, except per-share amounts) 2012 Key Competitor ratios Industry average ratios 2012 Key Competitor ratios Industry average ratios Fiscal year ended (after analysis adjustments) 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2007 2008 2009 2010 2011 AR turnover ratio NA 9.4 9.5 9.6 10.2 10.2 Sales, net NA 15,330 15,327 15,564 16,734 17,085 1,681 1,592 1,626 1,610 1,675 1,668 Average accounts receivable, net NA 1,636 1,609 1,618 1,643 1,672 Inventory turnover ratio NA 5.7 5.3 5.2 5.6 5.3 COS NA 6,704 6,319 6,360 7,144 7,153 1,171 1,197 1,209 1,222 1,327 1,365 Average inventory NA 1,184 1,203 1,216 1,275 1,346 AP and accrued liabilities turnover ratio NA 4.6 4.2 3.9 4.3 4.2 "Cash costs" [from above] NA 11,796 11,211 11,207 12,389 12,736 2,606 2,482 2,851 2,847 2,944 3,178 Average AP and accrued liabilities NA 2,544 2,667 2,849 2,896 3,061 PP&E turnover ratio NA 4.2 3.4 3.3 3.9 4.5 Sales, net NA 15,330 15,327 15,564 16,734 17,085 3,015 4,315 4,677 4,808 3,668 3,842 Average PP&E, net NA 3,665 4,496 4,743 4,238 3,755 Total asset turnover ratio NA 1.4 1.3 1.3 1.3 1.3 Sales, net NA 15,330 15,327 15,564 16,734 17,085 10,112 11,175 12,295 12,287 12,724 13,394 NA 10,644 11,735 12,291 12,506 13,059 AR, net Inventory, at LOCOM AP and accrued liabilities PP&E, net of accumulated depreciation Total assets Average total assets 13.4 9.3 6.0 6.0 3.9 4.4 4.0 4.7 0.6 0.9 All cells in this workbook tab are already properly formatted for correct and professional-looking display of ratios and FS amounts. Scroll to the bottom of the worksheet to view the assignment grading rubric. Financial Ratio Analysis LIQUIDITY (U.S. dollars in millions, except per-share amounts) 2007 STOCK PERFORMANCE (U.S. dollars in millions, except per-share amounts) 2012 Key Competitor ratios Industry average ratios 2012 Key Competitor ratios Industry average ratios Fiscal year ended (after analysis adjustments) 2008 2009 2010 2011 Fiscal year ended (after analysis adjustments) 2007 2008 2009 18.7 2010 18.8 2011 Price-earnings (P-E) ratio NA 18.6 18.4 20.3 Market price of common stock NA $ 68.54 $ 82.15 $ 80.37 $ 92.39 $ 104.54 Income from continuing operations (after analysis adjust's) NA 1,988 2,318 2,241 2,468 2,472 Less Dividends on preferred stock, net of any tax benefit NA 28 30 34 0 0 Income from contin oper's available to common stockholders NA 1,960 2,288 2,207 2,468 2,472 Diluted weighted average shares outstanding (in millions) NA 535.00 524.60 510.90 492.00 480.10 Diluted income from contin operations per common share NA $ 3.66 $ 4.36 $ 4.32 $ 5.02 $ 5.15 Dividend payout ratio NA 0.43 0.39 0.47 0.45 0.47 Dividends declared per share of common stock NA $ 1.56 $ 1.72 $ 2.03 $ 2.27 $ 2.44 Diluted income from contin operations per common share NA $ 3.66 $ 4.36 $ 4.32 $ 5.02 $ 5.15 Price-book (P-B) ratio NA 18.4 13.1 14.1 17.5 20.5 Market price of common stock NA $ 68.54 $ 82.15 $ 80.37 $ 92.39 $ 104.54 Common shares outstanding at FYE (in millions) NA 501.4 494.2 494.9 480.0 467.9 Total market capitalization ("cap") NA 34,366 40,599 39,775 44,347 48,914 Total common (only) stockholders' equity NA 1,863 3,088 2,817 2,541 2,390 19.2 20.0 0.59 0.54 3.0 20.1 The A602 Learning Topic 2 Background Paper discusses NAICS codes and the process of obtaining companies' financial ratios using Mergent Online via the university's online library Mergent Online Comparison Industry Group Ratios - Year: 2012 NAICS Code: Company Name Average days to sell inventory ratio computed as 360 DIVIDE inventory turnover ratio, at right 325611 - Soap and Other Detergent Manufacturing (includes toothpastes, gels, and tooth powders manufacturing) Current (working capital) ratio Avg AR Average Avg days Acid-test collection days to purch in Total L-T TIE OCF-to(quick) period sell AP/accr'd debt-to- debt-to- (Interest L-T debt ratio (days) inventory liab equity equity Coverage) ratio ROA Gross SG&A- R&D-tomargin to-sales sales Average (unweighted) 1.2 0.7 (1) 67 (1) 0.89 0.77 71.2 (1) Colgate-Palmolive Co. 1.2 0.7 (1) 68 (1) 2.39 2.25 258.8 (1) Procter & Gamble Co. 0.9 0.33 17.3 (1) 7.9% 16.4% 49.3% 31.6% 2.4% Church & Dwight Co., Inc. 1.3 0.32 38.9 (1) 9.7% 17.0% 44.2% 25.5% 1.9% Clorox Co. 0.7 - 7.2 (1) 12.7% - 42.1% 23.4% 2.2% Johnson & Johnson 1.9 0.18 33.9 (1) 9.3% For purposes of the For purposes of the (1) (1) 0.4 assignments (rather than 0.47 60 assignments (rather than the group project), (1) (1) 0.9 the group project), this 49 this 0.44 worksheet is already worksheet is already (1) (1) 0.4 complete. 44 complete. 1.3 (1) 115 (1) 0.25 [Industry Firm Name 5] (1) (1) (1) (1) 39.8% 52.3% 29.3% 4.2% 18.9% 108.0% 58.1% 34.7% 1.5% 17.9% 67.8% 31.0% 13.1% (1) [Industry Firm Name 6] 11.7% ROE (ROCE) (1) CHECK: Compute averages 1.2 0.7 (1) 67 (1) 0.89 0.77 71.2 (1) 11.7% 39.8% 52.3% 29.3% 4.2% (1) Mergent does NOT provide this ratio. Compute this ratio using firms' financial statements included in their respective Annual Repors on Forms 10-K. See Financial Ratios tab Other Advert- oper expto-sales to-sales Pre-tax Operating operating margin margin Net (profit) margin AR AP and Total turnover Inventory accr'd liab PP&E asset Ratio turnover turnover turnover turnover 8.4% (1) 19.5% 18.2% 13.1% (1) 6.0 (1) 4.7 0.9 10.5% (1) 22.7% 22.6% 14.5% (1) 5.3 (1) 4.5 1.3 11.2% (1) 15.9% 15.3% 12.9% (1) 6.0 (1) 4.0 0.6 - (1) 18.7% 18.3% 12.0% (1) 7.4 (1) 5.3 0.8 - (1) 16.4% 14.1% 9.9% (1) 8.3 (1) 5.1 1.3 3.4% (1) 23.6% 20.5% 16.1% (1) 3.1 (1) 4.4 0.6 4.7 0.9 (1) (1) (1) 8.4% (1) (1) (1) (1) 19.5% 18.2% 13.1% (1) 6.0 ve Annual Repors on Forms 10-K. See Financial Ratios tab (1) Operating Lease Commitment Analysis Adjustment The A602 Learning Topic 4 Background Paper, pages 36 - 45 discusses and illustrates accounting for operating leases and related analysis adjustments. Use this worksheet to determine the necessary analysis adjustment to financial statements related to the operating lease commitments. Use care not to unintentionally overwrite formulas in this worksheet tab. This should be the FY immediately following the FY of the financial statements from which the analyst obtained the lease commitment information. INPUT US dollar the Colgate-Palmolive assignments, this worksheet is For purposes of amounts IN MILLIONS COMPLETE and the analysis adjustments have been posted to the "BS Analysis Adjustments" and "IS Analysis Adjustments" worksheets. From company's income statement for: FY of BS and Form 10-K: 2008 Combined effective income tax rate Income from cont. oper's before taxes 3,005 Income tax provision 2007 $ $ 968 32.2% Discount (incremental borrowing) rate Year 1 2008 Minimum lease payments (MLPs) Operating leases Capital leases Total $ 135 2 2009 2010 89 4 2011 68 5 2012 66 (B) Thereafter Total The A602 Learning Topic 4 Background Paper discusses the how analysts estimate a company's incremental borrowing rate $ As discussed in the A602 Learning Topic 4 Background Paper, NO analysis adjustment is necessary with respect to capital leases because the financial statements already recognize the related assets, liabilities, implicit interest expense, and depreciation expense, as desired. 117 3 $ 115 (A) $ 590 $ $ Estimated number of years in "thereafter" period: (A) divide (B) Estimated total number of years, n As discussed in the A602 Learning Topic 4 Background Paper, analysts use only the information for the first 2 rows of this table to determine an analysis adjustment to the balance sheet. In order to determine analysis adjustments for subsequent FYs, similar analyses of subsequent FYs' operating lease commitment disclosures is necessary. 2.0 7.0 A 2.0% Discount rate provided by facilitator B C D E From footnote 1 / (1 + Discount rate) F CxD n G Prior H x Rate H I J C-F Prior H - G Begin. I - J Beg. I / last n Undeprec. Deprec. Future Company's Present year fiscal value Implicit of lease Lease lease (amortiz.) (n) year of MLP interest obligation obligation asset expense 0 2008 2 3 Discount factor 2007 1 MLPs Reduction $ $ 553 $ 553 124 429 474 $ 79 9 108 320 395 79 84 6 83 238 316 79 135 0.980392 $ 132 $ 2009 117 0.961169 112 2010 89 0.942322 11 $ 4 2011 68 0.923845 63 5 63 174 237 79 5 2012 66 0.905731 60 3 63 112 158 79 6 2013 0.887971 59 2 64 48 79 79 7 2014 0.870560 43 1 48 - - 79 8 2015 9 2016 10 2017 11 2018 12 2020 14 2021 15 2022 16 17 0.853490 - - - - - - 0.836755 - - - - - - 0.820348 - - - - - - 0.804263 - - - - - - 0.788493 - - - - 0.773033 - - - - 0.757875 - - - - - 0.743015 - - - - 2023 - 0.728446 - - - - 2024 - 0.714163 - - - - 18 2025 - 0.700159 - - - 19 2026 - 0.686431 - - 20 Totals 2019 13 Assumes 66 year 5 49 amount is also the amount in each year of the "thereafter " period, except . . . .-. . for final year ("plug" remainde r). 2027 - 0.672971 $ 590 $ 553 $ 37 $ Depreciation amounts are computed here using the straight-line method,-assuming this-is the policy disclosed in the company's "policy" footnote - its financial(in statements)