Period End Closes and Utilizing Your FP&A and IT for Efficiencies

Some Top Issues Facing Closes
               Over-Arching:: Manual Processes and Data
 

• Account Reconciliations
• Real-Time Data Visibility
• Inconsistent Processes and Lack of Standardizations (SOPs)
• Incomplete Data or Errors in Data

​First: Integrable Capabilities of Systems and Applications
                              --Manual Processes and Errors
 
Not every company has fully integrated ERP systems such as SAP, Oracle Fusion, Oracle NetSuite, MS Dynamics 365, Acumatica or some equivalent.  Some of these systems offer a full suite of integrable packages and modules covering all areas of business operations, while other smaller ERPs offer modules for a few functional areas and are expected to integrate with other applications.   
 
An example of a full suite, Oracle NetSuite, has it’s core finance and accounting suite, NetSuite Financial Management, but it also has modules for CRM, Inventory & Order Management, Supply Chain Management, Commerce & Ecommerce, HR & Project Management. 
 
Of course, all the big ones have a similar setup.  Not playing favorites, all systems have good points and bad points. 
 
It is quite common to see businesses attempt an amalgamated setup of operational applications, often with a partial ERP package and several other applications.  A recent setup I saw had an ERP system that acted as the primary Accounting, CRM and POS tool and they used separate applications for SCM, Logistics, Fixed Assets, Payroll, HR, Tax and so on.  This wouldn’t have to be an issue, but the reason I said ‘attempt an amalgamated’ setup, is that the integrable capabilities between applications is one key to smoother financial closes that are often not utilized. 

In the recent setup, for example, there was no bank feed integration being used within the ERP system.  The staff ERP specialist insisted that there was no integration available until I began questioning it and initiated a process with the bank to develop an API between the ERP and their system.  As it turns out, there was a native bank feed integration (as almost all possess) that had been shut down and overlooked because no one questioned how best to leverage the ERP and any integrable capabilities.  Can you imagine cash reconciliations without the bank feed integration? 
 
Leverage your systems capabilities.  The ERP system I mentioned from the example had numerous integrable, partner applications to assist in automating processes.  The next I initiated was an AR/AP natively integrable application with AI capabilities.  The AP team, specifically, was still receiving email invoices and processing payments by hand.  The AP application had 2-way and 3-way reconciliations and the ability to filter emails and learn to read invoices.  This not only reduces manual time, but when a system can input entries across applications, say from an AP vendor account to the ledgers and the bank statement, you reduce manual entry error.
 
With a simple setup for two integrations, both native, days were shaved off a very manual closing process. 
 
Yes. Obvious.  But, how many actually understand their systems, capabilities, or APIs and data connections?
 
It isn’t too complicated of a topic if we just stick to what they do.     
 
Most applications will list other applications for which they have native integrations.  Native integrations are pre-built APIs so you simply need to select the option to connect applications and log-in.  Okay … so what are APIs? 
 
Well, from layman speak…
 
Application integrations are conducted through APIs (Application Programming Interface) written specifically for two applications to communicate information.  The connection can be one-way, two-way, read only, or read and write.  (Check with your IT)  If you have ever migrated data from an application to a database or data warehouse you will be familiar with API usage.  In lieu of an API, data transference in many processes is ‘manual’, perhaps keyed or CSV uploads.  Plenty of opportunities for errors and lost time.
 
You will notice that a feature of the large ERP systems and modules covering the functional areas of operations is that they are integrable, they communicate information amongst various modules.  Without this capability, you would just have several disparate systems that require CSV uploads, properly formatted, to exchange data.  Their entire selling point of reducing period close times and errors is greatly diminished without the integrable functions.     
 
Below is a Mapping I had drawn up at a high-level for an ecommerce platform, although, I believe Square is now Block.
 
Mappings of processes are useful.  Look at how a mapping visual makes a complicated looking setup understandable.  I know most understand this; it is my way of supporting, in in my foundational strategy talk, my focus on the use of mappings for a strategy tool and training tool.  
​

​I have no affiliation with any of these companies.  I was sketching out integrable capabilities for a company and mapping manual steps where integrations might be targeted.  You will find that not all companies have native integrations for every other company and a buffer, such as Zapier, might be able to bridge that gap.  Zapier isn’t the only company that offers this service, but it is often mentioned in any research done on system applications and integrations.
 
You may note that this specific example does not include a payment processor for AR such as Stripe.  FreshBooks Payment is built on Stripe and FreshBooks can integrate with Google Workspace for invoicing.  If you have used QuickBooks, then this might resemble the invoicing functions from its invoicing and payments capabilities.  To make a point, this is not a unique setup.  It is highly likely your systems have capabilities that are either underutilized or not being utilized that could minimize manual processes, manual errors, and improve closing.

