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.
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.
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.
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.
How not having the right tools can be disastrous
A while back I wrote on this blog about forecasting the likelihood of a company meeting its loan covenants with its lenders Will you Breach your Loan Covenants?. I normally wouldn’t repeat this or be redundant unless I thought it was of great importance to the readers of this blog.
I recently visited a high mid-market company on the West Coast where I have done internal control work in the past. During my engagement I was made aware of the fact that the company had failed meeting one of its loan covenants with its primary lender for the second year in a row. The first year they came pretty close to meeting the value derived from a formula dictated by the bank, but the second year it appears that the value had further drifted apart from the minimum required number.
As it is usual in cases like this the company entered into negotiations with its primary and other lenders in an effort to remediate the situation. Without going into great detail, this was not a pleasant experience for those involved, although a solution was found and agreed on. Using actual accounting data this company knew they were going to blow the covenant, but they were not able to forecast it early enough in order to make changes in anticipation of the worsening of their financial health, of which this particular loan covenant was an obvious indicator.
What makes the situation worse is that this company, despite having a solid management in place, good and dedicated workforce, including the accounting and finance organization, great work ethics and an incredible array of information technology hardware and software solutions, has no ability to properly forecast their balance sheet, where key data elements can be extracted and used to calculate forecasted financial ratios and in this example use the exact formula needed to determine the specific loan covenant they failed to meet.
The software application used in that company to perform all planning, budgeting and other corporate performance management (CPM) functions, is considered a Tier 1 application in its category, along with a popular Tier 1 ERP solution; however, management was just not capable of answering two simple questions: “Will we be able to meet our loan covenants, and at what safety margin?” and, “Will there be a deterioration or strengthening of our future financial health and at what rate?”.
Finance professionals know that a company’s financial health does not deteriorate overnight. It often takes years of bad performance by certain business units, bad management decisions about acquisitions, product development, marketing efforts, etc., to put the financial health of the company on a noticeable decline. The sad part is that you often can’t easily detect that from reading financial statements and management disclosures to these statements, even when they are prepared in compliance with GAAP rules or meet SEC reporting standards.
As the link above shows, there are technology products that were designed to address these major challenges that so many organizations face. It is clear to me that just investing in expensive IT solutions without completely and clearly defining the needs of the organization (e.g., ability to forecast a complete and accurate balance sheet) can lead it down a path where due to lack of visibility can cause unnoticeable worsening of the financial health of the company until it becomes apparent that something is very wrong. This, often, it is too late and frequently results in either the failure of the business, or an unplanned acquisition at a deep discount.
As the title of this blog post suggests, I urge you to seriously look at what matters in your finance organization and equip it with the right tools that can make a clear difference between meeting or blowing your loan covenants. If loan covenants are not applicable in your business, the strength of your balance sheet certainly is and should be regularly forecasted.