Select Page
  

I need assistance in completing my Finance project, I attached the instruction. Thank you!
500306.pdf

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on
Finance Project
Just from $10/Page
Order Essay

Graded Project
Corporate Finance
CONTENTS
INTRODUCTION
2
Part 1: El Cap Climbing Company
Part 2: Mortgage Decision
Part 3: Can We Upgrade?
Part 4: Risky Business
2
3
5
6
GRADING CRITERIA
10
Submitting Your Project
11
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 1
CORPORATE FINANCE
INTRODUCTION
The purpose of this graded project is to bring together in a practical way some important
concepts you’ve learned in the course.
Once you’ve completed this project, you can perform similar analyses on other organizations. Some of the work for this project will be completed in Excel. Once you’ve completed
these problems in Excel, and if you’ve linked all your cells properly, you can reuse your
spreadsheets, only changing the inputs. Go to your student portal and download the
500306_Project_Files. This spreadsheet contains information needed to complete the
project. You’ll be submitting this Excel spreadsheet along with your project.
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 hasn’t kept the company’s 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 can’t 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:
n
An income statement for 2015 and 2016
n
A balance sheet for 2015 and 2016
n
Operating cash flows for the two years
n
Cash flows from assets in 2016
n
Cash flows to creditors for 2016
n
Cash flows to stockholders for 2016
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 2
B. Answer the following:
1. How would you describe the financial position of the firm in 2016?
Write a brief overview.
2. What do you think about Leah’s plans to expand?
Part 2: Mortgage Decision
In order 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.
A. Prepare the following:
n
A loan amortization schedule
n
A chart showing the percentage of the payment applied toward the principal
and interest
Loan Amortization Schedule
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.
Payment
Number
Payment
Amount
5% Interest
Expense
Principal
0
Balance
Annual
Interest
Expense
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
360
2,682.54
11.13
2,671.41
2,671.41

855.56
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.
Once you’ve determined how each of the amounts in the table is obtained, you can use
relative and absolute cell references to fill in the full 360 payments.
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 3
The following is an explanation of the columns in the table:
n
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).
n
Payment amount—The second column shows the monthly payment amount.
n
Interest—The third column shows the portion of the monthly payment that goes
to interest.
n
Principal—The fourth column shows the portion paid toward the principal.
n
Balance—The fifth column shows the starting balance of $500,000, and the
remaining balance each month after the principal is subtracted.
n
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.
n
Totals—The “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, you’ll 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. Don’t 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 don’t need to be the same).
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 4
B. Answer the following:
1. How can you describe the relationship between time and the amount paid
towards principal and interest?
2. Knowing what we know about ECCC’s cash flow from Part 1, is it reasonable to believe that ECCC can take on this new debt?
Part 3: Can We Upgrade?
It’s now 2017, 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.
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. It’s 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
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.)
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 5
Leah has provided you with a data report in an Excel spreadsheet that contains information
to answer the following questions:
1. What’s the payback period of the project?
2. What’s the profitability index of the project?
3. What’s the IRR of the project?
4. What’s the NPV of the project?
5. Should Leah accept the project?
6. 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)?
Part 4: Risky Business
Lastly, just for fun, El Cap Climbing Company (ECCC) is looking at determining their
sensitivity to market fluctuations.
Since ECCC isn’t publically traded and can’t look at their own stock history, they must
evaluate their competitors. Black Diamond Equipment is their 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.
Finding Beta with CAPM
Note: This information is also in your textbook.
The 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, we start with the CAPM:
E(Ri) 5 Rf 1 β[E(RM) 2 Rf ]
Since CAPM is an equation, we can subtract the risk-free rate from both sides, which
gives us:
E(Ri) 2 Rf 5 β[E(RM) 2 Rf ]
This equation is deterministic—that is, exact. In a regression, we realize that there’s
some indeterminate error. We need to formally recognize this in the equation by adding
epsilon, which represents this error:
E(Ri) 2 Rf 5 β[E(RM) 2 Rf ] 1 ε
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 6
Finally, think of the above equation in a regression. Since there’s no intercept in the equation, the intercept is zero. However, when we estimate the regression equation, we can
add an intercept term, which we’ll call alpha:
E(Ri) 2 Rf 5 αi 1 β[E(RM) 2 Rf ] 1 ε
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.
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 K–M 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.
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 7
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 image below.
If you don’t see Data Analysis, you might need to add Analysis Toolpak
in Excel. For instructions on how to load the Analysis Tookpak into your
version of Excel, visit https://support.office.com/en-us/article/Loadthe-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4.
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.
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 8
2. Click on Data Analysis. A dialog box with a list of analysis tools will open.
Select Regression from the list and click OK.
3. 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 check boxes for Labels, Confidence Level, and
Residuals. Click OK.
4. 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.”
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 9
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 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 2 Rft 5 αi 1 βi [RMt 2 Rft ] 1 ε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.
GRADING CRITERIA
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:
Part 1—
25 Points Total
Part 2—
25 Points Total
Part 3—
25 Points Total
Part 4—
25 Points Total
Interest: 4 points
Question 1: 3 points
Graph: 5 points
Principal: 2 points
Question 2: 5 points
Balance: 3 points
Question 3: 5 points
Display Amounts:
6 points
Operating Cash
Flows: 4 points
Annual interest:
4 points
Question 4: 5 points
Asset Cash Flows:
6 points
Sub Totals: 2 points
Creditor Cash
Flows: 2 points
Question 1: 2 points
Income Statement:
6 Points
Balance Sheet:
5 points
Stockholder Cash
Flows: 2 points
Graph: 5 points
Question 5: 2 points
Question 6: 5 points
Question 1: 4 points
Question 2: 2 points
Question 3 : 2 points
Question 4: 6 points
Question 2: 3 points
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 10
Submitting Your Project
Each project is individually graded and therefore could take approximately five to seven
business days to grade. Be sure to submit the Excel spreadsheet with your answers and
any word documents.
Follow this procedure to submit your project online:
Make sure the following information is in the heading of each document:
n
Your name
n
Your email address
n
Your student number
n
Course name and number
n
Project number (50030600)
To submit your graded project, follow these steps:
1. Go to http://www.pennfoster.edu and log in to your stu­dent portal.
2. On your student portal, click on Take an Exam.
3. In the box provided, enter the project number. The number for this project
is 50030600.
4. Click on Submit.
5. On the next screen, enter your email address. (Note: This information is required
for online submission.)
6. If you wish to tell your instructor anything specific regarding this project, enter it in
the Comments.
7. Attach your file or files as follows:
a. Click on the first Browse box.
b. Locate the file you wish to attach.
c. Double-click on the file.
d. To attach the additional files, click on the next Browse box and repeat steps
b and c. Repeat until all files are uploaded.
8. Click on Submit.
Be sure to keep a backup copy of any files you submit to the school!
© PENN FOSTER, INC. 2017
CORPORATE
FINANCE
Graded Project
PAGE 11

Purchase answer to see full
attachment

Order your essay today and save 10% with the discount code ESSAYHSELP