Skip to main content
Quanta Meridian logo
Skip example collections

All examplesExample 3 of 15

Workbook and Spreadsheet RebuildsExample 1 of 1 in this collection

View all 15 examplesReturn to Workbook and Spreadsheet Rebuilds
Workbook and Spreadsheet Rebuilds

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.

Project previewOpen full image

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?

June 2026 decisionREADY TO ISSUE

All 192 registered files are present and all 8 checks pass. The Scotland file arrived late, so the analyst reviewed its schema and totals before the sales director issued the pack.

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.

Regional sales files through Power Query to a monthly workbookEight regional monthly files pass through file and schema checks, typed Power Query stages, customer and product mappings and adjustment review. Failed rows branch to correction while accepted rows enter the sales and margin workbook.MONTHLY REGIONAL EXPORTSNORTHCENTRALSOUTHSCOTLANDLONDONNORTH WESTSOUTH WESTEASTone file · one month · one regionPOWER QUERYFILE + SCHEMA192 presentTYPEdates · valuesMAPcustomer · productCHECKkey · line valueFAILEDROWSnamed record · reason · team to correct itMONTHLY SALES + MARGINJUNE 20268 regional results8 of 8 checks passedapproved adjustments onlyREADY TO ISSUEANALYST REVIEWS LATE FILESFINANCE APPROVES CHANGESSALES DIRECTOR ISSUES OR HOLDS
  1. 01
    Receive

    Eight regional CSV files arrive for each month.

  2. 02
    Combine

    A folder query keeps the filename and appends the rows.

  3. 03
    Prepare

    Power Query assigns dates, numbers, percentages and currency types.

  4. 04
    Check

    Keys, mappings, quantities and line values are tested.

  5. 05
    Review

    Finance approves adjustments and the analyst reviews late or failed records.

  6. 06
    Issue

    The sales director receives the pack only after the checks pass.

June file-arrival register8 regional files · 6,250 invoice lines
North78805 Jul, 05:00
Central79705 Jul, 04:00
South81905 Jul, 03:00
Scotland749late · 07 Jul, 10:00
London76905 Jul, 01:00
North West77105 Jul, 00:00
South West77704 Jul, 23:00
East78004 Jul, 22:00

The Scotland CSV arrived after the expected time. It is still accepted because the file was reviewed, the columns matched and every report check passed before the sales director issued the pack.

Start with the working workbook

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 report checks pass. The workbook records one late file as reviewed and includes approved adjustments only.

08:30Files checked

192 of 192 present

08:31Scotland reviewed

Lewis Grant file received late

08:32Adjustments reviewed

48 approved items included

08:33Report checks reviewed

8 of 8 passed

08:34Pack prepared

READY TO ISSUE

June 2026 issue review

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.

Focused issue-decision worksheet

The compact workbook view keeps the month-end issue decision legible.

This is a second worksheet view from the same verified XLSX, prepared for smaller evidence and card surfaces. It keeps the late Scotland file, Lewis Grant as its owner and the issue checks beside the decision. The adjustment line shows that approved changes enter the result while pending changes remain outside it, before the revenue-to-margin bridge reaches the June margin.

Focused workbook decision · verified build

The card follows the month-end handover: review the late file, include only approved changes, reconcile £9,942,644 of sales to £3,952,326 of margin, then issue the pack.

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.

01
Folder parameterRegional exports folder

The analyst changes one workbook setting when the month closes.

02
Folder.Files192 regional CSV files

Filename, modified time, region and reporting month stay attached to each row.

03
Transform functionOne invoice-line shape

The sample file defines the expected columns and types.

04
Mapped sales rowsCustomer and product references

Unknown customer, product or cost records stay out of the reported figures.

05
June workbook resultSales director review

Only accepted invoice lines and approved finance adjustments reach the pack.

Folder combine and staged query route

The complete query definition is retained in build/power_query_steps.m and inside the workbook's DataMashup package.

June adjustment bridge

The workbook shows how invoice-line sales became the reported June result.

The bridge keeps the June sales review focused on the numbers the sales director signs off. Accepted invoice lines total £9,944,369. Approved finance adjustments reduce the month by £1,725, so the issued pack reports £9,942,644.

