Tag Archives: ERP

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 Proformative.com 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.

Should Finance Become an Analytics Powerhouse?

See why Finance finally has the right tools to excel in this function

Recently there was a discussion on the Proformative.com site whether finance should take over analytics and own the process, the advantages to the company and the steps required to do so. The following is the link to the discussion:  http://www.proformative.com/blogs/anders-liu-lindberg/2015/06/20/how-finance-can-become-analytics-powerhouse and I encourage all readers of this blog to become Proformative.com members as the site includes free membership and great resources and insight in all areas of accounting, finance, HR, corporate governance and more.

This is a great topic for discussion with many opinions and points of view, depending on which functional area of the company you visit. The general opinion in this particular Proformative discussion was that finance should own analytics and be tasked with providing management with the essential data that is necessary to run the company and steer it on its charted course.

Unfortunately, in many organizations today there is no central area where analytics is performed, along with reports, alerts and other research into variances anomalies. Many functional units (e.g., sales) do their own analysis of only the data produced by their business units or which are directly related to their area. With analytics being fragmented, and often incomplete and inaccurate, management cannot benefit from the real meaning of the data generated in their companies, validating the notion that there are many versions of the truth.  Centralized analytics is designed to change that.

What is analytics?

Analytics is the process of:

1)    Retrieving stored data from various company departments, divisions or business units, with historical as well as recent actual results of operations and financial data, plus the organization’s plan and budget data.

2)    Understanding the meaning of all relevant actual data collected and comparing to plan and budget data.

3)    Preparing reports using text and other visual aids to convey these data and their interpretations to individuals within management and other functional areas, supporting the decision making process.

When there is a plan and budget in place while monitoring the actual results and comparing with the anticipated results (as expressed by the budget and derived from the plan), meaningful information can be obtained when performing analytics.

The data used to perform analytics originates in several or many functional areas of the company. For example: Sales data, marketing data, accounting data of actual closed periods, etc.  It makes perfect sense to centralize the analytics process and have one owner over the process: The finance organization, reporting to the company CFO.

Information technology enables all data used in analytics to be retrieved by the analytics process owner and processed by the analytics software. The output, in turn, is a pre-defined set of reports and other visual aids (e.g., graphs, dashboards) designed to allow the user of these reports to quickly understand what these reports and visual displays are trying to communicate.

The CEO of a leading Planning, budgeting and Analytics software publisher defines this system as:

“A single source of accurate financial information- backward and forward looking- with the ability to integrate with Operations data- and that can be served up to end users for them to analyze themselves, and thus, allows them to contribute to the strategic and operational decisions and direction of the company in a timely (and informed) manner.”

The purpose of analytics is to give management the tools to be able to make timely and informed decisions with confidence. Having the right data reduces the risk of bad decisions, or decisions made long after the symptoms of trouble have actually appeared, when there is little that can be done to turn things around.

In a blog entry I recently made, titled “Why CFOs Should Adopt Financial Analytics”, I emphasize the need to have accurate, complete and timely information  that upper management can reasonably act on it, making decisions backed up by solid facts and not just intuition. It is the CFO’s responsibility (as the CEO’s second in command) to ensure that such data exists, is accurate and reliable, is available on a consistent basis and is presented in a manner that is simple to understand.

Since finance reports (or should report) to the CFO, it is finance that must develop the analytics process, acquire the right tools, implement them and use them on a regular basis, while communicating with the various business entities that generate the data.  Duplicating this effort in other departments, or having a fragmented system with incomplete or erroneous data is completely counterproductive, thus rightfully giving finance its status as an analytics powerhouse.

Does the Budget Process Need Changes?

Why the fundamental process is still valid but the outcome can be more useful and meaningful

I’ve covered a lot of material in the last 2 years here in this blog. Most of the entries made reference to the planning, budgeting and analysis processes, all of which are key elements in any corporate finance department. We saw why these activities must take place year round and why analytics is critical. We also saw why we have to use a purpose designed software solution to perform these tasks and why spreadsheets and certain software applications that employ spreadsheet-like techniques, including use of formulas and links, are not the right tools to use.

A major discovery we made was the ability to accurately and completely forecast a company’s future financial health, through automated forecasts of its Balance Sheet and Statement of Cash Flows, with their derived forecasted financial ratios all of which provide insight that can only be gained by reviewing these future period financial statements, along with the forecasted P&L.

