Report

Name of the Writer

Name of the University

Introduction

One of the most valued tools within businesses is the business REPORT. The business report has helped businesses all around the world to track and analyze the performance and the business health overall. A well worked out business report can be helpful in revealing nuances that are in relation to the core business functions. Furthermore, a well-organized report also has the ability to present opportunities to businesses that can help increase the current market gains of the business. The use of business reports is not for independent contractors and models with a single owner. This is because they are only focused on reviewing financials. On the other hand businesses of varying nature such as small, large and medium are all beneficiaries of a detailed report. This business report comprises of financial modelling of three different companies. These companies are Woolworth, CommonWealth Bank Australia and Crown Resort. This report focuses on the financial modeling approach on a selected period of share prices of each of these organizations. These are then used to solve the different questions.

Woolworth

The table below shows the statistical analysis of the data of Woolworth. The data was checked for any outliers but there were no presents.

Woolworth

Statistical analysis

Mean

24.88908115

Standard Error

0.485664975

Median

26.895

Mode

27.11892

Standard Deviation

6.936683315

Sample Variance

48.11757541

Kurtosis

-0.525433312

Skewness

-0.565749762

Range

26.277672

Minimum

11.252328

Maximum

37.53

Sum

5077.372554

Count

204

Confidence Level (95.0%)

0.957594785

The table below shows the compounded monthly returns of Woolworth. The compounded monthly rate of returns are calculated for each and every month within the given period.

Compounded monthly returns

0.94239894

1.01518536

0.953405226

0.941408671

1.024122465

1.00170097

0.982858682

0.982911528

0.987612653

1.007804225

1.061331086

1.014189316

0.930117386

0.978692376

0.94199667

1.048989387

0.975805271

1.012903405

0.985611263

0.960972505

0.973770272

1.030855797

1.026567027

0.955582285

1.019771918

1.031364747

0.985762217

0.971678168

0.953923542

0.979465398

0.948488293

1.032662981

0.983801862

1.018731923

0.987266381

0.886107294

1.057211601

0.999547818

0.999547614

1.035559302

1.058516056

1.009020151

0.965535306

1.018418269

1.029828565

1.060908753

1.080042708

0.940947568

1.037850224

1.051507557

1.000339156

1.040537112

1.034248827

0.964773815

1.014239723

1.145668239

0.949875613

1.054558984

1.021612112

0.952048456

1.06352651

0.994388763

0.95618138

1.009750739

0.942955737

0.993521768

0.995262058

1.035285772

1.002861232

1.018961938

0.931847818

0.98457553

1.005774214

1.098335901

0.925321439

1.033181158

0.888353266

0.936911558

0.998293806

1.016259815

0.97765078

1.029464276

0.963907407

0.936044671

0.986476127

0.980908164

1.001540832

0.973872563

0.979240771

1.0124275

0.980691275

0.96654654

1.043154426

1.014377244

1.06597484

1.029623669

0.98767207

0.96661411

1.013860495

0.99328105

1.010061572

0.99367675

1.056225143

1.017136282

0.961832823

0.926390684

1.046590513

0.986211818

1.016744577

1.032670782

0.957688802

0.962804061

1.079507137

1.001784122

1.020466487

1.021424455

0.957038967

0.97213498

0.966794189

0.96093102

1.052633619

0.933863396

1.044190963

1.057965705

0.962106925

1.004862549

1.036634133

0.978180953

1.041762728

0.881864293

0.969786221

1.125523958

1.034742948

1.010398707

0.999655113

0.993424444

1.165690977

0.995872636

0.988112787

0.881431903

1.007043464

0.907993182

0.989316531

1.018712709

1.02831223

0.959987454

1.000367715

0.864365557

1.006295928

0.917163994

0.938608038

0.978998007

1.021485201

0.916408329

1.056662259

0.919957292

1.003220615

1.009069147

0.976899322

0.935461479

0.976538513

1.00945075

0.959202501

1.018204386

0.976884616

1.009188426

1.006684923

0.972987922

0.95024484

1.04850983

0.968994669

0.935632844

1.028380283

0.999333555

0.885710854

1.017778246

0.926117914

0.922785962

0.973170178

1.026829822

1.007656354

1.004228336

0.989821795

1.000852152

1.005097717

0.960236767

0.980408991

1.052551495

1.003407158

1.003395589

1.059227834

0.978198571

1.053250561

0.945115451

0.944523592

1.031478567

0.953719247

0.992957717

1.083840867

0.999187322

0.975307387

1.010775073

