reconciliation

We recently launched a new video series on different ways to import reconciliation supporting items with BlackLine. In today’s video, our BlackLine Practice Leader, Adam Riskin, demonstrates how to import multiple accrual schedules into a reconciliation.

Step 1. Importing Multiple Amortization Schedules from an Excel File

• In this demonstration, the Excel file contains two prepaid items: health insurance premium and dental insurance premium, set to amortize over 12 months.
• Columns C and D represent the start and end dates of the amortization, while columns E and F hold the invoice amounts and open dates.
• Make sure that the field names in the Excel file match exactly with those in the BlackLine.

Step 2. Ensuring Field Name Consistency

• BlackLine automatically maps Excel fields to its own fields if the names are consistent.
• This step simplifies the import process by ensuring the system recognizes the starting amortization date and invoice amounts correctly.
• The process becomes easier and more efficient with consistent field naming conventions.

Step 3. Importing the Amortization Schedules

• Click on the “Multiple” hyperlink to import the two schedules.
• You need to set up the calculation method, typically straight line, before importing the file.
• Browse and select the Excel file containing the scheduled items.
• Select all records in the Excel file for import and click Import to add the schedules to the reconciliation.

Step 4. Viewing Imported Schedules

• The imported schedules appear in the amortizable Schedule section of the reconciliation.
• You’ll see the amortization amount for each month.
• The “Roll Forward” button provides a traditional view of the amortizable balance at the end of each fiscal period.

Step 5. Handling Non-Standard Schedules

• BlackLine can calculate standard schedules automatically, but non-standard schedules may require manual input.
• Non-standard schedules, such as those with varying amounts each month, may not use auto calculation methods like catch-up.
• The system administrator can use a specific technique to import these non-standard schedules using the Import v7 Data Import tab.

In our next video, Adam will demonstrate how a system administrator can import non-standard schedules.Have you missed some videos in this series? You can find them all on our YouTube channel.

Read more about Accounting & Accounts Receivable:

Importing a Single Accrual Schedule into a Reconciliation With BlackLine

How a System Administrator can Import Multiple Amortization Schedules in BlackLine

Import Multiple Amortization Schedules into a Reconciliation with BlackLine

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