Thread: Inventories FIFO

I need help finding the gross profit which is $11,420 and setting this problem up in excel. I have been working this problem a week straight and getting the wrong answer.

Date Transaction Number of Units Per Unit Total
Jan 1 Inventory 50 $20 $1,000
7th Purchase 200 $22 $4,400
20th Sale 90 $40 $3,600
30th Sale 110 $40 $4,400
Feb 8 Sale 20 $44 $880
10th Purchase 130 $23 $2,990
27th Sale 90 $42 $3,780
28th Sale 50 $45 $2,250
Mar 5 Purchase 180 $24 $4,320
13th Sale 90 $50 $4,500
23rd Purchase 100 $26 $2,600
30th Sale 80 $50 $4,000

1. Record the inventory, purchases, and cost of merchandise sold data in a perpetual inventory record using the first in, first out method.

2. Determine the total sales and the total cost of merchandise sold for the period. Journalize the entries in the sales and cost merchandise sold accounts. Assume that all the sales were on account.

3. Determine the gross profit from sales for the period. ($11,420)
4. Determine the ending inventory cost.

Originally Posted by curlyman

Date Transaction Number of Units Per Unit Total
Jan 1 Inventory 50 $20 $1,000
7th Purchase 200 $22 $4,400
20th Sale 90 $40 $3,600
30th Sale 110 $40 $4,400
Feb 8 Sale 20 $44 $880
10th Purchase 130 $23 $2,990
27th Sale 90 $42 $3,780
28th Sale 50 $45 $2,250
Mar 5 Purchase 180 $24 $4,320
13th Sale 90 $50 $4,500
23rd Purchase 100 $26 $2,600
30th Sale 80 $50 $4,000

Total items purchased (includes inventory): 50+200+130+180+100 = 660
Total items sold: 90+110+20+90+50+90+80 = 530

So 660-530 = 130 items are left, made up of (from last purchases):
Mar 23: 100 @ 26 = 2600
Mar 5: 30 @ 24 = 720
Total of 3320 = ending inventory

Sales : 23410
Purchases: 15310 (includes inventory)
Profit: 23410-15310 = 8100
Add ending inventory: 8100 + 3320 = 11420

That's how 11,420 is arrived at.
I'm not familiar with Excel...so good luck...