SmartCap Logo

Risk Reporting Process

Please sign in to access the documentation.

Green Fund Risk Reporting Process

An interactive overview of the data flow for the Green Fund Risk Profile Report, from initial sources to the final analysis tool.

Quarterly Risk Reporting Process Manual

This manual outlines the step-by-step process for generating the quarterly Green Fund Risk Profile Report, from initial data gathering to final report generation.

Phase 1: Data Collection

Timeline: Begins approximately 3 weeks after each quarter-end.

1.1Collect Data from Fund Administrator

The Fund Administrator (Swedbank) provides core valuation and NAV data. The liaison for this data is Helen Kapp (CFO), who receives the files and uploads them to the network drive.

  • Valuation Data: Sourced from a file like VCFUND_GREEN_DEFENCE_PORTFOLIO QQ_YYYY.xlsx.
  • NAV Data: Sourced from a file like SMARTCAP_NAV_VARIATION QQ_YYYY.xlsx.
  • Location: Files are saved to the network drive path for valuation data.

1.2Collect Data from Internal Teams

The Investment Team provides information on new and existing investments. The primary contact is Gioia Pucci.

  • New Investment Data: Ensure new investment details are added to the Investments.xlsx master file. This data is used to populate the commitments worksheet.
  • Investment Documentation: For each new investment, Gioia must add the relevant legal and decision-making documents to the network drive in a folder named YYYY.M {investment_name}.
    • For direct portfolio company investments: Investment Memorandum, Investment Committee Decision, Shareholder Agreement (SHA).
    • For fund investments: Investment Memorandum, Investment Committee Decision, Limited Partnership Agreement (LPA), Side-letters, Subscription Agreement.
  • Partner Fund Reports: For existing fund investments, collect quarterly investor reports and save them to the network drive path for indirect valuations. This data is transcribed into the valuations_partnerships worksheet.
  • Drawdown/Distribution Notices: Collect and save notices to the network drive path for drawdown notices. This data is transcribed into the drawdowns and distributions worksheets.

Phase 2: Data Consolidation & Entry

Timeline: Begins once Fund Administrator data has been collected.

2.1Update Valuations (GF).xlsx

This is the central file for financial data. Follow the steps below to update its worksheets.

Update the valuations worksheet

1. Copy New Data:

  1. From the source valuation file (VCFUND_GREEN_DEFENCE_PORTFOLIO...), filter for and copy all rows where Portfolio is 'SmartCap Green Fund'. Include the 'TotalNetAsset' row.
  2. In Valuations (GF).xlsx, navigate to the valuations worksheet.
  3. Select the first empty cell in column A below the existing table and paste the data as values. The table range will extend automatically.

2. Perform Data Adjustments:

  • Merge SEB Overnight Deposit: Swedbank reports SEB overnight deposit principal and interest on separate rows. Merge them:
    1. Identify the principal (AssetName = 'AS SEB Pank') and interest (AssetName = 'Cash Acc Interest Receivable') rows.
    2. Copy the value from MarketValuePC on the interest row to the InterestPC and InterestQC columns of the principal row.
    3. On the principal row, update MarketValuePC with the formula: =[@Quantity]+[@InterestPC]
    4. Delete the original interest row.
  • Standardize Overnight Deposit Names: In the AssetName column, rename overnight deposits to the format 'Overnight Deposit ({cpty_name})'. E.g., 'AS SEB Pank' becomes 'Overnight Deposit (SEB)'.
  • Verify Instrument Types: Check the InstrumentType and DetailedAssetType columns for correctness (e.g., direct investments are 'Equities', funds are 'Equity Fund', deposits are 'Money Market').
  • Highlight Changes: Apply a yellow background to any cells you manually modified. This is a crucial step for auditability.
Update the nav worksheet
  1. From the source NAV file (SMARTCAP_NAV_VARIATION...), select the row where Fund Name is 'SmartCap Green Fund'.
  2. In Valuations (GF).xlsx, open the nav worksheet.
  3. Select the first empty cell in column A below the existing table and paste the data as values. No manual corrections are typically needed.
Update the valuations_partnerships worksheet

