Most Powerful Open Source ERP

How to fill the Category Spreadsheet.

This Presentation explains how to fill the Category Spreadsheet.
  • Last Update:2023-06-02
  • Version:004
  • Language:en

How to fill the Category Spreadsheet

This visual guide is part of a collection of documents created by the One Student One ERP (OSOE) project in collaboration with Institut Mines Telecom, Telecom Bretagne, Dresden University of Technology and the South Westfalia University of Applied Sciences. It can be used to teach modern ERP theory and practice to undergraduate students or professionals.

Copyright: You are free to copy, distribute, display, and perform the work under the following conditions: you must attribute the work in the manner specified by the author or licensor; you may not use this work for any commercial purposes including training, consulting, advertising, self-advertising, publishing, etc.; you may not alter, transform, or build upon this work. For any reuse or distribution, you must make clear to others the license terms of this work. Any of these conditions can be waived if you get permission from the copyright holder through a commercial license or an educational license. For more information, contact info@nexedi.com

Agenda

  • What do you need categories for
  • How to fill the Category Spreadsheet
  • How to distinguish the different categories from each other

The guide will teach you how to fill the category spreadsheet, containing general advice as well as a detailed description of each category.

What do you need categories for?

What do you need categories for?

Spreadsheets are used to transfer the configuration information from the real world into the ERP5 system, in order to configure an ERP system.

Category Configuration Example

You can Download an Example Category Configuration of a "Fish Shop" here: P-OSOE-Configuration.Sample

What are "categories"?

What are "categories"?

What are categories and what are they used for?

Categories are facets of the organization that is modeled. All facets are bundled in one configuration spreadsheet. In order to configure the ERP system, each facet has to be configured. They are found in the bottom bar of spreadsheet template you have downloaded.

Fill the lines...

Fill the lines...

To configure a facet, you have to fill up its lines in the category spreadsheet.

Each facet has the columns Path, ID, Reference, Codification, Title, Short Title and Description. The orange fields don't have to be filled, therefore only Path, ID, Title, Short Title and Description remain.

Keep in mind that ID always has to consist of small letters without special characters. And it's important to add a description to each line to provide information about the entity in the ERP system.

Path: Categories Hierarchy

Path: Categories Hierarchy

The first columns are reserved for hierarchy adjustment. Hierarchies are set by adjusting star characters in the first rows based on the Object hierarchy depth. In the example provided the cashier function is a subordinate of the shop function. Therefore, the shop line's star is in the first column and the cashier star is adjusted in the second column. The required hierarchy depth depends on the category facet. The goal is always to model the company as accurately as possible. If more detail is needed, the configuration enables you to model hierarchies with a maximum depth of seven.

You vs. External World

You vs. External World

Each organization has corresponding partners. In order to configure your organization's ERP system, not only the organization itself has to be modeled. Additionally, the corresponding clients and suppliers from the external world need to be included in the modeling process. As a consequence, the configuration needs to encompass your company's objects, as well as the related objects from the external world.

Configuration Scope

Configuration Scope

A company can use ERP5 for all its processes or for some parts only. The grade of how much is covered is referred to as "scope". For example, a company could use ERP5 for the sales department only, but still, the configuration has to be done for all parts of the company.

Category Details

Category Details

The shown categories have to be filled. The following slides will discuss the different facets in more detail.

Site

Site

In this sheet of the configuration spreadsheet, define the physical sites of the organisation. Sites are structured as a tree of sites with children sites. The level of detail may be different from one site to another. For example, a small retail site may be known only by its city or region name. A factory may on the contrary describe 6 levels of hierarchy, from the regional name to the identifier of a storage cell in one of multiple warehouses of the factory.

If reporting is needed on groups of sites, then it can be useful to group sites per regions based on the reporting requirements.

It is preferable to use region than cities. This will eliminate the need for changes in case an office moves from one city to another.