I’ve expressed my opinion that this entire process should be performed in finance and owned by the CFO, who along with the CEO and other senior managers are the direct users of the presentation data generated in the analytics process.

What about the budget process itself? Does it require any changes? Is the traditional workflow, including the departmental submissions, the reviews and subsequent iterations of the budget still valid and necessary?

Fundamentally, certain aspects of the budget process are still valid and necessary.  Revenues must be forecasted in all divisions and business units and should include all product lines, with forecasting of new products and services, discontinuing existing products and services within the budget period where applicable, etc. Costs of inventory or services must be forecasted and clearly shown in the budget along the revenues they will generate. Operating Expenses and payroll must be budgeted and this is also true for capital expenditures and other miscellaneous income and expense. In order for a complete and accurate set of forecasted financial statements to be produced, all budget components must exist, be realistic and go though the review and approval process.

This implies that a review of all budget pieces submitted by regional managers, department heads and other responsible persons according to the organization structure must be conducted, revisions proposed and made, with final review and approval by management.

What is changing is how budget numbers are automatically calculated by the software and how future period financial statements and other management reports are generated and become available to users. Another major departure from traditional methods is the ability to seamlessly interface the planning and budgeting software to the company’s actual accounting general ledger, where actual period balances and even transaction level data can be automatically made available to the analytics software.

“Smart-Budget” software applications, such as Centage Corporation’s Budget Maestro with Analytics allows the budget to be developed from plan assumptions and user data, in combination with drivers and built-in business rules (e.g., cash for product A sales is collected in 30 days following the sale but in 60 days for Product B). Since there are no formulas, functions or links required anywhere in the program, budget administrators only need to rely on approved business units’ submissions of their budget data and the company’s selected business rules and applied drivers and other assumptions in addition to correct assignment of budget lines to G/L accounts, correlating to actual G/L accounts used in the company’s accounting system.

As each major area in the budget is entered in the system (e.g., Revenue and Cost, Personnel Expenses, Operating Expenses, Fixed Assets, etc.), the system automatically applies the selected business rules, drivers and G/L account assignments to produce, in real time, forecasted financial statements resembling the company’s actual financial statements. These reports, including an Income Statement, a Balance Sheet and a Statement of Cash Flows, plus any number of other desired reports, represent the state of the budget in any given point in its creation cycle and for each period in the budget. As changes to the budget are made, all reports are automatically updated. With different versions of the budget, and employing “What-If” scenarios, the output, including all presentation reports, graphs and other visual displays set up in the Analytics module, immediately respond to the requested data.


You can keep the annual budget process the same, but make sure you receive reliable and approved data from business units. Be sure to include all G/L accounts in the budget process (some will automatically be calculated), as it will ensure that you get complete and accurate forecasted financial statements. When applying business rules and assumptions, combined with use of drivers, you will essentially experience an extension of the company’s actual accounting into future periods.

Using analytics to display all key data and “seeing” future period balance sheets and statements of cash flows is the closest you can come to using a crystal ball to predict the future of the business. Remember that the output is only as accurate as the data supplied to the system and use of reasonable assumptions and drivers. However, there is a major and profound difference between this method and the traditional way where only revenue and expenses were part of the budget.

Although the basic functions of the budget preparation process and workflow remain unchanged, there are many valid and completing reasons why the traditional way of using the budget data output is no longer a viable option. More and more organizations are discovering that having the ability to forecast what the financial health of their companies is going to look like in future periods gives them the ultimate control of their companies’ destiny.

Those Hard to Forecast Numbers

Learn to use readily available data to forecast and display key financial information

Readers of this blog have seen several posts focused on the importance of regularly forecasting things such as a company’s Balance Sheet, a Statement of Cash Flows, key financial ratios, loan covenants compliance probability and more.

Why is this important?

As any finance manager or professional knows, much of the financial future of a company is dependent on correct interpretation of key data and decisions made based on that data.  Unfortunately, that data isn’t always readily available from actual accounting systems and from traditional budgeting and forecasting solutions. We’ve also seen in these blog articles that preparing a traditional budget and periodic re-forecasts usually involves only the income and expense accounts, producing a forecasted Income Statement for each period in the budget (e.g., month).

