FIN 355 Investments Final Exam Part II
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIN 355 Investments
Final Exam Part II (Takehome Exam)
Excel Applications & Analytics Case Studies
General Instructions:
1. Collaborations of any kind are strictly prohibited.
2. Final Deliverables:
1). Include all answers in one single Excel file with multiple tabs or worksheets, and label the individual worksheets.
2). Include your full name as a part of the file name (e.g. Lisa Zimmer Final Exam.xls). Please save frequently.
3). Submit your Excel solution file using iLearn portal. Email submission is only ok as the last resort. Score markdowns may apply if you submission is late.
4). Due EOB Wednesday, 11:59PM, 8/10/2022 (Pacific Time). Final exam (part 2) accounts for 50% of the final test weights.
5). The course project is due the same time as the final takehome exam.
5). Thanks for your efforts and best luck with your SFSU studies!
Question 1
Investment Risk Analytics via Sensitivity Analysis (15 Points)
ABC Technology LLC is a subsidiary of SAP Corporation based in central Germany. The firm is working on a project that has a time horizon of 10 years. The table below contains the expected cash flow (in $000) over the next 10 years. The initial cost is $1,995 (in $000). Assume an 8.25% discount rate.
1). Calculate the project’s IRR and NPV using Excel’s financial functions. Assuming reinvestment return is also 8.25% (5 points)
2). Sensitivity analysis using Excel data table: Use the Excel to draw a chart of the NPV as a function of discount rate. The discount rates should range between 618%, with 1% increments. (10 points)

Cash flow ($000) 
1 
320 
2 
350 
3 
350 
4 
400 
5 
400 
6 
420 
7 
375 
8 
350 
9 
375 
10 
350 
Question 2 Investment Analysis
Bartman Industries and Reynolds Inc’s stock prices and dividends, along with Wilshire 5000 Index, are shown below for the periods 20092014. The Wilshire index data are adjusted to include dividends. (24 points; 6 points each)

Bartman Industries 
Reynolds Incorporated 
Wilshire 5000 

Year 
Stock Price 
Dividend 
Stock Price 
Dividend 
Includes Dividends 

2014 
$ 18.50 
$ 1.15 
$ 49.25 
$ 3.00 
$ 11,773.98 

2013 
$ 15.00 
$ 1.09 
$ 53.10 
$ 2.85 
$ 8,895.70 

2012 
$ 16.00 
$ 1.04 
$ 49.25 
$ 2.71 
$ 8,689.98 

2011 
$ 11.00 
$ 0.99 
$ 58.25 
$ 2.57 
$ 6,444.03 

2010 
$ 12.00 
$ 0.94 
$ 61.00 
$ 2.44 
$ 5,612.28 

