Please answer all parts - deals with amortization and other intermediate accounting concepts

A 1 B C E F G 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 H I J K L M N O P Q R Accounting 321, Excel Worksheet Kimball Corporation Worksheet December 31, 2015 2 Name: Accounts Cash Accounts receivable Allowance for uncollectibles Supplies Prepaid insurance Inventory Investments Property, Plant, & Equipment Accumulated depreciation Accounts payable Accrued liabilities Income tax payable Dividends payable Interest payable Notes payable Mortgage payable Bonds payable Premium on bonds payable Preferred stock, 5%, $10 par Paid in excess of par, preferred Common stock, $1 par Paid in excess of par, common Retained earnings Dividends preferred Dividends common Sales revenue Sales returns Sales discounts Cost of goods sold Depreciation expense, PP & E Insurance expense Supplies expense Bad debt expense Income tax expense Interest expense Salaries and wages expense Loss on discontinued operations Loss on sale of warehouse Totals Trial Balance 12/31/2015 Debit Credit 100,087 130,000 Adjusted Trial Balance 12/31/2015 Debit Credit 100,087 130,000 Adjustments 12/31/2015 Debit Credit 400 Post-closing Trial Balance 12/31/2015 Debit Credit 100,087 130,000 Closing Entries 12/31/2015 Debit Credit 400 6,600 164,000 3,097,026 2,795,480 400 6,600 164,000 3,097,026 2,795,480 6,600 164,000 3,097,026 2,795,480 483,411 12,430 483,411 12,430 124,000 531,851 250,000 9,357 1,140,000 1,710,000 220,000 25,000 954,884 124,000 531,851 250,000 9,357 1,140,000 1,710,000 220,000 25,000 954,884 483,411 12,430 124,000 531,851 250,000 9,357 1,140,000 1,710,000 220,000 25,000 954,884 - - 4,125,632 4,125,632 20,564 15,420 640,000 2,300 15,320 20,995 122,000 2,145,098 312,075 9,586,965 9,586,965 - - 20,564 15,420 640,000 2,300 15,320 20,995 122,000 2,145,098 312,075 9,586,965 9,586,965 4,125,632 - - 20,564 15,420 640,000 2,300 15,320 20,995 122,000 2,145,098 312,075 9,586,965 Instructions: Type your name into cell B4. Complete requirements 1 through 14 and submit your completed assignment to the dropbox before its due date and time. Adjustments: PLEASE ROUND ALL NUMBERS TO THE NEAREST WHOLE DOLLAR BEFORE ENTERING THEM INTO THE WORKSHEET. 1. Adjust for accrued salaries of $7,400. Credit accrued liailities in your adjustment. 2. Adjust for 2015 bad debt expense. Kimball uses the allowance method, and estimates an uncollectible of 3% of accounts receivable. 3. Record the year-end adjustment for supplies. The ending inventory of supplies is $6,100, all purchases of supplies during the year were immediately expensed. 4. Record the year-end adjustments to the insurance accounts. Prepaid insurance includes the remainder of a 24-month contract that was purchased on April 1, 2014. Hint: you are expensing 12 months for the current year. Start with the remaining cost divided by remaining months and go from there. 5. Property, plant and equipment includes the following depreciable assets: Buildings Equipment Furniture and Fixtures $ Cost 1,900,000 425,000 210,000 Life 25 10 7 $ Salvage 200,000 39,000 35,000 Income Statement 12/31/2015 Debit Credit Depreciation Method Straight Line Double Declining Balance Double Declining Balance Use this information to calculate and record depreciation expense on these assets assuming it is their sixth full year of use. 6. Kimball entered into a 15 year, $675,000 mortgage on September 1, 2011. The mortgage rate is 4%, with payments due annually on August 31, beginning 8/31/2012. Use the financial functions in the "formulas" tab to have the spreadsheet calculate the annual payment on this mortgage in cell C83. Fill in the first five years of the 4,125,632 20,564 15,420 640,000 2,300 15,320 20,995 122,000 2,145,098 312,075 9,586,965 Balance Sheet 12/31/2015 Debit Credit 100,087 130,000 400 6,600 164,000 3,097,026 2,795,480 483,411 12,430 124,000 531,851 250,000 9,357 1,140,000 1,710,000 220,000 25,000 954,884 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 A B C E F G H I J K L M amortization table (peach box) referencing the cell numbers of the input (blue) box. Use the information generated in the mortgage amortization schedule to determine the amount of accrued interest on the mortgage at 12/31/2015 and enter this amount into cells E113 and F113. Remember, you are accruing interest at December 31, 2015, the August 31, 2015 payment has already been recorded and is past history. Mortgage Input Table: Present value Interest rate n Payment (ordinary) Mortgage Amortization: Date Payment 9/1/2011 XXXXXXXXX 8/31/2012 8/31/2013 8/31/2014 8/31/2015 8/31/2016 675,000 0.04 15 Interest XXXXXXXXX Principle XXXXXXXXX N Remaining Liability 7. The note payable was signed on 3/1/2012 and is due 2/28/2016. Interest is payable annually beginning 2/28/2013. Use this information to calculate accrued interest on this note at December 31, 2015 and enter this value into cells E114 and F114 below. The note rate is 6%. 8. Kimball issued $250,000, 4%, 5 year bonds at a premium on August 1, 2014. The bonds pay interest semi-annually every January 31st and July 31st. The market rate of interest at the date of issue was 3%. Use this information and effective interest amortization to fill in the bond amortization table below. Use the amortization table to determine the December 31, 2015 interest accrual on these bonds. Enter the interest expense and interest payable into cells E115 and F115 below. Interest Date Payment Expense 8/1/2014 XXXXXXXX XXXXXXXXX 1/31/2015 7/31/2015 1/31/2016 7/31/2016 1/31/2017 7/31/2017 1/31/2018 7/31/2018 1/31/2019 7/31/2019 Accrued Interest Worksheet: Interest Expense Premium Amortization XXXXXXXXXX Carrying Value Interest Payable Mortgage Long-term note Bonds Total *Remember: interest expense and interest payable on bonds is not the same when there is a discount or premium, so your adjustment must also amortize the discount or premium. 9. Use the interest worksheet totals (E116 and F116) to enter the adjustment for accrued interest into the worksheet. 10. Kimball has 114,000 shares of 5%, $10 par preferred stock outstanding. On December 31, 2015 the company declared a $120,000 dividend to be shared between preferred and common shareholders. Preferred shareholders have a current dividend preferrence of 5% of par value, with the remainder of the $120,000 paid to common shareholders. Record the debits to dividends preferred and dividends common separately for the two classes of stock (you can make a single credit to dividends payable). 11. The income tax rate on all items is 34%. Record income tax expense and income tax payable as a single adjustment in the worksheet. 12. Close the nominal accounts directly to retained earnings. 13. Prepare a multistep income statement for Kimball Corporation for the year ended 12/31/2015 on sheet two of this worksheet (see the tabs at the bottom of the sheet) through earnings (loss) per share. There are 220,000 shares of common stock outstanding and there was a preferred dividend declared in the current period. The loss on the sale of the warehouse is not unusual, while the loss on discontinued operations should be reported separately net of tax. NOTE: the total of income tax expense from your worksheet above will be broken into two separate values in your income statement: income before tax and discontinued operations. Review pages 171 and 172 on intraperiod income tax allocation. 14. Prepare a classified balance sheet for Kimball Corporation as of 12/31/2015 on sheet three of this worksheet. Use good form on both financial statements. O P Q R Kimball Corporation Income Statement For the Year Ended December 31, 2015 Kimball Corporation Balance Sheet As of December 31, 2015