statistics assignment ..

MGT-251-Z Fall 2016, Final Exam (take home), Due December 17 by midnight

On Sunday Dec 18 I will post the solutions and consider anything uploaded after that as late.

Take home exam file: final-exam.xlsx . It has 2 tabs Part 1,2. Show all your work well annotated

in each tab. Include your results (highlighted) in each tab as well as any explanations ( also

highlighted). Upload the final file renamed with your name in it.

Introduction

Remember the complicated fitting of the time series with seasonal effects problem in class?

The one with the linear tread that had an added sin(t) component? We were only able to see it

through the residual analysis.

How about “a square root with a linear function inside an exponential”! That would be

something? And yet I think you can do it now. To prove this to you I dig up the most difficult

formula I could find. It a prediction formula from financial analysis. It forecasts the price of a

stock based on the results of a historical data analysis. You may or may not have seen it

somewhere in your finance classes.

The mathematical representation of the formula is: 𝑃(𝑡) = 𝑃(𝑡0)𝑒 (µ−

𝜎2

2 )𝑡+𝜎𝑍√𝑡

The translation to Excel can be like:

=CurrentPrice*EXP((spread-0.5*Volatility^2)*time+Volatility*RandomWalk*SQRT(time))

CurrentPrice is the last value of the historical data analysis for the price

Spread is a type of average difference to the price mean (logarithmic) from the average values

of the price.

Volatility is a type of standard deviation (logarithmic).

The particular meaning of these is Financial and beyond the scope of this class. I want you to

focus on the inside mechanism of the prediction and the analysis of the results of this

simulation of time-series in the future. Think of it like “the small child breaking up a toy to see

how it works” scenario in this test.

The figure (right) is a snapshot of what is called

“Random Walk” or “Brownian Motion in 1

dimension” for the more mathematically

inclined, or “Drift” in the language of the

financial analysists doing time-series

forecasting, but what’s in a name anyway. It

represents the uncertainty of the future (data

bounces?) when predicting.

In the tab labelled Part 1, there is information on the results of a historical data analysis of a

stock price. The info in the “historical time series analysis”

section of the worksheet is the output of the historical time series analysis. It is also the input

you want to apply to the future prediction for the value at 30 days after that snapshot.

The workbook is designed in such a way (dynamic) that it resets the numbers every time there

is a click. This is deliberate in order to produce different values for each of you since this is a

take home exam and I am not looking over your shoulder. This is also done because this is a

take home exam and I am not looking over your shoulder to stop you from getting stuck trying

to interpret micro features in the data and missing the big picture. In real life the data are

bounce around in values that are caused by randomness. By seeing different distributions every

time you click something you need to learn to focus on the overal/common/surviving features

you see and not things that appear once and get lost with the next click. Confusing at first , I

agree, but rewarding if you struggle through it, like all the classes we had so far.

Problem 1 for 5 points

Study the “RandomWalk” column (provided) for pure randomness.

a) Find the mean and standard deviation of this data set from a fit.

b) show that they randomly fluctuate around 0.00 through residual analysis as we did it in

the class.

c) Search your book(s) to find out how one can simulate a Random Walk.

d) Find instructions in the text-book(s) or online how one can simulate a Random Walk in

Excel (2013, 2016 and up).

Problem 2 for 5 points

Look up how use “named references” in excel equations in order to use the above formula.

a) Apply the formula to create a column under the heading of “Price Forecasting” and plot

it.

b) Link the last (day 30) value of price to the relevant cell in the Current analysis section of

the Worksheet.

c) In the “sdev” cell of the current analysis associates the standard deviation of a sample

(be careful about the “sample” concept). What Excel formula you used and why?

CurrentPrice 50.00$

spread 0.002270$

volatility 0.005154$

d) Try to change manually the values of the historical time-series output and watch how

the numbers and plot changes.

a. Do you see an overall trend within the randomness of the fluctuations of the

price?

b. Can you make the plot look like a straight line by choosing different values? Take

a snapshot and add it as a pic in the worksheet somewhere.

c. The initial trend should be a faint up ward one for the initial values above. Can

you make it go downward trend (as in a losing money case) by choosing different

values? Take a snapshot and add it as a pic in the worksheet somewhere.

d. (optional) as you “play around” do you observe any other interesting features

created or disappear from the change of “initial conditions” to the forecasting

simulations?

Problem 3 for 10 points

Return the initial condition values manually to the settings in the insert in the Introduction

section. Now that you have observed qualitatively the behavior of the simulating algorithm it is

time to convince ourselves that it holds quantitatively as well. You may know that the “real”

formula is a complicated expression but do it systematically to see how many of its components

you can reproduce with the “Trendline” tool of Excel.

a) Try all the trendlines in 1 plot with the data. It will be complicated, so plan

appropriately for your presentation. Add the equation, and R-squared value for each

trendline. Color code each line and associate equation text for each.

a. Take a snapshot and add it as a pic in the worksheet somewhere.

b) Observe which are better fits than others and explain.

a. Do you see weak parameters for some higher order cases? It is more obvious in

the polynomial and power lines.

b. Notice the danger of how the higher order polynomials fit good insignificant

features of the plot with progressively vanishingly small coefficients.

i. How would you define features as insignificant?

c. Do you see a “loss of predictive power” for higher order moving averages cases?

d. Which trendlines have the better R-square values?

c) Pick one of the fits that you think is better than the rest. For this one and the linear

trendline create each a column of predicted values. Then create a column of residuals

(for each) from the financial model column values. It is time for residual analysis.

e) Compare the features of each of the residuals just like you did in problem 1.

a. Find the mean and standard deviation of this data set.

b. Are they fluctuate around 0.00 normally? Use the residual analysis methods as

we did in class.

c. Do you see data group clustering on one side the 0.0 line or the other? This is

called positive autocorrelation and it means that you can see a “hidden process”

beyond the simple fit you made.

d. How would you folmulate this search for autocorrelation in the language of

hypothesis testing?

i. Write the null and alternate

ii. Express carefully the conclusion of this search ( you see or not see the

features).