CLA 2 Comprehensive Learning Assessment 2 – CLO 1, CLO 3, CLO 4, CLO 5, CLO 6,

CLO 7

This is a complete written report of your portfolio formation in a Word file. Your historical data

and relevant derived values in tables can be pasted from your previous calculations in the

Excel file. Please provide explanations of all calculations and the justifications in the Word

format. Also, make sure to paste all underlying Excel formulae that you used for calculations

in the Word file.

1. Provide once again the data that you presented in answering part 2 of professional

assignment 2. (I have attached a file for reference)

2. Calculate the mean, variance, and the standard deviation of each security’s annual rate

of return.

3. Calculate the correlation coefficient between every possible pair of securities’ annual

rates of return.

4. Choose percentages of your initial investment that you want to allocate amongst the

five (5) securities (weights in the portfolio). Create embedded formulae which generate

statistical properties of the portfolio upon insertion of the weights. Observe the mean,

the standard deviation, and the CV of the annual rate of return of the portfolio.

5. Find the combination of the weights that minimizes CV of the portfolio. How the CV of

the optimal portfolio compares with the CV’s of its constituents. What is the expected

rate of return and standard deviation of the rate of return of the portfolio?

6. Choose different values within the range of the standard deviation of the portfolio, and

for each chosen value locate the corresponding point on the efficient frontier by finding

the weights that maximize the expected rate of return of the portfolio. Subsequently,

construct the efficient frontier of your portfolio.

7. Assume that you initially invested $1,000,000 in the portfolio and that the distribution of

the annual rate of return of the portfolio is normal. What is the distribution of the return

of the portfolio 20 years after its formation? Provide the graph of the distribution of the

return of portfolio.

Provide your explanations and definitions in detail and be precise. Comment on your findings.

Provide references for content when necessary. Provide your work in detail and explain in your

own words. Support your statements with six (6) peer-reviewed in-text citation(s) and

reference(s).

CLA2 Comprehensive Learning Assessment (CLA 2) Presentation:

In addition to your CLA2 report, please prepare a professional PowerPoint presentation

summarizing your findings for CLA2. The presentation will consist of your major findings,

analysis, and recommendations in a concise presentation of 15 slides (minimum). You should

use content from your CLA2 report as material for your PowerPoint presentation. An

agenda, executive summary, and references slides should also be included.

WALMART 2

Walmart

Student

University

Subject

Professor

Date

Walmart

Walmart Inc. is a company that is classified as a multinational retail corporation operating a chain of hypermarkets. Walmart has been operating for many decades and has been able to maintain the shareholders’ trust. The company has invested in its employees to enhance the operations. However, the stockholders and the investors are the most significant source of revenue for the company. This is why it maintains clear and concise financial records inbalance sheets, income statements, and historical data. This information is useful during the investors’ decision-making process because they want their money to be invested in a venture that has good returns.

In this study, the financial statements and the historical data was used. The first step will be to calculate the return rate over the last 20 years the company has been operating. This is from 2000 to 2020. It is easy to make the analysis and conclusions by feeding them into the formula with these values. The investors use the return rate to estimate whether a business investment is lucrative (Burkhanov, 2018). It is the ratio of the money gained or lost by a company compared to the money is invested in the beginning. Therefore, using the return rate over several years, the investors can see the trend and hence make crucial financial decisions. The companies also can use these values to forecast the maximum wealth the stockholders should anticipate. Since investors focus on where they get the best returns, with these values, it is possible to compare companies and decide the one with greater yields and returns.

ROI formula is

ROI = [(Final Value of Investment – Initial value of investment)/ Initial investment] * 100

For Walmart,it will be:

ROI = [144.47-57.55]/57.55*100

= 151.03%

From this formula, it is clear that Walmart has been making profits over the years that are above 100%. A higher value of ROI is an indication of good performance over time on the company’s profits compared to the initial investment. With this value, investors will have some confidence in investing in the company.

