Streamline Account Analysis for Month End Close Process

Account analysis can happen at the account, group of accounts, or even across an entire category of accounts. The purpose of an analysis of accounts is to make sure everything in the account is correct and complies with the appropriate accounting principles, be that GAAP (Generally Accepted Accounting Principles) or IFRS (International Financial Reporting Standards) during month end close.

The first challenge with account analysis is that accounts are analyzed differently depending on the account type, which includes asset, liability, equity, income, expense, etc. There’s a risk of accuracy associated with the account, and the account analysis methods used.

Here are a few examples of a few situations that can create the risk of accuracy:

• Some accounts the finance teams reconcile, including the GLTrial balance to Subsidiary Ledger, GL Trial balance to an accounting schedule, report or match transactions between two different systems. The team also validates the transactions during the month that are appropriate for a particular account.
• For some business activities or documents, the finance teams list and accrue expenses against them (purchase orders).
• Some business activities are performed each month partially for verification purposes like physical inventories.
• Some accounts have a very high level of precision than other accounts the finance teams estimate and are “good enough” to apply the “materiality” principle to them (Reserves).

The overriding need for account analysis is the flexibility to analyze the account in the appropriate method, as required by the accounting transactions supporting the account balance. Because of this required flexibility, spreadsheets are the current tool of choice for month end close analysis, but the tool also comes with many challenges.

Here are some of the advantages and disadvantages of spreadsheets

The advantage: Spreadsheets are flexible and can apply the appropriate analysis required for each account, including bringing in a million rows of detail if it is needed. It can sort and filter the information as required to focus on the correct information and then apply formulas to the data. Technically, there are row formulas, column formulas, and cell formulas to analyze the information and create the appropriate journal entries needed to adjust the books at month end.

The disadvantage: Spreadsheets create a disconnected and informal close process, which results in workpapers stored electronically across multiple desktops and servers. There is no “official” monthly close binder.

The need for real-time data is one of the key challenges impacting the monthly close process. To summarize,  the ultimate need is that the data, listings of information, and general business information flow like water through the close process, effortlessly and incrementally.  And the next challenge includes the too many steps involved in the close process.  

These two challenges, combined with the need for analytic flexibility [enabled by spreadsheets], highlight the need for a system that combines the ease of accessing real-time data with a system for managing the process through workflow and collaboration that supports the execution of tasks performed in spreadsheets.  

We call this solution Connected Workspace.  Interestingly, there are a lot of low code/no code solutions in the market, but none manage and formalize informal spreadsheet processes.

Connected Workspaces – the solution provides a listing of monthly close tasks where each task contains the necessary task level information that includes:

With each task you launch a system stored and maintained spreadsheet, which provides the same flexibility as Excel with additional system controls:

• Updating the data input sheets from the real-time data feeds
• Providing the ability to create custom spreadsheet tabs that use the input and perform the necessary vlookup functions and calculations to correctly calculate adjusting journal entries or let the operator choose which entries need to be adjusted. The rows in the spreadsheet are dynamic as the number of input rows changes from month to month.
• Provide the ability to perform analysis like pivot tables, sorting, filtering, etc.
• Provide a summary sheet that calculates the journal entry and can post back to the general ledger. [screenshot of Excel Interface]

The finance teams need to take a few steps to ensure a closed system. These steps included having a monthly close checklist converted to an automated and collaborative system and a catalog of the different spreadsheets used in the financial close process.  Here we will are some of the other crucial steps, which include taking the listings of spreadsheets and:

• Analyze: the sources of the data and bring that data into the financial close system.
• Build: The close spreadsheets in the system. The first series of tabs are input tabs, you can’t edit the data, but you can connect to the data in the same manner you connect to a tab of *.csv data, except the connection is dynamic. Once the data is connected, building out the spreadsheets is a four-step process:
• Copy/paste the tabs from the spreadsheet to the connected workspace tabs
• Link the dynamic data to the input tabs
• Do a “show formulas” in the spreadsheet and copy those formulas into the appropriate connected workspace template
• Save the template

You now have system-maintained spreadsheets attached to both data and workflow under security control.

• Review: At this point, you may want to take a step back and look at the closing task and decide, in the spirit of account analysis. Is there more data you want to bring in, in case things don’t balance? The user has the necessary detailed information available in the same workbook if they want to do additional analysis because account analysis is a very flexible process. What you are trying to do is anticipate if there is a problem, and what additional information would be needed to solve it for this account or account grouping, thus providing all the information necessary in one place to resolve it.
• Summarize: As you convert the spreadsheets, also make sure you have a summary sheet that calculates the journal entry to be sent back to the General Ledger.