Finance

FIN 317 – Computer Analysis for Finance

Final Exam – Spring 2020

The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017.

Ticker	6/30/2017	5/31/2017	4/30/2017	3/31/2017	2/28/2017	1/31/2017
ORCL	50.14	45.39	44.96	44.61	42.59	40.11
MSFT	68.93	69.84	68.46	65.86	63.98	64.65
NVDA	144.56	144.35	104.3	108.93	101.48	109.18

a) Enter the data, as shown, into a worksheet and format the table as shown.

b) Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each company. Format the results as percentages with two decimal places.

c) Calculate the total return for the entire holding period, the compound average monthly rate of return, the average monthly rate of return using the AVERAGE function, and the average monthly rate of return using the GEOMEAN function.

d) Create a line chart showing the stock prices from January to June 2017 for these companies. Make sure to title the chart and label the axes. Also present the data from January to June and use Times New Roman for the title, labels, and numbers. Select a different dash type for each line representing each company.

Using the TGT Quarterly Sales (Target Corp.) data:

a) Fit the sales data using the Holt-Winters Additive Seasonal model, and opti-

mize the smoothing constants with the Solver.

b) Use the Forecast Sheet button to create a forecast worksheet. (Or you can

also do as we did in class using the Forecast.ETS, Forecast.ETS.stat and Fore-

cast.ETS.confint functions.)

3) Bob’s Rawhide Company has a dividend payout ratio of 65%. Next year it will earn $1.25 per share and have a return on equity of 11%. The shareholders’ required return is 8%.

a) Calculate the company’s growth rate of EPS.

b) Using the earnings model, what is the value of the stock?

c) Construct a data table that shows how the growth rate and value of the stock will change if the ROE ranges between 10% and 30%, in 1% increments. Now, using that data, create a scatter chart to show the relationship between the value of the stock and the ROE. Is the relationship linear? At what point does the model break down?

Using the constant-growth model, what is the value of the stock?

d) Using the constant-growth model, what is the value of the stock?