Simulation #3: Gross Profit and Conventional Retail Methods

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.

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%

Answer the short response question.

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 $110,000

Freight-In 3,000

Selling Expenses (commissions) 9,750

Sales 180,000

Sales Returns 2,800

Purchase Returns 4,000

Sales Discounts 2,200

In addition, the Controller is aware of $8,000 of inventory that was apparently stolen during May from DEF warehouses.

Calculate the estimated inventory at the end of May, assuming a gross profit ratio of 40%.

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. Unluckys 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 $ 27,889,356

Purchases 355,086,236

Freight-in for purchases 908,368

Purchase discounts taken 4,372,980

Purchase returns 12,352,525

Sales revenue 704,767,693

Sales returns 58,627,749

Retail Data:

Value of inventory at January 1 62,345,952

Value of net purchases 754,973,542

Net markups 7,426,242

Net markdowns 154,297,270