FP&A case study
AI-Assisted Profitability & Utilisation Model
From fragmented operational data to a repeatable management view.
01 · Context
The business question came before the workbook.
Management needed a consistent way to understand project profitability, contractor profitability and employee utilisation without analysing the underlying operational and financial records separately each time.
The objective was to create a repeatable monthly view that connected those questions to the data already available across operational and finance systems.
02 · Model architecture
Connect the operating detail to a decision-ready output.
The model brings together timesheet, invoicing and cost information with business mappings and assumptions, then aggregates the resulting calculations into project-, contractor- and employee-level views.
Inputs
Model
Management view
03 · Modelling logic
Profitability and utilisation answer different parts of the same management question.
Invoiced revenue
−Direct people cost
−Allocated fixed cost
= project / staff profitabilityRecorded working hours
÷Available working capacity
= utilisation view04 · AI contribution
AI supported implementation, not financial judgement.
I defined the business questions, described the available datasets and what each field represented, determined the analytical structure, and built the pivot tables, slicers and management views.
I used ChatGPT as a technical assistant to help translate parts of the calculation logic into Excel formulas. This extended the technical complexity I could implement quickly while keeping ownership of what the model should measure and how the results should be interpreted.
- Business question
- Meaning of the data
- Metric and model design
- Pivots and slicers
- Interpretation
- Validation
- Formula construction
- Formula iteration
- Technical troubleshooting
05 · Validation
Selected outputs are traced back to source data every month.
AI-assisted formulas are not accepted at face value. During the monthly refresh, I filter selected contractors and projects in the management view, then filter the underlying data to the same records and check that the resulting profitability and utilisation calculations reconcile.
Repeating these spot checks each month gives the model a practical control loop and helps surface formula, mapping or source-data issues before management relies on the output.
06 · Management output
A compact view of revenue, cost, profitability and utilisation.
The public visual below is reconstructed with synthetic values solely to demonstrate the analytical structure.
| Project | Consultant | Revenue | Direct cost | Allocated cost | Profitability | Utilisation |
|---|---|---|---|---|---|---|
| Project Alpha | Consultant 01 | £28.4k | £11.2k | £4.1k | £13.1k | 82% |
| Project Beta | Consultant 04 | £21.7k | £9.8k | £3.7k | £8.2k | 74% |
| Project Gamma | Consultant 02 | £18.9k | £7.4k | £3.5k | £8.0k | 91% |
Illustrative values reconstructed for confidentiality. No company or client financial data is disclosed.
07 · Outcome
A repeatable framework for monthly profitability and utilisation review.
The model gave the client a clearer framework for reviewing profitability and utilisation across projects, contractors and employees, and was well received by the client.
The broader lesson is simple: AI is most useful in finance when the business question, data structure, assumptions and validation process are explicit.