How can we help?

Import Data - Distribution Bulk Upload

IMS provides the capability to upload distribution data in bulk batches from an Excel template. This tool provides an easy method for clients not utilizing waterfall rules to upload data quickly in a self-service fashion. This feature will also allow users to easily edit existing batches and delete previously approved batches if needed whether they are uploaded or generated via the IMS Waterfall Module.

To get started, you will need to assign permissions to your trusted team members that you want to have access to this feature. Best practice is to minimize the number of Team Members that have access since there is the ability to manipulate and delete existing distribution data. To activate this feature, simply select Enabled for the Import Data option in the Team Member’s Functionality tab.  You can access permissions from the Team Members tab under Admin Settings.  Select an individual and go their permissions tab to enable.



import_1.png

You will need to log out and back into the system for this feature to display. To access the Import Wizard, click on your name and select the Import Data option.

import_2.png

To upload a distribution batch, you’ll first want to click your name at the top right, then select the Import Data tab. You will land on the Import Data page which has three tabs: Upload, Pending, and Archived. 

  • Upload: provides the tools to download a template for one or more projects along with a file upload tool to take your completed files.
  • Pending: will show any distribution batches that are sitting in Draft Status and are not visible to your investors.
  • Archived: shows all historical distribution batches that have been marked as distributed and are visible to your investors.

import_3.png

To get started, you’ll first need to download a template for one or more of your projects. Provided the upload you are completing is for the same batch (i.e. all Q1 2018 distributions with the same period start and end dates), you can choose to complete multiple projects at one time. Select the Single Batch Distribution box to get started.

import_4.png

Once selected, you will be prompted to enter information about the batch, similar to using the waterfall module in IMS. In addition, you will find a listing of all your account projects with the ability to select multiple. All required fields with an asterisk must be populated, and at least one project must be selected to download a file.

import_5.png

Please note: that if you select a large number of projects, it may take longer to generate your file. Once the file generates you are ready to begin populating distribution info.

The template is standardized to minimize the inputs needed while providing the identifiers a user needs to correctly identify and match data to the source system the distribution info is coming from. Rows 1-6 contain the batch description info that will be used on import and Columns A-E contain identifiers around the investment, project, class, and investor to help match data correctly.

import_6.png

Columns G-K have the distribution buckets that can be populated. This portion of the template is not locked and your dollar amounts may be entered. If a distribution is made up of multiple components, you should input on the same row. For example, if you are distributing $5,000 and $1,000 is Preferred Return and $4,000 is a Return of Capital, put the amounts in their respective buckets in the same row. Column F will confirm the total amount of the distribution that will be imported into IMS.   Column K represents the payment preference selected for the investor and is optional. You may change or populate blanks but this will only impact the current batch. Best practice is to populate this on the investor profile in IMS Admin if you need to update a preference.

import_7.png

To the extent you have loaded any External IDs on investor profiles, the system will append these values at the end of the template.

import_8.png

If any of the Projects downloaded are a multi-tier structure, the template will generate rows for each entity in the project. If you don’t wish to show distributions on a particular entity, just leave blank. The system will Ignore if there is no data for an Entity.

Once you have updated the distribution data for your template, you are ready to import the file. You can drag and drop the file or select “Browse your files” to search your desktop for the template. Once selected, simply select the “Upload File” button.

import_9.png

Once the “Upload File” button is selected, the system will generate a preview based on the data contained in the template. This will summarize the batches that will be created for each Entity and include the number of investors receiving a distribution and the total dollar amount. If you are satisfied with the preview, you have two options on importing:

  1. If you are satisfied and do not wish to review any further, you can select the “Automatically publish the distributions without review” and your batches will be marked as sent and available to your investors via the Investor Portal.
  2. Select “Notify me by email when the distribution data is ready for review or has been published”. Selecting this by itself will send you an email to alert you when your draft batches are ready to review further. If you select this in addition to the first option, you will receive an email when your distributions are available for investors to see. If you do not select either option, the system will create draft distributions that still need to be approved.

import_10.png

