Executive Summary

This engagement focused on improving financial tracking and reporting workflows in a small professional services environment involved in complex case-related accounting requirements.

The existing process relied on fragmented manual tracking of accounts payable and receivable across paper notes and disconnected documents, resulting in high effort for reconciliation and periodic reporting.

An initial solution was developed as a lightweight cloud-based application with structured data storage and dashboard-based reporting. However, adoption constraints related to operational risk perception, maintenance concerns, and support overhead limited its suitability for the client context.

The solution was subsequently redesigned as a simplified spreadsheet-based system with structured inputs, centralised storage, and standardised reporting logic. This alternative preserved core improvements in data structuring and retrieval while aligning with the client’s operational comfort level and support constraints.

The final implementation significantly reduced time required for monthly financial reconciliation and improved auditability of case-level financial flows.

Context and Problem Framing

I was initially engaged as a litigation consultant supporting a complex tax dispute. My role focused on analysing financial records, business documentation, and supporting evidence to help prepare clear, defensible materials for legal proceedings.

The engagement involved reviewing a substantial volume of financial and operational records over several months. In the process, I developed a detailed understanding of the firm’s internal workflows, particularly around billing, case accounting, and financial reporting.

One process stood out immediately.

The firm’s senior partner personally managed a significant portion of the accounts receivable and accounts payable reconciliation process. Although the firm maintained a digital ledger, the underlying information was distributed across invoices, physical files, handwritten notes, and supporting documents. Reconciling a client’s financial position often required tracing information across multiple sources before balances could be verified with confidence.

What struck me was that the process was simultaneously effective and fragile. Effective because it had been refined over many years by someone who understood every nuance of the firm’s operations. Fragile because much of that understanding existed in experience rather than in the structure of the underlying system.

The firm’s work introduced additional complexity. Clients frequently had multiple active cases, invoices were often associated with specific cases, and financial records needed to remain traceable for both operational and professional reasons. As a result, routine reporting and reconciliation required significant manual effort and careful cross-referencing.

The primary challenge was not computational complexity, but the absence of a structured representation of financial relationships. Reporting often required reconstructing the state of the business from distributed records rather than querying a reliable source of truth.

From an operational perspective, this was an opportunity. Time spent reconciling records was time unavailable for client work, business development, or higher-value activities. The objective was therefore not to change how the firm practised law, but to reduce the administrative burden required to support it.

Baseline state

The firm’s accounting process had evolved organically over many years and was closely aligned with its operating model. For a practice of its size, the approach was functional and produced reliable results. However, maintaining that reliability required substantial manual effort and institutional knowledge.

Financial information was distributed across multiple sources, including invoices, payment records, physical files, handwritten notes, and supporting documentation. Client cases frequently contained multiple invoices, partial payments, retainers, and case-specific allocations, requiring regular reconciliation to establish an accurate financial position.

The senior partner conducted much of this reconciliation manually. Routine reporting activities required aggregating information from disparate records, verifying balances, and updating running totals across active cases. While each individual task was straightforward, the cumulative process was time-consuming and demanded a high degree of attention to detail.

The primary operational challenge was not the volume of transactions, but the fragmentation of information. Financial data existed, but not in a form that could be readily queried, verified, or reported upon.

As a result, various accounting efforts often required reconstructing financial state from distributed records rather than retrieving it from a structured source of agregated and self-consistent data. This increased both the time required for reconciliation and the cognitive effort associated with routine financial administration.

Diagnostic

Following initial discussions, I conducted a workflow reviews and stakeholder interview to better understand how financial information was created, maintained, and ultimately used throughout the firm’s operations.

The objective was not simply to document the existing process, but to identify the information requirements underlying it. Particular attention was given to how records were reconciled, how year-end reporting was prepared, and how financial information was traced across clients, cases, invoices, and payments.

Several core requirements emerged consistently:

• Traceability at the case level
• Invoice-level transaction visibility
• Client-level aggregation and reporting
• Support for financial verification and auditability
• Simple retrieval of current balances and historical activity

Notably, there was relatively little interest in advanced reporting capabilities or sophisticated visualisations. Their primary concern was reliability. If a system could reduce manual effort while preserving confidence in the underlying data, it would be considered successful.

Based on these discussions, the initial assessment suggested that the problem was largely structural. Financial information already existed and was being maintained diligently; however, relationships between records were not formally represented. As a result, routine reporting required repeated reconstruction of financial state from multiple sources.

Several important constraints, however, were not fully apparent during the initial discovery phase.

