Margins and management accounting
Invoices and costs sit in the ERP, customers and sales reps in the CRM, the budget in a spreadsheet. In Muvia they become one margin by customer and by product, with the Monday report and an alarm when the numbers drift from budget.
Illustrative scenario: it describes a typical case, not a customer project.
Your margin should not depend on who ran the extract.
Every month the controlling team pulls invoices from the ERP, standard costs from another database and customers from the CRM, then stitches it all together in a spreadsheet. The answer arrives after month end, and two people doing the same sum get two different numbers.
The spreadsheet is not the problem. The problem is that the definition of margin lives in a formula nobody can see, rebuilt by hand every time the data changes.
- Who it's for
- Controllers and the management accounting team, sales leadership and general management.
What you get
- One margin
- The dashboard, the Monday report and Athena's answers start from the same query: the definition is written once and everyone can see it.
- The report sends itself
- The PDF goes out every Monday on the latest copy of the data, with no extracts and no copy and paste.
- Deviations seen mid-month
- The alarm fires on the first data refresh that pushes margin off budget, and stays in the register with the name of whoever took it on.
- The ERP never notices
- Muvia copies data on a schedule, incrementally; no analytical query ever runs against the production database.
For the technical team
How it is built in Muvia
- 1
Connect the ERP and the CRM
Add SQL Server and Oracle as sources: Muvia copies invoices, customers, items and costs every night, incrementally and over an encrypted connection, without querying the ERP live. Salesforce comes in through its connector, the budget as an Excel file.
- 2
Write the margin once
In the SQL editor, write the query that joins invoice lines, standard costs and the customer master. Save it as a dataset with stable fields: dashboards, reports and alarms all read the same definition.
- 3
Build the dashboard
Margin by customer, by product family and by sales rep, with the budget alongside. Whoever opens it filters by region or period without asking for a new extract.
- 4
Schedule the Monday report
A notebook of text and queries becomes a PDF every Monday at 8 and goes to management by email. It is always built on the latest copy of the data.
- 5
Set an alarm on deviations
A threshold on the gap between actual margin and budget opens an episode when it is crossed. In the alarm register, whoever picks it up classifies it and notes what happened.
SQL query
SELECT
toStartOfMonth(l.invoice_date) AS month,
c.company_name AS customer,
l.product_family AS family,
sum(l.net_amount) AS revenue,
sum(l.quantity * k.standard_cost) AS cost,
revenue - cost AS margin,
round(margin / nullIf(revenue, 0) * 100, 1) AS margin_pct
FROM invoice_lines AS l
INNER JOIN customers AS c ON c.code = l.customer_code
LEFT JOIN standard_costs AS k ON k.item = l.item
WHERE l.invoice_date >= toStartOfYear(today())
GROUP BY month, customer, family
ORDER BY month, margin DESC- The data it needs
- Invoice lines, customers and items from the ERP on SQL Server
- Standard costs and bills of materials from an Oracle database
- Customers, sales reps and regions from Salesforce
- Annual budget by region and product family, from an Excel file
- Current price lists, from a SharePoint folder
More cases in this line
All use cases- Demand and stock forecastingSales history and stock levels are already in the ERP. Your data scientist builds a forecasting model with scikit-learn; Muvia runs it every night next to the data and puts forecast, actual sales and understock in front of the people who reorder.
- Sales and pipelineThe CRM knows what is being negotiated; the ERP knows what was ordered and invoiced. Muvia joins them: pipeline and conversion with one definition, the customers who are ordering less, and Athena answering the sales team's questions.
- Data science on company dataYour data scientist trains wherever they like, in their own Jupyter with their own tools. Muvia gives them governed data to start from, runs the model on a schedule next to the data, and puts the scores into dashboards, alarms and reports.
Bring us a question you can't answer today.
We start from a real question your business has and walk the path from source to dashboard with your systems, not a demo dataset.