Tag Archives: Statement of Cash Flows

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.

The Benefits of an All-Encompassing Budget

Why using inadequate tools and approximating the balance of forecasted Balance Sheet accounts is a bad approach for budgeting

I recently had a discussion with a colleague who is a partner in a local management consulting firm about organizations’ attitude toward the planning and budgeting process and the benefits they reap from these activities. I expressed my views on how a proper budget should be prepared and why it is vital to budget the entire chart of accounts with the benefit of obtaining a complete set of future period financial statements. I further explained that all accounts that contribute to creating the Balance Sheet must have their balances updated throughout the budget period, performed through increases and decreases (debit and credits) which are derived from the forecast of revenue and expense accounts (Income Statement accounts).

The argument against doing this was (according to this person) that you can use a spreadsheet to forecast the ending balances of all critical Balance Sheet accounts using simple assumptions and the data from forecasted revenue and expense accounts. He recognized that this would be a rough approximation of  account balances but argued that since most budgets are not accurate anyway and companies almost never hit their revenue and expense targets, even if there was a way to accurately and completely forecast the Balance Sheet and Statement of Cash Flows, they would be inaccurate due to the inaccuracy of the revenue and expense account balances all due to bad assumptions, inaccurate budget data supplied by the various reporting entities and other reasons.

The reason I bring up this subject is that I have heard these arguments before from finance executives and professionals who were tasked with preparing their companies’ annual budgets but not given the appropriate tools to do so, added to old traditions, misconceptions and workloads only allowing these people to repeat traditional processes without taking the initiative to look for more advanced ways to obtain meaningful and useful results.

If you accept the fact that budgets are never accurate and therefore no additional effort should be put into forecasting what really matters (e.g., cash account balances, other assets and liability account balances, etc.) than you are left with repeating the same budget process chores year after year with little or no benefit to the company. With this attitude, how can we expect company managements to make solid business decisions, let alone have insight into the future financial health of their organizations?

It is true that advanced software solutions such as Budget Maestro by Centage Corporation can produce inaccurate budget period Balance Sheets as compared with the actual accounting Balance Sheets. Of course, the forecasted P&L will also be different than the actual period P&L. But these forecasted Balance Sheets and Statements of Cash Flows are always going to be accurate and true to their corresponding forecasted Income Statements through the built-in logic and automated journal entries that ensure that each Balance Sheet account’s balance is correct.

If you are approximating or grossly estimating the balances of your forecasted Balance Sheet accounts through use of spreadsheets or purpose designed Planning and Budgeting software solutions that behave like spreadsheets (e.g., require user supplied formals and links and with no built-in business logic and automated journal entries) your errors are likely to be compounded by the errors in the forecast of revenue and expenses.  Essentially, what you get is an inaccurate P&L forecast driving a flawed Balance Sheet forecast causing its account balances to be removed from reality; that is if the Balance Sheet is forecasted at all.

In contrast, if you use a solution that ensures your forecasted Balance Sheet and Statement of Cash Flows are systematically complete and accurate by the nature of the system logic and automated forecasted transaction processing, you can focus on good planning and budgeting with built-in tools and business logic that will allow your P&L forecast to be complete and reasonably accurate; as accurate as your assumptions. The accuracy and completeness of your forecasted Balance Sheet and Statement of Cash Flows will follow and match the accuracy of the Income Statement.

That is a giant leap from traditional budgeting approaches, those based on tradition, bad habits and inferior tools. I think it is time to evaluate the new alternatives.

Why you Must Forecast your Balance Sheet – Part 2

It is not as hard as you think and the results will greatly justify the effort

In the first part of this series we saw why we need to be able to forecast our company’s balance sheet.  In this installment we will see examples of how a forecasted balance sheet is constructed and a software solution that allows its users to produce a forecasted Balance Sheet and a Statement of Cash Flows automatically from their budget data.

Here are a few examples:

Your sales on credit generate accounts receivable in the period products were shipped or services were provided.  The forecasted balance sheet (A/R balances, and Retained Earning – Current) needs to reflect that, taking into account all of your credit sales to all of your customers, at the right prices and the right terms.  Then forecasted cash and A/R must automatically reflect collections from these customers, according to forecasted payment terms, which may differ from customer to customer.

At the same time, your forecasted expenses on the P&L will require cash.  This cash will have to be disbursed according to forecasted purchases and their specific payment terms as dictated by suppliers. Your other cash disbursements to employees, taxes, purchases of assets and other expenses shown on your forecasted P&L will also need to be considered and shown on the forecasted balance sheet (and Statement of Cash Flows).

Only then, when you have your forecasted cash receipts and cash requirements (represented by the ending cash balance in each forecasted balance sheet period, as well as the output from a forecasted Statement of Cash Flows), will you know whether or not your plan and budget are feasible and what you need to do in order to prepare for execution of the plan.

