5-1 Final Project Milestone Two: Stock Analysis and Portfolio Development

See attachments

FIN 340 Milestone Two Guidelines and Rubric

Overview: In the first milestone, you prepared a client analysis. In this milestone, you will create the stock analysis and portfolio development sections of your
final project.

First, you must understand what you are investing in. You have to know the underlying characteristics of the investment. What type of asset is it? What type of
security? How is it priced? What are the expected cash flows? Who are the typical investors and what are their typical motives? If you do not understand the
answers to those questions, then the initial expectations you develop about the value and risk of the asset will be fundamentally flawed. This sets you up for
missteps that can lead to underperforming your investment objectives.

Second, you must be able to estimate the value of the asset. Valuation is about assessing the estimated cash flows of the asset. This is a key component of
discerning absolute return potential and the differences between competing assets. It has a significant influence on the third step in the process as well.

The third step is developing a thesis about an asset’s expected return and the associated risk. This is accomplished by assessing your valuation estimates against
the current market price and any developing economic or market dynamics that may impact your expected valuation or its pricing. The market is constantly
changing, and these expectations need to be monitored on a regular basis to ensure they continue to correspond to the objectives you are trying to achieve.

Finally, you must understand how the assets in a portfolio interact with one another. It is likely that you will not have just one investment, so any additional
assets will impact the overall performance of the portfolio. You want to formulate a plan to add assets that, when combined together, will have the potential to
meet your objectives. Putting all of these steps together into a consistent, thorough process will position you to better meet the investment objectives laid out
at the beginning.

Prompt: This milestone involves creating a draft of the stock analysis and portfolio development sections of the final project. Use the provided spreadsheet to
calculate your portfolio’s standard deviation and the Final Project Scenarios document.

Specifically, the following critical elements must be addressed:

II. Stock Analysis: In this section, you will select five stocks from the provided list and determine their values by applying an appropriate valuation model
from the following options: price to multiple model (earning or sales), dividend valuation model, or free cash flow to equity valuation model.

A. Determine the value of each stock by using an appropriate model based on the characteristics provided for each stock; use each model at least
once.

B. Provide a rationale for the stock valuation method you chose for each stock. Cite specific information to support your decisions.
C. Using the calculated valuation, the current market price, and historical performance, determine the expected return for each stock.

http://snhu-media.snhu.edu/files/course_repository/undergraduate/fin/fin340/fin340_spreadsheet.xlsx

https://learn.snhu.edu/d2l/lor/viewer/view.d2l?ou=6606&loIdentId=9033

III. Portfolio Development: In this section, you will develop a portfolio for a client (Ezra or Jacob and Rachel) based on the client’s risk tolerance, return
objectives, and liquidity objectives. You will select appropriate assets from the provided list.

A. For the client, develop a portfolio from the list of assets provided that is informed by your analysis of the client’s objectives and (if applicable)
the stock valuation you determined.

B. Calculate the expected portfolio return using the CAPM (beta) model. Based on the risk tolerance and return objective of the client you didn’t
choose for this assignment, would you design an investment portfolio that has a higher or lower expected portfolio return, and why?

C. Calculate the expected portfolio standard deviation. Based on the risk tolerance and return objective of the client that you didn’t choose for this
assignment, would you design an investment portfolio that has a higher or lower expected standard deviation, and why?

Rubric
Guidelines for Submission: Your client analysis should be a 3- to 5-page Microsoft Word document, double spaced, with 12-pt. Times New Roman font, one-inch
margins, and citations cited in APA format. Note that your submission may be longer than 6 pages as work must be shown for all calculations. You may use and
upload an Excel workbook to show your calculations. In your written paper, if you are referring to data that is found within an uploaded Excel workbook, be sure
to include a citation—for example, “the portfolio’s expected return is 7.2% (E64, Sheet1, WB1),” where E64 is the cell that the calculation took place in, Sheet1 is
the tab, and WB1 is designating the name of your file. This ensures that your instructor can quickly and accurately check data entry, formula use, and financial
calculations.

Critical Elements Proficient (100%) Needs Improvement (75%) Not Evident (0%) Value
Stock Analysis: Determine

the Value
Accurately determines the value of
each stock using an appropriate model
based on the characteristics provided
for each stock

Determines the value of each stock, but
determination contains inaccuracies, or model
applied is not appropriate

