Monthly Archives: February 2016

Use of CPM Analytical Tools in the Budget Process

What really matters in the budget preparation and analysis process and why it should be implemented

I was just reading an article on TechTarget.com by Barry Wilderman of Wilderman Associates, titled: The Benefits of using CPM (Corporate Performance Management) analytical tools for budgeting. Here the author walks the readers through the benefits of using CPM data and specific analytical tools throughout the budget preparation process and during the entire budget period where the budget data is compared to the actual performance of the company as communicated by its accounting system reporting data.

The author also emphasizes the importance of using a central database where all of the organization’s business entities (divisions, departments, revenue centers, cost centers, etc.) have their individual budgets which consolidate to the corporate budget but with the ability to process each individual budget separately, arrive at individual approvals for the entity level budgets and then roll up to the corporate budget. This recommendation implies that there are software applications written specifically to handle these budget requirements and using a relational database as the storage medium for all the data.

I particularly like Mr. Wilderman’s assessment that there is great value in storing all data in a multi-dimensional structure, in addition to the implied row and column format that each relational database table provides. Additional data dimensions can be geographical territories, product lines, customer classes and others. The advantage of doing that, and assuming the budgeting application allows additional data dimensions, is that analysis of data can be more meaningful and lends itself to greater visibility and clarity of data and reports, when communicated to senior management.

Another important feature according to the author is the ability to employ drivers within the budgeting solution, as well as availability of financial modeling were KPIs (Key performance Indicators) can be created in the planning process, based on actual, historical data, as well as desired forecasted data.

Finally, Mr. Wilderman mentions the importance of data visualization, where CPM data can be visually displayed, using both actual and forecasted data, and communicated in a manner that is simple to read and understand by managers and those in charge of the organization (CEO, CFO, etc.).

While I was reading this article, I was thinking to myself that these important points are what really matters in a planning, budgeting and analysis environment, where senior management must be given exactly the data they need to make informed decisions. This data must be communicated periodically and timely, so these decisions can be made in response to actual accounting data compared with anticipated results (represented by the budget) plus all relevant economic and market changes as they unfold during the budget period.  The formatting and appearance of this data must be such that management can easily see and understand the data.

The points raised by Mr. Wilderman are exactly the points delivered by Budget Maestro / Analytics, commercially published by Centage Corporation, an application I use regularly and recommend to organizations in various industries. Many of the blog entries here make reference to it and why its design and implementation provide CPM data and aid company managements in making more timely and accurate decisions and with greater confidence.

Cash Flow Forecasting Best Practices

It is time to demystify existing misconceptions and practices

Earlier this year I participated in a discussion on the Proformative.com site, titled: Cash Flow Forecasting Best Practices. A Proformative member asked a question which is very common in many finance organizations: What are the best practices when it comes to developing a cash flow forecast model? The person indicated that it was for a large publicly held company with global operations and that they have a comprehensive P&L forecast but struggle with a large and cumbersome Excel model which must be tied to the budget (P&L). This person was looking to start from scratch and build a more robust and manageable model. It was clear that they needed help. Does that sound familiar?

  • Is your company struggling in forecasting its cash flow or is unable to forecast its Balance Sheet and the derived Statement of Cash Flows?
  • Are you using home grown spreadsheets you inherited from a person who is no longer with the company?
  • Have you noticed broken link messages and suspect that other errors may exist in these worksheets?
  • Are you unable to maintain these spreadsheets, or add records without introducing new errors?
  • Are these new additions properly linked into the model?
  • Most importantly, is the output from these worksheets meaningful and reliable?

If you answered yes to any of the first four questions and no to one or more of the last two you probably realize that you must make a change in this process. You also realize that you are not alone which explains why many people responded to this question on Proformative.com and why the topic of cash flow forecasting is popular on that site.

What surprised me was that a good number of the answers were focused on developing a more robust spreadsheet approach to solving this problem, convinced that the spreadsheet is the answer to this challenge; some claiming that they have a model that works and is able to provide a forecast of the cash going in and leaving the organization.

What about the sources of this cash, or the inflows and outflows of cash into and out of each of the three main categories and in each forecasted accounting period? And what about the one or two people who suggested that a cash flow projection can be easily obtained if you have a reliable forecasted Balance Sheet? But how do you reliably forecast a Balance Sheet, complete and accurate and always synchronized to your P&L forecast?  Do you use another home grown Excel model to do that?

As I have written before on this blog and in other forums, Excel is a fine application with a tremendous amount of power and features. One, however, must understand its limitations (and their own limitations in using this application) when using Excel in certain financial processes such as financial reporting, planning, budgeting and forecasting, processes that should always include a Balance Sheet and a Statement of Cash Flows. The blog post titled “Should Excel be Expelled” touches on this idea.

It seems to me that many finance professionals, greatly skilled in using and programming Excel, don’t realize that much of Excel’s apparent power and seemingly endless features may lead to a false sense of believing that anything can be done with the software. This results very often in gigantic models being developed, incorporating many workbooks containing many worksheets each. The risk of having material errors in these models increases exponentially as the complexity of the model increases. To that add the often lack of documentation and rarely used change management controls, even in large organizations, and you begin to see the magnitude of these unmitigated risks.

Even in a perfect world with perfect Excel programming, a robust internal control environment and other positive factors, a cash flow forecast, or more accurately, a forecasted Statement of Cash Flows cannot realistically be modeled in Excel because it requires a complete and accurate forecasted Balance Sheet, perfectly synchronized to the P&L budget model. My blog posts “Can you Really Forecast your Cash Flow?”, “Forecasting a Balance Sheet in a Spreadsheet World”, and “Why you Must Forecast your Balance Sheet(and Part 2), further explain these concepts.

To me it makes a lot more sense to implement a purpose designed solution to accomplish the tasks of planning, budgeting, forecasting and analytics. Many of the blog posts on this site cover this critical set of business processes. Before embarking on new, complex projects, we need to realize Excel’s strengths and limitations, and our own challenge of controlling our desire to solve any problem with this tool.