Monthly Archives: September 2015

Are you Still Forecasting Only your Revenue and Expenses? Part 2

Can you tell if the budget you just put together is achievable?

In part 1 of this series we saw why a forecasted Income Statement is insufficient when preparing a corporate plan and budget. In this installment we will learn how we can make this plan and budget complete and useful to management on all levels.

The real question is how to reliably and consistently forecast a Balance Sheet given all the difficulties associated with this task. By now I believe most readers of this blog realize that using a spreadsheet is the wrong approach to forecasting a Balance Sheet, see Forecasting a Balance Sheet in a Spreadsheet World. In fact, this effort will be futile, as many finance professionals have discovered. There are companies that have a rudimentary forecasted Balance Sheet done in a spreadsheet; however, all critical numbers are a rough approximation of budget period GL account balances, and should not be relied on.

Many dedicated planning and budgeting applications allow their users to construct a forecasted Balance Sheet, however, users are required to program formulas, functions and links just like in a regular spreadsheet, and the results are the same as what you would get from doing this in a spreadsheet application (e.g., Excel). It seems to me that allowing users to program their balance sheets was an afterthought by the designers of these planning and budgeting applications. The result here, however complex the model, will only show rough approximations of key account balances (e.g., Cash, A/R, A/P).

I’ve looked at many planning and budgeting software applications that claim to be a departure from spreadsheets. In many ways they are.  In other ways their functionality is just like using spreadsheets: Tedious and time consuming programming of formulas and links, troubleshooting of errors, and the inability to arrive at an accurate and complete set of future period financial statements.

There is one main reason why most planning and budgeting applications cannot deliver a complete and accurate Balance Sheet: They do not treat the budget as an extension of the actual accounting system into future periods and they cannot accomplish that due to design deficiencies. To be successful at delivering an accurate and complete Balance Sheet, the planning and budgeting solution must operate like an actual accounting system and have its own General Ledger and subsidiary ledgers (revenue, expenses, fixed assets, debt, equity, etc.).

This can only be accomplished by having the planning and budgeting software make journal entries in its own “General Ledger” in response to all budget line data that has accumulated from all business units. A more detailed explanation can be found here, Those Debits and Credits. This GL must be linked to the actual accounting GL and mirror its accounts.

With the forecasted Balance Sheet accounts updated in each budget period with debits and credits, automatically posted by the software and reflecting all activity as dictated by the budget, you consistently get a complete and accurate Balance sheet for each period defined in the budget. The Statement of Cash Flows will be just as accurate and complete since it is generated from the forecasted Balance Sheet and Income Statement.

When you work with Budget Maestro from Centage Corporation you realize why automatically obtaining forecasted Balance Sheet (and of course an Income Statement and a Statement of Cash Flows) is possible. It is the only software solution I am aware of that employs this future period automated journal entry approach to generating accurate and complete forecasted financial statements. And they do it by design and not as an afterthought.

Are you Still Forecasting Only your Revenue and Expenses? Part 1

Can you tell if the budget you just put together is achievable?

If you are still budgeting and then re-forecasting only your revenue and expenses you may have run into a well known, yet often ignored phenomenon: Missing budget revenue targets due to cash shortages, inadequate credit lines, poorly timed financing arrangements, etc. This is very common during periods of growth, where a company must forecast its revenues and expenses required to achieve these growth objectives during the duration of the strategic and budget plan.

Companies that are in high growth industries and even established enterprises that cannot achieve very high gross margins soon find out that they have the potential to become very profitable but invariably run into one or more cash crunches along the way.  In fact, many organizations will not be able to deliver the results outlined in their budget for that reason alone. Of course, there are other reasons why companies miss their budget numbers but this discussion only focuses on poor and incomplete planning and budgeting.

Forecasting revenue and expenses is relatively easy if you have a solid strategic and operational plan. You can even do it using a set of spreadsheets, although I strongly recommend against it for many reasons, see my article Should Excel be Expelled?. The end result will be a forecasted income statement with columns of numbers for all periods in your budget. The trouble is that this is using accrual based accounting, which means that in reality a good portion of the forecasted sales in a certain period will bring in cash in a future period (in 30, 60 days or however many days each customer has to pay their invoices).