This table contains look-through valuation data for indirect investments. The information is found in Quarterly Investor Reports from partner funds (see Phase 1.2).

These reports are often PDFs, requiring manual data extraction. Ensure all financial values are converted to EUR before entry.

Update the commitments worksheet

Add records for any new direct or fund investment commitments made during the quarter. Source information is from the Investments.xlsx master file and can be cross-referenced with investment documentation (Investment Memos, SHAs, LPAs, etc.).

Update the drawdowns worksheet

Add records for all capital drawdowns made from the Green Fund into its investments. The data is sourced from files like Investments.xlsx (for direct investments), Drawdown Notices (for fund investments), and payment records in Cash Flows (GF).xlsm.

A. Adding a Direct Portfolio Company Drawdown:

  • Instrument Name: Use the standardized name from Reference Data.xlsm.
  • Portfolio Company Name: Same as Instrument Name.
  • Drawdown Date / Pay Date: Use the actual payment date.
  • Currency: The original currency of the investment (ISO3 code).
  • Drawdown Amount: This is the EUR equivalent, calculated automatically from Amt Ccy and Fx Rate.
  • Type: Enter "Investment".
  • PaymentsRef: Get the reference from the Cash Flows (GF).xlsm file.
  • Compartment: Enter "GF".
  • Entry Type: Enter "Original".
  • Deal Stage: Source from Investments.xlsx.
  • DDRef: Enter "-".
  • Amt Ccy: The amount in the original currency, from Investments.xlsx.
  • Fx Rate: The exchange rate at the time of investment.

B. Adding a Fund Investment Drawdown:

  • Instrument Name: Use the standardized name from Reference Data.xlsm.
  • Portfolio Company Name: From the Drawdown Notice (use of funds). Use a standardized name or "-" for expenses.
  • Drawdown Date: Date from the Drawdown Notice.
  • Pay Date: Actual payment date from Cash Flows (GF).xlsm.
  • Currency: The original currency of the drawdown (ISO3 code).
  • Drawdown Amount: This is the EUR equivalent, calculated automatically.
  • Type: "Investment" for capital calls or "Expenses" for fees.
  • PaymentsRef: Get the reference from the Cash Flows (GF).xlsm file.
  • Compartment: Enter "GF".
  • Entry Type: Enter "Original".
  • Deal Stage: Leave empty.
  • DDRef: Use the format '{fund_symbol}-{DDN#}', e.g., 2C-DDN9.
  • Amt Ccy: The amount in original currency, from the Drawdown Notice.
  • Fx Rate: The exchange rate at the time of investment.

Phase 3: Update Master & Reference Data

Timeline: After new investments for the quarter are known.

3.1Update Reference Data.xlsm

The Reference Data.xlsm file is the master source for all non-financial instrument data. It must be updated each quarter to reflect new investments and ensure data integrity.

Update the Portfolio Companies worksheet

A new record must be added for any new direct investment made by SmartCap or any new indirect investment discovered through partner fund reporting (e.g., drawdown notices, quarterly investor reports).

  • For direct investments: Source data from the Investments.xlsx master file, maintained by Gioia Pucci.
  • For indirect investments: Source data from quarterly reports provided by partner funds.

Important: Before adding a new company, search the worksheet to ensure it does not already exist. Each unique portfolio company must have only one record.

For detailed guidance on each column and its specific data source (e.g., PitchBook), refer to the Portfolio Companies documentation in the Data Files tab.

Update the Investment Funds worksheet

A new record is added to this table whenever SmartCap makes a new fund investment.

  • Source information about new fund investments from the Investments.xlsx master file.
  • If additional information is needed, contact the Investment Team via Gioia Pucci.

Important: Before adding a new fund, search the worksheet to ensure it does not already exist. Each unique fund must have only one record.

For detailed guidance on each column, refer to the Investment Funds documentation in the Data Files tab.

Perform Data Mapping & Validation

