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.
Q1
Woolworth
a)
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
b)
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
1
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
c)
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
d)
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
a)
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
b)
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
c)
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
d)
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
0.069994234
Variance
0.022253736
Crown Resort
a)
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
b)
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
1
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
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
c)
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
d)
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.
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.
Q2
a)
Weights
Weights
Woolworth
0.40
0.3
Commonwealth Bank
0.28
0.25
Crown Resort
0.32
0.45
Sum
1.0
1
Expected Return
5%
6%
Standard Deviation
11%
12%
Risk free rate
1.50%
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.
b)
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.
c)
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).
d)
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.
