Over the past year, a financial analyst has tracked the daily change in the price per share of common stock for a major oil company. The financial analyst wants to develop a simulation model to analyze the stock price at the end of the next quarter. Assume 63 trading days and a current price per share of $51.60.
a. Based on the data in the Data To Fit worksheet of the file Daily Stock, use the Excel formula 5CORREL(B3:B313, B4:B314) to compute the correlation between the percent change in stock price on consecutive days. What do you conclude about the dependency of the percent change in stock price from day to day?
b. Based on the data in the Data To Fit worksheet of the file Daily Stock, compute sample statistics and construct a histogram to visualize the distribution of the data. Select a distribution that appears to fit this data.
c. Using the distribution that you selected in part (b) to represent the daily percent change in stock price, construct a simulation model to estimate the price per share at the end of the quarter. What is the probability that the stock price will be below $26.55?
d. The What Really Happened worksheet of the file Daily Stock contains the 63 values of the daily percent change in stock price that actually occurred during the quarter. What does this reveal about the limitations of simulation modeling? What could the financial analyst do to address this limitation?
