I need the formulas used, so please show all work. the gross profit rate does not have to be calculated, it can be a made up number.

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 585,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 spreadsheett)