Financial Modelling Assignment | Online Homework Help
FIN 380 Take-Home Final Examination- Spring 2019
The following pages contain the take home final exam for Finance 380, sections A and B. The exam is due no later than Monday, May 13th at 9:00 AM. Late exams will lose 1 point for every minute late. By electronically submitting the final exam, you are attesting to the following. Please READ carefully.
- I understand that this is an examination and attest that the work presented is my own; that it is done without outside assistance of any kind (except that provided by the instructor). I understand that the penalty for cheating or collusion of any kind is a grade of F for the course.
- Spreadsheet solutions will be appropriately formatted and developed in a logical, readable, coherent fashion. Confusing or unformatted spreadsheets may result in a reduction of points.
- I understand that the file tabs are in their original order and understand that out-of-order problems will not be graded (Note: it is YOUR responsibility to insure that what you pass in ordered correctly)
- I understand that the due date and time for the exam is Monday, May 13th at 9:00 AM. I understand that the exam must be submitted electronically by email to me at JAshburne@Bryant.edu. I understand that exams passed in after that time will be considered late and will incur a minimum penalty of 1 point lost per minute late.
- The file name should contain both your last name, first name, section and the word “Final” in that order. For example, if you’re in the 10AM section (Section A), your file name should be Ashburne.John.A.Final.xlsx
- Make sure you pass in the FINAL exam, not a homework assignment or your midterm. I don’t have time to hunt you down.
- If you submit your exam early, I’ll try to open the file and make sure I have the right exam and that I can view it. I’ll let you know if I have problems. It is unlikely that I will be able to do so for any exam submitted after 5:00 P.M. on the day (Sunday) before the due date.
- The exam is being made available to you on or before May 6th and is due May 13th. That means you have ample time to ask questions if you are unclear about the instructions or what you are expected to provide as an answer. If you don’t understand a question, ask BEFORE the exam is due for clarification. Misunderstood questions leading to wrong answers will count against you.
- Good luck! It’s almost over
INDEX [10] This worksheet contains prices and dividends (Hyatt recently began paying dividends in March, 2018) for four companies; each is chosen to represent a different industry sector. Note that Kroger split 1 for 2 in July 2015. Share prices and dividends have been adjusted so that pre-split values are consistent with values after the split. The number of shares for Kroger as shown in the table below should be used for all dates.
Construct a Price Index, an Equally Weighted Returns Index, and a Value Weighted Returns Index. Set the initial index level at 25. Graph all three indices on the same graph.
Company | Shares (MM) | Industry | |
1 | Pepsico, Inc. | 1,401.8 | Food and Beverage |
2 | Hyatt Hotels | 40.3 | Hotels |
3 | Kroger Markets | 797.4 | Food Distribution |
4 | CVS Health | 1,290.0 | Health Care |
=====================================================================================
DECIDE [10] Write a spreadsheet that allows the user to input an initial outlay, up to 10 years of non-constant cash-flows after tax and a discount rate. The spreadsheet should return the Payback Period, the Discounted Payback Period, the Internal Rate of Return , the Modified Internal Rate of Return, and the Net Present Value.
=====================================================================================
EQUATION [5] Consider the following system of four equations and four unknowns:
3α + β +2γ + 3δ =35
2α – β +8γ – δ =-29
-9α +7β +9γ + 6δ =-70
α + 8β +7γ – 8δ =4
Solve for α, β, γ, and δ.
=====================================================================================
NEWELL [10] Newell Brands Inc (NWL) is a global manufacturer and distributor of consumer products that include household names such as Coleman Camping, Mr. Coffee, Rubbermaid, Sunbeam and other familiar names. I have provided monthly stock price and dividend information as well as index levels of the S&P 500 and monthly risk free rates. Using a characteristic line in risk premium form, calculate beta and determine if Newell Brands Inc. stock has outperformed the market on a risk-adjusted basis.
=====================================================================================
PORT [10] In Investment class, you’ve no doubt seen the derivation (at least graphically) of the Capital Market Line, a line which shows the risk-return tradeoffs for efficient portfolios when the risk-free asset is added to the mix. The market portfolio is found by drawing a line from the risk-free rate of interest (r_{f}) on the Y axis tangent to the efficient frontier. The market (or tangent) portfolio is the portfolio ON THE EFFICIENT FRONTIER that maximizes the slope of the Capital Market Line. That is, it is the portfolio (call it portfolio T) that maximizes:
Subject to the usual constraints (ie the weights add up to one). The worksheet PORT contains the covariance matrix and mean returns for eight stocks. Using the matrix approach and SOLVER, solve for the weights in the tangent portfolio assuming the risk-free rate of interest is 2.5% (you may allow for short-selling).
=====================================================================================
SPORT-T [15] Sports-T Company makes T-shirts for sports fans. I have provided pro forma balance sheets, income statements and the structure for a statement of cash flows. Using the percent of sales method, forecast financial statements (balance sheets, income statements, and statements of cash flow) for the next five years. Assume excess funds are invested in marketable securities, and any financing shortfall is made up by ISSUING LONG TERM DEBT. Estimate the Terminal Value at the end of Year 5 using the Perpetuity Growth Method (where the risk free rate is 2.5%), the Total Enterprise Value at 12/31/17 and the implied share price.
=====================================================================================CAPITAL [15] Capital Choppers is a new business making frames for custom-built motorcycles. The company must buy three pieces of equipment; a pipe cutter, a pipe bender, and a robotic welder to accomplish these tasks. The project is expected to produce bike frames for eight years.
Equipment | Cost | Salvage (year 9) | MACRS life |
Pipe Cutter | $85,000 | $0 | 3 yrs |
Pipe Bender | $165,000 | $10,000 | 5 yrs |
Robot Welder | $825,000 | $40,000 | 5 yrs |
The pipe cutter is expected to wear out and must be replaced in year 5 (assume the cost of the asset increases at 2% per year). The other two pieces of equipment will last through the entire project life. Sales are expected to increase by 3.5% per year throughout the project life. COGS/Sales is 75%; fixed costs are $85,000 per year and working capital/sales (starting in year 1) is 6% (assume it can be recovered in year 9). The project is partially financed by $550,000 in interest only debt paying 4% interest. The tax rate is 25% and the discount rate is 12.5%. What minimum level of sales (in year 1) is necessary to justify this project?
=====================================================================================
OPTION [5] The stock for Bryant Company is currently trading for $84.77 per share. A put option with an exercise price of $89.00 per share and expiring 36 days from now sells for $6.22. If the annual risk free rate is 2.45%, what is the standard deviation of Bryant’s stock?
=====================================================================================
POLLING [10] Bryant Polling has been hired to do political research for Gabby Blovian, a local candidate for State Representative. The costs of interviewing prospective voters varies by gender and political affiliation; for males, it costs $9.50 per Democrat, $10.25 per Republican, and $12.50 per Independent. For females, the costs are $9.75 per Democrat, $11.75 per Republican, and $13.50 per Independent. In order to assure quality, the polling company has been given the following criteria.
- They must interview at least 5,000 voters
- The sample must interview a minimum of 1,000 independent voters.
- At least 2,100 males must be interviewed
- At least 1,950 females must be interviewed
- No more than 30% of those interviewed should be Democrats
- No more than 30% of those interviewed should be Republicans
- No more than 25% of those interviewed should be Republican males
- Each of the six categories of voters (Republican males, Republican females, Democratic males, Democratic females, Independent males or Independent females) must constitute at least 10% of the total interviews
Determine the least expensive sampling plan. What is the total cost of that plan?
=====================================================================================
SUNNY [10] Sunny Solar Energy is considering a highly variable capital budgeting project which will last for four years. (Assume the salvage value can be recovered, along with the working capital in year 5). The project will carry a discount rate of 11% and will be depreciated using 3 year MACRS factors. You have been provided the following information:
Variable | Distribution | Mean | Standard Deviation |
Initial Outlay | Normal | $ 1,000,000 | $ 10,000 |
Initial Sales | Normal | $ 1,600,000 | $ 200,000 |
Growth in Sales | Normal | 4% | 2% |
COGS/Sales | Normal | 70% | 4% |
Initial Fixed Cost | Normal | $ 100,000 | $ 10,000 |
Growth in Fixed Cost | Normal | 3% | 2% |
Working Capital/Sales | Normal | 5% | 1% |
Salvage | Normal | $ 100,000 | $ 5,000 |
Discount Rate | Normal | 12% | 2% |
Minimum | Maximum | ||
Tax Rate | Uniform | 22% | 30% |
Sunny figures that growth in sales, COGS/Sales, growth in fixed costs, WC/Sales and tax rate can vary each year. Estimate the project’s cash flows and calculate the project’s NPV. Run 100 simulations, and graph out the frequency distribution of the project’s NPV. Determine the probability that the NPV is positive.