In: Accounting
Periodic (FIFO, LIFO, Average)
Spring River sells bottled water using a periodic inventory system. The following is a list of beginning inventory and purchases for bottles used in manufacturing their spring water.
Beginning Inventory 6,000 bottles at $0.20
First Purchase 4,500 bottles at $0.22
Second Purchase 5,600 bottles at $0.24
Third Purchase 2,000 bottles at $0.26
At the end of the year, 2,500 bottles remained in ending inventory. What is cost of merchandise sold and ending inventory under the FIFO, LIFO and average cost methods? Present your answers in the following form (in an excel spreadsheet):
Cost
Inventory Method Merchandise Inventory Merchandise Sold
a. FIFO $ $
b. LIFO
c. Weighted average cost
Please can this be answered NEATLY and easy to understand in an excel spreadsheet.
Thank You!
FIFO | LIFO | Weighted Average Cost | ||
The ending inventory | $640 | $500 | $560 | |
The cost of goods sold | $3,414 | $3,554 | $3,494 | |
FIFO | ||||
Units | Unit Cost | |||
500 | $0.24 | $120 | ||
2,000 | $0.26 | $520 | ||
Ending Inventory | $640 | |||
COGS = Beginning inventory + purchases - ending inventory | ||||
= | $1,200 + $2,854 - 640 | |||
= | $3,414 | |||
LIFO | ||||
Units | Unit Cost | |||
2,500 | $0.20 | $500 | ||
Ending Inventory | $500 | |||
COGS = Beginning inventory + purchases - ending inventory | ||||
= | $1,200 + $2,854 - 640 | |||
= | $3,554 | |||
Weighted Average Cost | ||||
Units | Unit Cost | |||
6,000 | $0.20 | $1,200.00 | ||
4,500 | $0.22 | $990.00 | ||
5,600 | $0.24 | $1,344.00 | ||
2,000 | $0.26 | $520.00 | ||
18,100 | $4,054.00 | |||
The average cost per unit | $0.224 | |||
Ending Inventory = 2,500 x $0.224 | $560 | |||
COGS = Beginning inventory + purchases - ending inventory | ||||
= | $1,200 + $2,854 - 560 | |||
= | $3,494 | |||