What more, different quantities of the item entered in the Item Asssembly or Sales Invoice journals result in different unit cost displayed/posted:

Q'ty 1 entered -- Unit Cost shown as 1.39 -- Total Amount is CAD 139.00

Q'ty 10 -- Unit Cost 1.387 -- Total Amount is CAD 138.70

Q'ty 100 -- Unit Cost 1.3868 -- Total Amount is CAD 138.68

Eventually, after posting a few sales transactions at diferent unit quantity one finds that the Quantity on Hand for the item and the Total Value are out of sync because of these rounding differences that compound in the item inventory record throwing off the Total Value by a few cents. As you remember, we're using FIFO costing and there are rigid unit cost layers in place.

So am I missing something here, is this the "normal" behavior of Sage 50c? 

I ran a couple of tests as you did and found most of what you are saying is in fact correct based on how the program runs on my system.

I posted a purchase of a brand new item using the same numbers as you and then ran an assembly using only the one raw material component to create another item, using up all the 200 units and based on the image below, it worked fine.  This was done using the 2021.2 version (you didn't indicate which one you were using so I picked the one I had open at the time).

I couldn't test your sales transaction Qty issue because I don't know what sales you posted.

The different unit costs did happen on my system.  I can only assume that the unit costs based on the quantities are rounded at the unit cost stage due to the fact that the final costs will be rounded to two decimal places.  I'm not saying I feel that is right just that it might be the logic of the programmers.

Denver Flynn said:

item entered in the Item Asssembly or Sales Invoice journals result in different unit cost displayed/posted:

Q'ty 1 entered -- Unit Cost shown as 1.39 -- Total Amount is CAD 139.00

This is definitely bizarre and I cannot replicate this.  First, no costs are shown directly on the data entry screen of the Sales module.  Second, I can't see how your version of the program is calculating 1 * 1.39 = 139 = 100 * 1.39.  That makes no sense to me at all.

I agree the unit cost does show up as 1.39 instead of 1.3868 like it should in the Assembly module.  However, when you use the Sales module and look at the journal entry for selling one item, of course it will round to 1.39 because in final costs there is only two decimal places.

In your original version of your post before you edited, you only mentioned the unit costs, so I can only assume this is a typo but I don't see the relationship to showing costs in the sales journal at anything other than two decimals.

I used a new item because it was the easiest and cleanest way to test.  I assumed you had none in stock prior to your issue and because of FIFO lot programming, there should have been no difference in your scenario to mine.  However, if you are still running across problems, then I would manually zero the quantity and cost and put it back in at the correct qty and unit cost using the Inventory Adjustment module.

Thank for your comment, Richard.

Based on your screenshot it would appear that you used the Universal Construction file which, I believe, is based on Average Inventory and also is negative inventory-enabled. When I attemt to run an assembly in my FIFO-based file with negative inventory not allowed, using only the one raw material component to create another item, using up all the 200 units (just as you did), this is the message that I get:

Pls also note how the Inventory record and the Total Amount in the Assembly journal are off by 1c after the initial purchase transaction as per my first post.

As to my second question, all I was trying to say is that I find it puzzling that the total debits/credits outcome of posting 100 * 1 unit sales transactions (100 * 1 * 1.39 = 139) is not the same as the total debits/credits of posting a single transaction of 1 * 100 units (1 * 100 * 1.3868 = 138.68). Where's the 0.32 difference coming from and how this is going to affect the sales transactions of the remaining inventory? At least in FIFO setup, where there are supposed to be rigid unit cost pools. Maybe I don't understand something here. Could you have a look a the below screenshots please?

My universl file was switched to FIFO years ago but you are correct, I still have negative inventory still active on that file because I was too lazy to reverse everything that was negative.

I agree that the unit cost of 1 unit should still be 1.3868 but the end result in the journal entry will still be 1.39.  Are you expecting something different than 1.39?  Rounding has to happen at some point.

I see what you mean. But, say, I had to sell these 200 units one at a time each to a different client, wouldn't this result in total credits of 1.39 * 200 = 278 to the Inventory account? Whereas the original purchase transaction only debited the inventory account by 277.35? At some point I would still have a Quantity of 1 remaining on hand, but the Value would be less than what Sage expects to find for this Q'ty given the rigid unit cost in this FIFO layer, - just as it happened in the assembly transaction above. Wouldn't at this point I get another error mesage about inventory going negative as in the above screenshot? Or am I missing something here?

In other words, the inventory item is going in at 1.3868 when purchased 200 units at a time, but is being taken out at 1.39 if sold by 1 unit per transaction. Obviously the latter can't continue forever unless inventory is allowed to go negative or... a manual unit cost adjustment made at the time of the negative inventory warning message poping up?

By the way, I appreciate your time, Richard. I'm only so persistent in trying to understand this behavior of the program and the best solution for it because we found about 10 more items that are like this on the PO of 100 lines. I took these out and put them back in via the Inventory Adjustment, and now we can use these in the Item Assembly, but after these adjustments we now have a CR balance of 10c in the Inventory Cost COGS account... would need to GJ it out manually to the Variance account or something.

Other than allowing negative inventory, and doing periodic manual cost adjustment, is there any other way to deal with this? Maybe next time I'll try to enter the exchange rate with fewer decimals, i.e. 1.26 instead of 1.2607... to see if it helps.

No problem.  I had tried to get Sage to look at the inventory costing calculations in the program years ago because it goes out of balance with the balance sheet on occasion.  I know they fixed up a couple of things over the years but balances still go crazy once in a while.

I think you may have nailed some more causes of the problem and that's why I wanted to look into it.

See the image below.  After selling one item based on your scenario, these are the results left in the database.  The dOrigQty is the original lot qty for FIFO costing.  After selling one item at 1.39, this is what is left at the lot level.  Ignore row three and the first row is the original lot quantity and price when the item was first set up under FIFO (I created a brand new file with FIFO and no negative inventory).

I did get the same "negative inventory" error you did on this file that does not allow negative inventory.

I'll try and find some time to test this in 2022.1 once I get it installed.  If it is still a problem, I'm going to try to see if I can find someone at Sage that can take a look at this.  I cannot guarantee this will be fixed and it definitely won't be fixed in 2021 as that version is discontinued at the end of Mar/22.  Right now I believe your only solution is reestablish the unit price when this happens using inventory adjustments.

Thanks, Richard. We're going to update to 2022.1 soon, so hopefully Sage will look into this issue at some point. I just haven't checked the latest reports from other users on this forum to see if it's stable enough yet to update to.

One common thing I noticed about the inventory items on our PO affected by this issue, these all are purchased in Q'ty > 100 (i.e. 200, 300, 600, 1000). None of the sub-100 Q'ty items are affected (as much as we can tell without digging deep in the database as you did for the above item, that is). 

The only thing I am aware of regarding 2022.1 is that the T5018 is not printing properly and there will be another update sent out in early Feb to correct this issue before forms have to be filed for tax purposes.

Probably the >100 qty issue is due to the unit cost getting into multiple decimals rather than just two.

200 1 unit sales.  See the pdf attached.

Rounding200Sales.pdf

So basically, if I read your PDF correctly, we wouldn't be able to process the sales transaction for the last unit unless we manually adjusted the cost to whatever remained in the account at that time (assuming no further purchases)? Otherwise Sage will be looking to find 1.39, and there's only 0.74 left. Alternatively, allow negative inventory. But after reading some of the horror stories on this forum and multiple RandyW's warnings not to allow negative inventory unless absolutely necessary, I'd prefer to leave that feature disabled.

I also wonder, how allowing negative inventory would work across diiferent FIFO cost layers. Do you have any experience with this in the past?

I made no adjustments.  I just posted a sale for 1 unit based on the original 200 unit purchased.  Then did the same thing 200 times.  On the last one, the program posted the variance line on it's own so there was no error that there was not enough cost on the last full sales invoice.

And this is in FIFO file with negative inventory disabled? I wonder why then the program wouldn't automatically post 1c or whatever to Variance in the Item Assembly transaction and instead gives the error message as in my screenshot & as you reproduced it.?

I haven't traced entries that closely.  The only time a problem may come up is when doing an adjustment where part of the sale comes from one lot and the rest comes from another.  But I have not attempted to trace that because I have not had a client need that kind of detailed tracing/testing done.

Correct negative inventory was never activated on the new file.

Denver Flynn said:

why then the program wouldn't automatically post 1c

The default answer is because no one has yet told the programmers to program it that way.  This is why I want to test 2022.1 and see if it still a problem and let Sage know about it if it still works the same way.

Thanks Richard. I don't think we need that kind of detailed tracing/testing done either. I was just curious if you had to deal with something of this sort before.

Richard S. Ridings said:

The default answer is because no one has yet told the programmers to program it that way.  This is why I want to test 2022.1 and see if it still a problem and let Sage know about it if it still works the same way.

Thank you, Richard, for your help with this. Hopefully Sage will program it at some point soon.

By the way, I've read many of your posts on this forum, and I can say that what makes this resource so useful is forum users like yourself. Your experience combined with the willingness to investigate and help truly is beyond valuable!

Thank you, I appreciate that.  The many people willing to answer questions is one of the best parts of these forums.  There are a good handful of consultants, some very knowledgeable end-users and a few Sage staff (usually they don't identify themselves as staff but they help out when they can) that answer questions here.  Usually someone knows how to solve a particular problem and sometimes you get different points of view that help decide which way to go.

Last question while I still have you here, Richard. You mentioned inventory costing calculations go out of balance with the balance sheet on occasion. Our business is heavily inventory based. Any early warning signs of the above behavior that we need to watch out for? Also, should the need arise, is it ok to contact you privately if we ever need this sort of thing fixed in our file?