Asset Level Input
For all funds for which asset level data entry is enabled data of the companies which the fund is invested in can also be loaded on a quarterly basis (assuming the data is made available by the GP). The data which the system can track quarterly are:
LTM Sales
LTM EBITDA
Net Debt
Investment Cost
Gross MoIC
Enterprise Value
Where the functionality can be found
How the functionality is used
Data can be loaded either individually per fund or in bulk for all fund/company combinations at once.
quarterly data,
company setup data The “setup data” is a mixture of once off type masterdata and other asset level data which is not bound to a quarter.
Screen 1: portfolio setup_ Company Entry Data
The portfolio companies are managed here
Company entry
new portfolio companies are setup
Change “fund”
“+Add Company” to add postion
Excel upload is possible
Switch to 2 Quarterly Updates
Asset Level Masterdata
Company Name
Description
Industry
Country / Region
Private/Public
Entry data
Bord seats
Non-quarterly KPIs able to be updated (at Entry & Current)
ship %
Revenue
EBIT
Debt
IRR
EV/E
OwnerBITDA, Debt/EBITDA
Before you start company updates
Before you load company data for a fund make sure the fund is configured for asset level input and the asset level input currency is configured (should this be different from the fund level input currency).
Data Management > Fund Management > Fund Setup
Bulk updates
bulk loading company masterdata & setup data
To load data via the “Upload from Excel” functionality. Use the template.
Be alert to ensure perfect dataquality to upload asset level data, to avoid incorrect reports and large manual efforts.
Please consult the “how to” guide Data Quality Check for Excel Uploads
The “record number” in column A is required
Blank rows are allowed
Only valid countries and industries should be used
Date fields must be formatted as Text and in the format dd.mm.yyyy for the system to recognize as valid dates
All numeric fields should be unformatted with no thousand separator, no %, etc.
Click the “Upload from Excel” button and drag the filled in Excel file to the drop zone on the dialog
Click the “Upload” button. The dialog will display a preview of the data.
Next click the “Load” button. Data will now be written from the form to the intermediate staging cube displayed in the main form. The form should display a success message.
When you return to the main screen the form should be full of data.
Should you receive an error message like the one below while loading the data this indicates that there is an error in the file.
Should this happen click cancel ! You will then get a summary of the error messages
The reason to cancel the transaction is that if all validation passes then the entire table is loaded via a bulk update. Since a single cell with bad metadata or data would fail the entire bulk update the loader on finding an error will attempt to load each cell one-at-a-time so that all valid data still gets loaded. However, if the table contains 1000 cells then this will be approximately 1000x slower than the bulk update. There’s no reason to wait. It is much better to cancel, find what is wrong in the file and try the upload again.
The most likely reasons for an error are:
Incorrect header in rows 1-2 columns B-AM (headers must be exactly as per the template above)
Incorrect row ID in column A
Text value in a numeric field (columns N-AM). E.g. “-“, “NA” (like in the example above)
Decimal separator in Excel was different from the regional settings of the browser
Find and fix the errors and the load should be fine on the next attempt.
Up to this point the asset level data has been loaded only to a staging area in the database. The reason a staging area is used is to protect the integrity of the masterdata and data. In the next step each record will be screened, and only valid records will be loaded to the asset level reporting module.
Up to this point the “Data Load Validation” form to the right of the main form should be empty. (Hopefully it stays this way!)
Loading data from the staging area to the asset level reporting module is done via the “Load Data” button on the header of the main form.
The Data Validation form is no longer empty! The form will display a detailed error message for each record with errors which was unable to be loaded. Scrolling to the right of the validation form you can see a flag for any field causing the rejection.
In the example above we can see that records 190-215 are using incorrect industry sector assignments. The main form on the left also reacts to the reject flag and will highlight records which were not loaded.
Using the error messages find and fix errors in the Excel file and re-upload until all errors are resolved.
During the bulk update any new company name not previously encountered will be registered in the database. Should a company have changed name in the file received from the GP or there is a typo this can result in duplication of company data since there will now be 2 records for the same company with data held against both names.
To avoid this company name changes need to be recorded in advance on the “At Entry Data” form. This is described below.
Ad-hoc updates
Manual company masterdata setup
Companies can also be manually created for funds and existing company setup data can be maintained and adjusted via the “At Entry Data” form
Data Management > Fund Management > Asset Level Input > Portfolio Setup > Company Bulk Update
To add a company to a fund click the “Add Company” button and fill out the required fields.
Once the company is created data can be maintained either directly in the form or via uploading from Excel. The templates below are in the correct format to load.
All numeric fields should be unformatted (no thousand separator, no % just decimal values)
Entry Date must be TEXT in the format dd.mm.yyyy
Excel templates
These templates for loading data can be used for initial company masterdata setup and loading non quarterly data (section below). In case of loading non quarterly data, change the column headers for the correct KPIs).
Excel Template for company data upload single fund - at entry.
Excel Template for company data upload multi fund
On this screen you can also maintain name changes for companies in the case that the company name changes in the information provided by the GP. This is done in the “Name Key Changes” table.
Entering alternate names allows data to be loaded against any of the names and automatically resolved to the correct underlying company ID avoiding duplication of companies and company data.
Note that company names must be unique across all funds.
Loading non-quarterly data
“Current KPIs” which are not linked to a quarter but represent the current performance can also be updated quarterly. E.g. Invested Capital, Unrealized Value, Realized Value, Exit Date, Current Ownership %, current EV/EBITDA multiple, Current Debt/EBITDA multiple, IRR.
The templates for loading data are identical to the “At Entry Data” templates (just change the column headers for the correct KPIs).
It is not actually necessary to load the current KPIs separately to the At Entry KPIs. This can all be done during one step/upload either from Portfolio Setup > At Entry Data or from Quarterly Updates > Company Info. All that is required it to have the appropriate set of KPI names in the column header in row 1. (Names must match to the KPI definition in the database).
Non-quarterly KPIs able to be updated (at Entry & Current)
ship %
Revenue
EBIT
Debt
IRR
EV/E
OwnerBITDA, Debt/EBITDA
Quarterly updates
Loading quarterly data
Data Management > Fund Management > Asset Level Input > Quarterly Updates > Input by Quarter
Quarterly data (LTM Sales, LTM EBITDA, Net Debt, Investment Cost, Gross MoIC, Enterprise Value) can be loaded in bulk for all funds and all companies. Data can also be loaded individually for all companies of a given fund. Just be careful which template you use! Unused rows and columns should be deleted before using the Upload from Excel function.
Excel Templates
Excel Template for quarterly data upload of performance measures for multiple fund and companies with multiple measures per company.
Excel Template for quarterly data upload of performance measures for multiple companies with multiple measures per company.
Excel Template for quarterly data upload of performance measures for a multi companies for a single measures per company.
You can also copy/paste directly from Excel into the form, but if you do this make sure that the companies, quarters and the measure match between Excel and what is displayed in the form.
Important: After data is updated
After all company data is loaded (both metadata and financial data) run the “Update Analysis” function.
For convenience this button is found on multiple screens within the “Asset Level Input” module, but the function only needs to be run once after all data for all companies is loaded. This function runs a background job which groups companies according to country, region, industry but also to financial groupings such as MoIC cluster, EV category, EBITDA growth cluster, Sales growth cluster, etc.
For portfolio level reporting it is important to run this function in order that the region and industry splits are correct and updated. At asset level this ensures all the additional company level analysis is updated and clusters are correct and updated.