# 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.