While functional requirements were relatively clear, non-functional requirements proved more significant than anticipated. Stakeholders demonstrated a low tolerance for operational risk, limited appetite for ongoing technical maintenance, and a strong preference for tools they could independently understand and manage.

This distinction ultimately became central to the project.

Requirements were constrained as much by adoption risk as by functional need.

Solution Design

Initial Concept

Architecture:

Based on the initial diagnostic, the most direct solution appeared to be a lightweight custom application designed to centralise financial information and formalise relationships between clients, cases, invoices, and payments.

The proposed architecture consisted of a cloud-hosted application with authenticated user access and a relational database supporting structured financial records. Rather than relying on manually maintained links between documents, financial relationships would be represented explicitly within the underlying data model.

The system was designed around four core entities:

• Clients
• Cases
• Invoices
• Payments

This structure allowed financial activity to be tracked at multiple levels simultaneously. Individual transactions could be traced to specific invoices, invoices could be associated with particular cases, and cases could be aggregated at the client level for reporting and reconciliation.

The objective was not automation for its own sake. Data entry remained a necessary part of the process. Instead, the design focused on improving the organisation, retrieval, and verification of information once entered.

By representing financial relationships directly within a structured dataset, routine reconciliation could shift from manual reconstruction of records toward query-based retrieval of current financial state.

Functional design

The initial design centered on creating a single source and repository for the firms billing information while preserving the firm’s existing accounting workflow.

Rather than replacing underlying business processes, the application would provide a structured environment for recording and retrieving information related to clients, cases, invoices, and payments. The primary objective was to reduce the effort required to reconcile records and prepare periodic reporting.

Planned functionality included:

• Case-level balance tracking up to client-level
• Accounts receivable and accounts payable monitoring
• Invoice and payment history views
• Running balance calculations
• Quarterly and year-end reporting summaries

Effort initially focused on validating the underlying data model and reporting concepts rather than delivering a production-ready application. Particular attention was given to how financial information would be structured, linked, and retrieved once entered into the system.

The proposed design demonstrated that much of the firm’s administrative effort stemmed not from the accounting itself, but from the need to repeatedly reconstruct relationships between records. A structured data model offered a straightforward path toward improved traceability, verification, and reporting efficiency.

At this stage, the working assumption was that functional capability would be the primary determinant of success. Subsequent discussions revealed that operational considerations would prove equally important.

Adoption constraint discovery

There was a steering metting where the proposed solution was reviewed with the client, discussions began to shift away from functionality and toward operational ownership.

From a purely functional perspective, the concept aligned well with the requirements identified during discovery. Financial relationships could be represented more clearly, reporting could be simplified, and reconciliation effort would be reduced. However, important a different set of questions emerged that had not been well planned for previously:

How would data be backed up? What would happen if the application became unavailable? Who would maintain the system if requirements changed? How dependent would the firm become on external support?

These concerns were not objections to the solution itself. Rather, they reflected a broader preference for operational transparency and self-sufficiency.

The firm was familiar with examples of larger organisations adopting specialised software platforms only to become dependent on ongoing vendor relationships, maintenance agreements, and periodic upgrades. While such arrangements can be entirely appropriate in larger environments, they were not viewed favourably within the context of a small professional services practice.

The discussion revealed an important requirement that had not been fully captured during the initial diagnostic: users needed to understand and trust the mechanism by which the solution operated. Familiarity was not simply a preference; it was part of the firm’s risk management approach. I had planned touchpoints on back up solutions and was confident on the nature of the solution that it would not “break” easily, but I realised that my best answers did not dissuade the “what-ifs” that lay overhead.

This shifted the framing of the problem considerably.

The limiting factor was no longer technical feasibility. The proposed application was capable of addressing the identified workflow challenges. The limiting factor was operational trust.

Any successful solution would need to improve financial administration while remaining understandable, maintainable, and controllable by the people responsible for using it.

In retrospect, the engagement highlighted a common implementation risk: requirements are often constrained as much by adoption considerations as by functional need, if not more.

Pivot Solution - Spreadsheet-based system

Design Principle shift

Following the adoption review, the design approach was reframed around a more fundamental constraint: the solution needed to be not only functionally correct, but also operationally sustainable within the firm’s existing capabilities and risk tolerance.

The initial architecture had optimised for system design quality—structured data, formal relationships, and extensibility. However, it assumed that technical robustness would be the primary driver of adoption. In practice, the dominant constraint was usability in the context of day-to-day ownership, maintenance, and perceived operational risk.

This led to a shift in design principles:

From system-centric architecture To user-operable structured tooling

