How to Create a FIFO Excel Spreadsheet

A side hustle is a wonderful way to grow personal wealth, but starting one comes with a learning curve. It is vital to choose which accounting methods you want to use as you make sales from inventory. FIFO is one popular accounting method that can help you keep track of profits in a way that maximizes your net income. While you could do FIFO by hand, it is relatively simple to use FIFO in Excel, as long as you understand the basics of how this accounting method works.

First In, First Out, also known as FIFO, is a method of accounting where items are used in the order in which they are purchased. According to Investopedia, this means that any items remaining at the end of the period are items that were purchased most recently. Some people use Last In, First Out (LIFO) instead of FIFO. In LIFO, the last items purchased are the first out, so it is the exact opposite of FIFO. However, FIFO will usually result in a higher net income, so when this is a desirable outcome, FIFO is generally used.

In order to create a method for FIFO in Excel, it is vital to create purchase record columns that record the following data for each purchase:

• Date
• Product
• Cost Per Item
• Number of Items
• Total Cost

Simply record your purchases in the order that they are made, recording information accordingly in each Excel spreadsheet column.

For instance, let's say your accessory business purchases 25 pairs of earrings for $5 per pair in January. You might choose to buy 25 more in February, but now they cost $5.25, and then you buy 25 again in February at the cost of $4.85 per pair. Each of these purchases will have its own line in your Excel spreadsheet.

In FIFO, the first items purchased are the first to be sold. You will need to include the following columns in your Excel spreadsheet to record your sales:

• Date
• Product
• Quantity Sold
• Price Per Item Sold
• Total Selling Price
• Cost Price Per Item
• Total Cost
• Profit

In the case of an accessory business, these columns will help you to keep track of how many pairs of earrings you have sold and what the profit is.

Let's say you hold an online sale in March to begin selling the earrings you have purchased for your side hustle. One customer buys 10 pairs of earrings as Christmas gifts. To use FIFO in Excel, you will record this purchase with the cost price per item that you paid for the first earrings you bought in January, which was ​$5​. If your customer paid ​$10​ per pair of earrings, this means that you made a ​$50​ profit on your investment and your Excel spreadsheet will reflect this with one simple line of entries.

According to FreshBooks, things get more complex once you have sold your initial purchase of earrings and have two price points in one transaction. Your first customer bought 10 of the 25 pairs of earrings you purchased in January, which means there are only 15 pairs remaining. Perhaps your next customer purchases 25 pairs of earrings for a local youth group. This means that 15 pairs of earrings will use January's cost, but the other 10 will use February's cost.

To make this entry in Excel, enter the sale date and product into your Excel spreadsheet, as you usually would. When it comes to the quantity column, you will need to make use of two rows. One row will reflect January's ​$5​ cost for 15 pairs of earrings. In the same entry, but one row down, you will record February's ​$5.25​ cost for 10 pairs of earrings. Excel will calculate your profit for each cost level. If the youth group paid ​$10​ per pair of earrings, your profit for January's earrings will be ​$75​, while the profit for February's earrings will be ​$47.50​, for a total profit of ​$122.50​.