What it does
Use to create repetitive journal entries that are based on data sources.  Map the data source fields to the journal entry required output, and once run, a journal entry is created, ready for import to your G/L accounting system.

When to use it
When you have repetitive journal entries, and the same formulas/copy/paste are used to prepare them based on consistent source data.

Why to use it
This saves quite a bit of time and reduces the risk of errors in preparing repetitive journal entries, allowing you to think through the logic once and apply it over and over again in a controlled, efficient way.

Default shortcut
None

Other Details

• Category: Accounting / Other
• Difficulty: 2/5
• Usage/frequency: 4/5
• Automation factor: 5/5 (estimated 600 seconds saved each time used)
• Type: Bulk task
• Date added: 3/1/2022
• Tags: accounting, journal entries

Related Macros
Create Allocation Journal Entry Lines
Calculate Amortization Lines
Calculate Accrual Lines

Other Articles
None

Create Journal Entry Lines Example File

Prerequisites
For recurring journal entries spanning multiple fiscal periods, have available your fiscal period calendar that will cover the range of recurring journal entry start/end dates.  Identify the data source for your journal entry and have a journal entry import template ready.  Leveraging an existing journal entry support file is helpful.

Instructions
Run the Create Journal Entry Lines macro.  The fist time you run this, it will create the JE Period End Dates, JE Mapping, JE Data Source, and JE Output sheets if those sheets are not found in the active workbook.  If they are found, their contents will be validated and processed.  To populate the sheets, follow the instructions below.

Initial Setup Instructions

1. Populate your period-end schedule, leaving no gaps or overlaps between periods (rows).  Conditional formatting is in place to assist in validating the dates.
2. Identify the data source for your journal entry and copy it to the JE Data Source sheet.  Headers must be in row 1, and data must be in row 2 and down.
3. On the JE Output sheet, add the labels for the fields required for your journal entry (i.e. amount, date, reference, description, currency, etc.). Enter these in the exact same order/format you would for a journal entry that you would normally import to your general ledger.
4. On the JE Mapping sheet, set up a mapping of the columns from the JE Data Source sheet and how they should flow into the JE Output sheet. There are three options for how to set data that will be copied to the JE Output sheet:
A. Data already existing in the JE Data Source sheet
B. Fixed values (i.e. currency)
C. Formulas that can append data to the JE Data Source sheet (and then be copied as if the data already existed)
Make sure to set the mapping for two separate lines (i.e. the debit and the credit) in the two sets of columns. Leverage formulas to reverse the signs or apply other calculations on the values in the JE Data Source sheet.

Ongoing Usage Instructions

1. To record journal entries for multiple periods, enter dates in the JE Start Date and JE End Date fields in the yellow boxes at the bottom of the JE Period End Dates sheet. If these are used, a date field must be specified on the JE Mapping sheet using a [PED] fixed value. The period-end date will be inserted for the JE lines. If the JE start/end dates are not used (left blank), only one set of JE lines will be created, and the dates used for them can flow from the JE Data Source sheet.
2. Copy the source data to be included in the journal entry to the JE Data Source sheet. Remember that data in the JE Data Source sheet must have header fields in row 1 and data in the rows below.
3. Run the Create Journal Entry Lines macro again once the JE Data Source sheet has been populated (and the JE Mapping sheet has been updated, if applicable). This will create JE lines on the JE Output sheet for one or more periods by leveraging the JE Data Source sheet and the JE Mapping sheet to copy the data to the right place, apply fixed values, or apply formulas.  The JE Output sheet should be ready to import to your general ledger accounting system.

Make sure to bookmark the template where you set the mapping fields so you don’t have reconfigure it again unless necessary.