Standing Up Reporting a Client or Auditor Will Accept in Weeks

To produce reporting an auditor or a major client will accept when the data sits in scattered Excel files and systems that disagree, do not start by building a dashboard. First freeze and snapshot every source at one cut-off date so the figures stop moving. Define each metric in writing and get its owner to agree it before anything is built. Stage the data with row-level validation and a reconciliation report that accounts for every row read, loaded or rejected. Publish one agreed figure end to end before adding more, with lineage showing where the number came from, because that is what an auditor actually tests. Then add the controls that stop the reporting drifting back to hand-edited spreadsheets.

Why does reporting built from scattered spreadsheets fail an audit?

Rarely because the arithmetic is wrong. It fails because nobody can evidence it. Three copies of the same workbook exist with different totals, a column was overwritten last quarter and nobody knows by whom, one team counts a contract when it is signed and another when it is invoiced, and the person who built the lookup formulas has moved on. Each spreadsheet may be internally correct and the set still cannot answer the questions an auditor or a client's assurance team asks: which version is authoritative, who changed it, what the figure means and how to get back from the figure to the underlying records.

That is good news for a deadline. Evidence is a process problem, and a process can be stood up in weeks with tools most organisations already license, long before a new system could be selected and implemented.

How do you freeze and snapshot the sources so the figures stop moving?

Nothing downstream can be reconciled while the inputs are still being edited. Declare a cut-off date, then capture everything that feeds the figures as it stood at that date.

1. Build a source register: every workbook, export and system in scope, with its location, owner, last modified date, what it is used for and whether people still edit it.
2. Copy each file into a restricted snapshot location, such as a SharePoint library with versioning on and edit rights removed, and put the cut-off date in the file name.
3. Record a file hash for each snapshot (PowerShell Get-FileHash does this), so you can later prove the file used is the file captured.
4. Take extracts from any system in scope, such as Dynamics 365 or an accounting package, for the same cut-off, and store them beside the spreadsheets.
5. Agree a rule for late changes: corrections found after the cut-off go into the next period or into a recorded adjustment, never back into the snapshot.

The live spreadsheets can carry on being used by the business. The reporting now reads only the snapshots.

How do you define each metric in writing before building anything?

Conflicting spreadsheets are often two correct answers to different questions. Before any data is staged, write one definition per metric and get it signed by the person accountable for the number, and, where a contract or a regulator defines the figure, check the definition against that wording.

• Name and the business question it answers.
• Calculation in plain language, including what is included and excluded.
• Date basis, such as order date, invoice date or service date, and the period it reports.
• Currency and conversion rule, if more than one currency is involved.
• Source columns, named against the source register.
• Owner, approval date and version.

Disagreements surface here, on one page, instead of three weeks later as competing dashboards. If you already have two reports that disagree, our guide to reconciling dashboard numbers with finance walks one figure from source record to report cell and lists the usual causes of the gap.

How do you stage the data with row-level validation?

Load the snapshots into a staging area exactly as they are, adding the snapshot file name and the original row number to every row. Then apply validation rules row by row, so each row either passes or is written to a rejection table with the rule it failed.

• Required fields present, such as customer, date and amount.
• Types and formats: dates that are dates, amounts that are numbers, not text with stray spaces.
• Dates inside the reporting period.
• Codes valid against an agreed reference list, such as product, site or cost centre.
• Duplicates found by an agreed key, not by eyeballing names.
• Conflicts between sources, where the same key carries different values, resolved by a written rule about which source wins.

Do not fix errors by editing the snapshot. Either the owner corrects the source for the next cut-off, or the fix is a documented mapping rule in the staging logic. The detailed rule set for dirty spreadsheet data, including how to handle merged cells, free-text codes and rows that half pass, is covered in our guide to validating messy Excel imports. In a Microsoft 365 estate the staging and rules are usually Power Query, in a Power BI dataflow or semantic model, landing in Dataverse or a small SQL database where one is already available.

What should the reconciliation report show?

The reconciliation report is the document that proves no data was lost or invented between the snapshot and the figure. Produce it on every load, not once.

