Individual Assignment 3 Chapter 10 Excel And Analysis Case Parker And Suffolk Plc As 2825269

Individual Assignment #3 – Chapter 10 Excel and Analysis Case – Parker and Suffolk PLC

Assigned Week Five – Due Midnight on Sunday Week Seven 10/14

75 Points – Excel workbook

Students will complete the case Chapter 10 Excel and Analysis Case – Parker and Suffolk PLC,in good form, using Excel. The case includes Part I a-d, The case will be submitted as one workbook; enter part I a-d on separate worksheet tabs.

On January 1, 2016 Parker, Inc., a U.S.-based firm, acquired 100 percent of Suffolk PLC located in Great Britain for consideration paid of 52,000,000 British pounds ($), which was equal to fair value. The excess of fair value over book value is attributable to land (part of property, plant, and equipment and is not subject to depreciation. Parker accounts for its investment in Suffolk at cost. On January 01 2016.

2016, Suffolk reported the following balance sheet:


Cash. 2000000

Accounts receivable 3000000

Inventory . 14000000

Property, plant

and equipment (net) 40000000

total assets 59000000


Accounts payable …… $ 1,000,000

Long-term debt… 8000000.

Common stock.. 44,000,000

Retained earnings . 6,000,000


Suffolk’s 2016 income was recorded at $2,000,000. It declared and paid no dividends in 2016. On December 31, 2017, two years after the date of acquisition, Suffolk submitted the following trial balance

Cash. 1,500,000

Accounts Receivable. 5,200,000

Inventory . 18,000,000

Property, Plant, and Equipment (net) . 36,000,000

Accounts Payable . (1,450,000 )

Long-Term Debt.. (5.000,000)

Common Stock.. (44,000,000)

Retained Earnings, 1/1/17. (8,000,000)

Sales (28,000,000)

Cost of Goods Sold . 16,000,000

Depreciation. 2,000,000

Other Expenses. 6,000,000

Dividends (1/30/17) 1 750 000

Total 0

Other than paying dividends, no intra-entity transactions occurred between the two companies. Relevant

Exchange rates for the British pound follow:

January 1 January 30 Average December 31

2016 $1.60 $1.61 $1.62 $1.64

2017 1.64 1.65 1.66 1.68

The December 31, 2017, financial statements (before consolidation with Suffolk) follow. Dividend income is the U.S. dollar amount of dividends received from Suffolk translated at the $1.65 exchange rate at January 30, 2017. The amounts listed for dividend income and all affected accounts (i.e., net income, December 31 retained earnings, and cash) reflect the $1.65/$ exchange rate at January 30, 2017. Credit balances are in parentheses.


Sales. $ (70,000,0000)

Cost of goods sold . 34,000,000

Depreciation 20,000,000

Other expenses. 6,000,000

Dividend income . (2,887,500)

net income . $ (12 887 500)

Retained earnings, 1/1/17 . $ (48,000,000)

Net income, 2017 .. (12,887,500)

Dividends, 1/30/17 4,500,000

Retained earnings, 12/31/17 . (56,387,500)

Cash 3,687,500

Accounts receivable 10,000,000

Inventory . 30,000,000

Investment in Suffolk. 83,200,000

plant and equipment (net). 105,000,000

Accounts payable . (25,500,000)

Long-term debt.. (50,000.000)

Common stock (100,000,000)

Retained earnings, 12/31/17 (56,387,500)

Total 0

Parker’s chief financial officer (CFO) wishes to determine the effect that a change in the value of the British pound would have on consolidated net income and consolidated stockholders’ equity. To help assess the foreign currency exposure associated with the investment in Suffolk. The CFO requests assistance in comparing consolidated results under actual exchange rate fluctuations with results that would have occurred had the dollar value of the pound remained constant or declined during the first two years of Parker’s ownership.


Use an electronic spreadsheet to complete the following parts

Part 1. Given the relevant exchange rates presented,

  1. Translate Suffolk’s December 31, 2017, trial balance from British pounds to the U.S. dollars. the British pound is Suffolk’s functional currency

  2. Prepare a schedule that details the change in Suffolk’s cumulative translation adjustment (beginning net assets, income, dividends, etc.) for 2016 and 2017.

  3. Prepare the December 31, 2017, consolidation worksheet for Parker and d Suffolk.

  4. Prepare the 2017 consolidated income statement and the december 31, 2017, consolidated balance sheet.

Note: Worksheets should possess the following qualities:

Each spreadsheet should be programmed so that all relevant amounts adjust appropriately when different values of exchange rates (subsequent to January 1, 2016) are entered into it.

Be sure to program Parker’s dividend income, cash, and retained earnings to reflect the dollar value of alternative January 30, 2017, exchange rates.


When Excel Workbooks are specified as the deliverable for an assignment the following formatting will be required:

  • • The File Name will contain the Student Last Name (or Group Number for a Group Assignment)

  • • The File Name will also contain the Assignment Description (for example, Individual Assignment 2, Group Assignment 2)

  • • Each worksheet tab will be named with an assignment part number, such as Part I a.

    1. • The first 3 rows of each Worksheet will contain: o Student Last Name (or Team Number for a Team Assignment)

    2. o Assignment Description

    3. o Due Date