Inventory-Inventory Items & COGS



I've acquired a client who set up their own books and failed to include an
Inventory Asset account.

All their Inventory Items are setup with the Income accounts pointing to
Sales Income and both the Asset Account AND the COGS Acocunt pointing to
COGS. Consequently the Balance Sheet shows a ZERO balance for inventory.
Actually the BS doesn't even show an Inventory Asset account since one was
never created.

So the challenge is how to remedy this. Is it as easy as creating the
Inventory Asset account, correct the Inventory Items to point to the new
Inventory account, and run an Adjust Inventory for Quantity and Price? And
since we're looking at around 1000 inventory items, is there any way to
automate this with SQL Queries from MS Access?

And this raises another question: How are Inventory Items related to the
Inventory Asset account? Is there a FIFO table that totals to the Inventory
Account? If not, then how is the detail kept consistent with the summary? And
where can I learn about the inner workings of the OA database?




Lance [MSFT]


Yes to fix this you will need to do an inventory adjustment.

Currently this guy has all the sold inventory AND all the current inventory
in the cogs account. We need to move the current inventory into an asset
account, what makes it tricky is that once an account has been used to post
the inventory value, you can't record anything there without a quantity
specified. The program won't allow a journal entry to move the inventory
back where it belongs since the journal form does not have an input field for

I'd fix this as follows

1) create some placeholder account, which will only be used to move the
inventory balance out of the cogs acount(s)
2) open adjust inventory quantity and set the quantity to zero for all
items. Save. This will credit the asset account (cogs in this case) for the
value of all current inventory and debit the placeholder account. That takes
the current inventory value out of cogs, but now we need to replace the
placeholder value with the quantities and values for the items.

3) Create the inventory asset accounts he needs.
4) change the asset account settings for all the items to point to the
proceeding to the next step. Otherwise, we'll make an error again.
5) Use adjust inventory quantity and value to adjust inventory again, this
time enter his current inventory quantities. For the offset account, pick
the placeholder account again. Now that the asset account settings have been
corrected, the item inventory accounts will get debited, and the placholder
account gets credited. Before saving the transaction, make sure the values
of the items total to the balance of the placeholder account.

Once this is done, the cogs inventory should be in the proper account(s) and
the quantities correct. To verify this, there is an item stock status report
which should show quantity and value of each item. You could look at that
double check the item quantities and value are the one you expect.

For more info on what is stored in which part of the database, download the
office accounting software development kit which should be in the downloads
list at This contains the documentation for each data object
in our dataaccess layer. The dataobjects all have same names as the tables
where their info is stored, so if you read the info on say the
customeraccount data object, that should tell you what to expect in the
customeraccount table.

That documentation could be used to write a program that connects to the
database and enters the necessary inventory adjustments for you. It should
also have information on how the inventory transactions are recordeded.

Basically, there is a list of all incoming inventory entries and a list of
all outgoing inventory entries. The two lists are linked by a 'inventory
consumptions list', which stores which incoming entries were used to record
the cogs for the outgoing entries.

I hope there's more about that in the sdk documentation, because that's
about all I know on how the info is stored.

Hope some of this helps.




Thanks Lance-

I'll give it a try and let you know how it worked.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads