Overview The final project for this course is the creation

| June 5, 2016

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

Final Project Milestone One: Initial Model

For Milestone One, submit the initial Excel model for the report that shows the consolidation of entries and the final income statement and balance sheet. The milestone will be graded using the rubric below.

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

Accuracy of Entries, Income Statement, and Balance Sheet

The model is accurate, with no serious errors or omissions

(63-70 points)

The model is generally accurate, but contains a few minor errors

(56-62 points)

The model is fairly accurate, but contains several significant errors

(49-55 points)

The model contains multiple serious errors or omissions

(0-48 points)

70

Effective Use of Excel (macros, formulas)

Most items that can be computed are computed with Excel

(27-30 points)

Many itemsthat can be computed are computed with Excel

(24-26 points)

Some itemsthat can be computed are computed with Excel

(21-23 points)

Few itemsthat can be computed are computed with Excel

(0-20 points)

30

Earned Total

Comments:

100%

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

Order your essay today and save 30% with the discount code: ESSAYHELPOrder Now