Summary
When you hear the words “creative” and “accountant” in the same sentence, you probably either laugh or think of accounting scandals from the past 20 years. Creative accountants find easier ways to do things, reducing time and errors. In this article, we’ll discuss how accountants (and others that support them) can creatively code transactions, as well as set up the chart of accounts creatively. These are the key takeaways around GL transaction coding that we’ll dive into:
- Consistency is key
- Details matter
- Leverage cheatsheets
- Separate out different transaction types
Consistency is Key
One of the first things I learned in accounting classes was how important consistency (or comparability) is – sometimes even more important than accuracy. Here, I’m referring to the consistency of both the account coding, as well as the description or other accompanying details. Why is this important? When things are recorded consistently:
- Performance can be accurately assessed
- Transactions are easier to find
- Logic and formulas can be built around them
- Exceptions can be identified and resolved quicker
So how do you record account coding and related details consistently?
Leverage a template
Set up a template – write down what you do the first time and use this over and over again. Include instructions – these are great for others but also for your future self! Be careful not to just copy and paste – that’s a good way to make an error for something that should have changed since the last time. See the next tips for an even better way than just copy and paste.
Use formulas in journal entries that minimize manual inputs
You’ve probably noticed that a large portion of journal entries are very repetitive – the same logic and even the same values are used month after month or more. Use this to your advantage! Don’t just hand key JEs every time. That takes a while and it’s too easy to make errors. Instead, use formulas for all the fields in your journal entries. Link them to inputs like dates, locations, categories, etc. Your JE is likely based on some source data, so separate that so it can be copied over and over, then reference it with formulas like IF, SUMIFS, and VLOOKUP. Then build in some completeness and accuracy checks. I take this approach with everything in Excel that I can – building reports, journal entries, account reconciliations, etc.
Leverage lookups to categorize things
Using lookup formulas like VLOOKUP, HLOOKUP, and the newer XLOOKUP are helpful for a lot of things, but categorizing/mapping is a great one. Examples are categorizing by vendor, transaction type (accrual vs. reversal vs. payment, frequency, etc. This is also a good way to fix typos. Here’s how to use them:
- Keep a separate lookup sheet containing at least two columns – the value that should be looked up (from your source data) and the value that should be returned (to your formula-based journal entry).
- Use a lookup in your formula-based journal entry. When those formulas return an #N/A error, copy the value to look up to a new line in the lookup sheet, and fill in the return value. The next time that value is looked up, it won’t have an error!
Map as many details as possible from source systems for automatic JEs
Many journal entries come from source systems or subledgers – payroll, order management, inventory, point-of-sale, etc. Those systems spit out an entry that is either manually or automatically uploaded into the GL. Unfortunately, those entries don’t always have a lot of detail (the detail often stays behind in the source system). Look at the mapping or interface between the systems, and map as many fields as possible – location ID, terminal ID, user ID, batch ID, etc. are great examples of data points that get left forgotten but could be useful later on for research, matching things up, or other analysis. If the GL doesn’t have fields available for all of those data points, concatenate them (consistently of course!) within the description.
Details Matter
While consistency is important, there should be enough different details to tell different transactions apart. Think of including the right amount of detail as a gift to your future self, as well as anyone else that might analyze the data that you work with. Below is a screenshot of an example of how I’ve coded transactions in the past. Notice the four pieces of detail:
- Service period dates – helpful for analyzing prepaids/accruals
- Description – just enough words to distinguish between other transactions in the account
- Group/category – a way to organize, categorize, and compare transactions within the account
- Vendor name – another way to compare transactions over time
See in the second screenshot how all the details in the one description field can easily be split out if they are used consistently. You can then reference them, summarize them, filter/sort on them, or otherwise analyze them. This helps to ensure completeness and accuracy, and cuts down on time spent searching!
The link below is to the Excel file containing the screenshots above so you can see how the formulas are splitting apart those descriptions into the separate fields.
Leverage Cheatsheets
I try to leverage cheatsheets whenever I can. What do I mean by cheatsheet? It’s a reference with instructions you commonly apply, available at your fingertips (taped at your desk or bookmarked on your computer) so you don’t waste time looking things up or rethinking them. A great example is how to code transactions based on the vendor, type, or other details.
In accounting, we often work downstream of everyone else. We’re left to deal with the quality of data that other groups touch. A good example is coding accounts payable transactions. Whether you’re doing this manually on paper or through an AP workflow solution (which I highly recommend), you’re depending on other people to code things correctly and consistently so you can then analyze them, accrue based on them, or perform other tasks.
To help others code things correctly, we provided an AP coding cheatsheet for each department at the company – legal, IT, operations, HR, etc. These were living cheatsheets that showed them how to code transactions by vendor and then by type. It gave examples so they could understand how to apply it. After providing these, we had virtually no more coding errors! Everyone was thankful for our efforts. Then when we requested everyone to code transactions in the format above in the screenshots and helped them understand why, and how incredibly helpful it would be, it was easy.
Make sure to keep cheatsheets updated. Things change all the time, so be proactive and collaborate to decide how new transaction types should be coded. Don’t just dictate them, after all – these are their transactions, and don’t just rely on others – make sure you’re on the same page. Share cheatsheets with everyone that needs them. Somewhere on the page (I prefer the footer area), put the location of the living, most updated version of the cheatsheet so everyone knows where to find it.
Separate Out Different Transaction Types
I’m a fan of splitting out transactions to different accounts as much as possible. You already do this with bank accounts by splitting out deposits, payables, payroll, and other activity to different bank accounts, right? This same approach works for GL accounts too. I also think keeping different transaction types in separate transactions (or at least on separate lines) is helpful.
Separating transactions in different accounts
I am a big fan of creatively structuring the chart of accounts – using contra accounts and sub-accounts – so that things are organized well, and within each account – the lowest level at which you can book a JE line – there are as few types of transactions as possible. You might ask why create more accounts than are necessary? There are two main reasons: 1) to provide clarity (seeing the theme yet?) and 2) to make reporting easier. I’ve been able to completely automate the preparation of the cash flow statement by separating out different types of activity.
Let’s look at a simple example I’ve dealt with several times in my career. I’ve often seen prepaid expenses that are paid in installments. The first installment payment is amortized correctly over the full term, but subsequent payments aren’t – they are incorrectly amortized over the remaining term. To help make this easier, we split out one prepaid expense account into three separate accounts:
- Prepaid expense gross account
- Prepaid expense payments contra account
- Prepaid expense amortization
Upon initiation of the prepaid expense (i.e. at contract signing), we booked an entry to debit #1 and credit #2 for the same amount. We then booked amortization expense as a credit to #3 and a debit to whatever expense account should be used. Any payments were recorded to #2 – this separated out the payments from the coding and the amortization. That level of clarity was very helpful for ensuring things worked correctly. All three accounts were used together for reconciliations, and having the activity separate helped there as well. Lastly, by keeping the activity separate, you can easily run “life-to-date” reports of how much was spent/amortized/etc. There are dozens of examples of how to be creative like this.
Separating transaction types
If you’ve ever researched a transaction where someone netted things together in one line, you know what I’m talking about here. Even if the support is attached (which is rare), that’s not as easy to access as the transactions themselves. Why not just separate those into different lines instead of netting them? The classic example is a JE that has the signs recorded backwards. Instead of recording a reversing entry to zero out the first one, people often book double that amount to 1) reverse it and 2) correct it, at the same time. While that seems more efficient, you lose clarity. So while it may seem easier and quicker on the front end to book two lines instead of four, on the back end, just trying to understand what happened is definitely not quicker. Intercompany transactions are another area where I’ve seen way too many lines netted, causing lots of frustration since they are netted across different systems.
When recording that reversal and correction, reference the original transaction so that it’s obvious what they relate to. For the description, copy and paste the original description but add something “RJE ####” and “CJE ####” where RJE means reversing journal entry, CJE means correcting journal entry, and #### is the JE # that they are related to. Doing this consistently builds on the best practices above.
If you have your own GL coding best practices, please share in the comments below!
Recent Comments