On January 1, 2021, Phillips Company purchased 100 percent of the outstanding voting stock of Bearden, Inc., for $1,000,000 in cash and other consideration. At the purchase date, Bearden had common stock of $500,000 and retained earnings of $185,000. Phillips attributed the excess of acquisition-date fair value over Bearden's book value to a trade name with an estimated 25-year remaining useful life. Phillips uses the equity method to account for its investment in Bearden. During the next two years,

Bearden reporting the following:

Year	Income	Dividends Declared	Inventory Transfers to Phillips at Transfer Price
2021	$85,000	$27,000	$200,000
2022	90,000	29,000	220,000

Bearden sells inventory to Phillips after a markup based on a gross profit rate. At the end of 2021 and 2022, 30 percent of the current year purchases remain in Phillips's inventory.

Required:

Create an Excel spreadsheet that computes the following:

1. Equity method balance in Phillips's Investment in Bearden, Inc., account as of December 31, 2022.

2. Worksheet adjustments for the December 31, 2022, consolidation of Phillips and Bearden.

Formulate your solution so that Bearden's gross profit rate on sales to Phillips is treated as a variable (this requires the use of formulas in your Excel spreadsheet!).

PLEASE SHOW HOW THE ANSWERS ARE CALCULATED.