The objective became to preserve the benefits of structured data—traceability, consistency, and retrieval—while embedding them within a tool the firm could fully understand, control, and maintain without external dependency. Despite my enthusiasm and confidence in delivering a working solution, what happened when some dependency broke in 3 years at 2 A.M. on a Sunday? Could I realistically guarantee being available to quickly get things back up fully functional?

Rather than increasing system sophistication, the focus moved toward reducing operational uncertainty. The key question was no longer “what is the most scalable architecture?” but “what is the simplest structure with the least friction in adoption that still preserves financial integrity and reporting clarity?”

This reframing opened the door to a lower-complexity solution that better aligned with the firm’s size, use patterns, and tolerance for risk.

Spreadsheet architecture

The revised solution was implemented as a structured spreadsheet-based system designed to retain the core benefits of the original concept while operating within a familiar and trusted environment.

At its core, the solution functioned as a lightweight relational framework embedded within a single workbook (for a given year). Information was organised into distinct but interconnected worksheets representing the firm’s primary financial entities:

• Clients
• Cases
• Invoices
• Payments
• Reporting Dashboard

Consistent identifiers were used to maintain relationships between records, enabling financial activity to be traced from individual payments through invoices and cases to the client level. Standardised input formats and controlled data-entry fields reduced variability while preserving the flexibility required for day-to-day operations.

The design objective was to structure information at the point of entry so that reporting and reconciliation could occur with minimal manual reconstruction. Once data was recorded, balances, summaries, and financial views could be generated automatically through predefined formulas, aggregations, and reporting logic.

The resulting system provided visibility at multiple levels of analysis. Users could review financial activity by client, by case, by invoice, or across reporting periods while maintaining a consistent underlying data structure.

Although implemented using a familiar spreadsheet environment, the design philosophy remained unchanged from the original concept: financial relationships should be represented explicitly rather than inferred through manual reconciliation.

Data Integrity Mechanisms

Importantly, the newfound guiding simplicity of the implementation should not come at the expense of reliablity.

To support data quality, the workbook incorporated a series of controls intended to reduce entry errors and preserve consistency across records. Standardised identifiers, controlled input fields, validation rules, and predefined calculation logic were used to minimise opportunities for accidental inconsistencies.

Relationships between clients, cases, invoices, and payments were maintained through structured references rather than ad hoc linking. This allowed financial information to remain connected as records accumulated over time and enabled reporting views to update automatically as new transactions were entered.

Automated reconciliation checks and calculated summaries provided an additional layer of validation. Rather than relying solely on manual review, users could identify discrepancies through predefined control mechanisms embedded within the workbook.

An important design consideration was balancing sophistication with maintainability. Wherever possible, logic was kept visible and understandable to end users. This reduced dependency on specialised technical knowledge while allowing stakeholders to verify calculations and trace results independently.

The resulting approach prioritised transparency over complexity. While less technically sophisticated than a dedicated software platform, the workbook provided a structured and auditable environment for managing financial information at the scale required by the firm. The simplicity and, more importantly, the familiarity of the tool was de facto a reliability feature, not a limitation in this context.

Storage and governance

The final design deliberately leveraged existing enterprise tooling rather than introducing new infrastructure requirements.

Instead of relying on a custom authentication framework, backup strategy, or application hosting environment, the solution was integrated into the firm’s existing cloud storage and identity management ecosystem. Access control, authentication, file recovery, and version management were therefore governed through established organisational processes rather than bespoke technical components.

This approach significantly reduced operational overhead. Stakeholders did not need to maintain application infrastructure, manage software updates, or depend on external technical support for routine administration. Responsibility for access management and document retention remained aligned with familiar workflows and existing governance practices.

To support long-term maintainability, the workbook was designed around an annual operating cycle. A standardised template could be used to create a new master workbook each fiscal year, providing a natural mechanism for archival, retention, and ongoing record management without introducing unnecessary complexity.

Importantly, the governance model was readily understandable by end users. Questions regarding access, backups, recovery, and ownership could be answered using processes the firm already trusted and operated successfully.

What had initially appeared to be a technology problem was ultimately resolved through a combination of structured information design and operational governance.

Implementation & Transition

Given the nature of the solution built on top of existing software in use at the firm, implementation focused on adoption rather than a technical deployment

Before transition, the proposed solution was reviewed with stakeholders through a validation session covering workflow design, reporting outputs, anticipated use cases, and day-to-day operating procedures. Feedback from these discussions was incorporated into the final structure to ensure alignment with existing practices and user expectations.

To support long-term sustainability, implementation materials were prepared alongside the solution itself. These included user guidance, process documentation, permission management instructions, and reference materials covering common operational tasks and year-end procedures.

