Required:

• 8 points: Prepare an Excel spreadsheet analysis using the data provided on the following pages to complete the following problems, including determining the ending inventory and cost of goods sold for 2014. Any calculated number should be calculated in Excel using reference to other cells. The spreadsheet should be designed for reuse in subsequent periods.
• 18 points: Complete the following problems related to the gross profit and conventional retail methods for estimating inventory. Correctly calculate ending inventory and cost of goods sold as needed. Prepare any journal entry in Excel to adjust inventory to the estimated value and close the purchase accounts to cost of goods sold as needed. Link the amounts to your calculations.
  • Round all calculated values to whole integers, e.g., 123,456,789
  • Round all ratios to 5 decimal places, e.g., 0.12345 or 12.345%
• 4 points: Answer the short response question.

EXCEL GRADING RUBRIC (6 points):

• Proper headings, including the title and timeframe for the analysis
• Spreadsheet should be usable in subsequent periods
• Any inputs must be visible cells and properly labeled. Highlight all input cells in green
• All calculated amounts must be completed with Excel formulas using data from other cellsdo not insert numbers in the Excel formulas

ACCTG 331 - Intermediate Accounting I Simulation #3: Gross Profit and Conventional Retail Methods 30 points Required: 1. 8 points: Prepare an Excel spreadsheet analysis using the data provided on the following pages to complete the following problems, including determining the ending inventory and cost of goods sold for 2014. Any calculated number should be calculated in Excel using reference to other cells. The spreadsheet should be designed for reuse in subsequent periods. 2. 18 points: Complete the following problems related to the gross profit and conventional retail methods for estimating inventory. Correctly calculate ending inventory and cost of goods sold as needed. Prepare any journal entry in Excel to adjust inventory to the estimated value and close the purchase accounts to cost of goods sold as needed. Link the amounts to your calculations. a. Round all calculated values to whole integers, e.g., 123,456,789 b. Round all ratios to 5 decimal places, e.g., 0.12345 or 12.345% 3. 4 points: Answer the short response question. EXCEL GRADING RUBRIC (6 points): Proper headings, including the title and timeframe for the analysis Spreadsheet should be usable in subsequent periods Any inputs must be visible cells and properly labeled. Highlight all input cells in green All calculated amounts must be completed with Excel formulas using data from other cellsdo not insert numbers in the Excel formulas DEF Corporation Gross Profit Method for Estimating Inventory DEF Corporation uses the gross profit method to estimate ending inventory and cost of goods sold when preparing monthly financial statements required by its lender, a prominent financial institution. Inventory on hand at the end of May was $58,500. The following information for the month of May was available from corporation records: Purchases Freight-In Selling Expenses (commissions) Sales Sales Returns Purchase Returns Sales Discounts $110,000 3,000 9,750 180,000 2,800 4,000 2,200 In addition, the Controller is aware of $8,000 of inventory that was apparently stolen during May from DEF warehouses. 1. Calculate the estimated inventory at the end of May, assuming a gross profit ratio of 40%. 2. Calculate the estimated inventory at the end of May, assuming a markup on cost of 100%. Unlucky Blue Jean Company Conventional Retail Method Inventory and COGS During 2014, Unlucky Blue Jean Company lost its market share to Bonobos.com. Unlucky was unable to sell all of its merchandise, and thus offered big markdowns in December. Unlucky's year-end is December 31. Use the Conventional Retail Method and the information provided below to determine the ending inventory, cost of goods sold, and gross profit reported in the income statement for 2014. What is the expected gross profit Unlucky will record on sales of the 12/31/14 inventory in 2015? Compare the expected gross profit rate on 12/31/14 inventory to reported gross profit rate for 2014 and explain why they are different. FACTS for 2014: Balances in general ledger: Inventory at January 1 Purchases Freight-in for purchases Purchase discounts taken Purchase returns Sales revenue Sales returns Retail Data: Value of inventory at January 1 Value of net purchases Net markups Net markdowns $ 27,889,356 355,086,236 908,368 4,372,980 12,352,525 704,767,693 58,627,749 62,345,952 754,973,542 7,426,242 154,297,270