A forecasted income statement represents the results of a strategic plan used in the preparation of an operations plan.  Both are then used in building a corporate budget with forecasted revenues and the expenses projected to generate these revenues. Usually, the more ambitious forecasts will show more aggressive growth with usually greater forecasted earnings, which may look promising but can actually signal trouble ahead.

This type of budget, while necessary in any business, fails to deliver the most important set of data,  the forecasted financial position of the organization (as represented by a forecasted balance sheet) for any version of the budgeted P&L. As we’ve seen in this series of blog entries, the financial position of any organization is an indication of its financial health.  Predict the future financial health of your company and you’re much more likely to make better, more accurate and more timely decisions. Fail to do that and you are likely to deal with severe consequences of poor planning and deficiencies in executing a plan based only on revenue and expense goals.

The conclusion of all this is that a forecast consisting only of revenue and expenses is not only incomplete but may in many circumstances be unrealistic, and in all cases cannot predict the likelihood of a company’s future financial health either improving or deteriorating. The reason for this is that a budget consisting only of revenue lines and expenses, prepared on an accrual basis, will not tell you whether or not you can actually achieve the goals listed in your plan and entered into your budget. This presents several important questions:

1)    Will you have sufficient cash to accomplish what your budgeted P&L suggests?

2)    Will the timing of your cash flows (e.g., A/R collections, A/P payments, payroll disbursements) allow you to maintain the desired cash balance in each period?

3)    Will you be able to acquire the assets needed in order to produce the inventory or service lines projected to achieve the desired revenue?

4)    Will your financial ratios be satisfactory throughout the budget period?

5)    Will you be able to comply with your loan covenants requirements?

6)    In summary:  Will your financial health improve or worsen during the budget period and by how much?

The clear answer to these questions is: Without a forecasted Balance Sheet there is no way of reasonably answering any of these questions. You may be holding in your hands an approved corporate budget book that cannot be executed on, even if you had the ability to book the projected sales and find ways to adhere to your budgeted expenses.

What’s worse, you can’t run multiple versions of your budget and see how your financial position changes in future periods in response to these different budget versions, so you can adjust your plan and budget and make other changes that will allow you to meet the plan and budget objectives.

As you may have already found out, it is very hard and risky to base your operations on an incomplete budget, one that makes it a easy to miss the budget numbers altogether.

In past blog posts we saw why most organizations do not forecast their balance sheets:  Why you Must Forecast your Balance Sheet (Part 1 and Part 2) and Forecasting a Balance Sheet in a Spreadsheet World. This is just too hard to do using traditional tools such as spreadsheets . It is also becoming apparent that most dedicated planning and budgeting solutions were never designed to deliver a true budgeted balance sheet due to their architecture and relying on spreadsheet-like use, with formulas, functions, links and other user supplied programming, and no “accounting-like” transactions that maintain ending balances in all forecasted GL accounts. You simply can’t add up columns of numbers and arrive at the balance sheet account balances like you do with an income statement. See Those Debits and Credits blog entry.

Those who regularly visit this blog know that I like working with Budget Maestro with Analytics – published by Centage Corporation – mainly because of its unique architecture that enables users to obtain a forecast of their company’s future financial position (i.e., the Balance Sheet), closely representing the organization’s future financial health, and often well in advance of potential adverse changes to the financial position. I think this is a new and positive trend in the development of CPM and Budgeting / Analytics software which to me it seems like a wave of the future.

I don’t know of any other software publisher that implemented Centage Corporation’s philosophy and vision, but I sure hope other developers adopt these principals and offer competing solutions that will allow their users to use the vast amounts of data available from their ERP software, combined with smart budget data, in order to deliver a clear picture of what lies ahead in terms of financial health and stability.

I just want to see more companies embrace this technology and use it to their advantage and by doing so eliminate an old, incomplete and deficient process, often regarded a chore or a ritual more than a productive process with useful and meaningful results.

Forecast and Monitor your Loan Covenants Compliance