Does not determine the value of each
stock

17

Stock Analysis: Stock
Valuation Method

Provides a rationale for the stock
valuation method chosen for each
stock, citing specific information to
support decisions

Provides a rationale for the stock valuation
method chosen for each stock, but rationale is
missing components or misaligned, or
information cited is not relevant or
nonexistent

Does not provide a rationale for the
stock valuation method chosen for each
stock

17

Stock Analysis: Expected
Return

Accurately determines the expected
return for each stock based on the
calculated valuation, current market
price, and historical performance

Determines the expected return for each stock
based on the calculated valuation, current
market price, and historical performance, but
determination is missing components or
contains inaccuracies

Does not determine the expected
return of each stock

17

Portfolio Development:
Develop a Portfolio

Develops portfolio from the lists of
assets provided that are informed by
an analysis of the client’s objectives

Develops portfolio from the lists of assets
provided that are informed by an analysis of
the client’s objectives, but portfolio is missing
components or is illogical

Does not develop a portfolio for the
client

17

Portfolio Development:
Expected Portfolio Return

Accurately calculates the expected
portfolio return for the portfolio using
the CAPM model and accurately
discusses other client

Calculates the expected portfolio return using
the CAPM model but calculation contains
inaccuracies or other client is not accurately
discussed

Does not calculate the expected
portfolio return using the CAPM model
or does not discuss other client

13.

5

Portfolio Development:
Expected Standard

Deviation

Accurately calculates the expected
portfolio standard deviation for the
portfolio and accurately discusses
other client

Calculates the expected portfolio standard
deviation but calculation contains inaccuracies
or other client is not accurately discussed

Does not calculate the expected
portfolio standard deviation or does not
discuss other client

13.5

Articulation of
Response

Submission has no major errors
related to citations, grammar,
spelling, syntax, or organization

Submission has major errors related to
citations, grammar, spelling, syntax, or
organization that negatively impact
readability and articulation of main ideas

Submission has critical errors related
to citations, grammar, spelling,
syntax, or organization that prevent
understanding of ideas

5

Total 100%

• FIN 340 Milestone Two Guidelines and Rubric

Rubric

Sheet

1

is calculated below in grey below.

Standard Deviation

.

0% 0

0% 0

0% 0

0% 0

0% 0

16.0% 0% 0

0% 0

0% 0

0% 0

16.0% 0% 0

0% 0

0% 0

0% 0

0% 0

1.0% 0% 0

6.0% 0% 0

0% 0

6.0% 0% 0

8.0% 0% 0

0% 0 0

																																																																																									Instructions:
1. Insert portfolio weights in the yellow highlighted area.
2. Portfolio	Standard Deviation
Portfolio Weight	x1		x2
				IBM	2																					0																				0%
				KO	13.0%
				BMY	2	8.0%
				ORCL			1		6.0%
				MMM	14.0%
				BAX
				BIG	32.0%
				NFLX	45.0%
				AKAM	37.0%
				GE
				SPY	1	1.0%
				IWM	18.0%
				EFA	15.0%
				EEM	19.0%
				SHY
				IEF
				TLT	13.5%
				LQD
				HYG
Portfolio Standard Deviation	0.00%

Sheet2

IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG

IBM 1

89

4

5

0.539

4

KO 0.289 1

0.52

BMY 0.185 0.162 1

7

0.283

ORCL 0.589 0.316 0.262 1

0.5

4

MMM

0.447 0.242 0.535 1

0.135

8

0.012 0.51

BAX 0.368 0.283 0.247 0.432 0.425 1

BIG 0.215 0.184 0.155 0.249 0.231 0.086 1

0.187

0.048 0.231

NFLX 0.179 0.187 0.143 0.311 0.135 0.265 0.092 1

0.25

AKAM

0.194 0.117 0.404 0.228 0.257 0.187 0.225 1 0.372

0.417

-0.21

-0.053

GE 0.539 0.393 0.283 0.5 0.672 0.415 0.259 0.254 0.372 1

4

SPY 0.622 0.52 0.348 0.739 0.703 0.569 0.376 0.372 0.479 0.744 1

-0.054

IWM 0.539 0.359 0.313 0.636 0.585 0.476 0.483 0.317 0.417 0.651 0.877 1 0.74

