Recent Projects

Excel Model for PE Fund Investment and Returns planning

This solution was for a PE fund looking to raise funds and needing to show how the fund could and would perform under a range of assumptions. It included full IRR and cashflow forecasting as well as a summary of metrics.

Excel Application For Daily Market P&L

This solution involved building a custom Excel Application to pull data from Advent Geneva, aggregate it and build a Daily and MTD P&L by Market & by Currency as well as creating a comparison report between the current MTD and the aggregated previous day’s MTD P&L and Daily P&L to spot modifications to previous day trades. This process saved on average 60 mins per day for the user and reduced errors to zero.

Trade Reconciliation System

This involved building an automated system for reconciling daily trades by qty, price and closing position against the Prime Brokers reports. This saved the client on average 2 hours per day.

Equity SWAP Reset Reconciliation

This system took the swap reset report from the PB and the data from Advent Geneva and ran a full reconciliation for all positions by currency & market. It identified breaks on Qty and Cash settlement amount. The system handled over 3000 positions across 9 markets and saved the client around 8 hours for each reset period.

Cash Reconciliation Reconciliation

This was an Excel based system to manage daily cash reconciliations between Advent Geneva the PB’s daily cash statements. This system saved around 60 mins per day and reduced errors to zero.

Pari Passu Calculations

This involved building a a system to calculate the trade adjustments to be made each month end to bring a fund back into Pari Passu. It could show external trades required to reach Pari Passu as well as internal fund transfers to re balance the funds.

Creation of SQL/Excel Trade Book

This involved building an Excel based Trade Book which stored and validated every trade made since 2004. The system was built and tested, then all historic trades were backfilled and validated so that current positions could be created from the aggregation of the trades. Trade date and settlement date were captured, all pricing and FX data was stored, Accrued Income for each FI trade and each position was set up with key metrics to enable slicing and dicing for reporting purposes. All cash transactions were also captured. These included deal costs, special expenses and income, interest paid or received etc. As the data grew and become too excessive to store in Excel practically, an SQL interface was built using MS DotNet to enable all data to be uploaded to and retrieved from the SQLDB. Each upload was validated in real time to ensure that records were either updated or added to the SQLDB. The result was an SQL based Trade storage system with a powerful Excel based front End.

The data was then used for risk reporting, reconciliations with the Fund Administrators and generation of IRRs using a variety of metrics.

IRR Generator

Many funds use Excel to generate IRRs. The problem is that the Excel IRR formulas are not that easy to use and often give inaccurate results or no result at all. This project involved creating a robust and very flexible IRR engine that was integrated with an IRR report system which, by selecting a few metrics from dropdown choice lists, could generate IRRs by Position, by Market, by Investment Type, by Currency, by Fund, by Sector, by Country, by Region to name a few. The IRR reporting system was completely dynamic and could generate IRRs across all these metrics simultaneously in a few seconds while allowing the users easily to filter the actual cashflows used for each calculation. The system would iterate different IRR seed values if a result was not found and would highlight any where a result could never be found. It would also ensure that the correct IRR result was found when there was more than one available solution.

Document Management System

This project involved building a dynamic cataloguing system to manage all the Deal documents for current and past deals. Each deal contained between 20 and 200 documents which were all categorised using various tags such as date, language and type. The soft copies were then catalogued in the system to enable easy management of documents for distribution to investors, buyers and for compliance.

Imagine ITS I/O Solution

This involved building an I/O feed into an Excel based application which enabled the user to capture multiple VARS for different confidence levels and terms. Previously this process used to be done using a manual export from Imagine for each set of parameters and then combining them manually into a summary table. The solution saved the user on average 45 mins per day.

Imagine Fed Risk Report

This project involved taking an existing Excel based Risk report and revamping it so that the daily updating became fully automated. The core data came from Imagine and users updated via a manual export from Imagine. We automated the feed from Imagine. We removed the pivot tables and re-wrote the table using VBA. We built in extensive validation and robust dynamic formulas to complete missing data. Previously it took around 45 mins and required someone to be fully trained in the multi-step process. After completion, it could be run by anyone in around 30seconds and was 100% accurate.