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