Please answer all questions on a Microsoft Excel spreadsheet ONLY and show calculations.

Prepare the corresponding consolidating worksheets based on the following facts. 75% $ 5,700,000 Companies involved: Parent and Sub Date of acquisition: Jan. 1, 20X3 Date of the worksheets you must prepare: December 31, 20X4 20X5 20X6 Percentage of common shares of S owned by P Price paid by P At the time of acquisition these were some values concerning S: Common stock Aditional paid-in capital Retained earnings Years Inventories Other current assets Plant & equipment, remaining life: Land Long term liabilities, remaining life: 5 Intangibles amortization: $ 950,000 $ 950,000 $ 627,000 FMV-BV $ 760,000 $ 285,000 $ 1,900,000 $ 1,330,000 $ 285,000 st 8 6 Intercompany transactions: 20X3 $ Upstream inventory sales % in ending inventory Gross profit rate on sales. 20X4 142,500 $ 7.50% 37.50%; 20X5 121,125 $ 9.00% 45.00%; 20X6 129,604 10.80% 54.00%; Upstream building sale, Dec. 31 Sold for Book value at time of sale Remaining life: $ 1,900,000 760,000 10 % of S bonds purchased by P, Jan. Price paid BV at that date Remaining life: Maturity value of acquired bonds 55% $ 1,551,104 $ 1,534,930 $ 1,567,500 Required: 1. Complete the worksheets for 20X4-6 Reconciliation: Investment to S's Equity 20X3 5,607,683 Investment, 12/31 Removal of unamortized differential: Initial (570,000) (213,750) (1,425,000) (997,500) (213,750) (384,750) Amortiz. 570,000 213,750 178,125 Inventories Other current assets Plant & equipment Land L. T. liabilities Intangibles Unamortized differential 42,750 64,125 (1,246,875) (997,500) (171,000) (320,625) (2,736,000 2,871,683 S's stockholders' equity P's ownership interest Difference 3,828,911 75% 2,871,683 Reconciliation: Non-contr. Interest to S's Equity: 957,228 S's stockholders' equity X Non-contr. interest percentage Unrealized intercompany balances in RE-S Non-contr. interest balance at year end Unamortized differential Excess Difference 1,869,228 (912,000) 957,228 957,228 Issuer's Facts: Face value Stated Rate Effective Rate Interest Pmts. Bonds Life 2,850,000 6.00% 6.50% 171,000 10 Acquirer's Facts: Acquired Jan. % acquired Face value Stated Rate Effective Rate Interest Pmts. Bonds Life 20X5 55% 1,567,500 6.002 6.25% 94,050 5 At issuance: PV of Principal PV of Interest Bond price $1,518,269 $1,229,290 $2,747,559 At bond purchase PV of Principal PV of Interest Bond price $1,157,612 $393,493 $1,551,104 Issuing Company 6.50%. Payment Interest Exp. Purchasing Company 6.25% Collections Interest Rev. Date 1/1/20XO 12/31/X0 12/31/X1 12/31/X2 12/31/X3 12/31/X4 12/31/35 12/31/X6 12/31/X7 12/31/X8 12/31/X9 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 178,591 179,085 179,610 180,170 180,766 181,401 182,077 182,797 183,564 184,380 Carrying Value $2,747,559 2.755,151 2,763,235 2,771,846 2,781,016 2,790,782 2,801,182 2,812,259 2,824,056 2,836,620 2,850,000 Date 1/1985 12/31/35 12/31/X6 12/31/X7 12/31/X8 12/31/X9 94,050 94,050 94,050 94,050 94,050 96,944 97,125 97,317 97,521 97,738 Carrying Value $1,551,104 1,553,998 1,557,073 1,560,340 1,563,812 1,567,500 Parent Subsidiary Adjusts, and Elims. Cons. 9,500,000 28,500,000 home aumenn Sales Income from S Cost of goods sold Operating expenses Interest Expense Non-contr. interest income Net income 19,000,000 791,123 (13,300,000) (2,850,000) (5,415,000) (1,425,000) (180, 170) (791,123) (760,000) (665,000) (19,475,000) (4,940,000) (180,170) (263, 708) 3,641,123 (263,708) 3,641,123 2,479,830 6,650,000 6,650,000 (627,000) Astaas Earrings Retained earnings--P, 1/1/X3 Retained earnings--S, 1/1/X3 Net income Dividends Retained earnings--12/31/X3 3,641,123 (1,729,533) 8,561,589 627,000 2,479,830 (1,177,919) 1,928,911 1,177,919 3,641,123 (1,729,533) 8,561,589 760,000 1,900,000 2,280,000 6,653,906 5,607,683 4,750,000 100,000 387,971 931,131 853,537 775,943 Balance Shear Cash Accounts receivable Inventories Other current assets Investment in S Plant and equipment--net Land Intangibles Total assets 1,147,971 2,831, 131 3,133,537 7,429,849 2,948,582 1,862,262 (5,607,683) 1,662,500 1,330,000 826,085 9,361,082 3,292,262 826,085 28,021,918 22,051,589 7,759,426 1,900,000 190,000 (228,000) Accounts payable Notes Payable Bonds Payable, 10% Capital stock Additional paid in capital Retained earnings Non-contr. interest, 12/31/X3 Total liabilities and equity 1,045,000 104,500 1,250,690 950,000 950,000 1,928,911 9,500,000 1,900,000 8,561,589 (950,000) (950,000) 2,945,000 66,500 1,250,690 9,500,000 1,900,000 10,490,500 1,869,228 28,021,918 1,869,228 8,654,830 22,051,589 7,759,426 8,654,830 Proofs of balance 0 0 U 0 Prepare the corresponding consolidating worksheets based on the following facts. 75% $ 5,700,000 Companies involved: Parent and Sub Date of acquisition: Jan. 1, 20X3 Date of the worksheets you must prepare: December 31, 20X4 20X5 20X6 Percentage of common shares of S owned by P Price paid by P At the time of acquisition these were some values concerning S: Common stock Aditional paid-in capital Retained earnings Years Inventories Other current assets Plant & equipment, remaining life: Land Long term liabilities, remaining life: 5 Intangibles amortization: $ 950,000 $ 950,000 $ 627,000 FMV-BV $ 760,000 $ 285,000 $ 1,900,000 $ 1,330,000 $ 285,000 st 8 6 Intercompany transactions: 20X3 $ Upstream inventory sales % in ending inventory Gross profit rate on sales. 20X4 142,500 $ 7.50% 37.50%; 20X5 121,125 $ 9.00% 45.00%; 20X6 129,604 10.80% 54.00%; Upstream building sale, Dec. 31 Sold for Book value at time of sale Remaining life: $ 1,900,000 760,000 10 % of S bonds purchased by P, Jan. Price paid BV at that date Remaining life: Maturity value of acquired bonds 55% $ 1,551,104 $ 1,534,930 $ 1,567,500 Required: 1. Complete the worksheets for 20X4-6 Reconciliation: Investment to S's Equity 20X3 5,607,683 Investment, 12/31 Removal of unamortized differential: Initial (570,000) (213,750) (1,425,000) (997,500) (213,750) (384,750) Amortiz. 570,000 213,750 178,125 Inventories Other current assets Plant & equipment Land L. T. liabilities Intangibles Unamortized differential 42,750 64,125 (1,246,875) (997,500) (171,000) (320,625) (2,736,000 2,871,683 S's stockholders' equity P's ownership interest Difference 3,828,911 75% 2,871,683 Reconciliation: Non-contr. Interest to S's Equity: 957,228 S's stockholders' equity X Non-contr. interest percentage Unrealized intercompany balances in RE-S Non-contr. interest balance at year end Unamortized differential Excess Difference 1,869,228 (912,000) 957,228 957,228 Issuer's Facts: Face value Stated Rate Effective Rate Interest Pmts. Bonds Life 2,850,000 6.00% 6.50% 171,000 10 Acquirer's Facts: Acquired Jan. % acquired Face value Stated Rate Effective Rate Interest Pmts. Bonds Life 20X5 55% 1,567,500 6.002 6.25% 94,050 5 At issuance: PV of Principal PV of Interest Bond price $1,518,269 $1,229,290 $2,747,559 At bond purchase PV of Principal PV of Interest Bond price $1,157,612 $393,493 $1,551,104 Issuing Company 6.50%. Payment Interest Exp. Purchasing Company 6.25% Collections Interest Rev. Date 1/1/20XO 12/31/X0 12/31/X1 12/31/X2 12/31/X3 12/31/X4 12/31/35 12/31/X6 12/31/X7 12/31/X8 12/31/X9 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 171,000 178,591 179,085 179,610 180,170 180,766 181,401 182,077 182,797 183,564 184,380 Carrying Value $2,747,559 2.755,151 2,763,235 2,771,846 2,781,016 2,790,782 2,801,182 2,812,259 2,824,056 2,836,620 2,850,000 Date 1/1985 12/31/35 12/31/X6 12/31/X7 12/31/X8 12/31/X9 94,050 94,050 94,050 94,050 94,050 96,944 97,125 97,317 97,521 97,738 Carrying Value $1,551,104 1,553,998 1,557,073 1,560,340 1,563,812 1,567,500 Parent Subsidiary Adjusts, and Elims. Cons. 9,500,000 28,500,000 home aumenn Sales Income from S Cost of goods sold Operating expenses Interest Expense Non-contr. interest income Net income 19,000,000 791,123 (13,300,000) (2,850,000) (5,415,000) (1,425,000) (180, 170) (791,123) (760,000) (665,000) (19,475,000) (4,940,000) (180,170) (263, 708) 3,641,123 (263,708) 3,641,123 2,479,830 6,650,000 6,650,000 (627,000) Astaas Earrings Retained earnings--P, 1/1/X3 Retained earnings--S, 1/1/X3 Net income Dividends Retained earnings--12/31/X3 3,641,123 (1,729,533) 8,561,589 627,000 2,479,830 (1,177,919) 1,928,911 1,177,919 3,641,123 (1,729,533) 8,561,589 760,000 1,900,000 2,280,000 6,653,906 5,607,683 4,750,000 100,000 387,971 931,131 853,537 775,943 Balance Shear Cash Accounts receivable Inventories Other current assets Investment in S Plant and equipment--net Land Intangibles Total assets 1,147,971 2,831, 131 3,133,537 7,429,849 2,948,582 1,862,262 (5,607,683) 1,662,500 1,330,000 826,085 9,361,082 3,292,262 826,085 28,021,918 22,051,589 7,759,426 1,900,000 190,000 (228,000) Accounts payable Notes Payable Bonds Payable, 10% Capital stock Additional paid in capital Retained earnings Non-contr. interest, 12/31/X3 Total liabilities and equity 1,045,000 104,500 1,250,690 950,000 950,000 1,928,911 9,500,000 1,900,000 8,561,589 (950,000) (950,000) 2,945,000 66,500 1,250,690 9,500,000 1,900,000 10,490,500 1,869,228 28,021,918 1,869,228 8,654,830 22,051,589 7,759,426 8,654,830 Proofs of balance 0 0 U 0