Need to prepare consolidation worksheet ( consolidated trial balance) for the year ended 30th june 2018.

I have attached the question

Thanks in advance

Allteck Building Ltd (\"Allteck\") build luxury homes. In an attempt to vertically integrate their business, they acquired all of the shares of Landfry Ltd (\"Landfry\") on 1 July 2016 on an ex-dividend basis. Due to a downturn in the property market, Landfry made a loss in the prior year for the first time. Legal and accounting fees related to the acquisition totalled $11000. On 1 July 2016, Landfry had the following in its balance sheet; Share capital General Reserve Retained earnings/ (losses) Dividends Payable Plant Trademark Land inventory Building premises Goodwill $100,000 $750,000 ($135,000) $10,000 $275,000 (carrying amount of $85,000) $50,000 $2,500,000 $600,000 (carrying amount of $120,000) $20,000 Fair value of $65,000 Fair value of $60,000 Fair value of $2,900,000 Fair value of $850,000 Fair value of Nil 2016/2017 impairment testing reduces the carrying value of goodwill to $10,000. The building premises and plant are being depreciated on a straight-line basis over 20 years and 10 years respectively. The building was acquired on 1 Jan 2001 and the plant has a further 3 years of depreciation remaining. The trademark is considered to have an indefinitely life. Landfry currently has a legal dispute with a customer who claims that they were not advised of problems with the title of some land. They are suing Landfry for $350,000 and the directors believed there was a 35% chance of success at that stage. This was settled on 15/5/18 for $80000. All of the inventory was sold in the 2016 year. Additional information a) The business turned around and on 1/3/18, Landfry was able to pay a $30,000 dividend. Shareholder approval is not required in relation to payment of dividends. b) On 1/1/17, a parcel of land costing Landfry $450,000 on 30 Nov 2016 was sold to Allteck for $620,000 to build a display home. It is not expected that this land will be sold in the immediate future. c) On 30/06/17, Allteck sold a motor vehicle to Landfry for $50,000 for use by one its staff. The vehicle cost Allteck $80,000 in Jan 2017 and it had a carrying value of $65,000 at the date of sale. The M/V was being depreciated on a straight line basis over 5 years. d) During the 2017/2018 year, Landfry began selling some of the land acquired in Jan 2018 to Allteck to include as a home and land package. There were two blocks sold during the year, which cost Landfry $300,000 and $380,000. They were sold to Allteck at a 15% margin. Only the first block was sold before 30 June 2018 with Allteck recording the other block as part of their work in progress inventory. As at 30 June 2018, Allteck have not yet paid Landfry for the $300,000 block and it is expected to be paid in July 2018. e) Landfry was running low on cash and borrowed $500,000 from Allteck on 1/3/18. Interest of 5% payable monthly was charged and paid. The loan was partly paid back on 1/5/18 when Landfry issued $50,000 worth of additional shares to Allteck. f) The tax rate is 30% The trial balances of both companies as at 30 June 2018 is as follows; The trial balances of both companies as at 30 June 2018 is as follows; Required Using Excel, prepare the consolidation worksheet for the year ended 30 th June 2018. You are not required to complete the consolidated financial statements so your final result should be a consolidated trial balance. Please Include narrations and details of all of your journals and excel formulae (refer to submission guidelines)