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.
Supported Journals
The Import from Excel action can be found in the following journals:
| Journal | Description |
|---|---|
| General Journal | Used for month-end adjustments, payroll expenses, bank journals, etc. |
| Sales Journal | Process journals related to customers. |
| Purchase Journal | Process journals related to vendors. |
| Cash Receipt Journal | Process receipts from customers. |
| Payment Journal | Process payments to vendors. |
| Fixed Asset G/L Journal | Process journals related to fixed assets. You use this for FA Posting Types that are integrated to the general ledger. |
| Fixed Asset Journal | Process journals related to fixed assets. You use this for FA Posting Types that are not integrated to the general ledger. |
| Intercompany General Journal | Process entries that relate to intercompany parties. |
| Recurring General Journal | Process journals that recur on a regular basis or accruals that need to be raised and automatically reversed in the next period. |
| Job G/L Journal | Process general ledger journals related to jobs |
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.
| Value | Description |
|---|---|
| No | Imports all lines from the file, including lines that have a zero amount |
| Yes | Does 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.
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
Excel File
Imported Journal
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
- 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
Imported Journal
We created three journal lines for each line in the Excel worksheet - one for each dimension.
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.
- 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
Excel File
Imported Journal
