Excel代写 | Professional Development in Finance

这个作业是用Excel统计分析股票数据
Professional Development in Finance: Week One
Dear student-trainees,
Welcome to Finance One’s summer analyst training program! We are delighted that you have
elected to spend this summer working and training with our firm. With assets under
management of over $130 billion, Finance One relies of thousands of team members like you to
inform and monitor our investment and portfolio decisions. During this program, you will
complete a series of projects that culminate to your end of summer report to Finance One’s
Investment Committee. At each stage of the summer, your performance will be evaluated on
the accuracy of your responses, the clarity of your writing, and the polish of your analytical
presentation.
As a first task to introduce you to the firm’s report and memo formatting, please complete the
following exercise, using the attached template. Your assignment is to produce a visually clear
analysis of four assets that your supervisor, a senior analyst, will integrate into his report to
Finance One’s chief financial officer.
Your second task for this week is a small one-off project that has been requested of our office by
Finance One’s marketing department. Please see the attached email from the Head of Marketing
and draft a two-paragraph email answering their questions.
Thanks. I look forward to receiving your work.
-Anne
Task 1 specifics:
• Log-in to WRDS and download the monthly returns from 2010-2019 for Apple, General
Electric, Yum! Brands, and ArcelorMittal. You can first search online for the appropriate
tickers.
• Copy these data into excel and create a graph that includes a line for the time path of
each of the four stocks.
o Clearly format the graph’s x-axis with the data’s monthly date data
o Add axes labels and adjust font sizes to ensure the graph’s information is clear to
the reader
o Make any necessary changes to ensure your analysis is easy to read.
2
• Create an alternative way to present the returns data: can you design a format that tells a
clearer picture to your supervisor?
• Compute and present the mean, variance, standard deviation for each stock.
• Create a histogram for each stock. Use 20 bins. (Hint: watch the tutorial on
https://youtu.be/bjsWsGEO2xY)
o For a bonus challenge, can you overlay a normal curve over your histogram?
• Create and present scatterplot graphs for each stock pair (i.e. Apple and GE, GE and
ArcelorMittal, etc).
• Compute the covariance and correlation between each stock pair (i.e. Apple and GE, GE
and ArcelorMittal, etc).
• Write two concise paragraphs describing the data and their calculated statistics. The
first paragraph should discuss any patterns you see in the returns data and a
comparison of their volatility and distributions. Can you attribute any of these data
patterns to underlying characteristics of companies? The second paragraph should
discuss any co-movement you observe between the three stocks’ returns. Refer to your
graphs statistics in both paragraphs.
• Attach your data to your project submission. Please make sure that:
o Your spreadsheet, once opened, is easy to navigate. 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 annotation should be sufficiently clear that any
supervisor can replicate all of your results based on the workbook you submit
within 5-10 minutes.
3
Task 2 specifics
Mark Joseph 11:10 AM (8 hours ago)
to me
Hi Anne,
Our team has been requested to pull together the company’s 2021 midwest region
marketing campaign, and we just received back the preliminary numbers from the data
collection project we launched in November 2019 about new account applications. Could
you have one of your summer analysts review this information and send us a summary to
inform our ad targeting?
New Accounts Opened, Finance One, Midwest Branches 2019 (hundreds)
Age Range 18-24 25-39 40-49 50-59 60-65 65-80 80+
Midpoint 21 32 44.5 55.5 63 68 81
Number of
applications
30 63 39 42 63 39 12
Thanks in advance for your support on this work stream. I’ll be in touch.
Mark
Please respond to Mark’s email by providing him with the following summary information
in a concise, 1-2-paragraph email and include the figures requested below. Your email
should include no more than seven sentences. Be sure to proofread your draft before
forwarding it to me and the office.
a) Let X be the random variable of midpoint ages. Construct a probability distribution for
X. That is, provide Mark’s team with a list of the midpoint ages and their corresponding
probabilities.
b) Explain and show to Mark that these probabilities should sum to 1 when all outcomes
are considered.
c) Build a relative-frequency histogram of this probability mass function in excel and paste
it into the email (with midpoint ages on the x-axis and probabilities on the y-axis).
d) Provide the expected value of age.
e) Provide the variance of age.
f) Provide the standard deviation of age.