Question
Q1. Select any companies of your choice and answer the below questions for the data using MS Excel with step by step calculation for all
Q1. Select any companies of your choice and answer the below questions for the data using MS Excel with step by step calculation for all the five companies in the table.
a) What is the expected dividend in Year 5 for your companies, taking into account the growth of the dividends shown in column g - growth rate?
b) What is the rate of return if companies pay dividends at zero growth rate?
c) What are the dividend yield for your 5 companies?
d) What are the capital gains yield for your 5 companies?
e) What are the required returns on your stocks based on the dividend growth model?
f) What will be the share prices of the selected companies in 3 years, taking into account the dividend growth model, if the required rate of return is 10%? Do not use the current price of the shares (P0) in the calculation.
g) Compare selected companies, indicate the best investment options, and explain your choice.
The selected companies and data are the following:
SYMBOL | NAME | MARKET CAPITALIZATION | P0 - current price | g - growth rate | D0 - just paid annual dividend |
aapl | Apple Inc. Common Stock | 1,960,078,894,620 | $ 457.41 | 1.0% | $ 35.57 |
amat | Applied Materials, Inc. Common Stock | 61,349,123,873 | $ 67.00 | 1.0% | $ 5.42 |
asml | ASML Holding N.V. New York Registry Shares | 158,363,477,900 | $ 382.17 | 3.7% | $ 34.23 |
adsk | Autodesk, Inc. Common Stock | 52,388,015,602 | $ 239.00 | 2.0% | $ 23.77 |
adp | Automatic Data Processing, Inc. Common Stock | 60,048,968,462 | $ 139.66 | 0.5% | $ 11.87 |
Kindly attach the EXCEL sheet calculation step by step for each question also kindly put the final answer of the questions under this table under each company as well.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started