Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Excel and Analysis Case (continued)
a. Translation of Suffolk’s December 31, 2015 trial balance from British pounds to
U.S. dollars.
Suffolk PLC
Trial Balance
December 31, 2015
Exchange
Pounds Rate Dollars
Cash £ 1,500,000$1.68 $ 2,520,000
Accounts receivable 5,200,000$1.68 8,736,000
Common stock (44,000,000)$1.60 (70,400,000)
Retained earnings, 1/1/15 (8,000,000)Schedule A
(12,840,000)
Sales (28,000,000)$1.66 (46,480,000)
Cumulative translation
adjustment—positive (credit balance) (4 ,147,500)
£ 0 $ 0
Note: Amounts in parentheses are credit balances.
Exchange
Schedule A Pounds Rate Dollars
Retained earnings, 1/1/14 £(6,000,000)$1.60 $ (9,600,000)
Excel and Analysis Case (continued)
b. Schedule detailing the change in Suffolk’s cumulative translation adjustment
for 2014 and 2015.
Determination of Cumulative Exchange Exchange
Translation Adjustment Pounds Rate Rate Dollars
Net assets, 1/1/14 £50,000,000$1.64 $1.60 $2,000,000
Net income, 2014 2 ,000,000$1.64 $1.62 40 ,000
Translation adjustment, 2014
Dividends, 2015 (1 ,750,000)$1.68 $1.65 (52 ,500)
Translation adjustment, 2015
(positive) 2 ,107,500
Net assets, 12/31/15 £ 54 ,250,000
Cumulative Translation
Adjustment, 12/31/15 (positive) $4 ,147,500
Exchange
Cost Allocation Schedule Pounds Rate Dollars
Translation Adjustment Related to Exchange
Excess of Cost Over Book Value Pounds Rate Dollars
Excess of cost over book value £2,000,000
U.S. dollar value at 12/31/15 $1.68 $3,360,000
Excel and Analysis Case (continued)
c. Consolidation Worksheet—December 31, 2015
Parker Suffolk Adjustments & Eliminations Consolidated
Sales ($70,000,000) ($46,480,000) ($116,480,000)
Cost of goods sold 34,000,000 26,560,000 60,560,000
Net income (12,887,500) (6,640,000) (16,640,000)
Dividends 4 ,500,000 2 ,887,500 2,887,500 4 ,500,000
Ret. earnings,
12/31/15
($56 ,387,500) ($16 ,592,500) ($63 ,380,000)
Prop, plant & eq (net) 105,000,000 60,480,000 3,200,000 168,840,000
160,000
Accounts payable (25,500,000) (2,436,000) (27,936,000)
Long-term debt (50,000,000) (8,400,000) (58,400,000)
Excel and Analysis Case (continued)
d. Consolidated income statement and balance sheet—2015.
Parker, Inc.
Consolidated Income Statement
For the year ended December 31, 2015
Sales $ 116,480,000
Cost of goods sold (60,560,000)
Parker, Inc.
Consolidated Balance Sheet
December 31, 2015
Assets
Cash $ 6,207,500
Accounts receivable 18,736,000
Liabilities and Shareholders' Equity
Accounts payable $ 27,936,000
Long-term debt 58,400,000
Common stock 100,000,000
Excel and Analysis Case (continued)
Part II—Stable Foreign Currency
Relevant exchange rates: January 1, 2014 $1.60
December 31, 2015 $1.60
a. Translation of Suffolk’s December 31, 2015 trial balance from British pounds to
U.S. dollars.
Suffolk PLC
Trial Balance
December 31, 2015
Exchange
Pounds Rate Dollars
Cash £ 1,500,000$1.60 $ 2,400,000
Accounts receivable 5,200,000$1.60 8,320,000
Common stock (44,000,000)$1.60 (70,400,000)
Retained earnings, 1/1/15 (8,000,000)Schedule A (12,800,000)
Sales (28,000,000)$1.60 (44,800,000)
Cost of goods sold 16,000,000$1.60 25,600,000
Cumulative translation
adjustment 0
£ 0 $ 0
Note: Amounts in parentheses are credit balances.
Exchange
Schedule A Pounds Rate Dollars
Retained earnings, 1/1/14 £(6,000,000)$1.60 $ (9,600,000)
Excel and Analysis Case (continued)
b. Schedule detailing the change in Suffolk’s cumulative translation adjustment
for 2014 and 2015.
Determination of Cumulative Exchange Exchange
Translation Adjustment Pounds Rate Rate Dollars
Net assets, 1/1/14 £50,000,000$1.60 $1.60 $0
Net income, 2014 2 ,000,000$1.60 $1.60 0
Translation adjustment, 2014 $0
Translation adjustment, 2015 0
Net assets, 12/31/15 £ 54 ,250,000
Cumulative Translation
Adjustment, 12/31/15 $0
Exchange
Cost Allocation Schedule Pounds Rate Dollars
Cost £52,000,000 $1.60 $83,200,000
Translation Adjustment Related to Exchange
Excess of Cost Over Book Value Pounds Rate Dollars
Excess of cost over book value £2,000,000
Translation adjustment related
to excess, 12/31/15 $0
Excel and Analysis Case (continued)
c. Consolidation Worksheet—December 31, 2015
Parker Suffolk Adjustments & Eliminations Consolidated
Sales ($70,000,000) ($44,800,000) ($114,800,000)
Cost of goods sold 34,000,000 25,600,000 59,600,000
Net income (12,800,000) (6,400,000) (16,400,000)
Dividends 4 ,500,000 2 ,800,000 2,800,000 4 ,500,000
Ret. earnings,
12/31/15
($56 ,300,000) ($16 ,400,000) ($63 ,100,000)
Prop, plant & eq (net) 105,000,000 57,600,000 3,200,000 165,800,000
0
Accounts payable (25,500,000) (2,320,000) (27,820,000)
Long-term debt (50,000,000) (8,000,000) (58,000,000)
Excel and Analysis Case (continued)
d. Consolidated income statement and balance sheet—2015.
Parker, Inc.
Consolidated Income Statement
For the year ended December 31, 2015
Sales $114,800,000
Cost of goods sold (59,600,000)
Parker, Inc.
Consolidated Balance Sheet
December 31, 2015
Assets
Cash $ 6,000,000
Accounts receivable 18,320,000
Liabilities and Shareholders' Equity
Accounts payable $ 27,820,000
Long-term debt 58,000,000
Retained earnings 63,100,000
Accum. other comp. income 0
Total $248,920,000
Excel and Analysis Case (continued)
Part III—Depreciating Foreign Currency
January 30, 2015 $1.55
2015 Average $1.54
December 31, 2015 $1.52
a. Translation of Suffolk’s December 31, 2015 trial balance from British pounds to
U.S. dollars.
Suffolk PLC
Trial Balance
December 31, 2015
Exchange
Pounds Rate Dollars
Cash £ 1,500,000$1.52 $ 2,280,000
Accounts receivable 5,200,000$1.52 7,904,000
Long-term debt (5,000,000)$1.52 (7,600,000)
Common stock (44,000,000)$1.60 (70,400,000)
Retained earnings, 1/1/15 (8,000,000)Schedule A (12,760,000)
Sales (28,000,000)$1.54 (43,120,000)
Dividends, 1/30/15 1 ,750,000$1.55 2,712,500
Cumulative translation
adjustment—negative (debit balance) 4 ,147,500
£ 0 $ 0
Note: Amounts in parentheses are credit balances.
Exchange
Schedule A Pounds Rate Dollars
Retained earnings, 1/1/14 £(6,000,000)$1.60 $ (9,600,000)
Excel and Analysis Case (continued)
b. Schedule detailing the change in Suffolk’s cumulative translation adjustment
for 2014 and 2015.
Determination of Cumulative Exchange Exchange
Translation Adjustment Pounds Rate Rate Dollars
Translation adjustment, 2014
(negative)
$(2,040,000)
Net assets, 1/1/15 £52,000,000$1.52 $1.56 (2,080,000)
Translation adjustment, 2015
(negative) (2 ,107,500)
Net assets, 12/31/15 £ 54 ,250,000
Cumulative Translation
Adjustment, 12/31/15 (negative)
$(4,147,500)
Exchange
Cost Allocation Schedule Pounds Rate Dollars
Cost £52,000,000 $1.60 $83,200,000
Translation Adjustment Related to Exchange
Excess of Cost Over Book Value Pounds Rate Dollars
Excess of cost over book value £2,000,000
Translation adjustment related
to excess, 12/31/15—negative $(160 ,000)
Excel and Analysis Case (continued)
c. Consolidation Worksheet—December 31, 2015
Parker Suffolk Adjustments & Eliminations Consolidated
Sales ($70,000,000) ($43,120,000) ($113,120,000)
Cost of goods sold 34,000,000 24,640,000 58,640,000
Dividend income (2 ,712,500) 2,712,500 0
Net income ($12 ,712,500) ($6 ,160,000) ($16 ,160,000)
Ret. earnings, 1/1/15 ($48,000,000) ($12,760,000) 12,760,000 3,160,000 ($51,160,000)
Net income (12,712,500) (6,160,000) (16,160,000)
Accounts receivable 10,000,000 7,904,000 17,904,000
Inventory 30,000,000 27,360,000 57,360,000
Investment in Suffolk 83,200,000 3,160,000 83,160,000 0
3,200,000
Prop, plant & eq (net) 105,000,000 54,720,000 3,200,000 162,760,000
160 ,000
Ret. earnings,
12/31/15
(56 ,212,500) (16,207,500) (62,820,000)
Cum. Trans. adj. 4 ,147,500 160 ,000 4 ,307,500
$0 $0 $92 ,392,500 $92 ,392,500 $0
Excel and Analysis Case (continued)
d. Consolidated income statement and balance sheet—2015.
Parker, Inc.
Consolidated Income Statement
For the year ended December 31, 2015
Depreciation (23,080,000)
Other expenses (15 ,240,000)
Net income $ 16 ,160,000
Parker, Inc.
Consolidated Balance Sheet
December 31, 2015
Assets
Cash $ 5,792,500
Accounts receivable 17,904,000
Liabilities and Shareholders' Equity
Accounts payable $ 27,704,000
Long-term debt 57,600,000
Excel and Analysis Case (continued)
Part IV—Risk Assessment Report and Financial Management Recommendations
December 31, 2015 Exchange Rate
$1.68 $1.60 $1.52
Consolidated net income $16,640,000 $16,400,000 $16,160,000
Percentage difference 101.5% 100% 98.5%
Total Stockholders’ equity $167,687,500 $163,100,000 $158,512,500
Debt-to-equity ratio 51.5% 52.6% 53.8%
Percentage difference 98% 100% 102%
- 2% -- + 2%
Appreciation of the British pound from $1.60 to $1.68 results in consolidated
Depreciation of the British pound from $1.60 to $1.52 would have resulted in
An increase in the dollar value of the British pound results in higher
profitability, greater cash inflow, and an improved debt-to-equity ratio. The
opposite is true for a decrease in the dollar value of the British pound.
If the British pound is expected to appreciate, Parker should not hedge its
British pound exposure associated with its investment in Suffolk. However, if
the British pound is expected to depreciate, Parker may wish to hedge its
British pound net asset and cash flow exposure in some way. The decline in
