Do you allow negative inventory?

The usual approach would include:

1. Ensuring all batches are posted

2. Ensure you run the Item Valuation using transaction details, specifying a Fiscal Year / Period cut-off

3. Review your I/C Account Sets to ensure you're considering all G/L accounts in play

4. Ensure that there aren't any transactions from other modules in your I/C control account (check for source codes that shouldn't be there such as GL-* )

5. Ensure that no one modified the Fiscal Year / Period on batches in the G/L that came from another module

Good luck!

Yes we allow negative inventory.

Negative inventory invariably screws up costing, that's why I never allow it at my clients unless they use standard costing.  Do a query from ICHIST in chronological order and trace the costs, it's likely you'll find the problem.

Is there a report I can run, or do I need to have a programmer run a query on that table?  In SagePro I was able to do Dataviews, but I have not been taught queries in Sage300.  Is there documentation that can show me?

Thank you

Jeff

Run the IC Transaction History report.

Also, we had allowed the negative inventory because we have a few products that we make on demand.  So the order is taken and then we produce the inventory.  If I change the setup to be not to allow negative inventory - can we still take orders without have the product on hand?

Where is the History report?

We are running Sage300 ERP 2012.  I see a Stock Transactions report, is that it?

yes, you can still take orders for items. you just can't "commit" them unless you have stock.  

IC Transaction history is under INVENTORY CONTROL / IC STATISTICS AND INQUIRIES then choose TRANSACTION HISTORY INQUIRY

IC Price/Sales Analysis Reports, Transaction History

In order for us to keep on top of this we have an automatic daily report listing us GL inventory balances and IC inventory values. This way we can address the problem right away and saves us having to go back through the transaction history.

Jay said to run the report in Chronological order, do I do that by entering in the As at Year/Period and then just accepting the defaults?

I told you do to a query, not run a standard report.

Run a query on ICIVAL and sum TRANSCOST by year and period (you need a running total by year & period), then compare that to the GL.

The total of TRANSCOST in ICIVAL should agree with the GL balance in period 12 of the current year if all GL batches have been posted.

Alternatively have a Crystal report written to do the above.

Thank you for expanding on what to do for a query.  Again, I am just learning this and I don't understand all the fine details of the inventory issues.  What report in Sage300 would I compare this information to help me identify if one of the item costs has a problem.  Jay mentioned running a query on ICHIST - is that just a different angle to getting the same information?  What specifically am I looking for - is it an item whose unit cost has changed for some unexplained reason?  Jay mentioned that because we allow Negative Inventory - that may be causing a problem.  Should I be looking for items that went negative at some point?  If you can help me understand a little further - I would be appreciative.

Also - if we determine we need to change the option to NOT allow negative inventory - is there a better time to do that - and are there any problems changing that setting will cause?

Thank you again for your assistance.

Allowing negative inventory is never a good idea, no matter what compelling argument/reasons you may have.

The IC Transaction History report is the best stock report to highlight cost issues. Print it for a specific item and review the costs for all receipts and shipments, take note of the changes in average costs.

The ICHIST and ICIVAL tables contain almost the same data - the difference is that ICHIST (transaction history) can be cleared and ICIVAL can never be cleared, so ICIVAL should always have the complete history. They can mostly be used interchangeably.

Just to expand on my previous post - I did a conversion from another program to Sage 300 in August last year. The accountant has complained since then that the inventory never reconciled with the GL. I did an analysis using ICIVAL as in my post and compared it to the GL. The only difference I found was $600, amounts posted in Nov and Dec in IC were posted in Jan in the GL. I think that they back dated some transactions in IC and these posted to Nov and Dec, but since those periods were closed they were rejected in the GL - I found the error batch in the GL. The period was changed to Jan and posted in the GL - hence the difference. In the end everything reconciled to the penny.

If we determine that we need to NOT allow negative inventory, are there any repercussions for changing it?  And do we need to time the change to an end of month or anything like that?

Thank you again for your detailed response.

Don't over think it... you can change it any time. Keep in mind that if you change it it takes immediate effect and you need to deal with the issues that may arise. Repercussions are that you cannot have negative inventory.

I have started to look at trying to compare to the GL.  I am sorry for asking so many questions, but I feel I am missing something.  I haven't found a GL report that shows me what each ITEM should have as a balance - I am not even sure I have found a report that shows me the activity for the month on a GL.  

So in comparing ICIVAL to the GL, am I really just looking to see what the total change in ICIVAL was for a period as compared to the total GL change for the inventory accounts?  Once I determine a period, then do I use IC Tran History report to review each item for one that looks odd?  We use FIFO for costing, is the 'Average Cost' you mention a field I see - or do I need to calculate that based on the IC Tran History report for every transaction (If that is the case, you mean export it to Excel and then calculate it so I can see any change taking place.)   Does it sound like I am on the right track?  Or have I missed something?

If you sum ICIVAL.TRANSCOST by year and period then you get the net movement in IC for each year/period - you can compare that to the net movement in the GL.

By ave cost I mean cost/qty for each transaction - not specifically referring to the average costing method. Regardless of costing method used, the ave cost for each transaction will give you a gauge on unit cost and you will see where things go wrong.