0.66

EFA 0.568 0.444 0.264

0.617 0.516 0.275 0.284 0.437 0.635 0.857 0.74 1

EEM

0.491 0.198 0.628 0.586 0.487 0.268 0.314 0.412 0.603 0.807 0.688 0.856 1

0.11

SHY -0.127 0.002 -0.053 -0.178 -0.136 -0.141 -0.054 -0.11 -0.193 -0.229 -0.276 -0.271 -0.245 -0.118 1

0.668 -0.106

IEF -0.252 0.012 -0.147 -0.29

-0.175 -0.092 -0.106 -0.21 -0.319 -0.392 -0.386 -0.391 -0.208 0.821 1

TLT -0.246 0.021 -0.155 -0.31 -0.249 -0.159 -0.074 -0.082 -0.203 -0.32 -0.375 -0.365 -0.396 -0.205 0.668 0.929 1

LQD 0.023 0.135 -0.08

0.012 0.019 0.048 -0.052 -0.053 -0.089 -0.054 -0.072 -0.045 0.11 0.668 0.81 0.814 1 0.179

HYG 0.429 0.417 0.197 0.558 0.51 0.453 0.231 0.25 0.333 0.504 0.742 0.66 0.722 0.776 -0.106 -0.194 -0.198 0.179 1
x2

IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG
IBM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
KO 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
BMY 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
ORCL 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
MMM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
BAX 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
BIG 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
NFLX 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
AKAM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
GE 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
SPY 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
IWM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
EFA 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
EEM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
SHY 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
IEF 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
TLT 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
LQD 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
HYG 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000

Correlation Matrix
Security
	0.289		0.185				0.5			0.52		0.368		0.215				0.179			0.25				0.539		0.622		0.568			0.51		-0.127		-0.252		-0.246		0.023		0.429
	0.162		0.316		0.447				0.283		0.184				0.187		0.194		0.393		0.359		0.444		0.491		0.002				0.012		0.021				0.135				0.417
	0.262		0.242		0.247		0.155		0.143				0.11		0.348		0.313		0.264		0.198				-0.053		-0.147		-0.155		-0.08		0.197
	0.535		0.432		0.249		0.311		0.404		0.739		0.636			0.66		0.628		-0.178		-0.29		-0.31	–		0.048		0.558
0.524		0.425				0.231		0.228		0.672		0.703		0.585		0.617		0.586		-0.136			-0.21		-0.249
	0.086		0.265		0.257		0.415		0.569		0.476		0.516		0.487		-0.141		-0.175		-0.159		0.019		0.453
	0.092		0.259		0.376		0.483		0.275		0.268				-0.054		-0.092		-0.074
	0.225		0.254				0.372		0.317		0.284		0.314		-0.11				-0.106		-0.082		-0.052
0.255		0.479		0.437		0.412		-0.193		-0.203		0.333
			0.74		0.651		0.635		0.603		-0.229		-0.319		-0.32		-0.089		0.504
	0.877		0.857		0.807		-0.276		-0.392		-0.375		0.742
	0.688		-0.271		-0.386		-0.365		-0.072
0.664		0.856		-0.245		-0.391		-0.396		-0.045		0.722
0.514		-0.118		-0.208		-0.205		0.776
	0.821				0.668
-0.218		0.929		0.81		-0.194
	0.814		-0.198
-0.048
																																																																																																																																																																																																																																																																																																																																																																									0.0000

FIN 340 Final Project Scenarios and Tables

You will use these scenarios and tables to complete the final project.

Client 1:

Ezra, age 26, is single. However, he is dating and preparing to get engaged. He will need roughly $5,000 for an engagement ring almost immediately, and expects
he will need $10,000–$15,000 for the wedding in the next 12–24 months. He is currently employed and earns about $70,000 a year in salary. This salary is
enough to cover all his taxes and normal living expenses of approximately $4,800. This leaves him with about $1,000 in savings each month ($350 to 401K, $650
to savings). He has been able to save roughly $15,000 to date in a 401K plan from work and about $20,000 in cash savings. His 401K plan has been invested 100%
in the stock market, including some sector-specific funds. His other savings have been in interest-bearing savings and cash substitutes such as money market
funds. He recently received a windfall of $60,000, and this prompted him to come to you for some advice. The following are few of Ezra’s comments to help
guide your thoughts:

