Automating the 3-Way Forecast, also referred to as 3 Statement Model, has always been a strong point of Calxa. This key set of reports is the foundation for most financial analysis of a business including scenario planning and sensitivity analysis. The initial data comes from your accounting system. You then simply create your Profit & Loss and Balance Sheet budgets. The final step is setting the timing of inflows and outflows. Calxa provides smart tools to help with these.
Who needs a 3-Way Forecast?
Way back in the depths of time, banks would require a 3-way forecast when you applied for a loan. Once it was approved they rarely asked for more. In the last 10 years that has changed with many banks wanting updated reports quarterly or even monthly.
For the banks, it’s simple risk management. They want to be able to react early to any anticipated change of circumstances and it normally makes sense for a borrower to keep their lender informed. Similarly, lenders of all sorts, not just banks, don’t like surprises. This means, regular reporting is an important way of keeping them informed.
What is a 3 Statement Model?
A 3-way forecast, also known as 3 statement model, is simply an integrated set of reports. It comprises:
Future changes to the P & L Budgets or the Balance Sheet budgets are automatically reflected in all 3 reports, as are changes to any of the cashflow settings.
Traditionally, this has been challenging to achieve in a spreadsheet, but tools like Calxa simplify the process. The difficulty has been in avoiding circular references. Spreadsheets weren’t designed for double-entry accounting!
Calxa’s 3-Way Forecasting
The Budget Basics
If you don’t already have a budget, use the Budget Factory to create one. This will take last year’s actual results and project them forward. You can set a different percentage change for each year. Or, start your forecast from the profit figure and build the budget from there.
Most of the balance sheet accounts, certainly the difficult ones, are calculated for you. You just need to pay attention to the bigger things like asset purchases, loan repayments and that sort of thing – these can have a big impact on your cashflow projections so don’t neglect them.
At this point, it may be a good time to refresh your knowledge on the difference between Budgets and Cash Flow Forecast. This is particularly useful to understand timing of inflows and outflows discussed in the next section.
Timing of Payments and Receipts
The default setting for income accounts is a cashflow type of Debtor Days. Each time you update from Xero, MYOB or QuickBooks, we calculate the average days outstanding of your overall debtors. From this, we do some statistical analysis to estimate what gets received in the month of invoicing, the following month, the next month and so on. It’s not based on the terms you set for your customers but on how they actually pay you. So, it gives a realistic estimate of your future payment patterns.
Similarly, we estimate the timing of most Cost of Sales and Expense accounts based on your average outstanding Creditor Days.
Fine-tuning the 3-Way Forecast
While the default settings are great for a first draft and close enough for many situations, sometimes you need to modify them to get a better projection. In Cashflow Settings, you can modify the Cashflow Type of each account.
Each of the options modifies the profile for this account – how much gets paid this month, next month, the following month. Use this to modify the settings just the way you need them. One of the most common changes to make is to set the Cashflow Type to Profile and then 100% Current – implying that whatever is in the budget hits the bank in the same month. This makes sense for those payments you make by direct debit each month – phone, rent, bank fees, etc. While many of them are too small to have a significant effect on the overall cashflow forecast, some of them can be major and the small ones can add up to something that makes a difference.
Automating the 3-Way Forecast
We’ve provided a sample Report Bundle Kit to get you started on reporting. Choose the option to Create a New Bundle and choose the 3-Way Forecast. Select your organisation (if you have more than one) and the budget you want to use. This will create a Report Bundle containing the key components of your 3 Statement Model. It contains not just the numerical reports but also charts to give you an instant snapshot of your predictions.
Create a workflow to then deliver your automated 3-way forecast bundle to yourself, your client, your boss on your chosen date each month. This will give you an up to date, fully consistent, 360-degree view of the future of your business.
Calxa has been automating the 3-Way Forecasts for a long time. Over the years, many accounting firms have confirmed our thoughts that we’ve come up with a pretty good solution. However, we know that sometimes this method limits those that work with larger transactions at infrequent times.
Our team is working with James Cook University and the Queensland Government, on a project using artificial intelligence and machine learning to predict a more granular forecast. As with any experimental programs, we may yield big or it may lead to nothing. We’ll keep you informed as to the progress over the coming months.