Monthly Sales and Margin Workbook Rebuild
An Excel and Power Query rebuild that replaces the monthly copying of regional sales files with a repeatable refresh, visible adjustments and a checked sales and margin pack.
The June workbook is ready to issue after all 192 regional files are present, eight checks pass and the late Scotland file is reviewed.
A recurring sales routine
One reporting analyst used to open regional files, repair columns and copy the results into the monthly margin pack.
Eight regional sales administrators send invoice line exports to a wholesale company's reporting analyst. The analyst adds product costs, checks customer regions, applies approved finance adjustments and prepares the pack for the sales director.
A missing file, renamed column, duplicate invoice line or unknown product can change sales or margin without being obvious in a copied worksheet. The decision is simple: can the June pack be issued, and what must be corrected first?
How the refresh works
Power Query repeats the preparation. People keep the decisions.
The workbook combines regional files, assigns data types, maps customers and products and checks each invoice line. Finance still approves adjustments, the analyst reviews late or failed records and the sales director decides whether to issue the pack.
- 01Receive
Eight regional CSV files arrive for each month.
- 02Combine
A folder query keeps the filename and appends the rows.
- 03Prepare
Power Query assigns dates, numbers, percentages and currency types.
- 04Check
Keys, mappings, quantities and line values are tested.
- 05Review
Finance approves adjustments and the analyst reviews late or failed records.
- 06Issue
The sales director receives the pack only after the checks pass.
Native workbook first
The June review sits inside the workbook that combines and checks the regional files.
The retained data contains 150,000 invoice lines in 192 CSV files. All 8 release checks pass. The workbook records one late file as reviewed and includes approved adjustments only.
The retained workbook shows £9,942,644 of June sales,£3,952,326 of margin and a ready to issue decision after eight checks pass.
Inspectable Power Query
The workbook contains a real folder query, transform function and staged M code.
The query sequence reads the regional folder, keeps the filename, assigns types before joins and sends failed rows to a separate result. The M code and all 15 embedded query items remain in the XLSX.
The complete query definition is retained in build/power_query_steps.m and inside the workbook's DataMashup package.
One invoice line
REG-202601-004243 can be followed from the Scotland CSV to January sales and margin.
The trace is generated from the retained invoice records. It is not a story added to the website after the workbook was built.
The Scotland file records 96 units and £1,609 of sales. Power Query maps Chocolate frogs 250g · Display pack, adds its standard cost and retains a finance-approved £125 correction.
The accepted row enters January 2026 once, at £1,734 sales and £1,241 margin.
Before the pack is issued
Every invoice line and finance adjustment has a recorded result.
The supplied set passes all 8 checks. A separate failed refresh shows what happens when a file is missing, a column changes, an invoice is copied twice or an adjustment has not been approved. Each failed row keeps the reason and the team that must act.
- 01Every expected file arrived
The file register contains one CSV for each of eight regions across all 24 months. The late Scotland file remains visible with its reviewed time.
- 02Columns and values can be read
A renamed column, unreadable date or invalid number stops the combine step before the monthly totals change.
- 03Invoice lines cannot be counted twice
The stable invoice line key is unique across all 192 files. The failed refresh proves that a duplicate is held.
- 04Customers and products are mapped
Every accepted line receives a region, named file owner, product description and approved standard cost.
- 05Invoice lines, adjustments and monthly totals agree
Accepted invoice sales plus approved adjustments equal the retained monthly result. Every invoice line is either included once or held with a reason.
Data, handover and limits
The workbook is reproducible without pretending that it is a live company system.
The regional files are deterministic non-client data derived from the official Microsoft Wide World Importers sample. Seed 20260726 creates 24 months, eight regions, 1,000 customer mappings and 2,000 product mappings. The transformed dates, reporting variants and edge cases are disclosed in the data manifest.
This build proves a local Excel and Power Query routine. It does not prove a scheduled cloud refresh, shared drive permissions, concurrent editing or a live ERP connection.
Run npm run verify:workbook-rebuild to regenerate the files, workbook, embedded queries, evidence and independent checks. Desktop Excel opened the retained XLSX without repair and accepted Refresh All against the supplied local files.
Next step
Rebuild a reporting workbook around the real monthly routine.
Start with the workbook people use, a sample of its monthly files and the steps repeated each period.