I have received feedback from my instructor and I need a little help.  I  have uploaded my work along with my feedback.  

Part 1: El Cap Climbing Company

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 hasnt  kept the companys financial records as well as she might have.

The initial investment for El Cap was provided by her friends and  family and was small. However, current operations cant meet the demand  for the product, and Leah has plans to increase both production and the  number of storefronts.

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.)

Prepare the following:

• An income statement for 2018 and 2019
• A balance sheet for 2018 and 2019
• Operating cash flows for the two years
• Cash flows from assets in 2019
• Cash flows to creditors for 2019
• Cash flows to stockholders for 2019

B. Answer the following:

1. How would you describe the financial position of the firm in 2019? Write a brief overview.
2. What do you think about Leahs plans to expand?

Part 2: Mortgage Decision

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, youll create a chart  that represents the portion of each payment that goes toward principal  and interest.

A. Prepare the following:

• A loan amortization schedule
• A chart showing the percentage of the payment applied to the principal and interest

Loan Amortization Schedule

First, youll 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.

  Payment Number Payment Amount 5% Interest Expense Principal Balance Annual Interest Expense   0       500,000.00 –   1 2,684.11 2,083.33 600.78 499,399.22     2 2,684.11 2,080.83 603.28 498,795.94     break in the sequence   Totals   466,278.03 500,000.00       359 2,684.11 22.22 2,661.89 2,671.41     360 2,682.54 11.13 2,671.41 – 855.56    

The table serves as an example of what youll create in Excel. Note  that the table shows only the figures for the first and the last year of  payments; youll need to calculate the amounts for the remaining  payments and fill them in.

Once youve determined how each amount in the table is obtained, you  can use relative and absolute cell references to fill in the full 360  payments.

The following is an explanation of the columns in the table:

• Payment numberThe first column in the table shows the 360  payments required to pay off the mortgage loan (30 years, with 12  monthly payments per year).
• Payment amountThe second column shows the monthly payment amount.
• InterestThe third column shows the portion of the monthly payment that goes to interest.
• PrincipalThe fourth column shows the portion paid toward the principal.
• BalanceThe fifth column shows the starting balance of  $500,000, and the remaining balance each month after the principal is  subtracted.
• Annual interest expenseThe last column provides a running  total of the interest expense on the mortgage for the entire 12-month  period. Its the amount that would be reported on the financial  statements.
• TotalsThe Totals under the 5% Interest Expense and  Principal columns show the final totals for the 30-year life of the  mortgage.

Mortgage Principal and Interest Chart

Next, youll create a chart following these steps. Create the table on the tab named “Part 2 Chart.

1. Start by selecting the Interest Expense and Principal columns. Make sure to select the column headers and values. Dont select the Totals row.
2. 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).
3. Your final chart should be set up similar to the chart below, with  the data populating the chart. (The increments dont need to be the  same).

B. Answer the following:

1. How can you describe the relationship between time and the amount paid towards principal and interest?
2. Knowing what you know about ECCCs cash flow from Part 1, is it reasonable to believe that ECCC can take on this new debt?

Part 3: Can We Upgrade?

Its now 2020, and El Cap Climbing Company (ECCC) has continued to  grow. One of ECCCs major revenue-producing products is a spring-loaded  camming device called SLCD, or cams. Its 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. Its 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.

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.

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.

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 65,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. Its believed the value of the equipment in five years  will be $1.1 million.

Production of the current cam line is expected to be terminated in  two years. If ECCC doesnt 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, theres no  initial outlay for NWC, but changes in NWC will occur in Year 1 with the  first years 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.)

Leah has provided you with a data report in an Excel spreadsheet that contains information to answer the following questions:

1. Whats the payback period of the project?
2. Whats the profitability index of the project?
3. Whats the IRR of the project?
4. Whats the NPV of the project?
5. Should Leah accept the project?
6. If Leah needs to adjust the price of the product, whats 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)?

Part 4: Risky Business

Lastly, just for fun, El Cap Climbing Company (ECCC) is looking at determining its sensitivity to market fluctuations.

Since ECCC isnt publically traded and cant look at its own stock  history, it must evaluate its competitors. Black Diamond Equipment is  its closest competitor, but the company doesnt have enough trading  volume to make any sound conclusions. Leah identifies Callaway Golf  Company (ELY) as ECCCs closest publicly-traded competitor. Even though  ELY sells golf equipment, it too is a specialized company selling  high-tech sports equipment.

Finding Beta with CAPM

Note: This information is also in your textbook.

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:

E(Ri) = Rf + [E(RM) Rf]

Since CAPM is an equation, you can subtract the risk-free rate from both sides, which gives you:

E(Ri) Rf = [E(RM) Rf]

This equation is deterministicthat is, exact. In a regression,  you’ll realize that theres some indeterminate error. You need to  formally recognize this in the equation by adding epsilon, which  represents this error:

E(Ri) Rf = [E(RM) Rf] +

Finally, think of the above equation in a regression. Since theres  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:

E(Ri) Rf = i + [E(RM) Rf] +

The intercept term is known as Jensens 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. Youll 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.

A. Scatter Plotting and Regression Analysis

Use the following steps to create the scatter plot:

1. Go to the Part 4 Stock Data tab in your Excel spreadsheet. Highlight column KM headings, then hold down the Ctrl button and select cells K-3 through M-62. 
2. Go to the Insert tab, click on Scatter Chart, and select the first style.
    
3. 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.
4. In the Design tab, click on Quick Layout. Select the layout that gives you the y formulas and the R2.
    
5. Move the y formulas and the R2 to the bottom right-hand corner of the chart.

Now, use the following steps to create a regression analysis for ELY and for the Mutual Fund:

1. 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.
    

 

If you dont 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 .  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.

1. Click on Data Analysis. A dialog box with a list of analysis tools will open. Select Regression from the list and click OK.
    
2. 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 youre 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.  
    
3. 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.

B. Answer the following questions:

1. 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 stocks estimated beta). t represents the residuals for the regression. The intercept, i,  is often called Jensens alpha. What does it measure? If an asset has a  positive Jensens alpha, where would it plot with respect to the SML?
   Rt Rft = i + i [RMt – Rft] + t 
2. Is the alpha of either ELY or the mutual fund significantly more or less than zero? (Hint: The alpha is the intercept.)
3. How do you interpret the beta for the stock and the mutual fund? (Hint: The beta is next to the coefficient.)
4. Which of the two regression estimates has the highest R-squared? Is this what you would have expected? Use the scatterplot to explain why.