Every business faces a form of risk in its operations. This is why the investors must investigate the risks involved in a company to decide on investment. The value of beta is an important indicator used to show the sensitivity of stock prices to the systematic risks. These are the risks that are measured by observing the market portfolio. When the beta value is 1 it shows that the company stock moves at the same rate as the market. When the value is greater than 1,it shows that the stock is vulnerable to systematic risks, whereas below 1 shows less sensitivity (Potashnik et al., 2017).Beta’s value above 1 means that the investors will demand higher returns on their investment, although they are aware the risk involved is higher. The beta value for Walmart is 0.89. This value is below 1, and hence the company experiences lower systematic risks in the market. Therefore, the stocks will have a lower return and lower risk. For investors interested in certainty in returns, this is a good company to invest in, whereas those in need of high returns will not consider Walmart.

It is essential to understand the risk-free return rates and their impacts on the business. Thisrate shows the interest the investor expects from an investment free from risks within the period they commit their resources to a firm. Beta is used in determining the return of annual rate of the U.S. treasury. The risk-free rate value is obtained by subtracting inflation rate from yield (Fernandez et al., 2019). Therefore, the formula is;

Risk free return = (Yield rate + Beta) * equity risk premium

= (1.50 + 0.89) * 5.75

= 13.74%

Therefore, when the investors make their investment, they will get 13.74% of risk-free returns from the U.S. National treasuries.

Companies are faced with the problem of debt, especially when their performance is poor. Therefore, before the investors decide to invest, they must consider debt. A good company can pay its debts, and hence it is impossible to invest in a firm struggling to pay (La Rosa et al., 2018). The cost of debt is the total interest rate the firm pays on its debts. It includes costs such as bonds and loans. It is the amount before the taxes are taken into account.

Cost of debt = Interest amount / (1-tax rate)

= 0.0314/ (1-0.303)

= 4.5%

The cost of debt is considered low, and hence investors can be encouraged to invest with Walmart.

WACC is the rate is the rate of paymentto the holders of security for asset financing. It is the cost of capitaland it is influence by external factors (Vartiainen et al., 2020). WACC is used with the value of Beta because the two are directly proportional. When one goes up, it leads to a similar change on the other.

WACC=VE∗Re+VD∗Rd∗(1−Tc)

WACC = 4.2%.

The different values obtained for Walmart indicate that it is a safe company for the investors. Therefore, they can commit to their finances and expect average returns because the ROI is not very high.

The average annual rate of return for Different Companies

Walmart

Average annual return rate= (Final stock price – Initial stock price) / Initial stock price

For the year 2019/2020, the average annual return rate for Walmart was

= (129.6013 -108.4054)/ 108.4054

= 19.55%

Average annual return ratein 2019/2020 was 19.55%

Amazon

For Amazon Inc.,

Average annual return rate= (2680.8551- 1789.1929)/ 1789.1929

= 0.498

Average annual return rateis 49.8%

Starbucks

Average annual return ratefor Starbucks is

= (82.8624 – 81.4432) / 81.4432

= 0.017

Average annual return rate is 1.74%

Pepsi

Average annual return rateis

= (136.3060 – 127.9365)/ 127.9365

= 0.065

Average annual return rateis 6.5%

Boeing

Average annual return ratefor Boeing is given by;

= (197.095 – 365.0274)/ 365.0274

= -0.46

Average annual return rate is -46.0%

In conclusion, the investors are supposed to consider the factors such as the Beta, the cost of debt, and the ROI to make the right decisions on whether to invest in a company or not. Wrong decisions at any point mean that the investor can lose the money. Therefore, it is essential to consider the market factors such as the variations in demand and all systematic risks. Investing in any of the companies mentioned above requires considerations of market, technology, and the preparedness they show in handling disasters.

References

Burkhanov, A. U. (2018). The practice of Investment Funds Development in Developed Countries. Theoretical & Applied Science, (4), 275-284.

Vartiainen, E., Masson, G., Breyer, C., Moser, D., &Román Medina, E. (2020).Impact of the weighted average cost of capital, capital expenditure, and other parameters on future utility‐scale P.V.levelised cost of electricity. Progress in photovoltaics: research and applications, 28(6), 439-453.

Potashnik, Y. S., Garina, E. P., Romanovskaya, E. V., Garin, A. P., &Tsymbalov, S. D. (2017, July). Determining the value of own investment capital of industrial enterprises.In International Conference on Humans as an Object of Study by Modern Science (pp. 170-178).Springer, Cham.

