Posts Tagged ‘Customer relationship management’

Safety Stock and reorder points in OpenERP

Curves of the cycle service level & fill rate ...

Image via Wikipedia

Safety Stock is the level of stock maintained to mitigate the risk of stockouts. Do you have safety stock in OpenERP? It depends. If you are running a manufacturing operation, and you manufacture to stock or build to order while keeping raw material inventory on-hand, you are actually using safety stocks. Chances are, if you don’t have safety stock you will run risks of stockouts and customer service deterioration.

Do you have a module or something like that in OpenERP? Actually no. And there might be a reason for that. But we will not go into that, we will show how to manage safety stocks and reorder points in OpenERP.

What you do have in OpenERP is the possibility of defining Minimum Stock Rules in your products. You need to do so if you are planning on using the MRP module. Actually it is mandatory to have minimum stock rules for all the raw material that you need in order to manufacture products. And you need to have minimum stock rules for your work-in-progress components in place when you build to stock work-in-progress components, or products.

The Minimum Stock Rules form allows you to define two quantities for your product. First the minimum quantity. This is a number that when it is reached by the virtual inventory, OpenERP creates a procurement in order to increase the stock of the product up to the Maximum Quantity amount (which is the second quantity in the Minimum Stock Rule).

And if you think about it, since all you need is to calculate your minimum and maximum quantities for your product, you don’t need to calculate your safety stock since it is already taken care of by the minimum quantity.

Now, how do you calculate your minimum quantity? You can do it with the following formula in LibreOffice:

Minimum Quantity = NORMINV(Service Level, Average Demand During Lead Time, Standard Deviation of Demand During Lead Time)

It’s a simple formula that can be used in LibreOffice or PostgreSQL (you need to install pgnumerics though, not a big deal)

Service level is the % of orders that you are planning to fulfill. If you are planning on satisfying average demand, you need to set it to 50%. If you are planning on satisfying 95% of your orders, you need to set it at 95%.

Average and Standard Deviation of Demand during lead time are quite straight-forward parameters. In order to calculate them you need to take all your orders you took during a period of time, and then calculate its average and standard deviation.

The minimum quantity  is composed of two components, the safety inventory piece and the lead-time demand piece. Lead-time demand can be calculated as the average demand you have for that product, and the safety stock can  be calculated as

Safety Stock = Minimum Quantity – Lead Time Demand

At this moment we are developing a module that calculates Minimum Quantity, Safety Stock and Lead Time Demand. We will keep you posted on this.


OpenERP module of the day: base_crypt (or encrypting your user password in OpenERP)

February 22, 2011 2 comments
Category:WikiProject Cryptography participants

Image via Wikipedia

This is a quick post. I found this base module which is quite useful and I wanted to share it with you. The module is base_crypt and it encrypts user passwords in the OpenERP database. By  default OpenERP stores user passwords in the res_users table, they are stored in plain text in the password field. base_crypt module encrypts all passwords with the MD5 algorithm. Below is the link for downloading the module:

IMHO, this module should be part of core since it is a good security practice to store passwords in encrypted form. In some places it is mandated by law. It is also a nice module to look at, checking its code is fun.


OpenERP module of the day: sale_delivery (or managing open orders)

February 20, 2011 Leave a comment

Image via Wikipedia

Quick Sunday post. The sale_delivery module allows you manage open orders on OpenERP. An open order is when your client orders from you a product that needs to be delivered in multiple packings. After installing the module, every time you create a sales order you will notice a new tab with the delivery planning. There you will be able to enter when  you are planning on when and how many units of your product you are planning on delivering. Later, you will be able to invoice the customer from packing

Where is this module useful? In places like Argentina. Many businesses have open orders with their clients. This is a very common use case. Other common use cases are invoicing (that is the first one) and managing stock. Not surprisingly financial accounting is not on the list, since a huge size of the economy is informal.

Have a good Sunday!

Thoughts on a modular OpenERP

February 18, 2011 2 comments
Image representing Microsoft as depicted in Cr...

Image via CrunchBase

Sharing my thoughts on how we have been using OpenERP these past four months. Couple of our customer (which happen to be small businesses) appreciate it as a full integrated system, and they are starting to use most of its modules. Starting with financial management for creating invoices and receiving payments. Pretty much managing its finances. Then they use the logistics and purchasing modules, and they have started planning for the CRM modules.

