Excel代写 | Professional Development in Finance Week Two Practice

这个作业是用Excel整理股票数据并预测未来走势
Professional Development in Finance: Week Two

Part 1 (2 hrs)

• Log-in to WRDS and download the monthly average price from 2015-2019 for both
VRTX and JNJ.

• Compute four possible outcomes for the price of VRTX and their associated
probabilities.

o Using the price data you have for VRTX, generate a histogram (use 10 bins).
Does the distribution of prices look normal? In one or two sentences, comment
on the shape of your initial 10-bin distribution and what it might suggest about
the riskiness of this investment.

o When computing your price estimates, apply the concept of weighted averages.
First, use 10 bins and eliminate two “tail-end events.” Adjust the total number
observations accordingly. Consolidate your remaining bins and corresponding
prices using weighted averages. You should ultimately have four prices and four
probabilities. See below for an example (note: this example was not generated
with VRTX or JNJ data).

• Create a second histogram and table of probabilities using the price data for JNJ. Again,
does the initial 10-bin distribution of prices look normal? Comment on its shape and
what it might suggest about the riskiness of this investment.

Part 2 (1 hr)

• Our analyst who covers pharmaceuticals mentioned that Vertex has not reported any
dividend payments. Please find a credible source online to confirm this. Remember to
clearly cite your source, as always.

• Our office’s records suggest that JNJ will pay an annual dividend of 3.15%. Based on
price outcomes you computed, calculate and report the expected returns and standard
deviations of VRTX and JNJ.

o Compute the average monthly price for 2019. Use this as 𝑃0. Remember that you
are using probabilities to do this.

o Calculate the realized returns in each of the four states of the world before you
compute the expected return.

• Based on your graphs and statistics, write two or three sentences comparing the returns
and riskiness of VRTX and JNJ.

Part 3 (1.5 hrs)

• One of our senior analysts, Karen, has suggested that Finance One should rebalance our
holdings by selling some of our JNJ stock, using some of Finance One’s cash balances,
and by borrowing half of the necessary financing from our broker. She believes she can
secure a price of $145.50 per share for 500 shares of JNJ.
o Compute the funds that can be raised from this sale.

• The proposed strategy is to purchase 1000 shares in VRTX. Karen believes the Finance
One can lock in a price of $216 per share of VRTX. She proposes that half of the total
cost of these new VRTX shares be purchased using cash from Finance One plus the
funds raised from selling the shares of JNJ.

o Compute the total cost of the 1000 shares in VRTX.

o Compute the funds raised from the sale of JNJ shares and subtract this amount
from the cost of the proposed VRTX purchase. Report how much cash will be
needed if half of the total financing needs for this purchase are borrowed from
the broker.

• Karen proposes to cover the remaining half of the financing needs by borrowing from
Finance One’s broker. What is the initial margin?

o Compute the initial margin.

o Organize your computations the same way that BKM do in Example 3.1.

• The investment committee is concerned about downside risk, so you are requested to
perform some scenario analyses.

o Consider the four VRTX price scenarios you computed above. Compute the
value of the holdings if the price of VRTX falls to that of your second lowest
computed price.

o Compute the new margin on the account in this scenario.

o Report whether we would receive a margin call, if the maintenance margin is
30%.

Part 4 (30 min)

• The Investment Committee is likewise concerned about medium- and long-term returns.
Determine which investment will perform better after five years.

o Use the annual expected rates of return you computed for JNJ and VRTX for Part

2. (Hint: these returns can be negative.)

o Using a principal equal to $1000, compute the future values of investments in JNJ
and VRTX.

o In one or two sentences, compare the estimated performances of the two assets.
Part 5 (1.5 hrs)

• Finally, conduct some online research. In two brief paragraphs, discuss two key factors
that might make each either investment risky or undervalued (two for JNJ and two for
VRTX). These might be factors that are not yet reflected in either stock’s price. Should
Finance One adjust the probabilities and outcomes we are using to compute expected
returns? Provide two to three sentences making a recommendation for any adjustments,
along with an explanation justifying your argument(s).
Please make sure that you append all data, spreadsheets, and relevant calculations and that:

o Any figures you include in your report are clearly labeled, with easy-to-read axes
and legends.

o Your spreadsheet, once opened, is easy to navigate. Data series and any worksheet
tabs should be clearly and appropriately labeled.

o You include one or more clearly labeled worksheets for any calculations discussed in
your memo. Please annotate your worksheets so that any third party can see what
computation corresponds to any part of the assignment.

o Ideally, your labeling and annotations should be sufficiently clear that any
supervisor can replicate all of your results based on the workbook you submit within
5-10 minutes.

o You correctly cite any sources you use in forming your analysis.
When submitting to latte, make sure that click submit and you receive a confirmation email
that your work has been submitted.