To make things worse, in order to make these forecasted sales you have to have inventory (if you are a product based business). This inventory must be readily available to ship in the period you say in your plan you will be shipping it. This means that you most likely have to either buy or make that product in a period earlier than the period the sale takes place. Now this implies that you had to spend cash either on the purchase of the finished product or on raw material, labor and outside services, with each one of these components occurring prior to the period in which the sale took place and with their unique payment terms.

Now consider forecasting new products or services that have not yet been developed and tested. Research and development expense that is often necessary prior to introducing new products will most likely show on the forecasted income statement; however, judging by the Income Statement alone you won’t know whether or not you will have the cash to perform these activities. If you are unable to complete R&D, testing, and any required certifications, etc., your new product availability may be delayed or the entire new product line may be cancelled, which in reality will result in not achieving the sales forecast for this new product line. The conclusion here, again, is that you need to have visibility into your cash balance during the budget period.

Will your forecasted income statement tell you whether or not you will have the cash to accomplish all that? Of course not. To do that you need a forecasted Balance Sheet and a forecasted Statement of Cash Flows which will further clarify where and when this cash is coming from (in what amount, from what source and in which period). Furthermore, these two statements must be tightly linked to the Income Statement and dynamically change as budget items affecting the Income Statement change, or as various versions and budget scenarios are evaluated. A budgeted profit and loss statement that most companies prepare annually will never accomplish that.

By now I hope I have you convinced that you must also forecast your Balance Sheet: Why you must Forecast your Balance Sheet Part 1 and Part 2. If you have a complete and accurate forecasted Balance Sheet and a forecasted Income Statement you can relatively easily compile a forecasted Statements of Cash Flows. Now you can have an insight into the future financial health of the company, and particularly into your cash needs along the budget timeline.

In the next installment we will see how a company can practically forecast its Balance Sheet using modern forecasting tools.

Is Excel Reporting a Wise Choice?

That depends on where the data comes from.

I recently saw a comment by Randall Bolten to a Proformative webinar on Excel based reportingMr. Randall is author of the book “Painting with Numberswhich the author describes as “Presenting Financials and Other Numbers so People will Understand You”.

Seeing and understanding data is the outcome of effective communication of financial results to company management and users of financial statements (banks, investors, shareholders, etc.). These financial results can represent management reports of past performance, budget and forecast data, financial statements, the organization’s financial strategic and operational plan and more.

Microsoft Excel is the most widely used software application in the workplace.  You would be hard pressed to find a single person using a computer at work without at least occasionally opening an Excel workbook and using it for a variety of applications. In accounting and finance, Excel is used daily and can be found open on the computer desktop for as long as the user is logged in.

Financial reporting with Excel makes perfect sense as long as the user fully understands the benefits and limitations, and especially the pitfalls and risks involved, as we are about to discover here.

It still amazes me to see how companies, even large ones, depend on Excel for consolidations and compilations of financial statements, despite the fact that their ERP software or accounting applications are usually perfectly capable of producing such statements with the proper setup.

The main risk in using a spreadsheet in the preparation of corporate financial statements arises from the use of home-grown spreadsheets with user defined formulas, functions, macros and links, all of which can include errors and omissions, can be easily overwritten or manipulated, erased or lost. To that add the typical lack of periodic review of these spreadsheets for accuracy and completeness, and the risk for material errors in the financial statements increases considerably.

The same is true for corporate budget preparation and periodic re-forecasting done in Excel.  This is still common practice in many organizations and even well known, dedicated database based planning and budgeting solutions use a “spreadsheet like” approach having some of the pitfalls listed above, reasoning that users are already familiar with the Excel interface and use of its formulas, functions and links.

As we have seen in several of the blog entries on this site, this is a very dangerous and undesirable practice: Are You Still Consolidating Financial Statements in a Spreadsheet? and Forecasting a Balance Sheet in a Spreadsheet World.

There is however a set of compelling reasons why Excel can (and under the right conditions should) be used for presentations of financial results and other reporting. The principal example is when the financial data that needs to be communicated to users of these reports originates in a much more robust environment and is directly linked to the data source.

In the case of planning and budgeting applications, if Excel is only used for graphic and tabular presentation of data, without any significant formulas, functions, worksheet links and other user programming, there is a compelling reason to use this tool, for its rich set of formatting and graphical presentation options, and the robust and consistent data links established between the data source (e.g., Planning and Budgeting Software) and Excel.

