Austen said:

I am just trying to figure out the simplest and cleanest way of clearing this up.

If 'Is there an easy way' is your question, unfortunately... no. 

Once an inventory item has been used with the wrong 'relationship', it can't be changed without the use of a time machine.

Austen said:

So far we figure we just need to clear out everything and pick a new starting date for inventory and largely ignore everything up to now.

Yes, Change every 'old' inventory item name that may be wrong (i.e. put a 'z' in front) and create new inventory items.

Adjust 'out' all on-hand quantities and value from the 'old' items, then mark them inactive.

Add on-hand quantities & value from your count to the 'new' items.  

The net difference of all those adjustments is part of cost-of-goods-sold in the period.  

Do discuss the situation with the accountant.  Provided that inventory counts were done and adjustments correctly applied at year end, the net income and balance sheet should have been reasonably correct.

I hope that helps, please post back.

Randy

hey Randy,

thanks for your response.  Ya I have rebuilt the inventory system and where ever possible I have created the new code with an A at the end to denote adjusted.  In some cases I have just had to create an all new number due to how I need to set up the inventory.  For instance they buy bottled beer in 6/12/18/24 cases but sell it by the bottle, so I created an inventory item BB#### and this lets me just enter the number of bottles bought and the cost paid.

I guess what I am wondering more than anything is if putting everything into an "inventory writeoff" account and then moving it from there to the other accounts makes sense?  The previous bookkeeper was making all kinds of J/E adjustments to try and fix the mess.  For instance if it showed purchases of $24,000 in draught she was making an adjustment to change it to say $247.  Now my issue with this is that I don't know if she actually ran the numbers right in that some of the draught seemed to be in the system fine and others didn't.  So if she was just making blanket entries based on totals and not adjusting only the messed up stuff I am left chasing my tail.  The other thing I don't know how to reconcile are all of the "inventory" items that have negative quantities but no value?  These are ones where she had a bottle set up in inventory and then a glass also set up as an inventory item but since the glass was never "purchased" the two systems could never come up with a per unit cost.  For instance the liquor gun was showing about 2 bottles worth of vodka being sold through it on volume but there was no value.  So if I need to make the adjustments to inventory here I can come up with a per unit price no problem but I just don't know where to take it from?  This is why I am wondering if using a write off account to put all these things into and pull them out of makes sense as this way I hopefully can make the actual accounts related to the items make sense.

As I have never used the inventory mod on sage before this is a bit of a learning experience for me too.  Luckily I seem to be a much better student.  I am just trying to get the flow straight in my head as to how the different accounts should look and how they should read in terms of relating to things like sales and physical inventory.

Asset- 1500 inventory : this is where the units bought is placed

Rev - 4000 sales: actual sales of items is recorded

COGS - 5000 purchases : this is where when an item is sold it goes to take it out of inventory

Var - 5001 adjustments : this is where you adjust the actual inventory to account for things like spillage or discrepancies while doing a physical inventory

So if I have this right, you buy 15 items and they go into the 1500 account.  Then you sell 5 and the sales go into 4000 and 5 goes into 5000 and out of 1500.  So now your 1500 should reflect that you have 10 units still on hand.  The 4000 account and the 5000 account then are used in order to show you your profit margins on things as it is showing you what how much you sold stuff for and how much what you sold cost you.  I think I have all that right.

So in my case when it comes to trying to "fix" things I would need to try and find a way to adjust the purchases account in order to have any numbers that remotely make sense with regards to seeing profit/loss.  So take this one draught beer where I have -$1.24mil.  If I look at the "kegs" which should be ounces I can tell that there were about 442 beers sold.  So there were only about 3-4 kegs of beer actually sold for a real cost of $600.  If I have this right, based on how I did this I should be taking $1.24 less $600 out of the write off account and applying it to the purchases account in order to remove all the erroneous money in purchases.  If all that was in this account was this beer it should then leave me with $600 in this account that would be shown as a COGS expense.

Now when it comes to the adjustments for inventory.  What I don't get is how the Adjustment thing in Inventory and Services works.  If I want to remove 150 units from item 1234 I pull up the adjustment thing, enter in the item number 1234, make sure the per unit cost is right and then I change the adjustment account to 5001.  This then means the posting will go to the 5000 and 5001 accounts?  It changes the "stock" level in 1234 but where it is actually changing things is in the "draught beer inventory" account correct?

