Streamlining and Standardizing Journal Entries

Most accountants spend a lot of time on journal entries, especially during close time.  Based on my experience and from hundreds of people I’ve consulted or worked with over the years, an average journal entry takes about 30 minutes to prepare and post from start to finish (clearly some take much longer than others).  They are also the source of a lot of rework (booked backwards, mis-keys, mis-codings, preliminary amounts, etc).  With accountants being asked to do more than ever, this is an appealing thing to streamline.  In this brief article, we’ll discuss a simple Excel template that can automate and standardize many of the steps in the journal entry preparation process.

Long-term, the best way to automate and control the journal entry process is with a purpose-built solution like BlackLine.  In the meantime, the template below can still help quite a bit by letting you set up the logic behind your repetitive journal entries once, and leverage it each time you need to use it.  This frees up time to focus on analysis, review, and exceptions, or anything else you need to tackle.

There are two files referenced in this article:

• JE Automation Template (housed in the Support | Example files section) – this is the template you’ll want to build your journal entries in.
• JE Automation Template Example – this is the same template but with example source data and settings filled in.

The template has detailed instructions included so I’ll just summarize here.

Initial steps:

1. Identify the source data for your JE. It could be a GL detail, a subledger report, a third-party report, or even something you build out yourself.
2. Map the fields in your source data to where they should go in the JE – for one or two lines. I recommend two lines so that you can easily research later.  There are three types of mappings:
   1. • Linked from the data source field to the JE field
      • Formulas – these are appended to the data source and are then mapped to the JE field
      • Fixed values – these are hard-coded to the JE field
3. For any formulas that leverage lookups, build the lookup tables in separate sheets and reference them with the formulas.

Ongoing steps:

1. Download the source data with current period parameters.
2. Copy the source data to the source data sheet.
3. Update any lookup tables, if applicable.
4. Run the macro to build the journal entry.

Tips for success:

• Start with the most repetitive JEs and/or the ones with the most manipulations/formulas to manage.
• Build checks in for data quality (i.e. lookups returning errors)
• Compare the current period JE to the prior period (in detail or summarized like in PivotTables) as a reasonableness check
• If using the XLEV8 add-in, consider streamlining other steps involving your web browser like downloading the source data and importing the JE using the Run Browser Steps macro.

As you are looking at the contents of your journal entries or even your chart of accounts, consider reading through this related article on creative GL coding that can help you dramatically on the back-end when analyzing and reporting on all the journal entries you’ve booked!