1. “I understand I am young, so I need to take on as much risk as I can.”
2. “I am willing to lose 30–40% on my invested capital if the return is commensurate.”
3. “I do like to have a decent sized cushion in the bank in case something happens at my job.”
4. “I don’t foresee my risk tolerance changing after I get married.”
5. “Do you have any good stock tips?”

Client 2:

Jacob and Rachel, 53 and 52 respectively, are married with four children. Two of the children are currently in college, and two are in high school. They expect the
other two children to attend college. The couple has done relatively well for themselves and earn roughly $275,000 before tax between the two of them, which
equates to $190,000 after taxes. They live well below their means, and this should allow them to cover all of their children’s college expenses out of pocket, but
it will not leave much for them to save over the next six to eight years. Through savings and portfolio growth, they have managed to accumulate $900,000. To
this point, they have been moderately aggressive (70–75% equities) with their portfolio, but they feel that they need to begin preparing the portfolio for partial
retirement in eight years, and full retirement in 13 years.

1. “I know we still need to be somewhat aggressive—we could live until we’re 90—so we need to plan for some growth even in retirement.”
2. “We definitely can’t afford to take a big hit in our portfolio. We don’t have enough time to recover.”
3. “Our jobs allow us to work part-time in retirement, and we will probably do so as long as we are able.”
4. “What do bond yields look like today?”
5. “I think we’ll need to draw on 3–5% of our portfolio in retirement. We’d like to earn enough income from the portfolio to cover that.”

CAPM Inputs:

Market Return 9%
Risk-free Rate 0.75%

Stock Analysis Table:

Symbol Estimated
Beta

Dividends Earnings Sales Free Cash Flow 5-Year
Dividend
Growth

Average
Industry
P/E Ratio

Average
Industry
P/S Ratio

Free Cash
Flow
Growth

IBM 0.86 Use Last Year Use Last Year Use Last Year Use Last Year 13.7 23.7 1.12 2.60%

KO 0.66 Use Last Year Use Last Year Use Last Year Use Last Year 8.3 22.6 2.2 6.50%

BMY 0.78 Use Last Year Use Last Year Use Last Year Use Last Year 2.9 24.4 3.37 N/A

ORCL 1.1 Use Last Year Use Last Year Use Last Year Use Last Year 21.1 20.5 4.45 10%

MMM 0.98 Use Last Year Use Last Year Use Last Year Use Last Year 15.1 23.8 2.59 7%

BAX 0.75 Use Last Year Use Last Year Use Last Year Use Last Year -16.9 36.09 3.68 N/A

BIG 1.04 None Use Last Year Use Last Year Use Last Year N/A 23 1.12 N/A

NFLX 1.57 None Use Last Year Use Last Year Use Last Year N/A 52.5 6 N/A

AKAM 1.34 None Use Last Year Use Last Year Use Last Year N/A 41.8 3.58 17%

GE 1.12 Use Last Year Use Last Year Use Last Year Use Last Year 9.7 23.8 2.59 N/A

Available Assets Table: Stocks listed in Analysis Table and these additional assets

Symbol Estimated
Beta

Standard
Deviation

SPY 1 13%

IWM 1.15 16.50%

EFA 1.03 15%

EEM 1.09 20%

SHY 0 1%

IEF -0.2 6%

TLT -0.48 13%

LQD -0.02 5.25%

HYG 0.38 7.50%

Ex-post Return Statistics:

Symbol Holding
Period
Return

Standar
d
Deviatio
n

Benchmarks Holding
Period
Return

Standard
Deviation

IBM 8% 20.00% Growth 9.6% 13.1%

KO 6% 13.00% Capital Appreciation 8.1% 8.6%

BMY 13% 28.00% Income 7.7% 7.2%

ORCL 2% 16.00% Capital Preservation 5.8% 5.2%

MMM 6% 14.00%

BAX -6% 16.00%

BIG 13% 32.00%

NFLX 18% 45.00%

AKAM 21% 37.00%

GE 10% 16.00%

SPY 9% 11.00%

IWM 14% 18.00%

EFA 9% 15.00%

EEM -1% 19.00%

SHY 1% 1.00%

IEF 4% 6.00%

TLT 4% 13.50%

LQD 7% 6.00%

HYG 8% 8.00%