What it does
Use to create repetitive allocation journal entries that are based on data sources and allocation types (cost drivers).  Map the data source fields and allocation types/values to the journal entry required output, and once run, an allocation journal entry is created, ready for import to your G/L accounting system.  Define as many different allocation types as you’d like.  Each data source line can use a different allocation type or the same one.

When to use it
When you have repetitive allocation 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 allocation 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/29/2023
• Tags: accounting, journal entries, allocations

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

Other Articles
None

Create Allocation Journal Entry Lines Example File_2023-04-06.xlsx

Prerequisites
Identify the data source for your journal entry, the different segments you need to allocate to (and their allocation types/values), and have a journal entry import template ready.  Leveraging an existing journal entry support file is helpful.

Instructions
Run the Create Allocation Journal Entry Lines macro.  The fist time you run this, it will create the AJE Instructions, AJE Mapping, AJE Data Source, AJE Allocations, and AJE 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. Ensure that the allocation data source has header fields in row 1 and data in the rows below. The data should be items you want to allocate to other departments, cost centers, entities, etc.  Copy this to the AJE Data Source sheet.
2. Identify the allocation method for each line in the AJE Data Source sheet and all the departments, cost centers, entities, etc. that they should be allocated to. These should be listed on the AJE Allocations sheet (see examples on that sheet). The AJE Allocations sheet must have header fields in row 1 and data in the rows below. You can list as many different allocation methods as you want, starting in column B and moving to the right (with no gaps). On the AJE Data Source sheet, you can specify a column that includes the allocation method, matching one of the column headers (cell B1 and to the right). If no allocation method is specified, amounts will be allocated evenly to all items listed in column A of the AJE Allocations sheet.
3. On the AJE 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 JE that you would normally import to your general ledger.
4. On the AJE Mapping sheet, enter these three values for columns that will be used to calculate allocations:
1. Cell B3 – Amount (required) – enter the column letter on the AJE Data Source sheet containing the amount to be allocated
2. Cell B4 – Allocation Segment (required) – enter the column letter on the AJE Data Source sheet containing the allocation segment (i.e. department, cost center, entity, etc.) to allocate from. A reversing line will be built for this value and an allocation will be built for each item in the AJE Allocations sheet (column A).
3. Cell B5 – Allocation Field (optional) – enter the column letter on the AJE Data Source sheet containing the allocation method (should match a value on the AJE Allocations sheet in the headers, starting with cell B1). This will be used to determine the allocation percentage.
5. On the AJE Mapping sheet, set up a mapping of the columns from the AJE Data Source sheet and how they should flow into the AJE Output sheet. There are three options for how to set data that will be copied to the AJE Output sheet:
1. Data already existing in the AJE Data Source sheet
2. Fixed values (i.e. currency)
3. Formulas that can append data to the AJE Data Source sheet (and then be copied as if the data already existed)
Make sure to set the mapping for both JE line types (the reversal line and the allocation lines) in the two sets of columns. Leverage formulas to reverse the signs or apply other calculations on the values in the AJE Data Source sheet.
Note that for the allocation line type, there must be an amount column (matching cell B3) and an allocation segment column (matching cell B4).

Ongoing Usage Instructions

1. Copy the source data to be included in the allocation journal entry to the AJE Data Source sheet. Remember that data in the AJE Data Source sheet must have header fields in row 1 and data in the rows below.
2. Copy updated allocation values into the AJE Allocations sheet (if necessary). Remember that you can have as many different allocation types as you’d like in columns B and to the right. Include matching allocation types in the AJE Data Source sheet.
3. Run the Create Allocation Journal Entry Lines macro again once the AJE Data Source sheet has been populated, the AJE Allocations sheet has been updated (if necessary), and the AJE Mapping sheet has been updated (if applicable). This will create JE lines on the AJE Output sheet by leveraging the AJE Data Source sheet, the AJE Allocations sheet, and the AJE Mapping sheet to copy the data to the right place, apply fixed values, or apply formulas, using the desired allocation method.  The AJE 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.