The table below shows both the arithmetic average monthly return for Woolworth. Furthermore, this table also represents the calculated standard deviation of the monthly returns on the stocks of Woolworth.

Arithmetic average monthly return

-0.003829356

Standard Deviation

0.046336486

The table underneath details the values for the annualized arithmetic average returns on the stocks of Woolworth. Furthermore, the calculation of the variance-covariance has been done in the table below as well.

Arithemtic Average

0.073067

Variance

0.029438

CBA

The table below shows the statistical analysis of the data of the Commonwealth Bank. The data was checked for any outliers but there were no present.

CBA

Statistical analysis

Mean

55.89928

Standard Error

1.320144

Median

52.37564

Mode

79.43

Standard Deviation

18.85543

Sample Variance

355.5271

Kurtosis

-1.26718

Skewness

0.090353

Range

68.48816

Minimum

24.40748

Maximum

92.89564

Sum

11403.45

Count

204

Confidence Level (95.0%)

2.602953

The table below shows the compounded monthly returns of Commonwealth Bank. The compounded monthly rate of returns are calculated for each and every month within the given period.

Compounded returns

0.947039519

0.947841541

0.946529011

1.045792633

0.943819511

1.034859468

0.98384591

0.971520217

1.031003621

0.997616543

1.048629728

0.973992845

0.949767875

1.035718083

1.006799443

1.054889365

1.03194913

1.018466702

0.988608502

0.97707782

0.968861953

1.007282389

1.099499044

0.988951491

0.961093209

1.092853246

0.982804954

0.957313855

0.991950194

1.009142503

0.953300841

0.930780057

0.986418613

0.991817443

1.074316835

0.960712058

1.040321668

0.953203198

1.013843377

0.934072367

1.114768644

1.083605303

0.926008939

0.965416636

0.946323684

1.031937762

1.148355428

0.971855227

0.99953002

1.043465108

1.049716717

0.984027288

0.971418744

0.957931847

0.940717151

0.99702233

0.933338618

1.077044665

1.029444182

0.965130404

1.008740131

0.966474522

0.981322215

0.963441031

0.994223908

1.04697305

1.000257036

0.977386951

0.93384784

1.022631997

1.018633657

0.929812892

0.965889077

1.094004022

0.923049851

0.989059619

0.957175357

0.964143675

0.959131098

0.966958847

0.965112741

0.981358613

1.049711847

0.919870377

0.927773496

1.050716205

0.963354379

0.986536519

1.024579117

0.9711634

0.962322184

1.038693148

0.921495247

1.056963249

1.021541504

1.059680995

0.967350384

1.059252449

0.975307387

1.013458643

0.98768574

0.967254655

0.949711767

1.01276

1.045376026

0.982851654

1.043950297

0.922490863

1.054608107

1.130143335

0.96131922

0.956984567

0.987120681

1.02998004

0.961909004

0.9895287

0.990384541

0.882216964

0.927896706

0.907023544

0.895778437

0.999145907

0.989117877

0.846904527

0.897617893

1.071715309

1.165162491

1.167347383

1.055972158

0.994824736

0.935461479

1.010510607

1.052139339

1.058063801

0.929811957

1.007624531

1.159190348

1.1792805

1.009263224

1.030543026

0.913254663

0.977764908

0.983730562

1.018081033

1.002711253

0.951173734

0.951456204

0.994013153

1.0016

0.988745862

0.957686392

1.005676458

0.958260394

0.999344047

0.9796723

0.99125653

0.971912756

1.084770177

0.963159431

0.990016556

0.995530719

0.956514888

0.977527144

0.92758364

0.987837863

0.973352862

1.043428221

0.971170578

0.975998848

0.980650711

0.961285488

1.035682954

0.922254771

0.956713755

0.978270283

1.021729717

0.938654714

1.000331181

1.033216991

1.042639688

0.999078765

0.959242008

1.063538021

0.95012817

0.962368932

0.964639411

0.935121863

0.996370232

1.007246409

1.012556219

1.062196208

0.989898904

0.954655669

0.963212585

0.956065771

0.940286671

1.054324555

1.041207871

1.00554018

1.113065562

0.991410585

1.023931645

1.009671255

The table underneath details the values for the annualized arithmetic average returns on the stocks of Commonwealth bank. Furthermore, the calculation of the variance-covariance has been done in the table below as well.

Arithmetic average monthly return

-0.003378859

Standard Deviation

0.054712237

Arithmetic Average

0.069994234

Variance

0.022253736

Crown Resort

The table below shows the statistical analysis of the data of Crown Resort. The data was checked for any outliers but there were no present.