How loan covenant compliance can be forecasted with Budget Maestro and Displayed by Analytics Maestro
There has been a recent series of blog posts on this site of why companies must regularly forecast their balance sheets Why you Must Forecast your Balance Sheet Part 2. There is also an older blog post on one of the benefits of doing that: Knowing whether or not the company will be able to meet its loan covenants imposed by their lender Will you breach your loan covenants?. In this post I would like to present a simple example demonstrating how Budget Maestro with Analytics (www.centage.com) can monitor both actual and budgeted loan covenants using Analytics Maestro with actual data collected by Budget Maestro from the ERP or accounting software and budget data provided by Budget Maestro from the budget plan(s).
The easiest way to explain this is by looking at a very common loan covenant that the majority of lenders use with lines of credit and other types of asset based lending. The typical language found in the loan agreement reads as follows:
“Borrower to maintain a Debt Service Coverage ratio of no less than 1.35 to 1 (this ratio can vary), evaluated quarterly. It is defined as the ratio of Cash Flow to Debt Service, where Cash Flow is defined as: The sum of net profit, income tax expense, depreciation, depletion, amortization and interest expense minus distributions, withdrawals and dividends. Debt Service is defined as: The current portion of long term debt plus interest expense.”
Using the above definition as expressed in the loan agreement we construct the following table using actual results from a fiscal year-end quarter ending on 3/31/2015 plus four budgeted quarters of the 2016 fiscal year: 
blog chart 7-27
The actual and four quarterly forecasted sets of numbers are derived from the Budget Maestro plan and presented by Analytics Maestro. Notice that the Current Portion of Long-Term Debt is derived directly from the forecasted Balance Sheet. Depreciation, Amortization and Interest are derived from the forecasted Income Statement but are dependent on activities occurring within the forecasted Balance Sheet that depend on asset acquisitions, sales and disposals, and changes in borrowings that affect the Interest Expense. These are part of the budget model and are entered through the Capital Assets and Financing modules in Budget Maestro.  As you can see, Budget Maestro handles all this automatically and all forecasted financial statements are seamlessly interlinked, just like in an actual accounting system.
Using Analytics Maestro, both actual and forecasted numbers can be represented in a template, similar to our example template, formatted any way you like, with charts, graphs and other custom formatting. Then, when an accounting period is closed, your actual data will display within this Budget Analytics template, along with all forecasted data. The table shown above is an example of data available in Budget Maestro and used by Analytics Maestro to display the actual and forecasted Debt Service Coverage ratio defining this loan covenant.
In our example, you can clearly detect a deterioration of the Debt Service Coverage ratio.  In the last forecasted fiscal quarter, ending on 3/31/2016 the ratio drops to 1.53, not much higher than the minimum required 1.35 ratio.  This is a concern since additional deterioration of this ratio can cause the company to breech its loan covenants and may result in the lender calling the loan or in other adverse consequences to the company.
However, with Analytics Maestro you see this well in advance.  Note that in this example you’ll be able to display this ratio monthly if that’s how you set up your plan in Budget Maestro (although in this example the lender only requires a quarter-end analysis of this ratio).  Now you can revisit your plan objectives, goals, assumptions and drivers, as well as all data supplied by business unit managers and other data used in building the budget. You can contemplate changes, rethink some of the initiatives and implement changes in the business that will result in maintaining a healthy ratio.  For example:  Maybe declaring a cash dividend for the next fiscal year (2016) is not such a good idea.  Removing the dividend will improve the ratio and create a higher safety margin. 
This approach is true for any changes that must be made in response to forecasted results of not just the P&L but the entire chart of accounts. As we have seen in many of the posts in this blog, real visibility into the future financial health of the company is greatly dependent on the ability to budget all balance sheet accounts and have a way (e.g., via Analytics Maestro) to properly display this forecasted data.
Another idea would be to use the what-if analysis feature available in Budget Maestro.  You can also have multiple plans created as part of the budget model, and applying each version can further assist you in analyzing how this loan covenant ratio behaves with each version of the plan.
The benefits gained through using Budget Maestro’s integrated financial statements, including a complete and accurate forecasted balance sheet cannot be over-emphasized.  Seeing and understanding your company’s future financial health in Analytics Maestro should be one of the most compelling reasons for implementing this software solution. The example in this blog post illustrates only one of the many uses of this software and how important it is to have the right tools at your disposal.

Forecast and Monitor your Key Financial Ratios

How key financial ratios can be forecasted with Budget Maestro and Displayed in Analytics Maestro

I recently posted a series of articles on this blog on the importance of forecasting a company’s balance sheet and how the financial health of the organization can be predicted using data available from the actual accounting system and from budget data, Why you Must Forecast Your Balance Sheet Part 1 and Part 2, Forecast and Monitor your Loan Covenants Compliance and A New Way to Look at Accounting Data. One of the posts was focused on how users of Budget Maestro with Analytics can forecast and monitor their loan covenants and detect well in advance when there is deterioration in the financial performance that may lead to breeching one or more of these covenants.

