这个作业是用Excel整理股票数据并预测未来走势

Professional Development in Finance: Week Two

due Saturday June 13, 2020, 10pm EDT

Dear student-trainees,

Thank you very much for your work during your first week. I hope you are settling into your

role and the office’s work culture. Following Finance One’s most recent quarterly market

update, the Investment Committee is considering rebalancing one of our portfolios by selling a

portion of our position in Johnson and Johnson (JNJ) and using these funds to purchase stock in

Vertex Pharmaceuticals Incorporated (VRTX).

This week, your task is to again produce some statistics and analysis, now to inform your

supervisor’s consideration of the VRTX purchase. You must use price data to first construct four

potential outcomes for each stock, conduct some scenario analysis, and compare the two stocks’

estimated medium-term performances. Finally, you are asked to supplement your analysis of

historical data with some qualitative research about the prospects of each investment. Please

use the firm’s memo template to organize and submit your findings to your supervisor.

Part of this training is to strengthen your skills in Excel or a similar package, and for some of

you, this might involve a steep learning curve. I encourage you to invest some time working

through sticky parts of the data analytics portion of this assignment, but if you feel that you are

spending far too much time on any component, please reach out to your fellow trainees or me

for additional support. There are suggested maximum times for each component of this

assignment.

Thanks,

Anne

Task specifics

Your predecessor, Jacob, had been working on this task, but unfortunately, many of his

handover files were corrupted after he left Finance One. The office needs you to download the

underlying data, build the necessary Excel file, and recreate what we were able to recover from

Jacob’s computer.

We have been planning to use Jacob’s probability numbers in our final recommendations to the

CIO’s office, but we must be able to explain and cite their origin. Consequently, I need you to

include a detailed annotation of all the steps you follow to fix Jacob’s file in case the CIO asks

for specifics about our process.

2

I have attached the partial Excel file that we were able to recover from Jacob’s computer and

some basic instructions based on what I have been able to discern from examining the file and

how I have approached this kind of problem in the past. Please follow these instructions to fill

in Jacob’s probabilities and histograms before continuing to my other requests. I have also

included some questions I need you to answer in the report you submit.

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.

3

• 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%.

4

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.

**程序辅导定制C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB**

本网站支持 Alipay WeChatPay PayPal等支付方式

**E-mail:** vipdue@outlook.com **微信号:**vipnxx

如果您使用手机请先保存二维码，微信识别。如果用电脑，直接掏出手机果断扫描。