Please help me with this assignment in the excel file. Thank you!

Case/Simulation #3, Acct 3040, Spring 2016 Directions: 1) Reminder: THIS IS A CASE/SIMULATION ASSIGNMENT. DO NOT DISCUSS THIS ASSIGNMENT WITH ANYONE OTHER THAN DR.CHENG!! 2) Ace Corporation's debt instruments are described on each of the 5 separate "Debt" sheets. You are required to complete all 5 "Debt" sheets AND THEN summarize your analysis in the "Debt Summary" sheet in this workbook. Pay careful attention to the instructions on each sheet. 3) The 12/31/13 balance sheet and the income statement for the year-ended 12/31/13 provided for you on the "Balance Sheets & Income Stmt" sheet are correct in accordance with US GAAP and provide you with check figures for the 12/31/13 carrying value of debt and interest expense for the year ended 12/31/13. This sheet is protected so that you cannot make changes to it. You do not have any requirements on this sheet. 4) You must prepare a complete statement of cash flows for the year-ended 12/31/13 on the Stmt of Cash Flows sheet in this workbook. Instructions and additional information you need are included on this sheet in the workbook. 5) Your Excel file must be submitted through the Assignments function in Blackboard. Assignments submitted in any other way earn a score of zero. 6) This assignment is due by 11:00 pm on Wed. 3/9/16. Zero points for late submissions. 7) Name the project file you submit as follows: LastnameCS3. For example, my file name would be ChengCS3. Name: Enter Your Name Here! Debt 1 Requirement 1: Enter your name in cell B1. ACCOUNTING PERIOD DETAILS: Ace's fiscal year ends on December 31st every year. Ace prepares accrual adjusting entries semi-annually, on June 30th and Dec. 31st each year. Ace applies US GAAP for all of its debt instruments and does not use the fair value option. CONTRACT DETAILS FOR DEBT 1: Ace Corp. issued bonds with face value of $250,000 on July 1, 2009. These bonds mature on June 30, 2013 and have a stated interest rate of 8%. These bonds require semi-annual coupon payments on Dec. 31 and June 30 each year. Ace received $250,000 as original principal on 7/1/09 when these bonds were issued. Ace paid $8,000 of bond issue costs on 7/1/09 related to these bonds. Requirement 2: Fill in the boxes below for these bonds. Market (effective) interest rate for these bonds on 7/1/09: per period Semi-annual annuity payment amount required: Requirement 3: Prepare the entries that Ace would have prepared for these bonds on the dates below. If no entry is required, state so. Don't forget to account for bond issue costs. Date 6/30/2013 Account Debit Credit 12/31/2013 Requirement 4: Go to the Debt Summary sheet in this workbook and complete the cells for Debt 1. Debt 2 Enter Your Name Here! ACCOUNTING PERIOD DETAILS: Ace's fiscal year ends on December 31st every year. Ace prepares accrual adjusting entries semi-annually, on June 30th and Dec. 31st each year. Ace applies US GAAP for all of its debt instruments and does not use the fair value option. CONTRACT DETAILS FOR DEBT 2: Ace Corp. issued bonds with face value of $500,000 on January 1, 2013. These bonds mature on December 31, 2018 and have a stated interest rate of 10%. These bonds require semi-annual coupon payments on June 30 and Dec. 31 each year. The market interest rate for these bonds on 1/1/13 was 8.9%. Ace paid $24,000 of bond issue costs on 1/1/13 related to these bonds. Requirement 1: Fill in the boxes below for these bonds. Face value = Semi-annual annuity payment amount required = Number of periods (n) = Market interest rate per period = Cash proceeds (original principal) borrowed on 1/1/13 = Requirement 2: Prepare the entries that Ace would have prepared for these bonds on the dates below. If no entry is required, state so. Don't forget to account for bond issue costs. Date 1/1/2013 Account Debit Credit 6/30/2013 12/31/2013 Requirement 3: Go to the Debt Summary sheet in this workbook and complete the cells for Debt 2. Debt 3 Enter Your Name Here! ACCOUNTING PERIOD DETAILS: Ace's fiscal year ends on December 31st every year. Ace prepares accrual adjusting entries semi-annually, on June 30th and Dec. 31st each year. Ace applies US GAAP for all of its debt instruments and does not use the fair value option. CONTRACT DETAILS FOR DEBT 3: Ace Corp. issued bonds with face value of $300,000 on July 1, 2013. These bonds mature on June 30, 2016 and have a stated interest rate of 4%. These bonds require semi-annual coupon payments on Dec. 31 and June 30 each year. Ace received $261,316 as original principal on 7/1/13 when these bonds were issued. Ace's bond issue costs were immaterial for these bonds. Requirement 1: Fill in the boxes below for these bonds. Face value = Semi-annual annuity payment amount required = Number of periods (n) = Market interest rate per period = Cash proceeds (original principal) borrowed on 7/1/13 = Requirement 2: Prepare the entries that Ace would have prepared for these bonds on the dates below. If no entry is required, state so. Date 7/1/2013 Account Debit Credit 12/31/2013 Requirement 3: Go to the Debt Summary sheet in this workbook and complete the cells for Debt 3. Debt 4 Enter Your Name Here! ACCOUNTING PERIOD DETAILS: Ace's fiscal year ends on December 31st every year. Ace prepares accrual adjusting entries semi-annually, on June 30th and Dec. 31st each year. Ace applies US GAAP for all of its debt instruments and does not use the fair value option. CONTRACT DETAILS FOR DEBT 4: On 1/1/13, Ace decided to purchase equipment with a fair market value of $350,000. Ace financed this purchase with the vendor by issuing a loan payable. The loan payable has a face value of $492,243 because that's the amount that Ace is required to pay the vendor on the maturity date of December 31, 2016. No other payments are required on this loan. Requirement 1: Fill in the boxes below for these bonds. Annual annuity payment amount required = ZERO Number of periods (n) = (NOTE: Even though Ace prepares semi-annual AJEs, this loan requires annual compounding.) Market interest rate per period = Original carrying value of this NON-CASH LOAN = Requirement 2: Prepare the entries that Ace would have prepared for this loan on the dates below. If no entry is required, state so. You must ignore depreciation AJEs for the equipment purchased by this loan. Date 1/1/2013 Account Debit Credit 6/30/2013 12/31/2013 Requirement 3: Go to the Debt Summary sheet in this workbook and complete the cells for Debt 4. Debt 5 Enter Your Name Here! ACCOUNTING PERIOD DETAILS: Ace's fiscal year ends on December 31st every year. Ace prepares accrual adjusting entries semi-annually, on June 30th and Dec. 31st each year. Ace applies US GAAP for all of its debt instruments and does not use the fair value option. CONTRACT DETAILS FOR DEBT 5: On 1/1/13, Ace decided to purchase equipment by issuing an installment loan directly to the equipment vendor (NON-CASH LOAN). This loan has a face value of $1,115,966, a stated interest rate of 5%, and a maturity date of 12/31/17. Based on these contract terms, the annual payment due each 12/31 is $257,760 Upon further investigation, you have determined that the appropriate market interest rate for this loan is 9.1% on 1/1/13. REMEMBER: You cannot change the contractual terms of this loan, but you need to properly account for the SUBSTANCE of this loan. Requirement 1: Fill in the boxes below for these bonds. Lump-sum payment due on the maturity date = (Remember that this is a regular installment loan.) Annual annuity payment amount required = (Remember that this is based on the contract terms.) Number of periods (n) = (NOTE: Even though Ace prepares semi-annual AJEs, this loan requires annual compounding.) Market interest rate per period = ZERO $257,760 Original carrying value of this NON-CASH LOAN = Requirement 2: Prepare the entries that Ace would have prepared for this loan on the dates below. If no entry is required, state so. You must ignore depreciation AJEs for the equipment purchased by this loan. Date 1/1/2013 Account Debit Credit 6/30/2013 12/31/2013 Requirement 3: Go to the Debt Summary sheet in this workbook and complete the cells for Debt 5. Name: Debt Summary Sheet Enter Your Name Here! Each of the 5 Debt sheets that you have completed in this workbook requires you to complete cells in this worksheet. Note that you must complete columns C through H for each of the 5 debt instruments. Also note that the 12/31/13 Bonds and Loans Payable amount in cell C17 needs to agree with the carrying amount reported on the 12/31/13 balance sheet and the total interest expense amount in cell D17 needs to agree with the interest expense in the income statement for the year ended 12/31/13. Debt Instrument Debt 1 Debt 2 Debt 3 Debt 4 Debt 5 Totals CHECK FIGURES: Bonds and Loans Bonds and Loans Interest expense Payable 12/31/12 Payable 12/31/13 recognized Carrying Value Carrying Value during 2013 Cash borrowed during 2013 Cash paid for interest during 2013 Cash paid for principal during 2013 Non-cash interest expense recognized during 2013 250,000 0 0 0 0 250,000 2,003,279 190,575 36,909 Balance Sheets & Income Stmt NOTE: You do not have any requirements on this sheet. You will use these statements to help you prepare the Statement of Cash Flows for the year ended 12/31/13. Your 12/31/13 carrying value of debt instruments and your interest expense for the year ended 12/31/13 should agree with the numbers in these financial statements. Ace Corporation Balance Sheets Cash Accounts receivable Merchandise inventory Office supplies Prepaid bond issue costs Property, plant, and equipment, net Patent Totals Accounts payable Rent payable Income taxes payable Bonds and loans payable Common stock ($1000 par per share) Additional paid-in capital Retained earnings Treasury stock Totals Ace Corporation Income Statement For the Year Ended 12/31/13 12/31/2013 755,704 375,000 665,000 24,000 20,000 2,270,000 550,000 4,659,704 12/31/2012 442,000 45,000 485,000 21,000 1,000 1,215,000 600,000 2,809,000 52,000 5,000 100,000 2,003,279 400,000 900,000 1,309,425 (110,000) 4,659,704 92,000 8,000 27,000 250,000 400,000 900,000 1,132,000 0 2,809,000 Sales revenue Cost of goods sold Gross profit Operating expenses: Salaries expense Rent expense Supplies expense Patent amortization Depreciation Total operating expenses Operating income Other income (loss): Interest revenue (expense) Bond issue cost (expense) Gains (losses) on sales of equipment Other income (loss), net Income before income taxes Provision for income taxes Net Income 4,280,000 2,670,000 1,610,000 270,000 5,000 69,000 50,000 210,000 604,000 1,006,000 (190,575) (5,000) (30,000) (225,575) 780,425 200,000 580,425 Statement of Cash Flows Enter Your Name Here! Requirement: Complete the 2013 Statement of Cash Flows using the direct method for Operating Cash Flows. Don't forget any required disclosures! Use the comparative balance sheets and 2013 income statement provided along with additional information provided in Column E of this worksheet. You must include the appropriate descriptive language in Column A for the items you include in each section of the cash flows statement. ADDITIONAL INFORMATION: 1) Ace paid $65,000 to purchase equipment. 2) Ace sold equipment with an original cost of $440,000 and accumulated depreciation of $290,000 for $120,000 cash. 3) Ace declared and paid cash dividends. Ace Corporation Statement of Cash Flows For the Year Ended 12/31/13 You must determine the dollar amount. 4) Ace purchased treasury stock for $110,000. Cash flows from operating activities Net cash provided (used) by operating activities Cash flows from investing activities Net cash provided (used) by investing activities Cash flows from financing activities Net cash provided (used) by financing activities Net increase (decrease) in cash Cash, January 1, 2013 Cash, December 31, 2013 Reconciliation of Net Income to Net Operating Cash Flows: Supplemental Schedule of Noncash Investing and Financing Activities: 442,000 755,704