This Template Will Help Solve the Following Exercises and Problems from the Text

Chapter Three1

Chapter Three

This Template will help solve the following exercises and problems from the text:

Exercise 3-1This template will prepare the Determination and Distribution of Excess Schedule required in the exercise.

Exercise 3-2This template will prepare the Determination and Distribution of Excess Schedule required in the exercise.

Exercise 3-3Partial The Depreciable Fixed Asset has a market value of $262,500.

Exercise 3-4 Partial The Depreciable Fixed Asset has a market value of $262.500

Exercise 3-7Partial The Depreciable Fixed Asset has a market value of $262.500.

Exercise 3-8Partial The Depreciable Fixed Asset has a market value of $262.500.

Problem 3-1 Partial This template will solve Part 1.

Problem 3-2For this problem, I used the following Book Values on the Date of Acquisition: Inventory $100,000, Current Assets $50,000 and Building and Equipment $150,000.

Problem 3-4 For this problem, I assumed that the Depreciable Fixed Assets had a Book Value of $200,000 on the Date of Acquisition.

Problem 3-5 Parts 1 and 2

Problem 3-6 Parts 1 and 2

Problem 3-9

Problem 3-10

Problem 3-11

Problem 3-12

Problem 3-13

Problem 3-14

Problem 3-15

Problem 3-16

Problem 3-17

Problem 3-18

Overview

Chapter Three continues the focus on consolidated financial statements. The worksheets created in this chapter parallel the material covered in Chapter Three of the text where consolidations after the date of purchase are discussed. The template being generated will use the template created for Chapter Two as a starting foundation. To this template we will add income statements in future years, amortization schedules, cost versus equity analysis, date alignment analysis, and income distribution analysis. This template will again handle all price categories, as well as, 100% or less than 100% ownership interests.

This template will focus on the use of either the simple equity method or the cost method for the parent’s investment account. We will not be preparing a worksheet using the sophisticated equity method.

We will use the Paulos Company and Carlos Company data from the text, so you can follow along.

The Carlos Company Data

Paulos Company purchased an 80% interest in Carlos Company by paying $720,000 cash.. The book values and market values of the net assets of Carlos on the date of acquisition are given below:

Carlos Company / Cost / Market
Current Assets
Inventory / 75,000 / 80,000
Property, Plant and Equipment
Land / 150,000 / 200,000
Buildings / 600,000 / 500,000
Less Accumulated Depreciation / (300,000)
Equipment / 150,000 / 80,000
Less Accumulated Depreciation / (50,000)
Intangibles
Patents / 125,000 / 150,000
Total Assets / 750,000 / 1,010,000
Current Liabilities / (50,000) / (50,000)
Bonds Payable—6% / (200,000) / (200,000)
Discount on Bonds Payable / 13,240
Total Liabilities / (250,000) / (236,760)
Common Stock / (100,000)
Paid in capital in excess of par / (150,000)
Retained Earnings / (250,000)
Total Equity / (500,000)
Total Liabilities and Equity / (750,000)
Market value of net assets / 773,260

The Trial Balances for Paulos Company and Carlos Company at December 31, 20X1, (the current year) are given below:

Paulos / Carlos
Cash / 80,000 / 50,000
Inventory / 226,000 / 62,500
Investment in Carlos / 752,000
Land / 200,000 / 150,000
Buildings / 800,000 / 600,000
Accumulated Depreciation / (80,000) / (315,000)
Equipment / 400,000 / 150,000
Accumulated Depreciation / (50,000) / (70,000)
Patent (net) / 112,500
Current Liabilities / (100,000) / -
Bond Payable / - / (200,000)
Discount (Premium)
Common Stock - Carlos / (100,000)
Paid-in Excess - Carlos / (150,000)
Retained Earnings, Jan. 1, 20X1 - Carlos / (250,000)
Common Stock - Paulos / (1,500,000)
Retained Earnings, Jan. 1, 20X1- Paulos / (600,000)
Dividends Declared - Carlos / 20,000
Sales / (350,000) / (200,000)
Cost of Goods Sold / 150,000 / 80,000
Depreciation Expense-Building / 40,000 / 15,000
Depreciation Expense - Equipment / 20,000 / 20,000
Amortization Expense-Patent / 12,500
Other Expenses / 60,000 / 500
Interest Expense / 12,000
Subsidiary Income / (48,000)

