The Book Wermz purchases books for all of its stores through a central purchasing department. Books are then shipped to different stores for sale. One of the company’s largest selling items is an edition of Webster’s dictionary. A large volume of sales occurs in August and September each year to students returning to school. At the beginning of August 2004, the company’s inventory of dictionaries included 245 units purchased on April 12, 2004 at $27.00 per unit, 360 units purchased on May 3, 2004 at $29.00 per unit, and 1,000 units purchased on July 24, 2004 at $32.00 per unit. No purchases were made in August. The Book Wermz sold 1,447 units of the dictionary during August.

Required

Use a spreadsheet to prepare a schedule of Cost of Goods Sold and Ending Inventory for the dictionary for August. Show both FIFO and LIFO inventory numbers. Enter the captions illustrated on the next page at the top of the spreadsheet.

Beginning in row 8, enter data in each column for each of the purchase dates. In the Cost of Goods Sold columns, enter the number of units sold and the cost of these units (units sold × cost per unit) from each inventory layer1 using the FIFO method. In the Ending Inventory columns, provide the same data for units remaining in inventory at the end of August. Use formulas for all calculations so that if the units available or cost per unit numbers changed, these changes would automatically be updated in columns D–G.
In row 11, sum columns B and D–G, using the Summation button.
Beginning in row 13, repeat the captions, data, and calculations using the LIFO method.
You can copy the data from the FIFO section, select cell A13, and paste the data. Then make any needed changes for the LIFO calculations.
Beginning in row 21, provide a calculation of the amount of income taxes the company would save if it used the LIFO method in August. The company’s income tax rate was 35%. Place captions in column A and calculations in column B. The calculations should report the amount of LIFO cost, FIFO cost, the excess of LIFO over FIFO costs, the company’s income tax rate, and the tax savings.
Format the schedule using underlines, commas, and appropriate alignment so that it is easy to read and has a formal appearance.
Suppose the number of units available from the April purchase was 545 and the cost of the May purchase was $31.00 per unit. What would cost of goods sold be for August for the dictionaries? What would the ending inventory be at the end ofAugust?

Transcribed Image Text:

The Book Wermz Inventory of Webster's Dictlonary August 31, 2004 FIFO Basls Cost of Goods Sold Ending Inventory Units Date Purchased Units Available Cost per UnitUnits Cost Cost