financial modeling forecast

dhruv12

Main

Home >Homework Answsers >Business & Finance homework help >Accounting homework help

3 years ago
11.10.2023
30

Report issue

files (4)

Project1InstructionsDueDateFall2023.docx
Project1Fall2023DividendData.xlsm
Project1Fall2023Questions.docx
Project1Fall2023StockReturnData.xlsm

Project1InstructionsDueDateFall2023.docx

Project 1 Instructions and Due Date

(FIN 4453 – Gary Smith)

1) Project 1 materials may be found in the Module: Project 1 Fall 2023 on the Canvas course site.

2) There are 16 questions on Project 1. Some of the questions have multiple parts. Be sure to answer each part of each question. Clearly identify your answer to each part of each question.

3) Due Date: By 12:00 noon on Thursday, October 12, 2023.

4) You are expected to work on this project by yourself. You may consult with me if you have any questions.

5) Please provide me a Printed (no electronic copies will be accepted) copy of each problem in Project 1.

6) The Printed copy should be formatted and assembled in the following manner:

a. Staple all problems together so they are not lost or separated

b. Print your name, course number (FIN 4453), and section number at the top of each page

c. Print Row and Column Headings for each problem as demonstrated in class

d. Print out formulas used in each problem as demonstrated in class

e. Clearly identify your answer to each part of each question.

f. If you use Goal Seek to determine an answer, please identify the

i. Set Cell

ii. To Value

iii. By Changing Cell

g. If you use Solver to determine an answer, please identify the

i. Set Objective (Cell)

ii. To Value

iii. By Changing Variables Cell

iv. Subject to (if used)

7) Please format and present the problem solutions is such a manner that another individual would be able to look at your solutions and follow your logic in solving the problem. If you were not available, then another individual should be able to solve a similar problem by following the logic of your solution.

8) In order to achieve the above, you may need to document and comment on your solution logic.

9) The project will be graded on the basis of correct solutions, presentation and formatting of solutions, and the explanation of solutions. You should view this as a professional report you would submit to your supervisor in a professional setting. Be sure to use proper grammar in your explanations.

Project1Fall2023DividendData.xlsm

            Dividend Data

                        Dividend Data
                    
                        Semi-Annual Period	
                        Dividend per share
                    
                        1	
                        $0.55
                    
                        2	
                        $0.55
                    
                        3	
                        $0.55
                    
                        4	
                        $0.83
                    
                        5	
                        $0.83
                    
                        6	
                        $0.83
                    
                        7	
                        $0.83
                    
                        8	
                        $1.03
                    
                        9	
                        $1.03
                    
                        10	
                        $1.23
                    
                        11	
                        $1.23
                    
                        12	
                        $1.23
                    
                        13	
                        $1.48
                    
                        14	
                        $1.48
                    
                        15	
                        $1.48
                    
                        16	
                        $1.48
                    
                        17	
                        $1.78
                    
                        18	
                        $1.78
                    
                        19	
                        $1.78
                    
                        20	
                        $2.03
                    
                        21	
                        $2.03
                    
                        22	
                        $2.03
                    
                        23	
                        $2.03
                    
                        24	
                        $2.03
                    
                        25	
                        $2.25
                    
                        26	
                        $2.25
                    
                        27	
                        $2.25
                    
                        28	
                        $2.25
                    
                        29	
                        $2.65
                    
                        30	
                        $2.65
                    
                        31	
                        $2.65
                    
                        32	
                        $2.65
                    
                        33	
                        $2.65
                    
                        34	
                        $2.65
                    
                        35	
                        $2.65
                    
                        36	
                        $2.95
                    
                        37	
                        $2.95
                    
                        38	
                        $2.95
                    
                        39	
                        $2.95
                    
                        40	
                        $3.30
                    
                        41	
                        $3.30
                    
                        42	
                        $3.30
                    
                        43	
                        $3.30
                    
                        44	
                        $3.70
                    
                        45	
                        $3.70
                    
                        46	
                        $3.70
                    
                        47	
                        $3.70
                    
                        48	
                        $4.40
                    
                        49	
                        $4.40
                    
                        50	
                        $4.40

Project1Fall2023Questions.docx

FIN 4453 – PROJECT 1 (16 Questions)

FALL 2023

1. The current risk-free rate of interest is 3% and the expected return on the market is 10%.

a. Using EXCEL’s Data Table feature create a two-way data table to determine the cost of equity by varying both Beta and the risk-free rate of interest. Use Beta values of 0.70, 0.85, 1.00, 1.15, 1.30, and 1.45, and risk-free interest rate values of 4%, 5%, 6%, 7%, and 8%.

b. Explain how the cost of equity is impacted by:

i. A change in Beta

ii. A change in the risk-free interest rate.

2. A rapidly growing firm is currently paying a dividend of $2.50. The annual dividend growth rate is expected to be 8% for the next year, then 6% for the next 2 years, then 4% for the next 3 years, and 3% thereafter. The expected return on the market is 7%, the risk-free rate is 2% and the firm’s Beta is 1.25.