Security codes must be defined for a selection of sites, at an appropriate grain level. It would be for example useless to define different securities based on the storage cell of a warehouse. However, it can be useful to differentiate security permissions for each warehouse of the same factory. A warehouse clerk of warehouse A may be prevented from entering inventories for warehouse B.

Group

Group

In this sheet, define the juridical structure of your organisation with its subsidiaries and business units. This juridical structure will be used of analytical accounting. If the organisation is simple, a single group is enough.

Also define in this sheet your own vision on the structure of third party business entities (clients, suppliers, partners) with your own view on how they are structured. For example, if you have some business with a group of companies named "partner_group", you may want to differentiate its 2 entities (info service and administrative), especially if only info service employees are working with you as partners and if they are allowed to access your own ERP5 system within a specific partnership contract which grants them access to some documents.

Just as for site, security codes must be defined at reasonable granularity level. It is useful to differentiate entities with different security. It is useless to define different codes for entities which share the same security policies.

Function

Function

In this sheet, describe the functional structure of your organisation and of organisations you are in business with.

Organisation functions are implemented by nodes (ex. factory, shop) of the function tree. Person functions are implemented by leaves (ex. factory/manager, shop/sales).

Please make sure you understand the difference between grade and function. For example, a person whose grade is "director of research" may be assigned a function of "factory manager". Think of grades as in an army (ex. general, commander) and of functions as assigned missions (ex. researcher, spy).

Role

Role

In this sheet, describe the roles of the organization. People have different roles in the company. A possible configuration is:

- internal: A company, an office or a person within the company's group

- external: Persons who work for the organisation, but they are not permanent employees, such as temporary staff, leased laborer, consultant, etc.

- client: A client which buys goods or services from the company group

- supplier: A supplier which provides goods or services to the company group

- administration: An administrative body (ex. tax office, registration office)

- sales lead: A person or an organisation that is potentially interested in purchasing a product or service from the company group (first stage in sales process)

- sales prospect: A person or an organisation that is interested in purchasing a product or service from the group (qualified sales lead)

- user: User is for example a user of an online forum of the company, it may not be a client of the company.

- member: For some association or organisation which are not a company, there are members (active member who are voted into the board to have decision right, and non-active members).

These roles are proposals and have to be checked for completeness and applicability in your company.

Grade

Grade

Grade describes the position in an organisation from a "honorific" point of view. Salary is also usually based on grade, rather than on function.

Typical example of grade can be:

- owner: You can use "owner" for shareholder, partner or associate of the organization, who has the decision power in the board.

- executive: For the company managers who are "executives", e.g. CEO /CFO /CIO, you can use "executive" as grade. Note that for the other managers, they are treated as employees from a work law point of view, so they belong to the grade "employee".

- employee: Full-time or part-time employees.

- member: As stated in "Role", for some associations or organisations which are not a company, maybe there is not an owner, executive manager or employees, but there are members (active member who are voted into the board to have decision right, and non-active members). So you can use "member" as grade for this kind of organisations.

- trainee: Please remember this grade when there are trainees in the organisation.

There is no direct relation between grade and function. Grades differ from function in the sense that function describes actual operational position while grade describes a honorific position. A typical example is in the army, there are grades such as "General" and "Commander". Some army Generals are sometimes assigned to research management functions. Their grade is still general but their function is Director of R&D Center

Region

Region

Please fill here the list of regions and countries in the world according to your point of view.

UNO standards exist for this. However, they do not cover business concepts such as EMEA (Europe, Middle East, Asia). Moreover, the choice of a list of regions includes both commercial and political consequences.

For example, it is usual when sending a letter to Barcelona to locate Barcelona in Catalunya, Espagne (French is the standard for international mail). This would not be the case in France where few people consider writing to someone in Lyon, Rhone-Alpes, France but would write to someone in Lyon, France.

Consider also the case of Corsica which is more sensitive, or of any region in which part of its inhabitants consider that they belong to a country which aims at becoming independent from what they view as an occupation force. There numerous examples of this kind, including Taiwan which is a major industrial country and which is not considered as such by UNO.

