Costing Confusion

Fred_Deutsch

We have been seeing some variances between physical Inventory and General Ledger and found some strange G/L posts that help explain it as well as some incorrect costs on Item cards. I just did the following experiment in a database copy:

1-Create new Item, Average Costing, Cost = $10.00, Selling Price = $30.00
2-Purchase (receive&invoice) 10 pieces at $10.00, Inventory hit for $100, Accounts Payable hit for $100 - so far so good
3-Purchase (receive&invoice) 10 pieces at $20.00, Inventory hit for $200, Accounts Payable hit for $200 - so far so good, except new Average Cost is $20

4-Sell 20 pieces at $30.00, Inventory hit for $400, COGS hit for $400, Revenue and Accounts Receivable of course hit for $600

Should not the Average Cost have been changed after #3 to $15.00 ?????????????????

David_Singleton

Did you run the adjust cost routine?

Fred_Deutsch

No I did not- I see these two:

Adjust Cost - Item Entries
Adjust Item Costs/Prices

I assume that you are referring to "Adjust Cost - Item Entries" and that it should have been run after the second purchase ?????

Thanks,
Fred

Alex_Chow

Fred Deutsch wrote:

I assume that you are referring to "Adjust Cost - Item Entries" and that it should have been run after the second purchase ?????

Follow David's advice and run the ACIE. What cost do you get now?

Fred_Deutsch

It changed the Average Cost to $160.00 !!!!!!!

Last Direct Cost is still at $20.00

WOW

Edit - took a very very long time to complete

David_Singleton

Fred Deutsch wrote:

It changed the Average Cost to $160.00 !!!!!!!

Last Direct Cost is still at $20.00

WOW

Edit - took a very very long time to complete

Where are you getting these numbers from, did you use the inventory valuation report?

Alex_Chow

Fred Deutsch wrote:

It changed the Average Cost to $160.00 !!!!!!!

Last Direct Cost is still at $20.00

WOW

Edit - took a very very long time to complete

Last Direct Cost does not change from teh ACIE process.

Are you sure you don't have any overhead or indirect cost on the item?

David_Singleton

There are zero onhand, so average cost is meaningless now. 8)

Fred_Deutsch

I just looked at the Item card to get those Costs.
Average Cost and Unit Cost are now at $160.00, Last Direct Cost is at $20
Indirect Cost % is 0
It does not show in the Inventory Valuation because I sold them all..
it's a temporary Item that I just made up to test, Item No., Description, Cost, Price, posting groups were all that was filled in
No BOM or anything like that added

Also just bought another 10 pcs at $10, Cost went to $10
Then bought another 10pcs at $30, cost is now $30
I am running that Adjust Cost Item Entries process now.....12 min later, Cost is $30

David_Singleton

Fred Deutsch wrote:

I just looked at the Item card to get those Costs.
Average Cost and Unit Cost are now at $160.00, Last Direct Cost is at $20
Indirect Cost % is 0
It does not show in the Inventory Valuation because I sold them all..
it's a temporary Item that I just made up to test, Item No., Description, Cost, Price, posting groups were all that was filled in
No BOM or anything like that added

Also just bought another 10 pcs at $10, Cost went to $10
Then bought another 10pcs at $30, cost is now $30
I am running that Adjust Cost Item Entries process now.....12 min later, Cost is $30

And what does it say on the Inventory valuation report.

Please forget the fields on the item card, they are only guides, they are not the actual values. Take a look at the GL and see what was posted.

Fred_Deutsch

Here are the G/L postings to the Inventory Account(15200). API's are Purchase Invoices. I don't know how to make this list all nice and aligned, sorry.

Posting Date Document No. G/L Account No. Description Amount My Notes
3/4/2010 API26878 15200 API26878 $100.00 purch. 10pcs x 10.00
3/4/2010 API26879 15200 API26879 $200.00 purch. 10pcs x 20.00
3/4/2010 24690 15200 Invoice #24690 $(400.00) Sold 20
3/4/2010 API26882 15200 API26882 $100.00 purch. 10pcs x 10.00
3/4/2010 API26883 15200 API26883 $300.00 purch. 10pcs x 30.00

Alex_Chow

I'm assuming you're using version 2.x?

What is the value on the Adjusted Cost (Invoiced Qty.) and the Cost Posted to G/L say?

Fred_Deutsch

Help, About shows "US 1.20(US2.00C)"
Pic included also -

