Need it done in three hours. There is an excel template and a word document

ASSUME: Stated Interest Rate (____) is ______ than the Yield (_____) required by the lender: ________ Period 0 Dec June Dec June Dec June Dec June Dec June Dec June Dec June Dec June Dec June Dec June Year Interest Expense for Period, H*___ Balance at Beginning of Period % 2012 #DIV/0! 2013 2013 2014 2014 2015 2015 2016 2016 2017 2017 2018 2018 2019 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Premium amortization, D-E #DIV/0! 2012 Cash Payment, Face*__% 2019 2020 2020 2021 2021 2022 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Premium balance, prior balance - F #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Balance at End of Period Face-G #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Initial Valuation 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 0 #DIV/0! Valuation at Repurchase #DIV/0! Annuity Factor #DIV/0! Present Value factor 1 Annuity Factor at repurchase #DIV/0! Present Value factor at repurchase 1 ASSUME:Old bonds are refunded with a new $1,000,000 issue New Loan Period 10 9 8 7 6 5 4 3 2 1 0 Annuity Factor 0 Old Loan Year 2017 2017 2018 2018 2019 2019 2020 2020 2021 2021 2022 Balance at Beginning of Period 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 Present Value factor 1 Interest Expense for Period, H*__% 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Cash Payment, Face*___% 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Balance at End of Period Face-G 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 1,000,000.00 Cash Payment, Market Value of old bonds Face*__% Differential Cash Flows at repurchase date 0.00 0.00 #DIV/0! 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 #DIV/0! Economic was #DIV/0! Accounting GA sh: YES? NO? GAIN? LOSS? Example Company Black-Scholes-Merton Stock Price Strike Price Volatility Riskless Rate Term Dividend Yield d1 d2 Call 0 #DIV/0! #DIV/0! #DIV/0! You wold like the stock price at vesting to be at least If the stock price reaches $60, you got more then your employer thought your work is worth. You gain is a cost to the shareholders. Student name Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Total 125 PROBLEM # 1: Accounting for Bonds (30 points) Billabong Inc. needed financing to build a new plant. On June 30th, 2012, Billabong issued $1,000,000 of 10-year bonds with a 10% coupon rate (payments due on December 31st and June 30th). The effective interest rate (i.e. yield) was 8%. Use the financial statement effects template below to record the bond issue and Billabong's first interest payments. PLEASE SHOW YOUR CALUCLATIONS IN EXCEL. Please use the EXCEL Sheet provided and make the appropriate adjustments for your present value calculations. Tip: The cells in Yellow need adjustments. Income Statement Balance Sheet Transaction Cash Noncash + = Asset Assets Liabil+ ities Contrib. + Capital Earned Capital Revenues - Expen= ses Bond issue = - = Interest 12/31 = - = Dr. Andreas Simon, ACCT 652, Fall 2014 2 Net Income PROBLEM # 2: Early Retirement of Bonds. (30 Points) Assume further that after 5 years (10 periods left), Billabong wants to buy back the bonds they issued in Problem 1). At this time interest rates have fallen to 4% and the yield equals the coupon rate. Please calculate all the relevant present values PLEASE SHOW YOUR CALUCLATIONS IN EXCEL. Please use the EXCEL Sheet provided and make the appropriate adjustments for your present value calculations. The cells in BLUE need adjustments. Answer the following questions. What would be the price of the bonds in the market at the time of repurchase? Would Billabong generate an economic gain/loss? Show the present value differential of the old vs. the new bond. Would Billabong generate an accounting gain/ loss? Would you retire the bonds early? Give one reason for why and one reason for why not. Dr. Andreas Simon, ACCT 652, Fall 2014 3 PROBLEM # 3: Daisy Cakes. (10 Points) Please explain one advantage and one disadvantage of financing a company with debt. Advantage Disadvantage PROBLEM # 4: Financial statement effects of Stockholders' Equity Transactions (30 points) Billabong's statement of stockholders' equity on December 31, 2011 is as follows: -----------6% cumulative preferred stock, $50 par,10,000 shares authorized; 5,000 shares issued and outstanding $250,000 Common stock, $2 par, 500,000 share authorized; 245,000 shares issued and outstanding 490,000 Paid-in capital in excess of par - Preferred stock 10,000 Paid-in capital in excess of par - Common stock 1,960,000 Retained earnings Total stockholders' equity 620,000 $3,330,000 ---------The following transactions occurred during 2012: Dr. Andreas Simon, ACCT 652, Fall 2014 4 Jan 15 Issued 1,500 shares of preferred stock for $50 per share. Apr 14 Issued 75,000 shares of common stock for $10 per share. Net proceeds were $710,000 Jul 3 Repurchased 20,000 shares of common stock at $6 per share. Nov 1 Sold 10,000 shares of treasury stock bought on July 3 for $18 per share. Nov 26 Split stock of common stock 2-for-1 reducing the par value of the stock Dec 5 Issue a stock dividend of 100% of the outstanding shares of common share. Use the financial statement effects template, below, to record these transactions. Income Statement Balance Sheet Transaction Cash Asset + Noncash Assets Liabil= + ities Contrib. Capital Earned + Capital Revenues - Expenses Net = Income Jan 15 = - = Apr 14 = - = July 3 = - = Nov 1 = - = Nov 26 = - = Dec 5 = - = PROBLEM # 5: Jimmy Fu and Moog Corporation (25 points) Dr. Andreas Simon, ACCT 652, Fall 2014 5 If you were in Jimmy's shoes and are considering taking a job at Moog, how would you assess the following stock option grant? The strike and stock price are $30, volatility is 40%, the riskless rate is 1.5%, the vesting period is 4 years, the term is 10 years and the expected life is 5 years. What price would you like to see the stock price reach at the minimum after you are vested to make the stock options worth considering? PLEASE SHOW YOUR CALUCLATIONS IN EXCEL. Please use the EXCEL Sheet provided and make the appropriate adjustments for your present value calculations. The cells in GREEN need adjustments. 1. 2. Discuss the implications if the stock price is $60 when your options vest. Consider the strike price, option price at grant and stock price at vesting. 3. If Jimmy get 1000 options, use the same criteria as in 1, and the stock price is $60 at exercise, show the financial statement effects entry at issuance of the options and at exercise. Income Statement Balance Sheet Transaction Cash Asset + Noncash Assets Liabil= + ities Contrib. Capital Earned + Capital Revenues - Expenses Net = Income Issue = - = Exercise = - = Dr. Andreas Simon, ACCT 652, Fall 2014 6