We have an issue where partial shipments are displaying low or negative gross margin because all of the labor is being applied at time of shipment.
Example: We use Average Costing. Job is for 50 pieces. 10 pieces have been received into stock and shipped in March. The other 40 pieces have been worked on, but are still in WIP. When the 10 are shipped, all labor incurred against all 50 pieces was recorded as the labor cost for the 10. When running the Gross Margin report, the gross margin for the assembly is low or negative as stated above. Each subsequent receipt and shipment picks up any new labor incurred since the last receipt to stock.
The margin doesn’t turn around until the build is complete, but the initial margin for the first 10 looks terrible to management. Is there any way to keep the initial, partial shipment from having a low margin?
Not that I am aware of. We are lot fifo, so it is somewhat better than yours, but still heavy on the first receipts to inventory or shipments. For margin analysis I got them to move to a job costs and profits dashboard I built based on the sgm work of @hkeric.wci so the complete job is evaluated.
Interesting that you mentioned that. I do recall a KB, but could not find it tonight about costs being leveled out by daily wip recon reports, not doing any capturing just the report. I looked and I have a scheduled task I set in 2020 to run wip recon daily.
//
// Populate Job Costing Fields on InvcDtl when the Job is closed
//
// NOTES:
// Usually Job Closing Costs are populated when the Job is closed
// however if the Job has been closed before Invoicing the JC Costs
// are not populated. The manual is to open the job and close it again.
// Hence why this BPM was created to populate the JC Costs without
// having to open and close the job.
//
//
// Variable used for Roundint
// One should use Erp.Internal.Lib.GetDecimalsNumber.LibGetDecimalsNumber if they
// have different costing across companies, the default is 5 and most used is 5
int nCostDecimals = 5;
foreach (string invoiceNum in Invoices.Split(new string[] { Ice.Constants.LIST_DELIM }, StringSplitOptions.RemoveEmptyEntries))
{
// Get Invoices that are Make Direct, associated to a Job
// Where the Job is closed
// There could be multiple Invoice Lines
var invDetailRows =
(from ih in Db.InvcHead.With(LockHint.NoLock)
join id in Db.InvcDtl.With(LockHint.NoLock) on new { ih.Company, ih.InvoiceNum } equals new { id.Company, id.InvoiceNum }
join sd in Db.ShipDtl.With(LockHint.NoLock) on new { id.Company, id.PackNum, id.PackLine } equals new { sd.Company, sd.PackNum, sd.PackLine }
join jh in Db.JobHead.With(LockHint.NoLock) on new { sd.Company, sd.JobNum } equals new { jh.Company, jh.JobNum }
where ih.Company == Session.CompanyID
&& ih.GroupID == GroupID
&& ih.InvoiceType == "SHP"
&& ih.InvoiceNum == Erp.ErpEFFunctions.ConvertToInt(invoiceNum)
&& id.OurShipQty > 0
&& id.ConsolidateLines == false
&& jh.JobClosed == true
select new {
id.Company,
id.InvoiceNum,
id.InvoiceLine,
id.PackNum,
id.PackLine,
jh.JobNum,
jh.PartNum,
jh.JobClosed,
jh.ProdQty,
JobProdQty = (jh.ProdQty <= 0) ? 1 : jh.ProdQty,
PartTranShippedQty = Db.PartTran.Where(w => w.Company == id.Company && w.JobNum == jh.JobNum && w.PartNum == jh.PartNum && w.TranType == "MFG-CUS")
.Select(s => s.TranQty).DefaultIfEmpty(0).Sum()
}
).ToList();
// This is the exact same algorithm Epicor uses when you close a Job
foreach (var idRow in invDetailRows)
{
Ice.Diagnostics.Log.WriteEntry($"[ JC Fix ] InvoiceNum: {idRow.InvoiceNum} - InvoiceLine: {idRow.InvoiceLine} - PackNum: {idRow.PackNum} - PackLine: {idRow.PackLine} - JobNum: {idRow.JobNum} - JobClosed: {idRow.JobClosed}" +
$" - Part: {idRow.PartNum} - ProdQty: {idRow.ProdQty} - JobProdQty: {idRow.JobProdQty} - PartTranShippedQty: {idRow.PartTranShippedQty}");
var JobAsmblRow = Db.JobAsmbl.Where(w => w.Company == idRow.Company && w.JobNum == idRow.JobNum && w.AssemblySeq == 0).FirstOrDefault();
if (JobAsmblRow != null)
{
var JobPartJobProd =
(from jp in Db.JobPart
join jp2 in Db.JobProd on new { jp.Company, jp.JobNum, jp.PartNum } equals new { jp2.Company, jp2.JobNum, jp2.PartNum }
where jp.Company == idRow.Company
&& jp.JobNum == idRow.JobNum
&& jp.ShippedQty > 0
&& jp2.ShippedQty > 0
select new {
JobPart = jp,
JobProd = jp2
}
).ToList();
foreach (var JobProdRow in JobPartJobProd)
{
var JoinResult = JobProdRow;
decimal varShippedQty = (idRow.PartTranShippedQty <= 0) ? idRow.JobProdQty : idRow.PartTranShippedQty;
// Get InvcDtl Rows to update including historical partial shipments
// this is the exact same algorithm Epicor uses when you close a Job
var InvcDtlRows =
(from id in Db.InvcDtl
where id.Company == JoinResult.JobProd.Company
//&& id.InvoiceNum == idRow.InvoiceNum
//&& id.InvoiceLine == idRow.InvoiceLine
&& id.OrderNum == JoinResult.JobProd.OrderNum
&& id.OrderLine == JoinResult.JobProd.OrderLine
&& id.OrderRelNum == JoinResult.JobProd.OrderRelNum
&& id.OurShipQty > 0
&& id.ConsolidateLines == false
select id
).ToList();
//
// Update Costs
//
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var InvcDtl in InvcDtlRows)
{
InvcDtl.JCBurUnitCost = Math.Round((JobAsmblRow.TLABurdenCost + JobAsmblRow.LLABurdenCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCLbrUnitCost = Math.Round((JobAsmblRow.TLALaborCost + JobAsmblRow.LLALaborCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCMtlBurUnitCost = Math.Round((JobAsmblRow.TLAMtlBurCost + JobAsmblRow.LLAMtlBurCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCMtlUnitCost = Math.Round((JobAsmblRow.TLAMaterialCost + JobAsmblRow.LLAMaterialCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
InvcDtl.JCSubUnitCost = Math.Round((JobAsmblRow.TLASubcontractCost + JobAsmblRow.LLASubcontractCost) / varShippedQty, nCostDecimals, MidpointRounding.AwayFromZero);
Ice.Diagnostics.Log.WriteEntry($"[ Updated ] InvoiceNum: {InvcDtl.InvoiceNum} - InvoiceLine: {InvcDtl.InvoiceLine} - JCBurUnitCost: {InvcDtl.JCBurUnitCost} - JCLbrUnitCost: {InvcDtl.JCLbrUnitCost} - JCMtlBurUnitCost: {InvcDtl.JCMtlBurUnitCost} - JCMtlUnitCost: {InvcDtl.JCMtlUnitCost} - JCSubUnitCost: {InvcDtl.JCSubUnitCost}");
}
Db.Validate();
txScope.Complete();
}
// Epicor is also doing something with InvcDtlPack
// we have not tested this scenario but it may be in the future
// leaving this in for reference
//
// using (libCreateInvcDtlPack = new AR.CreateInvcDtlPack(Db))
// {
// libCreateInvcDtlPack.UpdateInvcDtlPackJobClosing(JoinResult.JobProd, JobAsmbl, varShippedQty, nCostDecimals);
// }
}
} /* JobAsmbl */
}
}
I spent so much time on SGM I kinda know what you are talking about but dont recall. I know the report had shortcomings. SGM wasnt calculated on InvcDtl until the post happened, etc…