ETFFIN Finance >> ETFFIN > >> Personal finance >> investing

FIFO Inventory Method in Excel: A Step-by-Step Guide

Accountants use spreadsheets and special methods for valuing and tracking inventory.

An accounting term, FIFO refers to the first-in-first-out method of inventory asset management and valuation. Unlike its sister methodology, last-in-first-out, the term defines that the first products put into inventory are the first inventory items taken out. In periods of rising prices, this means that older inventory -- the first inventory in -- has a lower value on the books, leaving newer and more expensive inventory on the balance sheet. This has the effect of decreasing the cost of goods sold, while increasing net income. For tax reasons, the IRS only allows companies to settle on one method of valuation without switching between them during the year without authorization.

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.

Tip

Once you add the Excel formula, which equates to the cells listed, you can copy and paste the formula down the column for "Ending Inventory" so you don't have to re-create it each time.