Given the firm’s size and transaction volume, a formal phased rollout was unnecessary. Instead, transition occurred through a structured handover supported by training sessions and practical walkthroughs of the new workflow. The training sessions proved relatively straightforward because the underlying workflow remained familiar; the solution changed how and where information was organised rather than how the firm’s accounting process operated. After all, succesful change often minimises behavioural disruption.

Historical migration was approached pragmatically. Rather than attempting to reconstruct and import legacy records, the new system was seeded from a recent financial baseline where outstanding balances and active cases could be verified with confidence. Current records were then transferred into the structured format, providing a clean starting point for ongoing use.

By the implementation stage, the focus of the engagement had shifted considerably. The challenge was no longer designing a better system, but ensuring that the solution could be adopted, understood, and maintained with confidence by the people responsible for using it.

Results

The project did not include formal post-implementation tracking, but feedback from the firm indicated a meaningful reduction in the effort required to maintain and reconcile financial records.

Several operational improvements were observed following implementation:

• Faster retrieval of case-level financial information
• Reduced effort required for monthly reconciliation activities
• Improved visibility into invoices, payments, outstanding balances, and in-trust accounts
• Greater consistency in financial reporting across clients and cases
• Reduced reliance on manual aggregation from multiple record sources

Perhaps more importantly, financial information became substantially easier to verify quickly. Relationships between clients, cases, invoices, and payments were explicitly represented within the system, allowing users to trace balances directly with a few clicks to sort and slice data rather than reconstructing results from distributed records.

Basically, the solution reduced reconciliation effort by enabling direct retrieval of structured financial relationships rather than manual reconstruction of financial state.

This improvement extended beyond reporting efficiency. Questions that previously required consulting multiple files, notes, and ledgers could now be answered from a single source of truth. The time required to understand a client’s financial position, verify invoice history, or review case-level activity was reduced considerably.

Stakeholder feedback was particularly positive regarding usability. Administrative tasks that had previously been accepted as a routine burden became easier to perform, easier to verify, and easier to delegate. The reduction in cognitive load was especially valuable given that much of the reconciliation process had historically relied on the experience and attention of senior personnel.

One comment from the senior partner was especially memorable. Reflecting on the time no longer spent manually reconciling records, he remarked—only partly joking—that he had likely recovered “at least 10–15%” more time for billable work. While this observation was never formally measured, it captured the practical value of the engagement more effectively than any dashboard metric could.

The solution was not transformative in scale, nor was it intended to be. However, by introducing structure into a recurring administrative process, it reduced friction, improved visibility, and returned time and attention to higher-value activities. For a small professional services firm, those incremental improvements were meaningful.

Interpretation

Looking back, my initial reaction to the firm’s accounting process was a mixture of admiration and concern.

Admiration because it worked. The senior partner had developed a disciplined process that successfully managed a surprisingly complex set of financial relationships across clients, matters, invoices, payments, and trust balances. Concern because much of that capability depended on experience, familiarity, and sustained manual effort. The system was effective, but it was also highly dependent on the person operating it.

At the time, I was still a student and relatively early in my professional development. This project never felt like a business opportunity or a chance to sell technology. The firm had trusted me with meaningful work on a demanding litigation matter and had invested considerable time helping me understand both the case and their operating environment. Identifying a way to reduce an administrative burden felt like a practical way to return some of that value.

The most important lesson from the engagement was that successful solutions are not defined solely by their functional capabilities.

My initial instinct was to pursue a custom application because, from a technical perspective, it appeared to be the strongest solution. The architecture was cleaner, the data model was more extensible, and the long-term possibilities were greater. What I underestimated was the importance of operational trust.

For the client, the primary concern was not whether a system could perform a task. It was whether the system could be understood, maintained, recovered, and relied upon without introducing new dependencies or uncertainties. Familiarity was not resistance to change; it was a rational response to operational risk.

The spreadsheet solution succeeded not because it was technically superior, but because it aligned more closely with how the organisation understood ownership, reliability, and control.

This lesson has resurfaced repeatedly in later engineering and consulting engagements. It is easy to optimise for sophistication, scalability, or architectural elegance. In practice, however, the most effective solutions are often those that balance capability with adoption. A technically perfect system that is never fully trusted creates less value than a simpler system that becomes part of daily operations.

The optimal solution is determined not only by functional requirements, but by an organisation’s tolerance for operational complexity. In many cases, simplicity is not a compromise. It is a design choice.

Appendix and Notes

