use financial statment of Colgate-Palmolive (Pakistan).XB
WEIGHTED AVERAGE COST OF CAPITAL 1. Prepare an excel spreadsheet that will: i. Compute a firm's weighted average cost of capital. ii. Determine how much financing will come from the various component sources. iii. Split common equity into internal and external components and adjust the weighted average cost of capital of various levels of internal equity availability. iv. How can the Capital Asset Pricing Model (CAPM) be used to estimate the cost of capital for a real investment decision? [hint: Rr+B(Rm - RO). V. 2. You will need the following input cells: i. Par value of bonds ii. Coupon rate paid by bonds iii. Time to maturity of bonds iv. Price of bonds Preferred stock dividend (DP) vi. Preferred stock price (PP) vii. Preferred stock flotation costs (FP) viii. Current common stock dividend (Do) ix. Common stock price (Po) X. Common stock growth rate (8) xi. Common stock flotation cost (Fs) xii. Tax rate (1) xiii. Total amount of financing needed xiv. Internal equity available xv. Capital structure (debt, preferred stock and common stock) Fiscal year is July-June. All values PKR Millions 2021 2020 2019 Sales/Revenue 50,563 43,530 36,961 Sales Growth 16 16% 17.77 18.199 Cost of Goods Sold (COGS) incl. DGA 36,292 31.423 26,866 COGS excluding D&A 35,448 30,611 26,047 Depreciation & Amortization Expense 844 811 819 Depreciation 835 798 806 Amortization of Intangibles 9 13 13 COGS Growth 1550 16.96% 26.73% Gross income 14271 12.108 10.095 Gross Income Growth 17.87% 19.94% 0.22 Gross Profit 28.22 Margin SG&A Expense 6,834 5.943 5,548 Other SG&A 6,834 5943 5,548 SGA Growth 15.00 2015 -2.42% Other Operating Expense 83 128 91 EBIT 7,354 6,037 4,455 Unusual Expense (4) 5 (1) Non Operating Income/Expense 420 333 246 Non-Operating Interest Income 292 443 261 Interest Expense 108 96 Interest Expense Growth 13.199 0.00% Gross Interest Expense 108 96 Pretax income 7,963 6.712 4,964 Pretax income Growth 18.64% 35 20% 634 Pretax Margin 15.75 Income Tax 2.286 1847 1,453 Income Tax Current Domestic 2,330 1911 1,451 Income Tax - Deferred Domestic (64) 3 3 Consolidated Net Income 5,677 4.865 3,511 Net Income 5,677 4,865 3,511 Net Income Growth 16,69 38.57% 7.79% Net Margin 11235 Net Income After Extraordinaries 5,677 4,865 3,511 Net Income Available to Common 5,677 4.865 3,511 EPS (Basic) 7798 66.83 48.23 EPS (Basic) Growth 1669% 38.57% 29.35 Basic Shares Outstanding 73 73 73 EPS (Diluted) 77.98 66.83 48.23 EPS (Diluted) Growth 16.69% 38.57% 29.35% Diluted Shares Outstanding 73 73 73 EBITDA 8,198 6,848 5,274 EBITDA Growth 19.71% 29.84% 5.85% EBITDA Margin 16.21% EBIT 7,354 6,037 4,455 WEIGHTED AVERAGE COST OF CAPITAL 1. Prepare an excel spreadsheet that will: i. Compute a firm's weighted average cost of capital. ii. Determine how much financing will come from the various component sources. iii. Split common equity into internal and external components and adjust the weighted average cost of capital of various levels of internal equity availability. iv. How can the Capital Asset Pricing Model (CAPM) be used to estimate the cost of capital for a real investment decision? [hint: Rr+B(Rm - RO). V. 2. You will need the following input cells: i. Par value of bonds ii. Coupon rate paid by bonds iii. Time to maturity of bonds iv. Price of bonds Preferred stock dividend (DP) vi. Preferred stock price (PP) vii. Preferred stock flotation costs (FP) viii. Current common stock dividend (Do) ix. Common stock price (Po) X. Common stock growth rate (8) xi. Common stock flotation cost (Fs) xii. Tax rate (1) xiii. Total amount of financing needed xiv. Internal equity available xv. Capital structure (debt, preferred stock and common stock) Fiscal year is July-June. All values PKR Millions 2021 2020 2019 Sales/Revenue 50,563 43,530 36,961 Sales Growth 16 16% 17.77 18.199 Cost of Goods Sold (COGS) incl. DGA 36,292 31.423 26,866 COGS excluding D&A 35,448 30,611 26,047 Depreciation & Amortization Expense 844 811 819 Depreciation 835 798 806 Amortization of Intangibles 9 13 13 COGS Growth 1550 16.96% 26.73% Gross income 14271 12.108 10.095 Gross Income Growth 17.87% 19.94% 0.22 Gross Profit 28.22 Margin SG&A Expense 6,834 5.943 5,548 Other SG&A 6,834 5943 5,548 SGA Growth 15.00 2015 -2.42% Other Operating Expense 83 128 91 EBIT 7,354 6,037 4,455 Unusual Expense (4) 5 (1) Non Operating Income/Expense 420 333 246 Non-Operating Interest Income 292 443 261 Interest Expense 108 96 Interest Expense Growth 13.199 0.00% Gross Interest Expense 108 96 Pretax income 7,963 6.712 4,964 Pretax income Growth 18.64% 35 20% 634 Pretax Margin 15.75 Income Tax 2.286 1847 1,453 Income Tax Current Domestic 2,330 1911 1,451 Income Tax - Deferred Domestic (64) 3 3 Consolidated Net Income 5,677 4.865 3,511 Net Income 5,677 4,865 3,511 Net Income Growth 16,69 38.57% 7.79% Net Margin 11235 Net Income After Extraordinaries 5,677 4,865 3,511 Net Income Available to Common 5,677 4.865 3,511 EPS (Basic) 7798 66.83 48.23 EPS (Basic) Growth 1669% 38.57% 29.35 Basic Shares Outstanding 73 73 73 EPS (Diluted) 77.98 66.83 48.23 EPS (Diluted) Growth 16.69% 38.57% 29.35% Diluted Shares Outstanding 73 73 73 EBITDA 8,198 6,848 5,274 EBITDA Growth 19.71% 29.84% 5.85% EBITDA Margin 16.21% EBIT 7,354 6,037 4,455