Item No. Posting Date Entry Type Document No. Quantity Adjusted Cost (Invoiced Qty.) Cost Posted to G/L
111-11111-11 3/4/2010 Purchase APR30317 10 0 0
111-11111-11 3/4/2010 Purchase API26878 0 100 100
111-11111-11 3/4/2010 Purchase APR30318 10 0 0
111-11111-11 3/4/2010 Purchase API26879 0 200 200
111-11111-11 3/4/2010 Sale ASH27336 -20 0 0
111-11111-11 3/4/2010 Sale 24690 0 -400 -400
111-11111-11 3/4/2010 Purchase APR30321 10 0 0
111-11111-11 3/4/2010 Purchase API26882 0 100 100
111-11111-11 3/4/2010 Purchase APR30322 10 0 0
111-11111-11 3/4/2010 Purchase API26883 0 300 300


Thanks for your patience BTW

Alex_Chow

The entries look fine and you're right, the cost shoudn't be posted at $400.

I'm guessing this may be a bug in version 1.2?

Perhaps David can shed more light on this.

David_Singleton

Fred Deutsch wrote:

Here are the G/L postings to the Inventory Account(15200). API's are Purchase Invoices. I don't know how to make this list all nice and aligned, sorry.

Posting Date	Document No.	G/L Account No.	Description	Amount 		My Notes
3/4/2010		API26878			15200		API26878	 		$100.00		purch. 10pcs x 10.00
3/4/2010		API26879			15200		API26879	 		$200.00		purch. 10pcs x 20.00
3/4/2010		24690				15200		Invoice #24690	$(400.00)		Sold 20
3/4/2010		API26882			15200		API26882	 		$100.00		purch. 10pcs x 10.00
3/4/2010		API26883			15200		API26883	 		$300.00		purch. 10pcs x 30.00

Use the [ code ] tags

Fred_Deutsch

Thanks David, I'll try that now. DOH I had to uncheck "Disable BBCode"

I have been looking at the code in the "UpdateUnitCost" module in Codeunit 22 - Item Jnl.-Post Line and I have to admit it's a bit over my head. There was one mod made a long time ago by our Solution Provider but I don't thing it has any affect on this. It was done because we have a location setup for defective goods, usually returned by a customer or rejected in house and our vendor tells us to just discard, we added a field called "Cost Factor" and set that location to zero, all posting code was modified by them to multiply in the "Cost Factor" which is 1.00 for all locations except the defective whse location.

WITH ItemJnlLine DO BEGIN
   // Update unit cost
   IF ItemLedgEntry."Invoiced Quantity" > 0 THEN BEGIN // Any increase in Net Invoiced Quantity
    Item.LOCKTABLE;
    Item.FIND;

    IF (ItemLedgEntry."Entry Type" IN
       [ItemLedgEntry."Entry Type"::Purchase,ItemLedgEntry."Entry Type"::"Positive Adjmt."]) AND
       (ItemLedgEntry.Amount + ItemLedgEntry."Discount Amount" > 0)
    THEN
      Item."Last Direct Cost" :=
        ROUND(
          (ItemLedgEntry.Amount + ItemLedgEntry."Discount Amount") /
          ItemLedgEntry."Invoiced Quantity",0.00001);

    IF NOT "Drop Shipment" THEN BEGIN
      Item.CALCFIELDS("Net Invoiced Qty.");
//NAME REMOVED - Do not compute average cost if location used has a cost factor of 0 as these are always valued at zero.
      IF (Loc.GET("Location Code")) AND
         (Loc."Cost Factor" = 1) THEN BEGIN
//NAME REMOVED

          IF (Item."Net Invoiced Qty." > ItemLedgEntry."Invoiced Quantity") THEN BEGIN

            Item."Average Cost" :=
            ((Item."Net Invoiced Qty." - ItemLedgEntry."Invoiced Quantity") *
            Item."Average Cost"); // Inventory valuation for this purchase
            Item."Average Cost" :=
            Item."Average Cost" + ItemLedgEntry."Adjusted Cost (Invoiced Qty.)"; // Purchase value
            Item."Average Cost" := Item."Average Cost" / Item."Net Invoiced Qty.";
          END ELSE
            Item."Average Cost" := ItemLedgEntry."Unit Cost";
      END;
      Item.VALIDATE("Average Cost",ROUND(Item."Average Cost",0.00001));
    END;

    Item.MODIFY;
  END;
END;

It seems to me that it is falling thru to here every time:
Item."Average Cost" := ItemLedgEntry."Unit Cost";

That or I am not even looking in the right place for the code that updates the Item cost.........
I wish I could add some debugging MESSAGES to this but the codeunit is protected since it alters the Item Ledger Entry Table.

