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 inter-company 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 - 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.
- Import statistical entries using external data
Feature Details
The general journal import can be accessed from inside the journal using the Import from Excel action.
Supported Journals
You can find the Import from Excel action 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 regularly 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 |
Statistical Account Journal |
In Business Central, recurring journals do not get deleted after posting, so you don't always need to import 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, 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.
Custom Values
Custom values can be used to add custom features to imports. The app includes a custom action for General Journals to set the balancing account of the journal to the control account setup for the customer, vendor, bank account, employee or fixed asset. You can read more about this here.
Statistical Account Journals
You can import Statistical Account Journals from the Statistical Account Journal page.
The template also includes a Custom Value that allows you to import more than one statistical value from the same line.
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 those 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. You will receive an error when importing if you do not specify this. You will also notice that we specified a constant value on this line. You must specify a valid dimension value for the constant value, but it does not matter which one you enter here.
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
Defaulting the control account for opening entries
This example is a variation of the previous customer entries example. The difference is that we have the app assign the control account. This feature is useful when importing opening entries for many customers and you have set up several control accounts.
You can use a similar approach to import opening entries for vendors.
Excel Mapping Template
Regarding the above image:
- Here, we selected the custom value CONTROLACCOUNT. You will notice no mapping to the Excel file and no Constant Value.
- We added a default for the System-Created Entry field to allow the direct use of the control account, which you usually configure to prevent direct posting.
This template is included with the other sample template when you use the setup guide.
Excel File
Imported Journal
Importing Statistical Account Journal
In this example we want to import Statistical Account Journal that represents sales volumes.