Essentials of Business Analytics 1st Edition by Jeffrey Camm,James Cochran,Michael Fry,Jeffrey Ohlmann ,David Anderson

Exercise 22

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. Using ASP, 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. Using the data in the datatofit worksheet of the dailystock file, fit a distribution to represent the daily change in stock price. Assume that the each day's change in stock price is independent of every other day's change in stock price. You can justify this assumption by estimating the correlation of consecutive days' change in stock price using the command CORREL(B3:B313, B4:B314) and observing that this quantity is relatively small.
b. What is the expected price per share at the end of the quarter
c. What is the probability that the stock price will be below $26.55
d. The WhatReallyhappened worksheet of dailystock file contains the 63 values of the daily change in stock price that actually occurred during the quarter. (You can plug these 63 values into your model to confirm the calculations.) What does this reveal about the limitations of simulation modeling
e. Based on the observation that many distributions underestimate the possibility of extreme values, some experts suggest the use of a "heavy-tailed" distribution to model the change in stock price. The Cauchy distribution is one such heavy-tailed distribution in which the likelihood of extreme daily changes in stock price are much more likely
than with other distributions (such as the normal distribution). Fit a Cauchy distribution to the data in the datatofit worksheet and observe the impact on the analysis. hint: To avoid unrealistic swings in stock price, truncate the possible values from the Cauchy distribution by setting the lower cutoff and upper cutoff values to 21 and 1, respectively.