If this was a blog post, I would title it 'Negative Inventory is Mostly Evil'.  So first, this advice:

1.  Talk to an accountant about the implications of switching to FIFO from Weighted Average Cost (if you haven't).

2.  Make every effort to get ready to shut off 'negative inventory' and record receipt of goods as they arrive.

3.  If costs vary widely, depending on supplier, consider using different item numbers if the items are of different quality.  If not, get your purchasing under control so that you're buying equivalent items at the best price.   This is a bit easier in Quantum, where you can run reports showing prices by vendor.

And to answer some of your questions:

g2685516 said:

Sage is currently setup for average costing and to allow inventory to go below zero.

There is no reliable way for any inventory system to make accurate predictions about future item cost. 

Using Weighted average, and repeatedly recording an 'automatic adjustment' to a sales invoice that causes the per item quantity to go from negative to positive, and back negative again, can result in the recorded per item value to diverge exponentially.   In other words, adjustments with a small cost variation on a ten dollar item can quickly mutilate it into a million-dollar item.  

Using FIFO instead of Weighted-Average Cost will result in less confusion when sales invoices are adjusted, since sales item adjustments are less dramatic.

g2685516 said:

This item price fluctuates a bit from suppliers and was being sold before the purchase invoices were being entered.

This is not a good practice, it's best to use Purchase Orders, and record inventory when it arrives.  This requires getting supplier pricing up front, and provides a purchasing control that they are actually delivering the promised price.  

It also shows the real situation on your books, (that you owe more to suppliers, and have more inventory, than if you use 'Negative Inventory' to just bill what you don't have.) This may not be popular with the business owners / bankers.

Sage 50 has no mechanism to automatically manage consignment sales, whether the goods are 'officially' on consignment, or a more informal sort of handshake agreement with a supplier's shipper that they won't process invoices until after your company has done returns, etc.

g2685516 said:

for the past transactions the COGS value is way off. Is there a way to correct this?

If adjustments to your inventory values and quantities are made at year end, and the books were in balance, they will correct the per-item value.

There is no easy fix for the individual transactions.  Using 'Automatic adjustments' in Sage 50 will push the numbers around, and the software will attempt to correct replenishment of negative inventory, over time.  But if it's a big enough mess, it's easier to start over and enter the purchases first.

g2685516 said:

If I input a new purchase invoice now before selling anymore of this product will the cost go back to normal

(A qualified) Yes.  The inventory ledger just adds what you put in, to what's already there.   Whether in FIFO or Weighted-Average Cost, if an item quantity and value are at zero when posting a purchase invoice, there's nothing to add to, so the new cost will be what was on the last invoice.  In Weighted Average, the inventory item has no 'memory' of prior costs once the cost is re-calculated, while in FIFO, each purchase goes into a new 'cost pool'. 

in general, inputs of purchase invoices will tend to correct the inventory ledger.  This process is faster when using FIFO, since new purchases go into separate, new 'cost pools', and slower when using Weighted Average Cost, since the new purchases are thrown together, so some proportion of all the old errors are still there until the 'pool' is completely empty.  

Automatic adjustments are generated for Purchases that replenish negative inventory, so that also tends to correct per-item values.

The last Per-Item cost is used as a value for negative quantity in either case.

The last per-item Purchase Price is a separate value, and is only used to auto-fill the price field when entering Purchase Orders and Purchase Invoices.  Pro and Premium tracks this value for each item, Quantum has options to track this value for each item, for each vendor.

Weighted Average Cost works like a cup of coffee in a restaurant that gets topped up from time to time.  Starting with tea, it takes a lot of times of adding coffee to a half cup to get to coffee, or back, and it won't be purely one or the other until the cup is completely emptied and refilled.

Sales invoices from a FIFO system work more like a series of cups of coffee from a drive-through - each one is separate and gets used up before the next one is started on.

Allowing 'negative inventory' and recording 'starting value' adjustments is a good way to start the transition into using computerized inventory.  But if you don't move on from that mode, it can become an awful mess.

I could also go into managing inventory and year-ends.  In a nutshell, the Inventory Ledger (all sub-ledgers, in fact), are not 'closed off' each year end, they just carry on through.   If you record a purchase in the current year, then an invoice in 'last' year, the transactions are recorded as if there was no such thing as a year end.   So if you later run a report showing inventory values 'as of' the end of the fiscal year, you might see an item with zero quantity on hand, but with a dollar value.

I hope that helps, please post back!

Randy Wester

Thanks for the reply, Randy. Very informative. Allowing negative inventory can be evil.

I have now cleaned up all the negative values and switched off the option to allow items to go below 0 quantity. I will look into switching to the FIFO method. This will likely be better? My business deals with a lot of different inventory items and the prices do tend to fluctuate, especially now with the way our dollar is going.

I am using the Quantum edition so I have the more powerful inventory management. I just need to get everything cleaned up and start using it the way it was meant to be used. Any other tips I should know for handling things better?

g2685516 said:

I have now cleaned up all the negative values and switched off the option to allow items to go below 0 quantity.

I think you'll find that has solved most, if not all, of the costing issues.

g2685516 said:

My business deals with a lot of different inventory items and the prices do tend to fluctuate, especially now with the way our dollar is going.

If you buy in a foreign currency, and use multi-currency in Sage 50, you will have a good record of the last foreign currency item cost, so you can adjust prices by running the 'Item Price Comparison' report, export into Excel, along with a price list, to see where your selling prices may need adjustment to get the margins you need.  Getting set up to record inventory in Sage 50 on receipt can help get pricing updated, which can help your bottom line.

g2685516 said:

Any other tips I should know for handling things better?

Getting an inventory ledger to high accuracy is a monumental but rewarding task.  As it gets under control, other areas for improvement will become more visible.

Speaking of accuracy, Sage 50 doesn't have an automated way of recording full landed cost.  Allocating freight-in to individual items based on cost or weight can be done manually, to get more accurate cost-of-goods-sold numbers - again, to make sure that you're getting the margins you want.  

This may be worth doing manually (recording additional inventory cost for each shipment by backing the amounts out of Freight Expense, using the Inventory Adjustments screen), if freight is a large component of inventory cost.

Glad to hear that worked out, please post back to this forum, if you see someone here flailing in deep water, throwing them a line is greatly appreciated.