Those clients happen to be very small and they don’t have a large budget for a full-fledged ERP implementation, so they use it as they learn it. They started playing with it and from time to time they call us to help them with their questions or problems. And I think this list is growing for a couple of reasons. They find a lot of functionality and they love it. Second reason is, it gives them a chance to run away from Microsoft. We set world records when it comes to software piracy in Argentina, and the law has become stricter about it. Nowadays small businesses are being audited and are being forced to either buy Microsoft (plus other) licenses, or go open source. And many are choosing open source. And they are finding OpenERP a super option for getting rid of Microsoft Windows servers and Microsoft SQL servers. In the long run, this is a great scenario for cloud computing, but more on that on a later post.

Now, we finished a project and starting a new one. With different clients. We could safely say they are not related to OpenERP but we ended up using it. Why? Because OpenERP provided us with the infraestructure we needed for our projects. The system provided with the business objects (and forms, reports and views) necessary for deploying our applications.

In one project we needed to keep track of attendances with a fingerprint reader. So we used the contract, employee and attendances business objects provided by OpenERP. We just integrated the fingerprint reader module to the HR module. And the customer is pretty much happy with this, plus it saved us a lot of hours. Now we are starting a project where we need to calculate the safety stock, reorder point and order size for a manufacturing company. We will use the MRP module in OpenERP since it will provide us the business objects we need for this project (such as Bill of Materials).

Well, using OpenERP business objects is an scenario we never anticipated, and we are very happy with it. And we are very glad OpenERP is getting more and more modular. Plus, it is a good way to expand the OpenERP presence in customers.

Have a great weekend!

OpenERP Partners – Getting revenues from existing customers

February 13, 2011 Leave a comment
Polish OpenERP screenshot

Image via Wikipedia

Well, just wanted to share I reply I posted in the OpenERP forum. This discussion was started by Fabien Pinckaers and it is an interesting one. It is how to get more revenue from existing customers. You can also read the presentation “OpenERP Partners – Getting revenues from existing customers”. I found the discussion quite interesting and I wanted to share my reply (which is below).

Just found this discussion and I wanted to share my thoughts on this. First, I would like to make clear where I stand now. My partners and I are starting a new software company and right now we are in the stage of being incorporated. We will be a formal company in one or two months. We are not OpenERP partners yet for the reason of not being a company yet (you could think of us as a bunch of software developers working together). We intend to become partners when we are incorporated as a company.

Why are we thinking of becoming partners? Basically because we intend to use the OpenERP software and we understand good software does not happen in a vacuum and people who develop it should be encouraged to continue its development. We intend to make money of OpenERP so we will devote a percentage of our income to its founding (we had a discussion with our partners on whether to become partners or donate a percentage of the money we make in each implementation, but we are leaning towards being partners)

OK, this is what I wanted to share. The best way to make money with OpenERP is with existing customers. From time to time we will make more money with new implementations, but on the long run we will make more money with an existing customer base. Plus, they allow you build a sustainable business.

What is the best way to increase this incremental revenue? Find new ways of either reducing costs or boosting sales. OpenERP (IMHO) is not about a technical proposition but a business proposition, which will allow business reduce its expenses or increase its revenues. How will this software do that? By understanding the customer business and its competitive environment. There is no secret to this. By understanding how the client makes or saves money, we will be able to propose new functionalities or projects that will allow us more money with the customer.

For example, we have a client who will soon implement OpenERP. Why is this client thinking of OpenERP? Because if it is well implemented it will reduce the stock, and the customer is thinking of reducing its stock in millions (OK, pesos, but still millions!!!). What the client needs is to reduce the expenses associated with inventory. The customer is thinking of implementing the stock, mrp and purchase modules.

But after implementing those modules, the customer will need to improve its forecast system. Why? Because the first step in inventory optimization is having a good forecast. And we will need to develop a forecast module for forecasting the demand of thousands of products. That will be a profitable second project (and we will be glad to share that module with the community). And after the forecast improvement module, we will need to implement the CRM module.

That is a good example of how aligning the OpenERP implementation with the business initiatives or business needs boosts the partner revenues. Our customer is not the people in IT (in two of our customers the IT department is only one guy doing whatever it takes to keep the computers and servers running). It’s the people in production, accounting, sales and marketing. It is understanding how to increase sales for the customer, or reduce its costs. Understanding this makes the OpenERP an easier sell (not easy, but much easier). And alleviates the pressure on the team.

A last thought, I think understanding the customer business will create the need for additional modules or applications. Such as Activity Based Costing module, Markdown management module, Revenue management module and so on. I think implementing these modules will generate very  interesting revenue opportunities for partners as well.

Well, just wanted to share my thoughts. Have a great Sunday!