Crown Resort

Statistical Analysis

Mean

10.52217

Standard Error

0.233671

Median

10.96986

Mode

13.5

Standard Deviation

2.754944

Sample Variance

7.589714

Kurtosis

-0.93088

Skewness

-0.0128

Range

11.81082

Minimum

4.335616

Maximum

16.14643

Sum

1462.581

Count

139

Confidence Level (95.0%)

0.462039

The table below shows the compounded monthly returns of Crown Resort. The compounded monthly rate of returns are calculated for each and every month within the given period.

Compounded monthly returns

1.006405146

1.058886104

0.85620435

0.995646489

1.041868567

0.992440115

0.986417448

1.066939483

1.090137315

1.036576325

0.949447721

0.98507435

0.974103656

0.978946591

1.061875404

0.981307867

0.982489084

0.947169426

0.938210733

0.973820494

1.025317808

1.09217046

0.964796365

1.053123865

0.964632856

0.943217986

1.00165012

0.895165939

1.015666117

1.013722342

0.924843123

1.185530361

1.001524391

0.997712542

0.96187792

0.95374429

0.98071219

1.053576883

0.930204238

1.054429507

1.018571386

0.928718798

0.984415269

0.853672046

1.144581229

1.17515726

0.889162706

1.070412628

0.993869713

1.027295099

1.134839437

0.882951988

0.930009465

1.125024071

1.006239188

0.952591052

1.135341348

1.029312981

0.92669545

1.072689733

0.986377498

1.036122691

1.03834147

0.964660634

1.01014026

1.002962965

0.998223275

0.918523742

0.937645587

0.867738945

0.944586387

1.059312254

1.0038835

0.952371951

0.976140374

0.964403021

0.917293408

0.95984164

0.946907913

0.934088795

0.986725469

0.939166559

1.004722559

0.998821449

1.070563964

0.953898526

0.966058767

0.962397025

1.013505423

0.984019328

0.987531011

1.044178963

1.06843249

1.001120448

0.98077951

0.961600867

0.966221155

1.024243612

1.027172375

0.960779287

0.995139723

1.014510533

1.007177064

0.986802448

0.960586031

0.975841165

1.00641439

1.052315789

0.992691807

0.977749391

0.969540793

1.035446749

0.973533427

1.05225069

1.079203195

0.878711387

0.946775239

0.968853271

0.994482745

0.951832049

0.918383728

0.798278884

1.065260804

1.074752427

0.747967439

1.36739316

1.237958637

1.073687716

0.858159396

1.157025278

1.125289773

1.040935948

0.956210983

1.099629841

1.005159083

1.142814925

The table underneath details the values for the annualized arithmetic average returns on the stocks of Crown Resort. Furthermore, the calculation of the variance-covariance has been done in the table below as well.

Average monthly return

0.003248425

Standard Deviation

0.082435603

Arithmetic Average

0.061934044

Variance

0.035964768

Conclusion

Under this question the different calculations were done for the three companies chosen.. In order to do a statistical analysis, the closing prices of Woolworth, Commonwealth Bank, and Crown Resort were taken and their prices were analyzed to find any outliers within the data. With no outliers, it shows that the data was concise and within the accepted framework. Furthermore, using the closing prices, the rest of the calculations were done

In comparison to the standard error of the three companies, Commonwealth Bank has the highest standard error in comparison to the other two (Wolfers & Zitzewitz,2018) . This entails that the data spread of the closing prices of the shares of the Commonwealth Bank are too apart with allot more variations compared to its counterparts. Furthermore, Commonwealth bank also has a higher standard deviation compared to the other companies. The data within the commonwealth bank spread is not close to the mean. Whereas, the data for crown resort is closest to its mean in comparison to the other two.

However, once the data was restructured and positioned to find the monthly returns to find the arithmetic average monthly return the positioning of the companies shifted. Crown resort had the highest positive monthly return but had the highest standard deviation of the three companies (Mc Grath, et al, 2019). In comparison Woolworth and Commonwealth Bank both had negative monthly returns but Woolworth had the lowest standard deviation out of the three companies. This means that its data was closest to its mean in comparison to all three.

In order to calculate the annualised arithmetic average return, year end values at july were taken for each company. Upon calculation it was noticed that Woolworth had the highest annualised arithmetic average return for each year (Chambers, et al, 2017) . After Woolworth, Commonwealth Bank had the highest annual average returns. On the other hand, in the data set of the annual returns, Crown resort had the highest variance, making its values too further away from its mean. Compared to it Woolworth had a lower variance, and Commonwealth Bank had the lowest variance. The values for these two are closest to their means.

