How Rittman Analytics Automates our Profit & Loss Reporting and Commentary using VertexAI Gemini 1.5-FLASH and Google BigQuery
A Profit & Loss Report (or “Income Statement” in some countries) is a financial report, typically produced monthly, quarterly or annually, that sets out the income, direct and overheads costs and the final net profit for a business.
If, like us, you use Xero to run your company accounts then your typical default Profit & Loss report looks like the one below, based on the Demo Company data available in Xero.
It’s great to see your sales, gross profit, operating profit and what’s left after taxation on one page but as I said in our previous blog on Automating Insights and Dashboard Report-Level Narratives, producing the report is just the first stage in a data-driven organization; you have to then understand what the report is telling you and then work-out what actions need to be taken with this new information.
In the case of a Profit & Loss report such as this one, I’d want to understand the answer to questions such as:
How does this month’s revenue, gross margin (the percentage of revenue left after direct costs are deducted) and net margin (the same but after overheads are deducted) compare to last month? And for quarter-to-date how do these compare against the last quarter-to-date? Year-to-date? And so on
For account categories such as Administrative Costs (aka “overheads”) which account groups are trending-up over the past few months, something I’d want to keep a handle on if elements of our back-office costs keep rising month-on-month
Similarly, if spend on a category or account group has risen suddenly this month, or if sales have jumped unexpectedly, what individual transactions were the root-cause of these changes?
If I have my budget figures also stored in Xero, how does this months, quarter’s and year-to-date numbers compare to plan and are we on-track to hit our overall targets?
And then, once I have this information, what does then mean for our management plans for the coming months? Do we need to look at our costs and bring them back in-line with our original plan for the year? Are sales an issue and do we need to work on building our pipeline?
In this blog we’ll look at how generative AI, in the form of Vertex AI’s Gemini 1.5-FLASH LLM, can be used to automate the analysis of this multi-level financial dataset to produce insightful and actionable report commentary such as the examples shown below.
In our previous article on using gen AI to automate the production of report narratives we used a relative simple example where we asked an LLM to analyze a table of monthly KPI values and asked it to comment on this month’s performance compared to last months’ and quarter-to-date, and gave it some basic guidance on how profit was made-up of revenue minus costs and so forth.
In the case of analyzing a profit and loss report though you need to be more structured in how you perform your calculations and approach the process of analyzing the result of those calculations and investigating root cause.
Mirroring the thought process outlined earlier where we start at account category level and calculate all the period-on-period variances, then go down to account group level and finally identify the individual transactions that led to those variances and anomalies, we created a data pipeline that starts with the dbt transformations that Fivetran can run when replicating your Xero data into your data warehouse and created a BigQuery Python notebook that performed those same analysis steps, which we’ve made available to download along with a readme file from our Github account.
Looking at the output from this stage of processing in that BigQuery notebook shows you enhanced P&L report dataset that we’ll pass to Vertex AI the following step to get our report commentary.
The notebook then performs all of the trend analysis, root-cause identification and other complex financial calculations that need to be done in a specific, controlled and repeatable way using Python Pandas dataframe calculations, like this example that identifies significant transactions contributing to a variance in month-on-month account-level spend:
The way that we then structured the prompt that was then sent to the Gemini 1.5-FLASH model was key to the quality of the report analysis and commentary:
Key aspects of the prompt include:
Context Provision: The prompt includes processed financial data, account group details, and cost change analysis.
Specific Instructions: It provides clear guidelines on what to focus on in the analysis, such as revenue, cost of delivery, and significant transactions.
Output Structuring: The prompt specifies the desired HTML structure for the output, ensuring a consistent and well-organized report.
Analytical Guidance: It provides guidance on how to approach the analysis, including comparing current performance to previous periods and identifying significant changes.
Customized Formatting: Instructions for using specific styles and tags in the output are included.
The notebook example also stores the calculation table output and the commentary in a BigQuery table so that we can then include it in our Looker financial dashboard to run alongside our actual monthly P&L report, using a Looker custom visualization we’ve developed that allows us to display the formatted HTML that our LLM produces.
In our internal version of this notebook we’ve also extended the financial analysis performed by the notebook and LLM to include highlighting new recurring payments, forecasting our cashflow for the coming months and other key parts of our monthly financial reporting process.
For more examples of what we’re currently doing with GenAI within Rittman Analytics check out our other blogs below:
or take a look at our Generative AI services page and our fixed-price, Jumpstart GenAI and Looker consulting package that can deliver these types of AI-powered dashboards for your organization, now!
INTERESTED? FIND OUT MORE!
Rittman Analytics is a boutique data analytics consultancy that helps ambitious, digital-native businesses scale-up their approach to data, analytics and generative AI.
We’re authorised delivery partners for Google Cloud along with Oracle, Segment, Cube, Dagster, Preset, dbt Labs and Fivetran and are experts at helping you design an analytics solution that’s right for your organisation’s needs, use-cases and budget and working with you and your data team to successfully implement it.
If you’re looking for some help and assistance with your AI initiative or would just like to talk shop and share ideas and thoughts on what’s going on in your organisation and the wider data analytics world, contact us now to organise a 100%-free, no-obligation call — we’d love to hear from you!