Perpetual Inventory Accounting: FIFO Method (Excel Example)¶
This document demonstrates the perpetual inventory accounting method using the First-In, First-Out (FIFO) assumption. We'll recreate the exercise in a format that's easy to follow, similar to how it would be done in a spreadsheet program like Excel.
Provided Data¶
We have data on receipts (purchases) and shipments (issues) of inventory over a six-month period:
Receipts (Purchases)
| Date | Units | Rate (₹) |
|---|---|---|
| January 1 | 500 | 200 |
| February 1 | 600 | 230 |
| March 1 | 800 | 210 |
| April 1 | 1,000 | 180 |
| May 1 | 700 | 220 |
| June 1 | 400 | 250 |
Issues (Shipments)
| Date | Units |
|---|---|
| January 15 | 400 |
| February 15 | 500 |
| March 15 | 900 |
| April 15 | 800 |
| May 15 | 500 |
| June 15 | 600 |
FIFO Method Explanation¶
FIFO (First-In, First-Out) assumes that the oldest inventory items are sold first. Therefore, the cost of goods sold (COGS) reflects the cost of the oldest purchases, while the ending inventory reflects the cost of the most recent purchases.
Perpetual Inventory Tracking (FIFO)¶
Here's a step-by-step breakdown, mimicking an Excel spreadsheet:
| Date | Receipt/Issue | Units | Rate (₹) | Value (₹) | Balance Units | Balance Value (₹) |
|---|---|---|---|---|---|---|
| Jan 1 | Receipt | 500 | 200 | 100,000 | 500 | 100,000 |
| Jan 15 | Issue | 400 | 200 | 80,000 | 100 | 20,000 |
| Feb 1 | Receipt | 600 | 230 | 138,000 | 700 | 158,000 |
| Feb 15 | Issue | 500 | 200 | 46,000 | ||
| Issue (Jan Stock) | 100 | 200 | 20,000 | |||
| Issue (Feb Stock) | 400 | 230 | 92,000 | |||
| Mar 1 | Receipt | 800 | 210 | 168,000 | 1,000 | 214,000 |
| Mar 15 | Issue | 900 | 100 | 21,000 | ||
| Issue (Feb Stock) | 200 | 230 | 46,000 | |||
| Issue (Mar Stock) | 700 | 210 | 147,000 | |||
| Apr 1 | Receipt | 1,000 | 180 | 180,000 | 1,100 | 201,000 |
| Apr 15 | Issue | 800 | 300 | 54,000 | ||
| Issue (Mar Stock) | 100 | 210 | 21,000 | |||
| Issue (Apr Stock) | 700 | 180 | 126,000 | |||
| May 1 | Receipt | 700 | 220 | 154,000 | 1,000 | 208,000 |
| May 15 | Issue | 500 | 500 | 108,000 | ||
| Issue (Apr Stock) | 300 | 180 | 54,000 | |||
| Issue (May Stock) | 200 | 220 | 44,000 | |||
| Jun 1 | Receipt | 400 | 250 | 100,000 | 900 | 210,000 |
| Jun 15 | Issue | 600 | 300 | 75,000 | ||
| Issue (May Stock) | 500 | 220 | 110,000 | |||
| Issue (Jun Stock) | 100 | 250 | 25,000 | |||
| Totals | Receipt | 4,000 | 840,000 | |||
| Issue | 3,700 | 765,000 |
Explanation of Calculations¶
- Receipt: When goods are purchased, the units, rate, and value are recorded. The balance units and value are updated.
- Issue: When goods are sold (issued), the FIFO method is applied. This means the oldest units in stock are assumed to be sold first.
- If the issue quantity is greater than the oldest stock available, the issue is split into multiple parts, each using the cost of the respective purchase lot.
- Balance: The balance units and value are calculated after each receipt and issue.
Key Observations¶
- Total Units Purchased: 4,000
- Total Units Issued: 3,700
- Ending Inventory (Balance Units): 300
- Ending Inventory Value: ₹75,000
- Cost of Goods Sold (COGS): ₹765,000 (Sum of all issue values)
Inventory Accounting: FIFO, LIFO, and Weighted Average (Excel Example)¶
This document demonstrates three common inventory valuation methods: First-In, First-Out (FIFO), Last-In, First-Out (LIFO), and Weighted Average. We'll use the same data as before and present the calculations in a way that resembles a spreadsheet.
Provided Data¶
Receipts (Purchases)
| Date | Units | Rate (₹) |
|---|---|---|
| January 1 | 500 | 200 |
| February 1 | 600 | 230 |
| March 1 | 800 | 210 |
| April 1 | 1,000 | 180 |
| May 1 | 700 | 220 |
| June 1 | 400 | 250 |
Issues (Shipments)
| Date | Units |
|---|---|
| January 15 | 400 |
| February 15 | 500 |
| March 15 | 900 |
| April 15 | 800 |
| May 15 | 500 |
| June 15 | 600 |
1. FIFO (First-In, First-Out)¶
(As explained in the previous response, this section is included for completeness.)
2. LIFO (Last-In, First-Out)¶
LIFO assumes that the most recently purchased inventory items are sold first.
| Date | Receipt/Issue | Units | Rate (₹) | Value (₹) | Balance Units | Balance Value (₹) | Details (Balance Split) |
|---|---|---|---|---|---|---|---|
| Jan 1 | Receipt | 500 | 200 | 100,000 | 500 | 100,000 | 500 @ 200 |
| Jan 15 | Issue | 400 | 200 | 80,000 | 100 | 20,000 | 100 @ 200 |
| Feb 1 | Receipt | 600 | 230 | 138,000 | 700 | 158,000 | 100 @ 200, 600 @ 230 |
| Feb 15 | Issue | 500 | 230 | 115,000 | 200 | 43,000 | 100 @ 200, 100 @ 230 |
| Mar 1 | Receipt | 800 | 210 | 168,000 | 1,000 | 211,000 | 100 @ 200, 100 @ 230, 800 @ 210 |
| Mar 15 | Issue | 800 | 210 | 168,000 | 200 | 43,000 | 100 @ 200, 100 @ 230 |
| Apr 1 | Receipt | 1,000 | 180 | 180,000 | 1,200 | 223,000 | 100 @ 200, 100 @ 230, 1000 @ 180 |
| Apr 15 | Issue | 800 | 180 | 144,000 | 400 | 79,000 | 100 @ 200, 300 @ 180 |
| May 1 | Receipt | 700 | 220 | 154,000 | 1,100 | 233,000 | 100 @ 200, 300 @ 180, 700 @ 220 |
| May 15 | Issue | 500 | 220 | 110,000 | 600 | 123,000 | 100 @ 200, 300 @ 180, 200 @ 220 |
| Jun 1 | Receipt | 400 | 250 | 100,000 | 1,000 | 223,000 | 100 @ 200, 300 @ 180, 200 @ 220, 400 @ 250 |
| Jun 15 | Issue | 600 | 400 | 79,000 | 100 @ 200, 300 @ 180 | ||
| Issue (Jun Stock) | 400 | 250 | 100,000 | ||||
| Issue (May Stock) | 200 | 220 | 44,000 | ||||
| Totals | Receipt | 4,000 | 840,000 | ||||
| Issue | 3,700 | 784,000 |
3. Weighted Average Method¶
The weighted average method calculates a new average cost after each purchase. This average cost is then used to value subsequent issues.
| Date | Receipt/Issue | Units | Rate (₹) | Value (₹) | Balance Units | Balance Value (₹) | Average Rate (₹) |
|---|---|---|---|---|---|---|---|
| Jan 1 | Receipt | 500 | 200 | 100,000 | 500 | 100,000 | 200 |
| Jan 15 | Issue | 400 | 200 | 80,000 | 100 | 20,000 | 200 |
| Feb 1 | Receipt | 600 | 230 | 138,000 | 700 | 158,000 | 225.71 |
| Feb 15 | Issue | 500 | 225.71 | 112,855 | 200 | 45,143 | 225.71 |
| Mar 1 | Receipt | 800 | 210 | 168,000 | 1,000 | 213,143 | 213.14 |
| Mar 15 | Issue | 900 | 213.14 | 191,826 | 100 | 21,317 | 213.14 |
| Apr 1 | Receipt | 1,000 | 180 | 180,000 | 1,100 | 201,317 | 183.01 |
| Apr 15 | Issue | 800 | 183.01 | 146,408 | 300 | 54,909 | 183.01 |
| May 1 | Receipt | 700 | 220 | 154,000 | 1,000 |
How can I help you today?