2009 
$ 8.60 
$ 0.89 
$ 56.05 
$ 2.32 
$ 4,815.97 
a). Using the data given, calculate the annual returns for Bartman, Reynolds, Wilshire index. Calculate simple average returns and geometric returns over the 5year holding period. [Hints: Returns are calculated by subtracting the beginning price from the ending price to get the capital gain or loss, adding dividend to capital gain or loss, and dividing the result by the beginning price].
b). Calculate the standard deviation of the returns for Bartman, Reynolds, the Wilshire index.
c). Calculate coefficients of variation for Bartman, Reynolds, Wilshire index (Hints: CV = Std dev / Mean)
d). Construct a scatter diagram graph showing the returns for Bartman and Reynolds on the vertical axis, the Wilshire index on the horizontal axis. Add the regression lines.
Question 3 30 points
3.1 (10 points)
Buckner Industries has prepared the condensed forecast income statement for the year ending December 31, 2002.
After creating the forecast, Buckner develops a new product, which will require $100 million in additional capital expenditures at the beginning of 2002. With the new product, EBIT in 2002 is expected to be 15 percent higher than the amount forecast in Exhibit above. To finance the increase in the capital budget, Buckner is considering a plan using 50 percent equity and 50 percent longterm debt. New equity would be issued at $25.00 net proceeds per share and the interest rate on the new longterm debt would be 8.50 percent. Buckner is reviewing how this financing, if completed on December 31, 2001, would affect the company’s EPS.
Question: Construct a pro forma income statement for 2002, assuming the financing plan is adopted. Show your calculations.
3.2 (10 points) Financial Analytics MiniCase Study
Dinsmore Artists International is in the business of managing singers and other artists in the entertainment industry. It is considering the purchase of an executive jet plane to transport its executives and the artists it represents to various meetings and performance sites. It expects that by owning its own executive jet, it can save $1,400,000 the first year of operation for expenses that it would otherwise incur for buying seats on commercial flights or for chartering flights. It expects that the yeartoyear growth in the annual savings would be 10%.
· The choice has narrowed down to two planes: the Aero Commander and the Super Eagle. Both provide the same savings and the same basic service (e.g., the same passenger and luggage capacity, flight speed, and maximum altitude of operation).
· The Aero Commander jet sells for $4,500,000. Its normal operating expenses would be $290,000 the first year and would increase 8% per year thereafter. In addition, there would be a cost of $350,000 for a major engine overhaul at the end of the third year. Treat the overhaul cost as an operating expense. The cabin noise level in the Aero Commander is lower than in the Super Eagle, and its seats are somewhat more comfortable.
· The Super Eagle jet sells for $3,950,000. Its normal operating expenses would be $325,000 the first year and would increase 8% per year thereafter. In addition, there would be major engine overhauls at the end of the second and fourth years, each of which would cost $300,000. Treat the overhaul costs as operating expenses.
· Dinsmore uses a WACC or discount rate of 10% and a reinvestment rate of 9% to evaluate its investments in fixed assets. Tax rates are 38% for regular income and 25% for capital gains or losses.
· The jet purchased would be paid for and put into service during the first quarter of Dinsmore’s financial year. It would be depreciated according to the appropriate MACRS schedule from (i.e., 7year life with firstquarter convention).
· Dinsmore expects to sell whichever plane it chooses at the end of the fifth year for 20% of its purchase price.
Questions:
1. What is the NPV, IRR, and modified internal rate of return associated with each of the two jet planes? Based on these values, what action do you recommend Dinsmore to take? Show your calculations (5 points)
2. What nonfinancial information should Dinsmore take into consideration before making its final decision? Why might the information be important in Dinsmore’s decision? How might this information change the decision in part 1? Show your calculations (5 points)
3.3. Financial Analytics MiniCase Study (10 points)
Lenders generally allow clients to borrow as much as they believe borrowers can afford, based on their income, debts, and credit history. When deciding whether or not a potential buyer qualifies for a first mortgage on a home, lenders usually look at two ratios, called the frontend and the backend ratios,. Each ratio generally produces a different home price that a buyer can afford. The maximum home price a buyer can afford is the lesser of the two affordable home prices produced by the two ratios.
· The frontend ratio is the monthly cost of ownership, which includes the monthly payments on the mortgage (principal plus interest), taxes, insurance, and any homeowners association dues, as a percentage of the buyer’s monthly income. Lenders used to limit frontend ratios up to 33 percent, but later they allowed up to 40 percent.
· The backend ratio is the sum of the monthly cost of ownership (as in part a) plus other debt payments, such as loans for cars, furniture, and home appliances, as a percentage of the buyer’s monthly income. Lenders used to limit backend ratios up to 36 percent, but later they allowed up to 42 percent (and sometimes higher).
· Marilyn and Paul Jones are potential firsttime home owners. Their combined annual income is $115,000. They are making $510 monthly payments on a 3year old car and $90 monthly payments for the furniture they have had in the condo they have rented for the past two years. They have saved enough money to make a down payment of $90,000 on the purchase of a home.
· The annual cost of home ownership and liability insurance would be 0.5% of the selling price of the home, and the annual taxes would be 1% of the selling price of the home. The lender will charge an annual interest rate of 6% on a conventional 30year first mortgage.
Questions:
1). Using the guide lines above, what is the maximum mortgage and home price Marilyn and Paul can afford? Include both a frontend and backend analysis sidebyside on a single worksheet. Use maximum allowable values of 40% for the frontend ratio and 42% for the backend ratio. Show all input data and all calculations or results. Use an IF statement to identify the maximum mortgage and home price Marilyn and Paul can afford. Show your calculations. (5 points)
2). Suppose Marilyn and Paul used $10,000 to pay off the entire balance of their car and furniture loans, thereby reducing their home down payment to $80,000. How would this change the maximum mortgage and home price they can afford? Show your calculations. (5 points)
Question 4: Investment Risk & Portfolio Analytics
Please review the attached Excel spreadsheet. Complete the yellowhighlighted areas. (10 points)
Question V Corporate Financial Analysis (21 points)
Part 1: Fixed Income Securities and TVM (5 points)
Please watch the following MIT video. Write 1 page of review note summarizing the key lessons you have learned from watching the video.
https://www.youtube.com/watch?v=hyc8h5T76BE
Part 2: DCF Analysis in Equity Research (5 points)
Please watch the following MIT video. Write 1 page of review note summarizing the key lessons you have learned from watching the video.
https://www.youtube.com/watch?v=rifTKTvPDM0
Part 3: Financial Derivatives (options / futures / forwards) (5 points)
Please watch the following MIT video. Write 1 page of review note summarizing the key lessons you have learned from watching the video.
https://www.youtube.com/watch?v=IwA7nVEwqto
Part 4: usprivateequitysafeguardingprivateequityfirms.pdf (assets.kpmg) (6 points)
Please the above PDF file link by KPMG. Write 1 page of review note summarizing the key lessons that you have learned.
20220811