Integrating OpeneRP stock management with your financial accounts

Edit: Added Valuation Account setting

ERP software is notoriously difficult to configure and manage. This is partly a reflection of the relative immaturity of the sector, especially in the Open Source world, and the sheer complexity of designing a system that can scale from a single mom-and-pop store to a multi-company corporation.  There is also the complexity of trying to design a system that is flexible enough to accommodate the myriad different ways companies can structure their internal business processes.  A good ERP system has to be complex enough that it can be used in a wide variety of businesses in a wide variety of business sectors from a wide variety of countries with different business norms and regulatory requirements.  However, since it is incredibly hard, dare I say impossible,  to design such a system that will not require modifications for specific business circumstances it should also be easy to understand, modify, and extend.  OpenERP is an Open Source ERP platform that strikes a good balance between “complex enough to be used in many business circumstances” and “simple enough to hack on.”

Having said that, one of the areas I have had trouble with in OpenERP is having stock movements accurately reflected in the financial accounts.  This includes the entire process from purchase to sale, and all the steps in between like movement between warehouses or from warehouse to production and back to stock as a finished good.  After quite some time of trying to puzzle it out and lots of experimentation I have a better understanding now, and I hope this post can help others out there struggling with this.

Decide

The first step is deciding how you want to track your stock.  Do you want to value your stock by category (regardless of which warehouse it’s in), do you want a valuation by warehouse or do you want a combination of both. OpenERP can accommodate any of these choices but you need to decide at the outset so you know which settings to change.

From Purchase to Sale

There are several places in the purchase-stock-sale life-cycle where stock valuations need to be updated:

  1. Purchase
  2. Receipt of purchased goods
  3. Internal Transfer (i.e. – if we manufacture finished goods)
  4. Sale

Financial Accounts

When we make a purchase we don’t want it to automatically be expensed (which is what happens by default). Instead, we need an account in which we will value goods that have been bought, but not delivered yet. We’ll call this account “Goods in Transit.” We will need another account (or multiple accounts) in which we will value goods in stock. This account will be called “Stock.” OpenERP is flexible enough that we can have one account for all stock, multiple accounts based on the product or the product category, multiple accounts based on the location of the stock, or a combination of all of these.  To keep things simple we will simply have one “Stock” account for all our products. Lastly, we need an account to value the cost of goods sold when we actually sell the items.

Here are the financial accounts we will create:

  • Sales of Goods
    • Account No: 110000
    • Internal Type: other
    • User Type: Income
  • Goods in Transit
    • Account No : 230100
    • Internal Type: other
    • User Type: Asset
  • Stock
    • Account No: 230200
    • Internal Type: Other
    • User Type: Asset
  • Cost of Goods Sold
    • Account No: 510100
    • Internal Type:  Other
    • User Type: Expense
Configuring Stock Properties

Your first stop in configuring products properly is  “Product Category” configuration. You will need to enable real-time stock valuation on the product itself and also enable the “Technical Features” access right for your user before you can do this.  You may also have to enable accounting entries generation per stock movement from Settings -> Warehouse -> Accounting. Once you’ve done that refresh the page and open up the “Warehouse” top-level menu. In the configuration sub-menu click “Product Categories.” This will open up the list of product categories.  By default there are only two to begin with. Open the “All Products / Saleable” record.  Edit the following fields:

  • Income Account – “110000 Sales of Goods”
  • Expense Account – “230100 Goods in Transit”
  • Stock Input Account – “230200 Stock”
  • Stock Output Account – “510100 Cost of Goods Sold”
  • Stock Valuation Account – “230200 Stock”

Now, any products you add under the “All Products / Saleable” category (or its children) will automatically be valued in the right financial accounts. That is, when you make a purchase accounting entries will be created in the “Goods in Transit” asset account.  When the shipment is received that balance will be transferred from “Goods in Transit” to “Stock”, and when you sell it the cost price of the goods will be reflected in the “Cost of Goods Sold” expense account.

Advanced Configuration

This configuration will work for simple setups, but how about for more complicated situations. For example, how do you accurately reflect inventory losses? If you also manufacture finished goods how do you value them correctly? This is where valuation accounts on stock locations come in.  If you set the “Stock Valuation Account (Incoming)” and “Stock Valuation Account (Outgoing)” to the appropriate financial accounts OpenERP will automatically create accounting entries when goods enter or leave those stock locations. Note that in OpenERP 7 these fields are only visible in “inventory” and “production” locations.

Bringing It All Together

In OpenERP you can configure stock valuation accounts in three places:

  1. Product
  2. Product Category
  3. Stock Locations (inventory and production)

