•Build a workflow to calculate average gross margin by invoice by product:
•Filter journal entry population on [Doc_Type] = “RI” (automated entries).
•Filter for Sales: [Object] = “40210”.
•Filter for COGS: [Object] = “50125”.
•Summarize records on [Object], [Doc_Type], [Doc_Number], [DESCR_2], and [AMOUNT].
•Join Sales to COGS on [Doc_Type], [Doc_Number], and [DESCR_2].
•Export unmatched records as “Products Not Matched” for further analysis.
•Calculate gross margin for matched records:
•((Sales – COGS) / Sales)
•If Sales = $0, then gross margin = 0%
•Export as “Gross Margin Calculated by Invoice by Product Type”.
•Summarize by product type (i.e. [DESCR_2]) and average gross margin.
•Export as “Gross Margin Calculated by Product Type”.