Unknown said:

If I reversed the same sales invoice and re-entered it exactly the same the Inv/COGS still did not change to correct amount

Unknown said:

We are using FIFO for inventory

If you had been using weighted average costing, it would have recalculated the average cost partway through the adjustment, and made a bigger mess.

Unknown said:

Why?

The inventory calculations look the same if you perform them in Excel.   They're done in the order entered, just like we did it in the olden days on the paper cards.

Unknown said:

what can I do to correct this?

You can reverse the sale and the purchase, then enter the purchase, then enter the sales invoice.  To make that a little less painful, you can save multi-line invoices as recurring transactions.

To prevent it from happening, I recommend:

Always, always enter the purchase when it's received.   If you don't have the vendor invoice, uncheck 'invoice received' and put in a packing slip number. 

If your vendors can't give you prices by the time you unload the truck, look around for more organized people to do business with.

Use Sales Order prepayments if you need to get paid before you order, not an invoice.

Shut off negative inventory.

I just saw this below - is this the bigger mess you are talking about?

If I do as you suggested above will that clean everything up?

Thanks

Unknown said:

is this the bigger mess you are talking about?

Not the particular 'bigger mess' I was talking about, no!

If you use Average Cost and allow negative inventory:

 - Create a new inventory item and sell two for $100 each, the COGS is zero, but the sale is correct, and the quantity on hand is -2.

 - Then, you bring in three at $75 each.  From the purchase, an automatic 'variance adjustment' dumps 2 @ $75 to the Variance COGS account.  

        Now On hand = 1 @ $75.

 - Then, you attempt to fix the sales transaction, or the date, or add shipping information, or even change nothing, you click 'adjust' and later hit 'post'.

        Sage 50 will reverse the sale and COGS of zero, and put 2 back into inventory.  

         Now, very temporarily,  On Hand = 3 @ $75

         Sage 50 will immediately do a little calculation, and come up with $25 each as the average cost, then post 2 @ $25 to COGS.

          Now, On Hand = 1 @ $25.

<shudder>

Using FIFO, the amount reversed goes into a cost pool, and immediately comes back out of that same cost pool, so the transaction is still wrong, but at least not differently wrong, and hasn't messed up the inventory, balance sheet, and income statement numbers.

Receiving goods before having the invoice, and later adjusting the 'received goods' to add the invoice and mark 'invoice received', is messy since there will be 3 times as many transactions. 

Recording sales invoices on goods that aren't recorded as being in stock is sometimes necessary, but eliminating the necessity is important in the long term.

But most any vendor should be able to find a way to send you the invoice faster than they can send the goods.  The fax machine was invented in 1860. 

You said - so the transaction is still wrong, but at least not differently wrong, and hasn't messed up the inventory, balance sheet, and income statement numbers.

But to me it has messed up inventory since it is now increased by 12K or am I missing something here?

I'm not sure why the JE 7161 was created as it is my understanding this is an automatic entry - not manually inputted by user.

JE 7161 was automatically created to correct the inventory value on hand, because the past dozen or so sales invoices had been costed at 100X the cost.

Invoice 55947 was costed at $0.0606 each

Invoice 22979 was costed at $0.0606 each.

After invoice 405799 was posted as qty 36 at $5.783 each , instead of qty 3600 at $0.05783 each, and since the quantity on hand was still negative,

Sage 50 has updated the per-item cost in the oldest cost pool, as being $5.783 each.

The next.... let me count... 2203? were costed at $5.783 each, so at $12,722.60 instead of ($127.23?)

Invoices from 56912 onward have a more correct-looking cost of 5 cents instead of five dollars each.

So the inventory wasn't messed up by $~12.8 K, rather it was corrected by that much.

Sage 50 (Canadian) has no facility for warning on margin percentage, and the item cost is utterly hidden from the sales invoice entry person.  When an error occurs, the data suffers a sort of 'cone of destruction' spreading out from the mistake.

But, consider the above situation without allowing negative inventory:

Q. Hey, didn't we just get a shipload of those?   I'm trying to bill 100 but the computer says it's more than we have on hand?.

A. That's because you entered 36 instead of 3600 yesterday.

Problems are caught before they're giant messes at year end, and there's a few dozen wrong invoices, or assembly costs. 

Whether Negative Inventory is allowed, or not, it's best to check the Inventory Summary report occasionally:

(the 'Cost' in this screen shot is Average Cost, the 'Price' is 'Regular Price' (can't be changed In the report)  The Margin % is at Regular Price.

The Margin column in this report may not be available in all Editions of Sage 50.  The above screenshot is from Sage 50 Quantum 2015.1

ok - I get it now. They are supposed to pull up an inventory transaction report at end of day every day and then download into excel spreadsheet to sort in Margin % order. This catches any errors made so that they can be adjusted I time before the next day. Obviously that is not being done.

Thanks for your help! - I am going to show the above to them and maybe that will do something.

If they periodically run an Inventory Summary report, click on Margin % to sort it descending, the items that have wrong costs / wrong prices should be obvious:

It's easy to drill down from this report to find where it went into the ditch.  And sometimes you have to ask around to find why someone bought something for $264 and is trying to sell it for $15:

Yes - someone else told me about that but when I go to Report -> Inventory And Services -> Summary and using any combo of options I do not get the Margin column.

Is there another report that does that?

Hi,

It's possibly a special 'Quantum' report.

If it's not in the Edition the client is using, they would have to either build a custom, 3rd party report, or use VLOOKUP in Excel to stitch the Summary and Price List reports together from two reports, and then calculate the margin.

Or... possibly they need other Quantum features, and would want to upgrade.

<edit>

Not a Quantum-only report!

Thanks Richard, anyone reading this, don't use my bodgy work-around above, instead follow Richards instructions in this thread, to set the inventory options.

Smithco,

Try selecting Setup, Settings, Inventory & Services, Options, Profit Evaluation Method.  Change it, then run the report and see if you get a Margin column.  You must be using Latest Transaction date option to see the column.

Oh - that was it - It is the Quantum edition but I was not using the 'latest transaction' option - just the date as of today. Thanks Richard.

RandyW,

I am having the same issues as smithco but cant seem to figure out how to have this corrected properly (its been happening without us noticing for around 8 months). I even tried hiring outside consultants in order to remedy this but cant seem to fine anyone that even knows how to have this rectified. Would you be interested in helping us out with this issue. Please let me know ASAP sicne this is quite an urgent request for us [email protected]

thank you

Hi DariusZ,

This forum does have a way to send private messages, the only reason not to post your email address is due to the problem of spam, and there is a restriction against advertising services in this forum.   Which you weren't.

Randy

Hi DariusZ,

This forum does have a way to send private messages, the only reason not to post your email address is due to the problem of spam, and there is a restriction against advertising services in this forum.   Which you weren't.

Randy