Another example is projecting in advance whether or not you will be able to meet your loan covenants Make a Covenant to Properly Plan your Company’s Financial Future or being able to forecast any financial ratio during the planning and budgeting period Why Financial Ratios Should be part of Your Budget and Forecasts.  That alone is worth the effort of having a forecasted balance sheet.

The above example can be carried through to all other sections and elements of the balance sheet.  As in actual accounting, every forecasted activity that appears on the budgeted income statement, must automatically find its way to the forecasted balance sheet and from there, automatically contribute to the creation of a forecasted Statement of Cash Flows.

We saw how hard it is (actually impossible to do it right) to create and maintain a budgeted balance sheet in a set of spreadsheets. Similarly, it is as hard to create and maintain a meaningful balance sheet in most dedicated planning and budgeting applications that rely on user supplied formulas, functions, links or any other user programming.

For these reasons I am a great believer and supporter of Budget Maestro from Centage Corporation which is the only planning, budgeting and analysis solution I have seen so far where the balance sheet is automatically derived from the budget and is automatically maintained; it actually evolves in real time as the budget is built. The secret to this remarkable ability lies in the unique design of the software to behave like an actual accounting system for all future periods. This concept is described here Those Debit and Credits.

Not forecasting a complete balance sheet is a dangerous and risky proposition. I seriously question the validity of the entire process when the future financial health of the company cannot be forecasted.  Every organization that engages in building and maintaining a budget should have visibility into its future balance sheet. This balance sheet must be accurate and complete and above all, must automatically follow all budget input and pre-set business rules.  Not being able to do that due to lack of technology tools is no longer a valid excuse why this should not be done.

Why you Must Forecast your Balance Sheet – Part 1

It is not as hard as you think and the results will greatly justify the effort

I have written several times on this blog about forecasting a company’s balance sheet and the many benefits one gets from it. If this is so beneficial, then why is this so hard if not impossible to do using conventional methods and tools? 

Firstly, if you are still working with a spreadsheet to create your budget and periodic re-forecast, then arriving at a reasonably accurate and complete balance sheet is an unrealistic expectation; there are just too many details that have to be passed to the balance sheet from the income statement forecasting activities, plus keeping track of activities that naturally occur only within balance sheet accounts.

To that you must program your various assumptions, that even for a small organization there are not enough rows in the spreadsheet to properly do it and the number of formulas, functions and links required to maintain it are just too numerous. And did I say “maintain”? It is the maintenance of these large spreadsheets that usually renders the whole exercise futile.

I’ve written here on spreadsheet use for planning and budgeting activities and why it is such a bad idea to use them (Forecasting a Balance Sheet in a Spreadsheet World, and Think you can rely on spreadsheets for financial applications?). Many compelling reasons exist against this practice as more and more finance managers and professionals have come to realize.

It is also a fact that many companies that use dedicated planning and budgeting software solutions do not budget their Balance Sheet or their Statement of Cash Flows, since the majority of these applications, despite being in a more robust, database environment, still behave like a collection of spreadsheets with required formulas and links, exposing their users to the many risks and challenges that spreadsheet users encounter.

Why is the balance sheet so important to forecast?  Isn’t the P&L (income statement) sufficient?  My answer is a definite no. Without a budgeted balance sheet company management cannot forecast the future financial health of the organization. The budgeted income statement allows the company to forecast most of its operations, such as sales with its associated costs, operating expenses, including payroll and its related expenses and anything one would normally see on an income statement.

However, unless there is a forecasted balance sheet, closely integrated to the budgeted income statement (think of your own ERP or accounting software where the balance sheet is seamlessly produced and follows the activities in the P&L, as well as directly receives entries into its own GL accounts) there is little value in just getting a complete and accurate forecast of your P&L.

Can you say with confidence that you’ll be able to execute the forecasted P&L? Will you have sufficient cash to purchase inventory for the projected growth or that new product line you are so eager to launch mid-year of your forecast? What about the additional workforce in your forecast? Will you have the cash to support these new hires? What about the three new positions in marketing and the five more inside sales representatives you determined are needed to achieve the sales targets?

Will you be able to finance this expansion? Will you have to sell additional equity in the business? Issue more debt? Sell assets? Will you have to use one, two or more of these methods and when, during the forecasted period?

As is clear here, none of these questions can be accurately and honestly answered without having visibility into a forecasted balance sheet. For this forecasted balance sheet to work well and be meaningful it must be tightly linked to your plan and budget in a way that every budget line affecting the forecasted income statement and all existing business rules must seamlessly affect the forecasted balance sheet.

In our next installment we will see a few examples and learn how this is all possible.