Weights

Woolworth

0.40

0.3

Commonwealth Bank

0.28

0.25

Crown Resort

0.32

0.45

Sum

1.0

Expected Return

Standard Deviation

11%

12%

Risk free rate

1.50%

Sharpe ratio

0.32%

0.34%

Researchers have often argued over the importance of combination lines and most importantly the presence of short sales constraint (Yin and Tian, 2017). This question focuses on the three companies and the combination lines in between the two companies and the returns that are received by using short sale constraints (Patatoukas, et al, 2018). In order to find this, a couple of things were needed such as the variance covariance matrix and the expected return. The variance-covariance matrix and the expected return was found in the previous question. In order to find the short sales constraints, the solver function was used in the excel. This function allowed for making the sharp ratio the factor while maximizing the value of the weightage of the shares in comparison to their sum being kept at hundred percent. The answers show that using this function the weightage of shares of Woolworth first changed into 0.40 and then fell to 0.30. Furthermore, the weightage of Commonwealth bank shares in the portfolio became 0.25 from 0.28. Finally, the weightage for the Crown Resort was originally found to be 0.32 but in the next calculation its value changed to 0.45. On the other hand, the standard deviation rose 11% to 12% after the two calculations.

True minimum variance

Woolworth

80.00%

Commonwealth Bank

16.00%

Crown Resort

4.00%

Exp Portfolio return

34.38%

Exp Portfolio variance

1.38%

A minimum variance set or portfolio, is an indication of a portfolio that is well diversified and has within it a collection of risky assets (Bodnar, et al, 2017). When these risky assets are traded together, they are hedged in such a way that the lowest possible rate of risk can be found for the expected return. In this manner, weights of the three stocks such as Woolworth, Commonwealth Bank and Crown Resort are subjected to the excel solver function, in order to determine the combination of the asset portfolio that will give the lowest possible risk and the highest possible reward. This way the risk within each of the three individual assets is being leveraged. This then hedges the total portfolio risk of the three assets to a smaller, more acceptable form of risk in relation to the total expected rate of return of the portfolio (Yang, et al, 2015). In the above table, the minimum variance set has been found using excel. Under the calculated minimum variance set, the minimum value of risk that can be acceptable to investors is when the eighty percent investment is done in Woolworth, sixteen percent in Commonwealth Bank and four percent in Crown Resort. The portfolio with this type of composition will allow for the occurrence of minimum level of risk in comparison to the portfolio expected return.

80.00%

16%

4.00%

4.09%

11.74%

At eighty percent investment in Woolworth, sixteen percent in Commonwealth Bank and four percent in Crown Resort, the risk is hedged at 11.74 percent. At this risk the return on portfolio is 4.09 percent. This shows that below this value the portfolio combination would not be accepted because this has the least amount of variance within it.

The capital market line is that line that is a representation of the portfolios that are able to combine optimally both risk and return (Garcia and Borrego, 2017). Similarly, the capital asset pricing model is a depiction of trade off in between return and risk. This is mostly present in efficient portfolios. Furthermore, it is a concept of a theoretical nature because it is the representation of all the portfolios that are able to combine risk and return optimally and the risky assets in the market portfolio. A position will be chosen by all the investors under capital asset pricing model on the capital market line (Campbell, et al, 2018). This will be in equilibrium through borrowing or lending at the rate that is risk free. This is done due to the fact that it gives the maximum level of return.

In order for us to draw the capital market line, we first had to formulate the risk free return. In order to do this, a treasury note was found from the commonwealth bank in accordance with the number of years the annual returns are based upon for the portfolio of assets. The risk free rate from the treasury rate was drawn to be 1.50 percent. This is the return at which there is no risk present with a healthy 1.50 percent return. A line is drawn from this point on to the efficient frontier and where it would intercept this frontier that is the most efficient portfolio. So the most efficient portfolio would be the one that gives us a healthy return of 4.61 percent at a risk of 12.92 percent. This portfolio would be comprised of investment in Woolworth (around sixty percent), in Commonwealth Bank (around twelve percent) and in Crown Resort (around twenty eight percent).

As already discussed above, the optimal or the most efficient portfolio on the capital market line is sixty percent of Woolworth, twelve percent of Commonwealth Bank and twenty eight percent of Crown Resort. In this the expected return is supposed to be of 4.61 percent and with a standard deviation or risk of 12.92 percent. However, using the risk aversion we have to determine the portfolio weights, the return on the portfolio and the standard deviation of a portfolio for an investor who has a degree of risk aversion.

