Can I please place a request to have a consolidated inventory quantity movement report exportable to Excel in one shot as per snapshot attached below?
This is because Manager allows us to sell even if net quantity goes negative as purchases can be made next day and delivered to customer same day (exactly my case). As invoice date is 1 day earlier and if any purchase is missed, often I have to see where any purchase entry might be missing?
Right now, we can click on inventory items quantity and item by item drill down 2-3 clicks for every item and then export to excel 1 by 1. I have close to 50 items and it takes time in extracting this information.
Therefore, a consolidated report of all inventory quantity movements would be useful and costing can also be added to this report to enable users to spot wrong data in 1 report extracted all together.
What you are asking for is not clear. You have combined descriptions of your purchasing practice with requests for report formatting with complaints about things not exporting. You show a spreadsheet where the column labeled “Qty on hand” actually seems to be composed of transaction quantities and “Balance” seems to be quantity on hand.
Based on my understanding of what you wrote, I fail to see why exporting the Inventory Items listing does not give you what you want. Your note says “Balance” does not come across in the export, but you are using different terminology than the program does. Everything in the Inventory Items listing comes across in the export, including the average cost and total cost you request.
Perhaps you can illustrate with a detailed example, not showing your own spreadsheet, but Manager pages. Explain what is missing from each view that you are trying to obtain.
This report to be extracted for all items in one go rather than item by item as now.
Balance to appear in export to excel same as appears on the software screen.
Add 2 columns for cost per unit and total cost along with the quantities for a comprehensive inventory movement report and analysis to enable reconciliation.
Now I see more of what you are explaining. The first problem is that in your initial screen shot, the terminology in the program is poor. What is labeled as “Qty on hand” is actually the quantity transacted. The “Balance” column shows the quantity on hand. This is because you drilled down on quantity on hand, so “Balance” applies to that.
But the page is showing a list of transactions, and the “Balance” column (quantity on hand) is grayed out.
This confusing display arises because of common coding for displays whether or not delivery notes and goods receipts are used. You can end up with a lot of columns in the Inventory Items listing, and you run out of obvious titles.
You are not going to get an expanded version of this screen because it is a drill-down on a limited set of data.
Further, I don’t see how what you are asking for helps with any kind of reconciliation. If you are using delivery notes and goods receipts, the Inventory Items listing gives you information that lets you know whether you are in a backorder situation, have delivered things you haven’t sold, etc. And the average and total costs are there.
If you haven’t read the three Guides on how to manage inventory, please do. All this is quite difficult to explain without illustrations. The Guides have them. There is also the Inventory Quantity Movement report, which might help you with what you want.
Irrespective of the titles, which may be A, B or C, as you asked to show Manager screens, I showed the same.
Reconciliation I will do myself of course it is simply not something I asked.
I am asking for a consolidated sales / purchase report quantity and cost movement for all items together, not item by item; and able to be copy/pasted to Excel. If cost columns are added, it is best.
All the data is available in the database, even if a report is not there, still there can be some custom report query to accommodate the request; and on top of it, consolidated sales/purchase report for all items at transaction level is a normal feature, not something out of the box that I have asked.
With all due respect, problem not solved and replies seem to be pointing towards a dead end to the guides (which I have read) rather than showing a new door to solve the problem or inefficiency faced due to lack of reporting feature.
@asifnwb, your request is quite clear. What you have not explained is why the existing listings and reports do not already provide what you need. The Inventory Quantity Movement and Inventory Value Movement reports include all items for which there has been activity during the defined reporting period. The Inventory Items listing includes quantity status, average cost, and total cost of all inventory items, regardless of whether there has been movement. I have tried to explain that your dissatisfaction with a particular drill-down report appears to come from the fact that the report you are looking at is the wrong report for what you seem to want.
Can you explain how you would use the report you are suggesting, as well as why the lists and reports I have referred you to do not meet your need?
Inventory qty and value reports are having all items but summary level. I requested for the same report at transaction level (purchase/sales invoice wise) and having export option.
Manager allows selling items even if quantity goes negative. I invoice someone in today’s date but item not in stock so I will buy tomorrow and deliver tomorrow. So today inventory goes negative. The idea is to have a consolidated report so I can see if inventory is negative due to this temporary 1-2 day difference or that I missed to enter any of my purchases but realized several months later.
Right now I have to click on each item qty on hand column and export transactions 1 by 1 to Excel which I can avoid with such report.
I still don’t see why you need the report you are describing. If your quantity goes negative because you have sold something you don’t have, that will appear in the Inventory Items listing with no drill-down or exporting required. So you know to purchase more. Why does it matter which specific transaction caused the quantity on hand or quantity owned to be negative? Sales and purchases of inventory items are not specifically associated with one another. They both subtract from or add to your stock. One does not cancel or fulfill another.
By clicking on the Qty on hand, if the negative is due to this 1-2 day then you should only see the negative once near the top, but if you see a lot of negative values then perhaps that indicates that you have missed your purchases - unless you regularly sell first and buy later.
@Tut@Brucanna Which specific transaction is important because… see below scenario.
I buy 10 qty on Jan 1
I sell 8 qty on Jan 21
I buy 5 qty on Jan 23 but forget to book the purchase
No transaction for several months
I sell 9 qty on Apr 8
Now balance qty will be -7, but not caused by recent transaction but something 3 months back.
This can become complicated if frequency of transactions is high and several months pass and also because missing one purchase can affect multiple items where many items come from the same supplier. Therefore, a consolidated transaction wise report can help as we can pull out this report on a month end, analyze what happened during the month and recognize anomalies that may only show up later and be difficult or time taking to resolve.
The point is not why I need the report. The point is it is a fairly basic report to have in the first place. I now have close to 50 items. If I will have 100 items tomorrow, who is going to go item by item, export 1 by 1, make 1 consolidated Excel file and analyze from there, then again we are reducing the software to a data collection bin rather than a value and efficiency adding tool.
Same way, although we have product wise profitability calculation for every item and every unit sold, but then why do we have a consolidated P&L for all items sold? Because nobody would want to take item by item profitability and then add it up to get to product profitability so that he can deduct the common expenses to arrive at net profit.
In a consolidated report, drill down is easier as all data is in one place but it is always difficult to add up manually to get a complete picture of the business.
I totally get the idea but not the logic - using your example - how does looking at those transactions indicate to you that you haven’t booked a purchase from 3 months back.
Based on your example it could still be a case of either oversold or under booked. What could tell you that it’s the under booked would be the comparison of the stock on the shelf (7) v’s the inventory item (-7). If it was just the oversold then the inventory item would be -2 as you have 7 on the shelf
My suggestion to you would be this, click on the Qty on hand column heading so that all the negative inventory items are listed at the top. Export that part of the list. Go to the stock shelves, if the shelf is empty for that inventory item then probably oversold, however if the shelf does have the inventory item then probably under booked.
Looking at the physical inventory situation is more validating then looking at historical transactions.
My second suggestion would be this, if your supplier hasn’t chased you after three months for payment of that non booked purchase, then keep buying from that supplier - alternatively if the supplier sends you month end statements then use those statements to either reconcile their supplier account or use it as a comparison when making your payment.
My third suggestion would be this, as your inventory increases then the importance of physical stock counts also increases. You don’t have to count all the items all of the time - it can be on a revolving basis - 10 item this week / month etc.
Your workarounds are welcome. However, differing situations arise and good reports are never a luxury, almost a necessity. As I said, the accounting software is not just a data bin, but value adding tool. My request for consolidated inventory movement report remains as it is.
I don’t know why additional feature requests are so heavily defended and opposed.
Anyway, @Brucanna@Tut thanks for trying to understand my problem and putting your efforts to respond. Topic closed.