La Rosa, F., Liberatore, G., Mazzi, F., &Terzani, S. (2018). The impact of corporate social performance on the cost of debt and access to debt financing for listed European non-financial firms. European Management Journal, 36(4), 519-529.

Fernandez, P., Martinez, M., &FernándezAcín, I. (2019). Market risk premium and risk-free rate used for 69 countries in 2019: A survey. Available at SSRN 3358901.

Pepsi Inc., (PEP). (n.d.). Retrieved on February 15, 2021, from https://www.macrotrends.net/stocks/charts/PEP/pepsico/stock-price-history

Boeing, (B.A.). (n.d.). Retrieved on February 15, 2021, fromhttps://www.macrotrends.net/stocks/charts/BA/boeing/stock-price-history

Starbucks, (SBUX). (n.d.). Retrieved on February 15, 2021, fromhttps://www.macrotrends.net/stocks/charts/SBUX/starbucks/stock-price-history

Walmart, (WMT). (n.d.). Retrieved on February 15, 2021, from https://www.macrotrends.net/stocks/charts/WMT/walmart/stock-price-history

Amazon Inc., (AMZN). (n.d.). Retrieved on February 15, 2021, fromhttps://www.macrotrends.net/stocks/charts/AMZN/amazon/stock-price-history

Appendix

WALMART 2020 BALANCE SHEET

https://www.macrotrends.net/stocks/charts/WMT/walmart/balance-sheet

WALMART 2020 INCOME STATEMENT

https://www.macrotrends.net/stocks/charts/WMT/walmart/income-statement

Walmart Historical Annual Stock price

https://www.macrotrends.net/stocks/charts/WMT/walmart/stock-price-history

PEPSiCo Historical Annual Stock price

https://www.macrotrends.net/stocks/charts/PEP/pepsico/stock-price-history

BOEING Historical Annual Stock price

https://www.macrotrends.net/stocks/charts/BA/boeing/stock-price-history

Starbucks Historical Annual Stock price

https://www.macrotrends.net/stocks/charts/SBUX/starbucks/stock-price-history

Amazon Historical Annual Stock price

https://www.macrotrends.net/stocks/charts/AMZN/amazon/stock-price-history

PORTFOLIO ANALYSIS 1

PORTFOLIO ANALYSIS 2

Portfolio Analysis

Name

University

Subject

Prof.

Date

Portfolio Analysis

Investing comes with a lot of risk associated with it already but with the pandemic on top it, adds a lot of uncertainty and risk to the market. Hence, in order to make a safe and educated a market as well as individual company study is required before investing. This paper discusses the factors to consider and how to evaluate a company and its performance for investing. Multiple companies with their historic data are analyzed in this paper. Historic data along with aspects like CV (coefficient of variance), mean, variance, standard deviation will be discussed in their paper for various securities.

Amazon Inc, Apple Inc., Berkshire Hathaway, Microsoft corp and Intel Corp are the five securities analyzed in this paper. First the rate of return is calculated for the five securities based on their past 20 years of historical data. (Finance Yahoo, 2020).

The mean, variance and standard deviation was calculated once the yearly rate or return and average yearly price was found. One of the best methods to analyze a portfolio and to come to a meaningful conclusion is to run a mean- variance model on it (Harry, Peter $ William, 2000). Mean is the sum of all values divided by the total number of values. So, to find the of each security the return for their last 20 years was calculated and then it was divided by 20 years. Variance is calculated by the squared difference of the mean and it helps understand the return over the entire period of time. Lastly, standard deviation was calculated which is simply the square root of the calculated variance.

Below are all the calculated mean, variance and standard deviation for all five securities:

The next important aspect calculated is the co-variance and correlation and these helps us understand the relationship between two securities. Correlation value cannot exceed 1 or go below -1 hence it always stays between -1 and 1. A negative value of the correlation between two securities helps us understand that the relationship shared between two securities is not the strongest and it may turn out to be a loss over time (Westfall, 2020). In the portfolio calculation shows in this paper, the relationship between Amazon and Apple and then between Microsoft and Apple have a positive value and is very strong, followed by Berkshire Hathaway and Intel and then Amazon and Microsoft, which also has positive value and strong relationship. All the other combinations and relationships turned out to be weak when compared to the below strong relationships.

