Skip to main content

General Journals

Business Value

Allows you to import General Journals from an Excel file in the format you receive the file. Use Excel Importer to import expenses, payroll entries, adjustments, internal cost reallocations or any other entries that get posted to the general ledger. The general journal supports entries that post to your bank ledger, customer ledger, vendor ledger, fixed asset ledger, employee ledger and intercompany entries. You can also use Excel Importer to import opening entries for new companies.

Possible Scenarios

Here are some possible scenarios where you might use this feature to import journals:

  • Expenses from a different system like Fraedom or Gorilla Expense.
  • Petty cash expenses that you have recorded in Excel.
  • Payroll entries that you have exported from your payroll system.
  • Auditor's adjustments or IFRS adjustments.
  • Internal cost reallocations - take a look at our example below, where we reallocate expenses from our Admin department to Sales and Production using formulae that we added to our Excel spreadsheet.
  • Consolidation entries for subsidiaries not using Business Central.
  • Create opening balances for new companies in Business Central. This feature can import general ledger, customer, vendor, bank, and fixed asset journals. Our example below shows how to import opening customer entries.

Feature Details

The general journal import can be accessed from inside the journal using the Import from Excel action.

General Journal

Supported Journals

The Import from Excel action can be found in the following journals:

JournalDescription
General JournalUsed for month-end adjustments, payroll expenses, bank journals, etc.
Sales JournalProcess journals related to customers.
Purchase JournalProcess journals related to vendors.
Cash Receipt JournalProcess receipts from customers.
Payment JournalProcess payments to vendors.
Fixed Asset G/L JournalProcess journals related to fixed assets. You use this for FA Posting Types that are integrated to the general ledger.
Fixed Asset JournalProcess journals related to fixed assets. You use this for FA Posting Types that are not integrated to the general ledger.
Intercompany General JournalProcess entries that relate to intercompany parties.
Recurring General JournalProcess journals that recur on a regular basis or accruals that need to be raised and automatically reversed in the next period.
Job G/L JournalProcess general ledger journals related to jobs
tip

In Business Central, recurring journals do not get deleted after posting, so you don't always need to be importing them - it depends more on the setup of the recurring journal lines. If you have lines where the amounts are cleared out and need to get recaptured in the following period, you may consider importing the entries using Excel Importer.

Settings

Exclude lines with zero amounts

The purpose of this setting is to allow you to exclude lines with zero amounts.

ValueDescription
NoImports all lines from the file, including lines that have a zero amount
YesDoes not import lines that have a zero amount

Dimension Code linked to Amount columns

This setting allows you to specify a range of columns that contain an amount for a different dimension. This feature is useful when performing cost reallocations based on formulae that you have defined in a spreadsheet. Here is an example of the type of file you can import.

Dimensioned Amount Columns

The setting value you specify will be the dimension (e.g. DEPARTMENT). In our example, we have a department dimension, and we are allocating expenses from department "ADM" to departments "SALES" and "PROD". Refer to the example below to see how this file gets configured.

Examples

Import for opening customer entries

We import opening entries for a new Business Central company in this scenario. The company previously used a different accounting system. The entries got exported from the previous accounting system.

Excel Mapping Template

Customer Excel Mapping Template

Excel File

Customer Excel Mapping Template

Imported Journal

Customer Excel Mapping Template

tip

The Account/Dimension Mapping is beneficial if the accounts and dimensions in the previous accounting system have different codes from the accounts and dimensions in Business Central. Our employee expense example below uses the Account/Dimension Mapping in a slightly different context but illustrates the possibilities.

Import for Dimensioned Amount Columns

Excel Mapping Template

Customer Excel Mapping Template

note
  • The field First Data Row has been set to 3 because, in the Excel worksheet, that is the first line we want to import
  • In the settings fact box, we have specified DEPARTMENT for "Dimension Code linked to Amount columns."
  • The Amount field is mapped three times - an amount column representing each department
  • We are required to add a mapping line for Dimension DEPARTMENT. If you do not specify this, you will receive an error when importing. As you can see, we do not need to specify a column ID or a constant value.

Excel File

Customer Excel Mapping Template

Imported Journal

Customer Excel Mapping Template

note

We created three journal lines for each line in the Excel worksheet - one for each dimension.

tip

You might have noticed that the journal has document numbers, but we specified no document numbers in the Excel worksheet. Excel Importer uses the number series selected for the batch to generate document numbers.

Employee Expense Import using Account/Dimension Mapping

Here we import employee expense claims. The expense claims got exported from a different system, and the employee codes, dimensions, and expense accounts do not match what we set up in Business Central. You will see how we use the Account/Dimension Mapping in this relatively complex scenario to handle this. Customer Excel Mapping Template

note
  • The columns that reference Account/Dimension Mapping are highlighted with the red box.
  • We are only getting values from the file for B, E and F - you will notice that the Mapping Type is Parent for all of these.
  • The blue boxes highlight where the Mapping Type is Child. These are associated with a Parent, which is the Account/Dimension Mapping that is used to get the value for that field. This feature of the Account/Dimension Mapping allows you to copy values to multiple fields even with only one mapped column.

Account/Dimension Mapping

Account/Dimension Mapping

Excel File

Expense Excel File

Imported Journal

Expense Imported Journal

Step-by-Step Guide

This guide will take you through the steps to import a file containing expenses, assuming the template is already setup.