inventory costing by lot

nostran

hi,

i've got a customer who imports products. Each import is treated as a lot and a lot number is assigned to the products. The costing method that he uses is FIFO(and needs to be that way). His selling price is a fixed margin based on the cost price.
What is needed is a way to keep the cost per batch or on a FIFO basis.

simple scenario tested
Costing method on item card: Price=Cost+Profit
Profit %: 20

01/01/04 bought 10 item @ 10 cents
02/02/04 bought 10 item @ 20 cents
03/03/04 sold 1. The system calculates the cost at 15 cents( i need it to be 10) and the selling price at 18.75 cents (need it to be 12 cents)


any workaround, solution, modules being sold by anyone welcomed.

thanks

ebrahma

Try costing method as specific.

Saruk

hi,

Run the following periodic activities under Invenotry module:

1)Adjust item cost entries
2)Post inventory cost to GL.


These activities will write new value entries, the cost of the item will depends upto its costing method

regards,

Saruk

chaswin

Saruk is perefectly correct. All fifo sales initially take place at the current average cost and are then corrected to the fifo cost in the adjust cost routine.

The adjust cost and post cost to GL must be run daily so that your sales are at the correct cost. Without this your sales report profit figure will be incorrect.

chaswin

Saruk is perefectly correct. All fifo sales initially take place at the current average cost and are then corrected to the fifo cost in the adjust cost routine.

The adjust cost and post cost to GL must be run daily so that your sales are at the correct cost. Without this your sales report profit figure will be incorrect.

Joe_Little

Actually, you are still going to have a bit of trouble. The adjust cost does a nice job of changing the average cost on the item ledger entries to the specific cost, but last time I checked, does not update the posted sales invoice, nor the customer ledger. This will become apparent while viewing the statistics on the invoice and customer card. The "Profit" value on the customer ledger is also used in the Customer Sales Statistics report as well, which would (last time I checked) make this inaccurate as well.

One way around this is to have the users select the applies-to item entry on the sales line itself. This transfers the selected unit cost to the sales order and "locks in" the entry. This used to be a little more trivial to customize when the serial number and lot number were entered on the sales lines rather than on the item tracking, but it is still possible if you want to go that far.

If not, maybe just make sure that the client is aware of this, and have them use the customer/item type of reporting instead (which uses the item ledger and would be correct).

It's been a while since I went down this path, so check the assertions for yourself, but I'm relatively sure that they still hold.

chaswin

Your assumption regarding posted invoices and the customer ledger entries still holds true. Both are perfectly good sources of sales analysis by revenue, but not by cost. This is a pity since these are the only places where you can produce accurate revenue figures, including sales against the GL or Resources. The only accurate source of profit is the item ledger.

But the cost is guess work. I think the inventory movement is actually matched to the fifo bucket at the time of sale, so it should theoretically be possible to mod the code to pick up actual cost from that bucket - assuming that the receipt has been invoiced. (Else the cost will be zero). The failureby Navision to receipt with a value (say the PO cost) is probably the reason they do it the way they do.

We have partially got around this issue in the past by taking the PO price as the real price, calculating a new average on receipt and treating any difference between the PO price and suppliers invoice as a variance, even in average cost environments. (This worked beautifully in 2.6)

This is a mod they appear to have sort of made in version 4.0 costing. (Although without the variance. They just calculate the average to include the expected cost).

Joe_Little

I think you are on the money when you say that Navision "sort of" handled this in 4.0. Including expected costs in the average is cool, but still results in nervious inventory values as the vouchered values come through. This is more pronounced with FIFO, especially when using manufacturing. The result is always lots of questions by auditors.

Your solution in 2.6 changes the timing of the cost and gives up the ability to see the variance by the associated departmentalization, customer grouping, etc. that would have been available if it were flowed through to COGS (as it is in the standard solution), but eliminates a myriad of headaches.

If a company can deal with this, or live with an allocation formula for variance, I think they will be much happier in the long run.

I'd love to read about any future solutions that you think up with to deal with this in 4.0. Thanks for sharing.

chaswin

I think there are more and more challenges facing us in costing going forward. The problem is that the Navision costing tries too hard! We spend an inordinate amount of time on costing issues.

The latest one is that for average costing the system calculates a "daily" average cost. This is fine except that it doesn't follow the transaction flow but adds up the positive transactions first and then calculates an average to apply to the issues. If you have a positive adjustment late in the day with a different average then every single issue for the day prior to the positive adjustment ends up getting adjusted. This can generate a huge number of adjustments for no good purpose. It's also fun trying to explain it to auditors. (And of course a back dated issue can have a ripple effect going forward as it can change the historical average).

In the past, an average cost was only changed by positive adjustments and most accountants seemed to cope. Everyone understood the process. Now nobody understands the process! Try telling an auditor that an issue can change the average! He'll think you're a bit simple.

Joe_Little

Hold the phone. What do you mean when you say "a back dated issue can have a ripple effect going forward". I don't know anything about this, but don't like the sound of what I am hearing.

I do know that if you post purchase invoices with an earlier date than you receive them, you will completely twist the inventory valuation into a pile of pig slop. (One of our clients thought this would be a handy way of dealing with purchases that were invoiced while the goods were still on the water - really bad idea).

chaswin

Consider the following transactions for a new item "TEST" with no opening stock and costing method Average. (All dates are dd/mm/yy):

15/11/04 P/INV TEST 40 at $10. Average cost $10.00
16/11/04 NEG ADJ TEST 5 at $10.
18/11/04 P/INV TEST 10 at $20. Average cost $12.222
19/11/04 NEG ADJ TEST 10 at $12.222.

So far so good. Average cost is still $12.222.

Now back date an issue:

17/11/04 NEG ADJ TEST 10 at $10. New average cost is $13.1112.

(See previous comment about auditor)

Please note that this will not happen if there are no GRN's with stock remaining. (Cost remains at 12.222 - although I hate the inconsistency of this approach).

Bottom line is that a back dated issue in an average cost environment can cause the current average cost to be recalculated!

Joe_Little

Wow, and that is about all I can say. Thanks for sharing this. It makes a strong case for using FIFO.

chaswin

Just remember to run the Adjust Cost and Post Cost to GL periodic updates at least once a day, otherwise your profit will be wrong!