In this installment we will see how simple it is to display forecasted key financial ratios that will tell management whether the company is improving its financial health or whether certain attributes of the financial health are deteriorating. Using Budget Maestro with Analytics, this display is available for the entire budget period (12 months, 18 month, 3 years, etc.) plus any actual and historical periods.

Two key financial ratios I would like to use in my example here are the Current Ratio and the Quick Ratio. Finance managers and professionals are already familiar with these ratios and are now actually able to display and monitor them using Budget Maestro with Analytics (actual, historical and forecasted).

The Current Ratio is a liquidity ratio and is defined as Current Assets divided by Current Liabilities and measures the company’s ability to meet its current obligations. Both Current Assets and Current Liabilities are available from the actual or historical balance sheet, and Budget Maestro users have the advantage of obtaining budgeted values through a system generated forecasted balance sheet for every period of their budget.

The Quick Ratio is similar to the Current Ratio except that the inventory balance is excluded from Current Assets when performing the calculation.  It is an indication of how likely a company is able to meet its short-term obligations using only its liquid assets (primarily cash and accounts receivable).  As with the Current Ratio, Budget Maestro users have both Current Assets and Current Liabilities ending balances in each period of the budget, as well as the Inventory balance for each period-end in their budget, obtained from the automatically generated forecasted Balance Sheet. This is also true for actual and historical data, obtained by Budget Maestro from the ERP or accounting software and provided to Analytics Maestro.

Once the required data is available in Budget Maestro, all that remains now is a one-time setup of a template in Analytics Maestro as shown in the following example (the format and appearance of this template is only limited to the formatting capabilities of Excel, the program where Analytics Maestro resides):


The numbers in this template automatically populate from both the actual accounting system (3/31/2015 column) and from the budget plan. Note that changes to the plan will automatically result in Budget Maestro recreating the Balance Sheet and Analytics Maestro re-displaying the data in the template. Similarly, using the What-if Analysis in Budget Maestro or applying multiple plans (e.g., Best Scenario, Average Scenario, Worst Scenario, etc.) will cause the display in Analytics Maestro to change accordingly.

The following is a simple graph that can be set up as a template in Analytics Maestro.  All changes in the actual accounting data and the budget data will automatically be reflected in this graph.

blog chart 7-21

In this example we can clearly detect deterioration of both the Current and Quick Ratios, well ahead of time. This decline can be due to increase in accrued expenses or other payables, higher than needed inventory, etc. Armed with this information, management can contemplate, plan and make changes well in advance of these forecasted adverse events.

There are many financial ratios that can be automatically forecasted in Budget Maestro and displayed in Analytics Maestro. Some of the more popular financial ratios are: Working Capital to Total Assets Ratio, Debt to Equity Ratio, Debt to Total Assets Ratio, Return on Assets, Return on Equity and many more.

A popular ratio is Inventory Turnover that can be displayed using both historical and forecasted data of inventory valuations and cost of goods sold. Here, for example, you can have Analytics Maestro display the number of times inventory is expected to turn during the budget period with a number at each period end reflecting results based on the 12 trailing periods (months).

You decide what ratios are meaningful to your organization and simply set them up the same way the ratios in our example were set up. There is no limit to how many ratios can be displayed and tracked in Analytics Maestro. A key concept to remember is that Budget Maestro automatically generates forecasted financial statements that contain all the data needed for any imaginable financial ratio; all you do is tell Analytics Maestro what data to use and how to display it.

Once the display templates and graphs are set up, all relevant data will automatically be placed in these templates using your ERP data (for actual data) and Budget Maestro data (for forecasted data). All reports you set up in the system follow the same principle.

Budget Maestro/Analytics users only have to focus on creating a plan and budget for their organization, and periodically maintaining it as the needs arise. Budget Maestro, using its built in business rules, drivers and automatic generation of reports and financial statements takes care of all the rest. The accuracy and completeness of financial statements, including the Balance Sheet and Statement of Cash Flows are only limited to the accuracy of the data in your forecast, your assumptions and drivers.

As our little example here shows, using Budget Maestro with Analytics makes the automated forecast, display and reporting of financial ratios a reality even for small companies.

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.