Kotle plans to fer apertfolio consisting of two securities Intl (INT) andre Gamble (1),she seeders how the portfolio's will depend on the sent that she levels in stock. Katie'e professe suggest that she was the capital set pricing model to define the required return for these companie nr./b, Katieres using the current longtem Treasury bendrum of 35 Kalle determine that the weapertum we the SAPS Index over the last several year is 11% este figure to Complete the steps belowing all refer to dele er prior alculation in imple call referme in all you need. Terases formes a celu, ar atrauterell reference or call reference may be preferred to specifical function is to beure, the directions will herefterful wwwrial dan inte eller functie de a reference to the celle de the data is fewed. Mleur colour sly in the lurcellNighlighted be walenteri directed, use the earliest appearanofthe data is your former, wally the Guated 1 1 To Do Create a sprealheel to model and malyse the distribution of securities in the portfolio: . Estimate the best values for INTCG bate on the data from the table baluss the graphic method and Ice SLOPE finction 1. Deing the CAIM, determine the required as one for beth INTC and Kate has decided that the portfolie will be distributed between INTC and PG in a 60% and 40 plit, respectively. The weighted average can be called for both them and beta e'the portfolio Determine the weighted we required to retum and the weighted average lets for the perfolie 10 Solution 1. Tatimate the bets value for INC and based on the data from the table for below uning the prophed and Bad Score action 14 De NTC The Ding the CAPM mine the giornatum for both INTC 3 11% Expected Hem But INTC Team AC11 Ax & fc * - r KM dad the portfelle will be distributed be NTC dins 0% 40% split, respectively. Herelghted avenger be called for both the red bem of the portfolio Define the weighted wergereidefret edhe sighted avengebets for the portfolio sing the following models for the rains ) + where weighted wegeindute of tor for the portfolio wwight of security in the portfolio 7, requieretum of security is the portfolio wwight of security in the portfolio -required tom of teority in the portfolio wab wb /w*) where web-weighted wegbeta for the portfolio w-weight of security in the portfolio ,-beta for security - weight of security in the portfolio ,-bets forsety) 46 4) 60% * Weight of INTC in the portfolio Weight rain the portfolio Weighted average required iste of reum Weighted average beta 40 Stock Retum, Intel INTO 10 Stock Rom Procter able 48% -13% 23% 0.4% 0.3% -0.1% Market Raum 7.5% -0% -1.9% -3.9% -0.8% 2017-07-01 2017.01.01 2017-04-01 2017-05-01 2017-06-01 2012-07-01 2017-08-01 2017.09.01 2017-10-01 2017-11-01 2017-12-01 2011-01-01 2018-02-01 2018.03.01 2011-04-01 2011.05.01 5.1% 1.1% WAS 1969 -14% 36% 43% 2.4% -14% 46.19 50% 2.1% -4.4% 5.25 -1.4% -10.1% 66% 435 . 144 6:49 1.6% 6.9 SAN 32 0.75 3694 14 039 2011.07.01 2018.01 2018.01 2014.10.01 2016-11-01 2015.12.01 2011-01-01 2019-03-01 0.99 14 14 044 9.15 499 TEL -141 34 5.35 + Ready Requirements 1 In cells C15:129, insert a Scutter Chart to estimate bets for Intel (INTC) and Procter&Gamble (PG). Insetting Chart On the insert tab, in the Charts group, did the arrow next to Insert Scatter OXY) or Bubble Chart and choose Scatter Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells F50:974 for the values and cells 150:1974 for the values. Do not name the series. Add new series for the stocks from your portfolio asing cells 50:274 for the Xvalues and cells ESO:E74 for the values. Do not name the series. Trendline Options Select any point of the security market line and right click on it. Choose Add Trendline. In the Trendline Options window, select Linear with automatic trendline name Choose Series Select any point of the trendline and right click on it. Choose Format Trendline. Tick on Display Equation on chart. Select any point of the security market line and tight click on it. Choose Add Trendline. In the Trendline Options window, select Linear with automatic tremdline name, Choose Series 2 Select any point of the trendline and tight elick on it. Choose Format Trendline. Tick on Display Equation on chart. 7A 79 RO RI Edit Chartements Add axis titles. Replace Axis Title for the horizontal axis with Market Retura, % and Axis Title for the vertical axis with Stock Return, % 2 In cell D31, by using cell references and the function SLOPE, calculate the beta value for INTC. Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline. In cell D32, by using cell references and the function SLOPE, calculate the beta value for PG Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline. 4 In cell D3, by using cell references, calculate the required rate of retum for INTC. 5 In cell 39, by using cell references, calculate the required rate of return for PG 6 In cell 045, by using cell references, calculate the weighted average required rate of retum 7 In cell M6, by using cell references, calculate the weighted average beta 9 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed CEE 33 90 Times New Roman General h - A A . 29 Wrap Test Marpe Center PM B TV S.% -11 x fx Spreadsheet Exercise Problem 535 Katieplarni sumporfollecting of the metri, letel (0) Oblea de medens how the pollen will open the che le patate police dil to defend the life ly e com loptom Tretry bord tumer wieder we were to the ser sende over the last several yerlerde het Complete the map below any rere te pire date willen we call reforms all won. To copypasta fermareswards www cell reformer and referme my preferred to create and time will cafe Desar der in well as feed, learned this the datele und Molar computery is the collettidele. In allem, well Te De Chemiwly dia in the perfelia Amb el INTO wd On the other tale for best to store the CAPM me or for NTC Katiputi will be distributed and inspectively. wedge can be and for both the Solution the beta dhe att DOC LOCACC Rio 11 INTE 8.30 Times New Roman 14 ' ' 2 Wrap Text General Pasta Marge Cente $ % AC11 x x Kill Thapfolie will be distribe NA40plit,pectively, and there we teleftherio Dawid well on the weighted deprehli the followed wer war-weighted rear for the patie w.wight efterly in the perfet ired reneurity in the partie -iphatianity in thirparals - fery is the per web- web-weighted were for the perfelia wifi in the pollo htforway wwight efety is the perfolie des 405 WINTC in epiftolie Welefon de portfolio guide from Waitin maresan Stock Rom Intel INTO Sed eum, urine Market Hom 02% 035 -1.95 DAS 19 2017.02.41 2017-03-01 2017-04-01 2013-05-01 2011.04.01 2017-01-01 2017.01.01 2012 2017-10-01 2012:03 2017.12.01 2013 01.01 2018-02-01 15 2014 1.49 OS 13 14 JAS 434 245 EAS 2.49 10 4.4 4451 4. 6.79 2011-04-01 2018 OSO 2016 2018.01.01 2011 2011 01 2011-12 55 13 16 76 2018 2018.01.01 0.4% 45 5.35 27 Mequirements 1 In cells C15:29, insert a Seatter Chart to imate betes for Intel (INTC) and Procter & Gamble (P). lantis Chat On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X.) or Bubble Chart med choose Scatter Chat Selecting Data Serie Then in Select Data Source window delete my series created witomatically Add new series for the security market line bring celle 56:274 for the values and cells 080-1714 for the values. Do not nanse the serie Add new series for the stocks from your portfolio using cells 7501774 for the X values and cells :E74 for the values. Do not use the series. Tredline Options Select any point of the security market line und eight dick on it. Choose Add Trendline, in the Trendline Options window, select Linear with automatic trendile name. Choose Series Select any point of the trendline and right click on it. Choose Foma Trendline. Tiek on Display antion on chart. Select any point of the security market line and tight click on it. Choose Add Trendline. In the Trendline Options window, select Linear with stomatic trendline Cheese Series2 Select any point of the trendline and right click on it. Cheese Format Trendline Tick on Duplay Huation on chart Chattaments Add axis title. Replace Axis Title for the horizontal axis with Market Return, and Axle Title for the vertical unit with Stock Return, %. 2 In cell D31, by using cell references and the function SLOPE, calculate the beta value for INTC Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline 3 In cell, by using cell references and the function SLOPE, calculate the beta value fora Note: Compare called bets value with that obtained in the equation of the coresponding trendline 4 ls vells, by using cell reference calculate the required rate ofretum for INTC 5 In cell 09, by using cell references, calculate the required rate of return for Pa 6 In cell Dus, by using cell reference calculate the weighted average required rule of retum 7 In cell 46, by using cell references, calculate the weighted werages . Save the work book. Close the workbook and then exit cel Submit the work back as directed 79 1 NE Kotle plans to fer apertfolio consisting of two securities Intl (INT) andre Gamble (1),she seeders how the portfolio's will depend on the sent that she levels in stock. Katie'e professe suggest that she was the capital set pricing model to define the required return for these companie nr./b, Katieres using the current longtem Treasury bendrum of 35 Kalle determine that the weapertum we the SAPS Index over the last several year is 11% este figure to Complete the steps belowing all refer to dele er prior alculation in imple call referme in all you need. Terases formes a celu, ar atrauterell reference or call reference may be preferred to specifical function is to beure, the directions will herefterful wwwrial dan inte eller functie de a reference to the celle de the data is fewed. Mleur colour sly in the lurcellNighlighted be walenteri directed, use the earliest appearanofthe data is your former, wally the Guated 1 1 To Do Create a sprealheel to model and malyse the distribution of securities in the portfolio: . Estimate the best values for INTCG bate on the data from the table baluss the graphic method and Ice SLOPE finction 1. Deing the CAIM, determine the required as one for beth INTC and Kate has decided that the portfolie will be distributed between INTC and PG in a 60% and 40 plit, respectively. The weighted average can be called for both them and beta e'the portfolio Determine the weighted we required to retum and the weighted average lets for the perfolie 10 Solution 1. Tatimate the bets value for INC and based on the data from the table for below uning the prophed and Bad Score action 14 De NTC The Ding the CAPM mine the giornatum for both INTC 3 11% Expected Hem But INTC Team AC11 Ax & fc * - r KM dad the portfelle will be distributed be NTC dins 0% 40% split, respectively. Herelghted avenger be called for both the red bem of the portfolio Define the weighted wergereidefret edhe sighted avengebets for the portfolio sing the following models for the rains ) + where weighted wegeindute of tor for the portfolio wwight of security in the portfolio 7, requieretum of security is the portfolio wwight of security in the portfolio -required tom of teority in the portfolio wab wb /w*) where web-weighted wegbeta for the portfolio w-weight of security in the portfolio ,-beta for security - weight of security in the portfolio ,-bets forsety) 46 4) 60% * Weight of INTC in the portfolio Weight rain the portfolio Weighted average required iste of reum Weighted average beta 40 Stock Retum, Intel INTO 10 Stock Rom Procter able 48% -13% 23% 0.4% 0.3% -0.1% Market Raum 7.5% -0% -1.9% -3.9% -0.8% 2017-07-01 2017.01.01 2017-04-01 2017-05-01 2017-06-01 2012-07-01 2017-08-01 2017.09.01 2017-10-01 2017-11-01 2017-12-01 2011-01-01 2018-02-01 2018.03.01 2011-04-01 2011.05.01 5.1% 1.1% WAS 1969 -14% 36% 43% 2.4% -14% 46.19 50% 2.1% -4.4% 5.25 -1.4% -10.1% 66% 435 . 144 6:49 1.6% 6.9 SAN 32 0.75 3694 14 039 2011.07.01 2018.01 2018.01 2014.10.01 2016-11-01 2015.12.01 2011-01-01 2019-03-01 0.99 14 14 044 9.15 499 TEL -141 34 5.35 + Ready Requirements 1 In cells C15:129, insert a Scutter Chart to estimate bets for Intel (INTC) and Procter&Gamble (PG). Insetting Chart On the insert tab, in the Charts group, did the arrow next to Insert Scatter OXY) or Bubble Chart and choose Scatter Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells F50:974 for the values and cells 150:1974 for the values. Do not name the series. Add new series for the stocks from your portfolio asing cells 50:274 for the Xvalues and cells ESO:E74 for the values. Do not name the series. Trendline Options Select any point of the security market line and right click on it. Choose Add Trendline. In the Trendline Options window, select Linear with automatic trendline name Choose Series Select any point of the trendline and right click on it. Choose Format Trendline. Tick on Display Equation on chart. Select any point of the security market line and tight click on it. Choose Add Trendline. In the Trendline Options window, select Linear with automatic tremdline name, Choose Series 2 Select any point of the trendline and tight elick on it. Choose Format Trendline. Tick on Display Equation on chart. 7A 79 RO RI Edit Chartements Add axis titles. Replace Axis Title for the horizontal axis with Market Retura, % and Axis Title for the vertical axis with Stock Return, % 2 In cell D31, by using cell references and the function SLOPE, calculate the beta value for INTC. Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline. In cell D32, by using cell references and the function SLOPE, calculate the beta value for PG Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline. 4 In cell D3, by using cell references, calculate the required rate of retum for INTC. 5 In cell 39, by using cell references, calculate the required rate of return for PG 6 In cell 045, by using cell references, calculate the weighted average required rate of retum 7 In cell M6, by using cell references, calculate the weighted average beta 9 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed CEE 33 90 Times New Roman General h - A A . 29 Wrap Test Marpe Center PM B TV S.% -11 x fx Spreadsheet Exercise Problem 535 Katieplarni sumporfollecting of the metri, letel (0) Oblea de medens how the pollen will open the che le patate police dil to defend the life ly e com loptom Tretry bord tumer wieder we were to the ser sende over the last several yerlerde het Complete the map below any rere te pire date willen we call reforms all won. To copypasta fermareswards www cell reformer and referme my preferred to create and time will cafe Desar der in well as feed, learned this the datele und Molar computery is the collettidele. In allem, well Te De Chemiwly dia in the perfelia Amb el INTO wd On the other tale for best to store the CAPM me or for NTC Katiputi will be distributed and inspectively. wedge can be and for both the Solution the beta dhe att DOC LOCACC Rio 11 INTE 8.30 Times New Roman 14 ' ' 2 Wrap Text General Pasta Marge Cente $ % AC11 x x Kill Thapfolie will be distribe NA40plit,pectively, and there we teleftherio Dawid well on the weighted deprehli the followed wer war-weighted rear for the patie w.wight efterly in the perfet ired reneurity in the partie -iphatianity in thirparals - fery is the per web- web-weighted were for the perfelia wifi in the pollo htforway wwight efety is the perfolie des 405 WINTC in epiftolie Welefon de portfolio guide from Waitin maresan Stock Rom Intel INTO Sed eum, urine Market Hom 02% 035 -1.95 DAS 19 2017.02.41 2017-03-01 2017-04-01 2013-05-01 2011.04.01 2017-01-01 2017.01.01 2012 2017-10-01 2012:03 2017.12.01 2013 01.01 2018-02-01 15 2014 1.49 OS 13 14 JAS 434 245 EAS 2.49 10 4.4 4451 4. 6.79 2011-04-01 2018 OSO 2016 2018.01.01 2011 2011 01 2011-12 55 13 16 76 2018 2018.01.01 0.4% 45 5.35 27 Mequirements 1 In cells C15:29, insert a Seatter Chart to imate betes for Intel (INTC) and Procter & Gamble (P). lantis Chat On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X.) or Bubble Chart med choose Scatter Chat Selecting Data Serie Then in Select Data Source window delete my series created witomatically Add new series for the security market line bring celle 56:274 for the values and cells 080-1714 for the values. Do not nanse the serie Add new series for the stocks from your portfolio using cells 7501774 for the X values and cells :E74 for the values. Do not use the series. Tredline Options Select any point of the security market line und eight dick on it. Choose Add Trendline, in the Trendline Options window, select Linear with automatic trendile name. Choose Series Select any point of the trendline and right click on it. Choose Foma Trendline. Tiek on Display antion on chart. Select any point of the security market line and tight click on it. Choose Add Trendline. In the Trendline Options window, select Linear with stomatic trendline Cheese Series2 Select any point of the trendline and right click on it. Cheese Format Trendline Tick on Duplay Huation on chart Chattaments Add axis title. Replace Axis Title for the horizontal axis with Market Return, and Axle Title for the vertical unit with Stock Return, %. 2 In cell D31, by using cell references and the function SLOPE, calculate the beta value for INTC Note: Compare calculated beta value with that obtained in the equation of the corresponding trendline 3 In cell, by using cell references and the function SLOPE, calculate the beta value fora Note: Compare called bets value with that obtained in the equation of the coresponding trendline 4 ls vells, by using cell reference calculate the required rate ofretum for INTC 5 In cell 09, by using cell references, calculate the required rate of return for Pa 6 In cell Dus, by using cell reference calculate the weighted average required rule of retum 7 In cell 46, by using cell references, calculate the weighted werages . Save the work book. Close the workbook and then exit cel Submit the work back as directed 79 1 NE
