Build a monthly branch MIS pack from ERP dumps with SQLite

Agree four definitions before writing SQL

Most MIS arguments are definition arguments disguised as data arguments. Write these down with the finance head and put them on the cover sheet of the pack:

If head office allocates shared costs to branches, keep that as a separate, clearly labelled line. Branch managers will accept a contribution figure they can influence; they will argue forever with one that includes an allocation key they never agreed to.

Why SQLite for a pack that lives in Excel

The dumps from most ERPs arrive as CSV or Excel files with tens of thousands of lines. You can build the pack with SUMIFS, but every new month means extending ranges and every new branch means a new block of formulas. Loading the dumps into a local SQLite file (with Python's sqlite3 module, DB Browser for SQLite, or .import in the sqlite3 shell) lets you keep one query that works for any month and any number of branches. The result goes back into Excel as a single clean table for formatting and charts.

Load the dumps exactly as they arrive. Do not clean them in Excel first; any fix you make by hand is a fix you have to remember next month. Clean in the query, where it is written down.

The pack query

Synthetic data below: two mapped branches, one unmapped, two months.

-- Synthetic ERP dumps: sales lines, expense vouchers and a branch master.
CREATE TABLE sales (inv_date TEXT, branch_code TEXT, net_amount REAL, cogs REAL);
INSERT INTO sales VALUES
 ('2026-07-04','NOI',410000,287000), ('2026-07-19','NOI',395000,272000),
 ('2026-07-11','GZB',260000,190000), ('2026-07-25','GZB',240000,171000),
 ('2026-08-02','NOI',452000,309000), ('2026-08-21','NOI',431000,300000),
 ('2026-08-09','GZB',228000,166000), ('2026-08-30','GZB',251000,180000),
 ('2026-08-14','FBD',118000,84000);
CREATE TABLE expenses (exp_date TEXT, branch_code TEXT, head TEXT, amount REAL);
INSERT INTO expenses VALUES
 ('2026-07-05','NOI','Rent',90000), ('2026-07-31','NOI','Salaries',140000),
 ('2026-07-05','GZB','Rent',60000), ('2026-07-31','GZB','Salaries',95000),
 ('2026-08-05','NOI','Rent',90000), ('2026-08-31','NOI','Salaries',146000),
 ('2026-08-05','GZB','Rent',60000), ('2026-08-31','GZB','Salaries',95000),
 ('2026-08-18','GZB','Repairs',38000);
CREATE TABLE branches (branch_code TEXT PRIMARY KEY, branch_name TEXT, region TEXT);
INSERT INTO branches VALUES ('NOI','Noida','NCR East'), ('GZB','Ghaziabad','NCR East');

-- Check 1: codes in the dumps that the branch master does not know.
-- (FBD appears here. Add it to the master or the pack under-reports.)
SELECT 'sales' AS source, branch_code, SUM(net_amount) AS amount
FROM sales WHERE branch_code NOT IN (SELECT branch_code FROM branches) GROUP BY branch_code
UNION ALL
SELECT 'expenses', branch_code, SUM(amount)
FROM expenses WHERE branch_code NOT IN (SELECT branch_code FROM branches) GROUP BY branch_code;

-- The pack: one row per branch per month, unknown branches kept visible.
WITH s AS (
  SELECT strftime('%Y-%m', inv_date) AS month, branch_code,
         SUM(net_amount) AS revenue, SUM(net_amount - cogs) AS gross_margin
  FROM sales GROUP BY 1, 2
), e AS (
  SELECT strftime('%Y-%m', exp_date) AS month, branch_code, SUM(amount) AS opex
  FROM expenses GROUP BY 1, 2
), keys AS (
  SELECT month, branch_code FROM s UNION SELECT month, branch_code FROM e
), pack AS (
  SELECT k.month, k.branch_code,
         COALESCE(b.branch_name, 'UNMAPPED ' || k.branch_code) AS branch,
         COALESCE(s.revenue, 0) AS revenue,
         COALESCE(s.gross_margin, 0) AS gross_margin,
         COALESCE(e.opex, 0) AS opex
  FROM keys k
  LEFT JOIN s ON s.month = k.month AND s.branch_code = k.branch_code
  LEFT JOIN e ON e.month = k.month AND e.branch_code = k.branch_code
  LEFT JOIN branches b ON b.branch_code = k.branch_code
)
SELECT month, branch, revenue,
       ROUND(100.0 * gross_margin / NULLIF(revenue, 0), 1) AS gm_pct,
       opex,
       gross_margin - opex AS contribution,
       ROUND(100.0 * (revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0), 1)
         AS revenue_mom_pct
FROM pack
WINDOW w AS (PARTITION BY branch_code ORDER BY month)
ORDER BY month, branch;

The first SELECT is a check, not part of the pack. It finds branch codes in the dumps that the master does not contain; here, FBD with ₹1,18,000 of August sales. The second is the pack itself.

What the output tells a manager

Ghaziabad's contribution fell by ₹44,000 while its margin percentage did not move. The expense dump explains it: a ₹38,000 repairs voucher in August. That is the kind of sentence the pack's commentary should contain, and it only takes one filter on the expense table to find.

Three design choices worth copying

The `keys` CTE. If you start from the sales table and left join expenses, a branch with expenses but no sales that month disappears, along with its costs. Building the key list from both sources with UNION guarantees every month-branch pair with any activity appears once.

`UNMAPPED` instead of an inner join. An inner join to the branch master would have dropped FBD without a word, and the company total in the pack would be ₹1,18,000 lower than the ERP's. Labelling unknown codes keeps the total honest and makes the problem impossible to miss. Once the master is fixed, the label disappears on its own.

`NULLIF` in every ratio. A new branch with opex but no sales yet would otherwise cause a division by zero. NULLIF(revenue, 0) returns an empty percentage instead of an error or a misleading zero.

Tie out before you send

Run these three totals and compare them with the ERP's own reports for the month:

1. 1Sum of revenue in the pack equals the sum of net_amount in the sales dump for the same dates.
2. 2Sum of opex equals the expense dump total.
3. 3Number of distinct branches in the pack equals the branch count in the master plus any unmapped codes.

If any of these disagree, find out why before formatting anything. A beautifully formatted pack with a wrong total costs you more credibility than a late one.

Laying out the pack itself

Keep the query output on a hidden data sheet and build the visible pages from it with pivot tables or formulas. A typical order that managers can read in two minutes: company summary with the tie-out figures, branch table like the one above, top five expense movements, and a short commentary written by a person. Show amounts in lakhs on the summary if your readers prefer; the formatting options are in Show numbers in lakhs and crores. Month labels here are calendar months; to group into quarters and YTD, add the columns from Indian financial year and quarter columns.

A pack built this way takes a few minutes each month instead of a day, and the saved time goes into the commentary, which is the part people actually read. SQL for reporting like this is taught from first principles in our Data Analyst course.