Please note: that if you are processing a large number of Projects, it may take 15-30 minutes for processing to complete. Best practice is to select “Notify me by email” regardless of whether you are publishing drafts or distributed batches.

When generating draft batches, you have a number of options on how to proceed. Clicking on the Pending tab will display all draft batches in your account. The page will show batch details, how the batch was created (Upload or Waterfall), who created, and the date the batch was created. As a user, you can choose the following actions:

  • Approve – if you are satisfied with the batch, select approve to approve the batch and make available on your investor portal.
  • Edit in Project – select this option if you want to review in IMS Admin via the traditional Distribution Batch View. This will also allow you to use the existing IMS approval workflow if desired.
  • Edit in Spreadsheet – this will download the specific batch selected for review in the excel template which can be modified and uploaded again with your changes.
  • Download Nacha File – you may generate a Nacha file if desired for any batches created by the IMS Waterfall and the necessary bank inputs are loaded in IMS Admin for the relevant Entity and Investors
  • Delete – if you do not wish to proceed with the batch, you can delete and start over or disregard.

import_11.png

In addition to performing actions on a one by one basis, you can also Approve, Edit in Spreadsheet and Download Nacha Files in bulk up to 25 at a time.  Again, Nacha files will only run for Batches that were created using the IMS Waterfall.

import_12.png

Once a Batch has been approved, it will move to the Archived tab.  The Archived tab will house all of the distribution batches in the account that are visible to investors.  This includes batches created by the IMS Waterfall module and distributions uploaded with the Import Data feature.  Most accounts will have large amounts of data in the system.  The batches default to sort by most recent, but users can take advantage of the Search bar to narrow the list to a particular Project or Entity.  Users have several options on this tab:

  • Edit in Project – this allows the user to go to the Batch details page in IMS Admin
  • Edit in Spreadsheet – this will download the Excel template which contains the populated data for the relevant batch which can be modified to move money between buckets, investors and increase or decrease the total amounts distributed. Once you upload the revised spreadsheet, the data for the existing batch will be altered.
  • Download Nacha File – you may generate a Nacha file if desired for any batches created by the IMS Waterfall and the necessary bank inputs are loaded in IMS Admin for the relevant Entity and Investors
  • Notify Investors – this will launch a distribution notification to the investors of the selected batch based on your Account Settings (if this feature is not Enabled, no notifications will be sent). Please note that you must access the Archived tab and select "Notify Investors" for each batch or no notification will be sent.
  • Delete – this allows users to delete distribution batches that were previously marked as distributed.

import_13.png 

Distribution Uploader FAQ and Best Practices

When uploading my template, I received an Upload Warning that I have an incorrect format. What would cause this?

In most cases this means you have text in a numeric field. This may occur if you used an Excel lookup function to match data. Use the filters in the template to look for non-numeric values such as #N/A that may result when trying to match data.

I received an email that stated it did not re-cognize an Investment ID or Investor Name. What should I do?

In the event unlock the template and miss-sort, the system validates the Investment ID and Name match on import to ensure no sorting errors occurred. Download a fresh template to ensure your data is current.

Can I re-use an old Template that I downloaded?

Best practice is to download a new template each time you want to upload distributions. This is to ensure you have the latest ID’s and Names which may be changed from time to time by Transfers and general maintenance of your investor profiles.

Is there a limit to the size of the file I upload and how long should this take to complete?

There is no limit to the size that you can upload, but the larger the file the longer it will take to complete. We recommend that you select the Notify option when importing and the system will send you an email when the batches are completed. If you don’t select Notify, when you go to the Pending tab, the system will display a message that batches are pending so you know they are still in process.

I deleted a batch that I did not mean to. Can I recover?

No, deleting is permanent and you will need to re-upload the batch.

Is it possible to have a placeholder for withholdings or fees?

Yes, but you will need to add Fee/Withholding rulesets to the projects that need them visible. Please see a tutorial here on adding waterfall rules. They will append as columns after the Excess Cash column on the waterfall template when generated. Remember that these will need to be input as a negative on the template.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request