Using Google Sheets
Most of us work in businesses that already have their accounting software, that calculates for us all the account balances. We do not need to prepare agings, schedules, or inventory lists since the system takes care of us. However, several times your software does not accomodate your ideal solution for calculating an account balance. In this post I want to give you an idea to solve the inventory calculation method known as FIFO using Google Sheets.
First In, First Out
This method of valuing inventory assumes that the earliest goods purchased are the first sold. Thus, the FIFO method attempts to match the normal flow of merchandise in a business. Generally, businesses try to sell first the oldest units purchased. The cost of the earliest goods purchased determine the cost of goods sold. Remember, this is for valuation costing purposes, this does not necessarily mean that the oldest units are sold first.
SOLUTION:
In the above image, I have created a spreadsheet showing one way of solving for FIFO and Cost of Goods Sold (COGS). First, please note that I have segregated the data for easy understanding and presentation. The idea is to solve what we want and present the result nicely.
The Data:
Before calculating the ending inventory valuation with FIFO, lets prepare the data: column A, shows the date of either the beginning balance or purchases made; column B, Description of the transaction. Next, column C, shows, how many units were available; column D, the unit cost. The column E, multiplies columns C times D. Column F, represent the cumulative units purchased over the reporting period.
The FIFO Formula:
=if(F8<$C$13,0,if(and(F8>$C$13,$C$13>F7), (F8-$C$13)*D8,C8*D8))
This nested formula test for various conditions at once; therefore, each cell will result in the correct amount based of FIFO.
First test: =if(F8<$C$13,0 we want to test if the cumulative units is less than the sold units, if it is true, then units should be 0. Why? Because that means those items were sold.
Second test: if(and(F8>$C$13,$C$13>F7), (F8-$C$13)*D8 if the first test fails (FALSE) then we want to test if the sold units falls between the current and prior cumulative units, if TRUE then we substract the cumulative amount less the sold units, and times the unit cost. The result shows the starting for calculating the ending inventory.
Third test: C8*D8)) if the first and second tests fails, then just multiply units times unit costs, this represent ending inventory at cost.
Cost of Goods Sold (column H) to obtain the cost of goods sold, just substract the extended cost less the calculated ending inventory.
Testing the Result:
In lines 17 to 20, I included a manual test of units that represent the ending inventory with the unit costs; the amount equals to our calculation. Therefore, our spreadsheet is correct.
Conclusion:
Since the creation of spreadsheets, we all found a great tool to calculate and solve several of our problems. This solution represent one of several possibilities and depending on your needs, you can modified it. Explore more on it, and remember that you can solve personal and business calculations with spreasheets.
I understand that sometimes, you do not have the time to explore and put together spreadsheet solutions. To ease up things, I have included a spreadsheet with this methodology, it is only $3 to cover the costs of running this website and keep adding new material. You can modify, update or change the spreadsheet to accommodate your needs.
First In, First Out Spreadsheet
FIFO spreadsheet, include example and empty sheet for your use.
Please add some comments, requests of new material or spreadsheets. I want to improve your site experience and keep you as a regular visitor.
Please subscribe to our webpage, and be notify when new blogs are available!