Weightage of stocks is very important when managing a portfolio for the best returns possible. It is so important to have the correct weightage of stocks in a portfolio that if not done correctly then it may totally eat up the entire profits of the portfolio. Proper weight distribution of stocks also helps mitigate the risk of losses and betters the chances of good returns (Seralurin, 2019). In the table below the CV (coefficient of variance), mean, variance and standard deviation is calculated and put according to its weightage.

Starting with $1,000,000 dollars in this portfolio, 5 different companies were invested in as discussed above. As first case, the 1 million dollars were equally divided and invested in these 5 securities, therefore the weight of each stock came to be 20% per security, which is 0.2 per security. Now the weightage was changes according to the security that turned the highest return with minimum risk and hence the weightage was distributed for apple to 69% for Apple, for Amazon to 24%, for Microsoft to 3%, for Berkshire Hathaway too 3% and then lastly for Intel to 1%.

With the calculations, Apple’s stock was found to be the one with highest returns and least risks when compared to other securities. Apple’s future plans are to introduce more new products to the market which will eventually soar its stock price even higher and when the historical data of Apple is compared with other securities it clearly shows that it has turned highest profit in 20 years. An initial investment of $200,00 in Apple 20 years ago, would turn it into approx. $2,685,000 today. While the same amount of initial investment in Intel would have led to loss of $30,000 over 20 years.

The graph below shows the ups and downs of each individual security in the portfolio over the 20 year period.

In conclusion, portfolio management is very important for anyone considering to invest in the stock market and looking to maximize gains while minimizing risks. After considering all the aspects and scenarios of the above mentioned portfolio, it is quite evident that this portfolio will turn a huge profit if managed this way and this was calculated on the basis of CV, mean, standard deviation, variance for all 5 securities. With this portfolio and weightage of the above mentioned securities if we would have invested $1,000,000 20 years ago then it would have turned 40% profit today.

References

Apple Inc. Common Stock (AAPL). (n.d.). Retrieved on October 25 from https://www.nasdaq.com/market-activity/stocks/aapl

Apple Inc. (APPL). (n.d.). Retrieved on October 25 from https://finance.yahoo.com/quote/AAPL?p=AAPL

Beattie, A. (June 1 , 2020). A Guide to Calculating Return on Investment (ROI). Retrieved on October 25 from

https://www.investopedia.com/articles/basics/10/guide-to-calculating-roi.asp

Chen, J. (June 25 , 2020). Risk-Free Rate of Return. Retrieved on October 25 from

https://www.investopedia.com/terms/r/risk-freerate.asp

Crawford, C. (n.d.). Purpose of Financial Analysis. Retrieved on October 25 from

https://smallbusiness.chron.com/purpose-financial-analysis-59178.html

Hargrave, M. (April 20 , 2020). Weighted Average Cost of Capital – WACC. Retrieved on October 25 from

https://www.investopedia.com/terms/w/wacc.asp

Marriott International, Inc. (MAR). (n.d.). Retrieved on October 25 from

https://finance.yahoo.com/quote/MAR?p=MAR&.tsrc=fin-srch

Microsoft Corporation (MSFT). (n.d.). Retrieved on October 25 from

https://finance.yahoo.com/quote/MSFT?p=MSFT&.tsrc=fin-srch

Nickolas, S. (June 11 , 2019). The Formula for Calculating Beta. Retrieved on October 25 from

https://www.investopedia.com/ask/answers/070615/what-formula-calculating-beta.asp

The Coca-Cola Company (KO). (n.d.). Retrieved on October 25 from

https://finance.yahoo.com/quote/KO?p=KO&.tsrc=fin-srch

Walmart Inc. (WMT). (n.d.). Retrieved on October 25 from

https://finance.yahoo.com/quote/WMT?p=WMT&.tsrc=fin-srch

Zarzycki, N. (March 13 , 2020). The Cost of Debt (And How to Calculate It). Retrieved on October 25 from

https://bench.co/blog/accounting/cost-of-debt/

Appendix: