The challenge
Billing needed to be accurate against booked activity, but the bigger risk sat in cost accruals - the expected costs on bookings that suppliers had not yet invoiced. Without booking-level visibility, finance couldn't easily see which bookings had had their costs fully received and which still carried accrual exposure. That made it hard to control expected costs, supplier risk and margin. The tool had to:
- Ensure clients are billed accurately against booked activity.
- Create and maintain cost accruals for bookings where costs have not yet been invoiced.
- Provide a clear view of costs received vs costs still expected for each booking.
- Let client directors and finance look back at bookings and their accrual status to support review and reconciliation.
In other words, this was explicitly about controlling accrual risk and exposure - not just convenience.
My approach
1. Defined the risk and the tool's objectives
I started by clarifying the risk and what the tool had to achieve, framing it around accurate billing, maintained cost accruals, a clear costs received vs expected view, and the ability for both client directors and finance to review bookings and their accrual status.
2. Mapped data flows and responsibilities
I then mapped how information and accountability should flow:
- Client directors: enter bookings, assign them to cost centres, and later review what was booked.
- Consolidation: bookings from different client/project files needed to be brought together into a single, controlled view.
- Owner: needed to review, approve or reject bookings from that consolidated view.
- Finance: needed to turn approved bookings into client invoices and cost accruals, and monitor how much cost had been received vs how much was still expected per booking.
This clarified which sheets, views and controls the VBA macros needed to support to manage risk and accountability.
3. Identified key booking and invoice fields
I reviewed client plans and spoke to the team to define the data that mattered for both billing and risk monitoring - client, supplier, media type and cost centre; booking details such as description and dates; discount; and the fields needed for later analysis and for what appears on client invoices. This ensured the VBA logic was built around the information needed to manage accruals and margins, not just basic billing.
4. Clarified the approval process
I spoke with the owner about how he wanted to approve bookings - how he wanted to see them in a consolidated view, and how he would signal approval or rejection. This fed into the consolidated sheet design and the statuses the macro needed to set, reinforcing control over which bookings moved into invoicing and accruals.
Building the tool with AI
5. Designed the input template and used AI for dropdowns and validation
I designed a structured booking template in Excel for client directors, with columns for all required booking fields and dropdown lists for clients, suppliers, media types and cost centres to reduce input errors. I then used AI as a coding assistant to generate VBA that populates and maintains those dropdowns from reference tables, and to implement basic validation (for example, ensuring cost centre and key fields were always populated) - reducing the risk of incomplete or misclassified bookings.
6. Built the consolidated bookings sheet with AI-generated macros
I created an "All bookings" sheet that consolidates data from individual client/project files. Using AI, I described the structure of the source files and the consolidated sheet, then asked for VBA that would loop through each client booking file, extract the relevant data, and append it into the consolidated sheet in a standardised format - including cost centres and key booking details. I refined the code so the owner had a single view to approve or reject bookings, and finance had a controlled, complete dataset to drive invoices and accruals.
7. Streamlined billing and accrual imports with AI and formulas
I used Excel formulas to determine what should appear on client invoices and what should be recorded as cost accruals, based on the booking data and status. I then asked AI to help write a VBA macro that takes those outputs and saves two CSV files - one for billing, one for cost accruals - in the formats Xero requires. This let finance extract data directly from the tool and upload to Xero, reducing manual handling of high-risk accrual data.
8. Created an accrual-visibility report from Xero
I set up a custom report in Xero, downloadable into Excel, showing the remaining cost accrual balance on each booking billed to a client. By linking this back to booking information, finance can clearly see which bookings have had costs fully received and which still carry accrual exposure - a booking-level view of accrual risk rather than just a general-ledger total.
The results
- Strengthened accrual risk management: finance now has booking-level visibility of accrual balances, making it easier to spot where expected costs haven't yet arrived and to challenge or adjust accruals before they become issues.
- Reduced data-entry and classification risk: AI-generated VBA for dropdowns and consolidation reduces manual errors that could misstate accruals or misclassify costs.
- Controlled end-to-end process: bookings move from entry, through owner approval, into billing and accruals with clearer checkpoints - improving governance around revenue and cost recognition.
- Practical application of AI in finance control: demonstrated that AI tooling can build automation that directly improves financial visibility and control, not just efficiency.
This wasn't AI for efficiency's sake - it was AI used to build genuine financial control, giving finance a booking-level view of accrual risk it never had before.
Future enhancements
Next stages will further strengthen risk and control:
- Automating the sending of CSV files to Xero, reducing the risk of missed or incorrect uploads.
- Automatically notifying client directors when booking costs haven't been received three months after billing, prompting follow-up and potential cost-accrual review.
- Using AI to match supplier invoices against bookings, further reducing the risk of unmatched costs, mis-accruals and unspotted variances.