Question
Use Excel to answer the questions 1. Suppose that you invest 20% in Singapore Airlines Limited, 50% in Qantas Airways Limited and 30% in Cathay
Use Excel to answer the questions
1. Suppose that you invest 20% in Singapore Airlines Limited, 50% in Qantas Airways Limited and 30% in Cathay Pacific Airlines. Calculate the portfolio expected return, variance and standard deviation.
2. How does the expected return and standard deviation change if you decide to invest 30% in Singapore Airlines Limited, 60% in Qantas Airways Limited, and 10% in Cathay Pacific Airlines? Explain your answers.
3. Find the weights of Singapore Airlines Limited, Qantas Airways Limited and Cathay Pacific Airlines that will provide a daily return of 0.01% with the lowest level of risk. 4. Plot a scatter diagram showing the portfolios from parts (e), (f) and (g), with the X-axis displaying standard deviation and the Y-axis displaying daily returns.
5. If the daily risk-free rate is 0.1%, estimate the Sharpe ratio for the portfolios mentioned in parts (e), (f) and (g) and highlight the highest Sharpe ratio using conditional formatting.
D 18 19 24 14 15 Qanntas Airways Limited daily stock return Singapore Airlines Limited daily stock return Cathay Pacific Airlines daily stock return Date 16 17 -0.053254438' 0.01618705 0.009852217 -0.046875 -0.026548673 -0.001219512 01 0.045454545 0.00976801 20 0.042622951 0.048695652 0.027811366 21 0.022012579 -0.008291874 0.012941176 22 0.095384615 0.025083612 0.049941928 23 0.033707865 0.019575856 0.008849558 -0.035326087 -0.0128 -0.020833333 25 -0.022535211' -0.003241491 0.008958567 26 0.07204611 0.01300813 0.038845727 27 -0.034946237) 0.003205128 28 07 0 -0.035313002 -0.052183174 29 -0.064066852 0.0083194681 0.013483146 30 -0.005952381 -0.286130363 -0.00443459 31 0.01497006 -0.016287375 -0.008908686 32 0.044247788 0.008278523 -0.003370787 33 -0.011299435 -0.013135018 0.024802706 0.048571429 0.013309844 0.0110011 35 -0.029972752 0.003283755 -0.014145811 0.016853933 -0.026186378 0.008830022 -0.033149171 -0.00672204 -0.002188184 -0.011428571 0.056676273 -0.012061404 -0.01734104 0.002272727 0.012208657 40 0.038235294 0 0 0.002192982 0.002832861 -0.027210884 -0.004376368 42 0.002824859 -0.067599068 -0.015384615 43 -0.061971831 -0.0475 -0.006696429 0.006006006 0.023622047' -0.005617978 45 0.008955224 -0.046153846 -0.032768362 46 0.035502959 -0.005376344 0.005841121 47 0.008571429 -0.035135135 0.016260163 2020/4/1 2020/4/2 2020/4/3 2020/4/6 2020/4/7 2020/4/8 2020/4/9 2020/4/14 2020/4/15 2020/4/16 2020/4/17 2020/4/20 2020/4/21 2020/4/22 2020/4/23 2020/4/24 2020/4/27 2020/4/28 2020/4/29 2020/5/4 2020/5/5 2020/5/6 2020/5/7 2020/5/8 2020/5/11 2020/5/12 2020/5/13 2020/5/14 2020/5/15 2020/5/18 2020/5/19 2020/5/20 34 36 37 38 39 41 44 an ' nora 20air B 18 9 10 0.019607843 -0.002747253 -0.008264463 0.044444444 0.015957447 * 51 =2 13 14 55 5 6 17 58 59 =0 51 12 =3 4 5 =6 0.028328612 -0.008264463 0.072222222 0.054404145 0.004914005 0 0 -0.024449878 0.005012531 -0.007481297 0.052763819 0.071599045 0.031180401' 0.064794816 0.004056795 -0.061646465 -0.030237581 -0.035634744 0.043879908 ' 0 -0.037610619 0.004597701 -0.041189931 0.028639618 -0.027842227 -0.090692124 -0.049868766 0.044198895 0.034391534 -0.023017903 -0.007853403 -0.034300792 -0.013661202 0 -0.027700831 -0.024 -0.050351288 -0.017262639 0.017565872 0.003699137 -0.031941032 -0.002538071 0.036895674 0 0.013496933 0.021791768 0.05450237 -0.01011236 -0.010215664 -0.03440367 -0.014251781 -0.025301205 0.001236094 0.002469136 -0.00862069 -0.007453416 -0.007509387 -0.010088272 -0.014012739 -0.003875969 -0.029831388 0.002673797 -0.135857333 -0.01881788 0.034246931 -0.021192387 -0.021651229 -0.009680651 -0.050279294 0.015706806 0.059278351 0.0486618 0.016241299 -0.011415525 -0.009237875' 0.013986014 -0.034482759 -0.00952381 -0.033653846 0.032338308 -0.004819277 -0.014527845 -0.017199017 0.005 -0.017412935 -0.012658228 -0.015384615 -0.005208333 -0.018324607 -0.005333333 0.021447721 0.010498688 0.012987013 0.007692308 -0.015267176 -0.005167959 -0.01038961 2020/5/21 2020/5/22 2020/5/25 2020/5/26 2020/5/27 2020/5/28 2020/5/29 2020/6/1 2020/6/2 2020/6/3 2020/6/4 2020/6/5 2020/6/9 2020/6/10 2020/6/11 2020/6/12 2020/6/15 2020/6/16 2020/6/17 2020/6/18 2020/6/19 2020/6/22 2020/6/23 2020/6/24 2020/6/26 2020/6/29 2020/6/30 2020/7/2 2020/7/3 2020/7/6 7 58 9 1 -2 3 4 5 6 "8 /2020 2020/7/8 2020/7/9 0 21 10//2020 11 ' - 181 -0.027700831 182 0 183 184 -0.01038961 -0.015748031 -0.008 0.01344086 -0.013262599 -0.005376344 -0.010810811 185 186 187 -0.050279294 0.020587282 0.008646086 -0.032434755 0.03559322 -0.014729951 -0.021594684 -0.01188455 -0.010309278 0.005208333 -0.039723661 0.010791367 -0.03024911 -0.023853211 188 -0.005698006 0.042979943 0.016483516 -0.021621622 0.002762431 0.046831956 -0.034210526 0.019073569 -0.026737968 0.010989011 -0.024456522 -0.030640669 -0.034482759 189 190 2020/7/10 2020/7/13 2020/7/14 2020/7/15 2020/7/16 2020/7/17 2020/7/20 2020/7/21 2020/7/22 2020/7/23 2020/7/24 2020/7/27 2020/7/28 2020/7/29 2020/7/30 0.008196721 -0.010840108 0.010958904 -0.01897019 -0.002762431 -0.011080332 -0.011204482 -0.031161473 191 192 193 194 195 -0.009398496 196 197 198 Qantas Airways Limited average return Singapore Airlines Linited average return 199 0.000595331 -0.005236136 Cathay Pacific Airlines average return -0.005106553 D 18 19 24 14 15 Qanntas Airways Limited daily stock return Singapore Airlines Limited daily stock return Cathay Pacific Airlines daily stock return Date 16 17 -0.053254438' 0.01618705 0.009852217 -0.046875 -0.026548673 -0.001219512 01 0.045454545 0.00976801 20 0.042622951 0.048695652 0.027811366 21 0.022012579 -0.008291874 0.012941176 22 0.095384615 0.025083612 0.049941928 23 0.033707865 0.019575856 0.008849558 -0.035326087 -0.0128 -0.020833333 25 -0.022535211' -0.003241491 0.008958567 26 0.07204611 0.01300813 0.038845727 27 -0.034946237) 0.003205128 28 07 0 -0.035313002 -0.052183174 29 -0.064066852 0.0083194681 0.013483146 30 -0.005952381 -0.286130363 -0.00443459 31 0.01497006 -0.016287375 -0.008908686 32 0.044247788 0.008278523 -0.003370787 33 -0.011299435 -0.013135018 0.024802706 0.048571429 0.013309844 0.0110011 35 -0.029972752 0.003283755 -0.014145811 0.016853933 -0.026186378 0.008830022 -0.033149171 -0.00672204 -0.002188184 -0.011428571 0.056676273 -0.012061404 -0.01734104 0.002272727 0.012208657 40 0.038235294 0 0 0.002192982 0.002832861 -0.027210884 -0.004376368 42 0.002824859 -0.067599068 -0.015384615 43 -0.061971831 -0.0475 -0.006696429 0.006006006 0.023622047' -0.005617978 45 0.008955224 -0.046153846 -0.032768362 46 0.035502959 -0.005376344 0.005841121 47 0.008571429 -0.035135135 0.016260163 2020/4/1 2020/4/2 2020/4/3 2020/4/6 2020/4/7 2020/4/8 2020/4/9 2020/4/14 2020/4/15 2020/4/16 2020/4/17 2020/4/20 2020/4/21 2020/4/22 2020/4/23 2020/4/24 2020/4/27 2020/4/28 2020/4/29 2020/5/4 2020/5/5 2020/5/6 2020/5/7 2020/5/8 2020/5/11 2020/5/12 2020/5/13 2020/5/14 2020/5/15 2020/5/18 2020/5/19 2020/5/20 34 36 37 38 39 41 44 an ' nora 20air B 18 9 10 0.019607843 -0.002747253 -0.008264463 0.044444444 0.015957447 * 51 =2 13 14 55 5 6 17 58 59 =0 51 12 =3 4 5 =6 0.028328612 -0.008264463 0.072222222 0.054404145 0.004914005 0 0 -0.024449878 0.005012531 -0.007481297 0.052763819 0.071599045 0.031180401' 0.064794816 0.004056795 -0.061646465 -0.030237581 -0.035634744 0.043879908 ' 0 -0.037610619 0.004597701 -0.041189931 0.028639618 -0.027842227 -0.090692124 -0.049868766 0.044198895 0.034391534 -0.023017903 -0.007853403 -0.034300792 -0.013661202 0 -0.027700831 -0.024 -0.050351288 -0.017262639 0.017565872 0.003699137 -0.031941032 -0.002538071 0.036895674 0 0.013496933 0.021791768 0.05450237 -0.01011236 -0.010215664 -0.03440367 -0.014251781 -0.025301205 0.001236094 0.002469136 -0.00862069 -0.007453416 -0.007509387 -0.010088272 -0.014012739 -0.003875969 -0.029831388 0.002673797 -0.135857333 -0.01881788 0.034246931 -0.021192387 -0.021651229 -0.009680651 -0.050279294 0.015706806 0.059278351 0.0486618 0.016241299 -0.011415525 -0.009237875' 0.013986014 -0.034482759 -0.00952381 -0.033653846 0.032338308 -0.004819277 -0.014527845 -0.017199017 0.005 -0.017412935 -0.012658228 -0.015384615 -0.005208333 -0.018324607 -0.005333333 0.021447721 0.010498688 0.012987013 0.007692308 -0.015267176 -0.005167959 -0.01038961 2020/5/21 2020/5/22 2020/5/25 2020/5/26 2020/5/27 2020/5/28 2020/5/29 2020/6/1 2020/6/2 2020/6/3 2020/6/4 2020/6/5 2020/6/9 2020/6/10 2020/6/11 2020/6/12 2020/6/15 2020/6/16 2020/6/17 2020/6/18 2020/6/19 2020/6/22 2020/6/23 2020/6/24 2020/6/26 2020/6/29 2020/6/30 2020/7/2 2020/7/3 2020/7/6 7 58 9 1 -2 3 4 5 6 "8 /2020 2020/7/8 2020/7/9 0 21 10//2020 11 ' - 181 -0.027700831 182 0 183 184 -0.01038961 -0.015748031 -0.008 0.01344086 -0.013262599 -0.005376344 -0.010810811 185 186 187 -0.050279294 0.020587282 0.008646086 -0.032434755 0.03559322 -0.014729951 -0.021594684 -0.01188455 -0.010309278 0.005208333 -0.039723661 0.010791367 -0.03024911 -0.023853211 188 -0.005698006 0.042979943 0.016483516 -0.021621622 0.002762431 0.046831956 -0.034210526 0.019073569 -0.026737968 0.010989011 -0.024456522 -0.030640669 -0.034482759 189 190 2020/7/10 2020/7/13 2020/7/14 2020/7/15 2020/7/16 2020/7/17 2020/7/20 2020/7/21 2020/7/22 2020/7/23 2020/7/24 2020/7/27 2020/7/28 2020/7/29 2020/7/30 0.008196721 -0.010840108 0.010958904 -0.01897019 -0.002762431 -0.011080332 -0.011204482 -0.031161473 191 192 193 194 195 -0.009398496 196 197 198 Qantas Airways Limited average return Singapore Airlines Linited average return 199 0.000595331 -0.005236136 Cathay Pacific Airlines average return -0.005106553
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started