Gen AI and Financials Pt 1

Applying Gen AI to partially automate (basic) analysis of leasehold property financial statements, specifically expenses

Methodology

Using Gen AI, I extracted unstructured data from 4 PDFs into Excel within seconds/minutes. However, on closer inspection, I found transposition errors, resulting in the need to execute partial recs until subtotals matched original docs.

Interestingly, using Gemini 1.5 Pro Flash resulted in fewer errors than Chat GPT 4o. While both models struggled to deal with “-“ used in place of zeros, (a common character in financial statements), Gemini's error rate was about 4% compared to ChatGPT's 15%. Further testing warranted here.

In Excel, I transformed and normalized the various changing categories in order to allow like for like comparison over time. With Alteryx and similar tools, this could have been sped up 10x. Will attempt with KNIME in future.

Once transformed and normalized, I fed the results into PBI for manual analysis. Results could probably be hastened with CoPilot (untested), subject of future tests.

Total time spent ~ 4 hours, largely due to correcting Gen AI transposition errors, Excel work to normalize the expense categories, and manual data analysis / writing up findings / this blog.

Now I will compare results across 3 categories: human (i.e. doing mine first) then ChatGPT 4o then Gemini.

Key Findings - Human

Whilst budgeted numbers for 2024 and 2023 appear to be stable / slightly down, in reality when compared to most recent published actuals (2022 and 2021), expenses are actually up ~1m since 2021.

Focusing on this overall 1m increase, the biggest drivers are Electricity, Insurance, and Reserve Funds, as can be seen below:

Electricity's budget shot up significantly in 2023, perhaps in anticipation of further price rises in the disrupted energy market. It will be interesting to see if the actual 2023 spend matches the broader market overall, where prices fell almost 67% from its highest point in Q1 2023 of ~£4.4k, to about £1.6k in the latter 3 quarters of 2023.

Even if purchases are locked in 12 months in advance, one would expect the 70% reduction to be reflected in the 2024 budget - however the 2024 budget is only 54% down from 2023.

https://www.energyconnects.com/news/utilities/2023/august/window-opens-for-brits-to-lock-in-cheaper-energy-as-prices-drop/

Gas, Supplies, Management fees, and General Repairs have all also gone up significantly, and gas is a completely new line item, see more below.

The following new expense categories appeared from 2022 Actuals onward, and are budgeted to increase in 2023 and 2024. The following are noteworthy:

• Almost 250k of new expenses (compared to 2021) have been budgeted for 2024, and “Gas” is the largest at 122k. The only comparable equivalent I could find in the past accounts is “Heat consumption” of about 42k in 2020, but it was 0 for both 2021 and 2022.

• Procurement management ~40k and licenses ~13k are worth probing.

• Uniforms seem overpriced, shooting up from 4k to 25k budgeted.

• Business centre costs of 12k are new.

“Bank charges” paid 6k of historically. Assume this is overdraft related, not sure why leaseholders should pay for an admin error / poor cash management on management’s behalf.

0 has been budgeted for health club staff as this is now an outsourced service, presumably there will be a fee for this service not too different from what we paid historically. Not sure which line the outsourced health club staff falls under in our 2024 Budget, if not currently include, we should expect another ~160k hit to actual 2024 numbers.

Inflation: CPI was approx. 6% in 2022 so the 17% year on year increase is far in excess of inflation. CPI was approx. 11% in 2023, so the 2% yoy increase in 2023 budget is below CPI. CPI is tracking at ~2.4% as of Jun 2024, so overall, the 3 years appear to track inflation.

https://commonslibrary.parliament.uk/research-briefings/cbp-9428/

Conclusion: The 2024 budget tracks with 2022 actuals, but is significantly higher than 2021, and expense lines highlighted above are worth further challenge. Electricity actual spend for 2023 deserves additional scrutiny given that prices actually fell for most of 2023. It should be noted however that the 3 year period's increase does eventually track inflation, with expense increases far in excess of inflation only in 2022. It remains to be seen where 2023 and 2024 actuals fall.

