We have a stock status BAQ that has been working for us for the past 5 months pretty well; it ties closer to the GL accounts through Chart Tracker than the canned report ever has.
This period, however, we were off by $223k (higher than GL) when normally it’s a couple hundred to a thousand off.
How do I go about troubleshooting that? Chart Tracker doesn’t offer much to be able to vLookup off of.
My guess is this is some sort of UOM problem. You will not be able to balance to GL because it only has transactional data . However, do you balance to the system Stock Status report? Although this is very clunky, you can request the system STOCK STATUS report in CSV format to compare to your BAQ.
Do you have the PartTran table linked to the TranGLC table I suppose? for that BAQ you are talking about?
Could it be a manuel journal entry, or a po/ap charge directly to inventory?
I once had a customer who’s AR was suddenly off by over 600k from AR reports. I suggested a journal entry. They said no, and spent weeks searching for a bad invoice or cash payment. Then they found that someone entered a journal entry when they purchased a company (and their ar/ap/inventory). The merger was not public to everyone in the company, but someone in accounting knew and incorrectly did the journal entries.
1 Like
If you have both a raw materials and a finished goods account, could it be a mistake in the gl control code attached to the part class or an incorrect part class? You didn’t say whether the Stock Status was for raw mtls or ???
@Mario.Cortes Here’s the BAQ:
select distinct
[PartBin].[PartNum] as [PartBin_PartNum],
(SUM(case when WhseBin.NonNettable = 1 then 0 else PartBin.OnhandQty end)) as [Calculated_SumQty],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgLaborCost+PartCost.AvgBurdenCost+PartCost.AvgMaterialCost+PartCost.AvgSubContCost+PartCost.AvgMtlBurCost)
else
SumQty * AVG(PartCost.StdLaborCost+PartCost.StdBurdenCost+PartCost.StdMaterialCost+PartCost.StdSubContCost+PartCost.StdMtlBurCost)
end) as [Calculated_Cost],
[Part].[TypeCode] as [Part_TypeCode],
[Part].[CostMethod] as [Part_CostMethod],
(case when Part.CostMethod = 'A' then
AVG(PartCost.AvgLaborCost+PartCost.AvgBurdenCost+PartCost.AvgMaterialCost+PartCost.AvgSubContCost+PartCost.AvgMtlBurCost)
else
AVG(PartCost.StdLaborCost+PartCost.StdBurdenCost+PartCost.StdMaterialCost+PartCost.StdSubContCost+PartCost.StdMtlBurCost)
end) as [Calculated_UnitCost],
[GLAccount].[GLAccount] as [GLAccount_GLAccount],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgLaborCost)
else
SumQty * AVG(PartCost.StdLaborCost)
end) as [Calculated_Labor],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgBurdenCost)
else
SumQty * AVG(PartCost.StdBurdenCost)
end) as [Calculated_Burden],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgMaterialCost)
else
SumQty * AVG(PartCost.StdMaterialCost)
end) as [Calculated_Material],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgSubContCost)
else
SumQty * AVG(PartCost.StdSubContCost)
end) as [Calculated_Subcon],
(case when Part.CostMethod = 'A' then
SumQty * AVG(PartCost.AvgMtlBurCost)
else
SumQty * AVG(PartCost.StdMtlBurCost)
end) as [Calculated_MtlBurden]
from Erp.PartCost as PartCost
cross join Erp.WhseBin as WhseBin
inner join Erp.PartBin as PartBin on
PartBin.Company = PartCost.Company
And
PartBin.PartNum = PartCost.PartNum
and ( PartBin.OnhandQty <> 0 )
inner join Erp.PartBin as PartBin and
WhseBin.Company = PartBin.Company
And
WhseBin.WarehouseCode = PartBin.WarehouseCode
And
WhseBin.BinNum = PartBin.BinNum
and ( PartBin.OnhandQty <> 0 )
inner join Erp.Part as Part on
PartBin.Company = Part.Company
And
PartBin.PartNum = Part.PartNum
inner join Erp.EntityGLC as EntityGLC on
Part.Company = EntityGLC.Company
And
Part.ClassID = EntityGLC.Key1
inner join Erp.GLCntrlAcct as GLCntrlAcct on
EntityGLC.Company = GLCntrlAcct.Company
And
EntityGLC.GLControlType = GLCntrlAcct.GLControlType
And
EntityGLC.GLControlCode = GLCntrlAcct.GLControlCode
inner join Erp.GLAccount as GLAccount on
GLCntrlAcct.Company = GLAccount.Company
And
GLCntrlAcct.COACode = GLAccount.COACode
And
GLCntrlAcct.GLAccount = GLAccount.GLAccount
where WhseBin.NonNettable = False and WhseBin.BinType = 'Std' and EntityGLC.RelatedToFile = 'PartClass' and GLCntrlAcct.GLAcctContext = 'Inventory/Expense'
group by [PartBin].[PartNum],
[Part].[TypeCode],
[Part].[CostMethod],
[GLAccount].[GLAccount]
Finance uses this at month end when no one is transacting to tie their inventory to their GL and has been working well for quite some time.
@timshuwy I don’t believe it’s a JE, but I can follow up. How would I identify the charge direct to inventory?
@Claudia it pulls both. My finance manager will manipulate the data in Excel to reconcile each account.
Use the Chart Tracker, and examine all the transactions that are NOT done with periodic posting process… in this case, there are 4 entries that are touching the Material stores account that were manually entered with a journal entry.
1 Like
Nope, none of those.
We have, however, discovered that the parts with the biggest variances are kits (both parents and their components).
Can someone help me understand the kit transactions?
On the components, I see:
- MFG-STK and PUR-STK (Job or PO received to stock)
- STK-KIT (Kit component issued to parent part)
On the Parent, I only see:
- KIT-CUS (Shipment)
Should there be an offsetting transaction for STK-KIT? I feel like something is missing or does that look correct?
You can use a BAQ linking PartTran and TranGLC, that is what I do. It seems that everything that has GLC has been posted, so If you run a partTran for the discrepancies you have you might see that they haven’t been posted (here in my company we run capture process every monday or whenever is month end) so everything that happens during the week would not show up in the Chart tracker until its posted.
That is the general structure of the BAQ, filter the ParTran for everything not posted.
These are the field I have
The most tricky part is to calculate debits and credit here is an example
I did several of these BAQs and created an excel file using oData to simulate the Inventory/WIP reconciliation report. It was not a fun task to do, but it matches all the time. We use it for Job Variance analysis.
1 Like
Would you mind exporting and sending that to me, so I can play around with it?
Aaron.Willett@Elkay.com
Sent!
Please keep in mind that this is just for Material, since there is no GL transaction yet, I had to take the Inventory transaction by element, and I created several different BAQ, one for mateiral, one for subcontract, one for labor and another for burden. You could remove those filters from the BAQ to get everything.
Hope it helps!
1 Like
In a weird sequence of events, it turns out the prior months where it was close to tying out were wrong. There were parts missing a part class and I had a filter in my BAQ that restricted that, so they dropped off the report.
Looks like May is starting to be back on track.
Thanks everyone.
1 Like
Did the BAQ help?
Mario,
Would you mind sending me the BAQ you helped Aaron with. I am also have issues matching the stock status report to our GL accounts.
thanks in advanced.
Please send to me too. I’m also having same issue…
Thanks
Prasad Ramawickrama | Director | Information Systems
ND Industries, Inc. | 1893 Barrett Drive | Troy, MI 48084
Direct: 248-655-2520 | Cell : 248-408-7874
I would love to get a copy of this BAQ too if you’d be will share it.
Thanks,
Patti
For all who have issues match stock sts to the GL…….please be aware that depending on the E10 version you are on there is a know issue where the stock sts report will ignore stock if it does not match the master table bin record, ie case. For example if a user transferred stock to a bin say 1ab2 but your master table bin record is actually 1AB2, the stock sts report will ignore this and hence the two wont match. Apparently this was fixed in versions from 10.2.