EBM reporting - Requirements for setup and routines in Q3

Most sales data and accounting data can be retrieved directly from Q3 with the right setups and by correctly executing the routines involved. This applies, among other things, to setup for account numbers and dimensions in accounting, product groups and account groups, different types of internal customers, setup in the makes and models register, setup for resources, routines for selling vehicles, routines for stamping and so on. Here we consider the points that are important to focus on. 

Standardized setups can be built for different industries/chains to simplify the job of setup and facilitation for each retailer. This also ensures that everyone gets the same setup, and everyone will save time and money with preparations. Exports, in turn, will increase the quality of the reporting.

Not all records can be retrieved automatically, so some must be supplemented manually.

Dimensions

The most common dimensions:

• Project number: specifies which vehicle the posting belongs to.
  • The project number is entered on the vehicle and is unique per vehicle.
• Make (Merke): specifies which make the entry belongs to.
  • The make is entered in the Makes and models register on the right node.
• Model (Modell): specifies which model the entry belongs to.
  • The model is entered in the Makes and model register on the right node.
• Department (Avdeling): specifies which department the entry belongs to.
  • When invoicing, the department is assigned automatically.
  • The department that should have the income and cost of items sold is entered in the product group.
  • The department that will bear the cost of internal invoicing is entered on the internal customer.
  • For posting vouchers in the document center and diary, the department must be entered in the profit and loss accounts manually.
• Species (Art): specifies whether the posting is Sales, Purchases, Stock transaction, Item cost, Additional cost, Additional cost after sale.
  • For posting merchandise purchases to the inventory account, you must remember to use Species (Art) = Purchase.
• Category (Kategori): specifies the type or group of the entity (vehicle).
• Other dimensions can be created if necessary to meet the reporting requirement of the supplier.

Chart of accounts - Dimensions

Dimensions are used in accounting to specify postings to achieve an additional possibility for divisions within an account using an additional dimension, which is used for various reporting purposes. Dimensions are assigned automatically during the invoicing with the correct setup. It is therefore absolutely crucial that all setup is prepared in the right way.

For the accounts that must have the various dimensions for bookkeeping, it is important to create the 'Required' dimension setting. This is set under the Dimensions tab in the entity account.

The stricter the rules are set on each account, the better the control possibilities are achieved. By setting Dimension = Required on an account, you will not get an invoice or voucher with an incorrect/missing dimension posted. Some may feel that this is too strict, but it may be necessary to ensure the best quality of book documents.

If an account requires a project number and species, for example, you will not get an outgoing invoice posted if no entity is linked to the order. This in itself is a safeguard to avoid errors in bookkeeping.

Makes and models register

Most of the logic for assigning dimensions is controlled based on the layout in the Makes and models register.

• The Makes and models register must contain all relevant makes and models that the dealer sells, and which the reporting requires. This is built as a hierarchy.
  • Makes can be such as Yamaha, Harley Davidson, Jaguar, Other Makes.
  • Model is entered under nodes for each Make.
• Dimensions are used to specify entries per make and model in the accounts.
  • Create the dimensions for all makes and models to be specified.
  • A dimension for the make and model must be entered (for each node). In other words, the make is entered on the parent node, while the model is specified below the nodes.
  • Harley Davidson is, among other things, looking for division for bike types, such as 'Street, Sportster, Softail, Touring, etc'. This is solved with a separate dimension for Category (Kategori) that is entered on each model node.
  • You also need a collection node for Other Makes and Other Models.
  • If you negotiate about makes other than the main make from the importer, and which are not to be specified at the time of export, these must be dimensioned for Other Makes and Other Models.
  • NB! It is important to remember to add the correct dimension to the new makes and models that are added later. This also applies if entities are downloaded from the "Vognkort" register and are different makes/models than what was previously created.
