Assignment 4 (Chapter 5)

100 out of/100 Total points awarded

The score awarded for the assignment 100 out of/100

Part 1 of 5

This case introduces you to data analysis by allowing you to extract, visualize, and compare financial data over a span of 11 years, from 2010-2020, for six publicly traded companies whose identities have been disguised. The data is provided as an Excel file called “Ch03_CompanyData”.

Download the Excel File Here

For the case, assume you are an analyst conducting research into the relative performance and state of financial health of the companies.

For this question you will use PwerBI—instructions on how to calculate a variable or create a measure are included below. Once you have created your visualization you will refer to it to answer the included questions.

Instructions

If you do not already have Power BI, you can download a free version of Power BI desktop from Microsoft.com. After installing the application, you should be ready to import your data.

To import your data, open the application and click get data, Import data from Excel and select the file you downloaded and click the transform button. This opens up a query window where you see a preview of your data. Ensure that the data is properly formatted paying close attention that the variable “Year” is formatted as text. Click the “Close & Apply” button on the top left corner of the screen. Once your data is imported, you should see the worksheet name under the Fields pane in the right most part of your screen. This blank canvas labelled page 1, will be the starting point of your task. You can change this label to whatever you wish. Now you are ready to perform your visualizations.
The task requires you to manipulate information that is provided to yield other information that is not provided.
To complete this task, you will create a new “Measure” which will be labelled “Dividends”. An examination of the data shows that dividends are not provided but inputs to calculating dividends are provided. “Ending Retained Earnings” and Consolidated Net Income” are provided. You will apply the dividend formula to create the measure
To begin, you will need to generate an “Opening Retained Earnings”. You do this by re-arranging the data such that the Ending Retained Earnings of the immediately preceding year becomes the opening retained earnings of the following year.
To do this, you will need to create two news index columns, one beginning from 0 and another beginning from 1. You can do this by clicking on the “Add Column” menu and then selecting Index Column icon.
Next you will go to the Home tab and click on the “Merge Queries” icon. You will be merging the data with itself on the index columns you just created. You will see the Company Data in the top area of your merge query. In the lower drop down menu, you select the same data and merge on Index and Index.1. Click OK. Note that the data is grouped by company so it is essential that we merge by company as well. So while holding down the “Ctrl” key on your keyboard, select the “Company Name” in both panes of the merging query. Then click OK.
You will notice that a new column labelled “Added Index” has been created. Click on the arrows in that name and deselect all and select only the “Ending Retained Earnings” measure. A new measure will show up with the prefix Added Index1.Ending Retained Earnings. You can rename this Opening Retained Earnings. You will notice that there is no information for the 2010 year as that is the beginning of the data. You can delete the Index Columns you created as they are of no use anymore.
You are now ready to create the “Dividend declared” measure by using the formula: Opening Retained Earning + Consolidated Net Income – Ending Retained Earnings.
To do this, click on the menu “Add Column” and select “Custom Column”. In the dialog box that opens, name the new column, “Dividends Declared” and in the formula pane insert the measures for the formula as in (8) above and click OK. Your measure will be appended to the Table.
To begin creating your visualization, create the report you want to visualize, by selecting the variables of interest – Company Name, Dividends declared and Year. This will create a Table showing the data. Next, click on the visualization of your choice on the right side of the screen. A clustered column chart yields an acceptable format. Ensure that “Company Name” is in the Axis and the values are of the variable that you are interested in.

Present a visualization that shows the goodwill for each company for the years from 2010 – 2020. Using this visualization, answer the following questions.

What was the amount of dividend declared by Coolridge Inc. in 2020?

Multiple Choice

$1,982
$1,740
$1,850
$1,142

What was the least amount of dividends declared by any company during the period?

Multiple Choice

$120
$157
$108
$110

What was the largest amount of dividends declared by any company during the period?

Multiple Choice

$2,740
$1,956
$1,982
$1,850

How much dividend did Skylight Inc. declare in 2011?

Multiple Choice

$250
$120
$100
$0

What was the change in dividend declared by Wonder Corporation between 2019 and 2020?

Multiple Choice

