Expected VS. Automatic Cost Posting

Savatage

As many know, sometimes the Vendors invoice does not arrive until a few weeks after the merchandise has arrived. This causes havoc with the Unit costs - some items show really crazy costs when the invoice has not arrived yet. Even when we run Adjust Costs & post to g/l reports there are always about 100 or more items sitting in limbo at any given time.

So i wanted to create My own Avg cost based on the Item ledger entries.
So at least we know "where we are" in terms of reporting on inv & such.

I want this Number to be based on:
Entry Type: Purchase in the item lesdger entries
I want to add up all Purchase:Quantity and add up all Adjusted Cost (Expected) fields and divide them to give me my new Avg cost.

I'm looking at the Codeunit 5804 ItemCostManagement to get some ideas on how to tackle this. Specifically Prodedure:CalculateAverageCost.

Now i was thinking about creating a new code unit to do this so not to mess with standard Navision. I was just wondering before I jump into this If anyone had thoughts on the best way to do this or if my path is a good one.

And any reasons why using these two fields would NOT give me an accurate result.

All Comments Welcome...thanks

Savatage

Not 1 - LOL!

Marije_Brummel

Maybe MBSOnline?

Sorry, I'm not realy into costing... :?

Savatage

ok

WITH ItemLedgerEntry DO BEGIN
  RESET;
  SETCURRENTKEY(
 "Entry Type","Item No.","Variant Code","Drop Shipment","Location Code","Bin Code","Posting Date");
  SETRANGE("Item No.",Item."No.");
    SETFILTER("Entry Type",'PURCHASE');
    CALCSUMS(Quantity,"Adjusted Cost (Expected)");
  AverageQty := Quantity;
  AverageCost := "Adjusted Cost (Expected)";
END;
IF AverageQty <> 0 THEN BEGIN
  AverageCost := AverageCost / AverageQty;
  IF (AverageCost < 0) THEN BEGIN
    AverageCost := 0;
     END;
END ELSE BEGIN
  AverageCost := 0;
END;
IF AverageQty <= 0 THEN
  EXIT(FALSE);

EXIT(TRUE);

problem now is that Adjusted Cost (Expected is a flowfield) & a flowfield CONNOT BE USED AS A SUNINDEXFIELD

Next will try to move the Item No., Quantity & Adjusted Cost (Expected) Item ledger entries for entry type "Purchase" to a temp table and try again.

Savatage

going a new way - need help

Ok I found the Expected Cost Check Box - we've been using Automatic Cost Posting ONLY!

Sounds great but..

Warning
Enabling both automatic cost posting and expected cost posting to G/L means that the amount of entries the system creates and the level of detail produced every time you post something will increase. This may reduce the performance of your database considerably.

Given that...

Expected costs are posted to interim accounts in the general ledger. If you want to post expected costs, you must set up interim accounts for the relevant posting groups.

In the Inv Posting Setup - I do not have any accounts under Inv Account (Interim). I'm assuming I should creat new account #'s and not post them directly to the regular INV ACCT.

Am I correct here?
Once an PO Invoice is posted what exactly happens in the interim account?

Should I have both :
Automatic & Expected costs checked off?
Should I do it NOW or wait till Jan 1 2006?
I don't want to freak the system out!

Thanks...

Kowa

If Expected Cost is used, Report 1002 "Post Inventory Cost to G/L" (with Codeunit 5802 "Inventory Posting To G/L") will transfer the expected cost from the item value entries to these interim accounts. When the purchases are invoiced, the actual cost is available. The expected cost is set to zero and the next run "Post Inventory Cost to G/L" will post new G/L entries to balance the interim accounts to zero.

Savatage

Thanks..

Under what scenario would you check BOTH off?
Should It be one or the other? Or is Both the way to go?
And does anyone feel that this should be done JAN 1 or it doesn't matter

this has been a really big issue for us (the costs) and I don't want to screw it up more than it already is.

thanks again.

Kowa

Automatic Cost posting isn't really necessary unless the COGS and Inventory Accounts are checked on a daily basis. Usually running Report 1002 once a month is enough, e.g on the 10th of each month when all the (available) purchases have been invoiced for the previous month and after Report 795 has been run.
Automatic Cost posting will produce 2 G/L Entries for every Item Ledger Entry at the moment of posting as well as additional ones when Report 795 has determined the actual cost ( Report 1002 needs to be run regularly even if "automatic" is ON (!)), so it does accelerate database growth.
If you do not use it , it may even be sufficent to produce the G/L Entries only per posting group and not for each value entry (Option in Report 1002), which will reduce the number of G/L entries drastically.

