Question

1 Approved Answer

Posted on Jul 27, 2024

8. Compare the stock prices from the two methods to the actual stock price. What recommenda tions can you make as to whether clients should

8. Compare the stock prices from the two methods to the actual stock price. What recommenda tions can you make as to whether clients should buy or sell Columbia stock based on your price estimates? 9. Explain to your boss why the estimates from the two valuation methods differ, Specifically, ad dress the assumptions implicit in the models themselves as well as those you made in preparing your analysis. Why do these estimates differ from the actual stock price of Columbia 3 4 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity $91.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 4b. Average Divident Payout Rate Average 30.18% 2018 23.36% 2017 48.43% 2016 25.08% 2015 24.90% 2014 29.04% 8% 125.5-year Divident Growth Rate 13 14 Balb. Excel Timeline 15 Dividend 16 6c/d Long-Term Growth Rate of 3% 17 Cost of Equity 18 Price for year four P4 19 6e Current Price(PO) 2019 $1.04 3% 2020 $1.12 2022 $1.31 2023 $1.41 2024 $1.52 $30.47 $26.55 TTM 217a 22 EBITDA (line 21 Pretax income?) 23 Dep & Amort (Cash flow line 7) 24 EBIT 25 Sales (Income stmine 3 Total revenue?) 26 EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year averago 360,717,000 266,734,000 256,898,000 247,319,000 198,521.000 58,230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487.000 206,789,000 196,882,000 190,790,000 144,504.000 2,802,326,000 2.466,105,000 2,377.045.000 2,326,180,000 2.100,500,000 0 .107941403 0.08385247181 0.08202636635 0.08202202753 0.06879210127 000508687398 291,696,000 281,394.000 279650.000 291,687.000 291,563,000 28 Net Property Plant & Equipment (BS line 21) 207178000 + Cash Flow - Shoots - Sheet1 Income Statement Balance Sheet - O Type here to search 7100% $ $.00 123- Default (Ari... 10 -BIGA. B E I E OY- 19 6e Current Price(PO) $26.55 20 21 22 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) EBIT/Sales 24 25 26 TTM 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266,734,000 256,898,000 247,319,000 198,521,000 58,230,000 59,945,000 60,016,000 56,521,000 54,017,000 302,487,000 206,789,000 196,882,000 190,798,000 144,504,000 2,802,326,000 2.466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0.08508687398 28 29 Net Property Plant & Equipment (BS line 21) Sales ii Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326,000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414649200 0.1199998699 1,849,497,000 673,166,000 522,695.000 1,412,023,000 551,389,000 497,783,000 1,249,389,000 369.770.000 513,549,000 1.266,041,000 413.558,000 479,363,000 32 33 34 35 36 37 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 Not working capitalsales 1,765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Balance Sheet Cash Flow Sheets + Sheet1 Income Statement Type here to search 100% - $ $. 00 123- Default (Ari... 10 -BIGA B - I -- 0 EBY. 1960 Current Price(PO) $26.55 20 21 22 24 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) . EBIT/Sales TTM 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266,734,000 256,898,000 247,319,000 198,521,000 58,230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487,000 206,789,000 196,882,000 190,798,000 144,504,000 2,802,326,000 2.466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0.08508687398 26 28 29 Net Property Plant & Equipment (BS line 21) Sales i Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326,000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414449200 0.1199998699 1,849,497,000 673,166,000 522,695,000 1,412,023,000 551,389,000 497,783,000 1,249,389.000 369.770.000 $13,549,000 1.266,041,000 413.558,000 479,363,000 32 33 34 35 36 37 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 Not working capitalsales 1.765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Sheet1 - Income Statement Balance Sheet Cash Flow Sheets Type here to search 8. Compare the stock prices from the two methods to the actual stock price. What recommenda tions can you make as to whether clients should buy or sell Columbia stock based on your price estimates? 9. Explain to your boss why the estimates from the two valuation methods differ, Specifically, ad dress the assumptions implicit in the models themselves as well as those you made in preparing your analysis. Why do these estimates differ from the actual stock price of Columbia 3 4 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity $91.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 4b. Average Divident Payout Rate Average 30.18% 2018 23.36% 2017 48.43% 2016 25.08% 2015 24.90% 2014 29.04% 8% 125.5-year Divident Growth Rate 13 14 Balb. Excel Timeline 15 Dividend 16 6c/d Long-Term Growth Rate of 3% 17 Cost of Equity 18 Price for year four P4 19 6e Current Price(PO) 2019 $1.04 3% 2020 $1.12 2022 $1.31 2023 $1.41 2024 $1.52 $30.47 $26.55 TTM 217a 22 EBITDA (line 21 Pretax income?) 23 Dep & Amort (Cash flow line 7) 24 EBIT 25 Sales (Income stmine 3 Total revenue?) 26 EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year averago 360,717,000 266,734,000 256,898,000 247,319,000 198,521.000 58,230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487.000 206,789,000 196,882,000 190,790,000 144,504.000 2,802,326,000 2.466,105,000 2,377.045.000 2,326,180,000 2.100,500,000 0 .107941403 0.08385247181 0.08202636635 0.08202202753 0.06879210127 000508687398 291,696,000 281,394.000 279650.000 291,687.000 291,563,000 28 Net Property Plant & Equipment (BS line 21) 207178000 + Cash Flow - Shoots - Sheet1 Income Statement Balance Sheet - O Type here to search 7100% $ $.00 123- Default (Ari... 10 -BIGA. B E I E OY- 19 6e Current Price(PO) $26.55 20 21 22 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) EBIT/Sales 24 25 26 TTM 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266,734,000 256,898,000 247,319,000 198,521,000 58,230,000 59,945,000 60,016,000 56,521,000 54,017,000 302,487,000 206,789,000 196,882,000 190,798,000 144,504,000 2,802,326,000 2.466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0.08508687398 28 29 Net Property Plant & Equipment (BS line 21) Sales ii Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326,000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414649200 0.1199998699 1,849,497,000 673,166,000 522,695.000 1,412,023,000 551,389,000 497,783,000 1,249,389,000 369.770.000 513,549,000 1.266,041,000 413.558,000 479,363,000 32 33 34 35 36 37 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 Not working capitalsales 1,765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Balance Sheet Cash Flow Sheets + Sheet1 Income Statement Type here to search 100% - $ $. 00 123- Default (Ari... 10 -BIGA B - I -- 0 EBY. 1960 Current Price(PO) $26.55 20 21 22 24 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) . EBIT/Sales TTM 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266,734,000 256,898,000 247,319,000 198,521,000 58,230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487,000 206,789,000 196,882,000 190,798,000 144,504,000 2,802,326,000 2.466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0.08508687398 26 28 29 Net Property Plant & Equipment (BS line 21) Sales i Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326,000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414449200 0.1199998699 1,849,497,000 673,166,000 522,695,000 1,412,023,000 551,389,000 497,783,000 1,249,389.000 369.770.000 $13,549,000 1.266,041,000 413.558,000 479,363,000 32 33 34 35 36 37 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 Not working capitalsales 1.765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Sheet1 - Income Statement Balance Sheet Cash Flow Sheets Type here to search