Monthly Archives: March 2014

CFO’s Big Picture – Can you Leverage Available Data and Technology?

How the CFO role evolved and is more dependent on information technology

The CFO’s office has changed considerably over the years.  While the title still stands for “Chief Financial Officer”, many of the duties of a typical modern day CFO are not financial in nature. These range from legal and compliance, to risk management, to overseeing the IT and HR functions and other responsibilities.

Continue reading CFO’s Big Picture – Can you Leverage Available Data and Technology?

Are You Still Consolidating Financial Statements in a Spreadsheet?

Why consolidating financial statements in a spreadsheet is a very bad idea

It seems that ever since there was a spreadsheet people have been relinquishing many tasks and responsibilities to it and for apparently good reasons.  It was easy to set up and perform all but the most complex functions and it was available everywhere.

Admittedly, an application such as Microsoft Excel which took the business world by storm (have you seen a non Excel spreadsheet used lately?) is a lot more powerful than its earlier versions.  While packing an impressive collection of features suitable to anything from making simple lists and flat file databases, to adding up columns of numbers, to more complex analysis tasks using functions and VB programming and incorporating its incredible graphing and display capabilities, it also inherently presents risks which become increasingly greater as the complexity of the model increases.

It is commonplace in business today that most of these spreadsheets (workbooks in Excel, containing from several to many individual worksheets, or tabs, often linked to other Excel files across entire computer networks) are authored by individuals, who while having the necessary skills to design and implement them, almost never think of reviewing them for accuracy and completeness of formulas, functions, links and any programming code.

The few spreadsheets that are looked at are usually reviewed by their own authors and are almost never subject to a peer review, or by someone completely independent.  From an internal control perspective, this practice is a bad idea and causes so many spreadsheets to be flawed and without proper risk mitigating controls.

In an article by Tony Kontzer in Tech Target, titled “Even with spreadsheet management, using Excel for finance isn’t wise”, and quoting additional authors and researchers, the author makes a point of the typical “ungoverned” use of spreadsheets (Excel) and how ingrained it is in the daily work of accounting and finance professionals.

Even with the new MS Excel 2013 with it’s better than ever built-in audit tools, it is ultimately the end-users who must establish an effective control environment that can be practically maintained and audited.  This rarely happens in the real world, leaving numerous critical financial statements at risk for material errors.

In a research paper published by Dr. Ray Panko, of the University of Hawai’i
College of Business Administration,

(http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm)

and a follow up paper  (http://arxiv.org/ftp/arxiv/papers/0809/0809.3613.pdf),

it is made clear that a very large percentage of spreadsheets used in business today are flawed.  This should become a major concern for those using spreadsheets in accounting and finance functions.

In my work in internal audit and financial reporting consulting I constantly run into organizations (many of which are fairly large and complex, some publically held) that put too much trust into results produced by an array of homegrown spreadsheets.

This is especially true for spreadsheets entrusted with producing consolidated financial statements and also with the preparation of annual corporate budgets and various forecasts, still very popular with spreadsheets.

I have advised managements on the need to disclose such material weaknesses, and I have witnessed several occasions when the external auditors insisted on making such disclosures.

When testing the design and effectiveness of an internal control performed in a spreadsheet, and especially consolidations of financial statements, unless there is clear evidence that the spreadsheet (or any end-user computing activity) is periodically reviewed for accuracy and completeness of its design and use, and is under change management control, you can’t help but conclude that there may be a material weakness in this process, one that must be disclosed by publically held companies in their annual reports.

What this implies is that (for any type of organization) financial statements produced by spreadsheets are likely to contain material errors.  For that reason, purpose designed consolidation tools are much more preferable to using spreadsheets.  Many of the small and medium size company ERP software applications offer consolidations within the application database without exporting data to a consolidation spreadsheet.  Once internal control over the process is established within the database, it is much simpler to monitor and audit than with spreadsheets.

If your organization still relies on financial statements consolidated using a spreadsheet, I strongly suggest you take another close look at the process and either introduce a well documented and periodically tested set of internal controls over the design, use and change management of this process, or better still, perform it in a purpose built application or more practically for small and medium size organization, implement the process within your accounting or ERP software, in a more secure environment.

Not taking one of the above steps is leaving too much to chance, which invariably, sooner or later, will result in material misstatements in critical financial statements.

As seen on Centage’s Budgeting and Forecasting Experts Blog

Two Key Principles in the Budget and Forecast Process

Observe these principles and you will help steer your organization in the right direction

It was a great revelation when I realized that there are just two key principles in the budgeting & forecasting process, regardless of the size of the organization:

1)      Budgeting and periodic re-forecasting of the entire chart of accounts and not just the income statement (P&L).