a. Calculate the estimated price (intrinsic value) for a share of this firm’s stock.

b. If an analyst uses an 8% rule, is this stock overvalued, undervalued, or fairly priced if the current stock price is $57?

c. Using EXCEL’s Text Box Feature explain the purpose of employing a 8% rule in this valuation process?

3. The Excel file Stock Return Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).

a. Compute the Beta for Dell using the Variance-Covariance relationship.

b. Compute the annualized return on a Compounded Basis for Dell.

c. Compute the annual standard deviation for Dell.

4. The Excel file Stock Return Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).

a. Compute the equation of the Characteristic Line for Cin.

b. Explain what the intercept term in this equation measures relative to the market return.

c. Explain what the slope term in this equation measures relative to the market return.

d. Conduct a complete hypothesis test to determine if  Significant.

e. Conduct a complete hypothesis test to determine if β is Significant.

f. Compute the R2 value associated with this equation.

g. Explain what R2 measures for this equation.

h. Construct a graph of the Characteristic Line for Cin.

i. On this graph (using EXCEL’s graphing features) include a trendline, the computed equation, and the R2 value.

ii. Label the axes and include a title for the graph.

5. An investment has the following cash flows:

May 22, 2015 $ -1600

January 20, 2016 $ +1200

August 9, 2017 $ +800

February 10, 2018 $ +700

July 27, 2019 $ +900

December 15, 2020 $ +200

June 24, 2021 $ -2400

a. Calculate the Net Present Value of this investment. Assume the annual discount rate is 8%.

b. Create a data table and graph illustrating the impact of the discount rate on the Net Present Value of this investment. Include a title and label the axes for the graph.

c. Use Solver to determine the Internal Rate of Return(s) associated with this investment.

6. Suppose the Price/Earnings Ratio for the S&P 500 is 21 and the dividend payout ratio of the S&P 500 is 45%. The future growth rate of dividends is expected to be 4.75%.

a. Use Goal Seek or Solver to determine the dividend growth rate that would yield an expected Market return of 8%.

7. An investment has a cost of $5600. The investment will have a payout of X at the end of the first year. This initial payout X will grow at the rate of 8% per year for the next 2 years, then by 6% per year for the next 4 years, then by 5% for the next 2 years, and then at the rate of 3% per year for the following 3 years. You believe the riskiness of this investment is 10%.

a. Calculate the smallest X that would entice you to invest.

8. You are considering buying a risky bond. The bond has a $1,000 face value, a 1-year maturity, and a coupon rate of 14%. Coupon payments are made annually. You believe the probability the company will survive to pay off the bond is 55%. You also believe there is a 45% probability the company will default within 2 months, in which case you will be able to recover 36% of the bond’s face value at the end of year 1. The bond is selling for $725.

a. Calculate the expected return on this bond.

b. Use Goal Seek or Solver to determine the probability of survival that would yield an expected return of 12%.

9. An employee plans to invest in a retirement fund at the beginning of each of the next 15 years. The employee will initially invest $30,000 in the first year and increase the annual investment amount by 4% in each subsequent year. The employee believes she will earn 12% annually on her investments in the first 2 years, then 9% annually in the next 4 years, then 7% annually in the next 3 years, and then 4% annually in each of the final 6 years before she retires.

a. How much money will the employee have in the retirement fund when she retires?

b. What would be the internal rate of return associated with her investments and her final retirement position?

10. A college savings education plan claims that had you invested $900 quarterly in the plan for the last 25 years you would have accumulated $450,000. Assuming the investments were made at the beginning of each quarterly period, calculate the effective compound annual rate of return an investor would have received using either Goal Seek or Solver.

11. This question relates to the computation of a firm’s cost of equity.

a. Assume the current selling price of a firm’s stock is: $125. Using the Excel file Dividend Data, compute the firm’s annualized cost of equity using the Gordon dividend model.

12. Compute the Present Value of the following cash flows using a continuously compounded annual interest rate of 8.0%.

Year Cash Flows

0.85 500

1.30 745

2.45 465

3.75 675

4.60 300

5.28 800

13. Suppose at time 0 you had $3500 in the bank and 21 years later you had $12,500. Use Solver to calculate the effective annual rate of return if the bank pays interest continuously.

14. If today is May 18, 2016, and the cost of capital r = 5%, what is the net present value of the following cash flows as of May 18, 2016:

06/25/17 $ -1,200

08/08/18 $ +700

10/22/19 $ +2200

06/14/20 $ +990

08/18/21 $ +685

06/25/22 $ +845

15. As discussed in class and using EXCEL’s Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

a. Security Market Line

16. As discussed in class and using EXCEL’s Text Box Feature, identify, explain the components, how the components are determined, what the components measure, and the uses for the:

a. Characteristic Line