The indicator: contribution margin
Contribution margin says how much of each sale is left after the costs that grow with volume, so it is the figure that lets you compare customers and products with each other. It is calculated line by line and then added up, never as an average of percentages.
Net revenue = quantity × unit price − discounts − credit notes
Variable cost = quantity × standard unit cost
Contribution margin = net revenue − variable cost
Margin % = contribution margin ÷ net revenue
The guide to contribution margin explains the break-even point, the margin of safety and how to read a negative margin.
What the dashboard shows
- Margin by customer, in value and percentage, with the customers that weigh most at the top and loss-making ones highlighted.
- Margin by product and family, to see whether a drop comes from prices, purchasing or the sales mix.
- Month by month trend against the same period last year and against budget.
- A view per sales rep, filtered on each one's portfolio.
- Variances from budget, the same ones that open the alarm.
The data you need from the database
Table names change from one ERP to another; the content you need is usually this.
| Data | Where it usually sits | What it is for |
|---|---|---|
| Invoice lines | Header and lines of the sales documents | Quantity, price, line discount, customer, item, date |
| Credit notes and returns | The same tables with another document type, or separate tables | Removing from revenue what was returned or credited |
| Standard costs | Item master or a cost table by period | The variable cost of each line |
| Customers and sales reps | Customer master, or the CRM | Grouping by customer, area and rep |
The budget often lives in an Excel file: it is loaded as a source of its own and joined to the rest in the query.
The steps in Muvia
In New source choose SQL Server, give the server and a read-only user, and select the tables for headers, lines, items and customers. Muvia copies the tables on the schedule you choose, incrementally and over an encrypted TLS connection; the ERP is not queried each time the dashboard opens.
For invoice lines, the Only changes mode copies just the rows added, changed or deleted since the last refresh, deletions included: a cancelled credit note disappears from the margin too.
A query joins lines, credit notes, standard costs and customers and becomes a dataset with stable fields: net revenue, cost, margin, customer, product, rep, month.
The dashboard reads that dataset; every Monday a PDF report goes out by email to management and the sales managers.
An alarm opens when the margin of a customer or family moves away from budget beyond the threshold you choose, and the register says who took it on.
The pitfalls of an ERP on SQL Server
- Discounts at the foot of the document: if the discount sits on the header and not on the lines, it has to be spread over the lines before calculating the margin by product, or a product looks more profitable than it is.
- Which cost to use: the standard cost at the invoice date and the current one give different margins. Choose one, write it in the query and do not change it halfway through the year.
- Internal documents: intercompany invoices and test documents should be excluded with an explicit rule, not by hand in a spreadsheet.
- Currencies and units of measure: lines in another currency or unit than the item master must be converted before adding up.
Data rules can set aside lines with a missing cost or a zero price, so the margin does not change behind your back.
Frequently asked questions
No. Muvia only reads: it copies the chosen tables on a schedule, incrementally and over TLS, and does not query the database each time the dashboard opens. A read-only user is enough for the connection.
As often as the copy you schedule. For management control once a day, before the working day starts, is usually enough; the Monday report reads the most recent copy.
Yes, if the cost you want is in the data. Muvia does not impose a costing method: the cost is a field of the query, and changing it changes dashboard, report and alarm together.
The CRM is connected as another source and the query joins customers by code. The margins use case does this with the account data in Salesforce.