Decrease of $550
Increase of $550
Decrease of $1,300
Increase of $1,300

Large Ltd. purchased 70% of Small Company on January 1, Year 6, for $770,000, when the statement of financial position for Small showed common shares of $560,000 and retained earnings of $260,000. On that date, the inventory of Small was undervalued by $71,000, and a patent with an estimated remaining life of five years was overvalued by $90,000.

Small reported the following subsequent to January 1, Year 6:

	Profit (Loss)			Dividends
Year 6	$	144,000		$	41,000
Year 7		(51,000	)		26,000
Year 8		106,000			56,000

A test for goodwill impairment on December 31, Year 8, indicated a loss of $20,900 should be reported for Year 8 on the consolidated income statement. Large uses the cost method to account for its investment in Small and reported the following for Year 8 for its separate-entity statement of changes in equity:


Retained earnings, beginning	$	660,000
Profit		360,000
Dividends		(54,000	)
Retained earnings, end	$	966,000

Required:

(a) Prepare the cost method journal entries of Large for each year. (If no entry is required for a transaction/event, select “No journal entry required” in the first account field.)

(b) Compute the following on the consolidated financial statements for the year ended December 31, Year 8: (Omit $ sign in your response.)

Goodwill
Non-controlling interest on the statement of financial positio
Retained earnings, beginning of year
Profit attributable to Large’s shareholders

Profit attributable to non-controlling interest

(c) Now assume that Large is a private entity, uses ASPE, and chooses to use the equity method to report its investment in Small.

Prepare Large’s journal entries for each year related to its investment in Small. (If no entry is required for a transaction/event, select “No journal entry required” in the first account field.)
Determine the investment in Small at December 31, Year 8. (Omit $ sign in your response.)

Question 7

On January 1, Year 4, Grant Corporation bought 4,000 (80%) of the outstanding common shares of Devy Company for $35,000 cash. Devy’s shares were trading for $7 per share on the date of acquisition. On that date, Devy had $12,500 of common shares outstanding and $15,000 retained earnings. Also on that date, the carrying amount of each of Devy’s identifiable assets and liabilities was equal to its fair value except for the following:

	Carrying Amount		Fair Value
Inventory	$	25,000	$	27,500
Patent		5,000		10,000

The patent had an estimated useful life of five years at January 1, Year 4, and the entire inventory was sold during Year 4. Grant uses the cost method to account for its investment.

The following are the separate-entity financial statements of Grant and Devy as at December 31, Year 7:

BALANCE SHEETS
At December 31, Year 7
		*Grant*		*Devy*
Assets
Cash	$	2,500	$	9,000
Accounts receivable		92,500		41,000
Inventory		155,000		50,000
Investment in Devy		35,000
Equipment (net)		115,000		102,500
Patent (net)				1,000
	$	400,000	$	203,500
Liabilities and Shareholders’ Equity
Accounts payable	$	95,000	$	97,500
Other accrued liabilities		30,000		25,000
Income taxes payable		40,000		36,000
Common shares		85,000		12,500
Retained earnings		150,000		32,500
	$	400,000	$	203,500

INCOME STATEMENT
Year ended December 31, Year 7
		*Grant*			*Devy*
Sales	$	450,000		$	180,000
Cost of goods sold		(170,000	)		(120,000	)
Gross margin		280,000			60,000
Distribution expense		(15,000	)		(12,500	)
Other expenses		(90,000	)		(28,000	)
Income tax expense		(60,000	)		(8,000	)
Net income	$	115,000		$	11,500

Additional Information

The recoverable amount for goodwill was determined to be $5,000 on December 31, Year 7. The goodwill impairment loss occurred in Year 7.
Grant’s accounts receivable contains $15,000 owing from Devy.
Amortization expense is grouped with distribution expenses and impairment losses are grouped with other expenses.

Required:

(a) Calculate consolidated retained earnings at December 31, Year 7. (Input all values as positive numbers. Omit $ and % sign in your response.)

(b) Prepare consolidated financial statements for Year 7. (Input all values as positive numbers.)

Related Assignment: (Solution) MOS4465 Assignment 5 (Chapters 6 & 7)