Automating the 3-Way Forecast has always been a strong point of Calxa. Now, with the imminent release of Cashflow Settings to the Calxa browser app, the final piece falls into place to complete the process of automating a 3-way forecast anywhere, anytime. After the initial data coming from your accounting system, simple tools to create your Profit & Loss and Balance Sheet budgets, the final step is setting the timing of inflows and outflows.
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. Lenders of all sorts, not just banks, don’t like surprises so regular reporting is an important way of keeping them informed.
What is it?
A 3-way forecast is simply an integrated set of reports comprising a Cashflow forecast, Balance Sheet forecast and Profit & Loss forecast. 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!
Nitty Gritty on 3-Way Forecasting in Calxa
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. With the new improvements, you can set a different percentage change for each year and even 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.
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 our 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.
Bundling the 3-way Forecast
Configure your Cashflow Forecast report (or the chart if you prefer your projection graphically) and add it to a Report Bundle. Follow this with the Balance Sheet forecast and then the Profit and Loss Projection. Set the starting date for each of them as Relative Month +1 – this will then always give you a co-ordinated 3-way set for the future, whenever you run the bundle.
Create a workflow to then deliver your 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 doing 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 next year.