[Solution Available] FIN 301 Penn State World Campus Final Excel project
8 views 0 purchase
Course
FIN 301
Institution
FIN 301
FIN 301 Penn State World Campus Final Excel project Scenario: Tom Jones, the CFO for the firm PSUWC Energy, LLC, woke up with a start at 4:00 am on July 2, 2012, due to his phone ringing. It was his senior financial analyst, vacationing in Europe, calling with bad news. Tom was supposed to present ...
solution available fin 301 penn state world campus final excel project
Written for
FIN 301
All documents for this subject (55)
Seller
Follow
EvaTee
Reviews received
Content preview
Grading Rubric Your text here 1
Grading Rubric for Final Project in FIN 301
Total
0 0.5 1 2 Possible
Points
Correct Calculation using Correct Calculation using
No Calculations or Calculation using book
Calculation of Weights
Explanations values for weights
Market Rates with NO Market Rates with 2
Explanation Explanation
Incorrect Calculation,
No Calculations or Correct Calculation based
Cost of Debt
Explanations
Brief Explanation,
on YTM with Explanation 1
Reasonable estimate
Cost of Equity
Reasonable Estimate of
Beta based on
Correct Estimate of Beta
No Calculations or information provided
Beta
Explanations online (i.e Yahoo
based on online data with 1
Explanation
Finance) but No
Explanation
Estimate for Market Explanation of Expected
Expected Market No Calculations or Return is not reasonable Returns based on historical
Return Explanations based on historical returns with reasonable 1
market performance estimate
Estimate not based on
Explanation of Risk free
No Calculations or current Treasury yields
Risk-free Rate
Explanations but reasonable no
rate based on current T- 1
bond or T-bill yield
explanation
Incorrect Calculation
Incorrect Calculation with
with minor errors carried Correct Cacluation with
No Calculations or minor errors carried through
WACC Calculation Explanations through from above
from above assumptions
Explanation of all 2
assumptions with no parameters
with explanation
explanation
Total Possible Points from WACC calculation portion of project 8
Total
Cash Flow Calc 0 1 2 4 Possible
Points
All initial CFs properly
No Calculations or
Initial Cash Flows
Explanations
Partially correct CFs identified with brief 2
explanation
All OCFs properly
identified with brief
Few OCFs properly Most OCFs properly
No Calculations or explanation of all
Operating CFs
Explanations
identified with no identified with brief
calculations (ie taxes &
4
explanation explanation
depr properly accounted
for)
All terminal after tax CFs
No Calculations or Partially correct CFs no
Terminal CFs
Explanations tax adjustments
properly identified with 2
brief explanation
All PV of Cash Flows
No Calculations or correctly calculated and
75/25 D/E Split
Explanations
Partial Calculations
Justification for use of NPV
2.5
technique
All PV of Cash Flows
No Calculations or correctly calculated and
25/75 D/E Split
Explanations
Partial Calculations
Explantion for underlying
2.5
assumptions of case
Total Possible Points from Cash Flow portion of project 13
0 1 2 3
Create NPV Profile No Graph, Partial Graph is correct with all
No Calculations or Graph is mostly correct with
for Varying
Explanations
Calculations, Partial
Partial Explanation
axis properly labled with 3
Debt/WACC Explanation Explanation
Final Decision is correct Final Decision is correct
Final Decision is correct
Final Decision is with limited or no with thorough
Final Decision by with partial explanation of
Case: Accept or Reject
Incorrect or Not explanation of basis of
basis of decision and all
explanation of basis of 3
Stated decision and all decision and all
assumptions
assumptions assumptions
Somewhat difficult to Very Organized, Neat, and Very Organized, Neat,
Unorganized
find some information Easy for reader to follow and Easy for reader to
difficult to follow
Presentation
with spelling and
and some disorder with all inputs and follow with all inputs and 3
gramar and spelling assumptions not clearly assumptions clearly
grammar errors
errors identified in one area identified in one area
Total Possible Points from NPV Profiles and Presentation portion of project 9
Total Possible Points for the Final Project 30
Page 1
,Minimum requirements for the Project.
1. Calculate the WACC for the company.
2. Create a partial income statement incremental cash flows from this project in the Blank Template
worksheet.
3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. (You
can either use the EXCEL formula PV() or use mathematical formula for PV of a lump sum.)
4. Set up the EXCEL worksheet so that you are able to change the parameters in B4-B12 and E9-E10.
These are highlighted in Yellow on the Blank template. Run the two cases: 75/25% split between issuing
bonds/equity and a 25/75% split between issuing bonds/equity. You can do this by completing one case
and then simply creating a copy of the worksheet and changing the numbers of the split (cells E9 and
E10). Alternately, if you are comfortable with creating drop down boxes in excel, you can set the sheets
up so that the user can choose this value from the drop down box and obtain the necessary solution. Note
that it is important to use the VLOOKUP function for calculating the cost of debt for automatic updating
5. Cells B54:64 List the Debt Fraction for New Capital varying from 0% to 100%. Using the Data Table
Concept, calculate the WACC and NPV for each value.
6. 5) Using the data generated using Data Tables, plot the NPV & WACC as a function of Debt Fraction.
Make sure to plot the NPV on the primary Y-axis and the WACC on the secondary Y-axis. The Debt
Fraction should be on the X-axis. Alternately, you can also plot NPV & Debt Fraction as a function of
WACC. If you do so, ensure that you plot the NPV on the primary Y-axis and the Debt Fraction on the
secondary Y-axis. The WACC should be on the X-axis in this case. (see case for full instructions)
6. Make a clear recommendation whether the company should accept or reject the project for each case
scenario.
7. If you have any discussion/explanation for any assumptions you have made or need to provide more
discussion, you may do so on the worksheet titled " Answer Sheet "
8. Turn in your project in the drop box.
, This sheet is provided in case you want to provide explantions for your answers
Topic
Weights of Debt and Equity, Wd & We
Cost of Debt Calculation
Beta for the Firm
Expected Market Return
Risk-Free Rate
WACC
WACC Calculation
Initial Cash Flows
Operating CFs
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through EFT, credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying this summary from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller EvaTee. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy this summary for $16.47. You're not tied to anything after your purchase.
