FIFO Unit Cost questions

Cuypz

Hello,

I've been looking into FIFO recently for one of our customers and I have some problems with standard unit cost behaviour. I'm wandering if some of you can shine a light in the dark about this.

Let's say I have one item X. I registered the following 3 purchase orders for it, in this order:

- Qty = 5, Unit Cost = 5 euro (super bargain)
- Qty = 10, Unit Cost = 20 euro
- Qty = 18, Unit Cost = 30 euro

Now, when our customer sells 8 pieces, he'd expect to sell the first 5 units and 3 units of the second purchase, thus having two different unit costs/profit percentages.

I've been testing this behaviour in a standard 3.70 database (since our client is working with this version) by creating those three purchase orders and then creating a sales order, and I noticed that Navision suggests an average unit cost of 23,18 euro (765 euro / 33 units). When pressing F9 to calculate my costs and profits, Navision will tell me that the costs for this sales order are 23,18 * 8 = 185,44. However, when I'm talking FIFO, I'd expect a total cost of (5 * 5) + (3 * 20) = 85 euro. I double-checked, and the costing method on the item I'm purchasing and selling is indeed FIFO, and not Average or something like that.

This is a difference of about a 100 euro and will make serious impact on my profit % calculation. Even more so when I'd like to assign a special unit price to this particular customer and I have to rely on Navision to tell me whether or not I'm making profit or not.

Not happy with this result I tested exactly the same in a standard 5.1 database, hoping that it would be a 3.70 glitch that has been fixed since, but discovered that Navision now takes 5 euro as the unit cost on the sales line and calculates the total cost and profit based on this unit cost, while I am selling three units out of 8 with a significantly higher unit cost. Once again, Navision is not providing me with the correct information to assure me that I am making profit. Here it is even more dangerous, since I'm now likely to assign a unit price well below the unit cost of 3/8 of the outgoing units.


Looking for possible fixes for this, we came up with one where we'd have to split up the sales line in to multiple lines, each with their own unit cost (in the example above, that would mean one line of 5 pieces with a unit cost of 5 euro, and one line with 3 pieces with a unit cost of 20). However, while correct, that wouldn't translate very nicely into an invoice.

A better solution would be if Navision would make an average unit cost based on the FIFO items he's about to deliver. In this case, I would expect a unit cost of 10,625 euro, since I am selling 5 units of 5 euro (= 25 euro) and 3 units of (20 euro), making a total unit cost of 85 euro for 8 units, thus 10,625 per unit. That way, cost and profit calculation would have been correct and I'd be able to pin down a unit price accordingly.


Now we have been thinking this over and our feeling is that this behaviour seems to be so obvious that it must exist within Navision. Furthermore, we would like to stay within standard Navision behaviour if possible; we know everything is possible with custom code but that doesn't work in our favor when we have to upgrade our client to a higher version.

We therefore are wondering if any of you know what it is we are missing here? Or maybe you feel that our logic is incorrect?

AdamRoue

With FIFO costing average is used until the cost is known. Running the adjust cost item routine will apportion the cost of sales correctly to the sales line. The F9 view on teh sales order never gets backdated with teh new information, but then you would never run any reports from this, and it is purely an indicator of possible profit knowing the limitations with FIFO.

If your F9 view is a marker for sales price setting and you are in a volatile cost moving environment, firstly why use FIFO? and secondly modify the F9 screen to give you the information you require.

Cuypz

Thanks for your quick reply!

Our client wants to sell his bottles of whisky in the same order he purchased them, so that sounded like FIFO music to our ears

What do you mean exactly by

Running the adjust cost item routine will apportion the cost of sales correctly to the sales line.

Is there a report/codeunit to recalculate the unit cost on the sales line based on the quantity and FIFO-wise the unit costs that go along with those items? Thus changing the unit cost on the sales line in the above example to 10,625 euro?

AdamRoue

The cost on the sales line never gets adjusted, because this is written when the line is inserted, the item ledger transaction however is updated with the value entries by the adjust cost item entry routine - this is probably one of THE most critical routines in NAV. The routine will correctly calculate the cost of the outnbound transaction and make the necessary adjustments.

The selling of items in the same order as purchased is only a financial element with FIFO - there is nothing actually tying one bottle to another.