​Second:  Bookkeeping as a Relational Database
                              --Real-Time Visualization
 
 
 
I know … this is basic accounting but stay with me for a moment.
 
In today’s finance and accounting departments it is a rarity to find employees that have an understanding of the process of recording transactions using the old school bookkeeping methods.  Yes, I mean the paper journal and associated ledgers.  Certainly, some accounting courses still teach students to use the paper method through semester projects, but many just use images in textbooks.
 
Recall you keep a chronological tracking of all transactions and then post these to their appropriate ledger for ease of balancing and reconciliations later.  You see the exact same setup in your accounting software with transaction lists and the various ledgers.    
 
 
The purpose ? - A paper relational database.
 
Again, bookkeeping entries also included cross-references from journal number and page number to ledger number and page number so that entries could be easily tracked when reconciliations were needed at period close. 
 
The double-accounting system was essentially a relational database, but rather than assigning entries a unique primary key-foreign key relationship, the journal number or ledger and page number for transactions were listed with descriptions. The descriptions would include sales numbers, Invoice numbers, pr approvals, po numbers, billing numbers, and any identifiers related to a transaction entry.  What's more, the paper receipts had to be organized and stored for ‘easy’ referencing for both reconciliations and audits.  Hence ... boxes.
 
Just because an accounting entry isn’t triggered, it doesn’t mean that you shouldn’t be able to trace the entry's source for an audit.  For example, when the AP accounting entry is triggered on receipt of an order, you should still be able to trace the PO and PR to that order. This is why boxes were often catalogued with the entries in addition to the journals, ledgers and pages.  
IF A TRIAL BALANCE was off, the ledger entries had to have a reference to the various items for reconciliation.  For example, if AP was off, the AP ledger would have entries for the PO, Billing, and Receipt numbers and may even include the Payment number, which in the old days would be the check number.  While all these numbers occur at different times, the use of Subledgers for specific vendors would make it easier to find these entries and identify what process entry was incorrect.  (Yes, I have had to track paper receipts for different situations.)     
   
Now imagine ... your identifiers such as PO, Billing, and Receipt numbers created by your system as you upload and store documents, automatically create transaction entries in the accounting system and operational systems and it processes unique primary and foreign keys to identify every line, for every relationship when you need to complete an audit or period close.  This is, of course, what happens.
 
example:

Drawing from a large system and sticking with our AP process:
 
In the SAP system and referencing the data dictionary.  SAP tables
 

• RSEG : fields (BELNR : Invoice Number, EBELN :: PO Number)
• BSEG : links accounting (invoices) to purchase orders through key AWKEY
• MSEG : links material documents (receipt) to purchase orders through EBELN

 
The relational setup allows you to join tables and identify the missing or mismatched values.  

Almost all applications and systems run a relational database and create tables for storing data. All systems do so in a similar fashion regardless of whether it is Microsoft, Oracle, NetSuite, or some other set of applications.  The heart of the matter is that you can use the database structures and concepts to create visuals for matching transactions and reconciliations in the same way you would use the database to create a dashboard of financials even if you don't have integrable capabilities.
 
This is where your FP&A team could be utilized for maximum benefit by having them use their data ETL (Extract-Transform-Load) skills and dashboard skills to create reconciliation tables.
 
Three major areas of the closing process are Cash, AR, and AP reconciliations.
 
If you have connected a bank feed into your accounting system, then you have seen how an integration helps to identify matching and unmatched or mismatched entries.  The bank feed gives you a list of transactions that have cleared and a list of transactions that are pending, but in the system.  These transactions are matched against those recorded in your Cash Account.  The comparison between the two helps you identify any transactions that have no match.  And we all know the manual process could be done by downloading the transactions from the bank accounts and reconciling against the Cash Accounts. 
 
But, again, consider that these are just two tables being compared.  For all intents, these could be tables in a relational database.  All you need is the ability to connect, extract, and visualize these comparisons, on a daily basis, in a BI tool. 
 
This process would improve visibility of unmatched transactions from AR, AP, and Cash, using a tool such as Power BI, Excel, Tableau or the like and the OData (Open Data Protocol) protocol or ODBC (Online Database Connector) connection.  This is the exact method your analysts would use to retrieve data from your warehouse or other applications currently used for dashboards, models, and reporting.

---

​            - I should begin by saying that, ideally, you should create a backup of all application data by migrating all of it into a database, data warehouse, or data lake.  You would then connect to the database to perform these visualizations.  It is not best practice to connect directly to an application or an ERP system, although many offer this capability.  Also, migrating your data to a data center creates at least one back-up for your data at a centralized location, but an offsite server backup would meet a risk management procedure for data protection.  -    

---

​Just to provide a quick example search answer to the question to provide support for my suggestion, Does NetSuite have an OData feed?