The assets of Carlos Company were given the following lives on their purchase date.

Asset Account / Life
Inventory / 1
Bonds Payable—To be entered in Cell D35. / 4
Buildings / 20
Equipment / 5
Patent / 10

Creating a Template allocating the purchase price and preparing a worksheet to consolidate the balance sheet of a consolidated enterprise.

Worksheet Layout

Based on the work in Chapter Two the following layout seems appropriate.

AREA / CONTENTS
Data Entry
This will be mostly data entry with formulas being used for totals and to check the accuracy of the amounts entered. / Name of Companies and Date
Book and Market Values of Assets and Liabilities of Company being Acquired---This is similar to the analysis done in Chapter Two.
The trial balances of both the acquiring company and acquired company for the year of the consolidation known as the current Year---This analysis is new to Chapter Three.
Purchase Price Analysis—This is similar to the analysis done in Chapter Two.
Ownership Interest Percentage---This should remain the same as Chapter Two.
Goodwill Applicable to NCI—This should remain the same as Chapter Two.
Method of Accounting for Investment—Two methods are available –Cost or Equity. Under the cost method the investment account will remain the same as on the date it was purchased. Thus the investment account should equal the purchase price. This will not be true under the simple equity method. This template will not solve problems using the sophisticated equity method.
Years Since Purchase—Because Chapter Three deals with consolidated financial statements for years subsequent to the purchase, we will need to note the number of years since acquisition. We will assume all acquisitions occurred on January 1, Year 1. Thus, if we are working on December 31, Yr 3, we will enter a 3 here. This analysis is new to Chapter Three.
Analysis Section
Done through a series of formulas with an occasional label. / Value Analysis---This should remain similar to Chapter Two.
D and D Schedule—This schedule remains the basically the same as in Chapter Two. We will however eliminate account rows which will not be used in the problems.
Amortization Schedule—Because the consolidation is not being done on the date of purchase an amortization schedule is needed.
Method Adjustment Schedule—We will handle all future work using the simple equity method. Thus if the investment account is being carried at cost a conversion to equity is necessary.
Date Adjustment Schedule-Typically the acquiring company (Parent) records the income of the acquired company (Subsidiary) prior to the closing of the subsidiary’s books. Thus the investment account must be adjusted to agree with the beginning equity balance on the acquired company’s books.
Consolidated Worksheet
Done through a series of formulas with an occasional label. / Worksheet—The completion of our work. This will combine and consolidate the acquiring company and the company being acquired. This worksheet will be expanded from Chapter Two to include the nominal accounts.
Income Distribution Schedule ---This will be prepared at the same time as the Worksheet as the adjustment is needed on the worksheet. Because this involves the nominal accounts, it is new to Chapter Three.

Color Schemes

Black will be used to indicate data that is manually entered and blue will be used to indicate that data is being entered using a formula.

Entering Data

Begin by opening up TemplateCH2. We will be using this as our starting point. (Note that this should be blank.)

Save this as a new workbook named TemplateCH3.

Remove border using Format/Cells/Border/None. (Note: Cells must first be highlighted.)

Insert Carlos Company in Cell B1.

Insert PaulosCompany in Cell B2.

Insert January 1, 20X1 in Cell B3.

Delete all of the formulas and labels in Column D

Merge Cells B4 through D4.

Enter Date of Acquisition in merged Cells B4 through D4 and Center.

Merge Cells E3 and F3.

Enter Current Year in merged Cells E3 and F3 and Center.

Merge Cells E4 and F4.

Enter Trial Balance in merged Cells E4 and F4 in Center.

Using the =Cell command put the name of the Acquiring Company in Cell E5.

Using the =Cell command put the name of the Acquired Company in Cell F5. Enter Life in Cell D6 and Bold.

We will now name some specific cells in our worksheet or reserve cells with differing book and market values. (Be sure to add comments to reserved cells.)

• Cell A8 will be labeled Cash in bold.
• Cell A9 will be reserved for Accounts Receivable.
• Cell A10 will be labeled Inventory in Bold
• Cell A11 will be reserved for any other current asset with differing book and market values.
• Cell A16 will be reserved for Land
• Cells A18, 20, and 22 will be reserved for Accumulated Depreciation.
• Cell A23 and Cell A24 will be reserved for intangibles with differing book and market values.

