How to create a FIFO excel spreadsheet
FIFO refers to "first in, first out." It is an asset management and valuation methodology used to manage inventory for accounting purposes. Unlike its sister methodology, LIFO (last in, first out), FIFO means that the first inventory in is the first inventory out.
In periods of rising prices, this means that older inventory (the first inventory in) will be cheaper on the books, leaving newer (more expensive) inventory on the balance sheet. This has the effect of decreasing the cost of goods sold, which increases net income. For tax reasons, the IRS will not allow companies to switch between FIFO and LIFO unless authorised.
Review the inventory equation. It is: Beginning Inventory + Net Purchases - Cost of Goods Sold (COGS) = Ending Inventory. The FIFO method means the first product that goes into inventory is the first product sold.
- FIFO refers to "first in, first out."
- Unlike its sister methodology, LIFO (last in, first out), FIFO means that the first inventory in is the first inventory out.
Open an Excel spreadsheet. Create columns with the following column heads: Beginning Inventory, Net Purchases, Cost of Goods Sold and Ending Inventory.
Input 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 60p and 100 more the next day that sell for an average cost of £1.30 each.
Label the first row under the column headings as Day 1. Beginning inventory value for Day 1 is 100 cups of coffee at a cost of 60p. The total cost of beginning value is £65. Label the second row Day 2 and the value is now £130 ($2 x 100).
- Open an Excel spreadsheet.
- Label the second row Day 2 and the value is now £130 ($2 x 100).
Input the number of purchases you make to replenish your inventory. Let's say you purchase 100 cups of coffee for a price of £1.90 a cup. Enter this amount in column 2 (net purchases).
Input the cost of the first cup of coffee as the cost of goods sold first in the next column. You sold 200 cups of coffee. The first 100 cups of coffee cost £65 and the next 100 cost £1.30. The total cost of goods sold for Day 1 and Day 2 is £195.
- Input the number of purchases you make to replenish your inventory.
- Enter this amount in column 2 (net purchases).
Calculate ending inventory. Using the equation: Beginning Inventory + Net Purchases - Cost of Goods Sold (COGS) = Ending Inventory create an equation in the Ending Inventory Column. This is £195 + £195 - £195 = £195.
Working as a full-time freelance writer/editor for the past two years, Bradley James Bryant has over 1500 publications on eHow, LIVESTRONG.com and other sites. She has worked for JPMorganChase, SunTrust Investment Bank, Intel Corporation and Harvard University. Bryant has a Master of Business Administration with a concentration in finance from Florida A&M University.