Case: Dr$.$ Shelly's Portfolio

Dr$.$ Ashley Shelly completed Family Medicine training at the University of South Alabama Family

Medicine Residency, where she served as chief resident during her last year. She enjoyed working

in Alabama, so as she neared the end of the residency requirement, she accepted a position as a

Family Medicine Doctor in Mobile, Alabama. Her new salary would be nearly six times more than

the internship salary. Her hard work had finally paid off. She knew it was time to put together a

financial plan which would focus on saving for a home purchase, while tackling her huge $$300,000$

debt in student loans.

She remembered from an undergraduate finance class, that it was a good strategy to invest in a

solid company that was currently experiencing lower than expected stock prices in the hopes that

the would turn things around. She decided that Ford Motor Company may be a great investment

choice since its recent electric vehicle $($EV$)$ initiatives would be supported by President Biden's

policy in which he set a target of $50\%$ of EV sale shares in the U$.$S$.$ by $2030.$ She also learned that it wasn't good to place all your eggs in one basket, so combining shares of

Ford with other stocks or even with a well$-$diversified Standard & Poor index fund might be worth

considering. She would analyze portfolios formed by combining Ford shares with Dorman

Products $($DORM$),$ Cooper Standard $($CPS$),$ and the SPDR S&P $500$ ETF $($SPY$)$ in separate

portfolios. CPS and DORM compete in the auto industry with Ford.

Ford $($F$),$ founded in $1903,$ is globally headquartered in Michigan. It is an auto maker with three

customer$-$centered business segments that focus on: gas$-$powered & hybrid vehicles, electric

vehicles $($EV$),$ and commercial vehicles & services. Additionally, the company offers financial

services and is pursuing a non$-$automotive mobility tech solution through its Ford Next business.

The auto industry is experiencing high demand and supply$-$chain constraints, which has affected

Ford's ability to offer some features in its new vehicles. Despite the challenges, Ford's strength in

innovation has made it America's second largest EV maker behind Tesla. Dorman Products $($DORM$)$ founded in $1918,$ is globally headquartered in Pennsylvania. It is an

after$-$market auto parts maker that provides replacement parts and replacement upgrades. They

provide tens of thousands of products for different makes and models, covering parts and

electronics. Their strategy is innovation, new product development, and acquiring brands. Strong

macro fundamentals in the automotive after$-$market are expected to remain robust due to an

increasing average vehicle mileage and average vehicle age, as well as a continued shortage of

new vehicles.

Cooper Standard $($CPS$),$ founded in $1936,$ is globally headquartered in Michigan. It makes and

sells sealing and fluid handling systems and components used in equipment, machinery and

automotive. Its serves diverse transportation and industrial markets. The company leverages its

engineering and manufacturing capabilities to win new business and capitalize on positive trends

associated with electric vehicles that constitutes over $40\%$ of future annualized sales from new

business awards received in the second quarter of $2023.$ SPDR S&P $500$ ETF Trust $($SPY$)$ is the first exchange traded fund $($ETF$)$ that was launched in

$1993$ by State Street Global Advisors in Massachusetts. It is the world's largest ETF that aims to

track the S&P $500$ index performance. SPY is also the most widely traded ETF globally. It was

designed to trade at approximately $\frac{1}{{10}^{th}}$ of the S&P $500$ index. If the index is at $$4,500,$ SPY

price would roughly be $$450.$ It does an excellent job in tracking the S&P $500.$ However, it is not

an exact tracker, but differences are very small.

Dr$.$ Shelly's goal is to analyze the various risk$/$return results when different portfolios are created

and learn more about beta and CAPM.

Spreadsheet Requirements

Create portfolios in the gray area referencing the appropriate weights and stock return cells

in your formulas.

a$.$ Portfolio $1$: $\mathrm{.}5F+\mathrm{.}5$ Dorm

b$.$ Portfolio $2$: $\mathrm{.}5F+\mathrm{.}5CPS$

c$.$ Portfolio $3$: $\mathrm{.}99SPY+\mathrm{.}01F$

d$.$ Portfolio $4$: $\mathrm{.}98$ SPY $+\mathrm{.}01F+\mathrm{.}01$DORMe. Portfolio $5$: $\mathrm{.}98$ SPY $+\mathrm{.}01F+\mathrm{.}01CPS$

Calculate expected returns for F$,$ SPY$,$ DORM, and CPS$.$ Calculate expected returns for

Portfolios $1-5.$

Calculate standard deviation for F$,$ SPY$,$ DORM, and CPS$.$ Calculate expected returns for

Portfolios $1-5.$

Calculate required rate of return for F$,$ SPY$,$ DORM, and CPS using CAPM. Calculate

expected returns for Portfolios $1-5$ using CAPM.