A good example of using Excel to create presentation quality reports and analysis is Analytics Maestro from Centage Corporation. I have covered this application earlier in this blog: Accounting and Business Data Come to Life. This is a perfect situation where Excel does not directly participate in the creation of data through use of formulas, functions or any other programing. It merely is the recipient of data from Budget Maestro where all current and historical accounting data plus all budget versions reside. Through a seamless interface with Excel, raw data is completely and accurately transferred where Analytics Maestro Excel add-in takes over and displays it in the chosen format and includes the exact content needed for the particular presentation.

An application such as Analytics Maestro considerably reduces the risk of introducing material errors in reports. All presentation reports are as accurate as the raw data received from the actual accounting or ERP software and the data from the planning and budgeting solution. Since a link is directly established between these applications, there is no manual data entry required or any user interaction, hence the accuracy, completeness and error-free data transferred into Analytics Maestro.

Finally, changes to the source data (e.g., revision of budget data) will automatically update the information displayed in Analytics Maestro. Here again, no formulas, functions or links ever need to be created or updated in Excel. You get the outstanding display features of Excel with no risk to the integrity or accuracy of the reported information.

With this level of formatting and presentation capability, reporting and analysis with Excel becomes a sensible choice for users in many areas of the enterprise. Conversely, any use of Excel for planning and budget modeling or preparation of consolidated financial statements remains a risky endeavor and should be avoided.

Financial Planning & Analysis – Art or Science?

How technology reduces the “art” element in successful FP&A activities

This morning I received an e-mail from inviting me to a webinar titled “The Art (and some Science) of Great FP&A”.  I am glad to see a resurgence of such an important facet in any company’s finance department and the increasingly stronger endorsement of this function by executive management.

FP&A stands for Financial Planning and Analysis. This function is performed in the finance organization and consists of preparing financial plans and budgets based on strategic plans and historical data, gathering actual and current data and comparing with budgeted data and doing re-forecasting during the budget year. The analysis performed is used to provide upper management with information they need to make strategic and operational decisions.

Historically, and to a certain degree even today in many organizations, FP&A is only performed annually, during the so-called “Budget Season”, with little attention to analysis (the “A” in FP&A). There are several reasons for this:

a. The planning and budgeting processes are complex and very tedious. In the past, each iteration of the budget required an enormous amount of work to update data and correct newly discovered errors and omissions. By the time the budget was completed and approved, it was in many cases already obsolete.

b. The budget data had little or no correlation to the actual data. In fact, many budgets had a structure that did not tie directly to the actual accounting data structure; there was no 1 to 1 relationship between specific budget items and actual accounting general ledger accounts.

c. Due to existing technology, planning and budget data resided primarily in spreadsheets.  Eventually, this data found its way to more “purpose designed” applications, which initially borrowed from the spreadsheet model, albeit in a more robust database environment. The results were inaccurate, incomplete and often suffered from serious logical and computational errors stemming from the budget model as implemented using spreadsheets and other software.

The end result is often a budget that is not used for its intended purpose. Management does not get the benefit of having the insight into the company’s future financial health, due to the disconnect between the budget data and the actual results as produced by the accounting department. Often, bad business decisions are made, not supported by solid data, as intuition and prior experience, if available, play a major role in making these decisions, a risky behavior at best.

When true analysis is performed, all of the data used resides in the analysis module representing actual and budgeted data, with separate budget data for each version of the budget where applicable. The analysis system will then display the required data, either visually via graphs and charts or in tabular format, all of which can be printed or forwarded to a pre-defined distribution list.

The content and format of this data can be pre set according to management’s needs and for clarity, only the required data should be conveyed. In a properly set up environment, FP&A’s software solution is linked to the ERP software and data is automatically available as soon as an accounting period is closed. This allows the decision process to start sooner and since the data can include specific indicators about future period performance (e.g., forecasted financial ratios), management can make tactical and operational decision with greater confidence.

Today there is a lot more science and less art in FP&A activities. To me the “Art” in all this is having vision and recognizing what technology solutions are available and pairing the technology with company processes and existing systems. Once properly set up, the technology will do the rest, allowing FP&A to successfully do its job.