Reconcile Inventory to GL

[PTina]

Does anyone get the GL figures to match back with the Inventory values reported on the Historical Stock Status or Purchase Receipts Report on a consistent basis?

Each time we set the GL and Inventory Subledger to be the same the figures ultimately end up with huge differences.

I am experiencing the same problem that Luvsql had in thread632-875655. Was this ever solved?

It is not due to not marking Post to GL on the inventory batches nor is it from entering journals directly into the GL and bypassing the subledger.

Any suggestions will be welcome - I'm at my wits end to explain to the VP Finance how this could happen.

 

[jazgeek]

There are multiple reasons for GL not to match subledger inventory value.

are you running perpetual or periodic inventory? There's reasons why both run out of balance. I've never seen historical balance as the costs change but the system doesn't track historical standard or perpetual costings. Inventory value is fluxable in a perpetual environment depending on receipt/invoice/transaction cost for the item, and subsiquently inventory historical value (debits to the inventory account) do not revalue based on the "new" perpetual cost of the item

ie
Item A - 100 pieces in at $1.00
current cost = $1.00
Item A - 100 pieces in at $1.50
current cost = $1.50 (depending on what perpetual you use this may change to an average)
therefore you would think your stock status would say $100 + $150 = $250 right? Nope, it shows $300
GL has $250, but subledger does't

In a periodic environment, anyone making a change to the standard cost of the item throws subledger out of balance, but that could be locked down with security if required.

Perpetual's much worse at going out of balance.



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

jaz

 

[PTina]

We are using FIFO perpetual for all items. Isn't the whole concept of FIFO Perpetual to record & value inventory at each receipt cost while consuming the older items first?

Your example above does not look like a FIFO perpetual one. Are you saying that the FIFO Perpetual valuation method in GP does not work at all?

 

[barbola]

We use FIFO perpetual, and our stock status would show $250, not $300. It is never on-hand x current cost and should match the GL. But ours never does and we don't worry too much about it.

One of our divisions tries to reconcile it, and always have a difficult time. Mostly due to human error.

 

[swabunn]

Hi Luvsql:

You mentioned in previous threads that you have made historical stock status report in crystal. we have 9 sites and users are transferring quantities to each other sites. I have problem in puting transfers transacitons in each location. I can put 'transfer from qty' and not able to put 'transfer in' qty in my report. could you let me know how did you treated inter division transfers in your report

Thanks

-G

 

[Dukester0122]

here's what you can try:

1) Created a parameter for Location(IV00102.LOCNCODE)
2) Go to Select Expert then create 2 formulas:
a) IV00102.LOCNCODE = {?Location}
b) IV30300.TRXLOCTN
{?Location} in {IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]
3)Create your Date Range
4)Create a formula called QUANTITY or QTY
if {IV30300.DOCTYPE} = 3 and {IV30300.TRXLOCTN}= {?Location} AND {IV30300.TRFQTYTY} = 1 THEN
- {IV30300.TRXQTY} * {IV40202.QTYBSUOM}
ELSE {IV30300.TRXQTY} * {IV40202.QTYBSUOM}

Hope this helps.

 

[swabunn]

if I am setting up {?Location} as allow range or enter multiple value then the formula # 2 does not work

I want to use multiple location

IV30300.TRXLOCTN
{?Location} in {IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]

it highlight {IV30300.TRNSTLOC} and
it gives errror message: the remaining text does not appear to be part of the formula

My record selection formula in select expert is as below:

{IV00102.LOCNCODE} = {?Location} and
{?Location} in {IV30300.TRXLOCTN},{IV30300.TRNSTLOC}] and
{IV30300.DOCTYPE} <> 3 and
{INVGLPSTDT.GLPOSTINGDATE} <= {?END DATE }

-g

 

[swabunn]

I tried (?Locatiom) with discrete value. but I am not geting any transafer qty for "transfer to location"

-g

 

[swabunn]

Sorry (?Location) not (?Locatiom)

-g

 

[Dukester0122]

My mistake should be:

{?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]

 

[swabunn]

I don't understand what I am doing wrong?

If I am subcripted like above, now it is giving a error
'A NUMBER, CURRENCY AMOUNT,DATE,TIME,DATE-TIME OR STRING IS REQUIRED HERE. if I use descrete value, Report still does not give any data for 'trasfer to' location.

thanks

-g

 

[Dukester0122]

here's my record selection:

{IV00102.LOCNCODE} = {?Location} and
{IV30300.DOCDATE} <= {?Ending Date} and
{?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]