Can you please help me with Problem # 2 and 4 in excel. Thank you!

AFF 210 ASSIGNMENT ONE SUMMER 2017 STUDENT #1, NAME: ______________________ STUDENT NUMBER: ___________ STUDENT #2, NAME: ______________________ STUDENT NUMBER: ___________ STUDENT #3, NAME: ______________________ STUDENT NUMBER: ___________ INSTRUCTOR: ___________________________ SECTION #: __________________ PLEASE NOTE THE FOLLOWING: You should solve all of the problems using MS Excel. If you make any assumptions to solve the problem, clearly state them at the beginning of the solution and explain the reason. The assignment may be done either individually or in groups of up to three students. If you are doing the assignment in a group, all of the group members must be registered in the same section of the course. Each member of the group must submit an electronic copy of the assignment via D2L. However, the groups only need to hand in one hard copy of the assignment. Both the hard copy and the electronic copy of the assignment are due at the start of Term Test on May 25th, 2017. The assignment will be graded according to the work written on the hard copy. Please make sure all the details and answers clearly shown and printed. If your answer is correct but you have not shown your work, you will receive a grade of zero. Photocopies or other reproductions of other groups' work will receive a grade of zero. AFF 210 Page 1 of AFF 210 ASSIGNMENT ONE SUMMER 2017 Please include the cover page and this second page when you hand in your assignment. Question # AFF 210 Marks 1 /20 2 /20 3 /20 4 /20 5 /20 Total /100 Page 2 of AFF 210 ASSIGNMENT ONE SUMMER 2017 PROBLEM ONE You are planning to buy a 4 bedroom house in Markham that has a price of $1,200,000. One of the local banks has offered you a mortgage at a quoted rate 3.2% per year, compounded semi-annually. The bank has indicated that they will require a down payment of $300,000. The bank is prepared to lend you the remainder of the purchase price of the house. The amortization period will be 20 years and the term of the mortgage will be 43 years. You are going to make monthly (12 payments per year)semi-monthly payments on your mortgage. The payments will be made at the end of each period. a) b) Prepare a complete mortgage amortization schedule to illustrate how the mortgage will be repaid over the next 20 years and calculate the following: i. What is the amount of your periodic payment? ii. How much will you pay in total over the life of your mortgage (20 years)? iii. What is total interest that will be paid over the life of your mortgage (20 years)? iv. How much principal will you have paid off during the initial term of your mortgage (the first 43 years)? v. What is total interest that will be paid during the initial term of your mortgage (the first 4 years)? Print the amortization schedule using the following instructions: Do NOT print the entire mortgage schedule. Hide the rows in between and print ONLY the first 10 rows and the last 10 rows of the mortgage schedule. c) Assuming that you decide to make to increase your periodican additional semi-monthly payment by $of250 each period,$50 only during the initial term of your mortgage, how much less do you owe to the bank after the first 43 years compared to the original schedule? Assuming that after the initial you only make the regular required payment, how much interest will you save over the life of the mortgage? d) Assuming that you decide to make to increase your periodican additional semi-monthly payment by $of250 each period,$50 during the entire life of your mortgage, how much interest will you save over the life of the mortgage? e) AFF 210 Page 3 of AFF 210 ASSIGNMENT ONE SUMMER 2017 PROBLEM TWO Beth is 25 years old. However, she is already planning for retirement. She plans on retiring in 30 years when she will be 55 years old. Beth believes she will live until she is 95. In order to live comfortably, she thinks she will need to withdraw $10,000 every month during retirement. These monthly withdrawals will be made at the end of each month during retirement. There will no periodic payment paid on the day that Beth dies. Apart from her personal withdrawals, being a lover of animals, Beth has pledged to donate $2,500 every two years to \"World Wide Fund for Nature\" during her retirement. The first donation will occur one year after she retires. There will be no payments to World Wide Fund after Beth dies. In addition, she would like to establish a scholarship with annual payments at Ryerson. The first payment from the scholarship would be $15,000, which would be made 5 years after she retires. In order to keep pace with inflation, Beth would like the amount of scholarship payments to increase by 3% each year. She wants the payments to continue after her death, which will last forever. During retirement, Beth expects to earn 5% per year compounded annually. Finally, Beth wishes to take an around the world trip 20 years from now. The trip will cost $50,000 at that time. (In order to take this into account you will need to calculate how much the trip would cost when Beth retired. In order to do this you need to calculate the Future Value of the trip at retirement using the rate of return on shortfall investments, 7.5 per year compounded monthly.) She currently has $10,000 in her investment account that earns 8% interest per year compounded semi-annually. Beth currently contributes $200 every month to her RRSP. These contributions are made at the end of each month until she retires at 55. Beth expects to earn 5% per year compounded monthly on her RRSP contributions prior to retirement. a) Based on her financial goals described, how much money does she need when she retires at the age of 55? b) Based on her savings described, how much money will she have when she retires? c) In order to finance any shortfall between (a) and (b) calculated above, Beth will make weekly contributions into a new retirement account. This new retirement account will earn 7.5% per year compounded monthly. The contributions will be made at the end of each week until she retires at 55. How much must she contribute each week to the new retirement account? d) Suppose that everything is the same except that Beth would like the monthly payments during retirement to increase by 0.3% (3/10 of a percent) each month. How much money must Beth have when she retires? Given the new shortfall, what will be amount of the new monthly contributions. AFF 210 Page 4 of AFF 210 ASSIGNMENT ONE SUMMER 2017 PROBLEM THREE TRSM Ltd. Income Statement For the Year Ended December 31st, Sales Cost Of Goods Sold Other Expenses Depreciation Earnings Before Interest and Taxes Interest Expense Earnings Before Taxes Taxes (20%) Net Income TRSM Ltd. Balance Sheet As at December 31st, ASSETS Cash & Equivalent Short-term investments Inventories Accounts Receivable Total Current Assets Prop, Plant & Equip - Net Total Assets LIABILITIES & EQUITY Accounts Payable Wages Payable Accruals Taxes Payable Short-term Debt Total Current Liabilities Long-Term Debt Total Liabilities Common Share Capital Retained Earnings Total Equity Total Liabilities and Equity 2016 2015 5,320,000 4,650,500 2,560,000 2,210,400 1,150,000 975,800 125,500 105,300 1,484,500 1,359,000 74,350 58,650 1,410,150 1,300,350 282,030 260,070 $1,128,120 $1,040,280 2016 2015 72,510 205,220 303,750 170,575 1,450,625 1,303,750 230,785 173,440 2,057,670 1,852,985 1,850,850 1,753,325 $3,908,520 $3,606,310 283,225 275,300 80,730 85,000 62,502 55,735 30,303 25,203 315,750 283,302 772,510 724,540 825,855 844,139 1,598,365 1,568,679 825,000 700,000 1,485,155 1,337,631 2,310,155 2,037,631 $3,908,520 $3,606,310 Answer the following questions. Please note: short-term investments are non-operating current assets; notes payable and short-term debt are non-operating current liabilities. AFF 210 Page 5 of AFF 210 a) ASSIGNMENT ONE SUMMER 2017 What is Free Cash Flow for 2016? b) The stock is traded at $38 per share at the end of 2016 and there are 80,000 shares outstanding. What is MVA during 2016? c) Given the firm's WACC is 8%, what is EVA during 2016? d) Create common size income statement and balance sheet for both 2016 and 2015. e) Create income statement and balance sheet percentage change analysis for 2016. (Use 2015 as the base year) AFF 210 Page 6 of AFF 210 ASSIGNMENT ONE SUMMER 2017 PROBLEM FOUR In addition to the TRSM Ltd. financial statements in Problem Three, you are given more information as follows. Sales are forecasted to increase by 40% in 2017. Short-term Debt, Long-term Debt, and Common Share Capital will not change. Net Plant and Equipment is forecasted to be $2,200,000 next year. Short-term investments are expected to be $100,000. Taxes Payable are forecasted to be $35,000. The company has a financial policy to use Short-term Debt as \"the plug\". Therefore, when extra funding is needed, Short-term Debt will increase to cover the shortage. Thus, the pro-forma balance sheet will become balanced. In 2017, the company's dividend payout ratio will be 40%. In 2017, cost of goods sold is expected to be 46% of sales. Other expenses will be 20% of sales. Depreciation expense in 2017 is expected to be $145,000. In 2017, cash is expected to be 3% of sales, and inventories will be 28% of sales. Accounts receivable will be 16% of sales. Accounts payable will be 13% of sales. Accruals will be 3% of sales. Wages Payable will be 4% of sales. The company is expected to pay 7% per year on its short-term debt and 9% per year on its longterm debt. The interest expense on the short-term debt in 2017 is calculated as interest rate on shortterm debt * amount of short-term debt outstanding at the end of 2016. The interest expense on the long-term debt is calculated as interest rate on long-term debt * amount of long-term debt outstanding at the end of 2016. The company will earn 4% on its short-term investments. Therefore, next interest expense equals short-term interest expense + long term interest expense - interest earned on short-term investments. The company's tax rate is 30%. Based on the information provided you are to: a) Calculate the amount of Additional Funds Needed in 2017. b) Complete the pro-forma income statement and balance sheet for 2017. AFF 210 Page 7 of AFF 210 ASSIGNMENT ONE SUMMER 2017 PROBLEM FIVE On December 31st, 2010 you decided to buy 3 Government of Canada bonds. There were 3 bonds available from the Government of Canada. On December 31st, 2010 the yield to maturity on Government of Canada bonds was 3.5% per year. (The term structure of interest rates was flat.) Bond A had a face value of $10,000. The coupon rate on the bond was 5%. Coupons were paid semi-annually. The bond had 20 years to maturity Bond B was a perpetual bond, with a face value of $50,000. The bond had a coupon rate of 6% and coupons were paid semi-annually. Bond C was a zero coupon bond with a face value of $100,000. The bond had 50 years to maturity. a) What was the price of each of the bonds on December 31st, 2010? b) After holding the bond for 6 years you decided to sell the bond on December 31st, 2016. Prior to selling any of the bonds you received the December 31st, 2016 coupon payment. The yield to maturity on the bonds, did not change from December 31st, 2010 to December 30th, 2016. However, just prior to you selling your bonds, on December 31st, 2016 the yield to maturity on Government of Canada bonds unexpectedly changed to 4.5% per year. (The term structure of interest rates remained flat.) How much did you sell the bonds for on December 31st, 2016? c) What was the effective annual rate of return that you earned on each of the bonds during the 6 years? AFF 210 Page 8 of