By risk aversion or risk averse investors most financial gurus refer to the investors who, when given a choice two portfolios with the same expected return. On the other hand, there is varying degree in the level of risk of both the portfolios (Guiso, et al, 2018). The investor who is risk averse will chose the portfolio with the least amount of risk attached to it. The term risk averse can also be contrasted with risk seeking. Risk averse investors stay away from risk and dislike it. They further try to stay as far as possible from stocks that are supposed to be high risk and even are ready to forego high rates of return for that level of risk.

Utility =

Expected return-0.5*A*volatility

From the optimal portfolio

Utility =

4.61-0.5*3*(12.92*(252)^0.5

Utility =

-303.038

In the case of the investor that has a risk aversion of three, the utility was calculated. Utility came out as negative and showed that the investor with this much risk aversion should not invest in such a portfolio. This is because the more and more he invests in such a stock, the higher the likely chances that the investor will lose allot more. The calculation above, hence proves that.

Q3: a.i) Beta is regarded as a measure of the volatility or systematic risk of a specific stock when compared to unsystematic risk of the entire market (Kenton, 2015). In statistic beta is used to represent the slope of the line through regression. Beta measured how volatile a specific can be the entire stock. Using first-pass regression to calculate the Beta for two companies. The Beta for Crown resort is established to be 0.01318 as illustrated in the excel file attached. The Beta for Commonwealth bank of Australia -0.00600. However, the beta for Woolworth is obtained to be -0.001434265. From this analysis it is evident that CBA is volatile to the S&P 500 market and therefore, its market performance affect the performance of the market.

ii.) The average portfolio for the market is obtained to be 0.044285932 variance of the portfolio is 0.004831682 and the standard deviation of the market portfolio of the market is 0.069510299.

b. The estimated equation for the second pass regression would be

= r1-r1=y0+y1b1+y202 (e1)

The second pass regression therefore, would be

Y0=0; y1= rm-rf y2 = 0

C. SML plot of the beta

Commonwealth Bank of Australia beta Plot

Crown Resort beta

Woolworth Beta plot

D. The SML would be used for investment decision. It would help in determing the volatile or the most risk company to invest in and therefore, provide understanding on the market investment. However, based on the CAPM analysis it is evident that Commercial Bank of Australia is highly volatile and it performance affect the general market performance compared to other companies Woolworth, and Crown Resort. It is therefore, means that the stock performance of CBA is not good. However, the best performing company among the three companies is the Crown Resort.

References

Bodnar, T., Mazur, S., & Okhrin, Y. (2017). Bayesian estimation of the global minimum variance portfolio. European Journal of Operational Research, 256(1), 292-307.

Campbell, J. Y., Giglio, S., Polk, C., & Turley, R. (2018). An intertemporal CAPM with stochastic volatility. Journal of Financial Economics, 128(2), 207-233.

Chambers, J. M., Freeny, A. E., & Heiberger, R. M. (2017). Analysis of variance; designed experiments. In Statistical models in S (pp. 145-193). Routledge.

Garcia, M. T., & Borrego, D. (2017). Markowitz efficient frontier and capital market line: evidence from the portuguese stock market. European Journal of Management Studies, 22(1), 3-23.

Guiso, L., Sapienza, P., & Zingales, L. (2018). Time varying risk aversion. Journal of Financial Economics, 128(3), 403-421.

McGrath, S., Zhao, X., Steele, R., Thombs, B. D., & Benedetti, A. (2019). Estimating the sample mean and standard deviation from commonly reported quantiles in meta-analysis. arXiv preprint arXiv:1903.10498.

Patatoukas, P. N., Sloan, R. G., & Wang, A. Y. (2018, March). Short-sales constraints and aftermarket IPO pricing. In 27th Annual Conference on Financial Economics and Accounting Paper.

Wolfers, J., & Zitzewitz, E. (2018). The" Standard Error" of Event Studies: Lessons from the 2016 Election. In AEA Papers and Proceedings (Vol. 108, pp. 584-89).

Yang, L., Couillet, R., & McKay, M. R. (2015). A robust statistics approach to minimum variance portfolio optimization. IEEE Transactions on Signal Processing, 63(24), 6684-6697.

Yin, Y., & Tian, R. (2017). Investor sentiment, financial report quality and stock price crash risk: role of short-sales constraints. Emerging Markets Finance and Trade, 53(3), 493-510.

More Subjects

Education

(5614)

English

(2294)

Business And Management

(1760)

Healthcare And Nursing

(1684)

Psychology

(929)

History And Anthropology