Thought id ask if anyone has a Sales Gross Margin BAQ/Dashboard that works similar to the Sales Gross Margin Report. I figured it’s not trivial to build, but I am sure someone already has built one and I could save 2-4hrs (pay it forward).
I could return the favor by sharing an Excel Friendly Version of the Sales Gross Margin Report (.rdl).
I have a BAQ, although never tried to compare it to the report. I will post tomorrow when I go to office.
My version is 10.1.600. I also have a test box for 10.2.400. Which one do you need.
Vinay Kamboj
10.1.600 would work fine – Since I am on 10.2.200.
@hkeric.wci Atached is the BAQ called SalesMargin. It has a lot of fields since we use it for many reports for different purposes.SalesMargin.baq (47.9 KB)
Here’s mine from 10.2.300 SalesGrossMargin.baq (33.2 KB)
I did plenty of verification against actual data from in the inbuilt report, keeping in mind that you won’t get all figures through on current sales until capture WIP/COS is run.
@hkeric.wci There is also a zHomepage_SalesGrossMargin BAQ that @bconner built for EDD. It doesn’t use the Sales Gross Margin BO specifically but I would hope that the logic is the same.
@John_Mitchell @aseidel @Vinaykamboj
Thank you all for the contributions… I’ll make sure I return the favor soon. All 3 have something to add… I’ll take the best from each one and build my version of it. Awesome!
zHomepage_SalesGrossMargin.baq (46.3 KB)
Thank you for your example. I am looking to design a similar BAQ with a dashboard. I am running E10.1.400.38 and was not able to import your BAQ.
This may be a question that needs a new post, but I am trying to dig into this report (Sales Gross Margin) a little more. I have a question about when to use OurShipQty v.s. SellingShipQty to calculate total cost.
If OurShipQty <> SellingShipQty then which one do you use to calculate total cost. You multiply each unit cost by total qty shipped to get total cost, but again, which one do you use. In @Vinaykamboj 's query, “ourshipqty” is always used, but I have seen the SGM report use a different field to calculate the totals. In fact there is one on our report where the selling ship qty is 2.5 and our ship qty is 5. In @Vinaykamboj query the margin is lower because it uses 5, but on the SGM report it uses 2.5.
Can anyone explain or teach the logic to me about what qty field to use when calculating total cost?
Part of me thinks it is whatever the tran qty is in the PartTran table, but I really would like to avoid joining to that table.
Furthermore, The zHompage_SalesGrossMargin that @hkeric.wci posted (I know it is a system query) uses the selling ship qty to calculate total costs. When the selling ship qty is 1 but the our qty is something else (i.e. the sales UM is 50x the IUM), then the margin is way higher than it should be.
It is all confusing me…
Looking forward to any replies or clarity.
The right one to use are the Selling Columns.
The time you would use OurShipQty is to Calculate your StdCosts.
[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty],
[InvcDtl].[SalesUM] as [InvcDtl_SalesUM],
(InvcDtl.OurShipQty * InvcDtl.MtlUnitCost) as [Calculated_MaterialCost],
(InvcDtl.OurShipQty * InvcDtl.LbrUnitCost) as [Calculated_LaborCost],
(InvcDtl.OurShipQty * InvcDtl.BurUnitCost) as [Calculated_BurdenCost],
(InvcDtl.OurShipQty * InvcDtl.SubUnitCost) as [Calculated_SubcontractCost],
(InvcDtl.OurShipQty * InvcDtl.MtlBurUnitCost) as [Calculated_MaterialBurdenCost],
((LaborCost + BurdenCost + MaterialCost + SubcontractCost + MaterialBurdenCost)) as [Calculated_TotalCost],
Here are the examples that are throwing me off. Both are found on our sales gross margin report.
Stock Part ABC is sold in PK50 Sales UM. The order line calls for 1 PK50.
This means 50 EA of part ABC (our qty).
If the mtl unit cost is 0.39 EA then the formula you posted above would come out as 50 * 0.39 which is what I see the sales gross margin report doing.
Here is Scenario 2:
We manufacture part EFG.
The order calls for 5 EA of EFG.
On the pack we ship off 5 BASE-2 UOM. This equates to a selling ship quantity of 2.5 EA but is 5 (our quantity).
If the mtl cost is 144.902 then your formula would come out at 5 * 144.902 which is 724.51. However, that is not what the sales gross margin report has down. I can see it uses the 2.5 (selling qty) * 144.902…
@hkeric.wci I am just confused.
The Epicor Sales Gross Margin Report and the Homepage Query have flaws. I spent a solid 1 month trying to balance the BAQ out to the Sales Journal, SGM Report, Trial Balance, Chart Tracker etc…
I can’t recall all the flaws but what I learned is that on an Invoice all things go into “seperate journals” or “GL Accts” and the SGM Report out of the box will exclude numbers and throw you off.
For example:
Not only that but you can’t even use the Invoice Date or Apply Date, you have to go to TranGLC and find the “Actual Apply Date” which is the TranDate.
(ISNULL(Erp.FiscalYear(InvcHead.Company, TranGLC.TranDate), ISNULL(Erp.FiscalYear(InvcHead.Company, InvcDtlMiscChargesSubQuery.TranGLCMisc_TranDate), InvcHead.FiscalYear))) as [Calculated_ApplyYear],
(ISNULL(Erp.FiscalPeriod(InvcHead.Company, TranGLC.TranDate), ISNULL(Erp.FiscalPeriod(InvcHead.Company, InvcDtlMiscChargesSubQuery.TranGLCMisc_TranDate), InvcHead.FiscalPeriod))) as [Calculated_ApplyPeriod],
Also for Epicor out of the box to calculate certain Misc Charges, you must enable those Categories or they will be excluded:
Anyways - as promised, attached are my BAQs (which are still missing some Targeted GL Acct #s) but it was good enough for us.
DB-SGMDetails.baq (58.8 KB) DB-SGMSummary.baq (66.1 KB)
Thanks Haso, I will check those out.
Download my BAQs and try them.
Column: PricePerCode
Indicates the pricing per quantity. It can be “E” = per each, “C” = per hundred, “M” = per thousand. Used to calculate the extended unit price for the line item. The logic is to divide the InvcDtl.ShipQty by the appropriate “per” value and then multiply by unit price. Use the OrderDtl.PricePerCode as default if referenced to an order else use Part.PricePerCode as a default. If Part record does not exist then default as “E”.
Column: InvoiceType
There are four types of invoices: " Shp " = Invoice for Shipment, " Adv " = Advanced Billing, " Dep " = For Deposit Payments, and " Mis " = Miscellaneous. The setting of this field affects invoice entry:
“Shipments” - These are generated by the “Get Shipments function” and is not selectable directly by the user.
“Advanced” - Must have a sales order reference. The detail lines on this type of invoice update the OrderDtl.AdvanceBillBal. Also the user indicates if this should be considered as deferred revenue. Which changes which G/L accounts are used for the line item credits.
“Deposit” invoices are used to request a “deposit” on an order. A Sales Order is mandatory. No line items or Miscellaneous records are allowed. The user enters a flat amount on the header (InvcHead.PrePayAmt) which will be printed in the body of the invoice. This also updates the OrderHed.PrePayBal field.
“Miscellaneous” - These invoices may or may not reference a Sales Order. If Invoice is generated in Project Billing then there are following options: “PFF” - Fixed Fee project; “PCP” - Cost Plus project; “PTM” - Time and Material project; “PPP” - Progress Payment project.
Column: DeferredRevenue
Only used when InvoiceType = “Adv” (Advanced Billing). Indicates if the detail line amounts are to be considered as sales or deferred revenue. If “No” then the G/L accounts on the detail lines are the Sales Accounts otherwise they will be set to the Deferred Revenue accounts established in the ARSyst/ARAcct files.
There is CreditMemo, UnappliedCash, InvoiceSuffix
I dont recall their purposes I know I was noting / debugging
Dang! Thanks for the notes!
PS: You will need to Group by the Column “ACCTSTargeted” and Invoice Type, to properly vet out your Costs.
The Epicor example InvcDtl.OurShipQty * InvcDtl.MtlUnitCost should give you the right cost. Also the Epicor Fields already account for the InvcDtl.PricePerCode logic:
InvcDtl.PricePerCode
Indicates the pricing per quantity. It can be “E” = per each, “C” = per hundred, “M” = per thousand. Used to calculate the extended unit price for the line item. The logic is to divide the InvcDtl.ShipQty by the appropriate “per” value and then multiply by unit price. Use the OrderDtl.PricePerCode as default if referenced to an order else use Part.PricePerCode as a default. If Part record does not exist then default as “E”.
That formula would be your “Cost”. The Total Cost is Calculated using the INVENTORY QTY and IUM NOT SALES InvcDtl.MtlUnitCost would be based of your “Inventory UOM”
Now to get your NetUnitPrice you would do something like:
-------------------------------------------------
-- NetUnitPrice
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
IIF(InvcDtl.SellingShipQty <> 0, (NetTotalPrice - TotalCost) / InvcDtl.SellingShipQty, 0)
@utaylor here are some more Notes I had of formulas:
=================================================
- MAINQuery
=================================================
-------------------------------------------------
-- MaterialCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.SellingShipQty * InvcDtl.MtlUnitCost
-------------------------------------------------
-- LaborCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.SellingShipQty * InvcDtl.LbrUnitCost
-------------------------------------------------
-- BurdenCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.SellingShipQty * InvcDtl.BurUnitCost
-------------------------------------------------
-- SubcontractCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.SellingShipQty * InvcDtl.SubUnitCost
-------------------------------------------------
-- MaterialBurdenCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.SellingShipQty * InvcDtl.MtlBurUnitCost
-------------------------------------------------
-- TotalCost
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
(LaborCost + BurdenCost + MaterialCost + SubcontractCost + MaterialBurdenCost)
-------------------------------------------------
-- TotalTaxAmt
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
ISNULL(InvcDtlTaxesSubQuery.Calculated_TotalTaxAmt, 0)
-------------------------------------------------
-- TotalMiscCharges
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
ISNULL(InvcDtlMiscChargesSubQuery.Calculated_TotalMiscCharge, 0)
-------------------------------------------------
-- NetTotalPrice
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.ExtPrice - InvcDtl.Discount
(CASE WHEN InvcHead.DeferredRevenue = 1 OR InvcHead.CreditMemo = 1
THEN TotalMiscCharges
ELSE
InvcDtl.ExtPrice + TotalMiscCharges
END)
| ACCOUNTS TARGETED
ISNULL(TranGLC.GLAccount, '') + ISNULL(InvcDtlMiscChargesSubQuery.TranGLCMisc_GLAccount, '')
| SALES TOTAL
CASE WHEN InvcHead.DeferredRevenue = 1 OR InvcHead.CreditMemo = 1 THEN TotalMiscCharges
ELSE
InvcDtl.ExtPrice + TotalMiscCharges
END
| ACTUALAPPLYPERIOD
ISNULL(Erp.FiscalPeriod(InvcHead.Company, TranGLC.TranDate), InvcHead.FiscalPeriod)
-------------------------------------------------
-- NetProfitMarginAmt
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
NetTotalPrice - TotalCost
-------------------------------------------------
-- NetProfitMarginPct
-- ->>>>9.99
-------------------------------------------------
IIF(NetTotalPrice <> 0, (NetProfitMarginAmt / NetTotalPrice) * 100, 0)
-------------------------------------------------
-- NetUnitPrice
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
IIF(InvcDtl.SellingShipQty <> 0, (NetTotalPrice - TotalCost) / InvcDtl.SellingShipQty, 0)
-------------------------------------------------
-- GrossTotalPrice
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
InvcDtl.ExtPrice + InvcDtl.TotalMiscChrg - InvcDtl.Discount - InvcDtl.AdvanceBillCredit + TotalTaxAmt
-------------------------------------------------
-- GrossProfitMarginAmt
-- ->,>>>,>>>,>>9.999
-------------------------------------------------
GrossTotalPrice - TotalCost
-------------------------------------------------
-- GrossProfitMarginPct
-- ->>>>9.99
-------------------------------------------------
IIF(GrossTotalPrice <> 0, (GrossProfitMarginAmt / GrossTotalPrice) * 100, 0)
-------------------------------------------------
-- ActShippingQty
-- ->>>>9.99
-------------------------------------------------
CASE
WHEN InvcHead.InvoiceSuffix = 'CM' OR InvcHead.InvoiceSuffix = 'UR' THEN
InvcDtl.SellingShipQty * -1
ELSE
InvcDtl.SellingShipQty
END
-------------------------------------------------
-- xLineQuantity
-- ->>>>9.99
-------------------------------------------------
(case when InvcHead.InvoiceType in ('ADV','DEP') then (case when InvcDtl.DocExtPrice<0 then -1 else 1 end)
else InvcDtl.SellingShipQty
end)*(case when InvcHead.CreditMemo=1 then -1 else 1 end)
-------------------------------------------------
-- xLineQuantityUnit
-- ->>>>9.99
-------------------------------------------------
(case when InvcHead.InvoiceType in ('ADV','DEP') then 'EA'
else InvcDtl.SalesUM
end)
-------------------------------------------------
-- xLineExtensionAmt
-- ->>>>9.99
-------------------------------------------------
(InvcDtl.DocExtPrice - InvcDtl.DocDiscount + InvcDtl.DocTotalMiscChrg)*(case when InvcHead.CreditMemo=1 then -1 else 1 end)
-------------------------------------------------
-- xACTUALUNITPrice xPriceAmt
-- ->>>>9.99
-------------------------------------------------
(case when InvcHead.InvoiceType in ('ADV','DEP') then abs(InvcDtl.DocExtPrice - InvcDtl.DocDiscount + InvcDtl.DocTotalMiscChrg)
else abs(InvcDtl.DocUnitPrice)
end)
-------------------------------------------------
-- xBaseQty
-- ->>>>9.99
-------------------------------------------------
(case when InvcHead.InvoiceType in ('ADV','DEP') then 1
else (case when InvcDtl.PricePerCode = 'E' then 1
when InvcDtl.PricePerCode = 'C' then 100
when InvcDtl.PricePerCode = 'M' then 1000
end)
end)
-------------------------------------------------
-- SalesTotalNoTax
-- ->>>>9.99
-------------------------------------------------
InvcDtl.ExtPrice + InvcDtl.TotalMiscChrg - InvcDtl.Discount - InvcDtl.AdvanceBillCredit
REFERENCES:
(((case when (OrderDtl.PricePerCode = 'M') then ((((case when
(OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) > 0 then
(OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) else 0 end))/ 1000)
* OrderDtl.DocUnitPrice * (1 - (OrderDtl.DiscountPercent / 100))) else ((case when (OrderDtl.PricePerCode = 'C')
then ((((case when (OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) > 0 then (OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) else 0 end))/ 100) * OrderDtl.DocUnitPrice * (1 - (OrderDtl.DiscountPercent / 100))) else ((((case when (OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) > 0 then (OrderRel.SellingReqQty - OrderRel.SellingJobShippedQty - OrderRel.SellingStockShippedQty) else 0 end))/ 1) * OrderDtl.DocUnitPrice * (1 - (OrderDtl.DiscountPercent / 100))) end)) end))) as [Calculated_OpenValue],
* ISNULL(OrderDtl.UnitPrice, 0)) as [Calculated_ExtendedAmt],
((CASE
WHEN OrderDtl.PricePerCode = 'M' THEN (ISNULL(ShipDtl.SellingInventoryShipQty,0)+ ISNULL(ShipDtl.OurJobShipQty,0)) / 1000
WHEN OrderDtl.PricePerCode = 'C' THEN (ISNULL(ShipDtl.SellingInventoryShipQty,0)+ ISNULL(ShipDtl.OurJobShipQty,0)) / 100
ELSE (ISNULL(ShipDtl.SellingInventoryShipQty,0)+ ISNULL(ShipDtl.OurJobShipQty,0))
END)
Based on your above responses, it seems the calculated cost fields in this BAQ are at standards (since you’re using OurShipQty). How would you recommend creating calculated fields at actual cost? Would I just use SellingShipQty instead of OurShipQty?
Thank you!