Prerequisite: This process requires the valuations and valuations_partnerships worksheets in Valuations (GF).xlsx to be fully updated for the current quarter.

  1. Update the Codes worksheet: This worksheet maps source identifiers (e.g., from Swedbank) to standardized names. Add new rows to link any new instruments to their master records. Refer to the Instrument Code Mapping documentation for details.
  2. Validate in `CodeCheck`: Navigate to the CodeCheck worksheet and refresh all data connections (Data > Refresh All). Verify that all issue counts are zero and all status indicators are green. This confirms every instrument has a valid mapping.
  3. Final Audit: Open the Auditors worksheet and confirm that all checks in the 'AUDIT RESULT' column show "PASS". This is the final confirmation of data integrity before running the main report.

3.2Update Term Deposits (GF).xlsm

This file generally does not require manual data entry. Its primary purpose is to import deposit information from the main rebalancing tool (DepoRebalance_vX.X.xlsb) and link it to the external valuation data from the Valuations (GF).xlsx file.

Data Dependencies: Before proceeding, ensure the following source files are up-to-date:

  • Valuations (GF).xlsx
  • DepoRebalance_vX.X.xlsb (e.g., DepoRebalance_v2.2.xlsb)
Verification Process
  1. Refresh Data Connections: Open the workbook. Data connections should refresh automatically. To manually refresh, go to Data > Refresh All.
  2. Verify Data Linkage: In the Term Deposits worksheet, check column L: Check (AssetName from Swedbank Valuations). Ensure that every active deposit row has a valid entry and is not showing an #N/A error. This confirms that internal deposit records are correctly linked to the external valuation file.
Configuration Note: If source file names or locations change, the paths must be updated in the Sources worksheet of this file.

Phase 4: Prepare & Publish Report

Timeline: After all source data has been consolidated and validated.

Prerequisite: Ensure all steps in Phase 1, 2, and 3 are fully completed. The reporting tool relies on the data from Valuations (GF).xlsx, Reference Data.xlsm, and Term Deposits (GF).xlsm being up-to-date and validated for the current quarter.

4.1Generate Report Data

  1. Open the main reporting tool: Risk Profile Report vX.X.xlsm.
    Note: An automatic script will run on open to refresh the list of available reporting periods.
  2. Navigate to the MAIN worksheet.
  3. Select the quarter for which to generate the report (e.g., Date: 2025-Q2).
  4. Leave other selections as they are (Fund: Green Fund; Lookthrough Date Type: NAV Date).
  5. Click the REFRESH button. This initiates the core data processing sequence.
What happens when you click REFRESH?

The REFRESH button executes two critical, sequential actions:

A. Power Query Data Refresh:

  • The workbook updates all its data connections, pulling the latest validated information from the source files prepared in previous phases:
    • Valuations (GF).xlsx
    • Reference Data.xlsm
    • Term Deposits (GF).xlsm

B. VBA Macro Execution:

  • After the data is refreshed, a VBA script runs automatically to process the new data and populate the primary analysis worksheets:
    • direct worksheet: This sheet is populated with on-balance-sheet positions, largely reflecting the data received from the Fund Administrator, but enriched with standardized reference data.
    • lookthrough worksheet: This sheet decomposes each fund investment into its underlying indirect holdings, treating them as if the Green Fund had invested directly. The exposure is calculated as: (Fund's total investment size in company) × (Green Fund's % ownership in the fund).

4.2Validate Report & Enter Manual Data

