首页 » Excel代写 » Excel代写 | Professional Development in Finance

Excel代写 | Professional Development in Finance

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
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.
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
• Compute four possible outcomes for the price of VRTX and their associated
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
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.


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

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




电子邮件地址不会被公开。 必填项已用*标注