Monitoring COGS From Brewhouse to Finished Goods

ZachZach Business Process Manager
edited April 1 in Internal Processes

Created by: Zach Hill
Query Name: COGS Monitor
Version: 0.0.1
Date: 4/1/2019
Summary: Monitor the price of an item(s) as it moves through production
Business case: When unit costs deviate for an item(s) it can become a huge problem. If those errors are not immediately fixed your inventory numbers will be grossly inflated and calculating profitability will be way off. This query can help you find those errors quickly so you can fix them before other processes push bad data through your db. Don't let a COGS error become a bookkeeping travesty. Cheers!

SELECT T0.[DocEntry]
, T0.[DocDate]
, T0.[ItemCode]
, T1.[ItemName]
, T0.[Price]
, T2.[AvgPrice]
WHEN T2.[AvgPrice] = 0
THEN '0'
ELSE ((T0.[Price]-T2.[AvgPrice])/T2.[AvgPrice]*100)
END AS 'Δ in %'
, (T2.[AvgPrice]-T0.[Price]) AS 'Δ in $'

INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
INNER JOIN OITW T2 ON T1.[ItemCode] = T2.[ItemCode]

WHERE T2.[WhsCode] = T0.[WhsCode]
T0.[DocDate] = [%1]


  • ZachZach Business Process Manager

    This line:
    , (T2.[AvgPrice]-T0.[Price]) AS 'Δ in $'

    Should be:
    , (T0.[Price]-T2.[AvgPrice]) AS 'Δ in $'

  • ZachZach Business Process Manager
    edited April 3

    I'm following up this thread with a personal anecdote on how this query is useful and, much more importantly, how COGS can go off the rails. Hopefully this gives you insight on how PO's can cause COGS errors and how to train production staff on what to look for.

    So, I ran the query yesterday and I got this:

    Line item 4, Brite Beer - Big Bark, is costing a $1,1772.16 per barrel. That's a $150K batch of beer now! Oh no! But thank god I caught it today instead of somewhere downline the line, possibly even weeks or a month from now, post-packaging.

    After talking with my head brewer and the guy who expressed the order, this is how a $150k batch can literally come out of nowhere..

    (Example below demonstrates how the error above was caused... beer names and volumes are different but this is just one way a PO COG error can happen.)

    A PO was created with a Planned Quantity equaling 1 in the header. The body had a Planned Quantity that was the amount of beer intended to be transferred (eg not equaling 1). Like so:

    When the production guy checked the open documents for the production order he was executing he saw something like this:

    He then opened the production order to change the Planned Quantity in the header. And this is where the error actually occurred!

    When he did this the Production Order Function multiplied the Planned Quantity in the body by the Planned Quantity in the header. Upon expressing, a goods issue dumped approximately "99% of beer" down the drain as a loss and transferred the dollar value of this fake loss to the barrels of beer received by receipt into the Brite Tank.

    Thus a really f'n expensive batch of beer materialize outta nowhere.

    TO AVOID THIS: Make sure both Planned Quantity in the header and body match! Checking dates has been drilled into anyone who has done the Obeer on boarding to go-live, but being drilled on that Planned Quantity match in both places is more important, imho. Having to disassemble/reassemble because of a bad date is less painful than disassemble/reassemble because of a COGS error.

    But, errors will happen so catching them and fixing them ASAP is the second best thing to not making errors at all. So hopefully you'll use my query to help catch your COG errors as early as possible.

    If you found this post interesting, or relatable, then please vote on THIS in the ideas portal.

    Thank you!

Sign In or Register to comment.