Once the data refresh is complete, review the key worksheets to ensure data integrity and add required manual inputs.

  • Navigate to the RISK PROFILE worksheet. You should see updated values for the selected quarter.
    • Carefully validate all automatically sourced data points against your expectations.
    • Fill in the values for all manual entry fields (marked with a distinct color). Refer to the Risk Profile Analysis tab for detailed definitions and sourcing for each line item.
  • Navigate to the COMPLIANCE worksheet.
    • Validate the calculated values and ensure there are no calculation errors (e.g., #REF!, #N/A).
    • Important: In the event of changes to the fund's Investment Rules or Investment Policy Statement (IPS), the compliance rules in this worksheet must be updated accordingly.

4.3Archive Quarterly Data

After all data has been added and validated, the current quarter's results must be archived for historical reference. This action is performed directly from the main report view using a status-aware icon.

  1. On the RISK PROFILE worksheet, locate the database icon in the column header for the current reporting period.
  2. The icon's color indicates the action that will be performed upon clicking:
    • Green Icon: Indicates that no data is currently stored for this quarter. Clicking it will **archive** the data, copying all values and comments as static data to the store worksheet. The icon will then turn red.
    • Red Icon: Indicates that data for this quarter is already stored. Clicking it will **delete** the existing data for that quarter from the store worksheet. This is useful for making corrections. The icon will then turn green, allowing you to re-archive the corrected data.
  3. Ensure the icon is green, then click it to finalize and store the quarterly data.
Purpose of the Data Store: The store worksheet acts as a historical log of all risk reporting data. The "Previous Quarter" values on the main RISK PROFILE sheet are populated by looking up data from this log, enabling quarter-over-quarter analysis.

4.4Publish Final Report

The final step is to publish the report by creating a finalized, version-controlled copy in the designated network folder.

  1. Save the master Risk Profile Report vX.X.xlsm file.
  2. Use "Save As" to create a copy of the entire workbook.
  3. Navigate to the correct folder path: ..\Risk reports\YYYY\QQ YYYY (e.g., ..\Risk reports\2025\Q2 2025).
  4. Name the file using the following convention: 'Risk Profile Report GF YYYY (QQ)_FINAL.xlsm'.
    • Where 'YYYY' is the 4-digit year (e.g., 2025).
    • Where 'QQ' is the 2-digit quarter (e.g., Q2).
    • Example: Risk Profile Report GF 2025 (Q2)_FINAL.xlsm

Fund Administrator

Swedbank Logo Swedbank
Arbo Kalk Head of Fund Administration
Email: Arbo.Kalk@swedbank.ee Liaison: Helen Kapp (CFO)

Data Providers

PitchBook Logo PitchBook

Internal Teams

Investment & Finance
Helen Kapp CFO
Email: Helen.Kapp@smartcap.ee Phone: +372 504 3284
Gioia Pucci Investment Specialist
Email: gioia.pucci@smartcap.ee Phone: +372 528 2724

Risk & Compliance

Risk Team
Matis Tomiste External Consultant
Email: matis.tomiste@risk.eu Phone: +372 525 4262
Erik Punger Head of Compliance and Risk
Email: erik.punger@smartcap.ee

Source Data Files

Select a source file from the list to see its contents.

Tool Overview

The Risk Profile Report (GF).xlsm is an Excel-based tool that uses Power Query to consolidate data from multiple sources. It automates the calculation and visualization of key risk metrics, compliance checks, and portfolio allocations for the Green Fund.

File Location:\\8. Riskijuhtimine\Draft materials\

Key Features & Outputs

  • Generates a comprehensive Risk Profile Report.
  • Performs Investment Compliance checks against fund mandates.
  • Calculates and visualizes portfolio allocations (by country, sector, etc.).
  • Provides analytics on NAV, returns, and drawdowns.

Data Inputs

  • Valuations (GF).xlsx
  • Reference Data.xlsm
  • Term Deposits (GF).xlsm

Configuration Parameters

The tool's data sources are configured via named ranges within the sources worksheet, allowing file paths to be updated without editing the queries directly.

  • rootfolder: Defines the main directory path for all data.
  • datafolder: Defines the sub-directory where source data files are located.
  • filepath_valuations: Constructs the full path to the Valuations file.
  • ips_start_date: Defines the start date for Investment Policy Statement (IPS) compliance checks.

Understanding the Report Automation (VBA Macros)

The Risk Profile Report workbook contains powerful automation scripts written in a language called VBA (Visual Basic for Applications). These scripts, often called "macros," handle complex, repetitive tasks to ensure data is processed consistently and accurately every quarter. This section explains what these key automations do in simple, non-technical terms.

The Main Refresh Process

Triggered by: Clicking the REFRESH button on the MAIN worksheet.

This is the primary engine of the report. When you click REFRESH, it kicks off a sophisticated, multi-step process (runLTP macro) that builds the entire analysis from the ground up.

Step 1: Gathering All Direct Positions

The first task is to collect and understand every asset the fund holds directly on its books for the selected quarter.

Think of this step like creating a set of virtual filing cards. The macro (DirectPositions) goes to the refreshed data from valuations and creates one "card" for each position (e.g., a cash deposit, a direct investment in a company like 'Paul-Tech', or a fund investment like '2C Ventures').

Each card is filled with all the necessary details for that single asset, such as its name, fair value, currency, counterparty, and reference data (sector, country, etc.) that was linked via Power Query.

Step 2: Performing the "Look-Through"

This is the most complex part of the automation. When the process in Step 1 finds a "filing card" for a fund investment (e.g., '2C Ventures'), it triggers a special sub-process (LookthroughPositions).

This sub-process "looks through" the fund investment to see the individual companies it holds. It goes to the valuations_partnerships data and creates a new set of virtual filing cards for each underlying company (e.g., 'Zerofy', 'ÄIO').

Crucially, it calculates our fund's effective share of each underlying company's value. These new "indirect" cards are then virtually attached to the main '2C Ventures' card.

Step 3: Writing the Final Data Tables

Once all the virtual filing cards are created and organized, the final step is to write this information into the workbook's data sheets (addDataDirect and addDataLookthrough macros).

  • The data from all the main "filing cards" is neatly written into the table on the direct worksheet.
  • The data from all the "indirect" filing cards (from the look-through process) is written into the table on the lookthrough worksheet.

After these tables are populated, the automation refreshes all Pivot Tables in the workbook that depend on this new data. This entire sequence ensures that the final report you see is fully updated and internally consistent.

The Data Archiving Process

Triggered by: Clicking the icon on the RISK PROFILE worksheet.

This automation (ToggleStoreClear macro) allows you to save a permanent, historical snapshot of the current quarter's report. The icon is interactive and changes color to show what it will do.

  • When the icon is Green, it means the data for that quarter has not been saved yet. Clicking it will:
    1. Check if all manually-entered fields have been filled in.
    2. Copy the final values from the report column to the store worksheet.
    3. Turn the icon Red to show the data is now saved.
  • When the icon is Red, it means data for that quarter is already in the archive. Clicking it will:
    1. Delete all rows for that specific quarter from the store worksheet.
    2. Turn the icon Green, allowing you to re-save if you've made corrections.

Other Utility Scripts

Triggered by: Various events, like opening the file.

The workbook also contains smaller helper scripts that improve usability and data integrity.

  • On Workbook Open: A small script (Workbook_Open) automatically runs when you open the file. Its only job is to refresh the underlying data connection to the Valuations (GF).xlsx file. This ensures that the dropdown menu on the MAIN sheet always shows the latest available reporting quarters.
  • Housekeeping Scripts: Several other utilities (utils.bas) run in the background to handle tasks like cleanly deleting old data from tables before new data is written, and ensuring the workbook can correctly locate its source files, even if they are saved in a OneDrive or SharePoint folder.

Risk Profile Line-Item Analysis

This section details the data source, ultimate owner, and transformation logic for each indicator in the RISK PROFILE worksheet. Use the search box to filter indicators.

Interactive System Map

Hover over any item to focus on its data path.

Fund Administrator
Data Providers
Investment/Finance Team
Risk & Compliance
Valuations (GF).xlsx
Reference Data.xlsm
Term Deposits (GF).xlsm
Risk Profile Report (GF).xlsm

Data Mapping & Reconciliation (Codes Worksheet)

A critical step in the data process is mapping inconsistent source data to standardized internal names. The Codes worksheet in Reference Data.xlsm serves as this master lookup table. It allows the system to link an instrument mentioned in a source file (e.g., a valuation report from Swedbank or a partner fund report from SuperA) to its official record in the Portfolio Companies or Investment Funds tables.

Mapping Logic Example:

The system uses this table to translate source identifiers. For example, when a valuation report lists an asset with Security_ID = 'PAULTECH', the table maps it to the standardized internal name 'Paul-Tech'.

Standardized Internal Name Source Identifier / Name Source System Links to Master Table
Paul-Tech PAULTECH Swedbank tbl.instruments
Lumebot Autonoomne Robot OÜ / Lumebot SuperA tbl.instruments
Superangel Fund SUPERANGEL1 Swedbank tbl.parnerships