graded project

reading_assignment_core_1.html

Home >Business & Finance homework help >Management homework help >graded project

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"> <head> <meta http-equiv=Content-Type content="text/html; charset=unicode"> <meta name=ProgId content=Word.Document> <meta name=Generator content="Microsoft Word 15"> <meta name=Originator content="Microsoft Word 15"> <link rel=File-List href="reading_assignment_core_1_files/filelist.xml"> <link rel=Edit-Time-Data href="reading_assignment_core_1_files/editdata.mso">  <title>FIN310 Graded Project: Corporate Finance</title>  <link rel=themeData href="reading_assignment_core_1_files/themedata.thmx"> <link rel=colorSchemeMapping href="reading_assignment_core_1_files/colorschememapping.xml">  <link rel=Stylesheet type="text/css" media=all href="../../pagestyles.css"> <style>  </style>  <meta charset=utf-8> <script type="text/javascript" async src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML"> </script>  </head> <body lang=EN-US link=blue vlink=purple style='tab-interval:.5in;word-wrap: break-word'> <div class=WordSection1> <h1>FIN310 Graded Project: Corporate Finance<o:p></o:p></h1> Your project must be s..bmitted as an Excel document. Your project will be individually graded by your instructor and, therefore, will take up to a few weeks to grade.<o:p></o:p> Be sure that each of your files contains the following information:<o:p></o:p> <ul type=disc> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l12 level1 lfo1;tab-stops:list .5in'>Your name<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l12 level1 lfo1;tab-stops:list .5in'>Your student ID number<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l12 level1 lfo1;tab-stops:list .5in'>The project number (500289)<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l12 level1 lfo1;tab-stops:list .5in'>Your email address<o:p></o:p></li> </ul> To submit your graded project, follow these steps:<o:p></o:p> <ul type=disc> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l4 level1 lfo2;tab-stops:list .5in'>Log in to your student portal.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l4 level1 lfo2;tab-stops:list .5in'>Click on Take Exam next to the lesson you’re working on.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l4 level1 lfo2;tab-stops:list .5in'>Find the exam number for your project at the top of the Project Upload page.  <o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l4 level1 lfo2;tab-stops:list .5in'>Follow the instructions provided to complete your exam.<o:p></o:p></li> </ul> Be sure to keep a backup copy of any files you submit to the school!<o:p></o:p> <div class=MsoNormal align=center style='text-align:center'> <hr size=2 width="100%" align=center> </div> <h1>Part 1: El Cap Climbing Company<o:p></o:p></h1> El Cap Climbing Company (ECCC) is a small startup that manufactures and sells high-quality climbing gear in Fresno, California. The founder of the company, Leah, has been incredibly successful, but she hasn’t kept the company’s financial records as well as she might have.<o:p></o:p> The initial investment for El Cap was provided by her friends and family and was small. However, current operations can’t meet the demand for the product, and Leah has plans to increase both production and the number of storefronts.<o:p></o:p> These plans require a large investment from both equity and debt financing. The new investors and creditors require detailed financial statements. Leah has hired you, a financial analyst, to prepare these statements and give insight into the financial position of the firm. Leah has provided information from her bank statements, bills, and receipts in an Excel spreadsheet, which is found in your downloaded project files. She explained to you that taxes are paid at a rate of 30 percent, and dividends are paid at a rate of 40 percent. (Note: You can create the statements in the same Excel spreadsheet that has the financial information. Be sure to let the instructor know if you choose to do this instead of creating them in a Word document.)<o:p></o:p> Prepare the following:<o:p></o:p> <ul type=square> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>An income statement for 2021 and 2022<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>A balance sheet for 2021 and 2022<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>Operating cash flows for the two years<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>Cash flows from assets in 2022<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>Cash flows to creditors for 2022<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l3 level1 lfo3;tab-stops:list .5in'>Cash flows to stockholders for 2022<o:p></o:p></li> </ul> B. Answer the following:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l9 level1 lfo4;tab-stops:list .5in'>How would you describe the financial position of the firm in 2022? Write a brief overview.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l9 level1 lfo4;tab-stops:list .5in'>What do you think about Leah’s plans to expand?<o:p></o:p></li> </ol> <h1>Part 2: Mortgage Decision<o:p></o:p></h1> To expand, El Cap Climbing Company (ECCC) is considering taking out a mortgage for a new store location, a nonresidential real property that includes land and a building. Leah is unsure if she has the cash flow to take on any more debt. She asked you to create a loan amortization schedule for the proposed mortgage loan. Then, you’ll create a chart that represents the portion of each payment that goes toward principal and interest.<o:p></o:p> A. Prepare the following:<o:p></o:p> <ul type=square> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l2 level1 lfo5;tab-stops:list .5in'>A loan amortization schedule<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l2 level1 lfo5;tab-stops:list .5in'>A chart showing the percentage of the payment applied to the principal and interest<o:p></o:p></li> </ul> <h2>Loan Amortization Schedule<o:p></o:p></h2> First, you’ll need to create a loan amortization schedule in the downloaded Excel spreadsheet. Create the table on the tab named "Part 2 Loan Amortization Sched.” The following table illustrates the payments and interest amounts for a fixed-rate, 30-year, $500,000 mortgage, at a five-percent interest rate. The monthly payment will be $2,684.11.<o:p></o:p> <table class=MsoNormalTable border=1 cellspacing=0 cellpadding=0 width=650 style='width:487.5pt;mso-cellspacing:0in;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .75pt;mso-yfti-tbllook:1184;mso-padding-alt: 0in 0in 0in 0in'> <tr style='mso-yfti-irow:0;mso-yfti-firstrow:yes'> <td style='padding:0in 0in 0in 0in'> Payment Number<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Payment Amount<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 5% Interest Expense<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Principal<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Balance<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Annual Interest Expense<o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:1'> <td style='padding:0in 0in 0in 0in'> 0<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 500,000.00<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> -<o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:2'> <td style='padding:0in 0in 0in 0in'> 1<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,684.11<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,083.33<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 600.78<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 499,399.22<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:3'> <td style='padding:0in 0in 0in 0in'> 2<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,684.11<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,080.83<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 603.28<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 498,795.94<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:4'> <td colspan=6 style='padding:0in 0in 0in 0in'> …break in the sequence…<o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:5'> <td style='padding:0in 0in 0in 0in'> Totals<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 466,278.03<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 500,000.00<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:6'> <td style='padding:0in 0in 0in 0in'> 359<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,684.11<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 22.22<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,661.89<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,671.41<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'>  <o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:7;mso-yfti-lastrow:yes'> <td style='padding:0in 0in 0in 0in'> 360<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,682.54<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 11.13<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 2,671.41<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> -<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> 855.56<o:p></o:p> </td> </tr> </table> The table serves as an example of what you’ll create in Excel. Note that the table shows only the figures for the first and the last year of payments; you’ll need to calculate the amounts for the remaining payments and fill them in.<o:p></o:p> Once you’ve determined how each amount in the table is obtained, you can use relative and absolute cell references to fill in the full 360 payments.<o:p></o:p> The following is an explanation of the columns in the table:<o:p></o:p> <ul type=square> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Payment number—The first column in the table shows the 360 payments required to pay off the mortgage loan (30 years, with 12 monthly payments per year).<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Payment amount—The second column shows the monthly payment amount.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Interest—The third column shows the portion of the monthly payment that goes to interest.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Principal—The fourth column shows the portion paid toward the principal.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Balance—The fifth column shows the starting balance of $500,000, and the remaining balance each month after the principal is subtracted.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Annual interest expense—The last column provides a running total of the interest expense on the mortgage for the entire 12-month period. It’s the amount that would be reported on the financial statements.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l11 level1 lfo6;tab-stops:list .5in'>Totals—The “Totals” under the “5% Interest Expense” and “Principal” columns show the final totals for the 30-year life of the mortgage.<o:p></o:p></li> </ul> <h2>Mortgage Principal and Interest Chart<o:p></o:p></h2> Next, you’ll create a chart following these steps. Create the table on the tab named "Part 2 Chart.”<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l7 level1 lfo7;tab-stops:list .5in'>Start by selecting the Interest Expense and Principal columns. Make sure to select the column headers and values. Don’t select the Totals row.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l7 level1 lfo7;tab-stops:list .5in'>Click on the Insert tab and select a “Stacked Column.” Make sure to label the x-axis (payment month) and y-axis (dollars), and include a legend for the two values (interest and principal).<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l7 level1 lfo7;tab-stops:list .5in'>Your final chart should be set up similar to the chart below, with the data populating the chart. (The increments don’t need to be the same).<o:p></o:p></li> </ol>  <o:p></o:p> <img width=667 height=335 id="_x0000_i1031" src="40626/500306_01_hl.jpg" alt="An image of a chart depicting mortgage principal and interest."><o:p></o:p> B. Answer the following:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l8 level1 lfo8;tab-stops:list .5in'>How can you describe the relationship between time and the amount paid towards principal and interest?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l8 level1 lfo8;tab-stops:list .5in'>Knowing what you know about ECCC’s cash flow from Part 1, is it reasonable to believe that ECCC can take on this new debt?<o:p></o:p></li> </ol> <h1>Part 3: Can We Upgrade?<o:p></o:p></h1> It’s now 2023, and El Cap Climbing Company (ECCC) has continued to grow. One of ECCC’s major revenue-producing products is a spring-loaded camming device called SLCD, or cams. It’s a device with a small handle (called the “trigger”) and two spring-loaded “cams” on an axle. When the trigger is pulled, the cams move together, decreasing the size of the cams. It’s then inserted into a crack or pocket in the rock. When the trigger is released, the cams expand. These cams are used as anchors when “trad” rock climbing.<o:p></o:p> ECCC currently has one set of cams on the market, and sales have been excellent. The cams are lighter and perform better than their competitors'. However, as with any high-performance item, technology changes rapidly, and the cams are now falling behind the competition.<o:p></o:p> ECCC spent $200,000 to develop a prototype for a new line of cams that has all the features of the existing cams but are made from an even lighter and stronger 7075-T6 aluminum alloy. The company has spent a further $150,000 for a marketing study to determine the expected sales figures for the cam line.<o:p></o:p> ECCC can manufacture a set of the new cams for an average of $140 each in variable costs. Fixed costs for the operation are estimated to run an additional $2.1 million per year if the new project is undertaken. The estimated sales volume is 75,000, 85,000, 80,000, 70,000, and 60,000 per year for the next five years, respectively. The unit price of the new cam set will be $240. The necessary equipment can be purchased for $10.5 million and will be depreciated on a seven-year MACRS schedule. It’s believed the value of the equipment in five years will be $1.1 million.<o:p></o:p> Production of the current cam line is expected to be terminated in two years. If ECCC doesn’t introduce the new line of cams, sales will be 45,000 units and 25,000 units for the next two years, respectively. The price of the cam set is $150, with variable costs of $95 each, and fixed costs of $1.5 million per year. If ECCC does introduce the new cams, sales of the existing product will fall by 10,000 units per year, and the price of the existing sets should be lowered to $120 each. Net working capital for the cams will be 22 percent of sales and will occur with the timing of the cash flows for the year; for example, there’s no initial outlay for NWC, but changes in NWC will occur in Year 1 with the first year’s sales. ECCC has a 30-percent corporate tax rate and a required return of 10 percent. (Note: You can create the solutions in the same Excel spreadsheet that has the data report information. Be sure to let the instructor know if you choose to do this instead of creating them in a Word document.)<o:p></o:p> Leah has provided you with a data report in an Excel spreadsheet that contains information to answer the following questions:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>What’s the payback period of the project?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>What’s the profitability index of the project?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>What’s the IRR of the project?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>What’s the NPV of the project?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>Should Leah accept the project?<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l1 level1 lfo9;tab-stops:list .5in'>If Leah needs to adjust the price of the product, what’s the lowest Leah could make the price of the new cam set and still have a positive NPV project (keeping all other assumptions the same)?<o:p></o:p></li> </ol> <h1>Part 4: Risky Business<o:p></o:p></h1> Lastly, just for fun, El Cap Climbing Company (ECCC) is looking at determining its sensitivity to market fluctuations.<o:p></o:p> Since ECCC isn’t publically traded and can’t look at its own stock history, it must evaluate its competitors. Black Diamond Equipment is its closest competitor, but the company doesn’t have enough trading volume to make any sound conclusions. Leah identifies Callaway Golf Company (ELY) as ECCC’s closest publicly-traded competitor. Even though ELY sells golf equipment, it too is a specialized company selling high-tech sports equipment.<o:p></o:p> <h2>Finding Beta with CAPM<o:p></o:p></h2> Note: This information is also in your textbook.<o:p></o:p> CAPM is one of the most thoroughly researched models in financial economics. When beta is estimated in practice, a variation of CAPM, called the market model, is often used. To derive the market model, start with the CAPM:<o:p></o:p> E(Ri) = Rf + β[E(RM) − Rf]<o:p></o:p> Since CAPM is an equation, you can subtract the risk-free rate from both sides, which gives you:<o:p></o:p> E(Ri) − Rf = β[E(RM) − Rf]<o:p></o:p> This equation is deterministic—that is, exact. In a regression, you'll realize that there’s some indeterminate error. You need to formally recognize this in the equation by adding epsilon, which represents this error:<o:p></o:p> E(Ri) − Rf = β [E(RM) − Rf] + ε<o:p></o:p> Finally, think of the above equation in a regression. Since there’s no intercept in the equation, the intercept is zero. However, when you estimate the regression equation, you can add an intercept term, which is called alpha here:<o:p></o:p> E(Ri) − Rf = αi + β[E(RM) − Rf] + ε<o:p></o:p> The intercept term is known as Jensen’s alpha, and it represents the “excess” return. If CAPM holds exactly, this intercept should be zero. Think of alpha in terms of the SML: If the alpha is positive, the stock plots above the SML; if the alpha is negative, the stock plots below the SML. You’ll first create a scatter plot and then perform a regression analysis for ELY stock and the mutual fund. Then, use those results to compare and analyze the results.<o:p></o:p> A. Scatter Plotting and Regression Analysis<o:p></o:p> Use the following steps to create the scatter plot:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l5 level1 lfo10;tab-stops:list .5in'>Go to the Part 4 Stock Data tab in your Excel spreadsheet. Highlight column K–M headings, then hold down the Ctrl button and select cells K-3 through M-62.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l5 level1 lfo10;tab-stops:list .5in'>Go to the Insert tab, click on Scatter Chart, and select the first style. <figure><img width=507 height=818 id="_x0000_i1030" src="40627/500306_02__vl.jpg" alt="An image of a dialogue box with five types of scatter charts and two types of bubble charts"><o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l5 level1 lfo10;tab-stops:list .5in'>Move the chart to the side of the data, and increase the size of the chart. Click on the Chart Title and change it to Risk Premium Analysis.<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l5 level1 lfo10;tab-stops:list .5in'>In the Design tab, click on Quick Layout. Select the layout that gives you the y formulas and the R2. <figure><img width=507 height=836 id="_x0000_i1029" src="40628/500306_03_vl.jpg" alt="An image of a dialogue box of quick layout with 11 layouts."><o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l5 level1 lfo10;tab-stops:list .5in'>Move the y formulas and the R2 to the bottom right-hand corner of the chart.<o:p></o:p></li> </ol> Now, use the following steps to create a regression analysis for ELY and for the Mutual Fund:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo11;tab-stops:list .5in'>First, check to see that you have the ability to run the analysis. Go to the Data tab in Excel, and look for the Data Analysis feature shown in the following image. <figure><img width=667 height=82 id="_x0000_i1028" src="40629/500306_04_hl.jpg" alt="An image of the Excel sheet menu bar with the data analysis selection under data tab."><o:p></o:p></li> </ol> <div> <div> If you don’t see Data Analysis, you might need to add Analysis Toolpak in Excel. For instructions on how to load the Analysis Toolpak into your version of Excel, visit <a href="https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4" target="_blank" title="https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4">https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4</a>. Contact Microsoft Support if you have any issues with this add-in. Once you've completed all these steps, you can continue with your project.<o:p></o:p> </div> </div> <ol start=2 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l6 level1 lfo12;tab-stops:list .5in'>Click on Data Analysis. A dialog box with a list of analysis tools will open. Select Regression from the list and click OK. <figure><img border=0 width=667 height=292 id="_x0000_i1027" src="40630/500306_05_hl.jpg" alt="An image of data analysis dialogue box with regression option selected in it."><o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l6 level1 lfo12;tab-stops:list .5in'>Next, another dialog box opens for you to select your Inputs and Output for the regression. Select the input data ranges by highlighting S&P Risk Premium numbers (x-axes range) and the number for the asset you’re comparing as the y-axes range. Output to a new worksheet (do not type a name in the text box). Select the checkboxes for Labels, Confidence Level, and Residuals. Click OK. <figure><img border=0 width=667 height=516 id="_x0000_i1026" src="40631/500306_06_hl.jpg" alt="An image of Regression dialogue box."><o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l6 level1 lfo12;tab-stops:list .5in'>Your regression analysis will open in a new worksheet. Rename the worksheet based on the premium being compared to the S&P premium, for example: “ELY Regression Analysis.”<o:p></o:p></li> </ol> B. Answer the following questions:<o:p></o:p> <ol start=1 type=1> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l10 level1 lfo13;tab-stops:list .5in'>In this regression, Rt is the return on the stock, and Rft is the risk-free rate for the same period. RMt is the return on a stock market index, such as the S&P 500 index. αi is the regression intercept, and βi is the slope (and the stock’s estimated beta). εt represents the residuals for the regression. The intercept, αi, is often called Jensen’s alpha. What does it measure? If an asset has a positive Jensen’s alpha, where would it plot with respect to the SML? Rt − Rft = αi + βi [RMt - Rft] + εt<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l10 level1 lfo13;tab-stops:list .5in'>Is the alpha of either ELY or the mutual fund significantly more or less than zero? (Hint: The alpha is the intercept.)<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l10 level1 lfo13;tab-stops:list .5in'>How do you interpret the beta for the stock and the mutual fund? (Hint: The beta is next to the coefficient.)<o:p></o:p></li> <li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l10 level1 lfo13;tab-stops:list .5in'>Which of the two regression estimates has the highest R-squared? Is this what you would have expected? Use the scatterplot to explain why.<o:p></o:p></li> </ol> <h1>Grading Criteria<o:p></o:p></h1> Your project is worth a total of 100 points. Your instructor will use the following breakdown of points for each portion of the project to calculate your final grade:<o:p></o:p> <table class=MsoNormalTable border=1 cellspacing=0 cellpadding=0 style='mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt:0in 0in 0in 0in'> <tr style='mso-yfti-irow:0;mso-yfti-firstrow:yes'> <td style='padding:0in 0in 0in 0in'> Part 1—25 Points Total<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Part 2—25 Points Total<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Part 3—25 Points Total<o:p></o:p> </td> <td style='padding:0in 0in 0in 0in'> Part 4—25 Points Total<o:p></o:p> </td> </tr> <tr style='mso-yfti-irow:1;mso-yfti-lastrow:yes'> <td valign=top style='padding:0in 0in 0in 0in'> Income Statement: 6 Points Balance Sheet: 5 points Operating Cash Flows: 4 points Asset Cash Flows: 6 points Creditor Cash Flows: 2 points Stockholder Cash Flows: 2 points<o:p></o:p> </td> <td valign=top style='padding:0in 0in 0in 0in'> Interest: 4 points Principal: 2 points Balance: 3 points Annual interest: 4 points Sub Totals: 2 points Graph: 5 points Question 1: 2 points Question 2: 3 points<o:p></o:p> </td> <td valign=top style='padding:0in 0in 0in 0in'> Question 1: 3 points Question 2: 5 points Question 3: 5 points Question 4: 5 points Question 5: 2 points Question 6: 5 points<o:p></o:p> </td> <td valign=top style='padding:0in 0in 0in 0in'> Graph: 5 points Display Amounts: 6 points Question 1: 4 points Question 2: 2 points Question 3: 2 points Question 4: 6 points<o:p></o:p> </td> </tr> </table> <o:p> </o:p> </div> </body> </html>