Spreadsheet Modelling and Consultancy

The CG Financial complex spreadsheet and modelling consultancy.

CG Financial has in depth experience in financial modelling and spreadsheets and they have worked with a number of organisations in the financial and legal sector.

The CG Financial team includes quantitative analysts, mathematicians, physicists and programmers, and their breadth of experience in these differing fields enables CG Financial to consistently solve spreadsheet and modelling issues quickly and cost effectively.

If you rely on complex spreadsheet calculations in your work you will recognise some of the following issues:

1. Complex models becoming unwieldy as they evolve over the course of a deal.

2. Increasing risk of verifying the correctness of complex or large models.

3. Difficulties in specifying the model correctly to accord with underlying rules or principles.

4. Entering large data-sets into a spreadsheet is time consuming and error prone.

CG Financial offers:

1. Automation of data input for large data sets - saving time and reducing errors.

2. Exporting of data from spreadsheets into databases.

3. Construction of spreadsheets to your specific design and specifications.

4. Design of sophisticated cross checks to 'prove' the validity of the spreadsheet models you are using.

5. Support and advice in the design of complex models to ensure your spreadsheets are mathematically and logically consistent with the rules and principles that underlie them.

6. Analysis of your current spreadsheet models, with suggested design improvements which we can implement if appropriate.

CG Financial consultancy hedge funds include:

Valuations:
CG Financial can create a spreadsheet model for a hedge fund that does not have a fully automated fund valuation system. CG Financial will also value any parts of a portfolio that require independent calculation, such as over the counter trades etc.

Trade/cash reconciliation:
CG Financial is able to partially automate trade and cash reconciliation and improve the handling of this process.

Trade support:
CG Financial will build models and calculation engines to take advantage of trade opportunities.
Statistical and scenario analysis spreadsheets can be built according to the specification of the trader or fund manager.

Remuneration:
During a takeover, or when partners join or leave a hedge fund team, CG Financial can undertake independent analysis to ensure partners are remunerated fairly according to the principles underlying their scheme. By providing CG Financial with the underlying principles of a deal, we can model the appropriate remuneration for any given situation.

Risk management:
CG Financial can automate the creation of custom risk reports as well as automating any calculations necessary for generating these reports.

Our experience includes:

1. Challenge: A large and complex spreadsheet for current asset valuation and forecasting had evolved over time. This spreadsheet had become so complex that it was almost impossible to maintain and even minor changes would cause errors throughout.

Solution: CG Financial worked with the client to enumerate all the rules and principles underlying the spreadsheet. The rules were analysed and classified into a formal hierarchical model with a well-defined process logic. The formal model was then used to build a clean spreadsheet with proper categorization of the data and coded rules which could not be accidentally modified by the user. In addition, CG Financial created a single overview reporting page which provided summary numbers for all the calculations and corresponding cross-checks.

2. Challenge: Data from a large “flat-format” data file, which had be exported from an Enterprise Database, needed to be imported correctly into a spreadsheet model applying the appropriate assignment and allocation rules based.

Solution: CG Financialcreated a batch import script for our Universal Import tool to directly import the data into the relevant sections of the spreadsheet model. The script was designed to apply the classification rules to the data ‘on-the-fly’ using the values and relationships in the data file. The Universal Import tool was able to import more than 10,000 records in under an hour and reported all duplicate records and data value errors, which could then be corrected.

3. Challenge: A specialist advisor was working with a fund management company to provide the legal framework for charging an accrued performance fee with an annual reset for a unit trust fund. The advisor was struggling to devise a model which could reflect the performance fee in a fair fashion, such that investors would not be unfairly penalized or benefitted by buy or selling units at any time.

Solution: Our modellers worked with the client to create a model which reflected the daily fee accrual based on the performance to date as fairly as possible for both investors looking to either sell or buy units. A formula was developed which provided a daily fund price adjustment in such a fashion to reflect an ‘attributed’ mid-term performance fee. The CG Financial team created a spreadsheet to simulate how the formula would work in practice, simulating the daily fund asset evolution and the resulting unit price. This model allowed both the advisor and fund manager to clearly understand the adjustment formula and also provided the basis of presentation which was used to explain the fee structure to distribution channel.

Please contact us for futher information.