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 Planning 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 Planning 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 Planning 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, Planning 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 Planning 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 Planning Maestro recreating the Balance Sheet and Analytics Maestro re-displaying the data in the template. Similarly, using the What-if Analysis in Planning 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 Planning 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 Planning 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 Planning Maestro data (for forecasted data). All reports you set up in the system follow the same principle.
Planning Maestro/Analytics users only have to focus on creating a plan and budget for their organization, and periodically maintaining it as the needs arise. Planning 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 Planning Maestro with Analytics makes the automated forecast, display and reporting of financial ratios a reality even for small companies.
See how you can automate your forecasting processes, forecast the impact of multiple scenarios, and quickly identify where, when and why actuals differ from plan, so you can take appropriate action.