The following Spreadsheet is an example of such a model. Column B contains the inputs we...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
The following Spreadsheet is an example of such a model. Column B contains the inputs we have used so far for Honda. Column E contains dividend forecasts. In cells E2 through E5 we present the Value Line estimates for the next 4 years. Dividend growth in this period is about 8.6% annually. Rather than assume a sudden transition to constant dividend growth starting in 2016, we assume instead that the dividend growth rate in 2016 will be 8.6% and that it will decline steadily through 2027, finally reaching the constant terminal growth rate of 7.5% (see column F). Each dividend in the transition period is the previous year's dividend times that year's growth rate. Terminal value once the firm enters a constant-growth stage (cell G17) is computed from the constant-growth DDM. Finally, investor cash flow in each period (column H) equals dividends in each year plus the terminal value in 2027. The present value of these cash flows is computed in cell H19 as $40.29, about 5% higher than the value we found from the two-stage model. We obtain a greater intrinsic value in this case because we assume that dividend growth only gradually declines to its steady-state value. D 10 Inputs 11 beta 12 mat prem 13 f equity 14 15 plowback 16 ro 17 tem gwth 18 Value ine 22 forecasts of 23 annual dividends 24 25 26 Transitional period 27 with slowing dividend growth 0.95 0.08 0.02 -813-811812 0.75 0.1 816815 Beginning of cont growth period C D Year 20/2 0.78 2013 0.85 2014 0.92 2015 1 Dividend 2016 XETAITHF15) 2017 E15(1-F10) 2018 E16*(1+F17) 2019 2020 2021 E19 (1+F20) 2022 -E20(1+F21) 2023 -E21*(1+F22) 2024 -E22*(1+F23) 2025 2030 2027 The market risk premium is 8.5%. -E17 (1+F18) -E18 (1+F19) -E23 (1+F24) -E24 (1+F25) E25*(1+F26) Div growth E14/E11)(1/3)-1 -F15-FS15-$F$25y10 F16-3FS15-5F5251 F17-SFS15-4F5251 F18-(SFS15-8F5251 -F19-(SFS15-5F5251 -F20-(SFS15-5F5251 -F21-(SFS15-SFS251 -F22-(SFS15-4F5251 -F23-(SFS15-5F5251 term_g -817 Tem value +E28"(1+F26y_eq-tem_g) H Betum to Main Menu Investor CF *11+011 -E12 G12 -E13-013 *E14+G14 -E15+15 -E16 G16 -E17.G17 E18 G18 -E19+G19 -E20-020 -E21+021 -E22-022 E23-G23 -E24+G24 -E25+025 -E26+G26 NPV(Keq,H11:26) Help with Excers NPV Function Build the spreadsheet as per image above. Recalculate the intrinsic value of Honda in each of the following scenarios by using the three-stage growth model. Treat each scenario independently. a. ROE in the constant-growth period will be 9%. b. Honda's actual beta is 1.0. -PV of CF The following Spreadsheet is an example of such a model. Column B contains the inputs we have used so far for Honda. Column E contains dividend forecasts. In cells E2 through E5 we present the Value Line estimates for the next 4 years. Dividend growth in this period is about 8.6% annually. Rather than assume a sudden transition to constant dividend growth starting in 2016, we assume instead that the dividend growth rate in 2016 will be 8.6% and that it will decline steadily through 2027, finally reaching the constant terminal growth rate of 7.5% (see column F). Each dividend in the transition period is the previous year's dividend times that year's growth rate. Terminal value once the firm enters a constant-growth stage (cell G17) is computed from the constant-growth DDM. Finally, investor cash flow in each period (column H) equals dividends in each year plus the terminal value in 2027. The present value of these cash flows is computed in cell H19 as $40.29, about 5% higher than the value we found from the two-stage model. We obtain a greater intrinsic value in this case because we assume that dividend growth only gradually declines to its steady-state value. D 10 Inputs 11 beta 12 mat prem 13 f equity 14 15 plowback 16 ro 17 tem gwth 18 Value ine 22 forecasts of 23 annual dividends 24 25 26 Transitional period 27 with slowing dividend growth 0.95 0.08 0.02 -813-811812 0.75 0.1 816815 Beginning of cont growth period C D Year 20/2 0.78 2013 0.85 2014 0.92 2015 1 Dividend 2016 XETAITHF15) 2017 E15(1-F10) 2018 E16*(1+F17) 2019 2020 2021 E19 (1+F20) 2022 -E20(1+F21) 2023 -E21*(1+F22) 2024 -E22*(1+F23) 2025 2030 2027 The market risk premium is 8.5%. -E17 (1+F18) -E18 (1+F19) -E23 (1+F24) -E24 (1+F25) E25*(1+F26) Div growth E14/E11)(1/3)-1 -F15-FS15-$F$25y10 F16-3FS15-5F5251 F17-SFS15-4F5251 F18-(SFS15-8F5251 -F19-(SFS15-5F5251 -F20-(SFS15-5F5251 -F21-(SFS15-SFS251 -F22-(SFS15-4F5251 -F23-(SFS15-5F5251 term_g -817 Tem value +E28"(1+F26y_eq-tem_g) H Betum to Main Menu Investor CF *11+011 -E12 G12 -E13-013 *E14+G14 -E15+15 -E16 G16 -E17.G17 E18 G18 -E19+G19 -E20-020 -E21+021 -E22-022 E23-G23 -E24+G24 -E25+025 -E26+G26 NPV(Keq,H11:26) Help with Excers NPV Function Build the spreadsheet as per image above. Recalculate the intrinsic value of Honda in each of the following scenarios by using the three-stage growth model. Treat each scenario independently. a. ROE in the constant-growth period will be 9%. b. Honda's actual beta is 1.0. -PV of CF
Expert Answer:
Answer rating: 100% (QA)
Based on the provided spreadsheet image the task is to recalculate the intrinsic value per share of Honda as follows 1 Predicting Net Income for Years ... View the full answer
Related Book For
Entrepreneurial Finance
ISBN: 978-1305968356
6th edition
Authors: J. Chris Leach, Ronald W. Melicher
Posted Date:
Students also viewed these finance questions
-
KYC's stock price can go up by 15 percent every year, or down by 10 percent. Both outcomes are equally likely. The risk free rate is 5 percent, and the current stock price of KYC is 100. (a) Price a...
-
As an alternative alphanumeric code, consider a code where punched holes in the columns of a card represent alphanumeric codes. The punched hole represents a 1; all other bits are 0. The Hollerith...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Baxter Ltd requires a new machine to use in the manufacture of a new product. Two machines are available: Big Gee and Maxi-Shadbolt. Baxter Ltd. depreciates machinery using the straight-line method....
-
Interpreting disclosures of long-term debt. Exhibit 10.20 presents excerpts from the notes to the financial statements of Lowes. a. The amounts shown for Debentures, Notes, and the Medium-Term Notes...
-
Is an equation of state valid in the two-phase regions?
-
The members of the truss are assumed to be pin connected. If member \(G F\) is an \(\mathrm{A}-36\) steel rod having a diameter of 2 in., determine the greatest magnitude of load \(\mathbf{P}\) that...
-
Rowntown Cab Company has 70 drivers that it must schedule in three 8-hour shifts. However, the demand for cabs in the metropolitan area varies dramatically according to the time of day. The slowest...
-
Discuss the role of daemon processes and background services in operating systems. How do these processes differ from regular user processes, and what best practices are followed to ensure their...
-
You receive the following letter. Quark Pty Ltd. 500 Mowbray ROAD LANE COVE, NSW 2066 8 Sept 2018 Greetings, Quark Pty Ltd is a trader of machine parts. Its annual turnover in the last five years...
-
Capital Inc. has prepared the operating budget for the first quarter of 2015. They forecast sales of $50,000 in January, $60,000 in February, and $70,000 in March. Variable and fixed expenses are as...
-
salary information for the cities of Boston, MA = $58,048 Omaha, NE=$48,709 San Diego, CA=$5,5599 1. What is the difference between the median base salary for the job in each of these cities and the...
-
The meadows Company produced three joint products at a joint cost of P132,000. Additional information for a recent period is as follows: Sales value and additional Processing if Processed Product...
-
Walker Limited wants to incorporate the effects of learning into the cost of producing a component used in the laser printers it sells to commercial print shops. The standard cost of direct materials...
-
a. In your own words, briefly explain the concept of integrated reporting promoted by the International Integrated Reporting Commission/Committee (IIRC). Discuss whether such reporting is preferable...
-
Imagine are an office manager. You have been tasked with creating a new policy by merging and updating two office financial and collection policies. Policy 6.9, Managing Incoming Cash & Policy 13.19,...
-
Hydraulic residence time is defined as the average amount of time that a molecule of water will spend in a treatment basin before reaching the outflow. It is calculated by dividing the the volume of...
-
Danielle has an insurance policy with a premium of $75 per month. In September she is in an accident and receives a bill worth $2990 for the repair of her own property. Her deductible is $250 and her...
-
Kaj Rasmussen founded Scandi Home Furnishings as a corporation during mid-2013. Sales during the first full year (2014) of operation reached $1.3 million. Sales increased by 15 percent in 2015 and...
-
Describe examples of customer funding used to reduce financing needs.
-
What are unicorns? How might their exit values be impacted when they go public?
-
The rubber block is fixed along edge AB, and edge CD is moved so that the vertical displacement of any point in the block is given by v(x) = (vo/b 3 )x 3 . Determine the shear strain Y xy = 0.02 at...
-
A concrete cylinder having a diameter of 6.00 in. and gauge length of 12 in. is tested in compression. The results of the test are reported in the table as load versus contraction. Draw the...
-
A tension test was performed on a steel specimen having an original diameter of 12.5mm and gage length of 50mm. Using the data listed in the table, plot the stress-strain diagram, and determine...
Study smarter with the SolutionInn App