​I think you will find this is very common for applications.  You just need to retrieve your tables.  In my use case I might choose Power BI and Power Query Editor for ETL actions.

Let’s look at tables we might gather to track AP and the portion of Cash specific to disbursements on payable.  We see three different systems or modules, Bank Feed/Statement, Accounting (Recording), Procure-to-Pay Module. 
 
It is a near certainty that your bank will connect to your Accounting Platform and, due to the competitive nature of accounting offerings, your Accounting Platform will almost certainly integrate with your Procurement Platform, for example QuickBooks and Bill.  Also, since Power BI is a major BI tool, API and integrations are possible into these systems to query information.
 
Each system will present this information with different column headers and different naming conventions, but remember that, due to the requirement to relate transactions for analysis and rollups, you should be able to find the table Keys (Primary, Foreign) on which to perform JOINS (SQL language).  If your system does not have keys, you can use the supplier invoice numbers, billing numbers, and other unique identifiers within the reference and descriptions of transactions (just like the old bookkeeping system).
In addition, if you need some planning assistance, any system will have a data dictionary for their database and tables that describes the column, input type, allowable characters, related tables, formulas and so on for a reference.
 
For my example, I am going to create these tables in Excel, shown below and use the both the ID's that your system might produce and the unique Supplier Invoice number.  Your system might have more detail, but the key columns and dollars are really the focus.  The ledgers are going to begin from the assumption that you have filtered for relevant context as the data is imported into Power BI.  For example, all GL and AP will be for inventoriable Trade Payables to align with the POs, Supplier Invoices, and Good Receipts tables. 
 
 
Bank Statement
 
               *I am not going to create a Bank Statement from scratch. I believe most will have reconciled Bank/Cash and could utilize it in their BI tool for reconciliation in the same manner as will be done for ledgers and PO, Supplier Invoices, and Goods Receipts below.
 
               *As you become familiar with the abbreviations used by the Banking Institution for your Vendors and Customers you may want to store them as an added identifier for simplified Cash/Bank Reconciliation.
 
               *Note that if you begin reconciling AR and AP from Order to Pay, Procure to Pay that you will have a good portion of your Cash Reconciliation completed.  You then can proceed to Expense and Bank statement items to wrap it up.  (Through planning to prevent duplications of steps can also help to move closes along.)  

​
General Ledger

​
AP Ledger

Inventory Ledger

​*In addition to the Inventory Ledger we will have a GRNI (Goods Received, Not Invoiced) account and assume FOB Destination so that any paid invoices that have not had a Goods Receipt will be entered into Prepaid Inventory.

​Op Cash Ledger

Purchase Order  

Status:

• open                                  active; needs either invoice, goods receipt, or approvals
• pending                            waiting on both invoice and goods receipt
• due payment                 invoice approved, goods received, due payment
• closed                               received goods and paid
• partial                               partial goods received, partial goods sent

Supplier Invoice

Status:

• pending                            pending verification of goods
• approved                         approved for payment
• paid                                    payment completed
• partial payment           partial acceptance

Goods Receipt

Status:

• accepted                         received
• awaiting invoice           received, accepted, awaiting invoice GRNI
• pending                            pending inspection
• partial                               partial acceptance

​Please keep in mind, I quickly constructed these sample tables for a sample to spur some thoughts and considerations for use in your systems.  I may have missed some details.  For example, if your PO includes line-item numbers for purchase orders with a list of items, then you would have to accommodate this in your analysis.  I don’t believe that any details that I may have missed would hinder an analysis to the point that this method could not be applied to your data.
 
Hopefully, you can see where this is going an how we can use this to track inventory quantities and the accounting ledgers as well.  I do want to remind you that this matching and structuring could be done in SQL, the Power Query Editor, or from the calculated columns or DAX measures to arrive at the same visuals.
 
 
 
The Top Cover could look like the following:

​Of course, I am not doing a full design workup.  This is simply illustrating the relationship between the PO’s, Supplier Invoicing, and Goods Receipts with the associated Accounting Ledgers.
 
Quickly walking through the boxes.  The Green PO’s that are Closed or Partials should have the associated Goods Receipt, in addition to the PO’s that are Due Payment and hence should all be entries in the AP Ledger as Recorded Credits, follow the green chevron.
From the AP Ledger, it will equal the Inventory Ledger, Gold, sans the GRNI accrual, Light Blue, since the GRNI won’t move to the AP until the Goods Receipt is received.
 
Across the Blue row, the Supplier Invoice, comprised of Paid, Partial, or Approved invoices will equal the AP Ledger, Prepaid Inventory (for any invoices paid before receiving a Goods Receipts), and the invoices that have been approved, but have not been paid or received the Goods Receipt (recall FOB Destination).  This reconciliation could easily be extended into the details of the Vendor specific AP ledgers as required with slicers.
 