This is not a reflection of the true product (which was not in Enlgish), but similar enough to illustrate for this discussion

A - Data model (custom system)

This schema represents a normalized relational database structure (e.g., PostgreSQL) designed to minimise redundancy and ensure referential integrity.

Table Definitions:

• clients: The master list of entities the firm represents.
• cases: Linked to a client. Contains metadata for flexible tracking (e.g., case type: “Litigation,” “Family Law”).
• invoices: Linked to a specific case. Tracks the financial obligation.
• payments: Linked to an invoice. This structure simplifies allocation; a payment is applied directly to an invoice ID to clear that specific debt.

There were provisions for a catch-all base case client file and base case pseudoinvoice for advances deposited in-trust not yet earmarked for a specific true invoice.

B - Application architecture (prototype)

The conceptual, lightweight web app stack

1. Frontend

Tech: React.js or standard, simple HTML/JS dashboard. Views: Dashboard: Aging reports (0-30, 31-60, 60+ days). Case View: List of all invoices for a specific case with running balances. Client View: List of all invoices for a specific client with running balances. Payment Entry: Simple form to select an Invoice ID and enter an amount.

2. Backend Logic

API Layer: RESTful endpoints (e.g., GET /api/cases/{id}/invoices, POST /api/payments). Business Rules: Auto-calculates “Outstanding Balance” (Invoice.amount - SUM(Payments.amount)).

3. Authentication approach

Method: Token-based authentication (JWT). Roles: Partner: Read/Write access (can write off invoices). Accountant: Read/Write access (can post payments). Staff: Read-only access.

4. Database (Postgres schema)

Uses the schema defined in Appendix A. Indexes applied to client_id (in Cases) and case_id (in Invoices) for fast lookup.

C - Spreadsheet Architecture

Prototyping revealed constraints that shifted the project away from a custom web app. A low overhead approach, with a more familiar environment was required. Sheet Structure:

Settings: Static lists (e.g., Invoice Status codes, Payment Methods). Cases (The Source of Truth): List of all active legal cases. Invoices: Transaction log of bills sent. Payments: Transaction log of money received. Dashboard: High-level reporting and pivot tables. Key Fields:

Case Sheet: Case_ID (Unique): MAT-001 Client_Name Practice_Area Invoices Sheet: Invoice_ID (Unique): INV-1001 Case_ID (Foreign Key) Date Amount Payments Sheet: Payment_ID (Unique): PAY-5001 Invoice_ID (Foreign Key) - Crucial for direct allocation. Amount ID System Design:

Format: [Type]-[YYYY][NNN] Example: An invoice created in 2023 being the 5th one would be INV-2023005. Logic: This allows for chronological sorting and prevents duplicate IDs if data is merged later. Cross-sheet References:

Data Validation: The case_ID column in the Invoices sheet uses a Data Validation list referring to cases!A:A. This prevents typos (e.g., typing “MAT-01” instead of “MAT-001”). VLOOKUP/XLOOKUP: Used to pull Client_Name into the Invoices sheet based on the selected case_ID.

D - Formula logic

Running totals against specific invoice on the Invoices sheet: =SUMIFS(Payments[Amount], Payments[Invoice_ID], [@Invoice_ID]) adds all the rows in the payments tab where the Invoice ID matches the current row

There was also case-level aggregation to calculate the outstanding value for a specific Case on the dashboard: =SUMIFS(Invoices[Amount], Invoices[case_ID], “MAT-001”) - SUMIFS(Payments[Amount], Payments[Invoice_ID], Invoices[Invoice_ID]) very simply: for a given case: sum of invoices - sum of payments.

Visual flag for fully paid invoice, even if status was not updated: =IF(([@Amount] - [@Total_Paid]) <= 0, “Check Status”, “OK”)

E - Limitations

The designed tool was designed for simplicity, functionality and ease of adoption. This approach has limitations which were discussed with the client.

1. Spreadsheet scalability limits: Excel/Sheets will slow down significantly after ~100,000 transaction rows, whereas a SQL database handles millions effortlessly.
2. Manual entry still required: There is no automatic bank feed integration. Users must manually enter payment data from bank statements into the Payments sheet.
3. Limited automation vs full system: Notifications (e.g., “Invoice is 30 days overdue”) must be checked visually; the system cannot automatically email the client.
4. Depends on disciplined usage: If a user deletes a row or types an ID incorrectly (INV-001 vs INV-O01), the relational links can break and reports will fail.

Client understood and is comfortable navigating these details. At the scale of operation, they were confident in managing overdue invoices and still remained on top of things.

The final design prioritises operational robustness over architectural extensibility.