Create

From Paper Ledgers to Spreadsheets to Data Heroes

October 31, 2022 — by Ali Igram

How an Automated Workflow Resolves a Scary Data Story

The automation and digital transformation we read about in magazines and trade journals doesn't seem to reflect the reality on the ground for many accounting teams. It's 2022, but I guarantee you, that for all the accountants and departments that help in the close – from accounts payable, to accounts receivable, to treasury, finance, sales and operations – month-end is still as much of a data horror story as it was in 1999. Why is that?

The problems we have transitioning from spreadsheets to automated workflows are the same we had transitioning from paper ledgers to spreadsheets. It all feels a bit scary.

If we are to become heroes of the close, we need to have a diverse selection of modern tools in our toolbelt so that we can apply the best possible technology to the scenario.

Imagine if Batman only had one tool and then didn't have it when he needed it. If he worked with data, I'm pretty sure he would use KNIME, Python, Excel, Power BI, and more. And we should too!

In this article, I'd like to show how I used KNIME to optimize one of the most time-consuming month-end processes: journal entry.

Lower the Barrier to Workflow Development

In order to finally gain control of month-end, we need to move beyond the traditional ways of working that have defined the past 30 years of practice since spreadsheets took effect. We need to blur the lines between accounting and development, and learn new skills.

No-code/low-code data science tools are key to lowering the barrier to workflow development.

But how does an accountant become a developer? Do we need to go back to school to get a computer science degree? Many data analytics tools involve learning code to use them. And yet for many accountants, writing code is too much of a leap.

No-code/low-code workflow automation tools open the door to people without programming skills to use new advanced techniques. Many of us find that the concepts learned from years of spreadsheet use translate nicely into this new world.

End the Horror of Journal Entry

Preparing manual journal entries during month-end is one of the most manually intensive and risky activities in accounting. Mistakes can lead to serious material misrepresentations in a company’s financial statements. The traditional manual journal entry process is inefficient and error prone. Automating journal entries would therefore seem to be a natural step to avoid serious errors and save time.

However, automating the process is complex as there is no “one size fits all”; journal entries are very company specific. Each company has their own unique data sources, rules and processing steps. Final submission is also specific to the company’s Enterprise Resource Planning (ERP) system, with each ERP using their own unique upload templates. The SAP z-options upload for example, includes all header details at the top followed by all the lines of entry.

The accountant who prepares journal entry understands the process better than any other and is therefore in the best position to develop an automated solution.

Build a KNIME Workflow to Automate Journal Entry

A KNIME workflow can take data from multiple sources and transform that data until the journal entry meets your accounting standards.

All of the pieces are provided in the form of pre-coded KNIME nodes, it’s up to us to put the puzzle together. Each node performs a task to filter and rename columns, perform a pivot function or math formula, join or append columns.

Step by step I can build my workflow to insert journal entries into my company’s ERP journal entry upload templates.

This methodology can be followed to automate a variety of different journal entry types, whether it is to create the accounts receivable reserve, to record accruals and depreciation, to amortize prepaids, and more. 

We can engineer a workflow to prepare any month end entry, which can be executed month after month to produce the same results. 

Build a KNIME Workflow to Enter Accounts Receivable Reserve

Take the accounts receivable reserve entry for example, which could be handled with the following steps in KNIME. Once you have set up the workflow, it can be re-run time and time again. Whether it's for the next month-end or for a late entry.

  1. Table creator nodes to set up the JE template

  2. CSV reader to read Accounts Receivable aging data

  3. Rule engine node to calculate the aging amount 

  4. Group by to summarize the entry

  5. Table row as variable + rule engine node to populate the template with the entry

  6. Excel writer to write the output to an excel upload template

You can see these steps reflected in my workflow, below.

From paper ledgers to spreadsheets to a KNIME workflow to automate journal entry
From paper ledgers to spreadsheets to a KNIME workflow to automate journal entry. (Click the image to enlarge.)

75% Reduction in Processing Time

Another huge plus of low-code development is the speed of solutioning. In many cases, we can develop and test workflows for many business processes in days.This is because we are already given the building blocks, we just need to connect them together. This is the magic of visual programming.

I recently teamed up with a colleague on the accounting team, and together we were able to complete an accrual journal entry automation project in KNIME within just a few days of development, leading to a satisfying “quick-win” that resulted in a 75% reduction in processing time and lower risk due to fewer manual touchpoints.

Take Control of the Close

To summarize, low-code development in KNIME provides accountants with the ability to program their own workflows, which can be executed month after month or as needed, if the numbers change unexpectedly. In many cases, you will see a reductions in journal entry processing times of 50-100%

By redefining our roles, learning new skills, and putting that knowledge into practice we can counter the trend of severe talent shortages and finally gain control of the close.

Ali is a Certified Public Accountant who is passionate about data science. He develops solutions to automate manual accounting processes and solve typical data problems that slow down accounting and finance teams, such as dependence on spreadsheets, the inability to handle big data, and an over-reliance on manual reviews. He uses KNIME, Power BI, Power Automate, Power Apps, Python, and SQL in the solutions he develops.

You can connect with him on LinkedIn, where he frequently writes about using data science in his work as a CPA.

You Might Also Like

What are you looking for?