• When an entity is sold, the system checks which make the entity belongs to and then automatically assigns the correct make and model once the invoice is posted.
  These dimensions are the basis for the EBM reporting and also, among other things, for the circuit control report and entities stock list.
• Item numbers for New, Used and Trade-in must be entered as associations with the correct entity state, and stock customers must be entered as internal customers. If you use specific item numbers for new sales, the correct item number must be on the model node. This is setup that is done in connection with the installation and preparation of Q3 and has probably already been set up in that connection. Still recommend checking this to see that it is correct.

Vehicle (entity)

It is important that entities are provided with the correct information, as this is what guides the allocation of the right dimensions.

• Vehicles in stock must have the correct stock customer, model and state before invoicing.
• Vehicles that are traded in must have the correct external owner, model and status before invoicing.
• Vehicles in stock must have a unique project number when they are to be sold. All relevant income and costs related to the vehicle are booked with the same project number, so that the total is correct.
• The cost price for vehicles in stock must be registered, either as a parameter or booked with an incoming invoice/trade-in invoice. It is Species (Art) = Purchase which indicates that it will be cost price for the vehicle. When you sell capital goods using the model-specific item number, the cost price is retrieved from the stock log associated with that item.
• The cost should preferably be invoiced while the vehicle is in storage. Additional cost is stored and automatically gets Species (Art) = Additional cost.
• The cost that is invoiced after the vehicle has been sold must be invoiced to the customer for the Additional cost after sale. The cost automatically gets Species (Art) = Additional cost after sale.
• Change of ownership is carried out automatically when invoicing, and it is logged under the tab History - Owner history (a screenshot below; arrows 1-4) inside the vehicle card (entity). The date of ownership change is used by the export routine and other reports to recognize the time of stocking.

Sale vehicle

• In order to meet the criteria that form the basis of the EBM reporting, a complete set-up for capital items sales is required, and the sale must be carried out according to current routines. See article Invoicing of capital items for more information.
• Vehicles must be connected to a block on the sales invoice in order for the sale to receive the correct dimension value for the project number, species, make, model, category and department.
• As mentioned, the vehicle that is sold or traded in must be of the correct model, state and stock customer(owner) in order for all dimensions to be assigned correctly.
• Factory-fitted equipment is placed in the same block as the vehicle itself so that it is also assigned the correct dimensions. This must be done so that turnover and cost of items are included in the summation for the project.
• Additional equipment that is not to be included as part of the vehicle sale is placed in a separate block.
• The total cost price for a vehicle/project is the sum of Species (Art) = Purchase + Additional cost and is booked as cost of items and stock transaction for the sale.
• In this way, the inventory value for vehicles is reduced when sold per project number and the inventory account is left with the correct remaining inventory value per project number.

Internal customers

• If there are internal jobs or internal consumption of parts that should be measured separately, must be created a separate internal customer with the correct cost account. It is important to review previously created internal customers to see if they have the correct setup. None of the internal customers should be assigned to Account 1500 with the Accounting rule set to External Sales. We have seen examples of this when someone has created new customers without adjusting the settings.
• Time tracking is done on the current order to link the consumption of hours directly to the internal cost.
• Current internal customers:

                1. Additional cost for vehicles in storage are invoiced to the storage customer (own for new and used).

• Account = 14xx 'Vehicle stock'
• Accounting rule: Internal sales
• Posting type: Additional cost
• Dimension: Only department is set on the customer. Make and model come from the Makes and Models Register.

                2. Additional cost after sale are invoiced to internal customers with a cost account. Own for new and used.

• Account = 43xx 'Vehicle cost'
• Accounting rule: Internal sales
• Posting type: Additional cost after sale
• Dimension: Only department is set on the customer. Make and model come from the Makes and Models Register.

                3. Internal complaint, rework

• Account = 43xx 'Internal complaint'
• Accounting rule: Internal warranty sales
• Posting type: Internal warranty
• Dimension: Only department is set on the customer. Make and model come from the Makes and Models Register.

                4. Warranty per Make

