The assignment is based around the following:
With the information given in the Excel Spreadsheet, Perform a DCF Equity Valuation using both methods, additionally conduct a relative valuation analysis for SCB (Standard Chartered Bank)
Please show work in Excel (include formula if possible).
2 Exhibit 1: Income Statement for Standard Chartered Bank (US$) 2.62% 6.35% 8.97% 10.431% 28.71% 35,701,170,000 146,800,000,000 182,501,170,000 5 Interest Income% 6 Interest Income 7 Interest Expense 8 Net Interest Income 9 Non-interest Income 10 Total income 11 Provision for credit losses 12 Gross income 13 Non-interest Expense 14 Others (other impairment and profit from associates) 15 Profit beforetax 16 Provision for income taxes 17 Net Income 18 19 20 21 Exhibit 4: Dividend Discount Model Valuation Assumptions and other Selected Financial Data Data from Case Forecastingfigures 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 Financial Year End: 31-Dec 2.48% 2.48% 2.48% 4.5% 2.5% 4.5% 4.5% 2.5% 4.5% 45% 45% Ordinary Shares Outstanding 2,427,000,000 Risk Premium $ 16,378 $ 12,926 $ 13,500 $ 16,584 S 17,827 $ 17,593 $ 18,029 $ 18,476 $ 18,935 $ 19,787 $ 20,677 $ 21,608 $ 22,580 $ 23,596 $ 24,658 $ 25,767 $ 26,927 $ 28,273 Equity Beta: 1.23 rm 41.35% $ 8,991 $ 5,303 $ 5,030 $ 6,431 $ 7,046 $ 6,437 $ 7,455 $ 7,640 $ 7,829 $ 8,182 $ 8,550 $ 8,935 $ 9,337 $ 9,757 $ 10,196 $ 10,655 $ 11,134 $ 11,691 UK. Government Bond Yield: 2.62% Cost of Equity $ 7.387 $ 7.623 S 8 470 S 10.153 TS 10.781 S 11.156 S 10.574 S 10,836 $ 11,105 $ 11,605 $ 12,127 $ 12,673 $ 13,243 $ 13,839 $ 14,462 $ 15,113 $ 15,793 $ 16,582 Market Risk Premium: 6.35% Taxes 48.04% $ 6,581 $ 7,561 $ 7,592 $ 7,484 $ 8,002 $ 7,621 $ 8,661 $ 8,876 $ 9,096 $ 9,506 $ 9,933 $ 10,380 $ 10,847 $ 11,336 $ 11,846 $ 12,379 $ 12,936 $ 13,583 Dividend per Share in 2014 (Year O): $ 0.86 $ 13,968 $ 15,184 $ 16,062 $ 17,637 $ 18,783 $ 18,777 $ 19,235 $ 19,713 $ 20,201 $ 21,110 $ 22,060 $ 23,053 $ 24,091 $ 25,175 $ 26,307 $ 27,491 $ 28,728 $ 30,165 Book Value per Share: $ 19. 05 T d 8.58% $ 1,321 $ 2,000 $ 883 $ 908 $ 1,196 $ 1,617 $ 1,547 $ 1,585 $ 1,625 $ 1,698 $ 1,774 $ 1,854 $ 1,937 $ 2,025 $ 2,116 $ 2,211 $ 2,310 $ 2,426 Share Price Close(February 16, 2015) $14.71 TV $ 12.647 $ 13.184 S 15.179 S 16.729 S 17.587 S 17.160 $ 17,689 S 18,127 $ 18, 577 $ 19, 413 $ 20,286 $ 21,199 $ 22,153 $ 23,150 $ 24,192 $ 25,280 $ 26,418 $ 27,739 58 47% $ 7,611 $ 7,952 $ 9,023 $ 9,917 $ 10,722 $ 10,193 $ 10,542 $ 10,803 $ 11,071 $ 11,569 $ 12,090 $ 12,634 $ 13,202 $ 13,797 $ 14,417 $ 15,066 $ 15,744 $ 16,531 Year on Year Dividend Growth 1-$ 468 1-6 81 -$ 34 $ 37 - $ 14 - $ 903-$ 1 $ $ 1 $ 2 $ 31 $ 4 S 5 $ 6 $ 7 $ 8 $ 9 $ 10 Year Dividend Dividend Per Share 1 $ 4,568 $ 5,151 $ 6,122 $ 6,775 $ 6,851 $ 6,064 $ 7,146 $ 7,324 $ 7,507 $ 7,845 $ 8,199 $ 8,569 $ 8,956 $ 9,359 $ 9,781 $ 10,222 $ 10,683 $ 11,218 2014 0% 0.86 28.71% LS 1224 1674 IS1708 LS 1842 1S1866 $ 1866 2.052 15 2.103 $ 2.155 $ 2.252 S 2.354 IS 2.460 IS 2.571 1 2.687 1 2808 1$ 2935 $ 3.067 15 3.221 2015 3% 0.98 S3344 S 3477 S 4414 S 4.933 S 4.985 S 4.198 S5.094 S 5221 S 5352S 5.59355845 S 6109 S 6 385 S 6672 S5973 S7287 S 7516 S 7997 2016 3% 0.91 2017 3% 0.94 2018 5% 0.99 2019 5% 1.04 20205 % 1.09 Exihibt 5:Key Financial Figures and Market Comparable Multiples of Selected Publicily Traded Firms 2021 5% 1.14 2022 5% 1.2 2008 2009 2010 2011 2012 2013 Name SCB Royal Bank of Scotland Barclays HSBC UBS I NG Deutsche Bank 2023 5% 1.26 $ 46,583 $ 50,885 $ 52,058 $ 65,981 $ 67,797 $ 83,702 Ticker RBS BARC HSBA UBS INGI 2024 7% 1.35 $ 24,161 $ 18,131 $ 32,724 $ 47,364 $ 60,537 $ 54,534 Stock Exchange London London London London New York New York New York $ 92,575 $ 67,327 $ 79,709 $ 88,845 $ 80,422 $ 93,965 Share Price 956.4 385.6 257 87602. 2 17.19 14.03 32.29 Share price quoted in GBP (pence $ 61,849 $ 69,040 $ 70,967 $ 79,790 $ 95,374 $ 99,888 Market Capitalization 23.21B 43.73B 53.80B 121.52B 64.53B 5 4.08B 44.52B Therefore SCB price is 9.564*1.538-14.71 $ 225,168 $ 205,383 S 2354585 281,980 $ 304,130 $ 332,089 Employees 86,000 110,800 139,600 254,000 60,205 84,718 98,138 atrly revenue Growth (yoyl (%) -9.2 58.9 9.1 11.3 23.8 Revenue(trailing 12 months) 16.18B 143B 24.19B 58.36B 30.23B 29.02B 35.14B 89,817 9 8,563 118,172 133,229 138,733 149,312 Gross Profit (trailing 12 months) 17.03B 11.43B 24. 58.96B N/A 3.89B 2,3252,5232,454 3,8044 ,645 3,967 EBITDA (trailing 12 months) N/A N/A N/A N/A N/A N/A N/A 47,567 57,637 70,662 72,57472,627 69,789 Operating Margin (trailing 12 months)(%) 35.63 -14.71 26.93 3447 16.01 33,097 36,946 46,488 52,676 57,790 60,013 Net Income (trailing 12 months) 4.17B -6.43B 1.03B 15.32B 3.84B 3.03B 1.90B 6,357 7 ,0089 ,388 10,255 11,543 13,630 EPS (trailing 12 months/diluted) 1.7 -0.57 0.06 0.81 0.99 0.37 1.49 P/E (Trailing 12 months, forward) 9.23 0.12 0.1 6.69 11.16 10.02 21.77 179,163 202,677 247,164 2 72,538 285,338 296,711 P/S[ Trailing 12 months) 2.23 3.082 .21 2.07 2 .11 3.39 1.24 4,9854,3856,8065,7485,700 6,003 P/B (most recent quarter) 0.74 0.71 0.89 0.62 1.17 0.92 0.55 174,178| 198,292 240,358 266,790 279,638 290,708 Total Cash 1468B 1 . 486.5B 717.2B964.52B N/A 178.26B 1 ,170B Total Debt 143. 8B 4 63.4B 647.7B 541.46BN /A 205.87B 9 18B 3,5864,1034,5075,0786,6206,903 2,5743,0804,8475,485 4,9575 ,501 3,4663 ,241 2,1272 ,521 2,552 2,510 26,096 22,554 29,263 3 0,832 33,311 36,669 $ 435068 1 $ 436.653 1$ 516560 1 $ 592.686 631208 $ 674.380 STAN 22 Exhibit 2: Balance Sheet for Stanard Chartered Bank (US$) 23 24 Short Term Assets 25 Cash and duefrom banks 26 Federal Funds 27 Other short-term invest metns 28 Invest met n Securities held available for-sale 29 Total Investment DB 30 149 NA 9.55 31 Customer Loans 32 33 34 35 36 37 38 39 40 41 Other Assets 42 43 44 45 46 Commerical Loans Real Estate Construction loans Residential Mortgages Consumer Loans Lease financing (commerical real estate) International Loans Total Customer Loans Impairment provision, customers Loans and advances to cusomters I Premises and equipment Customer libaility on acceptance outstanding Accrued income and prepayments Other Assets Total Assets 2008 2009 2010 2011 2012 2013 30,572 126,726 133,882 Non-Interest-bearing deposits Saving deposits Cusomter Certificats of Deposits Institutional certitficates of Deposits Total-Interest-Bearing Deposits Total deposits Financial Liabilites held at hair value through profit or loss Total deposit liablities 24,195 93,092 145,206 12,084 250,382 274,577 8,660 265,917 40,820 137,255 162,652 5,249 305,156 345,976 10,433 335,543 265,117 295,689 5 ,984 289,705 38,510 1 53,185 193,035 6,502 352,722 391,232 10,210 381,022 42.230 173,103 201,123 6,056 380,282 422,512 13,211 409,301 45,482 181,159 200,142 8,714 390,015 435,497 10,914 424,583 48 49 Deposits 50 51 52 53 54 55 56 57 58 59 Other Liabilities 60 61 62 63 64 65 616 67 68 69 70 71 72 73 74 75 76 77 78 79 Short-term borrowings Acceptance outstanding Accurals and deferred income Other liablit les Total Liabilities 2,5392,9634,7745,473 4,132 4,1134 ,5284 ,458 139,785 111,952 132,850 160,358 412,3731 408,733 | 477,695 1 551,311 | 4,900 4,811 166,141 585,153 5,501 4,668 192,787 627,539 1,207 5,476 18 12,421 478 1,214 5,493 18 Share Capital Share premium account Capital and Capital redemption reserve Merger reserve Available-for-salereserve Cash flow hed gereserve Translation reserve Retained Earnings Parent company shareholders equity Non-controlling interest Total Equity Total Equity and Liabilites - - - 81 948 1,013 1,174 4,743 4,828 5,386 18 18 18 5,4507,28412,421 5 - 93 306 - 83 15 57 - 1,784 - 1,185 - 412 - 12,853 15,460 1 9,260 22,140 | 27,340 38,210 555580653 22,695 | 27,920 38,865| 435.068 1 4366531 516.560 1 1,192 5,432 18 12,421 109 13 1,394 23,167 40,714 6611 41,375 | 5926861 8 85 - 26,566 4 5,362 693 46,055 631208 1. 446 15 2,106 28,745 46,246 595 46,841 674.380 2 Exhibit 1: Income Statement for Standard Chartered Bank (US$) 2.62% 6.35% 8.97% 10.431% 28.71% 35,701,170,000 146,800,000,000 182,501,170,000 5 Interest Income% 6 Interest Income 7 Interest Expense 8 Net Interest Income 9 Non-interest Income 10 Total income 11 Provision for credit losses 12 Gross income 13 Non-interest Expense 14 Others (other impairment and profit from associates) 15 Profit beforetax 16 Provision for income taxes 17 Net Income 18 19 20 21 Exhibit 4: Dividend Discount Model Valuation Assumptions and other Selected Financial Data Data from Case Forecastingfigures 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 Financial Year End: 31-Dec 2.48% 2.48% 2.48% 4.5% 2.5% 4.5% 4.5% 2.5% 4.5% 45% 45% Ordinary Shares Outstanding 2,427,000,000 Risk Premium $ 16,378 $ 12,926 $ 13,500 $ 16,584 S 17,827 $ 17,593 $ 18,029 $ 18,476 $ 18,935 $ 19,787 $ 20,677 $ 21,608 $ 22,580 $ 23,596 $ 24,658 $ 25,767 $ 26,927 $ 28,273 Equity Beta: 1.23 rm 41.35% $ 8,991 $ 5,303 $ 5,030 $ 6,431 $ 7,046 $ 6,437 $ 7,455 $ 7,640 $ 7,829 $ 8,182 $ 8,550 $ 8,935 $ 9,337 $ 9,757 $ 10,196 $ 10,655 $ 11,134 $ 11,691 UK. Government Bond Yield: 2.62% Cost of Equity $ 7.387 $ 7.623 S 8 470 S 10.153 TS 10.781 S 11.156 S 10.574 S 10,836 $ 11,105 $ 11,605 $ 12,127 $ 12,673 $ 13,243 $ 13,839 $ 14,462 $ 15,113 $ 15,793 $ 16,582 Market Risk Premium: 6.35% Taxes 48.04% $ 6,581 $ 7,561 $ 7,592 $ 7,484 $ 8,002 $ 7,621 $ 8,661 $ 8,876 $ 9,096 $ 9,506 $ 9,933 $ 10,380 $ 10,847 $ 11,336 $ 11,846 $ 12,379 $ 12,936 $ 13,583 Dividend per Share in 2014 (Year O): $ 0.86 $ 13,968 $ 15,184 $ 16,062 $ 17,637 $ 18,783 $ 18,777 $ 19,235 $ 19,713 $ 20,201 $ 21,110 $ 22,060 $ 23,053 $ 24,091 $ 25,175 $ 26,307 $ 27,491 $ 28,728 $ 30,165 Book Value per Share: $ 19. 05 T d 8.58% $ 1,321 $ 2,000 $ 883 $ 908 $ 1,196 $ 1,617 $ 1,547 $ 1,585 $ 1,625 $ 1,698 $ 1,774 $ 1,854 $ 1,937 $ 2,025 $ 2,116 $ 2,211 $ 2,310 $ 2,426 Share Price Close(February 16, 2015) $14.71 TV $ 12.647 $ 13.184 S 15.179 S 16.729 S 17.587 S 17.160 $ 17,689 S 18,127 $ 18, 577 $ 19, 413 $ 20,286 $ 21,199 $ 22,153 $ 23,150 $ 24,192 $ 25,280 $ 26,418 $ 27,739 58 47% $ 7,611 $ 7,952 $ 9,023 $ 9,917 $ 10,722 $ 10,193 $ 10,542 $ 10,803 $ 11,071 $ 11,569 $ 12,090 $ 12,634 $ 13,202 $ 13,797 $ 14,417 $ 15,066 $ 15,744 $ 16,531 Year on Year Dividend Growth 1-$ 468 1-6 81 -$ 34 $ 37 - $ 14 - $ 903-$ 1 $ $ 1 $ 2 $ 31 $ 4 S 5 $ 6 $ 7 $ 8 $ 9 $ 10 Year Dividend Dividend Per Share 1 $ 4,568 $ 5,151 $ 6,122 $ 6,775 $ 6,851 $ 6,064 $ 7,146 $ 7,324 $ 7,507 $ 7,845 $ 8,199 $ 8,569 $ 8,956 $ 9,359 $ 9,781 $ 10,222 $ 10,683 $ 11,218 2014 0% 0.86 28.71% LS 1224 1674 IS1708 LS 1842 1S1866 $ 1866 2.052 15 2.103 $ 2.155 $ 2.252 S 2.354 IS 2.460 IS 2.571 1 2.687 1 2808 1$ 2935 $ 3.067 15 3.221 2015 3% 0.98 S3344 S 3477 S 4414 S 4.933 S 4.985 S 4.198 S5.094 S 5221 S 5352S 5.59355845 S 6109 S 6 385 S 6672 S5973 S7287 S 7516 S 7997 2016 3% 0.91 2017 3% 0.94 2018 5% 0.99 2019 5% 1.04 20205 % 1.09 Exihibt 5:Key Financial Figures and Market Comparable Multiples of Selected Publicily Traded Firms 2021 5% 1.14 2022 5% 1.2 2008 2009 2010 2011 2012 2013 Name SCB Royal Bank of Scotland Barclays HSBC UBS I NG Deutsche Bank 2023 5% 1.26 $ 46,583 $ 50,885 $ 52,058 $ 65,981 $ 67,797 $ 83,702 Ticker RBS BARC HSBA UBS INGI 2024 7% 1.35 $ 24,161 $ 18,131 $ 32,724 $ 47,364 $ 60,537 $ 54,534 Stock Exchange London London London London New York New York New York $ 92,575 $ 67,327 $ 79,709 $ 88,845 $ 80,422 $ 93,965 Share Price 956.4 385.6 257 87602. 2 17.19 14.03 32.29 Share price quoted in GBP (pence $ 61,849 $ 69,040 $ 70,967 $ 79,790 $ 95,374 $ 99,888 Market Capitalization 23.21B 43.73B 53.80B 121.52B 64.53B 5 4.08B 44.52B Therefore SCB price is 9.564*1.538-14.71 $ 225,168 $ 205,383 S 2354585 281,980 $ 304,130 $ 332,089 Employees 86,000 110,800 139,600 254,000 60,205 84,718 98,138 atrly revenue Growth (yoyl (%) -9.2 58.9 9.1 11.3 23.8 Revenue(trailing 12 months) 16.18B 143B 24.19B 58.36B 30.23B 29.02B 35.14B 89,817 9 8,563 118,172 133,229 138,733 149,312 Gross Profit (trailing 12 months) 17.03B 11.43B 24. 58.96B N/A 3.89B 2,3252,5232,454 3,8044 ,645 3,967 EBITDA (trailing 12 months) N/A N/A N/A N/A N/A N/A N/A 47,567 57,637 70,662 72,57472,627 69,789 Operating Margin (trailing 12 months)(%) 35.63 -14.71 26.93 3447 16.01 33,097 36,946 46,488 52,676 57,790 60,013 Net Income (trailing 12 months) 4.17B -6.43B 1.03B 15.32B 3.84B 3.03B 1.90B 6,357 7 ,0089 ,388 10,255 11,543 13,630 EPS (trailing 12 months/diluted) 1.7 -0.57 0.06 0.81 0.99 0.37 1.49 P/E (Trailing 12 months, forward) 9.23 0.12 0.1 6.69 11.16 10.02 21.77 179,163 202,677 247,164 2 72,538 285,338 296,711 P/S[ Trailing 12 months) 2.23 3.082 .21 2.07 2 .11 3.39 1.24 4,9854,3856,8065,7485,700 6,003 P/B (most recent quarter) 0.74 0.71 0.89 0.62 1.17 0.92 0.55 174,178| 198,292 240,358 266,790 279,638 290,708 Total Cash 1468B 1 . 486.5B 717.2B964.52B N/A 178.26B 1 ,170B Total Debt 143. 8B 4 63.4B 647.7B 541.46BN /A 205.87B 9 18B 3,5864,1034,5075,0786,6206,903 2,5743,0804,8475,485 4,9575 ,501 3,4663 ,241 2,1272 ,521 2,552 2,510 26,096 22,554 29,263 3 0,832 33,311 36,669 $ 435068 1 $ 436.653 1$ 516560 1 $ 592.686 631208 $ 674.380 STAN 22 Exhibit 2: Balance Sheet for Stanard Chartered Bank (US$) 23 24 Short Term Assets 25 Cash and duefrom banks 26 Federal Funds 27 Other short-term invest metns 28 Invest met n Securities held available for-sale 29 Total Investment DB 30 149 NA 9.55 31 Customer Loans 32 33 34 35 36 37 38 39 40 41 Other Assets 42 43 44 45 46 Commerical Loans Real Estate Construction loans Residential Mortgages Consumer Loans Lease financing (commerical real estate) International Loans Total Customer Loans Impairment provision, customers Loans and advances to cusomters I Premises and equipment Customer libaility on acceptance outstanding Accrued income and prepayments Other Assets Total Assets 2008 2009 2010 2011 2012 2013 30,572 126,726 133,882 Non-Interest-bearing deposits Saving deposits Cusomter Certificats of Deposits Institutional certitficates of Deposits Total-Interest-Bearing Deposits Total deposits Financial Liabilites held at hair value through profit or loss Total deposit liablities 24,195 93,092 145,206 12,084 250,382 274,577 8,660 265,917 40,820 137,255 162,652 5,249 305,156 345,976 10,433 335,543 265,117 295,689 5 ,984 289,705 38,510 1 53,185 193,035 6,502 352,722 391,232 10,210 381,022 42.230 173,103 201,123 6,056 380,282 422,512 13,211 409,301 45,482 181,159 200,142 8,714 390,015 435,497 10,914 424,583 48 49 Deposits 50 51 52 53 54 55 56 57 58 59 Other Liabilities 60 61 62 63 64 65 616 67 68 69 70 71 72 73 74 75 76 77 78 79 Short-term borrowings Acceptance outstanding Accurals and deferred income Other liablit les Total Liabilities 2,5392,9634,7745,473 4,132 4,1134 ,5284 ,458 139,785 111,952 132,850 160,358 412,3731 408,733 | 477,695 1 551,311 | 4,900 4,811 166,141 585,153 5,501 4,668 192,787 627,539 1,207 5,476 18 12,421 478 1,214 5,493 18 Share Capital Share premium account Capital and Capital redemption reserve Merger reserve Available-for-salereserve Cash flow hed gereserve Translation reserve Retained Earnings Parent company shareholders equity Non-controlling interest Total Equity Total Equity and Liabilites - - - 81 948 1,013 1,174 4,743 4,828 5,386 18 18 18 5,4507,28412,421 5 - 93 306 - 83 15 57 - 1,784 - 1,185 - 412 - 12,853 15,460 1 9,260 22,140 | 27,340 38,210 555580653 22,695 | 27,920 38,865| 435.068 1 4366531 516.560 1 1,192 5,432 18 12,421 109 13 1,394 23,167 40,714 6611 41,375 | 5926861 8 85 - 26,566 4 5,362 693 46,055 631208 1. 446 15 2,106 28,745 46,246 595 46,841 674.380