David_Singleton

1.2 was pretty problematic version. Very few countries released it, instead waiting for 1.3, which was probably the most stable version of Navision ever.

In this version, the average cost is tracked through a field in the item table. There were a lot of issues with average cost. I generally convinced clients to use FIFO in 1.2 for this reason.

Actually I always advise Navision customers against average cost, because it just doesn't work they way they expect.

Fred_Deutsch

Ugh - I cannot even imagine the pain of trying to convert all raw materials into FIFO, if even possible.

Our license according to MS allows us up to 3.6 but of course they refuse to fullfill anything.

We got tired of the 15-20% a year thing long ago. We also chose our initial Solution Provider poorly, within 6 months I was fixing their coding errors which says way more about how incompetent they were than how good I am.

David_Singleton

Fred Deutsch wrote:

Ugh - I cannot even imagine the pain of trying to convert all raw materials into FIFO, if even possible.

Our license according to MS allows us up to 3.6 but of course they refuse to fullfill anything.

We got tired of the 15-20% a year thing long ago. We also chose our initial Solution Provider poorly, within 6 months I was fixing their coding errors which says way more about how incompetent they were than how good I am.

I had a client on 1.2 that had upgraded to 2.6 and Average cost was a mess. We fixed it, but it was a huge job. It involved creating a new Average cost engine basically and new fields in the ILE to track the average cost. Big job, and probably not something you want to do. It would have been easier to move to FIFO, but they would not have that.

David_Singleton

Fred Deutsch wrote:

            Item."Average Cost" :=
            ((Item."Net Invoiced Qty." - ItemLedgEntry."Invoiced Quantity") *
            Item."Average Cost"); // Inventory valuation for this purchase
            Item."Average Cost" :=
            Item."Average Cost" + ItemLedgEntry."Adjusted Cost (Invoiced Qty.)"; // Purchase value
            Item."Average Cost" := Item."Average Cost" / Item."Net Invoiced Qty.";
          END ELSE
            Item."Average Cost" := ItemLedgEntry."Unit Cost";
      END;
      Item.VALIDATE("Average Cost",ROUND(Item."Average Cost",0.00001));
    END;

    Item.MODIFY;

This is the crazy bit. What I had to do was replace Item."Average Cost" with "ItemLedgEntry"."Average Cost". And of course take into account back dated invoices.

Fred_Deutsch

I have done the following:
Create a new blank Database, Import G/L Accounts, Cust, Vend, Company Info, etc.
Create a new Item 111-11111-11 FIFO Cost =$10, Price=$30
Buy 10 @ 10 each - All costs are now 10
Run the Post Inv to GL and then the Adj Cost Item Entries
All costs are still 10
Buy 10 more at 20 each - All costs are now 20
Run the Adj Cost Item Entries and then the Post Inv to GL
All costs are still 20
Sell 15 @ $30 each
Run the Post Inv to GL and then the Adj Cost Item Entries
All costs are still 20
Inventory GL is now at $50
I have 5 pcs left in Stock but I thought that the FIFO method would use the 10 pcs @ $10 first and then 5 of the $20 pcs
All costs are still 20

Did I do something wrong or is FIFO broken too ?

Alex_Chow

I'm placing my bet that whatever modification done to your database is broken.

You should try you test on an unmodified 1.2 database to see what you get. If you bought Navision back in 98/99, you should've received a product CD. Use the database on the CD to run your test and see if get the same results.

Fred_Deutsch

The CD I found is 2.00C (US 2.00 C)

Using the Cronus DB I did this:
Automatic Cost Posting is OFF

bought 10 at $10
Run the Post Inv to GL and then the Adj Cost Item Entries
Qty on hand is of course 10 and Inv G/L is at $100
bought 10 at $20
Run the Post Inv to GL and then the Adj Cost Item Entries
Qty on hand is of course 20 and Inv G/L is at $300
Sold 15 at $30
Run the Post Inv to GL and then the Adj Cost Item Entries
Qty on hand is of course 5 and Inv G/L is at $75
Sold 5 at $30
Qty on hand is of course 0 and Inv G/L is at $25



I also have a US 2.00 CD and a US 1.20 (US970516) CD - will try those in a bit and report


I have to just copy the files, the setup.exe I guess is 16 bit - Windows 7 doesn't like that at all

Fred_Deutsch

I have tried all 3 versions that I have and in all of those, FIFO is broken but Average is fine

This bug should be delightful to find

David_Singleton

Something is definitely wrong. I have installed many of those versions, and FIFO works fine. Not sure what you are doing to cause this.