Cost of Goods Sold Calculation Using FIFO Accounting in Excel

Overview

The First In, First Out (FIFO) method assumes that the oldest inventory costs are used up first when goods are sold. This approach is essential for accurately calculating the Cost of Goods Sold (COGS) in financial statements.

Key Components

To calculate COGS using FIFO in Excel, you need:

• Inventory Data: A list of inventory purchases, including quantities and costs.
• Sales Data: A record of sales transactions, specifying the quantities sold.

Sample Data Structure

1. Inventory Table:

   Purchase Date	Quantity	Unit Cost
   2023-01-01	100	10
   2023-02-01	150	12
   2023-03-01	200	15

2. Sales Table:

   Sale Date	Quantity Sold
   2023-02-15	100
   2023-03-10	200

Excel Formula for COGS Calculation

The calculation involves multiple steps due to the nature of FIFO. Here’s a structured approach to creating the formula.

1. Identify the Total Quantity Sold: You will need a way to track how much has been sold against the available inventory quantities.

2. Calculate COGS: Create a formula that iteratively checks the inventory, deducts the quantities sold, and accumulates the cost.

Formula Example

Assuming your Inventory data is in columns A (Purchase Date), B (Quantity), C (Unit Cost) starting from row 2, and your Sales data is in columns E (Sale Date) and F (Quantity Sold) starting from row 2, you can create a helper column to track COGS.

Cumulative COGS Calculation

To calculate COGS for each sale in the Sales Table:

=SUMPRODUCT((B2:B4)*(F2-SUMIF(E$2:E2,E2,F$2:F2-(F2-F$2)))*(F2-SUMIF(E$2:E2,E2,F$2:F2) < B2:B4)/ SUMIF(F$2:F2,"<="&SUMIF(E$2:E2,E2,F$2:F2),(1*COUNTIF(F$2:F2,F2<=(ROW(F$2:F2)-ROW(F$2)+1),1)*ROW(F$2:F2))) > 0)

Explanation of the Formula

• SUMPRODUCT: This function multiplies arrays and sums the results. Here, it is used to calculate COGS.
• (B2:B4): Represents the quantities available in the inventory.
• SUMIF function: Used to track the remaining quantity as sales occur cumulatively across rows.
• Logic Structure: The formula deducts from the inventory based on the sales from the earliest date first (FIFO), until the total sold quantity is matched.

Example Application

For the provided data:

• Quantity Sold on 2023-02-15 = 100
  • COGS = (100 units @ $10/unit) = $1,000
• Quantity Sold on 2023-03-10 = 200
  • 50 units @ $10/unit from the first batch + 150 units @ $12/unit from the second batch.
  • COGS for this sale = (50*$10) + (150*$12) = $500 + $1,800 = $2,300

Final COGS Total Calculation

To sum up the COGS for all sales:

=SUM(COGS from each sale calculated above)

Conclusion

This structured approach allows for accurate COGS calculations based on FIFO assumptions. For more complex inventory management and accounting scenarios, it is recommended to explore extensive data analysis techniques available on the Enterprise DNA platform. This method not only enhances data accuracy but also helps in financial forecasting and decision-making.