Quickbooks is so popular that it almost has a cult Following, but its not without its shortcomings. One of the featrues it lacks is the ability to generate a Stock Ageing report. The Report that shows you which stock has been lying with you for how many days and its value. This report is indispensabel for anybody in Distribution business with inventory.
In Quickbooks Enterprise Solution 17.0, there is a workaround to get this report. However its only a workaround and as the number of items and transactions grow, it becomes more time-consuming to get this report.
- Enable Inventory First in First out.
a. Go to Edit > Perferences > item and inventory
b. Click on “Advanced Inventory Settings”
c. tick “Use FIFO” and a start date.
2. Under Reports > Inventory , Find the report “FIFO LOT COST HISTORY BY ITEM”.
Now this report is very interesting. It shows you the purchase date of each item, and a sales date of that item. Any item that only has a purchase date, and no sales date is in your stock. Your Ageing is the time elapsed from the purchase date.
3. Export it to Excel and Filter only the transactions that have no Sales date / Disbursment Date.
4. Your ageing for such stock is date today – the date of purchase.
I know its a long way , but at least you would have some idea on the items that have been with you in stock for long time. I also wrote a python script to take in the CSV exported from Quickbooks and turn it into 0-30,30-60,60-90,+ ageing report. In my next post I will share the details of the script too.