Posted: July 16th, 2021

Acc/690 final project | Accounting homework help

 

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 Five, Eight, and Ten.

 

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 the 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 task 5-1, 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 task 8-2, 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 3: Final Report

In task 10-4, 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 Expense

24,000

 

5,000

 

 

Other Important Information:

1.      Subsidiary Company’s 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 Company’s 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

Distinguished

Proficient

Emerging

Not Evident

Value

Analysis of Data

 

 

Well-developed, accurate, and effective analysis of the data for purpose and consolidation
(32-35)

Accurate and effective analysis of the data for purpose and consolidation


(28-31)

Accurate analysis of the data for purpose and consolidation

 

(25-27)

The report does not include an accurate analysis of the data for purpose and consolidation
(0-24)

35

Accuracy of Model

The report includes useful, clear models with no errors or omissions

(32-35)

The report includes useful, clear models with a few minor errors or omissions

(28-31)

The report includes models with several errors or omissions

(25-27)

The report contains multiple serious errors or omissions

 

(0-24)

35

Effective Use of Excel (Macros, Formulas)

All items that can be computed are computed with Excel

(14-15)

Most items that can be computed are computed with Excel

(12-13)

Some items that can be computed are computed with Excel

(11)

Few items that can be computed are computed with Excel

(0-10)

15

Report
Design and Layout

Design and layout are professional, visually striking, clear, and uncluttered

(14-15)

Design and layout are generally professional and visually appealing

(12-13)

Design and layout are somewhat professional and visually acceptable

 

(11)

Design and layout are unprofessional

 

(0-10)

15

Earned Total

Comments:

100%

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00