What I am afraid of right now is that in their eagerness to get the inventory corrected that I have been forced to do things out of order and as a result I have made adjustments to the inventory based on June numbers but there are invoices that haven't yet been entered into the system from June.  So it seems like I might in fact need to reverse some of these entries, input all outstanding invoices and then make adjustments as otherwise I am adjusting adjustments.  What is super annoying here is that this should have been super simple if had when the person that set this all up noticed things were off fixed what was wrong rather than attempting to create work arounds using J/Es.

Austen said:

I guess what I am wondering more than anything is if putting everything into an "inventory writeoff" account and then moving it from there to the other accounts makes sense?

Cost-Of-Goods-Sold is always:

         Beginning Inventory

Plus       Purchases  

Minus Ending Inventory

(Whether it's recorded neatly with each sale, or in a lump sum correction after a count)

Austen said:

If I want to remove 150 units from item 1234 I pull up the adjustment thing, enter in the item number 1234, make sure the per unit cost is right and then I change the adjustment account to 5001.  This then means the posting will go to the 5000 and 5001 accounts?  It changes the "stock" level in 1234 but where it is actually changing things is in the "draught beer inventory" account correct?

If you're reducing the book quantity / value because there is not as much product 1234 physically on hand as shown on the books, the quantity and $ inventory amounts are credited (reduced) from the on-hand inventory account You can see the journal entry by pressing control-J.

Because you're working with 'accrual' (as opposed to 'Cash') accounting, you work with 'cost-of-goods-sold'.  Purchases go through inventory to become costs in an accrual system, while in a 'cash' system without inventory, every purchase is directly cost-of-goods-sold.

Austen said:

These are ones where she had a bottle set up in inventory and then a glass also set up as an inventory item but since the glass was never "purchased" the two systems could never come up with a per unit cost.  For instance the liquor gun was showing about 2 bottles worth of vodka being sold through it on volume but there was no value.

You don't / can't fix all the detail for every past transaction.  Once you post inventory adjustments to actual at (or by) the end of a period, the net effect of the adjustments is to give the correct inventory value, and the correct cost-of-goods-sold.

As I said before, I'm not expert on the details of setting up AccuPOS.  I would imagine that the AccuPOS software can be configured to cost multiple sales quantities and cost them back through multiple relationships to a single item.  But I don't know.

Sage 50 itself can ONLY keep track of ONE unit-of-measure relationship between a Purchase and Stock, and Stock to Sale, per inventory item.  

Sage 50 can be set up to: 

BUY 1 case containing 12 bottles, 

STOCK  as 12 bottles,

SELL as (72) 6 glasses per Bottle

But it can't then sell 1 case or 2 bottles, because the sales unit and price list is for a glass, period.

We can't very effectively work through this forum in detail, it's not practical to go much beyond discussing how the features of Sage 50 work, and some general theory.  Can you access someone with expertise in setting up AccuPOS? 

Austen said:

COGS - 5000 purchases : this is where when an item is sold it goes to take it out of inventory

Yes, when an inventory-linked sales transaction is posted, cost is debited to cost-of-goods-sold, and credited to the value of inventory, as well as the selling price debited to the bank / receivables / cash register and credited to sales / sales taxes

Austen said:

Var - 5001 adjustments : this is where you adjust the actual inventory to account for things like spillage or discrepancies while doing a physical inventory

No, you can keep track of that sort of variance in a separate account, but the linked inventory Variance account is where the software will automatically record a per-item cost difference after replenishing negative inventory. 

If you have 100 units with a value of $1.00 each, and sell 140, your books will show that you have minus 40 Qty, with a value of minus 40 $$.  (because a physical receipt of at least 40 units has not yet been posted.   

So now you go to post a purchase of 200 units for $226 ($1.13 each). 

The inventory should now show 160 on hand at $1.13 each worth $180.80, not ($226-40).   The cost of the 40 previously sold was already booked at $40.00, but the computer just found out it's actually $45.20.  The software will record the extra $5.20 cost as a variance, to the variance account.

Accounting is done in pen - the software absolutely NEVER goes back through the last 40 sold, to 'fix' the cost-of-good-sold to match the eventual invoice cost.

When doing a physical inventory it's important to keep track of the adjustments for shrinkage, to examine over / short quantities with an aim to learning where inventory loss is within the normal, expected range, or not. 

If you are using / allowing negative inventory, keep the inventory linked variance account strictly for automated purchase price variance transactions, do not post entries to it. 

Once you get inventory somewhat under control, it will be possible to look at the inventory count over / shortfall. 

thanks again.  With the bottles of beer I just figured it would be easier to create generic inventory items for the type of beer and then enter them into the system as bottles across the board.  So if we buy a 6 pack and a 12 pack I would just enter 18 bottles and put in the total amount paid and let Sage work out the per unit cost.  It just seemed to make the most sense as they only sell by the full bottle.  If I didn't break it down like this I would need separate buttons on accupos for each case size of beer too.

as for going back to try and fix things my idea was that I could get accupos to provide me with a monthly sales total of all units sold for Jan/Feb/Mar and then from there work out what the actual numbers should have been.  So it would tell me that for instance 2000 ounces of Black Label draught was sold and I could then work out that the purchase entry for that should be $220.  The sales figures are correct as they had the cost per units in and they all seem to make sense.  It really just the purchases accounts and the inventory accounts that are all messed up. 

what I figure is that if I know that there should have only been say $800 in draught purchases but it is showing $350,000 that I can make a J/E to correct this at the end of Jan and then do the same for Feb and Mar until I catch up to where I have fixed things.  Since I dumped things into this write off account I figured that, if in the case I put forth above with the $800 in actual purchases, I move $349,200 from the write off to the draught adjustment account and then move it from the adjustment account to the purchases account to correct that account.  I need to make sure this is the way that AccuPos posts things as I think that the 1500 inventory account is changed by the "charge" it creates for the sales of that day.

I have a sneaky suspicion that the way the "adjustment" account has been used in the past was to correct inventory levels after a physical inventory was counted.  It just blows my mind that someone could leave this how it is for over a year without addressing the core issue and correcting all the inventory accounts rather than making J/Es to attempt to fix the issue that way.

Austen said:

and I could then work out that the purchase entry for that should be $220.

Unless you're recording the purchase entry for selling a consignment that the company buys as it sells, it's technically a cost-of-goods-sold entry.  

Austen said:

if I know that there should have only been say $800 in draught purchases but it is showing $350,000 that I can make a J/E to correct this at the end of Jan and then do the same for Feb and Mar until I catch up to where I have fixed things.

Yes, you could make detail entries for each period, rather than recording a correction for the three months together.   

It would make the monthly income reports useful.  If you're recording the correction as one entry at the end of a financial year, that year will be correct in total, but the monthly comparative reports won't be terribly interesting.

There's always a need to strike a balance between the impossible work of correcting every transaction in detail, and recording a big pile of adjustments as of the last day of the financial year just to get the annual statements to the bank and the taxes filed.

Having good comparative numbers (i.e. June 1-30 income statements or bottled beer vs wine sales for the last 4 years) is what makes the accounting data useful for business operation. 

Having it together enough to submit bank reports, and file remittances and returns on time is a minimum for business survival.  To compete, you need better information. 

Austen said:

$350,000 that I can make a J/E to correct this at the end of Jan and then do the same for Feb and Mar until I catch up to where I have fixed things.  Since I dumped things into this write off account I figured that, if in the case I put forth above with the $800 in actual purchases, I move $349,200 from the write off to the draught adjustment account and then move it from the adjustment account to the purchases account to correct that account.

I think that's correct, except please stop calling it 'purchases' unless it's cash accounting or consignment (no inventory)

For clarity when posting adjustments, it's best to take the entire wrong number out, and put in the entire right number, to 'show your working out'.

If the actual cost-of-goods-sold for a particular month should have been $250, but what was actually posted was $109,375, record the journal or inventory correction entry to show both of those numbers with the correction.

i.e. (for one month)

                         dr          cr

Inventory   109375         (250/800 * 350000)

COGS                     109375

COGS           250                  (actual cost of sales

Inventory                    250

Let the computer do the math, rather than recording $349,200 as a 'magic amount' that you worked out on a spreadsheet somewhere.