90.2K

Verified Solution

Link Copied!

Looking for answer Milestone 1-3

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 submitted 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.

Guidelines

The Model Assignment:

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 December 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.

Company Parent Company Subsidiary Company Accounts Debit Credit Debit Credit Cash 10,000 4,000 Accounts Receivable 25,000 10,000 Inventory 30,000 12,000 Short-Term Investments 40,000 6,000 Prepaid Assets 35,000 12,000 Investment in Subsidiary 290,000 Long-Term Notes Receivable 150,000 14,000 Debt Service Fund 50,000 Depreciable Assets 900,000 350,000 Accumulated Depreciation 200,000 50,000 Intangible Assets 45,000 20,000 Current Liabilities 92,000 44,000 Long-Term Notes Payable 225,000 119,000 Common Stock 400,000 200,000 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

Other Important Information: 1.Subsidiary Companys assets and liabilities are all shown at fair value except for: a.The fair value of Inventory is 32,000. b.The fair value of Depreciable Assets is 370,000. 2.Subsidiary company sold Parent Company an item that is in Parent Companys inventory for 10,000 and cost Subsidiary Company 5,000. The sale was made to Parent Company on credit, and no payment has been made. 3.On December 27, 2012, Parent Company made a long-term loan to Subsidiary Company in the amount of 100,000. 4.Subsidiary Company paid Parent Company 7,000 for Consulting Services. Subsidiary Company considers this an Administrative Expense, and Parent Company considers it Sales Revenue. 5.Exchange rates are: March 31, 2012, Exchange Rate: 1 = $1.24 Average Rate for 2012: 1 = $1.22 December 31, 2012, Exchange Rate: 1 = $1.20

Final Report Rubric

Requirements of Submission: The project must be in Excel format, using as many calculations in Excel as possible.

Critical Elements	Exemplary	Proficient	Needs Improvement	Not Evident	Value
Analysis of Data	Well-developed, accurate, and effective analysis of the data for purpose and consolidation	Accurate and effective analysis of the data for purpose and consolidation	Accurate analysis of the data for purpose and consolidation	The report does not include an accurate analysis of the data for purpose and consolidation	35
Accuracy of Model	The report includes useful, clear models with no errors or omissions	The report includes useful, clear models with a few minor errors or omissions	The report includes models with several errors or omissions	The report contains multiple serious errors or omissions	35
Effective Use of Excel (Macros, Formulas)	All items that can be computed are computed with Excel	Most items that can be computed are computed with Excel	Some items that can be computed are computed with Excel	Few items that can be computed are computed with Excel	15
Report Design and Layout	Design and layout are professional, visually striking, clear, and uncluttered	Design and layout are generally professional and visually appealing	Design and layout are somewhat professional and visually acceptable	Design and layout are unprofessional	15
Earned Total Comments:					100%

Answer & Explanation Solved by verified expert

See Answer