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 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.

General Journal

Supported Journals

You can find the Import from Excel action 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 regularly or accruals that need to be raised and automatically reversed in the next period.
Job G/L JournalProcess general ledger journals related to jobs
Statistical Account Journal
tip

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.

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.

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.

Statistical Account Journal

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

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 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

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. 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

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

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.

tip

You can use a similar approach to import opening entries for vendors.

Excel Mapping Template

Customer Excel Mapping Template

Regarding the above image:

  1. Here, we selected the custom value CONTROLACCOUNT. You will notice no mapping to the Excel file and no Constant Value.
  2. 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.
note

This template is included with the other sample template when you use the setup guide.

Excel File

Customer Excel File

Imported Journal

Customer Imported Transactions

Importing Statistical Account Journal

In this example we want to import Statistical Account Journal that represents sales volumes.

Step-by-Step Guide

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