Key Findings - ChatGPT 4o

Interestingly, aside from insurance ChatGPT seems to have skipped / missed the Electricity and Reserve Funds moves. Conclusion at the end is also wide of the mark. More prompt engineering will be needed here to get more useful results.

Here are the ten most important points to highlight from the data:

Significant Increase in Insurance Costs:

The budget for "Buildings, terrorism and engineering insurance" increased by 21.7% from 2023 to 2024 (from $653,981 to $796,260), indicating a substantial rise in insurance costs.

New Expense for Business Centre:

"Business Centre" received a new budget allocation of $12,000 in 2024, which was not present in 2023, suggesting the addition of a new cost category or a reactivation of a previously inactive category.

Elimination of Bank Charges:

"Bank charges" were completely eliminated from the budget in both 2023 and 2024, after having actual expenses in previous years ($1,288 in 2022).

Stable Accountancy Fees:

"Accountancy fees" remained constant across all years with a budget of $15,250, showing stability in these costs.

Volatile Business Rates:

The actual expenses for "Business rates" fluctuated significantly, with a 50% decrease from 2021 to 2022 ($3,028 to $1,506), while the budget remained unchanged at $3,000 for 2023 and 2024.

Overall Decrease in Bank Charges Over Time:

"Bank charges" showed a steep decline in actual expenses from $4,329 in 2020 to $1,288 in 2022, leading to their complete elimination in subsequent budgets.

Insurance Cost Variability:

The actual expenses for "Buildings, terrorism and engineering insurance" showed high variability, with a notable 15% increase from 2021 to 2022 ($540,519 to $635,209).

Predictable Accounting Expenses:

The consistency in "Accountancy fees" suggests a well-managed and predictable accounting expense without significant changes over the years.

Strategic Cost Reductions:

Categories like "Bank charges" reflect strategic efforts to reduce or eliminate certain costs, showcasing cost-saving measures.

Steady Budget for Business Rates Despite Historical Fluctuations:

Despite significant fluctuations in actual expenses for "Business rates", the budget allocation remained steady at $3,000 from 2022 to 2024, indicating a cautious budgeting approach.

Key Findings - Gemini Flash

Same prompt, same data, and actually, this is pretty good, all big ticket items are here. I particularly like the conclusion at the bottom about challenges in budgeting / managing expenses, something which I did not explicitly highlight, but agree with.

Here are the top 10 things to highlight in the data:

Building, terrorism and engineering insurance: This category shows a significant increase in the 2024 budget, with a budget of 142,279.

Chilling income: This category shows a significant decrease in the 2024 budget, with a budget of -79,434.

Contribution to reserve fund: This category shows a significant decrease in the 2024 budget, with a budget of -1,314,119.

Electricity: This category shows a significant increase in the 2024 budget, with a budget of 9,042.

General Repairs & Maintenance: This category shows a significant decrease in the 2024 budget, with a budget of -70,439.

Health Club Staff: This category shows a significant decrease in the 2024 budget, with a budget of -163,963.

Health Club Supplies: This category shows a significant increase in the 2024 budget, with a budget of 78,204.

Landscaping & Floral: This category shows a significant decrease in the 2024 budget, with a budget of -17,681.

Lift Maintenance, Repairs & Telephone: This category shows a significant increase in the 2024 budget, with a budget of 111,137.

Parking Income: This category shows a significant decrease in the 2024 budget, with a budget of -48,876.

In addition to the top 10, here are some other notable items:

Several categories show significant variations in budget year-over-year.
Some categories have a substantial difference between the budget and actuals, suggesting that there are challenges in budgeting or managing expenses.
Overall, there is a significant trend towards decreased spending in 2024.

Conclusion: Gemini wins this round. However, further refinement to prompts to incorporate chain of thought reasoning will likely lead to better and more insightful outcomes. This will be for part 2.