Enter the data for Carlos Company in A8 through C48. (Note: Debits are entered as positive numbers, Credits are entered as negative. ADiscount on Bonds Payable account should be set up.)

Enter the lives of the assets as given in the appropriate row of Column D. It is assumed that the inventory will be sold the year after acquisition and thus should be given a 1-year life. Enter the life of the bond on the Premium or Discount on Bonds Payable Line

Insert 15 new rows using the Insert command beginning at row 49. Check Balance Sheet Equality should be in Row 64.

In Cell A49 enter Dividends Declared Acquired Company.

In Cell A50 enter Dividends Declared Acquiring Company.

In Cell A51 enter Sales.

In Cell A52 enter Cost of Goods Sold.

In Cell A53 enter Depreciation Expense of.

In Cells A54,A55and A56 enter the type of asset (i.e. Buildings) using the =Cell command.

In Cell A57 enter Amortization Expense of.

In Cells A58, A59 and A60 using the =Cell command enter the type of intangible listed in cells A23, A24, and A25.

In Cell A61 enter Other Expenses.

In Cell A62 enter Interest Expense.

In Cell A63 enter Subsidiary Income.

Replace the term Check Balance Sheet Equality with Total in Cell A64.

Bold all the data entered in Cells A51 through A64.

Using the trial balance data enter the information for Paulos Company and Carlos Company in columns E and F (Note: Credits should be entered as negative numbers.)

Replace the formula in Cell B64 to check the equality of the Balance Sheet of Carlos on the Date of Acquisition. The following formula was used in this template =IF(B47+B28=0,"Balances",(B28+B47)).

Create a formula in Cells E64 and F64 to check the equality of Debits and Credits. Because one can easily make errors in entering data, the following formula was used in Cell E64: =IF(SUM(E8:E63)=0,”Balances”,SUM(E8:E63)) This highlights any problems.

Insert one row if necessary so The Purchase Price Section of the Data Entry Area begins in row 67. This section remains unchanged. Thus, only the data needs to be entered. Remember Paulospaid $720,000 in cash for an 80% interest in Carlos Company.

The Ownership Interest now in Row 75 does not change. Enter .8 for the 80% interest acquired in Cell B75.

The Goodwill Applicable to NCI Section begins in Cell A77 and remains the same.

Enter a 0 in Cell B79 as we will assume the implied amount.

Insert six rows beginning in row 80. (The Value Analysis now begins in Row 89.)

In Cell A81 enter Method of Accounting for Investment—Enter Capital C for Cost or Capital E for Equity. (Note: We will be creating a template for either the cost or simple equity methods. This template will not work for investments kept under the sophisticated equity method.) Wrap Text here if necessary.

Since Parental is using the simple equity method enter E in Cell B81.

In Cell A83 enter Years since Acquisition.

In Cell B83 enter 1 since this is the first year of ownership.

Wrap the text in all Column A Cells if necessary.

Using the Format/Cells/Border command place a border around your data entry section. (The Value Analysis Section should begin in Cell A89.)

Your template should now resemble Template 3-1.

Value Analysis

The Value Analysis should begin in Row 89 so you may have to add or delete a row to keep in line with these instructions. The value analysis should not change. Since all the formulas were entered using relative references, they have all been changed appropriately. As you can see from the text the value analysis indicates Goodwill of $126,760.

Value Analysis
Parent Price / NCI Value / Company Fair Value
Company Fair Value / 720,000 / 180,000 / 900,000
Fair Value of Net Assets Excluding Goodwill / 618,592 / 154,648 / 773,240
Goodwill / 101,408 / 25,352 / 126,760
Gain on Acquisition / -

Determination and Distribution of Excess Schedule

The Determination and Distribution of Excess Schedule should begin in Row 97 so you may need to adjust your rows accordingly.

In order to keep our template concise and in line with the book problem we can eliminate some of the row in our Determination and Distribution of excess schedule. Remember this schedule is used to adjust the balances where book and market values differ.

Some current assets will not change so you can delete Rows 120 and 121.

Row 126 references intangible assets where book and market values are equal so it can be deleted as well.

Current Liabilities will not have differing book and market values, so Rows 127, 128, and 129 can be deleted.