2)      Periodically and timely analyzing actual financial performance against the budget and understanding the data so it can be effectively conveyed to management for informed and confident decision making.

These two key principles, when properly observed, can make the difference between business success and business failure.  This is especially critical for small and medium size organizations where cash flow and other balance sheet accounts are often impossible to forecast while periodic and timely analysis is seldom or inconsistently done.

Despite these challenges, observing these two principals is extremely important for these organizations and this is why:

  • If you only forecast your income statement accounts (generally using the accrual method of accounting) you will not have visibility into key balance sheet accounts (e.g., Cash, A/R, A/P); you will not be able to forecast key financial ratios and you will not have a complete understanding of the future financial health of the company.
  • If you do not periodically, and timely (i.e., right after your actual accounting period is closed) analyze the actual results against the budget, you will not be able to quickly react (or not at all) to significant variances of actual results versus expected results, your budget.  Your organization may be headed in a wrong (and often dangerous) direction and since management may not be aware of that, no corrective action will take place, often further causing the situation to deteriorate.  Exceeding budget expectation in one or more areas may not be noticed and focus may not be placed on these areas.

In order for you to consistently and accurately observe these two principles, you need to have the right tools and the commitment to properly use them.  From my experience, using a popular tool such as a spreadsheet (e.g., MS-Excel) is not going to address these challenges, especially in forecasting a reliable balance sheet, achieving error free results and performing a meaningful analysis.  You will need a more dedicated software solution that is designed specifically to perform these tasks.

If you have never forecasted a balance sheet because it was time consuming, very complicated and error prone, please realize that most organizations don’t do that for these very reasons.  Would they forecast their balance sheet if they had the right tools and at the right cost?  Of course they would.  The benefits are just too great not to do it.

One software solution that comes to mind, offering these great benefits, especially for small and medium size organizations, is Budget Maestro from Centage Corporation (www.centage.com).   It was designed and programmed to address these two principles.

Fulfilling Principle #1: With Budget Maestro you have the ability to forecast all of your G/L accounts.  This works by having each budget line (revenue, expense, etc.) linked to a G/L account in the Budget Maestro application.  The software then, based on your assumptions and other settings, will post all necessary amounts to all affected accounts, including all participating balance sheet accounts.

The forecasted balance sheet (and the derived forecasted Statement of Cash Flows) will reflect accurate balances, only relying on your data input and assumptions without any user provided formulas and links – everything is automatically done in the software.  Read my blog entry “Generate Accurate Forecasted Financial Statements as your Budget”  for more detail on how this really works.  For those of you who have built complex financial models using spreadsheets, there is absolutely no programing of formulas, functions and links in Budget Maestro, so fulfilling Principle #1 becomes a reality, even for a small organization with limited resources.

Fulfilling Principle #2:  Budget Maestro is designed to allow you to quickly and effortlessly extract data from your actual accounting G/L at each period end.  This is available for many of the more popular accounting and ERP software, and in those cases where there is no direct link, Budget Maestro easily allows the importing of trial balance numbers at each period end.

As soon as you have the actual data, you can see where you are, right after an accounting period is closed, and with no additional delays, intelligent and informed decisions can be made.  For the ultimate data analysis experience, Centage Corporation offers their Analytics Maestro software module, which seamlessly connects to Budget Maestro data to give you the ultimate analysis tool where you will truly understand your data, through tabular and graphic representation customized by you to precisely suit your needs.  Perform the analysis periodically, and you will have fulfilled Principle #2.

Regardless of what tools you use, you must make an effort to observe these key principles and from my experience in many industries, there are no shortcuts here.  Harnessing the power and intelligence of Budget Maestro with its companion product Analytics Maestro, you will obey these two principles and help navigate your business through each budget period.

Doing anything less is too much of a compromise and can never mitigate financial risks inherent in every business enterprise.

As seen on Centage’s Budgeting and Forecasting Experts Blog