CASE MATERIAL The audit of Almax Company and its subsidiaries for the year ended June 30, 2018 was completed and the trial balance on the attached spreadsheet was prepared. Ruster is a domestic subsidiary and Voiles is a foreign subsidiary located in Montreal, Canada. Voiles cor ducts all of its transactions in Canadian Dollars, which constitute its functional currency. (1) Almax Company acquired 400,000 shares of Ruster Company common stock for $34,000,000 on 7/1/15. Any excess of purchase price over underlying book value was attributed 50% to property, plant and equipment with a 10-year life and 50% to goodwill. (2) Almax Company acquired all of Voiles Company's 800,000 shares of stock on 7/1/15 for $65,000,000. Any excess of purchase price over underlying book value was attributed 50% to property, plant and equipment with a 10-year life and 50% to goodwill. (3) Condensed balance sheets of Ruster and Voiles on 7/1/15 and 7/1/17 in U.S. dollars were as follows. Ruster Company 7/1/17 7/1/15 38,500,000 35,500,000 51,300,000 47,500,000 Voiles Company 7/1/17 7/1/15 20,500,000 28,000,000 62,300,800 61,300,000 Current Assets Property & Equipment 10,500,000 9,500,000 Current Liabilities Bonds Payable Common Stock ($50 par) Retained Earnings 12,500,000 20,000,000 25,000,000 20,000,000 10,000,000 20,000,000 25,000,000 15,000,000 40,000,000 12,500,000 40,000,000 20,000,000 (4) Total dividends declared and paid during the 2017-2018 fiscal year were as follows: Almax Company Ruster Company Voiles Company 2,400,000 2,500,000 800,000 (Pd 10/24/17 - US Dollars) In addition to dividend payments, Almax Company and Ruster Company each declared dividends of $1 per share payable in July 2017. (5) On December 31, 2017 Almax sold equipment with a book value of $800,000 to Ruster for $1,000,000. Ruster depreciates equipment on the straight-line method over 10 years. (6) Almax Company consistently sells to its subsidiaries at prices that realize a gross profit of 25% on sales. Ruster and Voiles companies sell to each other and to Almax Company at cost. Prior to fiscal 2016 intercompany sales were negligible, but during 2017-2018 the following sales were made in US Dollars). Total Sales Included on Purchasers Inventory at 6/30/18 Almax Company to Ruster Company Almax Company to Voiles Company Ruster Company to Voiles Company Ruster Company to Almax Company 17,200,000 16,000,000 2,500,000 2,800,000 2,000,000 4,000,000 500,000 800,000 (7) At 6/30/18 Almax Company owed Ruster Company Ruster Company owned Voiles Company Voiles Company owed Almax Company 2,400,000 1,600,000 1,200,000 REQUIRED: Prepare excel spreadsheets for the translation of the foreign subsidiary to U.S. dollars and the consolidation of all three companies. Your spreadsheets should make extensive use of formulas which automatically make calculations. Supporting schedules (such as intercompany profit schedules and purchase price allocations) should be shown on your spreadsheets and automatically carried into your solution Suggestion: Wher doing the consolidation do it in financial statement format not trial balance format. Almax Company and Subsidiaries Trial Balances June 30, 2018 Almax Ruter Voiles Company (U.S. $) Company ($ U.S.) Company ($ Canadian) Cash 14,150,000 14,670,000 10,400,000 13,812,000 Accounts Receivable Dividends Receivable 16,300,000 18,300,000 Inventories Investment in Ruster Investment in Voiles 88,500,000 (51,115,000) (10,230,000) Investment in 6% Ruster Bonds Property Plant & Equipment Accumulated Depreciation Accounts Payable Dividends Payable Bonds Payable Capital Stock ($50 Par) Dividends Retained Earnings (7/1/17) Sales Gain on Sale of Equipment Interest Income Dividend Income Cost of Goods Sold Operating Expenses Interest Expense 8,800,000 10,600,000 400,000 19,750,000 34,000,000 65,000,000 15,000,000 63,900,000 (40,200,000) (20,800,000) (1,200,000) (55,000,000) (60,000,000) 3,600,000 (31,420,000) (294,850,000) (200,000) (900,000) (3,200,000) 250,000,000 41,100,000 1,620,000 56,600,000 (22,500,000) (10,640,000) (500,000) (20,000,000) (25,000,000) 3,000,000 (20,000,000) (155,000,000) (50,000,000) 1,000,000 (16,667,000) (230,000,000) 119, 160,000 28,000,000 189,000,000 37,000,000 1,760,000 CASE MATERIAL The audit of Almax Company and its subsidiaries for the year ended June 30, 2018 was completed and the trial balance on the attached spreadsheet was prepared. Ruster is a domestic subsidiary and Voiles is a foreign subsidiary located in Montreal, Canada. Voiles cor ducts all of its transactions in Canadian Dollars, which constitute its functional currency. (1) Almax Company acquired 400,000 shares of Ruster Company common stock for $34,000,000 on 7/1/15. Any excess of purchase price over underlying book value was attributed 50% to property, plant and equipment with a 10-year life and 50% to goodwill. (2) Almax Company acquired all of Voiles Company's 800,000 shares of stock on 7/1/15 for $65,000,000. Any excess of purchase price over underlying book value was attributed 50% to property, plant and equipment with a 10-year life and 50% to goodwill. (3) Condensed balance sheets of Ruster and Voiles on 7/1/15 and 7/1/17 in U.S. dollars were as follows. Ruster Company 7/1/17 7/1/15 38,500,000 35,500,000 51,300,000 47,500,000 Voiles Company 7/1/17 7/1/15 20,500,000 28,000,000 62,300,800 61,300,000 Current Assets Property & Equipment 10,500,000 9,500,000 Current Liabilities Bonds Payable Common Stock ($50 par) Retained Earnings 12,500,000 20,000,000 25,000,000 20,000,000 10,000,000 20,000,000 25,000,000 15,000,000 40,000,000 12,500,000 40,000,000 20,000,000 (4) Total dividends declared and paid during the 2017-2018 fiscal year were as follows: Almax Company Ruster Company Voiles Company 2,400,000 2,500,000 800,000 (Pd 10/24/17 - US Dollars) In addition to dividend payments, Almax Company and Ruster Company each declared dividends of $1 per share payable in July 2017. (5) On December 31, 2017 Almax sold equipment with a book value of $800,000 to Ruster for $1,000,000. Ruster depreciates equipment on the straight-line method over 10 years. (6) Almax Company consistently sells to its subsidiaries at prices that realize a gross profit of 25% on sales. Ruster and Voiles companies sell to each other and to Almax Company at cost. Prior to fiscal 2016 intercompany sales were negligible, but during 2017-2018 the following sales were made in US Dollars). Total Sales Included on Purchasers Inventory at 6/30/18 Almax Company to Ruster Company Almax Company to Voiles Company Ruster Company to Voiles Company Ruster Company to Almax Company 17,200,000 16,000,000 2,500,000 2,800,000 2,000,000 4,000,000 500,000 800,000 (7) At 6/30/18 Almax Company owed Ruster Company Ruster Company owned Voiles Company Voiles Company owed Almax Company 2,400,000 1,600,000 1,200,000 REQUIRED: Prepare excel spreadsheets for the translation of the foreign subsidiary to U.S. dollars and the consolidation of all three companies. Your spreadsheets should make extensive use of formulas which automatically make calculations. Supporting schedules (such as intercompany profit schedules and purchase price allocations) should be shown on your spreadsheets and automatically carried into your solution Suggestion: Wher doing the consolidation do it in financial statement format not trial balance format. Almax Company and Subsidiaries Trial Balances June 30, 2018 Almax Ruter Voiles Company (U.S. $) Company ($ U.S.) Company ($ Canadian) Cash 14,150,000 14,670,000 10,400,000 13,812,000 Accounts Receivable Dividends Receivable 16,300,000 18,300,000 Inventories Investment in Ruster Investment in Voiles 88,500,000 (51,115,000) (10,230,000) Investment in 6% Ruster Bonds Property Plant & Equipment Accumulated Depreciation Accounts Payable Dividends Payable Bonds Payable Capital Stock ($50 Par) Dividends Retained Earnings (7/1/17) Sales Gain on Sale of Equipment Interest Income Dividend Income Cost of Goods Sold Operating Expenses Interest Expense 8,800,000 10,600,000 400,000 19,750,000 34,000,000 65,000,000 15,000,000 63,900,000 (40,200,000) (20,800,000) (1,200,000) (55,000,000) (60,000,000) 3,600,000 (31,420,000) (294,850,000) (200,000) (900,000) (3,200,000) 250,000,000 41,100,000 1,620,000 56,600,000 (22,500,000) (10,640,000) (500,000) (20,000,000) (25,000,000) 3,000,000 (20,000,000) (155,000,000) (50,000,000) 1,000,000 (16,667,000) (230,000,000) 119, 160,000 28,000,000 189,000,000 37,000,000 1,760,000