Lastly, we will not be adjusting Accounts Receivable so Row 117 can be deleted.

To make the next schedule easier, the Life of adjusted assets will be entered in Column D.

Because the Gain will be taken to income if we are in the first year change Cell A130 to read Gain Taken to Acquiring Co. RE/Income

In Cell D116 enter Life in Bold.

Using the =Cell command copy the following lives.

• Copy the life of Inventory in Cell D10 to Cell D117.
• Copy the life in Cell D11 to Cell D118.
• Copy the life of the Buildings in Cell D17 to Cell D120.
• Copy the life of the Equipment in Cell D19 to Cell D121.
• Copy the life in Cell D21 to Cell D122.
• Copy the life of the Patents in Cell D23 to Cell D123.
• Copy the life in Cell D24 to Cell D124.
• Copy the life in Cell D34 to Cell D1267.
• Copy the life in Cell D35 to Cell D127.
• Copy the life in Cell D36 to Cell D128

97 / Determination and Distribution of Excess Schedule
98 / Implied Company Value / Parent Price / NCI Value
99 / Fair Value of Company / 900,000 / 720,000 / 180,000
100 / Less Book Value of Interest Acquired
101 / Common Stock / (100,000)
102 / Paid-in Capital in Excess of Par / (150,000)
103 / Retained Earnings / (250,000)
104 / Total Equity / (500,000)
105
106 / Interest Acquired / 0.80 / 0.20
107 / Book Value / (400,000) / (100,000)
108 / Excess of Fair Value over Book Value / 320,000 / 80,000
109
110 / Elimination Entry / Key
111 / Common Stock / 80,000 / EL
112 / Paid-in Capital in Excess of Par / 120,000 / EL
113 / Retained Earnings / 200,000 / EL
114 / Investment in Subsidiary / (400,000.00) / EL
115
116 / Adjustment to Identifiable Accounts / Debit (Credit) / Key / Life
117 / Inventory / 5,000 / D / 1
118 / - / - / D / -
119 / Land / 50,000 / D
120 / Buildings / 200,000 / D / 20
121 / Equipment / (20,000) / D / 5
122 / - / - / D / -
123 / Patents / 25,000 / D / 10
124 / - / - / D / -
125 / Goodwill / 126,760 / D / -
126 / Bonds Payable / - / D / -
127 / Discount on Bonds Payable / 13,240 / D / 4
128 / - / - / D / -
129 / Investment in Subsidiary / (320,000) / D
130 / Gain Taken to Acquiring Co. RE /Income / - / D
131 / Acquired Company RE / (80,000) / D
132 / Check / -

Amortization Schedule

The Amortization Schedule is new to Chapter Three. In Chapter Two we were looking at the companies on the date of acquisition, and thus we did not have to worry about amortizing any differences between book value and market values. Because Chapter Three looks at years since acquisition we have to look at this issue.

Think about what this statement should look like before you begin to lay it out.

Account Adjustment / Annual Amount / Current Year / Prior Years / Total
This should give us the name of the account being adjusted. / This is the amount of the adjustment from the D&D Schedule divided by the remaining life from the D&D Schedule / This is the adjustment that should be taken to income this year. / This is the sum of the prior years adjustments to the acquiring company’s Retained Earnings account. / This is the sum of the current and prior years.

Begin by adding rows to your worksheet at Row 133 such that your consolidated worksheet would then begin in Row 184.

In Cell A134 enter Amortization Schedule in Bold.

The following headings should be added in Row 135.

• In Column A enter Account Adjustment in Bold.
• In Column B enter Annual Amount in Bold.
• In Column C enter Current Year in Bold.
• In Column D enter Prior Years in Bold.
• In Column E enter Total in Bold.

In Column A we want to enter the name of the account adjusted. This can be done using the =Cell Command. Cell A136 should equal the account name in Cell A117.

Copy the formula from Cell A136 to Cells A137 through A147.

In the Annual Amount column, the annual adjustment should be entered. This amount is the Adjustment divided by the life. If there is no adjustment or the adjustment is not amortized in the case of land or goodwill, no annual amount should be computed. I used the following formula in Cell B136: =IF(B117=0," ",IF(D117=0," ",B117/D117)).

Copy this formula to Cells B137 through B147.