We do not have any clients that use "Expected Cost". But if the purchase invoicing takes weeks rather than days , it might be useful to see the balance on the interim accounts to provide an improved forecast.
I don't see any problems starting with it before JAN 1 ( I'm not an accountant ), but when the fiscal year is closed the interim accounts all must balance at zero for a proper Profit and Loss evaluation.

The only way to really screw up the system is to provide an incorrect item unit cost for a positve item ledger entry and the attached value entries, no matter whether it is a purchase, positive adjustment in an item journal/ phys. inventory journal , Sales Credit Memo or a Sales Line with a negative quantity.

Savatage

On jan1 we switched from Automatic Cost Posting to Expected cost posting due to the fact that our cost were so off. Somtimes it takes 3 to 4 weeks for the vendor invoice to arrive and our costs on the PO are correct so we wanted the system to use those costs instead of waiting.

Our accountant is now seeing that our profit margin if way off by $100,000 (approx) for April & may is off too.

I was wondering if the switch could have contributed to this difference. I have just noticed that someone ticked automatic cost posting so now we have both ticked. Is this a problem? Why would nav allow both to be ticked if it wasn't ok to do so?

I'm just wondering if both are checked if something is being done twice?

with expected we are still running the adjust cost & post to g/l batch jobs.

any thoughts would be very very apprecicated

Alex_Chow

No, you're allowed to check on the Automatic Cost Posting and Expect Cost Posting at the same time. You will still need to run the adjust cost process even if the Automatic Cost Posting is checked.

I do not see a need for you to program what you want to program. The standard Navision average cost takes into account expected and actual cost already. You can do this even if you don't check on the Expected Cost Posting.

The Expected Cost Posting will only affect the G/L level. It accrues the receipts that has not been invoiced for you. However, you can accomplish the same by running the Reconcile A/P to G/L report.

Your costing problem was probably caused by a large difference between your receipt cost and your invoice cost. Are there any business reasons why your company and the vendor have not agreed to the purchase price?

Miklos_Hollender

Savatage,

instead of writing a new costing engine, maybe you could just repair the original one.

The primary reason you get crazy unit costs before posting the invoice is Item Charges.

I too found the problem that if you buy 10000 stuff for $1 each, your current inventory is 1 PCS, and the total shipping charge is $1000, and you post the shipping charge, but not yet post the goods invoice, then Navision 3.6 will happily assign that $1000 to the 1PCS and you end up with a unit priice over $1000.

This has been repaired in 3.7, you could look for the solution there.

Otherwise I can't see any reason while unadjusted unit costs should be so wild.

Savatage

sorry maybe i should have started a new post.

The code thing is out! :P

it was a project asked of me - but I talked them out of it :-#

I'm more curious about the expected vs automatic cost posting and everyones experience with one/other or both.

ara3n

For all our clients we have expected cost turned on. Basically until the invoice comes in you can find the cost based on the expected cost.
The expected cost is based on the Unit cost on po line. when you recieve it.
Which gets populated based on last direct cost from item card unless you have different costing method. I can't remmeber the details for each costing method.



So at any given time adding Inventory and Inv Account (Interim), will give you your inventory value.

Alex_Chow

The Expected Cost Posting only affects the G/L.

Without turning on the Expected Cost Posting, you can still get the expected cost when you look at the item ledger or value entry.

The expected cost posts additional entries in the G/L. We almost never turn it on since the Reconcile AP to GL report will serve the exact same purpose in regards to the accrul requirements.

We only turn it on if the customer really REALLY insists.

Miklos_Hollender

Automatic c.p. is indinspensable because when it is not turned of, users will often make errors than can only be repaired manually in the tables, such as failing to give Dimension.

Expected c.p. is very handy to be able to tell the real inventory value of a location or the rotation speed of an item group.

Alex_Chow

One last thing I want to add to this topic is that the Inventory Valuation and the Inventory to G/L Reconcile report takes into account the expected cost already, without having to turn on the Expected Cost Posting.