The order of precedence of these accounts is as follows:

  1. Stock Locations
  2. Product
  3. Product Category

When you receive a product or when you transfer it internally, if the source or destination location has a valuation account(s) configured it will take precedence over any values in the product or product category.  Otherwise, any values configured in the product take precedence over any values configured in the product category.  This system allows you a great deal of flexibility in valuing your products in your financial accounts. But to take advantage of them you need to enable real-time valuation of stock, enable technical features for your user (or the Administrator depending on who is doing the configuration), enable accounting entries per stock movement (Settings -> Warehouse -> Accounting), and enable management of multiple locations (Settings -> Warehouse -> Accounting).

I know it took me a while to understand stock valuation in OpenERP, so I hope my experience can help someone else out there struggling with the same thing.  If I’ve left something out or I’ve misunderstood something please let me know in the comments.

Advertisements

20 thoughts on “Integrating OpeneRP stock management with your financial accounts

  1. Dear Mike,
    in my Product category, I’ve set all the accounts as you said, excluding “Stock Valuation Account”(which you didn’t mention).
    So, when I tried to recieve one incoming shipment, I am getting an Error and it says:
    Error!
    Please define inventory valuation account on the product category: “Saleable” (id: 2)

    What may be the problem?

  2. I Have configured exactly as you have described but gets following error!!!

    “Access Denied
    The requested operation cannot be completed due to security restrictions. Please contact your system administrator.
    (Document type: Journal, Operation: read) ”

    Any Solution ?

  3. Dear Mike,
    I tried that also,
    Then I’m getting Error like :
    Error!

    Cannot create Journal Entry, Input Account of this product and Valuation account on category of this product are same.

  4. Dear Mike,
    Thank you very much for sharing. As you probably know the OpenERP documentation is very superficial so the work and experience of people like you help newbies like me to understand and get involve in helping other people and sharing knowledge.
    Thanks again.

  5. This still doesn’t appear to be correct in version 7.. check your expense account postings..
    Seems to work ok for purchases.. but appears to be incorrect when selling the item..

  6. Hi Mike,
    I’d setup product category as below:

    Account Properties
    Income Account : 200000 Product Sales
    Expense Account: 210100 Purchase

    Account Stock Properties
    Stock Input Account: 210050 Opening Stock
    Stock Output Account: 210500 Closing Stock
    Stock Valuation Account: 110100 Stocks
    Stock Journal : Stock Journal (MYR)

    The problem is that how can I set the previous month stock as opening stock for current month.
    The above setting will capture current inventory and purchase on the opening stock which I don’t want.
    Is it possible to set stock account one for previous month stock (opening stock) and one for purchase stock.

    Thanks in advance.
    Amin

    • Hi Amin,
      I am looking for same configuration. I hope you may got solution, please share your solution regarding these opening stock and closing stock values.

  7. I have been searching for months for a solution to understand inventory valuation in Openerp. You blog has been useful for me. I think you are the best person who can tell if these setting should be set at the intial stages of setting up OpenERP or would it work for already existing companies in OpenERP.

  8. ** I am trying to create these accounts in OpenERP 7, but I don’t see Internal type ‘other’ as a selection, just view, regular, etc. Also, I don’t see ‘User Type’ in the Accounting – Configuration – Accounts settings. What am I missing here? Please advise – And thanks for the article!

    Here are the financial accounts we will create:

    Sales of Goods
    Account No: 110000
    Internal Type: other ???
    User Type: Income ???

    • ‘user type’ mean ‘Account type’ that used for mostly external information.’ other’s means it’s up to you which internal type is suitable for your scenario such as Regular, Liquidity… except Payable, receivable

  9. Thank you miketelahun it’s really helpful. but i wondring about the Default Debit & Credit Account for stock Journal?
    Thank you

  10. Dear Mike:

    About your own quesiton: “” If you also manufacture finished goods how do you value them correctly? “”
    How can the program calculate it?

  11. Thank you very much for your helpful article. It makes me understand the overall configuration parameters that are not easily handle out from tones of the official documents. Very nice.

    • How can we track opening and closing stock using this article. Please Can any one tell me? I am facing this problem since 3 months on wards

  12. Hi Mike (and others),

    I’m new to OpenERP, checking if this would be a good option for a startup of my brother instead of Exact and NAV.
    I’ve did the following:
    – setup all accounts in a new Product Category
    – checked “Generate accounting entries per stock movement”
    – Created a new product with the newly setup Product Category.

    I created a PO afterwards, conformed it and did the receipt of the goods.
    I don’t see a transaction in the Chart of Accounts.

    I’m not sure what I’m missing. Do you have an idea?

    Thanks,

    Rob

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s