To Compute the Current Year amount we have to compare the years since acquisition in Cell B83 with the life of the adjustment. If the number of years since acquisition is greater than the life of the adjustment, no current year adjustment is necessary, otherwise an adjustment is needed. Here is my formula for Cell C136: =IF($B$83>D117,” “,B136).

Copy this formula to Cells C137 through C147.

In Column D we want to get the adjustment needed to the retained earnings account, which is the sum of the prior year’s adjustments. If there is no annual amount in Column B it should be left blank. If Column C is also blank, it indicates that the account adjustment has been fully amortized. Also remember that the current year is shown in the trial balance income statement accounts. The formula used in Cell D136 is

=IF(B136=" "," ",IF(C136=" ",B136*D117,B136*($B$83-1)))

Copy the IF statement in Cell D136 to Cells D137 through D147. (Also note that we are doing this one for the first year so the prior year’s amount will be blank.)

The Total Column is computed by summing the Current Year amount and the Prior Years Amounts.

Consult the following Illustration if you are having difficulty with the Amortization Schedule.

A / B / C / D / E
136 / =A117 / =IF(B117=0," ",IF(D117=0," ",B117/D117)) / =IF($B$83>D117," ",B136) / =IF(B136=" "," ",IF(C136=" ",B136*D117,B136*($B$83-1))) / =SUM(C136:D136)
137 / =A118 / =IF(B118=0," ",IF(D118=0," ",B118/D118)) / =IF($B$83>D118," ",B137) / =IF(B137=" "," ",IF(C137=" ",B137*D118,B137*($B$83-1))) / =SUM(C137:D137)
138 / =A119 / =IF(B119=0," ",IF(D119=0," ",B119/D119)) / =IF($B$83>D119," ",B138) / =IF(B138=" "," ",IF(C138=" ",B138*D119,B138*($B$83-1))) / =SUM(C138:D138)
139 / =A120 / =IF(B120=0," ",IF(D120=0," ",B120/D120)) / =IF($B$83>D120," ",B139) / =IF(B139=" "," ",IF(C139=" ",B139*D120,B139*($B$83-1))) / =SUM(C139:D139)
140 / =A121 / =IF(B121=0," ",IF(D121=0," ",B121/D121)) / =IF($B$83>D121," ",B140) / =IF(B140=" "," ",IF(C140=" ",B140*D121,B140*($B$83-1))) / =SUM(C140:D140)
141 / =A122 / =IF(B122=0," ",IF(D122=0," ",B122/D122)) / =IF($B$83>D122," ",B141) / =IF(B141=" "," ",IF(C141=" ",B141*D122,B141*($B$83-1))) / =SUM(C141:D141)
142 / =A123 / =IF(B123=0," ",IF(D123=0," ",B123/D123)) / =IF($B$83>D123," ",B142) / =IF(B142=" "," ",IF(C142=" ",B142*D123,B142*($B$83-1))) / =SUM(C142:D142)
143 / =A124 / =IF(B124=0," ",IF(D124=0," ",B124/D124)) / =IF($B$83>D124," ",B143) / =IF(B143=" "," ",IF(C143=" ",B143*D124,B143*($B$83-1))) / =SUM(C143:D143)
144 / =A125 / =IF(B125=0," ",IF(D125=0," ",B125/D125)) / =IF($B$83>D125," ",B144) / =IF(B144=" "," ",IF(C144=" ",B144*D125,B144*($B$83-1))) / =SUM(C144:D144)
145 / =A126 / =IF(B126=0," ",IF(D126=0," ",B126/D126)) / =IF($B$83>D126," ",B145) / =IF(B145=" "," ",IF(C145=" ",B145*D126,B145*($B$83-1))) / =SUM(C145:D145)
146 / =A127 / =IF(B127=0," ",IF(D127=0," ",B127/D127)) / =IF($B$83>D127," ",B146) / =IF(B146=" "," ",IF(C146=" ",B146*D127,B146*($B$83-1))) / =SUM(C146:D146)
147 / =A128 / =IF(B128=0," ",IF(D128=0," ",B128/D128)) / =IF($B$83>D128," ",B147) / =IF(B147=" "," ",IF(C147=" ",B147*D128,B147*($B$83-1))) / =SUM(C147:D147)

Some of these accounts are not amortized so delete rows: