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) use financial statment of Colgate-Palmolive (Pakistan).XB Statement of Financial Position As at June 30, 2020 Note 2020 2019 (Rupees in '000) WOO 4,291,939 14.435 41.730 19,045 4.367.149 89212 10 11 5,102,949 13,675 33,176 21.127 5,170,927 348,815 le. 17/3,69 837,141 126.670 93.847 407,416 3,695 978,164 6. 30.408 2.339.835 176337 687 22.508.614 13 267,753 4,974,814 749.550 187.239 56,962 298,915 14,167 1,317.968 5,546,618 1.214300 14.628.286 18.995/45 14 15 ASSETS NON-CURRENT ASSETS Property, plant and equipment Intangible assets Long term loans Long term security deposits CURRENT ASSETS Stores and spares Stock in trade Trade debts Loans and advances Trade deposits and short term prepayments Other receivables Accrued profit Taxation Short term investments Cash and bank balances TOTAL ASSETS EQUITY AND LIABILITIES SHARE CAPITAL AND RESERVES Authorised share capital Issued, subscribed and paid-up share capital Reserves Remeasurement of post retirement benefits obligation Surplus / (deficit) on revaluation of investments LIABILITIES NON-CURRENT LIABILITIES Deferred taxation Long term deposits Deferred liability Long-term financing Lease liabilities CURRENT LIABILITIES Trade and other payables Accrued mark up Current portion of long-term financing Current portion of lease liabilities Unclaimed dividend 16 16 17 750,QOC 575,459 16.863,937 750,000 575,459 14,444,822 (147,819) (4.217 14.868.245 (188,474 259 17.251,181 18 19 20 21 22 103,348 112,613 47,293 273,732 3119.022 856.008 164.709 86 304 90.112 341,125 3,763.934 23 21 22 14.308.BC4 121 39,105 31.757 21.638 4,401.425 22.131 3,786,065 4.127.190 TOTAL LIABILITIES TOTAL EQUITY AND LIABILITIES CONTINGENCIES AND COMMITMENTS 5,257,433 22,508,614 18.995455 25 Statement of Profit or Loss and other Comprehensive Income For the year ended June 30, 2020 Note 2020 2019 (Rupees in '000) 57,870,219 48,718,781 (9,175.788) (7.718,475) (5.164,109) (4.039.264) 43,530,322 36,961,042 (30,912,914) (26,490,454) 12,617,408 10,470,588 26 Turnover Sales tax Trade and other discounts Net turnover Cost of sales Gross profit Selling and distribution cost Administrative expenses Other expenses Other income Profit from operations Finance cost and bank charges Profit before taxation 27 28 29 30 (5,503,832) (581,540) (724,664) 1,000.208 6,807,580 (5.132,341) (520,066) (428,655) 613,160 5,002.686 31 (95.813) 6,711.767 (38,496) 4,964,190 32 (1,846,951) 4,864,816 (1,453,387) 3,510,803 Taxation Profit after taxation Other comprehensive income / (loss) for the year - net of tax Item that may be reclassified subsequently to profit or loss Gain / (loss) on investments categorised as "fair value through other comprehensive income Impact of tax 6,729 (2,253) 4,476 (4.652) 1,379 (3.273) Item that will not be reclassified subsequently to profit or loss Remeasurement of post retirement benefits obligation Impact of tax (57,260) 16,605 (40,655) (49,198) 14,267 (34,931) (36,179) (38,204) Total comprehensive income for the year 4.828.637 3.472599 -Rupees--- Earnings per share - basic and dilutive 33 84.54 61.01 Statement of Changes in Equity For the year ended June 30, 2020 Issued subscribed and paid up share capital Capital reserve share premium Reserves Remeasurem- Revenue reserves Surplus/ ent on post Ideficiton retirement Unappro- Sub total Total revaluation of benefits General Equity priated reserves investments reserve obligation profit net of tax net of tax -Rupees in DOO 10.396.000 2.539.080 12.829.538 112.888 944) 13.304,250 479,549 13.456 Balance as at July 1, 2018 Transactions with owners Final dividend for the year ended June 30, 2018 at the rate of Rs 20 pesh Bonus shares issued at the rate of one share for every five shares held 959.099 959.000 1959.000 95.910 $5,910 (95.910 949.8081 1949.50 2.004517) 2004,517) 1949 50 (1.908.607 $5,910 3.510,800 3,510.803 Interim dividend for the year ended June 30, 2019 at the rate of Rs 16.5 per share Total transactions with owners Comprehensive income for the year Profit after taxation for the year ended June 30, 2019 Other comprehensive loss Total comprehensive income for the year ended June 30, 2019 Transfer to general reserve Balaron as at June 30, 2019 3.510.803 38.2014) 34,931 3.273 3.510,803 3,510,803 (34 901 3.273 3472,500 1483.000 (1.483,000 11.800.000 2.582.360 14,444,822 575,459 13.46 (147.819 14.868245 Transactions with owners Final dividend for the year ended June 30, 2019 at the rate of Rs 20 per share Interim dividend for the year ended June 30, 2020 at the rate of Rs 22,50 per share (1,150.918 || 1.150,918 11.150.918 L1.294.70.294,783 1297 Total transactions with owners 2.445,701) 2.445,701 2.445.7011 4864 816 4,854.816 4964.816 4.476 36.179 Comprehensive income for the year Profit after taxation for the year ended June 30, 2020 Other comprehensive income Total comprehensive income for the year ended June 30, 2020 Transfer to general VO Balance as at June 30, 2020 4.864 816 4,864 816 40.665 4.476 4828.637 1411,000 (1411,000 575,459 13.456 13.280.000 3.570.481 16.863.937 (188.474 269 17251.181 Statement of Cash Flows For the year ended June 30, 2020 Note 2020 2019 (Rupees in '000) 34 6,276,130 (43,102) (1,552,156) 8,554 (2,082) (142,967) 26,309 4,570,686 5,087,675 (38.496) (2.231,408) 921 (1.397) 242 2,817.537 CASH FLOWS FROM OPERATING ACTIVITIES Cash generated from operations Finance cost paid Taxes paid Long term loans Long term security deposits (assets) Staff retirement gratuity paid Long term deposits Net cash generated from operating activities CASH FLOWS FROM INVESTING ACTIVITIES Payments for property, plant and equipment Purchase of intangible assets Short term investments made during the year Proceeds from sale of property, plant and equipment Profit received on savings accounts Profit received on treasury bills Profit received on Sukuk bonds Profit received on Pakistan Investment Bonds Profit received on term deposit receipts Sale proceeds on disposal of short term investments Net cash generated from / (used in) investing activities CASH USED IN FINANCING ACTIVITIES Long-term finances Payment of lease liabilities Dividend paid Net cash used in financing activities Net increase / decrease in cash and cash equivalents during the year Cash and cash equivalents at beginning of the year Cash and cash equivalents at end of the year (1.297,132) (12,633) (21,802,438) 62,101 126,057 217,467 1,011 19.763 89,108 22.885,439 288,743 (1.382,021) (2.567) (21,032,596) 70,230 60,602 111,818 9,672 27.124 52,204 19,433,381 (2.652,153) 312,837 (56,537) (2,446,194) (2,189,894) (1,899.123 (1.899.123 2.669,535 (1.733.739) 2,557,300 5.226.835 4.291.039 2.557.300 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 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 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) 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) use financial statment of Colgate-Palmolive (Pakistan).XB Statement of Financial Position As at June 30, 2020 Note 2020 2019 (Rupees in '000) WOO 4,291,939 14.435 41.730 19,045 4.367.149 89212 10 11 5,102,949 13,675 33,176 21.127 5,170,927 348,815 le. 17/3,69 837,141 126.670 93.847 407,416 3,695 978,164 6. 30.408 2.339.835 176337 687 22.508.614 13 267,753 4,974,814 749.550 187.239 56,962 298,915 14,167 1,317.968 5,546,618 1.214300 14.628.286 18.995/45 14 15 ASSETS NON-CURRENT ASSETS Property, plant and equipment Intangible assets Long term loans Long term security deposits CURRENT ASSETS Stores and spares Stock in trade Trade debts Loans and advances Trade deposits and short term prepayments Other receivables Accrued profit Taxation Short term investments Cash and bank balances TOTAL ASSETS EQUITY AND LIABILITIES SHARE CAPITAL AND RESERVES Authorised share capital Issued, subscribed and paid-up share capital Reserves Remeasurement of post retirement benefits obligation Surplus / (deficit) on revaluation of investments LIABILITIES NON-CURRENT LIABILITIES Deferred taxation Long term deposits Deferred liability Long-term financing Lease liabilities CURRENT LIABILITIES Trade and other payables Accrued mark up Current portion of long-term financing Current portion of lease liabilities Unclaimed dividend 16 16 17 750,QOC 575,459 16.863,937 750,000 575,459 14,444,822 (147,819) (4.217 14.868.245 (188,474 259 17.251,181 18 19 20 21 22 103,348 112,613 47,293 273,732 3119.022 856.008 164.709 86 304 90.112 341,125 3,763.934 23 21 22 14.308.BC4 121 39,105 31.757 21.638 4,401.425 22.131 3,786,065 4.127.190 TOTAL LIABILITIES TOTAL EQUITY AND LIABILITIES CONTINGENCIES AND COMMITMENTS 5,257,433 22,508,614 18.995455 25 Statement of Profit or Loss and other Comprehensive Income For the year ended June 30, 2020 Note 2020 2019 (Rupees in '000) 57,870,219 48,718,781 (9,175.788) (7.718,475) (5.164,109) (4.039.264) 43,530,322 36,961,042 (30,912,914) (26,490,454) 12,617,408 10,470,588 26 Turnover Sales tax Trade and other discounts Net turnover Cost of sales Gross profit Selling and distribution cost Administrative expenses Other expenses Other income Profit from operations Finance cost and bank charges Profit before taxation 27 28 29 30 (5,503,832) (581,540) (724,664) 1,000.208 6,807,580 (5.132,341) (520,066) (428,655) 613,160 5,002.686 31 (95.813) 6,711.767 (38,496) 4,964,190 32 (1,846,951) 4,864,816 (1,453,387) 3,510,803 Taxation Profit after taxation Other comprehensive income / (loss) for the year - net of tax Item that may be reclassified subsequently to profit or loss Gain / (loss) on investments categorised as "fair value through other comprehensive income Impact of tax 6,729 (2,253) 4,476 (4.652) 1,379 (3.273) Item that will not be reclassified subsequently to profit or loss Remeasurement of post retirement benefits obligation Impact of tax (57,260) 16,605 (40,655) (49,198) 14,267 (34,931) (36,179) (38,204) Total comprehensive income for the year 4.828.637 3.472599 -Rupees--- Earnings per share - basic and dilutive 33 84.54 61.01 Statement of Changes in Equity For the year ended June 30, 2020 Issued subscribed and paid up share capital Capital reserve share premium Reserves Remeasurem- Revenue reserves Surplus/ ent on post Ideficiton retirement Unappro- Sub total Total revaluation of benefits General Equity priated reserves investments reserve obligation profit net of tax net of tax -Rupees in DOO 10.396.000 2.539.080 12.829.538 112.888 944) 13.304,250 479,549 13.456 Balance as at July 1, 2018 Transactions with owners Final dividend for the year ended June 30, 2018 at the rate of Rs 20 pesh Bonus shares issued at the rate of one share for every five shares held 959.099 959.000 1959.000 95.910 $5,910 (95.910 949.8081 1949.50 2.004517) 2004,517) 1949 50 (1.908.607 $5,910 3.510,800 3,510.803 Interim dividend for the year ended June 30, 2019 at the rate of Rs 16.5 per share Total transactions with owners Comprehensive income for the year Profit after taxation for the year ended June 30, 2019 Other comprehensive loss Total comprehensive income for the year ended June 30, 2019 Transfer to general reserve Balaron as at June 30, 2019 3.510.803 38.2014) 34,931 3.273 3.510,803 3,510,803 (34 901 3.273 3472,500 1483.000 (1.483,000 11.800.000 2.582.360 14,444,822 575,459 13.46 (147.819 14.868245 Transactions with owners Final dividend for the year ended June 30, 2019 at the rate of Rs 20 per share Interim dividend for the year ended June 30, 2020 at the rate of Rs 22,50 per share (1,150.918 || 1.150,918 11.150.918 L1.294.70.294,783 1297 Total transactions with owners 2.445,701) 2.445,701 2.445.7011 4864 816 4,854.816 4964.816 4.476 36.179 Comprehensive income for the year Profit after taxation for the year ended June 30, 2020 Other comprehensive income Total comprehensive income for the year ended June 30, 2020 Transfer to general VO Balance as at June 30, 2020 4.864 816 4,864 816 40.665 4.476 4828.637 1411,000 (1411,000 575,459 13.456 13.280.000 3.570.481 16.863.937 (188.474 269 17251.181 Statement of Cash Flows For the year ended June 30, 2020 Note 2020 2019 (Rupees in '000) 34 6,276,130 (43,102) (1,552,156) 8,554 (2,082) (142,967) 26,309 4,570,686 5,087,675 (38.496) (2.231,408) 921 (1.397) 242 2,817.537 CASH FLOWS FROM OPERATING ACTIVITIES Cash generated from operations Finance cost paid Taxes paid Long term loans Long term security deposits (assets) Staff retirement gratuity paid Long term deposits Net cash generated from operating activities CASH FLOWS FROM INVESTING ACTIVITIES Payments for property, plant and equipment Purchase of intangible assets Short term investments made during the year Proceeds from sale of property, plant and equipment Profit received on savings accounts Profit received on treasury bills Profit received on Sukuk bonds Profit received on Pakistan Investment Bonds Profit received on term deposit receipts Sale proceeds on disposal of short term investments Net cash generated from / (used in) investing activities CASH USED IN FINANCING ACTIVITIES Long-term finances Payment of lease liabilities Dividend paid Net cash used in financing activities Net increase / decrease in cash and cash equivalents during the year Cash and cash equivalents at beginning of the year Cash and cash equivalents at end of the year (1.297,132) (12,633) (21,802,438) 62,101 126,057 217,467 1,011 19.763 89,108 22.885,439 288,743 (1.382,021) (2.567) (21,032,596) 70,230 60,602 111,818 9,672 27.124 52,204 19,433,381 (2.652,153) 312,837 (56,537) (2,446,194) (2,189,894) (1,899.123 (1.899.123 2.669,535 (1.733.739) 2,557,300 5.226.835 4.291.039 2.557.300 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 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 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)