I need help with the final report the guidelines are all there and highlighted in yellow. Thanks

ACC 690: Final Project Guidelines and Rubric: Overview The final project for this course is the creation of an Excel spreadsheet model that shows the consolidation worksheet, intercompany elimination entries, other consolidation entries, and the final income statement and balance sheet for a sample parent and subsidiary company. The project is divided into three milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones will be sub mitted in Modules Four, Seven, and Nine. Assume the following when completing the project: Assume that the parent owned the subsidiary for the entire year for which financial statements are being prepared. The scenario indicates that as of December 31, there is a difference between book value and fair value for inventory and depreciable assets. Assume that these differences existed at the date of acquisition. Record only the differential and do not worry about amortization of the differential. Prepare the consolidation worksheet using the equity method. Assume that the trial balance was prepared prior to any entry the parent company made to record the net loss from the subsidiary. The Model Assignment: Guidelines Students will be given the description of a parent company and a subsidiary company along with the two firms' trial balances at book value as of Decem ber 31, 2012, the end of the year for both firms (see Company Information below). The financial data will be presented in English pounds () as local currency. Other data pertaining to the consolidation is also to be provided. The student will analyze the data for purpose of consolidation. The student will create a useful Excel model that shows the consolidation worksheet, intercompany elimination entries, other consolidation entries, and the final income statement and balance sheet. Using the consolidated financial statements created, students will then use Excel modeling to translate the consolidated income statement and balance sheet from English pounds to U.S. dollars based on exchange rates provided (the U.S. dollar is the functional currency). Requirements: This project should be prepared as a report for your supervisor. The report should be visually pleasing. As many computations as possible should be done by the model with the exception of entering the original financial statement data. The report should utilize \"macros\" and other built-in features found in Excel. Milestones Milestone One: Initial Model In Module Four, you will submit the initial Excel model for the report that shows the consolidation entries and the final income statement and balance sheet. This milestone will be graded using the Final Project Milestone One Rubric. Milestone Two: Model and Exchange Rates In Module Seven, you will translate the consolidated income statement and balance sheet from English pounds to U.S. dollars. This milestone will be graded using the Final Project Milestone Two Rubric. Milestone Three: Final Report In Module Nine, you will submit the final report as an attractive, polished artifact that includes all the main elements of the final product. The report should reflect the incorporation of feedback gained throughout the course. This milestone will be graded using the Final Report Rubric (below). Company Information Below you will find the trial balance for Parent Company and its wholly owned purchase, Subsidiary Company, as of December 31, 2012. The financial statements are denominated in British pounds. Other Important Information: Company Parent Company Subsidiary Company Accounts Debit Credit Debit Credit 1. Subsidiary Company's assets and liabilities are all shown at fair value except for: Cash 10,000 4,000 a. The fair value of Inventory is 32,000. Accounts Receivable 25,000 10,000 b. The fair value of Depreciable Assets is 370,000. Inventory 30,000 12,000 2. Subsidiary company sold Parent Company an item that is in ParShort-Term Investments 40,000 6,000 ent Company's inventory for 10,000 and cost Subsidiary ComPrepaid Assets 35,000 12,000 pany 5,000. The sale was made to Parent Company on credit, Investment in Subsidiary 290,000 and no payment has been made. Long-Term Notes Receivable 150,000 14,000 3. On December 27, 2012, Parent Company made a long-term loan Debt Service Fund 50,000 to Subsidiary Company in the amount of 100,000. Depreciable Assets 900,000 350,000 4. Subsidiary Company paid Parent Company 7,000 for Consulting Services. Subsidiary Company considers this an Administrative Accumulated Depreciation 200,000 50,000 Expense, and Parent Company considers it Sales Revenue. Intangible Assets 45,000 20,000 5. Exchange rates are: Current Liabilities 92,000 44,000 March 31, 2012, Exchange Rate: 1 = $1.24 Long-Term Notes Payable 225,000 119,000 Average Rate for 2012: 1 = $1.22 Common Stock 400,000 200,000 December 31, 2012, Exchange Rate: 1 = $1.20 Retained Earnings 482,000 50,000 Sales Revenue 750,000 245,000 Cost of Goods Sold 330,000 160,000 Selling Expenses 100,000 45,000 Administrative Expenses 120,000 70,000 Interest Expenses 24,000 5,000 Final Report Rubric Requirements of Submission: The project must be in Excel format, using as many calculations in Excel as possible. Critical Elements Analysis of Data Accuracy of Model Effective Use of Excel (Macros, Formulas) Report Design and Layout Comments: Exemplary Well-developed, accurate, and effective analysis of the data for purpose and consolidation The report includes useful, clear models with no errors or omissions Proficient Accurate and effective analysis of the data for purpose and consolidation The report includes useful, clear models with a few minor errors or omissions Needs Improvement Accurate analysis of the data for purpose and consolidation All items that can be computed are computed with Excel Most items that can be computed are computed with Excel Design and layout are generally professional and visually appealing Some items that can be computed are computed with Excel Design and layout are somewhat professional and visually acceptable Design and layout are professional, visually striking, clear, and uncluttered The report includes models with several errors or omissions Not Evident The report does not include an accurate analysis of the data for purpose and consolidation The report contains multiple serious errors or omissions Few items that can be computed are computed with Excel Design and layout are unprofessional Earned Total Value 35 35 15 15 100% Report on Consolidation Statement Companies consolidate financial statements as a result of their controlling various subsidiary companies. Companies obtain controlling interests in other companies by acquisitions and purchases. The acquired or purchased companies may maintain their separate legal entity status afterward, if they are not merged into the acquiring companies. But such a business combination often creates the need for a parent company to add financial statements of subsidiaries to its own original financial statements to better reflect the new business reality. Accounting Requirements Companies consolidate financial statements to meet both business needs and accounting requirements. Accounting rules require that a parent company holding more than half of the ownership interest in a subsidiary company report the financial results of the subsidiary company on its own financial statements. Consolidated financial statements provide a comprehensive overview of all the business operations that a parent company is involved in and has control over. A parent company may break out its consolidated financial statements by subsidiaries, which nonetheless is not required if the parent owns 100 percent of a subsidiary. Consolidated Statements Consolidated financial statements combine the assets, liabilities, revenues and expenses of a parent company with those of its subsidiaries. While a parent company's assets reflect its investments in subsidiary companies, plus any other assets that the parent company may hold, the financial statements of the parent company alone do not show assets that subsidiary companies own by means of debt financing besides using parent company investments. Therefore, without consolidating financial results with those of subsidiaries, a parent company may understate its total assets and thus operations. Analysis of the Given Scenario As the functional currency is US ($), thus translating the consolidated income statement and consolidated balance sheet into US pounds. All the assets were converted at closing rate of currency and liabilities were also converted at the date of transaction. Shareholder's equity was converted at the opening rate and retained earning's opening balance was converted at historical rate. When it comes to analyse the financial statements, the consolidated financial statements are prepared for both the parent and the subsidiary company. The parent company is showing a net consolidated income of 141,000 ($17,020); while the Subsidiary company is showing a loss of 35,000. This loss is because of the lower sales and higher expenses. Statement of retained earnings is showing that the Parent company has an ending balance of 623,000 ($769,700); while the Subsidiary company has only 15,000 ending balance. This is for the reason that the Subsidiary Company has suffered a loss of 35,000 that has declined the ending balance of retained earnings for the Subsidiary company. Finally, the balance sheet is showing the total assets of worth 1,340,000 for the Parent Company and 378,000 for the Subsidiary company. There were adjustments of 470,000 and the consolidated balance of total assets is 1,393,000 ($1,671,600). Similarly, total liabilities for Parent Company are 370,000 ($444,000). The total shareholder equity for the Parent Company is 1,023,000 ($1,227,600). Converting pounds into US dollars result in loss on foreign exchange which has been adjusted with the shareholder's equity. Both the assets and liability and shareholder equity are showing equal balance