How to Create a FIFO Excel Spreadsheet

How to Create a FIFO Excel Spreadsheet
Accountants use spreadsheets and special methods for valuing and tracking inventory.

Step 1

Review the inventory equation to understand how to set up a spreadsheet for FIFO. The equation is Beginning Inventory + Net Purchases - Cost of Goods Sold = Ending Inventory. The FIFO method means the first product that goes into inventory is the first product sold.

Step 2

Open an Excel spreadsheet. Create columns with the following column heads: "Beginning Inventory," "Net Purchases," "Cost of Goods Sold" and "Ending Inventory."

Step 3

Type in the amount of your beginning inventory. Let's say you own a coffee shop and make 100 cups of coffee that sell for a cost of $1 and 100 more the next day that sell for an average cost of $2 each.

Step 4

Label the first row under the column headings as "Day 1." Beginning inventory value for day one is 100 cups of coffee at a cost of $1.The total cost of beginning value is $100. Label the second row "Day 2" and the value is now $200, or $2 times 100.

Step 5

Add the number of purchases you make to replenish your inventory to the spreadsheet. Let's say you purchase 100 cups of coffee for a price of $3 a cup. Enter this amount in column two as net purchases.

Step 6

Type in the cost of the first cups of coffee as the cost of goods sold in the next column. You sold 200 cups of coffee. The first 100 cups of coffee cost $100 and the next 100 cost $2. The total cost of goods sold for these days is $300.

Step 7

Calculate ending inventory using the equation typed into the spreadsheet field for ending inventory based on each column's heading: Beginning Inventory + Net Purchases - Cost of Goods Sold = Ending Inventory. This should be $300 + $300 - $300 = $300.