The requirement of this assignment is to read through the situation below and submit an Excel file that includes responses to the two numbered items listed below. Submit the completed assignment (in Microsoft Excel) through the designated submission area.
Baugh Company purchased 100% of the outstanding stock of Wiley, Inc., on January 1, 2022, for a purchase price of $1,200,000. At the acquisition date, Wiley's book value consisted of common stock of $600,000 and retained earnings of $250,000. Baugh attributed the cost over book to a patent with an estimated 20-year remaining useful life. Baugh uses the equity method to account for its investment in Wiley.
During the next two years, Wiley reporting the following:
Year | Income | Dividends Declared | Inventory Transfers to Baugh at Transfer Price |
2022 | $120,000 | $42,000 | $250,000 |
2023 | 135,000 | 45,000 | 280,000 |
Wiley sells inventory to Baugh after a markup based on a gross profit rate. At the end of 2022 and 2023, 40 percent of the current year purchases remain in Wiley's inventory.
Required
Create an Excel spreadsheet that computes the following:
- Investment in Wiley, Inc., account as of December 31, 2023.
- Worksheet adjustments (consolidation journal entries) for the December 31, 2023, consolidation of Baugh and Wiley.
Your solution should be formulated so that Wiley's gross profit rate on sales to Baugh is treated as a variable (this requires the use of formulas in your Excel spreadsheet!). Most amounts should be formula-driven so that when you change the gross profit rate (or any other variable), all other numbers update including the equity income, balance in the investment account, and amount of the worksheet adjustments. Use formulas everywhere you reasonably can.
Not sure where to start or what all to include in your solution? Here are suggestions:
- Acquisition Date Allocation including the calculation of any amortization.
- Intra-entity inventory transfers (this could be just a small table calculating the amount of unrealized intra-entity profit) - this one isn't necessary because you can embed the calculations elsewhere, but seeing it separately usually helps students, and the more detail you have, the more partial credit I can award if you make mistakes.
- Equity Income for each year (these are needed to help arrive at the investment balance).
- Investment in Wiley
- Consolidation entries
- I also recommend having a small section somewhere in the worksheet with "given" information so that any formulas you use can pull from it (Wiley's income and dividends, etc.).
IMPORTANT:
- Hint: you will NOT calculate the gross profit rate; you can pick any rate you want. You will need a gross profit rate to complete this assignment - all you have to do is make one up. The number doesn't matter; how you apply it and the calculations that you perform matter. Make sure you use formulas in the Excel Case - that is the purpose of the assignment, and I will be checking for those. NOTE: Do NOT use 60% as your chosen gross profit rate (that's not a percentage you would normally see or use in this class related to gross profit rate, and if you do use it, I will know what you did and where you got it from!).
- Read the grading rubric - you'll notice in it that you cannot earn very many points if you don't use formulas.
- Label everything clearly so that I can understand your intentions - this way, even if you get something wrong, I will likely be able to give partial credit because I can understand what you meant.