Finance work

Excel Journal Format due Monday February 24th

IT MAY NOT BE CLEAR TO EVERYONE SO LET ME REPEAT:  YOU HAVE TO ANSWER ALL QUESTIONS IN THIS 'BOOK' (SESSION 1 TO SESSION 5) IN  YOUR EXCEL JOURNAL

Your Excel journal should be composed of a text part and an excel file.  You should submit at most two documents.  The text part should contain a one page answer for each session that explains your work, and makes clear reference to the excel file.  The excel file should be (as much as reasonable) self-explanatory.  That is, try to include comments that help the reader to follow your steps and understand how you derive your results.

You are restricted to one page of text (500 words maximum) per session (5 pages max) and one excel file that should contain 5 sheet (one per session).  The last Excel Session will be before the reading break and your journal is due after the reading break.  I encourage you to use this forum to post questions about your assignment and to answer others' questions. 

You will be graded on the basis of: the clarity of your explanations; the logic of your calculations; the accuracy of your results; and whether your excel file is easy to follow and self-explanatory. 

Session 2: Extreme Early Retirement

Part 1: The goal of this part of your journal is to re-create this Excel sheet, but using the following assumptions: you earn 100K each year, consume 30% of your earnings, and the interest rate is 4%. 

a. Replicate the Excel sheet and explain what each column mean.

b. How do you derive the earliest year you can retire such that your consumption remains constant forever after? What year do you obtain?

Part 2: The goal of that part of your journal is to derive a formula that delivers your earliest possible retirement year as a function of your saving rate.  Say you earn E each year, you save share alpha of E and consume the rest, and the interest rate is r.   

c. Assume for now that you always work. How much capital you have in your saving account at the end of the first and second years?  Derive the formula that gives your capital at the end of year T. 

d.  How much do you have in your saving account at the end of year 2 if you stop working in year 2?  At the end of year 3 if you stop working in year 3 ?  At the end of year T if you stop working in T?

e.  How many years do you have to work before you can stop working and maintain your consumption constant forever after?  Plug the values from Part I and check that you obtain the same answer. 

Session 3: Arithmetic and Geometric Rates of Return

Part A

We want to check the statement on p.141 that “the arithmetic mean is higher than the geometric mean by about half the variance.” 

1-Use the Rand() function to generate 400 random rate of return with mean 10.7% and variance 3.2%%.

2-Assume these 400 rates correspond to 400 consecutive years.  Compute the arithmetic rate of return, the variance over 400 years, and the geometric rate of return.

3-Resample several times using F9.  Would you say that the statement on p.141 holds?  Discuss.

Part B

We want to check the statement ‘Risk grows approximately with the square root of time’ on p. 171. 

1-Draw two rates of returns as you did above.  Say the  first draw is the rate of return over period 1, call it r_01, and the second is the rate of return over period 2, call it r_12.  Compute the holding rate over two periods, call it r_02. Repeat this 400 times to obtain 400 draws for each rate of returns (1200 numbers total).

2-Using your three sets of 400 draws, compute the variance of r_01, r_12 and r_02.  What should be the relationship between the these variances according to the statement on p.171?  Check that this is the case.

5. Session 4: Signal-to-noise ratio

The purpose of this simulation is to get some intuition for Ivo’s statistical answer to the question of whether it is possible to detect an extra signal of 2bp per day with noise 50bp if one observes 5000 realizations of this signal (See section 12.3 of the textbook and in particular p.289-290).  We want to get a sense of what this means using simulations and graphs. You should use the excel sheet supplied. 

1-According to Ivo’s N-day statistics, how many days would it take to reach a conclusion about this signal (assuming that you want a T-stat around 2).  

2-Check that the function IF(RAND()<0.5,-1,1) draws a random variable that take value +1 and -1 with equal probability.  To do so explain what you would expect to see when you draw 1000 independent realizations.  Check that this is the case. 

3-Explain how the formulas in the excel sheet use this function to draw a signal with 2bp drift and noise 50bp.  

4-Compute two 5000-days time series: (a) the realized compounded rate of return (an approximation of a Brownian motion), (b) the compounded rate of return from a 2bp drift alone.  Plot these two time series. 

5-Using the notion of arithmetic and geometric rate, would you expect the realized return after 5000 days to be on average equal to the compounded rate?   If not, what would be a good guess for what you should expect to earn after 1, 2,...5000 days.  Add that time series to your chart.

6-Say you draw many samples of 5000 realizations (using the F9 function).  According to your answer to question 1, what property would you expect these samples to have?  How would you use the chart you produced to check this?

7-Repeat your analysis for a signal with 1bp drift and 100bp noise?  Is the difference between the two signals sensible?

Note: You’ll have to learn how to draw a ‘scatter XY chart in Excel’.  This is a great tool to know to visualize data. You’ll find many online tutorials under that search entry.

Session 5: A DCF approach to Tesla valuation

The purpose of this session is to: (a) retrace the  main steps taken by A. Damodaran to value Tesla; and (b) replicate the analysis with updated numbers.  You should read Chapter 3 from "The Little Book of Valuation" by A. Damodaran to understand the big picture.  Your answers should try to make reference to that reading when relevant.  Question 2 and 3 refer to sheet 'valuation output' in this simplified Excel file.  (There are many sheets in this file but you have to look only at the first two.  Note also that I have simplified the Excel file so the final valuation is not the same as the ones reported in the paper you read for RN2.)  Limit your answers to 100 words per question.

1-Briefly summarize the 4 steps taken in Chapter 3 to value a company. 

2-Write a simple (math) formula that summarizes how Damodaran obtains the FCFF in line 13.  Your formula should highlight the key inputs and assumptions that go in that formula.  Explain. 

3-Write a formula that explains how A. Damodaran combines the FCFF with other inputs to obtain the valuation in B25. Explain.

The purpose of the next two questions is to update the Tesla valuation using the latest available information following the same methodology as above.  You will have to do some research to find the values you need to update the Excel file.  

4-Say you update only the 'base year' revenue.  What valuation do you obtain?  Compare the result with the corresponding market valuation.  If you had to change just one other input in the Excel file to get a more accurate and current valuation, what would it be?  When you make that change, what impact it has on your valuation?   

5-How has the Tesla stock performed over the last year?  In your opinion, can the valuation model explain this performance?  Discuss.