Stock Status Issue

[Luvsql]

We have been able to keep out stock status relatively in sync with our GL (stock status, not historical). We are now out by over $5000. I have verified the transactions for the month of June for SOP, POP and Inventory match the total net change value of the inventory accounts in the GL as of June 30, 2004, so everything is hitting the GL correctly. If this is the case, how can we prove that the stock status is correct? We believe our GL is the right number because we have proved the numbers by reconciling every transaction since January.

How can we use the stock status if it is not correct? We have every item marked to revalue at 0%, so any cost difference on our invoice matchings would hit the stock status. We have checked everything. We have gone over TK from MBS. I don't believe the purchase receipts table is right.

 

[Dukester0122]

Can you be more specific where you're getting the numbers so we know how to solve the problem. What numbers are you comparing, is it the Stock Status against the Quantity On Hand?

 

[Luvsql]

We are printing the stock status by Class ID. Each of our classes hit a different GL code, therefore the Stock Status total value for Class A should equal the ending balance of that Account in the GL. This has been the case up until the last few weeks. The stock status takes the qty on hand and values it depending on your valuation method. This value is supposed to be what our value of our inventory accounts are. If every transaction can be accounted for in the GL and the stock status value matched that of the GL one month ago, how can it not match now, if every transaction can be accounted for?

 

[Dukester0122]

There are 2 reports for Stock Status and I think you're using the Historical Stock Status because the Stock Status doesn't have Item Class as Ranges.

Were you using the Historical Cost for Receipt and Issue Transactions?

Another method of getting the difference would be to run a report or a sql query that would show all transactions for this Class on a certain period(in your case June1-30) against table IV30300 then deduct this with your June30 ending balance to get May30 balance then compare with what you had before. Try to get the results in QTY and COST because sometimes the QTY is okay but the cost isn't.

Tell me what results you get.

 

[Luvsql]

I'm not using the historical stock status. I have modified the existing stock status and added the group by item class. I have verified that the unmodified and modified have the same grand total as well I have even created a crystal report that yields the same grand total.

As I have stated, I have been able to reconcile our transactions in inventory to our GL by date. This is not the issue. Our issue is the inventory value that the stock status says, which we give to our auditors, is not what our GL says. This is a huge issue. We need an ending balance inventory value ie what is the value right now and what is the value in our Gl right now. These numbers don't match.

 

[barbola]

I would suggets running a historical stock status and verify the beginning balance with last month's ending balance.

If an adjustment or other transaction was posted to your previous period, you will not see the transaction if you are looking at only the current month.

Thanks!
Barb E.

 

[Luvsql]

We always post to previous month's as we batch date. We look at the current stock status because we need a current value ie value right now as compared to the GL. The historical stock status is a mess and takes hours to generate.

 

[Luvsql]

I just printed the historical stock status for June 30th and it is off by $60,000 (over the GL).

 

[GPSKid]

What version are you on?

As I understand it, prior to Version 7.5 SP 3, there was a problem with the Historical Stock Status Report giving incorrect values when there is a purchase price variance. So this might explain your $60,000 variance. There is an item in Techknowledge about this.

Another thing to check, is what ledger account are your purchase price variances being posted to. These variance amounts need to added/subtracted to the GL inventory account balance inorder reconcile to the stock status report.

Good Luck, Gene

 

[Luvsql]

We don't use the historical stock status because there are just too many issues with it (just wanted to show how bad it was and we are on 7.5). That is why we stop all posting, then print our stock status, then our GL. The issue isn't with postings hitting one period or the other as we can reconcile the transactions to the GL. The issue is the stock status report itself is not getting updated properly (obviously since we can reconcile the transactions themselves the to GL balance net change).

 

[jazgeek]

Hey Luvsql...

what's your inventory valuation method? Could a item cost have changed manually?



-----------
and they wonder why they call it Great Pains!

jaz

 

[Luvsql]

We use FIFO perpetual and have 6 inventory sites, each going to their own separate inventory accounts.

 

[jazgeek]

There's your problem

current cost gets updated for any number of reasons

unfortunately the gl does not revalue

for example

10 pcs of Part A were received at $100 ea

inventory value is now $100x10 = $1000 right?

now 2 pieces were bought at a premium of $110
GL gets hit with $220 more dollars and GL inventory shows
$1220

inventory value is now what? you would expect $100x10 + 2X$110

it is instead 12X$110 = $1320


you are now $100 out of balance with the GL