Accepted June invoice lines£9,944,3696,250 invoice lines
Approved finance adjustments-£1,72548 approved items in the full workbook
Reported June sales£9,942,644ready to issue after checks passed

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.

  1. 01
    Regional CSV2026-01_scotland_sales.csv

    REG-202601-004243 · 96 units · £1608.77

    The regional file retains the invoice line and the original Wide World Importers benchmark key.
  2. 02
    Typed Power Query rowstg_TypedSales

    Invoice date 2026-01-31 · product PROD-1132

    Power Query assigns the date, number and currency types before any join.
  3. 03
    Customer and product mappingstg_MappedSales

    Scotland · Chocolate frogs 250g · Display pack · unit cost £5.13

    The line receives its reporting region, product description and approved cost.
  4. 04
    Approved adjustmentADJ-202601-001

    £125.00 price correction

    The finance-approved correction remains visible instead of replacing the source value.
  5. 05
    Validationval_ReleaseChecks

    Accepted

    The key is unique, references exist and quantity, price and line value agree.
  6. 06
    Monthly sales and margin output2026-01 · Scotland

    reported sales £1733.77 · margin £1241.29

    The accepted line contributes once to its month and region.

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.

  1. FILE-01
    All 192 regional files are present

    Each region has supplied one file for every reporting month.

    Result
    PASS
    Affected rows
    0
  2. SCHEMA-01
    Every file has the expected columns

    A renamed or missing column stops the combine step before totals change.

    Result
    PASS
    Affected rows
    0
  3. KEY-01
    Invoice-line keys are unique

    No invoice line can be counted twice.

    Result
    PASS
    Affected rows
    0
  4. REF-01
    Customer and product codes are mapped

    Every accepted line has a region, owner, product and cost.

    Result
    PASS
    Affected rows
    0
  5. VALUE-01
    Source sales plus approved adjustments tie to output

    The monthly output contains the accepted source value and approved changes only.

    Result
    PASS
    Affected rows
    0
  6. COST-01
    Product costs tie to monthly output

    Margin uses the retained product cost for every accepted line.

    Result
    PASS
    Affected rows
    0
  7. ROW-01
    Every source row is accepted or held

    No source row disappears between the folder and the workbook.

    Result
    PASS
    Affected rows
    0
  8. ADJ-01
    Only approved adjustments are included

    A pending adjustment remains outside the issued sales and margin figures.

    Result
    PASS
    Affected rows
    0

Separate failed-refresh fixture

Seven deliberate defects prove that the clean result is not a permissive refresh.

These records never enter the supplied-set totals. They are retained only to prove that missing files, structural changes and invalid business records stop or divert the refresh as designed.

  1. FIX-01Missing regional file2026-06_east_sales.csv

    FILE-01 fails before the pack is issued

  2. FIX-02Renamed column2026-06_scotland_sales.csv · product_code

    SCHEMA-01 reports the missing product_id column

  3. FIX-03Duplicate invoice lineREG-202601-004243

    KEY-01 holds the second row

  4. FIX-04Unknown productPROD-9999

    REF-01 sends the row to Product Finance

  5. FIX-05Zero quantityREG-DEFECT-ZERO

    The row is held outside the monthly output

  6. FIX-06Credit without return reasonREG-DEFECT-CREDIT

    The row is held until a return reason is supplied

  7. FIX-07Unapproved adjustmentADJ-202606-PENDING

    The adjustment remains outside reported sales

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. A 26 July 2026 native check records that desktop Excel opened the 19-sheet build without repair and accepted Refresh All against the supplied local files. Today's rebuild passes the independent structural and reconciliation checks; repeat the desktop-open check if the XLSX is rebuilt again before the pack is issued.

  • The workbook is a local sample and is not connected to a live sales, finance or product cost system.
  • The build does not prove a scheduled cloud refresh, shared drive permissions or concurrent editing.
  • Dates, regional assignments, reporting variants and edge cases are deterministic generated data and are not original Microsoft transactions.
  • The example demonstrates a controlled routine but does not prove time saved or suitability for every regional reporting process.

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.

Discuss a reporting workbook rebuild