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 also allows 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, click the dropdown arrow next to their name, select edit, and go their permissions tab to enable.
You will need to log out and back into the system for this feature to display.
To upload a distribution batch, you’ll first want to click the Profile & Preferences icon, which looks like a silhouette, at the top right, then select Import Data. 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. This is also where you will find the option to upload a Base Point.
- Pending:will show any distribution batches that are sitting in Draft or Ready to Distribute Status and are not visible to your investors.
- Archived:shows all historical distribution batches that have been approved and marked as Distributed and are visible to your investors.
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.
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.
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.
Columns G-L have the distribution buckets that can be populated. Note that a column for fee/withholding will only appear if you have this configured within a waterfall ruleset. 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 total, 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 M 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.
To the extent you have loaded any External IDs on investor profiles, the system will append these values at the end of the template.
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. After you save the file, you can drag and drop it or select “Browse your files” to search your desktop for the template. Once selected, simply select the “Upload File” button.
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:
- 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.
- 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.
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 for any batches created by the IMS Waterfall if the necessary bank inputs are loaded in IMS Admin for the relevant Entity and Investors. The batch must be at “Ready to Distribute” status, which can be done from the project page view, and you must be set to be the distribution Approver within the distribution workflow settings.
- Delete – if you do not wish to proceed with the batch, you can delete and start over or disregard.
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 if you are the distribution Approver and the batches were created using the IMS Waterfall and are at “Ready to Distribute” status, or that have had a bank account manually associated to the batch via the projects distributions page.
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 as long as 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.
Please Note: The Notify Investors option will only populate on this dropdown menu if distribution notifications are turned on in Investor Settings. Distribution Notifications are located in the Waterfall/Distributions section of investor settings.
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. Best practice to avoid this and other errors is to copy the results of your formulas and just paste the values, rather than trying to upload data with formulas.
I received an email that stated it did not recognize an Investment ID or Investor Name. What should I do?
The most likely cause is using an outdated template, as the system validates the Investment ID and Name match on import to ensure no sorting errors occurred. If anything has changed in your cap table (e.g. transfer, new investment, etc.) the old template will no longer validate properly against system data. 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 IDs 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.