Question

On January 1, 2020, Tom Company is considering purchasing a 35 percent ownership interest in Jerry Company, a privately held enterprise, for $900,000. Jerry predicts its profit will be $250,000 in 2020, projects a 3% annual increase in profits (from one year to the next) in each of the next four years, and expects to pay a steady annual dividend of $20,000 for the foreseeable future. Jerry has on its books a patent that is undervalued by $120,000, and has an estimated remaining useful life of 6 years. All of Jerry’s other assets and liabilities have book values that approximate market values. Tom uses the equity method for its investment in Jerry.

Need a schedule in Excel for the years 2020 through 2024 to display the following:

1. Tom’s equity in Jerry earnings with rows showing these:
   1. Tom’s share of Jerry reported income
   2. Amortization expense
   3. Tom’s equity in Jerry earnings
2. Tom’s Investment in Jerry balance with rows showing the following:
   1. Beginning balance
   2. Equity earnings
   3. Dividends
   4. Ending balance

Answer 1

	Growth rate in income	3%
	Dividends	$20,000
	Cost	$900,000	(given in problem)
	Annual amortization	$20,000	(120000 ÷ 6 Years)
	1st year Jerry income	$250,000
	Percentage owned	35%
		2020	2021	2022	2023	2024
1	Jerry reported income- Toms Share	$87,500	$90,125	$92,829	$95,614	$98,482
	Amortization	7,000	7,000	7,000	7,000	7,000
	Equity earnings	$80,500	$83,125	$85,829	$88,614	$91,482
2	Beginning Balance	$900,000	$973,500	$1,049,625	$1,128,454	$1,210,067
	Equity earnings	80,500	83,125	85,829	88,614	91,482
	Dividends	-7,000	-7,000	-7,000	-7,000	-7,000
	Ending Balance	$973,500	$1,049,625	$1,128,454	$1,210,067	$1,294,549
	ROI	8.94%	8.54%	8.18%	7.85%	7.56%
	Average	8.21%
	Steps: Just put the figures as in bold and set the formulas as shown