In theory, any given time that you wanted to conduct a close, your various entries should balance and, by the same reasoning, as your operations progress a tracking system should balance or quickly alert someone to an issue.
 
You can also set up similar tools that you might find in your procurement system, for example searching for status and 3-Way matching on POs.  Granted, those that have access to the system would have this capability, but not necessarily those that sent the Purchase Request.

​Or perhaps you want a look at the partials, those orders that were partially ordered, or partially sent so that you could check on a credit or inventory items.

​Must like the previous Power BI article that I wrote, we could create detailed links to the Item ordered.  Perhaps we have an expected replacement date, or we have a critical inventory item for an assembly process, and while these are a bit outside the range of accounting reconciliation, an expected credit memo is in line with our process.
 
 
 
Integrations and Mimicking Bookkeeping with Operational Accounting Triggers
 
 
 
This is exactly what your ERP system or aggregation of systems is supposed to do for your record keeping.  But, if you note the relationship between the AP (procurement) system and the ledgers, we are doing a similar matching and tallying of transactions to run checks between the operations and accounting.
 
As pointed out earlier, your data is generally stored in a relational database, every transaction needs to have primary keys and foreign keys so that the ERP system can perform the same relationships that we can create in the Power BI report.  But not every company has an enterprise grade ERP system with full integration for all areas of supply chain, inventory, sales, leases, asset management, tax, accounting and so on.  Many companies will have several applications and manual processes. 
 
I am suggesting that you can utilize your access to the data in the applications through an API, or other connection, and a tool, such as Power BI, to create similar visualizations and reconciliation assistance.  Map your accounting process relative to your controls, gather the data and begin your process.  This is not necessarily easy given that you may have more status levels or item lines, but it could be worthwhile if you use a BI tool and do not have a fully integrated ERP system.  If so, then your FP&A team already has the tools it needs to begin to offer actual assistance to the process in a manner that utilizes their skills with data and applications.
 
 
 
Third:  Data at its Source and Clean-Up
 
 
A familiar saying that is most often associated with model construction … Garbage-In, Garbage-Out (GIGO).  While in a model construction this reminds you to check data, sources, recordings, and assumptions, this same saying can be used when it comes to period end close and reconciliations.  Only GIGO for reconciliations can mean tracking down input errors at the source, for example, the POS system.
 
I will cite a real world example of both vendor and customers input into the POS system, linked to an ERP system at the store front.  I know others have seen this very example.  You are going to create your schedules for AR and AP.  You are going to implement the capability to select a breakdown by vendor or customer so that you know who to target for further analysis, however, you discover that several vendors and customers have multiple entries in the system due to manually keyed entries.
 
Use the list below as an extreme example.

Close Up

​As you can see, this listing of companies could easily be a list of names for the same company.  While in the real world, duplicate entries for a single company usually only number two or three different entries, I had this set available from a clean-up exercise I use for skill building. 
 
This situation is not uncommon for brick-and-mortar companies.  A salesclerk takes an order and fails to conduct a thorough search in the system for a customer or is given a name for the company that is a ‘known’ name.  For example, Alpha Forge Manufacturing vs Alpha Cast.  The clerk may be new or busy but knows that they need to enter the order and then proceeds to create a new ID so this order can get processed.  This data is then migrated into the database and often only seen when it gets to the analyst trying to build a model. 
 
The analyst is simply expected to correct the data in an editor or in a spreadsheet so that the correct aggregations are shown.  This activity might be so common that the analyst keeps a list of all the alternative identifiers for companies so that a quick mapping can be performed for any reports or visualizations (Yes, this happens).  No one bothers to attempt a correction at the source … a standard procedure and training to prevent garbage data from going into the system at the source.  It is just expected the analyst will clean it up on the end of the data cycle.  While this isn’t necessarily a huge issue when creating models and AR/AP schedules, using this poor POS procedure and resulting data for reconciliations can be timely and therefore costly to a closing process.   
 
Because all processes naturally create data, it is the analysts that could be your best source of process issues that impact your capabilities and benefit potential within information technology.
 
 
Closing
 
 
 
The key to creating better processes is to get into the process. Map it.  Time it. 
 
Record each application used and each manual need, input-to-output, along the steps to completion. 
 
Take note of the manual processes and the bottleneck impacting the throughput.
 
The only way to remedy a slow process is to increase the throughput at the bottleneck.
 
When you have a good grasp of the process mark the accounting triggers and the accounts affected.  As you begin reconciliations don’t double up, partition; for example, a reconciled AR, AP and Payroll, can be used alongside the Cash Ledger to clear all relevant cash activity entries.  This leaves the rest of the GL cash activity.  And, of course, the totals for Cash should match the Bank statement with bank entry adjustments. 
 
Oversimplified … but hopefully it illustrates the partition.