• Per source file: rows read, rows loaded and rows rejected, with read equal to loaded plus rejected.
• Control totals: the sum of the key amount in the snapshot, in the loaded rows and in the rejected rows, tying in the same way.
• Rejections grouped by rule, with the rows listed, an owner and a decision.
• Conflicts between sources and which rule chose the value used.
• Mapping rules applied, with how many rows each one changed.

A rejected row is not a failure. An unexplained one is.

Why publish one agreed figure end to end before adding more?

Because one figure taken all the way through exposes every weakness in the process while it is still cheap to fix. Choose the figure the auditor or client cares about most, then take it through the full chain.

1. Snapshot the sources that feed it.
2. Sign off its written definition.
3. Stage and validate the rows it needs.
4. Tie the reconciliation report to the snapshot totals.
5. Build the measure and a single report page with the cut-off date shown.
6. Trace the figure back to source rows yourself, as the auditor will.
7. Get the owner to sign the published figure.

When that figure holds, the next one reuses the register, the staging pattern and the reconciliation report, and goes much faster. Building ten report pages in parallel before one has been reconciled is how teams arrive at the deadline with ten numbers nobody will sign.

How do you show the lineage an auditor actually tests?

An auditor typically picks a figure and asks you to show where it came from, then selects some underlying items and asks for the evidence behind them. Lineage means you can answer that in minutes: the report cell, the measure and its signed definition, the staged rows behind it, each row's snapshot file and row number, the file hash and cut-off date, and any rejection or mapping rule that touched those rows. The auditor should also be able to re-perform the calculation from the snapshot and reach the same number.

If the figures span Dynamics 365 and a finance system rather than spreadsheets alone, the lineage columns, load logs and canonical layer are set out in our page on consolidating reporting across Dynamics 365 and a finance system. For the data model under the report, a proper date table and a star schema rather than one flat table, our Power BI for manufacturing guide shows the modelling decisions on Dataverse, and they apply well beyond manufacturing.

Can this be done inside the licences you already have?

In most Microsoft 365 organisations, yes. SharePoint provides the restricted snapshot library with version history, Power Query provides the staging and validation logic, and Power BI provides the model and the published figure, with Dataverse available where Power Apps or Dynamics 365 licences already include it. What is included in each plan changes, so check current Microsoft licensing documentation before assuming a capacity or feature is available to you. Our Power Platform consulting team builds this kind of deadline-driven reporting inside existing licences and leaves it documented for your staff to run.

What controls stop the reporting drifting back to hand-edited spreadsheets?

The first audit passes and then the old habits return unless the process is kept in place.

• Snapshots are immutable. Each period gets a new snapshot and hash; old ones are never edited.
• Definitions are under change control. A change needs the owner's approval, gets a new version number, and the report shows which version it uses.
• Validation runs on every load, and rejections have an owner and a closing date.
• No overrides typed into reports. Adjustments are recorded rows with a reason, an amount, the approver and a date, so they appear in the lineage like any other data.
• Every report page shows its cut-off date, so nobody reads last month's position as today's.
• Retired spreadsheets are marked read-only and kept in a register with their retirement date; the approach to retiring departmental copies is covered in our post on consolidating customer data into a single source of truth.

When is a proper system the next step?

The fast path makes existing data defensible. It does not fix how the data is captured. A proper system is the next step when the same validation rules reject the same kinds of rows every month because nothing stops bad data being typed in, when many people edit the spreadsheets daily rather than a few people periodically, when the figures depend on workflow state such as approvals that a spreadsheet cannot record, or when the reporting needs data from several systems on a regular schedule.

At that point the capture belongs in an application with validation at entry: a Dataverse model-driven app or Dynamics 365 where the Microsoft ecosystem fits, or a custom CRM built on React, Node.js, PostgreSQL or .NET for organisations that need full control without Microsoft licensing. The definitions, reference lists and reconciliation rules built for the audit carry straight into that system's design, so the deadline work is not thrown away.

Where does Solzet help?

Solzet stands up audit-ready reporting on Power Platform and Power BI for organisations facing a client or auditor deadline, working with the definitions and data you have, and integrating with finance systems rather than implementing them. Our senior consultants and full-stack developers bring 8+ years of Dynamics 365, Power Platform and custom CRM work, delivered remotely from Yerevan, Armenia, directly or white-label for Microsoft partners.