COGS Timing Issues

Has anyone discovered a way to update the item cost, (and sales information) when an item is recieved vs. when the vendor invoice is received? Navision does not update the average cost of an item until the vendor’s invoice is received, rather than when the product is received, (using the negotiated p.o. cost). In our industry pricing changes quite frequently and can increase or decrease 50% on some items over the course of a year. It is therefore important for us to be able to review sales on daily basis and ensure that margins are being maintained. Since Navision is always using the “old cost” and not the current purchase price as per our P.O. we never have valid sales information on a real time basis. Some sales margins look great until the vendor invoice is recieved and the costing is updated, or vice versa. Sometimes we will not receive a vendor invoice for 2-3 weeks creating problems with month end and commission statements, since we pay our sales people a percentage of the margin. The whole thing seems a little backwards to me. In essence Navison is assuming that the old information, (previous average cost) is more accurate than the new information, (current p.o. cost). I would like a way to use the purchase order cost and then deal with the exceptions, (i.e. when the p.o. cost is different than the vendor invoice amount), rather than using outdated information updating only when the vendor invoice is received.

You could change the posting routines to hit the appropriate ledgers when the Purchase Order is received rather than when it is Invoiced. The accounting side of this could be problematic if not done right— but if you have a good accountant and a good NSC, the problems can be dealt with… This time, I will NOT question the logic behind the business rules. You pay your salespeople and do your reports on a certain schedule and that’s that. Just out of curiousity, through, what DO you do when the invoiced price is different from the received price. Do you redo the reports? Do you adjust the sales commissions? If so, how? Do you pay the commissions based on the real margin (that is the real price minus the real direct cost), or do you calculate a modified margin using fictititious prices and/or costs? ------- Tim Horrigan horrigan@aol.com

Why not just produce a report. That shows Current Month Item Sales looking at the item application to find if the costs are realised or unrealised to calculate realised or unrealised profit values. Then all the Invoiced Items that were posted in previous periods but the purchase was Invoiced in the current period Calculating the Adjusted Cost difference. then reporting to the salesmen two values so they know that they have been paid XXX on na unrealised profit % Note: You can switch on automatic cost adjustment which will adjust the costs at the point on Invoice but will create lots of G/L entries David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk Edited by - David Cox on 2001 Mar 20 08:54:18

Thanks for your replies. Obviously the key to proper costing begins with an accurate purchase order, if you are going to post on receipt of product vs. receipt of invoice. Our old system, which we dropped only a few months ago, updated costs on receipt of product based on the p.o. cost. Sales reports used these updated costs, but the receipt went into a temporary account until the invoice was received. When the invoice was received, cost adjustments were posted to the GL directly, rather than the sale. A report of adjustments could be run to modify the commissions manually, but the amount was never significant. What we are trying to get is accurate and timely information - the same as everyone else. The fewer system changes the better. If I can run a report without changing the posting routines - all the better. I would like to see via reports or postings: Expected cost, i.e. P.O. cost Actual cost, invoiced cost Expected margin Actual Margin It should be easy enough to produce a variance report from this information, identifying any major discrepancies. This would allow us to run daily reports with our expected costs, monthly reports with actual cost and a variance report. A couple of variables to complicate the matter: 1) We use a landed average cost, (we allocate freight costs to items when we receive them) 2) If we simply use the P.O. cost, we will not be accounting for the calue of the inventory on hand, i.e. we will not be using average cost, (which we want to use). Would it be possible for a report to consider this into its equation as well?

I had a similar situation and was able to deal with it using reports. A couple new flowfields on the item table and a function on the item table were a big help. The flowfields were “Rcvd. Not Invoiced (Qty.)” and “Rcvd. Not Invoiced ($)” both defined as the sum of fields (“Qty. Rcd. Not Invoiced (Base)” and “Amt. Rcd. Not Invoiced ($)”)from the purchase line table. These can be easily created by copying field 84 (“Qty. on Purch. Order”) and changing the field to be summed. The function is called EstimatedAverageCost and returns the estimated average cost based on the average cost for what’s in inventory but not yet invoiced and the purchase order amounts for what’s been received but not invoiced.