Look for purchase discounts/increases. Inventory transactions with wrong costings, multicurrency variances in purchase pricing. Inventory being driven below zero (bad bad bad) and of course inventory transactions not marked as post to GL.

This is a common issue and not avoidable.





-----------
and they wonder why they call it Great Pains!

jaz

 

[GPSKid]

What we need is a report that list all of the transactions in inventory over a period time. The report should show date, qty's, unit amounts,total amounts and hopefully a reference to the related document. This report then could be compared to what hit the ledger.

Any ideas on how to produce such a report.

 

[Luvsql]

I have created a report like that and use to verify inventory transactions to our GL numbers on a monthly basis, but it's not really a value ie I can't use it to find out the value of each item number. I can find the total qty and the amounts that hit each gl account, but not how much each item is valued at.

If I needed to know my value now, I would have to start at day one, which covers over 3 years worth of data. The issue I also have when trying to come up with this report is when you transfer between sites ie if from Site A to Site B, but both site A and B use the same GL codes. There is no affect on the GL , but the qtys change and the value for that site changes. We have transfers from Site A to Site E, which use a different segment, so those have an affect on the GL and qty and value of each site.

 

[GPSKid]

Can you tell me about your inventory transaction report? It sounds like something we could use.

 

[Luvsql]

This is the view I created to match my monthly inventory transactions to my NET CHANGE GL balance. Hope it helps!!

SELECT POP30390.POPRCTNM as DocNo, POP30390.CRDTAMNT as Credit, POP30390.DEBITAMT as Debit,
POP30300.receiptdate as DocDate, POP30300.GLPOSTDT as GLDate, POP30300.VENDORID AS MASTERID,
GL00105.ACTNUMST,GL00105.ACTNUMBR_1 as Main,GL00105.ACTNUMBR_2 as Location, 'POP'AS Origin,POP30390.ACTINDX
FROM POP30390 INNER JOIN
POP30300 ON POP30390.POPRCTNM=POP30300.POPRCTNM INNER JOIN
GL00105 ON POP30390.ACTINDX=GL00105.ACTINDX
WHERE POP30390.DISTTYPE=1 OR POP30390.DISTTYPE=201 OR POP30390.DISTTYPE=101
UNION ALL
SELECT SOP10102.SOPNUMBE AS DocNo, SOP10102.CRDTAMNT AS Credit, SOP10102.DEBITAMT as Debit,
SOP30200.DOCDATE AS DocDate, SOP30200.GLPOSTDT AS GLDate, SOP30200.CUSTNMBR AS MASTERID,
GL00105.ACTNUMST,GL00105.ACTNUMBR_1 as Main,GL00105.ACTNUMBR_2 as Location, 'SOP' as Origin, SOP10102.ACTINDX
FROM SOP10102 INNER JOIN
SOP30200 ON SOP10102.SOPNUMBE=SOP30200.SOPNUMBE AND SOP10102.SOPTYPE = SOP30200.SOPTYPE INNER JOIN
GL00105 ON SOP10102.ACTINDX=GL00105.ACTINDX
WHERE SOP30200.VOIDSTTS=0 AND SOP10102.POSTED=1 AND SOP10102.DISTTYPE=15 OR SOP10102.DISTTYPE=16
UNION ALL
select IV30500.DOCNUMBR AS DocNo, IV30500.CRDTAMNT AS Credit, IV30500.DEBITAMT AS Debit,
IV30200.DOCDATE as DocDate, IV30200.GLPOSTDT as GLDate, '' AS MASTERID,
GL00105.ACTNUMST,GL00105.ACTNUMBR_1 as Main,GL00105.ACTNUMBR_2 as Location,'INV' as Origin, IV30500.ACTINDX
FROM IV30500 INNER JOIN
IV30200 ON IV30500.DOCNUMBR=IV30200.DOCNUMBR AND
IV30500.IVDOCTYP=IV30200.IVDOCTYP INNER JOIN
GL00105 on IV30500.ACTINDX = GL00105.ACTINDX
WHERE IV30500.IVDOCTYP <=3

 

[GPSKid]

Thanks LUVSQL. I will give it a try.

 

[barbola]

If Smartlist doesn't give me what I need, I sometimes use Remove Inventory Transactions and make sure you UNCHECK remove and just print the report.

For FIFO, it shows the Receiving or Adjustment that each transaction is taken from.



Thanks!
Barb E.

 

[jazgeek]

LuvSql:

question on your view. What happens if some dolt doesn't mark the post to GL in the inventory transaction batch?

-----------
and they wonder why they call it Great Pains!

jaz