Another example is Japan, which most inhabitants consider that they are not part of Asia (China is part of Asia for them). Doing business in Asia often requires a special treatment for Japan, which is considered as a continent on its own, whereas China influenced countries are put in a single group.

We advice you making your own classification of regions in the world and making it as independent as possible of future changes (will Taiwan be part of China in 10 years ? will New Caledonia become independent in 5 years ?). Geopolitics may evolve, but business remains.

Regions need also sometimes a higher level of precision, but not uniformly. A European business does not care about states in the USA. However, a USA business needs to keep track of its clients based on the state information. USA states should therefore appear in the region category for a USA business. The same is true for a Japanese business, which probably needs prefectures (ex. Kanagawa) yet does not use very often the political concept of "region" (ex. Chubu) which does not make much sense usually in Japanese business culture. And a local business may require a precision at the level of the different areas in the same city.

Region

Region

The provided examples show three basic configurations:

International organizations may consider structuring the regions into continents. The hierarchy can be more detailed on the second level. For example, Europe could be further divided into northern and southern Europe or relevant countries.

Organizations and companies, which are focused on several countries could consider structuring the regions into those countries.

Small companies with local market alignment may structure their regions based on smaller geographical units like states. It is possible to combine the level of modeling granularity by using the hierarchy. The level of detail depends on the requirements of your company.

Remaining markets could be assigned to an "Other" or "Rest of World" region group.

Skill

Skill

Please fill the list of skills which you want to associate to Person in order to qualify them.

First consider people inside your organisation and their skills. Afterwards consider people outside your organisation and the skills they provide to your organisation.

Nationality

Nationality

It's time to fill the list of nationalities in the world according to your point of view.

UNO standards exist for this. However, they do not cover all nationalities. For example, the nationality of a Chinese from mainland and a Chinese from HK is treated quite differently by immigration services in Western Europe.

A person from Taiwan is considered as Taiwanese by some people and as Chinese by others. His or her passport mentions "Republic of China". Addressing to a Taiwan inhabitant as he or she was Chinese can sometimes create a lot of frustration.

Another interesting case is the case of group of counties (EU, CEDEAO, etc.) which inhabitants carry a passport with a dual mention: the group of countries and the country itself.

We advise you to define your own view on nationalities so that it serves best your business needs.

Activity - industry segments

Activity - industry segments

Activities describe your view on all possible economic activities in the world. There are UNO, European standards for activities. However, it is better to define your own classification based on specific business requirements

Activities are different from function. Activities usually relate to a classification of third parties based on the nature of their industry (e.g. banking, IT, automotive) whereas function is independent of the activity (i.e. both an IT and an automotive company can have entity which has the function of a warehouse).

Activity - industry segments

Activity - industry segments

Activities describe your view on all possible economic activities in the world. There are UNO, European standards for activities. However, it is better to define your own classification based on specific business requirements

Activities are different from functions. Activities usually relate to a classification of third parties based on the nature of their industry (ex. banking, IT, automotive) whereas function is independent of the activity (i.e. both an IT and an automotive company can have entity which has the function of a warehouse).

Publication Section

Publication Section

Please fill here the list document types in your company.

A document type is for example: a contract, a status report, a letter, etc. The different document types which are involved in your daily business can be stored in the ERP, either autonomously (ex. Letter) or in relation with a business document (ex. a specification document can be used to support a Sale Order).

Product Line

Product Line

Please fill here the list of product lines sold or purchased by your company.

Product lines are used for both purchase and sales. They are useful to create a catalogue of products (sold or purchased) and to structure a large database of products and services by families.

Grading of the Configurations

Grading of the Configurations

The configurations are graded by the tutor based on how clear the company is modeled in the configuration.

Columns that have to be corrected by the tutor are penalized.

For both, questionnaire and configuration filling, you get the possibility to improve the answers and configuration based on the comments made by the tutor. Although you get two marks (one after the first submission and one after your correction of the results) only the second mark is relevant for your final grading. As a result, it is possible to reach the maximum number of points if the correction of the configuration is done properly.