OpenERP module of the day: account_invoice_salesman

February 3, 2011 1 comment

Last week a customer asked us how to enter the salesman information on the invoice. He needed to keep track of which salesman was selling what. My instant answer was “you could do that with analytic accounts”, but told him I would check it. And I found the account_invoice_salesman module.

This module can be downloaded from the  documentation page. No need to use bazaar for this. You can either download it with your browser or with this command (in case you are using Linux)

# wget

Next step is to import the module and install it. The installation is quite straightforward and does not need any configuration. What does module do? Two things

  1. Creates the user_id field in the account.invoice class, with the “Salesman” label. This field references the res.users class, so each salesman nees to have a user created in OpenERP
  2. Adds the Salesman field to the account_invoice form

Below is a screenshot of the invoice form


Business Intelligence, integrating OpenERP and Mondrian

Crystal Reports

Image via Wikipedia

These days we are implementing a custom module we developed for a client, and the customer asked us for hundreds, if not  thousands, of reports. So, faced with the option of devoting hundreds of development hours to create reports, we decided to implement an OLAP and reporting tool for the customer. And integrating that tool with OpenERP. After making a quick assessment among the different business intelligence tools available in the open source “market”, we decided to go with JasperServer and iReport.

You can download both tools from the JasperForge website. Installing them was quite simple and straightforward, and in no time I was running reports against the OpenERP database. iReport is a report generation tool, and reminds me of Crystal Reports. JasperServer is a report and OLAP server. It can display the reports created with iReport and work with Mondrian cubes (more on this later).

I will not go into the details on how to work with both tools. I would like to talk about how I generated a cube from an existing OpenERP database. Well, this is the “DON’T TRY THIS AT HOME” section. Doing what I will explain in the next paragraphs is a very bad idea in a production setting, since OLAP calculations are resource hungry on the server, and this could run out of resources pretty soon if you are servicing your users and OLAP calculations.

Well, after making the disclaimer, I will proceed into explaining how to create an OLAP (actually ROLAP since in no moment I created a physical cube) from the OpenERP database. The first step is to create the Data Source in JasperServer. It is quite straightforward and JasperServer comes with a Test button which allows you check whether the connection settings you just created are right or wrong.

The next step is to create an OLAP schema. An OLAP schema in JasperServer (and the Mondrian world) is a XML file which pretty much describes the OLAP cube. In this file you describe the fact table, the dimesions, its elements and aggregations. For example, below you will find an example I created for the sales order table in OpenERP:

<?xml version=”1.0″?>
<Schema name=”SaleOrderSchema”>
<Cube name=”SaleOrder”>
<Table name=”sale_order”/>
<Dimension name=”Cliente” foreignKey=”partner_id”>
<Hierarchy hasAll=”true” allMemberName=”Todos los clientes” primaryKey=”id”>
<Table name=”res_partner”/>
<Level name=”Cliente” column=”name” uniqueMembers=”true”/>
<Dimension name=”Picking Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todas las policies”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Picking Policy” column=”picking_policy” uniqueMembers=”true” />
<Dimension name=”State”>
<Hierarchy hasAll=”true” allMemberName=”Todos los estados”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”State” column=”state” uniqueMembers=”true” />
<Dimension name=”Order Policy”>
<Hierarchy hasAll=”true” allMemberName=”Todos las ordenes”>
<!– No table element here.
Fact table is assumed. –>
<Level name=”Order Policy” column=”order_policy” uniqueMembers=”true” />

<Measure name=”Cantidad” column=”id” aggregator=”count” formatString=”Standard”/>
<Measure name=”Monto” column=”amount_total” aggregator=”sum” formatString=”Standard”/>
In this example, I have a fact table which is based on the sale_order table with three inline dimensions (order policy, picking policy and state) and the partner dimension. The cube has two measures, which is quantity and amount. The next step is to create the analysis connection, which connects the OLAP schema with the datasource. And the final step is to create the view. An analysis view is a query that pulls the information from the database, this is done with MDX, which is a query language similar to SQL for pulling OLAP information. An example of an MDX query is listed below:

select {[Measures].[Cantidad], [Measures].[Monto]} ON COLUMNS,
NON EMPTY {([Picking Policy].[Todas las policies],[State].[Todos los estados],[Order Policy].[Todos las ordenes])} ON ROWS
from [SaleOrder]

After creating the view (and entering the MDX) you will be ready to work with the OLAP cube. Just click on the view and you will see an screen like this,

Now you will be able to slice and dice your information,