data.day
operations / excel / workflow / management

The Month-End Panic: A Case of “Different Versions of the Truth”

Three files, five totals, and one stressed owner. We diagnose the 'Version Control' chaos that plagues Excel-based businesses and build a single source of truth.

Sofia Vector Sofia Vector

The Circle of Doom

Come, look at this desktop folder. It is a graveyard of good intentions.

  • Q3_Report.xlsx
  • Q3_Report_EDIT.xlsx
  • Q3_Report_Final.xlsx
  • Q3_Report_Final_JON_COMMENTS.xlsx
  • Q3_Report_Final_Final_v2.xlsx

This is the Circle of Doom.

Every time someone opens a file and hits “Save As,” they create a parallel universe. In Jon’s universe, the refund for Client X hasn’t happened yet. In the Final_v2 universe, the tax rate was updated.

When we try to merge these universes at the end of the month, the math breaks. We spend three hours tracing the error, only to find that someone hard-coded a number in cell D5 because “it looked wrong.”

The Lie: We convince ourselves that “saving a copy” is safe. It feels like a backup. But it is actually a fork in the road. Every copy dilutes the truth.

The Truth: There can be only one model. The data must flow in one direction: from the Source to the Report. Never backwards. Never sideways.

The One-Way Street

To fix this, we must become strict about our architecture. We need to build a “Data Waterfall.”

  1. The Source (ReadOnly): This is the raw export from your bank or CRM. It lands in a folder called 01_INPUT. No human is allowed to open this file and type in it. It is sacred ground.
  2. The Model (The Brain): This is your working Excel file. It uses Power Query to read the Input. It performs the calculations. It applies the logic.
  3. The Report (The Snapshot): When the month is done, we print the PDF or export the values. This is 03_OUTPUT.
SOURCE SYSTEM
Bank / CRM
     |
     v
01_INPUT
Raw CSV export, kept read-only
     |
     v
MODEL
Power Query + calculation logic
     |
     v
03_OUTPUT
PDF or values snapshot

Do not overwrite the input from the report.
Correct source errors in the source system, then refresh.

Each report should be traceable to its input export and model. A corrected source leads to a fresh export and recalculation; an edited PDF does not repair the upstream data.

Killing the Hard-Code

The greatest enemy of truth is the manual overwrite.

I see it all the time. A formula returns an error, so the user types “0” over it just to print the report. That “0” is a lie. It is hiding a broken process.

When we adopt the One-Way Street, we cannot cheat. If the model says “Error,” we must go back to the Source and fix the data entry there. We fix the root, not the fruit.

It feels slower at first. It is annoying to open the CRM to fix a typo instead of just fixing it in Excel. But the next time you pull the report? It is clean. And the time after that? Clean.

We stop panicking at month-end because we trust the machine we built. We stop asking “Which version is this?” and start asking “What does the data say?”

FAQs

Why do we have so many versions?

Because we fear deleting things. We 'Save As' to preserve history, but we end up fragmenting reality.

How do we stop the 'Final_v3' madness?

Separate the Data from the Calculation. One file holds the logic; the data is imported.

Does this require a database?

Ideally, yes. But you can simulate it in Excel by having a 'ReadOnly' folder for the source data.