We recently launched a new video series on different ways to import reconciliation supporting items with BlackLine.

When working with long-term loans, maintaining an accurate and up-to-date amortization schedule is crucial. This process becomes even more streamlined when using BlackLine, which allows users to import amortization schedules directly from Excel files.

Our BlackLine Practice Leader, Adam Riskin, recently demonstrated how to do this. Watch our video to see Adam walk through the steps to import a single amortization schedule for a five-year loan, ensuring accuracy, efficiency, and smooth data mapping from start to finish.

Step 1. Preparing the Excel File for Import

Before importing your amortization schedule into BlackLine, you need to make sure that your Excel file is well-prepared. Proper preparation not only prevents errors but also speeds up the import process.

• Include all necessary fields: Your Excel file should display a comprehensive debt schedule. This includes key columns like the month-end date, description, monthly payment, interest, principal, and ending balance. The schedule should capture each month’s data throughout the five-year loan period.
• Adjust field names: The column headers in your Excel file need to match the corresponding fields in BlackLine. This step is vital – BlackLine automatically maps fields based on these headers. For instance, if the system expects a field named “Fiscal Period” but your file uses “Month-End Date,” you will run into mapping issues.
• Enhance descriptions: You can simplify field organization by concatenating the month names with a descriptor. For example, a “description” field might contain values such as “January Amortization” or “February Amortization.” This not only makes the file easier to read but also improves its compatibility with BlackLine.

Once your file is set up with proper headers and descriptions, you’re ready to move to the next step.

Step 2. Importing the Excel File into BlackLine

After your file is prepared, importing it into Black Line is straightforward. Here’s how to do it:

• Browse for the file: Start by navigating to the import section within BlackLine and selecting your Excel file. Ensure you are working with the correct worksheet, as some Excel files may contain multiple tabs. Choose the tab that contains the debt schedule you want to import.
• Automatic field mapping: If you’ve followed the earlier step of aligning your Excel file’s headers with Black Line’s field names, the system will automatically map fields like the month-end date to the fiscal period. This saves time and reduces the potential for errors.

However, if some fields don’t match exactly, you can manually map them in BlackLine. Simply use the drop-down menus provided by the system to connect the fields in your Excel file to the corresponding BlackLine fields.

Step 3. Mapping Excel Fields to BlackLine

Accurate mapping is critical for a successful import. If all your field headers match BlackLine’s expected inputs, the system will automatically map them for you. However, when discrepancies arise, such as using different terminology in your Excel file, you will need to do some manual mapping.

• Manual mapping process: Use BlackLine’s user-friendly interface to select the appropriate field for each unmatched Excel column. This ensures that the correct data flows into the system. For example, if the “Month-End Date” column wasn’t automatically mapped, you would manually assign it to the “Fiscal Period” field in BlackLine.
• Selecting all records: After mapping, make sure you are importing the entire schedule by selecting all records. BlackLine provides a “Select All” button, which makes it easy to include the full amortization schedule in the import process.

Step 4. Finalizing the Import Process

The final step in the import process is straightforward: simply click the “Import” button. BlackLine will then bring the entire amortization schedule into your reconciliation. Afterward, you can review the summary view of the reconciliation for the selected period.

From this summary, users can drill down into the full amortization schedule by clicking the schedule icon. This provides a detailed view of the monthly payments, interest, principal, and ending balances for each month, right up until the end of the loan.

One of the major advantages of using BlackLine is that after this initial import, the system automatically rolls the schedule forward each month. This eliminates the need for repeated manual updates, streamlining the reconciliation process for the entire loan term.

Importing a single amortization schedule into BlackLine is a simple but powerful process that can significantly improve your workflow. By preparing your Excel file properly, using BlackLine’s automatic field mapping, and finalizing the import with ease, you can maintain an accurate and hassle-free loan reconciliation process. As a result, future months will require minimal effort as the system automatically manages the schedule on your behalf.

Make sure your Excel files are always aligned with Black Line’s field headers, and you’ll enjoy a smooth, efficient reconciliation process every time.

Read more about Accounting & Accounts Receivable:

Best Practices for Account Reconciliations

Mitigating Financial Risk through Automation

How Automating Accounting Meets the Growing Demands of Finance