• Account: 75xx 'Warranty cost'
• Accounting rule: Internal warranty sales
• Posting type: Internal warranty
• Dimension: Only department is set on the customer. Make and model come from the Makes and Models Register.

                5. Service for the Demo cars 

• Account = 702x 'Service and maintenance for the Demo cars'
• Accounting rule: Internal sales
• Posting type: Internal other
• Dimension: Only department is set on the customer. The Make and model come from the Makes and Models Register.
• In order to distinguish between work related to Internal sales and Internal warranty sales, two different internal accounting rules must be created (each with its own internal sales account).

For example, '3842-Sales equipment internally' and '3872-Warranty equipment internally'. These accounts are entered for all item groups that will be invoiced as 'Internal warranty'.

• Chart of accounts:
  • Check that accounts have the correct setup for dimensions with required/allowed/ignored.

Resources / Tracking time

• Tracking time must be carried out on the order/block in order to be related to the correct make/model/category. The vehicle concerned must also be entered on the order/block, and the vehicle must have the correct model. In this way, the system finds the relationships needed to find the number of invoiced hours per make/model/category.
• Tracking time on internal orders for complaints and warranty gives the same number of invoiced warranty hours per dimension.
• Mechanics must be in a separate resource group for the Workshop. This is used to document 'presence'.

Item groups / Accounting rule

• Items must be in the correct item groups.
• Item groups must have the correct accounting rule.
• Accounting rules must have the correct account setup.
• Consider minimizing the number of primary item groups to streamline the EBM reporting setup. Instead, use subgroups for detailed specifications, such as "Webshop".
• If the EBM reporting has separate codes for e.g., parts/equipment, accessories, oil/fuel, and tires, these items must be in their own item groups. This is necessary because the individual codes reference the item groups.
• If there are numerous item groups, simplifying the setup can be achieved by using the "EBM code" field within the item groups. Here, you can assign a custom code to all item groups that need to be included in an export code. In the "Item group EBM code" configuration, you can reference these values. More details on this process are provided in the article about setting up codes for Parts.
• These are prerequisites to ensure that sales, cost of items and stock transaction will be booked to the correct account.
• If EBM reporting requires that the sale of parts/equipment be specified per make, you must connect an entity to each order. This can feel somewhat cumbersome if, for example, you only want to sell window cleaners or oil to a customer. The items may be in their own item groups, with the right dimensions for the make. This will then create challenges in assigning the right make for items that fit several makes, such as oil and window cleaners. One must therefore consider how this can best be solved for the entity's dealer and look at what is most appropriate.

Order type

• Order type = Capital items sales, Service orders or Parts sales can be relevant order types in order to be able to make the necessary filters for the EBM reporting.
• It makes it possible to capture sales of parts/equipment associated with a vehicle sale, or just parts sales sold independently of vehicle sales.
• If this is a criterion for the EBM reporting, it is therefore important that the correct order type is used during the selling. In this case, we recommend creating your own order templates that have the correct order type.
• If sales are created with the wrong order type, this could lead to sales ending up on the wrong code in the EBM reporting.

Set dimensions on previous invoices

Once the settings have been completed, the new setup will apply to created invoices in the future. If changes have been made that require additional dimensions settings, these will not apply to previous invoices.

It is possible to repost an invoice to have the system automatically assign the new dimensions, if the invoicing and the routines prior to invoicing are done correctly. Administrators can do this with their own function which is available under the Finance tab inside the invoice.

It is also possible to manually enter dimensions on an invoice to get the necessary dimensions set. This is also done on an invoice under the Finance tab.

By entering these values manually, you get some good examples for running the export so that you can test along the way.

If it is desired to set the values for all previous invoices within the same year, this can be done with a database job from customer support.

Mapping for EBM reporting

See separate article for setting up and preparing EBM codes for export to e.g. Composite.